We are using a GTT, multiple times, in a view..
1) Just the query
2) with a profile (built from the query), note it is the same exact plan with a HUGE cost.
3) With a profile (built from the query), and a change to the view
*************************************************************\
4) Now with a Baseline
5) Now with a baseline built on the profile (then the profile is dropped).
Now the code snippet to make it all happen
set pagesize 0
set linesize 120
spool new_view.log
select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds'));
spool off;
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '7nsv5y0mnnq5m');
END;
/
alter system flush shared_pool;
select * from oe.profile_test;
set pagesize 0
set linesize 120
spool baseline.log
select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds'));
spool off;
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => 'SYS_SQL_d8d4a1b955747aea');
END;
/
VAR signature NUMBER;
REM
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select * from oe.profile_test
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
q'[DB_VERSION('11.2.0.1')]',
q'[OPT_PARAM('optimizer_dynamic_sampling' 4)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$13AC9F9F")]',
q'[PUSH_PRED(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65" 5)]',
q'[OUTLINE_LEAF(@"SEL$24A0192D")]',
q'[PUSH_PRED(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65" 4)]',
q'[OUTLINE_LEAF(@"SEL$1C4CCF65")]',
q'[UNNEST(@"SEL$3")]',
q'[UNNEST(@"SEL$5")]',
q'[UNNEST(@"SEL$6")]',
q'[OUTLINE(@"SEL$CE1D94FA")]',
q'[OUTLINE(@"SEL$1C4CCF65")]',
q'[UNNEST(@"SEL$3")]',
q'[UNNEST(@"SEL$5")]',
q'[UNNEST(@"SEL$6")]',
q'[OUTLINE(@"SEL$F495C89B")]',
q'[OUTLINE(@"SEL$110C15E8")]',
q'[MERGE(@"SEL$5BF935F8")]',
q'[OUTLINE(@"SEL$3")]',
q'[OUTLINE(@"SEL$5")]',
q'[OUTLINE(@"SEL$6")]',
q'[OUTLINE(@"SEL$1")]',
q'[OUTLINE(@"SEL$5BF935F8")]',
q'[MERGE(@"SEL$2")]',
q'[OUTLINE(@"SEL$4")]',
q'[OUTLINE(@"SEL$2")]',
q'[FULL(@"SEL$1C4CCF65" "B"@"SEL$2")]',
q'[INDEX_RS_ASC(@"SEL$1C4CCF65" "A"@"SEL$4" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[NO_ACCESS(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65")]',
q'[NO_ACCESS(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65")]',
q'[FULL(@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3")]',
q'[INDEX(@"SEL$1C4CCF65" "E"@"SEL$2" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$1C4CCF65" "B"@"SEL$2" "A"@"SEL$4" "VW_NSO_2"@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3" "E"@"SEL$2")]',
q'[USE_NL(@"SEL$1C4CCF65" "A"@"SEL$4")]',
q'[USE_NL(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65")]',
q'[USE_NL(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65")]',
q'[USE_MERGE(@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3")]',
q'[USE_NL(@"SEL$1C4CCF65" "E"@"SEL$2")]',
q'[FULL(@"SEL$13AC9F9F" "D"@"SEL$6")]',
q'[INDEX(@"SEL$13AC9F9F" "C"@"SEL$6" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$13AC9F9F" "D"@"SEL$6" "C"@"SEL$6")]',
q'[USE_NL(@"SEL$13AC9F9F" "C"@"SEL$6")]',
q'[FULL(@"SEL$24A0192D" "D"@"SEL$5")]',
q'[INDEX(@"SEL$24A0192D" "C"@"SEL$5" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$24A0192D" "D"@"SEL$5" "C"@"SEL$5")]',
q'[USE_NL(@"SEL$24A0192D" "C"@"SEL$5")]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_7nsv5y0mnnq5m_1263200402',
description => 'coe 7nsv5y0mnnq5m 1263200402 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
alter system flush shared_pool;
select * from oe.profile_test;
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '7nsv5y0mnnq5m');
END;
/
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_7nsv5y0mnnq5m_1263200402');
END;
/
alter system flush shared_pool;
select * from oe.profile_test;
set pagesize 0
set linesize 120
spool baseline_on_dropped_profile.log
select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds'));
spool off;
1) Just the query
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | NESTED LOOPS | | 30 | 9600 | 10 (0)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN | | 1 | 316 | 9 (0)| 00:00:01 |
| 3 | NESTED LOOPS SEMI | | 1 | 303 | 7 (0)| 00:00:01 |
| 4 | NESTED LOOPS SEMI | | 1 | 301 | 5 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 299 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | GTT_PRODUCTS | 1 | 16 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS | 1 | 283 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | | 0 (0)| |
| 9 | VIEW PUSHED PREDICATE | VW_NSO_2 | 1 | 2 | 2 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 23 | 2 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | GTT_PRODUCTS | 1 | 16 | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | 7 | 0 (0)| |
| 13 | VIEW PUSHED PREDICATE | VW_NSO_1 | 1 | 2 | 2 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 23 | 2 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | GTT_PRODUCTS | 1 | 16 | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | 7 | 0 (0)| |
| 17 | BUFFER SORT | | 1 | 13 | 7 (0)| 00:00:01 |
| 18 | SORT UNIQUE | | 1 | 13 | 2 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | GTT_PRODUCTS | 1 | 13 | 2 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PRD_DESC_PK | 30 | 120 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
2) with a profile (built from the query), note it is the same exact plan with a HUGE cost.
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 67M(100)| |
| 1 | NESTED LOOPS | | 2001M| 596G| 67M (1)|223:23:27 |
| 2 | MERGE JOIN CARTESIAN | | 66M| 19G| 271K (1)| 00:54:21 |
| 3 | NESTED LOOPS SEMI | | 8168 | 2416K| 50046 (1)| 00:10:01 |
| 4 | NESTED LOOPS SEMI | | 8168 | 2400K| 25518 (1)| 00:05:07 |
| 5 | NESTED LOOPS | | 8168 | 2384K| 990 (1)| 00:00:12 |
| 6 | TABLE ACCESS FULL | GTT_PRODUCTS | 8168 | 127K| 29 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS | 1 | 283 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | | 0 (0)| |
| 9 | VIEW PUSHED PREDICATE | VW_NSO_2 | 1 | 2 | 3 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 23 | 3 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | GTT_PRODUCTS | 3 | 48 | 3 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | 7 | 0 (0)| |
| 13 | VIEW PUSHED PREDICATE | VW_NSO_1 | 1 | 2 | 3 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 23 | 3 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | GTT_PRODUCTS | 3 | 48 | 3 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | 7 | 0 (0)| |
| 17 | BUFFER SORT | | 8168 | 103K| 271K (1)| 00:54:21 |
| 18 | SORT UNIQUE | | 8168 | 103K| 27 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | GTT_PRODUCTS | 8168 | 103K| 27 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PRD_DESC_PK | 30 | 120 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
3) With a profile (built from the query), and a change to the view
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 72496 (100)| |
| 1 | NESTED LOOPS | | 245K| 483M| | 72496 (1)| 00:14:30 |
| 2 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 3 | VIEW | | 245K| 483M| | 72494 (1)| 00:14:30 |
|* 4 | HASH JOIN | | 245K| 75M| 2712K| 72494 (1)| 00:14:30 |
|* 5 | HASH JOIN SEMI | | 8640 | 2607K| 2600K| 271 (1)| 00:00:04 |
| 6 | MERGE JOIN CARTESIAN | | 8640 | 2497K| | 105 (1)| 00:00:02 |
| 7 | VIEW | VW_NSO_2 | 1 | 13 | | 2 (0)| 00:00:01 |
| 8 | SORT UNIQUE | | 1 | 23 | | | |
| 9 | NESTED LOOPS | | 1 | 23 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | GTT_PRODUCTS | 1 | 16 | | 2 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | 7 | | 0 (0)| |
| 12 | BUFFER SORT | | 8640 | 2387K| | 105 (1)| 00:00:02 |
| 13 | TABLE ACCESS FULL | PRODUCT_DESCRIPTIONS | 8640 | 2387K| | 102 (0)| 00:00:02 |
| 14 | VIEW | VW_NSO_1 | 8168 | 103K| | 30 (4)| 00:00:01 |
| 15 | NESTED LOOPS | | 8168 | 183K| | 30 (4)| 00:00:01 |
| 16 | TABLE ACCESS FULL | GTT_PRODUCTS | 8168 | 127K| | 29 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | 7 | | 0 (0)| |
| 18 | VIEW | | 245K| 3828K| | 71765 (1)| 00:14:22 |
|* 19 | FILTER | | | | | | |
| 20 | MERGE JOIN CARTESIAN | | 70M| 1346M| | 71188 (1)| 00:14:15 |
| 21 | TABLE ACCESS FULL | GTT_PRODUCTS | 8168 | 127K| | 29 (0)| 00:00:01 |
| 22 | BUFFER SORT | | 8640 | 34560 | | 71159 (1)| 00:14:14 |
| 23 | INDEX FAST FULL SCAN| PRD_DESC_PK | 8640 | 34560 | | 9 (0)| 00:00:01 |
|* 24 | TABLE ACCESS FULL | GTT_PRODUCTS | 1 | 13 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
*************************************************************\
4) Now with a Baseline
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | NESTED LOOPS | | 30 | 9600 | 10 (0)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN | | 1 | 316 | 9 (0)| 00:00:01 |
| 3 | NESTED LOOPS SEMI | | 1 | 303 | 7 (0)| 00:00:01 |
| 4 | NESTED LOOPS SEMI | | 1 | 301 | 5 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 299 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | GTT_PRODUCTS | 1 | 16 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS | 1 | 283 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | | 0 (0)| |
| 9 | VIEW PUSHED PREDICATE | VW_NSO_2 | 1 | 2 | 2 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 23 | 2 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | GTT_PRODUCTS | 1 | 16 | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | 7 | 0 (0)| |
| 13 | VIEW PUSHED PREDICATE | VW_NSO_1 | 1 | 2 | 2 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 23 | 2 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | GTT_PRODUCTS | 1 | 16 | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | 7 | 0 (0)| |
| 17 | BUFFER SORT | | 1 | 13 | 7 (0)| 00:00:01 |
| 18 | SORT UNIQUE | | 1 | 13 | 2 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | GTT_PRODUCTS | 1 | 13 | 2 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PRD_DESC_PK | 30 | 120 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------- 5) Now with a baseline built on the profile (then the profile is dropped).
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 67M(100)| |
| 1 | NESTED LOOPS | | 2001M| 590G| 67M (1)|223:23:27 |
| 2 | MERGE JOIN CARTESIAN | | 66M| 19G| 271K (1)| 00:54:21 |
| 3 | NESTED LOOPS SEMI | | 8168 | 2400K| 50046 (1)| 00:10:01 |
| 4 | NESTED LOOPS SEMI | | 8168 | 2384K| 25518 (1)| 00:05:07 |
| 5 | NESTED LOOPS | | 8168 | 2369K| 990 (1)| 00:00:12 |
| 6 | TABLE ACCESS FULL | GTT_PRODUCTS | 8168 | 127K| 29 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS | 1 | 281 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | | 0 (0)| |
| 9 | VIEW PUSHED PREDICATE | VW_NSO_2 | 1 | 2 | 3 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 21 | 3 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | GTT_PRODUCTS | 3 | 48 | 3 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | 5 | 0 (0)| |
| 13 | VIEW PUSHED PREDICATE | VW_NSO_1 | 1 | 2 | 3 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 21 | 3 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | GTT_PRODUCTS | 3 | 48 | 3 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | 5 | 0 (0)| |
| 17 | BUFFER SORT | | 8168 | 103K| 271K (1)| 00:54:21 |
| 18 | SORT UNIQUE | | 8168 | 103K| 27 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | GTT_PRODUCTS | 8168 | 103K| 27 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PRD_DESC_PK | 30 | 90 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("A"."PRODUCT_ID"="B"."PRODUCT_ID" AND "A"."LANGUAGE_ID"="B"."LANGUAGE_ID")
11 - filter("D"."PRODUCT_ID"="B"."PRODUCT_ID")
12 - access("C"."PRODUCT_ID"="B"."PRODUCT_ID" AND "C"."LANGUAGE_ID"="D"."LANGUAGE_ID")
filter("C"."PRODUCT_ID"="D"."PRODUCT_ID")
15 - filter("D"."PRODUCT_ID"="A"."PRODUCT_ID")
16 - access("C"."PRODUCT_ID"="A"."PRODUCT_ID" AND "C"."LANGUAGE_ID"="D"."LANGUAGE_ID")
filter("C"."PRODUCT_ID"="D"."PRODUCT_ID")
20 - access("E"."PRODUCT_ID"="PRODUCT_ID")
Note
-----
- SQL plan baseline SQL_PLAN_djp51r5ar8yracbfabd3a used for this statement
Now the code snippet to make it all happen
drop TABLE oe.GTT_PRODUCTS;
CREATE GLOBAL TEMPORARY TABLE oe.GTT_PRODUCTS
(
PRODUCT_ID NUMBER(6),
LANGUAGE_ID VARCHAR2(3 BYTE)
)
ON COMMIT PRESERVE ROWS
NOCACHE;
create or replace view oe.profile_test as
select a.* from oe.product_descriptions a,
oe.gtt_products b
join oe.product_descriptions e on (e.product_id in (select product_id from oe.gtt_products))
where a.product_id=b.product_id
and a.language_id=b.language_id
and a.product_id in
(select c.product_id from oe.product_descriptions c,
oe.gtt_products d
where c.product_id=d.product_id
and c.language_id=d.language_id)
and b.product_id in
(select c.product_id from oe.product_descriptions c,
oe.gtt_products d
where c.product_id=d.product_id
and c.language_id=d.language_id);
insert into oe.gtt_products values (2449,'RU');
select * from oe.profile_test;
set pagesize 0
set linesize 120
spool no_profile.log
select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds'));
spool off;
VAR signature NUMBER;
REM
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select * from oe.profile_test
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
q'[DB_VERSION('11.2.0.1')]',
q'[OPT_PARAM('optimizer_dynamic_sampling' 4)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$13AC9F9F")]',
q'[PUSH_PRED(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65" 5)]',
q'[OUTLINE_LEAF(@"SEL$24A0192D")]',
q'[PUSH_PRED(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65" 4)]',
q'[OUTLINE_LEAF(@"SEL$1C4CCF65")]',
q'[UNNEST(@"SEL$3")]',
q'[UNNEST(@"SEL$5")]',
q'[UNNEST(@"SEL$6")]',
q'[OUTLINE(@"SEL$CE1D94FA")]',
q'[OUTLINE(@"SEL$1C4CCF65")]',
q'[UNNEST(@"SEL$3")]',
q'[UNNEST(@"SEL$5")]',
q'[UNNEST(@"SEL$6")]',
q'[OUTLINE(@"SEL$F495C89B")]',
q'[OUTLINE(@"SEL$110C15E8")]',
q'[MERGE(@"SEL$5BF935F8")]',
q'[OUTLINE(@"SEL$3")]',
q'[OUTLINE(@"SEL$5")]',
q'[OUTLINE(@"SEL$6")]',
q'[OUTLINE(@"SEL$1")]',
q'[OUTLINE(@"SEL$5BF935F8")]',
q'[MERGE(@"SEL$2")]',
q'[OUTLINE(@"SEL$4")]',
q'[OUTLINE(@"SEL$2")]',
q'[FULL(@"SEL$1C4CCF65" "B"@"SEL$2")]',
q'[INDEX_RS_ASC(@"SEL$1C4CCF65" "A"@"SEL$4" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[NO_ACCESS(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65")]',
q'[NO_ACCESS(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65")]',
q'[FULL(@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3")]',
q'[INDEX(@"SEL$1C4CCF65" "E"@"SEL$2" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$1C4CCF65" "B"@"SEL$2" "A"@"SEL$4" "VW_NSO_2"@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3" "E"@"SEL$2")]',
q'[USE_NL(@"SEL$1C4CCF65" "A"@"SEL$4")]',
q'[USE_NL(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65")]',
q'[USE_NL(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65")]',
q'[USE_MERGE(@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3")]',
q'[USE_NL(@"SEL$1C4CCF65" "E"@"SEL$2")]',
q'[FULL(@"SEL$13AC9F9F" "D"@"SEL$6")]',
q'[INDEX(@"SEL$13AC9F9F" "C"@"SEL$6" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$13AC9F9F" "D"@"SEL$6" "C"@"SEL$6")]',
q'[USE_NL(@"SEL$13AC9F9F" "C"@"SEL$6")]',
q'[FULL(@"SEL$24A0192D" "D"@"SEL$5")]',
q'[INDEX(@"SEL$24A0192D" "C"@"SEL$5" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$24A0192D" "D"@"SEL$5" "C"@"SEL$5")]',
q'[USE_NL(@"SEL$24A0192D" "C"@"SEL$5")]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_7nsv5y0mnnq5m_1263200402',
description => 'coe 7nsv5y0mnnq5m 1263200402 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
alter system flush shared_pool;
select * from oe.profile_test;
set pagesize 0
set linesize 120
spool profile.log
select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds'));
spool off;
create or replace view oe.profile_test as
select q.* from
(
select a.* from oe.product_descriptions a,
oe.gtt_products b
join oe.product_descriptions e on (e.product_id in (select product_id from oe.gtt_products))
where a.product_id=b.product_id
and a.language_id=b.language_id
and a.product_id in
(select c.product_id from oe.product_descriptions c,
oe.gtt_products d
where c.product_id=d.product_id
and c.language_id=d.language_id)
and b.product_id in
(select c.product_id from oe.product_descriptions c,
oe.gtt_products d
where c.product_id=d.product_id
and c.language_id=d.language_id)) q
,sys.dual r;
alter system flush shared_pool;
select * from oe.profile_test;
set pagesize 0
set linesize 120
spool new_view.log
select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds'));
spool off;
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_7nsv5y0mnnq5m_1263200402');
END;
/select * from oe.profile_test;
set pagesize 0
set linesize 120
spool new_view.log
select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds'));
spool off;
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '7nsv5y0mnnq5m');
END;
/
alter system flush shared_pool;
select * from oe.profile_test;
set pagesize 0
set linesize 120
spool baseline.log
select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds'));
spool off;
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => 'SYS_SQL_d8d4a1b955747aea');
END;
/
VAR signature NUMBER;
REM
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select * from oe.profile_test
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]',
q'[DB_VERSION('11.2.0.1')]',
q'[OPT_PARAM('optimizer_dynamic_sampling' 4)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$13AC9F9F")]',
q'[PUSH_PRED(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65" 5)]',
q'[OUTLINE_LEAF(@"SEL$24A0192D")]',
q'[PUSH_PRED(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65" 4)]',
q'[OUTLINE_LEAF(@"SEL$1C4CCF65")]',
q'[UNNEST(@"SEL$3")]',
q'[UNNEST(@"SEL$5")]',
q'[UNNEST(@"SEL$6")]',
q'[OUTLINE(@"SEL$CE1D94FA")]',
q'[OUTLINE(@"SEL$1C4CCF65")]',
q'[UNNEST(@"SEL$3")]',
q'[UNNEST(@"SEL$5")]',
q'[UNNEST(@"SEL$6")]',
q'[OUTLINE(@"SEL$F495C89B")]',
q'[OUTLINE(@"SEL$110C15E8")]',
q'[MERGE(@"SEL$5BF935F8")]',
q'[OUTLINE(@"SEL$3")]',
q'[OUTLINE(@"SEL$5")]',
q'[OUTLINE(@"SEL$6")]',
q'[OUTLINE(@"SEL$1")]',
q'[OUTLINE(@"SEL$5BF935F8")]',
q'[MERGE(@"SEL$2")]',
q'[OUTLINE(@"SEL$4")]',
q'[OUTLINE(@"SEL$2")]',
q'[FULL(@"SEL$1C4CCF65" "B"@"SEL$2")]',
q'[INDEX_RS_ASC(@"SEL$1C4CCF65" "A"@"SEL$4" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[NO_ACCESS(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65")]',
q'[NO_ACCESS(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65")]',
q'[FULL(@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3")]',
q'[INDEX(@"SEL$1C4CCF65" "E"@"SEL$2" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$1C4CCF65" "B"@"SEL$2" "A"@"SEL$4" "VW_NSO_2"@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3" "E"@"SEL$2")]',
q'[USE_NL(@"SEL$1C4CCF65" "A"@"SEL$4")]',
q'[USE_NL(@"SEL$1C4CCF65" "VW_NSO_2"@"SEL$1C4CCF65")]',
q'[USE_NL(@"SEL$1C4CCF65" "VW_NSO_1"@"SEL$1C4CCF65")]',
q'[USE_MERGE(@"SEL$1C4CCF65" "GTT_PRODUCTS"@"SEL$3")]',
q'[USE_NL(@"SEL$1C4CCF65" "E"@"SEL$2")]',
q'[FULL(@"SEL$13AC9F9F" "D"@"SEL$6")]',
q'[INDEX(@"SEL$13AC9F9F" "C"@"SEL$6" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$13AC9F9F" "D"@"SEL$6" "C"@"SEL$6")]',
q'[USE_NL(@"SEL$13AC9F9F" "C"@"SEL$6")]',
q'[FULL(@"SEL$24A0192D" "D"@"SEL$5")]',
q'[INDEX(@"SEL$24A0192D" "C"@"SEL$5" ("PRODUCT_DESCRIPTIONS"."PRODUCT_ID" "PRODUCT_DESCRIPTIONS"."LANGUAGE_ID"))]',
q'[LEADING(@"SEL$24A0192D" "D"@"SEL$5" "C"@"SEL$5")]',
q'[USE_NL(@"SEL$24A0192D" "C"@"SEL$5")]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_7nsv5y0mnnq5m_1263200402',
description => 'coe 7nsv5y0mnnq5m 1263200402 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
alter system flush shared_pool;
select * from oe.profile_test;
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '7nsv5y0mnnq5m');
END;
/
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_7nsv5y0mnnq5m_1263200402');
END;
/
alter system flush shared_pool;
select * from oe.profile_test;
set pagesize 0
set linesize 120
spool baseline_on_dropped_profile.log
select * from table(dbms_xplan.display_cursor('7nsv5y0mnnq5m',null,'typical +peeked_binds'));
spool off;
No comments:
Post a Comment