Thursday, November 2, 2017

How to bulk load execution plans for a statement into the SMB


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

No comments:

Post a Comment