Background:
Customer has been using a custom program which runs a complex select statement and then creates a CSV file. This program has been running for the last 2 years with no issues. It used to run within 5-6 minutes
Problem Statement:
On a weekend the database was bounced for some 3Par parameter changes while the program was running. After bringing the database up, the program ran for longer time without completing. Primary DBA had to cancel the job, run GSS with 30% and try it again. It ran for almost 12 hours without completing. On Dev and Preprod databases the same process took the normal 5 minutes time. Explain plan showed that the plan on Dev and Prod were different. During the week the issue got escalated and came to me.
Analysis:
I did the initial analysis and confirmed that the plan was different in Dev and Prod. Getting level 12 trace was not feasible on Prod as the program was not finishing within reasonable time.
It was decided to test and implement “Plan Stability” via Stored Outlines.
Creating Stored Outlines:
1) Grant necessary privileges to the user where stored outlines will be generated. In our case it is the user APPS.
GRANT CREATE ANY OUTLINE TO APPS;
GRANT EXECUTE_CATALOG_ROLE TO APPS;
2) Now connect to APPS and set the session level parameter which will help generate outlines:
ALTER SESSION SET create_stored_outlines=TRUE;
3) The actual creation of the outlines can be done in two ways
i) Using Create Outline command
ii) Using DBMS_OUTLN package
i) Using Create Outline Command:
CREATE OUTLINE tt_prof FOR CATEGORY apps_outlines
ON
Check if outline has been created successfully:
SELECT name, category, sql_text FROM user_outlines WHERE category = 'APPS_OUTLINES';
Check the hints associated with the outline:
SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = 'TT_PROF';
ii) Using DBMS_OUTLN Package:
Run the SQL statement once to get the statement in the shared pool. After running check the shared pool for the following information:
SELECT hash_value, child_number, sql_text FROM v$sql WHERE sql_text LIKE '
Create the stored outline:
BEGIN
DBMS_OUTLN.create_outline(
hash_value => 3909283366,
child_number => 0,
category => 'APPS_OUTLINES');
END;
/
Check if outline has been created successfully:
SELECT name, category, sql_text FROM user_outlines WHERE category = 'APPS_OUTLINES';
Check the hints associated with the outline:
SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = '
4) Turn off the outline creation for the session using:
ALTER SESSION SET create_stored_outlines=FALSE;
Exporting Stored Outlines:
Stored outlines needed to be exported to be used in the prod database. We need to export two tables (OL$ and OL$HINTS) for successfully using stored outlines in PROD. The command for export is:
exp userid=outln/outln file=outln.dmp tables=ol\$,ol\$hints query=\”where ol_name= \’
Once the export file is available, FTP it to the PROD database server and then import it to the PROD database using:
***NOTE: PLEASE BE CAREFUL IN USING THIS STEP. MAKE SURE THAT IF DATA ALREADY EXISTS IN THESE TWO TABLES THEN IT SHOULD BE BACKED UP AND THEN RESEEDED AFTER IMPORT. PERFORMANCE TEAM IS NOT RESPONSIBLE IF THE DATA IS NOT BACKED UP PROPERLY
imp userid=outln/outln file=outln.dmp full=y ignore=y
Using Stored Outlines:
In order to use stored outlines the following parameter must be enabled at an instance or session level:
ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET use_stored_outlines=APPS_OUTLINES;
Now run your query in PROD. We can check if the query used the stored outline or not:
SELECT name, category, used FROM user_outlines;
Outcome:
Using Stored Outline brought back the response time to the original 5-6 minutes.
Conclusion:
When resolving issue quickly is the key and when initial analysis shows a discrepancy in the explain plans between different environments for the same query, stored outline can be used to force the query to use the more efficient plan. 11g uses sql profiles and I will discuss that in a later post
