I’m a perfectionist as much as the next person (maybe more so), but I’ve been a DBA long enough to know that when management want results, they do not tolerate perfectionists. Picture this scenario; database performance is through the floor, key jobs are not completing on time, but nothing has changed – obviously, Do you
(a) Get volume 3 of the tuning guide and refer to the section on SQL Performance
(b) Use Enterprise Manager / Cloud Control to tune the SQL
(c) Have a meeting to discuss the possible causes and solutions
(d) Call Oracle Support and log a bug in their optimizer
(e) Call Oracle Support and complain about the performance?
(f) Sit at your terminal / PC and madly type commands in the hope that something will change, while your manager glares at you?
The correct answer, of course, is all of the above, but if I had to choose one option I’d choose (e). Then at least you have a team on your side, which means you don’t need to read the manual , option (a), nor do you need to use EM, as alternatives are available, a meeting to discuss options is not needed as you’ll be speaking to an expert in support. A bug in the optimizer is an outside possibility, but if it is a bug, support will coordinate that for you, and of course they’ll ask you to collect information which covers option (f).
One of the best tools for dealing with bad performance is SQLT and it has embedded in it a nice routine which ‘captures’ the performance of a particular SQL ID and imposes it on a SQL. So, if your SQL has regressed – which is code for the performance is now worse than it was – you can flash back the query to a previous execution plan with a SQL Profile. I like to call this technique a Severity 1 killer. It changes a severity 1 tuning problem into a nice exercise in finding out why the performance regressed. Use SQLT with the following steps
SQL> exec sqltxplain.sqlt$a.set_param(‘custom_sql_profile’,'Y’)’
SQL> @sqltprofile abcde nnnnnnnnnn
This enables the required feature then creates a profile to attach to the SQL. This sets the plan back to the plan as it was at some previous time. You choose the time of this by selecting the appropriate plan hash value. The values in the second command above are SQLT ID (abcde) and the plan hash values (nnnnnnnnnn). You can even transfer these SQL Profiles from one system to another. See MOS note 457531.1, which covers this procedure.
This turns you into a hero, who then has time to analyze the root cause. Management are then your best friends for ever. It’s great being a DBA on the front line, but you need the tools to do the job and the ability to cope with pressure.
Good luck and remember, support are your friends.