Facts:
* Your customer has a query against a couple of tables with very volatile contents
* The problem is simply solved by analyzing the tables involved using dbms_stats.gather_table_stats. After that, the query runs with acceptable performance
Solution:
* Add the SQLs execution plan to the SMB as a FIXED statement, so the optimizer will use it next time
Here is how:
1. Make sure the parameter optimizer_use_sql_plan_baselines is set to TRUE.
This is default for 11g.
2. Find the sql_id for the query you want to mark as fixed.
Easiest is to use Enterprise Manager.
3. When found, make a note of the sql_id. Then create an STS as follows:
BEGIN
DBMS_SQLTUNE.create_sqlset
(sqlset_name => 'ACCEPTED_NESTED_LOOP_QUERY',
description => 'Freeze of execution plan requested by customer.',
sqlset_owner => 'DB_ADMIN'
);
END;
/
4. Add the sql_id found in step 1 to your STS:
DECLARE
sqlset_cur dbms_sqltune.sqlset_cursor;
bf VARCHAR2(82);
BEGIN
bf := q'#UPPER(PARSING_SCHEMA_NAME) = 'SCOTT' AND UPPER(SQL_ID) = 'BHAANST01X9YB' #';
OPEN sqlset_cur FOR
SELECT VALUE(P) FROM TABLE(
dbms_sqltune.select_cursor_cache(bf, NULL, NULL, NULL, NULL, 1, NULL, 'TYPICAL')) P;
dbms_sqltune.load_sqlset(
sqlset_name => 'ACCEPTED_NESTED_LOOP_QUERY',
populate_cursor => sqlset_cur,
load_option => 'MERGE',
update_option => 'ACCUMULATE',
sqlset_owner => 'DB_ADMIN');
END;
/
5. Load the plan into the SQL Management Base (SMB). Put the following into a file called manual_load.sql:
declare
l_num_plans PLS_INTEGER;
begin
l_num_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET
(
'ACCEPTED_NESTED_LOOP_QUERY',
'DB_ADMIN'
,NULL,
'YES',
'YES',
1000
);
DBMS_OUTPUT.put_line('Number of plans loaded: ' || l_num_plans);
end;
/
Result:
$ sqlplus /nolog
SQL> connect DB_ADMIN
Enter password:
Connected.
SQL> set serveroutput on
SQL> @manual_load.sql
Number of plans loaded: 1
PL/SQL procedure successfully completed.
6. Verify that the plan is indeed saved:
SELECT sql_text,
plan_name,
sql_handle,
creator,
origin,
created,
last_executed,
enabled,
accepted,
fixed,
autopurge,
module
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT like '%your sql text%';
SQL_TEXT | PLAN_NAME | SQL_HANDLE | CREATOR | ORIGIN | CREATED | LAST_EXECUTED | ENABLED | ACCEPTED | FIXED | AUTOPURGE | MODULE |
your statement will be visible here | SYS_SQL_PLAN_50701d3df0871b58 | SYS_SQL_c95f410d50701d3d | DBA_ADMIN | MANUAL-LOAD | 06.11.2013 15:36:20.000000 | | YES | YES | YES | YES | TOAD background query session |
To view the execution plan that is now fixed in the SMB, use the DISPLAY_SQL_PLAN_BASELINE function of the DBMS_XPLAN package:
sqlplus / as sysdba
SQL>
set long 10000
set lines 200
set pages 300
SELECT *
FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_50701d3df0871b58'));
Further reading on the topic:
Oracle-base.com: SQL Plan Management in Oracle Database 11g Release 1
Oracle Database Performing tuning guide
Adaptive cursors and SQL Plan Managment by Arup Nanda