I have a copy of sql text taken from a sql_id#1, but when manually run this query, it generates new sql_id#2, and different query plan, unfortunately a very slow performed plan.
###how do I use sql plan baseline to guide the sql_id#2 use the good plan of sql_id#1?
###Tom has this doc: let me try it:
https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9541780100346252065
### this is very useful to explain sql_handle, sql_id and signature etc.
https://www.lab128.com/all_these_oracle_ids/article_text_sql_ids/
###how to force slightly modified sql query to use good query plan
###I have a sql plan baseline already for sql_id#1, EXACT_MATCHING_SIGNATURE='16767805607617541771' SQL_PLAN_fjctqbj4djdnbb90e17cc for plan_hash_value=857947328 ( a good plan)
> @sqlid_cursor
select PARSING_SCHEMA_NAME as schema,child_number as chld#, executions as exec#, buffer_gets, is_bind_sensitive, is_bind_aware,IS_SHAREABLE,hash_value,plan_hash_value,
first_load_time,LAST_ACTIVE_TIME,EXACT_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE,SQL_PLAN_BASELINE,SQL_PATCH,SQL_PROFILE
from v$sql where sql_id='&sqlid'
order by LAST_ACTIVE_TIME;
Enter value for sqlid: 627uz3qgxftpa
SCHEMA CHLD# EXEC# BUFFER_GETS I I I HASH_VALUE PLAN_HASH_VALUE FIRST_LOAD_TIME LAST_ACTIVE_TIME EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE SQL_PLAN_BASELINE
SQL_PATCH SQL_PROFILE
--------- ----- ----- ----------- - - - ---------- --------------- ------------------- ----------------- ------------------------ ------------------------ ------------------------------ ------------------------------ --------------------
MONTHEND 0 8 811419684 N N Y 2681693866 489359993 2021-05-25/12:16:13 20210602-09:25:38 16767805607617541771 10696951138187414235
MONTHEND 2 5 759204 N N N 2681693866 857947328 2021-05-25/12:16:13 20210602-09:53:50 16767805607617541771 10696951138187414235 SQL_PLAN_fjctqbj4djdnbb90e17cc
MONTHEND 3 10 1597045 N N Y 2681693866 857947328 2021-05-25/12:16:13 20210603-06:01:10 16767805607617541771 10696951138187414235 SQL_PLAN_fjctqbj4djdnbb90e17cc
MONTHEND 5 19 2819781 N N Y 2681693866 857947328 2021-05-25/12:16:13 20210603-08:43:03 16767805607617541771 10696951138187414235 SQL_PLAN_fjctqbj4djdnbb90e17cc
MONTHEND 4 2 419778 N N N 2681693866 857947328 2021-05-25/12:16:13 20210603-13:03:30 16767805607617541771 10696951138187414235 SQL_PLAN_fjctqbj4djdnbb90e17cc
MONTHEND 6 26 3858992 N N Y 2681693866 857947328 2021-05-25/12:16:13 20210604-09:45:49 16767805607617541771 10696951138187414235 SQL_PLAN_fjctqbj4djdnbb90e17cc
6 rows selected.
### let me get the sql text for this sql_id#1
@sqltext2 to get sqltext for sql_id=627uz3qgxftpa
run it, get new sql_id#3=a1gjdmbtsywpp
--------------------
JSUN(474,54605) ospid = 98240:22596 command = 3 sql_id = a1gjdmbtsywpp program = sqlplus.exe dedicated server=27704
Friday 09:13 Friday 09:47 last et = 2
09:49:58 > @sqlid_cursor
Session altered.
Elapsed: 00:00:00.04
Enter value for sqlid: a1gjdmbtsywpp
SCHEMA CHLD# EXEC# BUFFER_GETS I I I HASH_VALUE PLAN_HASH_VALUE FIRST_LOAD_TIME LAST_ACTIVE_TIME EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE SQL_PLAN_BASELINE SQL_PATCH SQL_PROFILE
--------- ----- ----- ----------- - - - ---------- --------------- ------------------- ----------------- ------------------------ ------------------------ ------------------------------ ------------------------------ --------------------
JSUN 1 1 196003 N N N 4086264501 857947328 2021-06-04/09:47:11 20210604-09:47:17 16767805607617541771 10696951138187414235 SQL_PLAN_fjctqbj4djdnbb90e17cc
JSUN 2 1 202565 N N Y 4086264501 857947328 2021-06-04/09:47:11 20210604-09:48:01 16767805607617541771 10696951138187414235 SQL_PLAN_fjctqbj4djdnbb90e17cc
2 rows selected.
###so it's using the baseline, though sql_id and hash_value are changed, but the exact_matching_signature are same
### explained here: https://www.lab128.com/all_these_oracle_ids/article_text_sql_ids/
###now add a space at end of first line and run it, noticed that the sql_id did not changed, still sql_id=a1gjdmbtsywpp
SQL> select sid,serial#,sql_id,prev_sql_id from v$session where username='JSUN';
SID SERIAL# SQL_ID PREV_SQL_ID
---------- ---------- ------------- -------------
319 44891 a1gjdmbtsywpp
###now add a space after the first word "select" of first line and run it, noticed that the sql_id changed, sql_id=4zkph1c7dmrpz
--------------------
JSUN(319,44891) ospid = 97316:83376 command = 3 sql_id = 4zkph1c7dmrpz program = sqlplus.exe dedicated server=17411
Friday 10:26 Friday 10:29 last et = 2
SQL> select sid,serial#,sql_id,prev_sql_id from v$session where username='JSUN';
SID SERIAL# SQL_ID PREV_SQL_ID
---------- ---------- ------------- -------------
319 44891 4zkph1c7dmrpz
###noticed that though sql_id changed, but it's using the baseline, because the EXACT_MATCHING_SIGNATURE remain same.
SCHEMA CHLD# EXEC# BUFFER_GETS I I I HASH_VALUE PLAN_HASH_VALUE FIRST_LOAD_TIME LAST_ACTIVE_TIME EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE SQL_PLAN_BASELINE SQL_PATCH SQL_PROFILE
--------- ----- ----- ----------- - - - ---------- --------------- ------------------- ----------------- ------------------------ ------------------------ ------------------------------ ------------------------------ --------------------
JSUN 1 1 195750 N N Y 249159359 857947328 2021-06-04/10:29:13 20210604-10:29:18 16767805607617541771 10696951138187414235 SQL_PLAN_fjctqbj4djdnbb90e17cc
1 row selected.
###now let me switch the first line and second line and run it, noticed that the sql_id changed to dw7ujxbty2t2u, this is my sql_id#2,
### also the EXACT_MATCHING_SIGNATURE changed, it's not using the baseline anymore, using plan hash value=489359993, this is a bad plan that will run forever
--------------------
JSUN(319,44891) ospid = 97316:83376 command = 3 sql_id = dw7ujxbty2t2u program = sqlplus.exe dedicated server=17411
Friday 10:26 Friday 10:40 last et = 2
SQL> @sqlid_cursor
Session altered.
Enter value for sqlid: dw7ujxbty2t2u
old 3: from v$sql where sql_id='&sqlid'
new 3: from v$sql where sql_id='dw7ujxbty2t2u'
SCHEMA CHLD# EXEC# BUFFER_GETS I I I HASH_VALUE PLAN_HASH_VALUE FIRST_LOAD_TIME LAST_ACTIVE_TIME EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE SQL_PLAN_BASELINE SQL_PATCH SQL_PROFILE
--------- ----- ----- ----------- - - - ---------- --------------- ------------------- ----------------- ------------------------ ------------------------ ------------------------------ ------------------------------ --------------------
JSUN 0 1 7392825 N N Y 4091634778 489359993 2021-06-04/10:40:37 20210604-10:42:08 17374508309824708810 4779597513614508270
1 row selected.
###Let's see if I can trick to let it use the baseline:
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'MySTS01');
END;
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'MySTS01',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => 'dw7ujxbty2t2u');
print :cnt
PL/SQL procedure successfully completed.
CNT
----------
1
SELECT
first_load_time, executions as execs , parsing_schema_name , elapsed_time / 1000000 as elapsed_time_secs , cpu_time / 1000000 as cpu_time_secs ,
buffer_gets ,
disk_reads ,
direct_writes ,
rows_processed ,
fetches ,
optimizer_cost ,
sql_plan ,
plan_hash_value ,
sql_id
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'MySTS01')
);
no rows selected
### so nothing in the tunning set, let me get it from historical table instead.
select plan_hash_value,max(snap_id) FROM dba_hist_sqlstat where sql_id='dw7ujxbty2t2u' group by plan_hash_value;
PLAN_HASH_VALUE MAX(SNAP_ID)
--------------- ------------
489359993 222388
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>222387, end_snap=>222388,basic_filter=>'sql_id = ''dw7ujxbty2t2u''',attribute_list=>'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'MySTS01', populate_cursor=>cur);
CLOSE cur;
END;
DECLARE
cursys_refcursor;
BEGIN
OPENcurFOR
SELECTVALUE(P)
FROMTABLE(
dbms_sqltune.select_workload_repository(begin_snap=>222387,end_snap=>222388,basic_filter=>'sql_id=''dw7ujxbty2t2u''',attribute_list=>'ALL'))p;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name=>'MySTS01',populate_cursor=>cur);
CLOSEcur;
END;
SELECT
first_load_time,executions as execs,parsing_schema_name,elapsed_time/1000000aselapsed_time_secs,cpu_time/1000000ascpu_time_secs,
buffer_gets,disk_reads,direct_writes,rows_processed,fetches,optimizer_cost,
--sql_plan,
plan_hash_value,sql_id
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name=>'MySTS01'))
;
FIRST_LOAD_TIME EXECS PARSING_SCHEMA_NAME ASELAPSED_TIME_SECS ASCPU_TIME_SECS BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES OPTIMIZER_COST PLAN_HASH_VALUE SQL_ID
------------------- ---------- ------------------------------ ------------------- --------------- ----------- ---------- ------------- -------------- ---------- -------------- --------------- -------------
1 JSUN 94.642963 90.0766 7788436 1831030 0 0 1 307 489359993 dw7ujxbty2t2u
1 row selected.
### now we have it in tunning set, let me load it to baseline
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'MySTS01',
basic_filter=>'plan_hash_value = ''3343592418'''
);
END;
11:38:17 > @planbaseline
SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC EXECS ELAPE BUFFER
---------------------- ------------------------------------------ ------------------------------------------------------- --- --- ---------- ---------- ----------
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnb9f5c9e2d SELECT asl.sail_leg_seq, vessel_id, actual_d YES YES 0 0 0
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnbb90e17cc SELECT asl.sail_leg_seq, vessel_id, actual_d YES YES 0 0 0
SQL_f11ea740f22ca0ca SQL_PLAN_g27p783t2t86a2b4860d2 SELECT to_char (actual_dept_ts, 'DD-Mon- YES YES 1
### disable it because it's bad query plan with poor performance
exec dbms_spm.alter_sql_plan_baseline(sql_handle=>'&sql_handle',plan_name=>'&plan_name',attribute_name=>'ENABLED',attribute_value=>'NO');
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle => NULL,
plan_name => 'SQL_PLAN_g27p783t2t86a2b4860d2',
attribute_name => 'enabled',
attribute_value => 'NO');
END;
@planbaseline
SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC EXECS ELAPE BUFFER
---------------------- ------------------------------------------ ------------------------------------------------------- --- --- ---------- ---------- ----------
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnb9f5c9e2d SELECT asl.sail_leg_seq, vessel_id, actual_d YES YES 0 0 0
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnbb90e17cc SELECT asl.sail_leg_seq, vessel_id, actual_d YES YES 0 0 0
SQL_f11ea740f22ca0ca SQL_PLAN_g27p783t2t86a2b4860d2 SELECT to_char (actual_dept_ts, 'DD-Mon- NO YES 1
### now trick it as Tom's note, this sql_id#2 is the sql_handle => 'SQL_f11ea740f22ca0ca', but let it use sql_id#1's plan_hash_value => '857947328')
execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_handle => 'SQL_f11ea740f22ca0ca', sql_id => '627uz3qgxftpa', plan_hash_value => '857947328');
PL/SQL procedure successfully completed.
SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC EXECS ELAPE BUFFER
---------------------- ------------------------------------------ ------------------------------------------------------- --- --- ---------- ---------- ----------
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnb9f5c9e2d SELECT asl.sail_leg_seq, vessel_id, actual_d YES YES 0 0 0
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnbb90e17cc SELECT asl.sail_leg_seq, vessel_id, actual_d YES YES 0 0 0
SQL_f11ea740f22ca0ca SQL_PLAN_g27p783t2t86a2b4860d2 SELECT to_char (actual_dept_ts, 'DD-Mon- NO YES 1
SQL_f11ea740f22ca0ca SQL_PLAN_g27p783t2t86ab90e17cc SELECT to_char (actual_dept_ts, 'DD-Mon- YES YES 64
### run the query sql_id#2 again, it complete in a few seconds, verify it's using baseline and good plan:
12:09:07 > select sid,serial#,sql_id,prev_sql_id from v$session where username='JSUN';
SID SERIAL# SQL_ID PREV_SQL_ID
------ ---------- ------------- -------------
864 11035 dw7ujxbty2t2u
12:12:33 > @sqlid_baseline
SELECT distinct sql_handle, plan_name,sql_id ,b.signature,s.FORCE_MATCHING_SIGNATURE,s.exact_MATCHING_SIGNATURE
from dba_sql_plan_baselines b ,
v$sqlstats s
where b.signature=s.exact_MATCHING_SIGNATURE
SQL_HANDLE PLAN_NAME SQL_ID SIGNATURE FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
------------------------------ ------------------------------ ------------- ----------------------- ------------------------ ------------------------
SQL_f11ea740f22ca0ca SQL_PLAN_g27p783t2t86a2b4860d2 0tmjdwnacq5bb 17374508309824708810 4779597513614508270 17374508309824708810
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnb9f5c9e2d 627uz3qgxftpa 16767805607617541771 10696951138187414235 16767805607617541771
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnb9f5c9e2d 4zkph1c7dmrpz 16767805607617541771 10696951138187414235 16767805607617541771
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnb9f5c9e2d 7fjm2nqvn89fh 16767805607617541771 10696951138187414235 16767805607617541771
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnbb90e17cc buj564abnncbd 16767805607617541771 10696951138187414235 16767805607617541771
SQL_f11ea740f22ca0ca SQL_PLAN_g27p783t2t86ab90e17cc 0tmjdwnacq5bb 17374508309824708810 4779597513614508270 17374508309824708810
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnbb90e17cc 627uz3qgxftpa 16767805607617541771 10696951138187414235 16767805607617541771
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnbb90e17cc a1gjdmbtsywpp 16767805607617541771 10696951138187414235 16767805607617541771
SQL_f11ea740f22ca0ca SQL_PLAN_g27p783t2t86a2b4860d2 dw7ujxbty2t2u 17374508309824708810 4779597513614508270 17374508309824708810
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnb9f5c9e2d buj564abnncbd 16767805607617541771 10696951138187414235 16767805607617541771
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnbb90e17cc d7cfbp7b51sv3 16767805607617541771 10696951138187414235 16767805607617541771
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnbb90e17cc 7fjm2nqvn89fh 16767805607617541771 10696951138187414235 16767805607617541771
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnb9f5c9e2d d7cfbp7b51sv3 16767805607617541771 10696951138187414235 16767805607617541771
SQL_f11ea740f22ca0ca SQL_PLAN_g27p783t2t86ab90e17cc dw7ujxbty2t2u 17374508309824708810 4779597513614508270 17374508309824708810
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnbb90e17cc 4zkph1c7dmrpz 16767805607617541771 10696951138187414235 16767805607617541771
SQL_e8b3365c48d8b68b SQL_PLAN_fjctqbj4djdnb9f5c9e2d a1gjdmbtsywpp 16767805607617541771 10696951138187414235 16767805607617541771
16 rows selected.
### notice above that sql_handle SQL_f11ea740f22ca0ca has signature =17374508309824708810
12:16:45 > @sqlid_cursor
Session altered.
Elapsed: 00:00:00.05
Enter value for sqlid: dw7ujxbty2t2u
SCHEMA CHLD# EXEC# BUFFER_GETS I I I HASH_VALUE PLAN_HASH_VALUE FIRST_LOAD_TIME LAST_ACTIVE_TIME EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE SQL_PLAN_BASELINE SQL_PATCH SQL_PROFILE
--------- ----- ----- ----------- - - - ---------- --------------- ------------------- ----------------- ------------------------ ------------------------ ------------------------------ ------------------------------ --------------------
JSUN 0 1 7788436 N N Y 4091634778 489359993 2021-06-04/10:40:37 20210604-10:42:12 17374508309824708810 4779597513614508270
JSUN 2 1 181392 N N Y 4091634778 857947328 2021-06-04/10:40:37 20210604-12:08:57 17374508309824708810 4779597513614508270 SQL_PLAN_g27p783t2t86ab90e17cc
2 rows selected.
###so sql_id=dw7ujxbty2t2u is using PLAN_HASH_VALUE=857947328 through baseline =SQL_PLAN_g27p783t2t86ab90e17cc now, though the signature is different to sql_id#1's
### verify the plan in cursor is using Plan hash value: 857947328
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +NOTE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT to_char (actual_dept_ts, 'DD-Mon-YYYY HH24:MI'),
asl.sail_leg_seq, vessel_id, actual_dept_locator "Dept",
actual_arr_locator "Dest", Overheight_full_flag "OH_full",
Underheight_full_flag "UH_full", foot_full_flag "Pass_full", -- Get
vehicles loaded (SELECT SUM(NVL(load_count,0)) FROM
sailing_leg sl, sailing_event_leg sel, sailing_event se, event_counts
eca WHERE sl.sail_leg_Seq = asl.sail_leg_Seq AND
sel.sail_leg_seq = sl.sail_leg_seq AND se.sail_event_seq =
sel.sail_event_Seq AND se.dept_locator = sl.dept_locator
AND se.sched_dept_Ts = sl.sched_dept_ts AND
eca.sail_event_seq = se.sail_event_seq AND eca.CREATE_SOURCE
= 'SLOG' AND eca.gs_type_code IN (SELECT
gs_fare_code FROM TRAFFIC_GROUP_FARE tgf,
TR_GROUP_DEFAULT_CONTENT dc WHERE
tgf.traffic_group_code = dc.TRAFFIC_GROUP_CODE AND
dc.REPORT_GR
Plan hash value: 857947328
--------------------------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | |
| 5 | NESTED LOOPS | |
| 6 | TABLE ACCESS BY INDEX ROWID | SAILING_LEG |
| 7 | INDEX UNIQUE SCAN | XPK_SAILEG |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED | SAILING_EVENT |
| 9 | INDEX RANGE SCAN | XIE_SAILEV_DEPTS_ARRTS_VESSEL |
| 10 | INDEX UNIQUE SCAN | XPK_SAEVLG |
| 11 | INDEX RANGE SCAN | XPK_EVENTC |
| 12 | SORT UNIQUE | |
| 13 | UNION-ALL | |
| 14 | NESTED LOOPS SEMI | |
| 15 | INDEX SKIP SCAN | PK_TRAFFIC_GROUP_FARE |
| 16 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 17 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 18 | TABLE ACCESS BY INDEX ROWID | EVENT_COUNTS |
| 19 | TABLE ACCESS BY INDEX ROWID | VESSEL |
| 20 | INDEX UNIQUE SCAN | XPK_VESSEL |
| 21 | SORT AGGREGATE | |
| 22 | NESTED LOOPS | |
| 23 | NESTED LOOPS | |
| 24 | NESTED LOOPS | |
| 25 | NESTED LOOPS | |
| 26 | TABLE ACCESS BY INDEX ROWID | SAILING_LEG |
| 27 | INDEX UNIQUE SCAN | XPK_SAILEG |
| 28 | TABLE ACCESS BY INDEX ROWID BATCHED | SAILING_EVENT |
| 29 | INDEX RANGE SCAN | XIE_SAILEV_DEPTS_ARRTS_VESSEL |
| 30 | INDEX UNIQUE SCAN | XPK_SAEVLG |
| 31 | INDEX RANGE SCAN | XPK_EVENTC |
| 32 | SORT UNIQUE | |
| 33 | UNION-ALL | |
| 34 | NESTED LOOPS SEMI | |
| 35 | INDEX SKIP SCAN | PK_TRAFFIC_GROUP_FARE |
| 36 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 37 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 38 | TABLE ACCESS BY INDEX ROWID | EVENT_COUNTS |
| 39 | TABLE ACCESS BY INDEX ROWID | VESSEL |
| 40 | INDEX UNIQUE SCAN | XPK_VESSEL |
| 41 | SORT AGGREGATE | |
| 42 | NESTED LOOPS | |
| 43 | NESTED LOOPS | |
| 44 | NESTED LOOPS | |
| 45 | NESTED LOOPS | |
| 46 | TABLE ACCESS BY INDEX ROWID | SAILING_LEG |
| 47 | INDEX UNIQUE SCAN | XPK_SAILEG |
| 48 | TABLE ACCESS BY INDEX ROWID BATCHED | SAILING_EVENT |
| 49 | INDEX RANGE SCAN | XIE_SAILEV_DEPTS_ARRTS_VESSEL |
| 50 | INDEX UNIQUE SCAN | XPK_SAEVLG |
| 51 | INDEX RANGE SCAN | XPK_EVENTC |
| 52 | SORT UNIQUE | |
| 53 | UNION-ALL | |
| 54 | NESTED LOOPS SEMI | |
| 55 | INDEX SKIP SCAN | PK_TRAFFIC_GROUP_FARE |
| 56 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 57 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 58 | TABLE ACCESS BY INDEX ROWID | EVENT_COUNTS |
| 59 | SORT AGGREGATE | |
| 60 | NESTED LOOPS | |
| 61 | NESTED LOOPS | |
| 62 | NESTED LOOPS | |
| 63 | NESTED LOOPS | |
| 64 | TABLE ACCESS BY INDEX ROWID | SAILING_LEG |
| 65 | INDEX UNIQUE SCAN | XPK_SAILEG |
| 66 | TABLE ACCESS BY INDEX ROWID BATCHED | SAILING_EVENT |
| 67 | INDEX RANGE SCAN | XIE_SAILEV_DEPTS_ARRTS_VESSEL |
| 68 | INDEX UNIQUE SCAN | XPK_SAEVLG |
| 69 | INDEX RANGE SCAN | XPK_EVENTC |
| 70 | SORT UNIQUE | |
| 71 | UNION-ALL | |
| 72 | NESTED LOOPS SEMI | |
| 73 | INDEX SKIP SCAN | PK_TRAFFIC_GROUP_FARE |
| 74 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 75 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 76 | TABLE ACCESS BY INDEX ROWID | EVENT_COUNTS |
| 77 | SORT AGGREGATE | |
| 78 | NESTED LOOPS | |
| 79 | NESTED LOOPS | |
| 80 | NESTED LOOPS | |
| 81 | NESTED LOOPS | |
| 82 | TABLE ACCESS BY INDEX ROWID | SAILING_LEG |
| 83 | INDEX UNIQUE SCAN | XPK_SAILEG |
| 84 | TABLE ACCESS BY INDEX ROWID BATCHED | SAILING_EVENT |
| 85 | INDEX RANGE SCAN | XIE_SAILEV_DEPTS_ARRTS_VESSEL |
| 86 | INDEX UNIQUE SCAN | XPK_SAEVLG |
| 87 | INDEX RANGE SCAN | XPK_EVENTC |
| 88 | SORT UNIQUE | |
| 89 | UNION-ALL | |
| 90 | NESTED LOOPS SEMI | |
| 91 | INDEX SKIP SCAN | PK_TRAFFIC_GROUP_FARE |
| 92 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 93 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 94 | TABLE ACCESS BY INDEX ROWID | EVENT_COUNTS |
| 95 | TABLE ACCESS BY INDEX ROWID | VESSEL |
| 96 | INDEX UNIQUE SCAN | XPK_VESSEL |
| 97 | SORT ORDER BY | |
| 98 | FILTER | |
| 99 | NESTED LOOPS SEMI | |
| 100 | TABLE ACCESS BY INDEX ROWID BATCHED | ACTUAL_SAILING_LEG |
| 101 | INDEX RANGE SCAN | XIE_ACTSAL_DEPTS_ARRTS_SRCSYS |
| 102 | VIEW PUSHED PREDICATE | VW_SQ_2 |
| 103 | FILTER | |
| 104 | NESTED LOOPS | |
| 105 | NESTED LOOPS | |
| 106 | NESTED LOOPS SEMI | |
| 107 | NESTED LOOPS | |
| 108 | TABLE ACCESS BY INDEX ROWID | SAILING_LEG |
| 109 | INDEX UNIQUE SCAN | XPK_SAILEG |
| 110 | TABLE ACCESS BY INDEX ROWID BATCHED| SAILING_EVENT |
| 111 | INDEX RANGE SCAN | XIE_SAILEV_DEPTS_ARRTS_VESSEL |
| 112 | INDEX UNIQUE SCAN | XPK_SAEVLG |
| 113 | TABLE ACCESS BY INDEX ROWID BATCHED | EVENT_COUNTS |
| 114 | INDEX RANGE SCAN | XPK_EVENTC |
| 115 | VIEW | VW_NSO_1 |
| 116 | SORT UNIQUE | |
| 117 | UNION ALL PUSHED PREDICATE | |
| 118 | NESTED LOOPS SEMI | |
| 119 | INDEX SKIP SCAN | PK_TRAFFIC_GROUP_FARE |
| 120 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 121 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 122 | SORT AGGREGATE | |
| 123 | NESTED LOOPS | |
| 124 | NESTED LOOPS | |
| 125 | NESTED LOOPS | |
| 126 | NESTED LOOPS | |
| 127 | TABLE ACCESS BY INDEX ROWID | SAILING_LEG |
| 128 | INDEX UNIQUE SCAN | XPK_SAILEG |
| 129 | TABLE ACCESS BY INDEX ROWID BATCHED | SAILING_EVENT |
| 130 | INDEX RANGE SCAN | XIE_SAILEV_DEPTS_ARRTS_VESSEL |
| 131 | INDEX UNIQUE SCAN | XPK_SAEVLG |
| 132 | INDEX RANGE SCAN | XPK_EVENTC |
| 133 | SORT UNIQUE | |
| 134 | UNION-ALL | |
| 135 | NESTED LOOPS SEMI | |
| 136 | INDEX SKIP SCAN | PK_TRAFFIC_GROUP_FARE |
| 137 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 138 | INDEX UNIQUE SCAN | PK_TR_GROUP_DEF_CONTENT |
| 139 | TABLE ACCESS BY INDEX ROWID | EVENT_COUNTS |
| 140 | TABLE ACCESS BY INDEX ROWID | VESSEL |
| 141 | INDEX UNIQUE SCAN | XPK_VESSEL |
--------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline SQL_PLAN_g27p783t2t86ab90e17cc used for this statement
- 2 Sql Plan Directives used for this statement
174 rows selected.
### also verify using this script:
SELECT PLAN_TABLE_OUTPUT
FROM V$SQL s, DBA_SQL_PLAN_BASELINES b,
TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic')
) t
WHERE s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
AND b.PLAN_NAME=s.SQL_PLAN_BASELINE
AND s.SQL_ID='dw7ujxbty2t2u';
...
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_g27p783t2t86ab90e17cc Plan id: 3104708556
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 857947328
...
That's confirm!
Comments
Post a Comment