Comparison of unstable SQL Plan in Oracle 11gR2.
In oracle there is one of of the more frustrating things about the CBO, It behave sometimes erratic performance. Oracle SQL Plans are being unstable.
We have AWR/ASH which collects a ton of info about databases activity and performance stuff.
Here I am going to discuss how we can check the plan difference and which plan it too much costly.
1. find the SQL statement, Plan_hash_value which is much costly using SQL_ID.
SQL> select sql_id, child_number, plan_hash_value plan_hash, executions execs, elapsed_time/1000000 etime,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, u.username,
sql_text
from v$sql s, dba_users u
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
and u.user_id = s.parsing_user_id;
Enter value for sql_text:
Enter value for address:
Enter value for sql_id: 0qa98gcnnza7h
SQL_ID CHILD PLAN_HASH EXECS ETIME AVG_ETIME USERNAME SQL_TEXT
------------- ------ ---------- ------------ ------------- ------------- ------------- -----------------------------------------
0qa98gcnnza7h 0 3723858078 5 356.53 71.31 SYS select max(FD_ID) from scott.sprout where cot1 < 10
0qa98gcnnza7h 1 568322376 1 7.92 7.92 SYS select max(FD_ID) from scott.sprout where cot1 < 10
0qa98gcnnza7h 2 568322376 10 52.14 5.21 SYS select max(FD_ID) from scott.sprout where cot1 < 10
0qa98gcnnza7h 3 568322376 30 1,064.19 35.47 KSO select max(FD_ID) from scott.sprout where cot1 < 10
0qa98gcnnza7h 4 3723858078 10 4,558.62 455.86 KSO select max(FD_ID) from scott.sprout where cot1 < 10
2. Then find the what changes are happened in Plan in sql, you can for that sql there and different plan hash value with estimated time of execution.
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3;
Enter value for sql_id: 0qa98gcnnza7h
SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_LIO
---------- ------ ------------------------------ ------------- --------------- ------------ ------------ --------------
3206 1 02-OCT-08 08.00.38.743 AM 0qa98gcnnza7h 568322376 4 10.359 121,722.8
3235 1 03-OCT-08 01.00.44.932 PM 0qa98gcnnza7h 1 10.865 162,375.0
3235 1 03-OCT-08 01.00.44.932 PM 0qa98gcnnza7h 3723858078 1 127.664 28,913,271.0
3236 1 03-OCT-08 01.28.09.000 PM 0qa98gcnnza7h 568322376 1 7.924 162,585.0
3236 1 03-OCT-08 01.28.09.000 PM 0qa98gcnnza7h 3723858078 1 86.682 27,751,123.0
3305 1 06-OCT-08 10.00.11.988 AM 0qa98gcnnza7h 4 64.138 22,616,931.5
3305 1 06-OCT-08 10.00.11.988 AM 0qa98gcnnza7h 568322376 2 5.710 81,149.0
3306 1 06-OCT-08 11.00.16.490 AM 0qa98gcnnza7h 6 5.512 108,198.5
3307 1 06-OCT-08 12.00.20.716 PM 0qa98gcnnza7h 2 3.824 81,149.0
3328 1 07-OCT-08 08.39.20.525 AM 0qa98gcnnza7h 30 35.473 156,904.7
3335 1 07-OCT-08 03.00.20.950 PM 0qa98gcnnza7h 3723858078 10 455.862 28,902,128.6
3. Now you can compare the Plan difference according to estimated time of execution and watch the difference.
SQL>SELECT * FROM table(dbms_xplan.display_awr(nvl(‘&sql_id’,’a96b61z6vp3un’),nvl(‘&plan_hash_value’,null),null,’typical +peeked_binds’));
Enter value for sql_id: 0qa98gcnnza7h
Enter value for plan_hash_value:
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0qa98gcnnza7h
--------------------
select max(FD_ID) from scott.sprout where cot1 < 10
Plan hash value: 568322376
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 44497 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS FULL| SKEW | 32M| 335M| 44497 (2)| 00:08:54 |
---------------------------------------------------------------------------
SQL_ID 0qa98gcnnza7h
--------------------
select max(FD_ID) from scott.sprout where cot1 < 10
Plan hash value: 3723858078
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW |
| 3 | INDEX RANGE SCAN | SKEW_cot1 |
--------------------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
8 rows selected.
No comments:
Post a Comment