Question: I
am migrating to Oracle 11g and I hear that it is important
to capture and save all of my 10g SQL execution plans.
What are the ways to get the 10g SQL plans?
Answer: Oracle
SQL Tuning Sets are one way to capture the execution
plans for your most popular SQL:
STEP 1: Create a SQL Tuning set:
-- create a SQL Tuning set --
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name => 'SPM_STS',
description => '10g plans');
END;
/
STEP 2: Populate the SQL tuning set created in STEP 1
from the current cursor cache:
DECLARE
stscur
dbms_sqltune.sqlset_cursor;
BEGIN
OPEN stscur FOR
SELECT VALUE(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(
'parsing_schema_name <> ''SYS''',
null,
null, null, null, 1, null, 'ALL')) P;
-- populate the SQL tuning set
dbms_sqltune.load_sqlset(sqlset_name
=> 'SPM_STS', populate_cursor => stscur);
END;
/
STEP 3: you can also query and see how many 10g plans
were captured:
select * from dba_sqlset;
