Advisor to Baseline – am I missing something?
March 18, 2011 3 Comments
I’ve mentioned before that I’m in the middle of an upgrade to 18.104.22.168.
I’ve been looking at the SQL Tuning Advisor, looking at Baselines, running into a performance crippling bug with baseline capture, running away from baselines, and now coming back to baselines now that the bug has been approved by the client internally.
Here’s what I want to do:
- Create a Guaranteed Restore Point
- Run “The Batch”
- Run a Tuning Advisor on the top N statements
- Accept some of the Advisor recommendations
- Export these recommendations
- Flashback to restore point
- Rerun same “The Batch”
(It may be that RAT would be a good solution for this cycle – we’re not licensing it I believe)
So, running the batch – done.
Some performance degradations – check.
Given 30 minutes to look at some pretty big statements statement, the good old SQL Tuning Advisor can come up with far more than I can (there’s still a place for manual tuning, of course).
So, if I take from AWR the top N statements by some metric from “The Batch” and give the SQL Tuning Advisor a few hours to chunter away, it should be a really positive contribution in a short space of time.
First up, this is a nice little function – DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY.
So, I know the AWR snaps that cover “The Batch” so to get the top 50 statements for example by elapsed time from AWR, I do this:
SELECT * FROM TABLE(dbms_sqltune.select_workload_repository (begin_snap => 4005, end_snap => 4011, basic_filter => 'parsing_schema_name in (''ABC'',''XYZ'')', ranking_measure1 => 'elapsed_time', result_limit => 50, attribute_list => 'ALL' )) p;
I’ve not included the output because it’s a bit wide. Suffice to say, I’ve got my 50 statements.
I can load these up into a SQL tuning set like this:
DECLARE baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'MY_11G_TUNING_SET', description => 'My 11g tuning set'); OPEN baseline_cursor FOR SELECT VALUE(p) FROM TABLE(dbms_sqltune.select_workload_repository (begin_snap => 4005, end_snap => 4011, basic_filter => 'parsing_schema_name in (''ABC'',''XYZ'')', ranking_measure1 => 'elapsed_time', result_limit => 50, attribute_list => 'ALL' )) p; DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name => 'MY_11G_TUNING_SET', populate_cursor => baseline_cursor); END; /
And then I can set the advisor on this bad boy thus:
DECLARE l_task_name varchar2(200) := 'MY_11g_TUNING_TASK'; l_sqlset_name varchar2(200) := 'MY_11G_TUNING_SET'; BEGIN l_task_name := dbms_sqltune.create_tuning_task (sqlset_name => l_sqlset_name, task_name => l_task_name, time_limit => 15300); dbms_sqltune.set_tuning_task_parameter (task_name => l_task_name, parameter => 'LOCAL_TIME_LIMIT', value => DBMS_SQLTUNE.TIME_LIMIT_DEFAULT); dbms_sqltune.execute_tuning_task (task_name => l_task_name); END; /
Note that I’ve given it a few hours to go off and do its stuff but I’ve restricted it to 30 minutes per statement using the TIME_LIMIT_DEFAULT argument.
Some time later, it’s done and I can get back the results like so:
SELECT dbms_sqltune.report_tuning_task('MY_11g_TUNING_TASK'') FROM DUAL;
And I get a great big stonking report back. Again, no point copying it in here.
Suffice to say that it mentions a bunch of indexes, doing lots more work in parallel, but for now I don’t want to rush into accepting profiles running all 50 statements simultaneously with a DOP of 48 🙂 – I’m just interested in the simpler SQL profiles it is recommending.
Next, I accept the sql profiles.
I can see them in dba_sql_profiles.
That would do me fine. I can do this. Initial aims up top met.
But, I thought I’d be able to run the advisor, accept the profiles and somehow move all this stuff into a baseline, then move the baseline.
But it’s not working as planned.
I’ve already got the tuning set and I’ve accepted the profiles recommended.
I had thought that if I used DBMS_SPM.LOAD_PLANS_FROM_SQLSET that it would use the information from the profiles.
But that doesn’t seem possible.
What’s in the baseline is the hints to get the original plans from the SQL statements concerned – i.e. the hints to get the plans which the Advisor subsequently poo-pooed as trumpable with a profile.
And I think it’s because I need to run the SQL with the profiles so that the appropriate plans exist against the relevant SQL, then create a tuning set of those sql statements and their plans and then load that into a baseline which can use the specific hints for a specific executed plan.
Of course, I might have got it the wrong way round. I know a baseline re-optimises statements at hard parse time and the SQL Tuning Advisor can run on baselines.
But it definitely seems at the moment that the initial baseline population needs an executed plan.
More later perhaps if I decide to knock up a specific isolated example to demonstrate or work it out … but not today though. It’s Friday after all.