To load a plan found in an AWR snapshot into the SMB, you need to add it to an STS first, then load it into the SMB.
Here is how:
1. create an empty STS:
exec dbms_sqltune.create_sqlset(sqlset_name=>'mysts');
2. Note the snapshots where your plan was recorded, and add the plans to your STS:
DECLARE
my_cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN my_cur FOR
SELECT VALUE(x)
FROM TABLE( dbms_sqltune.select_workload_repository(begin_snap=>5840,
end_snap=>5841,
basic_filter=>'sql_id = ''45wmakdh9ak9s''')) x;
dbms_sqltune.load_sqlset(sqlset_name => 'mysts', populate_cursor => my_cur);
END;
/
You can now check your STS for its contents:
select sqlset_name,
sql_id,
plan_timestamp,
parsing_schema_name "schema",
plan_hash_value,round(elapsed_time/1000000) "duration in seconds",
cpu_time,
buffer_gets,
disk_reads,
executions
from dba_sqlset_statements
where sqlset_name = 'mysts'
;
| SQLSET_NAME | SQL_ID | PLAN_TIMESTAMP | schema | PLAN_HASH_VALUE | duration in seconds | CPU_TIME | BUFFER_GETS | DISK_READS | EXECUTIONS |
|---|---|---|---|---|---|---|---|---|---|
| mysts | 45wmakdh9ak9s | 11.09.2018 15:22:13 | SH | 3827183161 | 0 | 0 | 0 | 0 | 0 |
| mysts | 45wmakdh9ak9s | 06.09.2018 16:45:26 | SH | 4026264003 | 579 | 85550842 | 24062750 | 101808 | 3278857 |
We can see from the output that there are actually two different plans in the STS now, one with plan_hash_value=3827183161 and another one with plan_hash_value=4026264003.
In my case I wanted only the one with plan_hash_value=3827183161.
3. Finallly, load the contents of your STS into your SMB:
VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
sqlset_name=>'mysts', basic_filter=>'plan_hash_value=3827183161', sqlset_owner=>'SYS');
print :cnt;
