Short background:
Customer were complaining about long execution times for a specific SQL.
Checking in the database:
select distinct sql_id,plan_hash_value, child_number,ROUND(ELAPSED_TIME/1000000) "duration in seconds"
from v$sql where sql_text like 'SELECT col1, col2, col3 .... FROM table 1 join table 2 %'
and parsing_schema_name='MYUSER'
and ROUND(ELAPSED_TIME/1000000) > 1000
order by 4 desc
;
These were the queries with the longest elapsed times:
| SQL_ID | PLAN_HASH_VALUE | CHILD_NUMBER | duration in seconds |
| cr43vm86rp9cy | 3790053540 | 0 | 2387 |
| fhtdk8ms42z0s | 3790053540 | 0 | 2255 |
| abbv8g54zzhjf | 3790053540 | 0 | 1424 |
| cragwkcwwvkrq | 3790053540 | 0 | 1106 |
Do these SQL IDs have any alternative plans?
select distinct sql_id,count(*) "num plans"
from v$sql where sql_text like ''SELECT col1, col2, col3 .... FROM table 1 join table 2 %'
and parsing_schema_name='MYUSER'
and sql_id in ('cr43vm86rp9cy','fhtdk8ms42z0s','abbv8g54zzhjf','cragwkcwwvkrq')
group by sql_id;
Yes they do:
| SQL_ID | num plans |
| cr43vm86rp9cy | 2 |
| abbv8g54zzhjf | 3 |
| fhtdk8ms42z0s | 3 |
| cragwkcwwvkrq | 3 |
Looking more closely at these 4 specific SQL IDs, it quickly became appaerant that the queries all had alternative plans generated for them, and plan hash value 133974613 gave the lowest execution times for all of these 4 samples:
select distinct sql_id,plan_hash_value, child_number,ROUND(ELAPSED_TIME/1000000) "duration in seconds", LPAD(is_resolved_adaptive_plan,15,' ') "adaptive?"
from v$sql where sql_text like 'SELECT col1, col2, col3 .... FROM table 1 join table 2 %'
and parsing_schema_name='MYUSER'
and sql_id in ('cr43vm86rp9cy','fhtdk8ms42z0s','abbv8g54zzhjf','cragwkcwwvkrq')
order by 1,2
;
| SQL_ID | PLAN_HASH_VALUE | CHILD_NUMBER | duration in seconds |
| abbv8g54zzhjf | 133974613 | 1 | 1 |
| abbv8g54zzhjf | 133974613 | 2 | 16 |
| abbv8g54zzhjf | 3790053540 | 0 | 1424 |
| cr43vm86rp9cy | 133974613 | 1 | 15 |
| cr43vm86rp9cy | 3790053540 | 0 | 2387 |
| cragwkcwwvkrq | 133974613 | 1 | 1 |
| cragwkcwwvkrq | 133974613 | 2 | 14 |
| cragwkcwwvkrq | 3790053540 | 0 | 1106 |
| fhtdk8ms42z0s | 133974613 | 1 | 1 |
| fhtdk8ms42z0s | 133974613 | 2 | 19 |
| fhtdk8ms42z0s | 3790053540 | 0 | 2255 |
We can also confirm by looking at the average duration for each cursor:
select sql_id, plan_hash_value, avg( ROUND(ELAPSED_TIME/1000000) ) "avg duration in seconds"
from v$sql where sql_text like ''SELECT col1, col2, col3 .... FROM table 1 join table 2 %'
and parsing_schema_name='MYUSER'
and sql_id in ('cr43vm86rp9cy','fhtdk8ms42z0s','abbv8g54zzhjf','cragwkcwwvkrq')
group by sql_id, plan_hash_value
order by 3;
Result:
| SQL_ID | PLAN_HASH_VALUE | avg duration in seconds |
| cragwkcwwvkrq | 133974613 | 7,5 |
| abbv8g54zzhjf | 133974613 | 8,5 |
| fhtdk8ms42z0s | 133974613 | 10 |
| cr43vm86rp9cy | 133974613 | 15 |
| cragwkcwwvkrq | 3790053540 | 1106 |
| abbv8g54zzhjf | 3790053540 | 1424 |
| fhtdk8ms42z0s | 3790053540 | 2255 |
| cr43vm86rp9cy | 3790053540 | 2387 |
We can firmly establish that one of the plans, the one with hash value 133974613, is much more efficent than the other.
As a matter of fact, I found that almost all of the SQL IDs having executed this query, would be much better off With using plan hash value = 133974613.
So let's load them into the management base.
First, create an empty SQL Tuning Set (STS):
exec dbms_sqltune.create_sqlset(sqlset_name=>'SD16659');
Populate the STS. Since almost all of the queries would be better off using this particular plan, we can use the plan hash value as the only criterion in our "basic filter" directive
DECLARE
my_cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN my_cur FOR
SELECT VALUE(x)
FROM TABLE( dbms_sqltune.select_cursor_cache(basic_filter=>'plan_hash_value = ''133974613''')) x;
dbms_sqltune.load_sqlset(sqlset_name => 'SD16659', populate_cursor => my_cur);
END;
/
From the view dba_sqlset, we can see that the previous code loaded the STS with 177 statements:
select name,owner,created,statement_count
from dba_sqlset
where name = 'SD16659';
| ID | CON_DBID | NAME | OWNER | CREATED | STATEMENT_COUNT |
4 | 2788575331 | SD16659 | SYS | 02.11.2017 12:16:04 | 177 |
Some more information about each statement in the STS can be found in the view DBA_SQLSET_STATEMENTS (fetching only the first 3 rows as an example):
select sql_id,plan_timestamp,parsing_schema_name,plan_hash_value,round(elapsed_time/1000000) "duration in seconds",cpu_time,buffer_gets,disk_reads,executions
from dba_sqlset_statements
where sqlset_name = 'SD16659'
fetch first 3 rows only;
| SQL_ID | PLAN_TIMESTAMP | PARSING_SCHEMA_NAME | PLAN_HASH_VALUE | duration in seconds | CPU_TIME | BUFFER_GETS | DISK_READS | EXECUTIONS |
| fza8j57a8n779 | 01.11.2017 23:25:45 | MYUSER | 133974613 | 4 | 3568458 | 62313 | 126 | 6 |
| 2srumd4vbs8j2 | 01.11.2017 23:51:21 | MYUSER | 133974613 | 2 | 1925707 | 17561 | 10 | 2 |
| 5ptw9m80y4asg | | MYUSER | 133974613 | 4 | 3550461 | 167656 | 9 | 18 |
Now we can manually add them to the database's SQL Plan Management Base.
VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
sqlset_name=>'SD16659', sqlset_owner=>'SYS');
print :cnt;
When executing the previous code, 100 plans were loaded into the SMB.
Details about the content of your SMB can be found by querying the view DBA_SQL_PLAN_BASELINES.
For example:
SELECT plan_name,
sql_handle,
creator,
origin,
TO_CHAR (created, 'dd.mm.yyyy hh24:mi') "created",
TO_CHAR (last_executed, 'dd.mm.yyyy hh24:mi') "lastexecuted",
enabled,
accepted,
fixed,
autopurge,
module,
ROUND (ELAPSED_TIME / 1000000) "duration in seconds",
cpu_time,
buffer_gets,
disk_reads
FROM DBA_SQL_PLAN_BASELINES
WHERE TO_CHAR (created, 'dd.mm.yyyy') = TRUNC (SYSDATE)
ORDER BY created ASC
;
The link to Oracle Documentation about manually loading the SMB can be found
here
The usage of dbms_spm can be found
here
