This past couple of weeks i was contacted about a performance issue with some plsql code in one of our live systems. The batch job was taking well over an hour to run where previously (allegedly) it was running 'much' faster than that.
OK i said I'll have a look - thinking it was just going to be a simple piece of code.
It turned out the problem code was several thousand lines long with calls to other procedures too....every wished you'd said no.......
So how to go about fixing such a 'large' problem?
The first thing i did was run the plsql block with debug mode switched on and wrapped it in the plsql profile procedure calls (well this was all done via my plsql developer gui - other tools are of course available.....). Thus breaks down all the plsql run and gives you a nice breakdown of where the time is being spent - as DBA's we rarely use this it;s more aimed at developers but it's surprising easy and nice to use.
This quickly showed me that it was in fact one piece of SQL inside the thousands of lines of plsql causing the issue.
So the problem just got easier right? Well yes and no....
The statement was an insert append with mutiple unions and joins, fetching from views and nested tables...... all in all about 600 lines long (would be much more apart from the views).
So how to deal with that?
Well first of course i look at the explain plan (from the executed code of course - not some approximation).
Now the plan was 103 lines long which apart from anythng else is hard to read let alone debug....
Now this is the really difficult bit - looking at the plan and trying to work out why oracle is doing badly - a good pointer is always to look at estimated rows vs actual rows - this is often the key factor in fixing these things - if stats are bad then oracle is going to make the wrong choices.
However in this case the stats looked ok and the plan did look sort of alright - i tried creating some indexes i thought may help and a couple of mviews which i thought would maybe remove some of the direct workload - however nothing seemed to be helping.
The volumes in the tables were all fairly large and FTS and hash joins were largely being used - from knowing about the data and what the query was trying to do (and this is perhaps the most valuable input you can have when tuning a query) this approach looked OK - in fact it looked like it should be using hash more than it was.
The system was set up with auto PGA which i generally do everywhere, however in this specifc case we needed more hash_area_size to improve the hashing speed and to also give oracle a nudge that doing hashing is the right way to go here (a large hash area does affect the explain plan the optimizer comes up with)- now this can be fiddled around with with underscore parameters with auto pga - but i didn't want to affect any more of the system as that was working fine.
So instead i reverted to using the ols style memory settings - just for my session - so to do this i just added the following lines onto the plsql block
execute immediate 'alter session set workarea_size_policy=MANUAL';
execute immediate 'alter session set sort_area_size=2048000000';
execute immediate 'alter session set hash_area_size=2048000000';
This makes just the session executing the plsql revert to old memory setting style and i increased the sort and hash area sizes up to their max (2GB).
Now when the plsql is run we get it completed in just under 14 minutes.
Now tuning this was no mean feat and a lot of trial and error got me to this point - I've maybe made it sound like it was just an easy process - it wasn't by any means.
As with any big problem you need to break it down to target what is actually wrong - there are lots of tools to do this now.
Tuning SQL is not some kind of mystical art - it just takes a lot of logical thinking, really understanding your data and a lot of trial and error. Over time you get better at it from expereince learned along the way. I would advise to always start with the simple stuff - don't jump into something hundreds of lines long as your first tuning exercise - you'll likely get very frustrated and not learn much.
So they way to tune an elephant is the same way you eat one - a little bit at a time... :-)
Comments
Post a Comment