Profiles, GTT’s and how changing the underlying view can cause FTS’s

We are using a GTT, multiple times, in a view..

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;

Advertisements

SQL profiles and Dynamic Sampling

I’ve been playing some more with profiles, and I came up with a reproduciple test case.

1) Start with the OE schema in the database

2) Create a GTT table in the schema

CREATE GLOBAL TEMPORARY TABLE GTT_PRODUCTS

(PRODUCT_ID NUMBER(6),LANGUAGE_ID VARCHAR2(3 BYTE)
) ON COMMIT PRESERVE ROWS NOCACHE;

3) run this query

select * from oe.product_descriptions a,

                    oe.gtt_products b
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);

You will see the following


Plan hash value: 2790750670——————————————————————————————————
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————
| 0 | SELECT STATEMENT     |      |      | | 7 (100)| |
| 1 | NESTED LOOPS SEMI    |      | 1    | 314 | 7 (0)| 00:00:01 |
| 2 | NESTED LOOPS         |      | 1    | 312 | 5 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN |      | 1 | 29 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL    | GTT_PRODUCTS | 1 | 16 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT          |      | 1 | 13 | 2 (0)| 00:00:01 |
| 6 | VIEW                 | VW_NSO_1 | 1 | 13 | 2 (0)| 00:00:01 |
| 7 | HASH UNIQUE          |      | 1 | 23 | | |
| 8 | NESTED LOOPS         |      | 1 | 23 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL    | GTT_PRODUCTS | 1 | 16 | 2 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN  | PRD_DESC_PK | 1 | 7 | 0 (0)| |
| 11 | TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS | 1 | 283 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN  | PRD_DESC_PK | 1 | | 0 (0)| |
| 13 | VIEW PUSHED PREDICATE      | VW_NSO_2 | 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)| |
——————————————————————————————————
Predicate Information (identified by operation id):
—————————————————

10 – access(“C”.”PRODUCT_ID”=”D”.”PRODUCT_ID” AND “C”.”LANGUAGE_ID”=”D”.”LANGUAGE_ID”)
12 – access(“A”.”PRODUCT_ID”=”PRODUCT_ID” AND “A”.”LANGUAGE_ID”=”B”.”LANGUAGE_ID”)
filter(“A”.”PRODUCT_ID”=”B”.”PRODUCT_ID”)
15 – filter(“D”.”PRODUCT_ID”=”B”.”PRODUCT_ID”)
16 – access(“C”.”PRODUCT_ID”=”B”.”PRODUCT_ID” AND “C”.”LANGUAGE_ID”=”D”.”LANGUAGE_ID”)
filter(“C”.”PRODUCT_ID”=”D”.”PRODUCT_ID”)

Note
—–
– dynamic sampling used for this statement (level=2)

Now create a profile to keep this plan locked in


VAR signature NUMBER;
REM
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select * from oe.product_descriptions a,
oe.gtt_products b
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)
]’;
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'[ALL_ROWS]’,
q'[OUTLINE_LEAF(@”SEL$2AD7F9D9″)]’,
q'[PUSH_PRED(@”SEL$CC7EC59E” “VW_NSO_2″@”SEL$CC7EC59E” 5)]’,
q'[OUTLINE_LEAF(@”SEL$683B0107″)]’,
q'[OUTLINE_LEAF(@”SEL$CC7EC59E”)]’,
q'[UNNEST(@”SEL$2″)]’,
q'[UNNEST(@”SEL$3″)]’,
q'[OUTLINE(@”SEL$291F8F59″)]’,
q'[OUTLINE(@”SEL$CC7EC59E”)]’,
q'[UNNEST(@”SEL$2″)]’,
q'[UNNEST(@”SEL$3″)]’,
q'[OUTLINE(@”SEL$2″)]’,
q'[OUTLINE(@”SEL$1″)]’,
q'[OUTLINE(@”SEL$3″)]’,
q'[FULL(@”SEL$CC7EC59E” “B”@”SEL$1”)]’,
q'[NO_ACCESS(@”SEL$CC7EC59E” “VW_NSO_1″@”SEL$CC7EC59E”)]’,
q'[FULL(@”SEL$CC7EC59E” “A”@”SEL$1” “PRODUCT_DESCRIPTIONS”)]’,
q'[NO_ACCESS(@”SEL$CC7EC59E” “VW_NSO_2″@”SEL$CC7EC59E”)]’,
q'[LEADING(@”SEL$CC7EC59E” “B”@”SEL$1” “VW_NSO_1″@”SEL$CC7EC59E” “A”@”SEL$1” “VW_NSO_2″@”SEL$CC7EC59E”)]’,
q'[USE_MERGE(@”SEL$CC7EC59E” “VW_NSO_1″@”SEL$CC7EC59E”)]’,
q'[USE_NL(@”SEL$CC7EC59E” “A”@”SEL$1”)]’,
q'[USE_NL(@”SEL$CC7EC59E” “VW_NSO_2″@”SEL$CC7EC59E”)]’,
q'[FULL(@”SEL$2AD7F9D9″ “D”@”SEL$3”)]’,
q'[FULL(@”SEL$2AD7F9D9″ “C”@”SEL$3”)]’,
q'[LEADING(@”SEL$2AD7F9D9″ “D”@”SEL$3” “C”@”SEL$3”)]’,
q'[USE_NL(@”SEL$2AD7F9D9″ “C”@”SEL$3”)]’,
q'[FULL(@”SEL$683B0107″ “D”@”SEL$2”)]’,
q'[FULL(@”SEL$683B0107″ “C”@”SEL$2”)]’,
q'[LEADING(@”SEL$683B0107″ “D”@”SEL$2” “C”@”SEL$2”)]’,
q'[USE_NL(@”SEL$683B0107″ “C”@”SEL$2”)]’,
q'[USE_HASH_AGGREGATION(@”SEL$683B0107″)]’,
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_877zbrarkvw1a_2790750670’,
description => ‘coe 877zbrarkvw1a 2790750670 ‘||: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;
/

Now run the query again, and look at the cost .. WOW. Same query. 8 Million instead of 7.



Plan hash value: 2790750670

——————————————————————————————————
| Id | Operation                       | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————
| 0 | SELECT STATEMENT |            | | | 8784K(100)| |
| 1 | NESTED LOOPS SEMI |           | 231K| 69M| 8784K (1)| 29:16:50 |
| 2 | NESTED LOOPS           |           | 231K| 68M| 8088K (1)| 26:57:42 |
| 3 | MERGE JOIN CARTESIAN |    | 66M| 1845M| 241K (2)| 00:48:17 |
| 4 | TABLE ACCESS FULL | GTT_PRODUCTS | 8168 | 127K| 29 (0)| 00:00:01 |
| 5 | BUFFER SORT              |           | 8168 | 103K| 241K (2)| 00:48:17 |
| 6 | VIEW                             | VW_NSO_1 | 8168 | 103K| 30 (4)| 00:00:01 |
| 7 | HASH UNIQUE            |           | 288 | 183K| | |
| 8 | NESTED LOOPS          |           | 8168 | 183K| 30 (4)| 00:00:01 |
| 9 | TABLE ACCESS FULL | GTT_PRODUCTS | 8168 | 127K| 29 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | 7 | 0 (0)| |
| 11 | TABLE ACCESS BY INDEX ROWID| PRODUCT_DESCRIPTIONS | 1 | 283 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PRD_DESC_PK | 1 | | 0 (0)| |
| 13 | VIEW PUSHED PREDICATE | VW_NSO_2 | 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)| |
——————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

10 – access(“C”.”PRODUCT_ID”=”D”.”PRODUCT_ID” AND “C”.”LANGUAGE_ID”=”D”.”LANGUAGE_ID”)
12 – access(“A”.”PRODUCT_ID”=”PRODUCT_ID” AND “A”.”LANGUAGE_ID”=”B”.”LANGUAGE_ID”)
filter(“A”.”PRODUCT_ID”=”B”.”PRODUCT_ID”)
15 – filter(“D”.”PRODUCT_ID”=”B”.”PRODUCT_ID”)
16 – access(“C”.”PRODUCT_ID”=”B”.”PRODUCT_ID” AND “C”.”LANGUAGE_ID”=”D”.”LANGUAGE_ID”)
filter(“C”.”PRODUCT_ID”=”D”.”PRODUCT_ID”)

Note
—–
– SQL profile coe_877zbrarkvw1a_2790750670 used for this statement

How to view sql_profiles

I am using profiles for some very stubborn queries. I wanted to know what the profile really looks like.
It looks like Oracle has significantly changed the way it stores profiles with 11g.. Here is the query I am using to view what is out there for them.. Enjoy.

select name,hint,sql_text

from (
SELECT extractValue(value(h),’.’) AS hint,
od.signature
FROM sys.sqlobj$data od, sys.sqlobj$ so,
table(xmlsequence(extract(xmltype(od.comp_data),’/outline_data/hint’))) h
WHERE so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id
) a,
dba_sql_profiles b
where a.signature=b.signature

SQL profiles

I have been spending a lot of time with SQL_PROFILES, and figuring out how my profile went stale (and subsequently the performance went from .04 seconds to 100 seconds).


First I know profiles can go bad because of cardinality. Kerry Osborne just did a great blog on this subject, and he talks about how profiles built by the SQL_ADVISOR use opt_estimate.  How to lock SQL_PROFILES created by SQL_ADVISOR. Here is a snippet of what you would see


OPT_ESTIMATE(@”SEL$5DA710D3″, INDEX_FILTER, “F”@”SEL$1”, IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06)


The issues I’ve been having come from profiles created by SQLT.  SQLT, can create profiles for you from a previous SQL statement that runs properly.  The profile is named COE_{sql_id}_{plan_hash_value}. 


The first thing I noticed is that I use GTT (global temporary tables) a lot.. In fact during some of the queries that have profiles, the same GTT is called 17 times.  The GTT is primed  with only a single row and dynamic sampling is utilized (there are no stats on the table).  The second thing I noticed is that we have queries, that look simple (a join of 2 objects), but they really call views.  I see the profile is built on a query, not on the views (more later).


So where did I start ?  First I looked at the explain plan for a database that was using the plan I want.  I know that my GTT (tmp_gcc_id) has 1 row in it. 

Operation                                                                          Name                  cardinality
TABLE ACCESS FULL                                                   TMP_GCC_ID      1   

Then later in the plan

NESTED LOOPS                                                                                        179696
     MERGE JOIN CARTESIAN                                                                      8168
         TABLE ACCESS FULL                                         TMP_GCC_ID        8168
           TABLE ACCESS BY INDEX ROWID              order                           1
              INDEX RANGE SCAN                                    idx_order                  22

The  interesting thing is that the cardinality is 8168 for the GTT for the rest of Explain plan.. This dramitically inflluences the cost, and cardinality estimates throughout.  Oracle however does use the profile and keeps the plan to be what I want it to be.  Now I look at the database where the profile is causing performance issues.
Operation                                                                    Name                  cardinality
TABLE ACCESS  FULL                                       TMP_GCC_ID         1




then later in the plan

As far as ADG (active data guard), baselines can’t be created. They require a sql_tuning_set which gets created in the database.

Baselines have long way to go to make them as portable as profiles, and profiles have a long way to go to make them as stable as baselines.  I hope there is migration path from profiles to baselines, but I haven’t heard any rumblings yet.NESTED LOOPS                                                                                        238,125
     MERGE JOIN CARTESIAN                                                                      8168
         TABLE ACCESS FULL                                         TMP_GCC_ID        8168
           TABLE ACCESS FULL                                     ORDER            534,285
    

I think my problem with the profile causing a FTS on a large table is 2 fold.

GTT  — SInce I am using a GTT, with dynamic sampling, oracle is only using dynamic sampling the first time it encounters the GTT in the query (from what I can gather).. All the subsequent subselects utilizing the GTT default to a cardinality of 8168, and follow that through..
This works fine when the profile is recognized, and working.. Once the profile starts to fail, the bad cardinality drives FTS where they shouldn’t be.

PROFILES on Queries with Underlying views –  My query that I built the profile on has an underlying view that does most of the work.. The view was modified, and the order of the tables accessed was changed.  This was enough to throw off the profile.. The profile is built on the SEL$ names that oracle assigns to each section of the query.  Since the query didn’t change, the profile was used.  And since the SEL$ names no longer matched the new view, the profile couldn’t be properly utilized.  Since the profile wasn’t properly utilzed, oracle defaulted to figuring out the best plan.. GTT’s cause issues because they didn’t dynamic sample every time.  The default cardinality of the GTT (8168) was enough to cause full table scans.

The moral of the story is that profiles can go bad 2 ways.

1) if they use any sort of cardinality hint (like Kerry mentions).

2) You change an underlying object (like a view), that doesn’t change the query text.

Now the next topic is Baselines.  I tried to use baselines (to save myself all this trouble with profiles), and I had no luck.. We have multiple databases with the same schema, but different login ID’s.  In order to utilize baselines you have to create SQL_TUNING_SETS.   Creating a SQL_TUNING_SET is much more difficult than just creating a SQL_PROFILE from an existing plan.  The other issue I have had is that we already have profiles on our queries that cause us trouble.. Creating a baselines, relies on the profile.. Create a baseline, remove the profile, and the baseline fails.,. This dependency to the profile makes it very difficult to replace our current profiles.

Lastly baselines have a column called “parsing_schema_name”.. I love the idea that you can move baselines between databases (and promote them as part of your code), but we don’t have the same parsing_schema_name in all our database.. I try to import the baseline, and it fails because the parsing_schema_name doesn’t exist in the database.

Oracle on ASM

I discovered an interesting little by product of running Oracle on ASM.. Our SGA was sized a little small on a non-production box.  All of a sudden the Users started seeing some I/O errors.

Error: Selecting clients for minimum billing ORA-01115: IO error reading block
from file (block # )


ORA-01110: data file 49: ‘+DATA/orcl/data_128k_dt01.dbf’
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk

Of course I looked at the sytem logs to find out if there was the dreaded corruption.. but nothing shows up.. I look in the alert log and I see

ORA-04031: unable to allocate 3240 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(1,0)”,”FileOpenBlock”)



ERROR: error ORA-4031 caught in ASM I/O path

 
 
Hmm.. looks like Oracle not being able to get to the ASM path causes I/O error messages.

SCAN (single client access name)

If you didn’t know it, there is a new feature in 11.2 Grid called SCAN.  Here is a link to a little more information http://www.orafaq.com/node/2369 .

The reason I thought I should mention this, is that scan is a new concept you have to deal with when you upgrade/install 11.2 grid.. There is no getting around it in the installation.  It is possible to disable it however once you install it.

My suggestion on using it, is to move slowly.. There is still a lot of old clients out there that have hard time with scan, so you might find that your client won’t connect the “new way”..  It is also very persnickity,.. I’ve seen some installations where the DNS resolution (files,dns), has thrown it off too.. For a new application, you can work through issues as you get ready to deploy, but for existing apps I would recommend you plan some time in your upgrade schedule to work through issues (or plan on disabling it).

Just  a word to the wise 🙂

Concurrency and parallelism

I’ve had a lot of discussions with some very “seasoned” professions on how to handle a high level concurrency. Most of these professions point to the new features of 11gr2 and and parallization..

True 11gr2 has added a new way of handling degree of parallization.

There are some new parameters

parallel_degree_policy
PARALLEL_MIN_TIME_THRESHOLD

These control how parallism is handled.. They can be used to actually create a funnel to ensure the system isn’t flooded.

The problem is all this, is that parallelism has a price.. Take a small efficient query, and turn on these parameters.. Guess what happens when you ramp up and run 500 of the same query concurrently ? You see much lower throughput (I’ve seen as much as 10x lower throughput). Why ?? The overhead of parallel query can be quite high, and can consume more time than even CPU in your AWR report.


Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: JX - SQL statement queue 71 7,815 1.E+05 99.3 Scheduler
DB CPU 96 1.2
PX Nsq: PQ load info query 46 9 201 .1 Other
enq: RD - RAC load 90 2 20 .0 Other
PX Deq: reap credit 152,105 1 0 .0 Other

The moral of the story is, parallism is good for longer queries.. For shorter queries your milege may vary