Hi All,
We have slow runnig query problem with below queries:
QUERY 1
=======
SELECT "PC0".pxObjClass AS "pxObjClass", "PC0".PXINSNAME AS "pxInsName"
, "PC0".WORKTYPENAME AS "WorkTypeName" , "PC0".PYSTATUSWORK AS
"pyStatusWork" , "PC0".PXCREATEDATETIME AS "pxCreateDateTime",
"PC0".LINKEDREFTO as "pxInsHandle" FROM V_FORM_RELATIONSHIPS_R_1_0
"PC0" WHERE ( "PC0".LINKEDREFFROM = :1 ) AND ( "PC0".pxObjClass = :2
) ORDER BY "PC0".PXINSNAME DESC , "PC0".PXCREATEDATETIME
QUERY 2
=======
SELECT PYID AS "pyID" , PXOBJCLASS AS "pxObjClass" , PZINSKEY AS
"pzInsKey", PZINSKEY as "pxInsHandle" FROM V_WORK_R_1_0 WHERE (
PYID = :1 ) AND ( pxObjClass = :2 )
V_WORK_R_1_0 is a view which union all of two table without any where clause
V_FORM_RELATIONSHIPS_R_1_0 is another view that is made up of V_WORK_R_1_0 view and one more table.
Production DBA accepted both the SQL PROFILE created for these two queries.
FROM DBA_HIST_SQLSTATS it looks like sometime the profile getting used and sometime old plan is being used.How to ensure that correct SQLPROFILE once accepted is used?
This is 4 node RAC oracle 11.2.0.2.0 on Linux
For first query below are sql stats
col BEGIN_INTERVAL_TIME for a26
col end_INTERVAL_TIME for a26
col ROWS_PROCESSED_TOTAL for 99999
col ROWS_PROCESSED_delta for 99999
select s.begin_interval_time, s.end_interval_time , q.snap_id, q.dbid, q.sql_id, q.plan_hash_value, q.optimizer_cost, q.optimizer_mode
--,ROWS_PROCESSED_TOTAL,ROWS_PROCESSED_delta--,CPU_TIME_TOTAL,CPU_TIME_DELTA,ELAPSED_TIME_TOTAL,ELAPSED_TIME_DELTA
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.dbid = 4026476544 and q.sql_id = '1um96ykvtwrh4'
and q.snap_id = s.snap_id
and s.begin_interval_time between sysdate-2 and sysdate
order by 1,s.snap_id desc;
BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_ID DBID SQL_ID PLAN_HASH_VALUE OPTIMIZER_COST OPTIMIZER_
-------------------------- -------------------------- ---------- ---------- ------------- --------------- -------------- ----------
03-JUL-13 06.00.33.805 AM 03-JUL-13 07.00.21.439 AM 17316 4026476544 1um96ykvtwrh4 377865450 20 ALL_ROWS
.
03-JUL-13 06.00.33.843 AM 03-JUL-13 07.00.21.475 AM 17316 4026476544 1um96ykvtwrh4 377865450 20 ALL_ROWS
03-JUL-13 06.00.33.843 AM 03-JUL-13 07.00.21.475 AM 17316 4026476544 1um96ykvtwrh4 1122713586 179350 ALL_ROWS
03-JUL-13 06.00.33.843 AM 03-JUL-13 07.00.21.474 AM 17316 4026476544 1um96ykvtwrh4 377865450 20 ALL_ROWS
.
.
03-JUL-13 07.00.21.475 AM 03-JUL-13 08.00.06.051 AM 17317 4026476544 1um96ykvtwrh4 377865450 20 ALL_ROWS
03-JUL-13 08.00.06.023 AM 03-JUL-13 09.00.11.626 AM 17318 4026476544 1um96ykvtwrh4 377865450 20 ALL_ROWS
03-JUL-13 08.00.06.023 AM 03-JUL-13 09.00.11.626 AM 17318 4026476544 1um96ykvtwrh4 1122713586 179350 ALL_ROWS
03-JUL-13 08.00.06.051 AM 03-JUL-13 09.00.11.596 AM 17318 4026476544 1um96ykvtwrh4 377865450 20 ALL_ROWS
03-JUL-13 08.00.06.051 AM 03-JUL-13 09.00.11.623 AM 17318 4026476544 1um96ykvtwrh4 1122713586 179350 ALL_ROWS
03-JUL-13 08.00.06.051 AM 03-JUL-13 09.00.11.596 AM 17318 4026476544 1um96ykvtwrh4 1122713586 179350 ALL_ROWS
.
.
03-JUL-13 11.00.10.502 AM 03-JUL-13 12.00.16.034 PM 17321 4026476544 1um96ykvtwrh4 1122713586 179350 ALL_ROWS
03-JUL-13 11.00.10.502 AM 03-JUL-13 12.00.16.033 PM 17321 4026476544 1um96ykvtwrh4 1122713586 179350 ALL_ROWS
03-JUL-13 11.00.10.503 AM 03-JUL-13 12.00.16.034 PM 17321 4026476544 1um96ykvtwrh4 377865450 20 ALL_ROWS
.
.
03-JUL-13 12.00.16.034 PM 03-JUL-13 01.00.42.715 PM 17322 4026476544 1um96ykvtwrh4 377865450 ALL_ROWS
03-JUL-13 01.00.42.685 PM 03-JUL-13 02.00.10.200 PM 17323 4026476544 1um96ykvtwrh4 1122713586 179350 ALL_ROWS
03-JUL-13 01.00.42.714 PM 03-JUL-13 02.00.10.200 PM 17323 4026476544 1um96ykvtwrh4 1122713586 179350 ALL_ROWS
.
.
03-JUL-13 03.00.15.887 PM 03-JUL-13 04.00.05.570 PM 17325 4026476544 1um96ykvtwrh4 1122713586 179347 ALL_ROWS
03-JUL-13 03.00.15.887 PM 03-JUL-13 04.00.05.570 PM 17325 4026476544 1um96ykvtwrh4 377865450 30585 ALL_ROWS
03-JUL-13 03.00.15.914 PM 03-JUL-13 04.00.05.541 PM 17325 4026476544 1um96ykvtwrh4 1122713586 179347 ALL_ROWS
.
.
03-JUL-13 03.00.15.915 PM 03-JUL-13 04.00.05.570 PM 17325 4026476544 1um96ykvtwrh4 377865450 30585 ALL_ROWS
03-JUL-13 04.00.05.541 PM 03-JUL-13 05.00.09.865 PM 17326 4026476544 1um96ykvtwrh4 1122713586 179347 ALL_ROWS
.
.
03-JUL-13 05.00.09.866 PM 03-JUL-13 06.00.26.710 PM 17327 4026476544 1um96ykvtwrh4 1122713586 179347 ALL_ROWS
.
.
03-JUL-13 08.00.12.716 PM 03-JUL-13 09.00.00.960 PM 17330 4026476544 1um96ykvtwrh4 1122713586 179347 ALL_ROWS
03-JUL-13 08.00.12.716 PM 03-JUL-13 09.00.00.989 PM 17330 4026476544 1um96ykvtwrh4 1122713586 179347 ALL_ROWS
377865450 is the sql_hash of explain plan given by SQL_PROFILE
select
2 extractvalue(value(d), '/hint') as outline_hints
3 from
4 xmltable('/*/outline_data/hint'
5 passing (
6 select
7 xmltype(other_xml) as xmlval
8 from
9 dba_hist_sql_plan
10 where
11 sql_id = '&sql_id'
12 and plan_hash_value = &plan_hash_value
13 and other_xml is not null
)
14 15 ) d;
Enter value for sql_id: 1um96ykvtwrh4
old 11: sql_id = '&sql_id'
new 11: sql_id = '1um96ykvtwrh4'
Enter value for plan_hash_value: 377865450
old 12: and plan_hash_value = &plan_hash_value
new 12: and plan_hash_value = 377865450
OUTLINE_HINTS
--------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$B01C6807")
OUTLINE_LEAF(@"SEL$8E13D68A")
OUTLINE_LEAF(@"SET$5715CE2E")
PUSH_PRED(@"SEL$F5BB74E1" "VW"@"SEL$2" 3)
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE_HINTS
--------------------------------------------------------------------------------
OUTLINE(@"SEL$4")
OUTLINE(@"SET$1")
OUTLINE(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$F5BB74E1" "LR"@"SEL$2" ("LINK_RELATIONSHIP"."PXLINKEDREFFROM"
))
NO_ACCESS(@"SEL$F5BB74E1" "VW"@"SEL$2")
LEADING(@"SEL$F5BB74E1" "LR"@"SEL$2" "VW"@"SEL$2")
OUTLINE_HINTS
--------------------------------------------------------------------------------
USE_NL(@"SEL$F5BB74E1" "VW"@"SEL$2")
INDEX_RS_ASC(@"SEL$8E13D68A" "W"@"SEL$4" ("WORK_COMMON"."PZINSKEY"))
INDEX_RS_ASC(@"SEL$B01C6807" "WORK_BATCH"@"SEL$3" ("WORK_BATCH"."PZINSKEY"))
select id, operation, options, object_name, cost
from dba_hist_sql_plan
where dbid = 4026476544 and sql_id = '1um96ykvtwrh4'
and plan_hash_value = 377865450 2 3 4
5 ;
ID OPERATION OPTIONS OBJECT_NAME COST
---------- ------------------------------ ------------------------------ ------------------------- ----------
0 SELECT STATEMENT 20
1 SORT ORDER BY 20
2 FILTER
3 NESTED LOOPS 19
4 TABLE ACCESS BY INDEX ROWID LINK_RELATIONSHIP 5
5 INDEX RANGE SCAN RELATIONSHIP_REFFROM 3
6 VIEW V_WORK_R_1_0 7
7 UNION ALL PUSHED PREDICATE
8 TABLE ACCESS BY INDEX ROWID WORK_BATCH 4
9 INDEX UNIQUE SCAN WORK_BATCH_PK 3
10 TABLE ACCESS BY INDEX ROWID WORK_COMMON 3
11 INDEX UNIQUE SCAN WORK_COMMON_PK 2
EXPLAIN PLAN OF SQL_PROFILE PROVIDED
select * from table(dbms_xplan.display_awr('1um96ykvtwrh4',377865450,4026476544, 'ALL +peeked_binds +ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 1um96ykvtwrh4
--------------------
SELECT "PC0".pxObjClass AS "pxObjClass", "PC0".PXINSNAME AS "pxInsName"
, "PC0".WORKTYPENAME AS "WorkTypeName" , "PC0".PYSTATUSWORK AS
"pyStatusWork" , "PC0".PXCREATEDATETIME AS "pxCreateDateTime",
"PC0".LINKEDREFTO as "pxInsHandle" FROM V_FORM_RELATIONSHIPS_R_1_0
"PC0" WHERE ( "PC0".LINKEDREFFROM = :1 ) AND ( "PC0".pxObjClass = :2
) ORDER BY "PC0".PXINSNAME DESC , "PC0".PXCREATEDATETIME
Plan hash value: 377865450
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 20 (100)| |
| 1 | SORT ORDER BY | | 2 | 1362 | 20 (5)| 00:00:01 |
| 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 2 | 1362 | 19 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | LINK_RELATIONSHIP | 2 | 204 | 5 (0)| 00:00:01 |
| 5 | INDEX RANGE SCAN | RELATIONSHIP_REFFROM | 2 | | 3 (0)| 00:00:01 |
| 6 | VIEW | V_WORK_R_1_0 | 1 | 579 | 7 (0)| 00:00:01 |
| 7 | UNION ALL PUSHED PREDICATE | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| WORK_BATCH | 1 | 89 | 4 (0)| 00:00:01 |
| 9 | INDEX UNIQUE SCAN | WORK_BATCH_PK | 1 | | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| WORK_COMMON | 1 | 109 | 3 (0)| 00:00:01 |
| 11 | INDEX UNIQUE SCAN | WORK_COMMON_PK | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
4 - SEL$F5BB74E1 / LR@SEL$2
5 - SEL$F5BB74E1 / LR@SEL$2
6 - SET$5715CE2E / VW@SEL$2
7 - SET$5715CE2E
8 - SEL$B01C6807 / WORK_BATCH@SEL$3
9 - SEL$B01C6807 / WORK_BATCH@SEL$3
10 - SEL$8E13D68A / W@SEL$4
11 - SEL$8E13D68A / W@SEL$4
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (VARCHAR2(30), CSID=873): 'xxxx-xxx-SERVICEREQUEST-WORK-ACCTMAINT AM-13640'
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
OLD EXPLAIN PLAN IS
select * from table(dbms_xplan.display_awr('1um96ykvtwrh4',1122713586,4026476544, 'ALL +peeked_binds +ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 1um96ykvtwrh4
--------------------
SELECT "PC0".pxObjClass AS "pxObjClass", "PC0".PXINSNAME AS "pxInsName"
, "PC0".WORKTYPENAME AS "WorkTypeName" , "PC0".PYSTATUSWORK AS
"pyStatusWork" , "PC0".PXCREATEDATETIME AS "pxCreateDateTime",
"PC0".LINKEDREFTO as "pxInsHandle" FROM V_FORM_RELATIONSHIPS_R_1_0
"PC0" WHERE ( "PC0".LINKEDREFFROM = :1 ) AND ( "PC0".pxObjClass = :2
) ORDER BY "PC0".PXINSNAME DESC , "PC0".PXCREATEDATETIME
Plan hash value: 1122713586
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 171K(100)| |
| 1 | SORT ORDER BY | | 32220 | 20M| 22M| 171K (1)| 00:34:18 |
| 2 | FILTER | | | | | | |
| 3 | HASH JOIN | | 32220 | 20M| 3560K| 166K (1)| 00:33:22 |
| 4 | VIEW | index$_join$_002 | 31931 | 3180K| | 1648 (1)| 00:00:20 |
| 5 | HASH JOIN | | | | | | |
| 6 | INDEX RANGE SCAN | RELATIONSHIP_REFFROM | 31931 | 3180K| | 430 (1)| 00:00:06 |
| 7 | INDEX FAST FULL SCAN| AK1_LINK_RELATIONSHIP | 31931 | 3180K| | 1096 (1)| 00:00:14 |
| 8 | VIEW | V_WORK_R_1_0 | 3946K| 2178M| | 54733 (1)| 00:10:57 |
| 9 | UNION-ALL | | | | | | |
| 10 | TABLE ACCESS FULL | WORK_BATCH | 3910K| 331M| | 53959 (1)| 00:10:48 |
| 11 | TABLE ACCESS FULL | WORK_COMMON | 35334 | 3761K| | 774 (1)| 00:00:10 |
------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
4 - SEL$838CAA44 / LR@SEL$2
5 - SEL$838CAA44
6 - SEL$838CAA44 / indexjoin$_alias$_001@SEL$838CAA44
7 - SEL$838CAA44 / indexjoin$_alias$_002@SEL$838CAA44
8 - SET$1 / VW@SEL$2
9 - SET$1
10 - SEL$3 / WORK_BATCH@SEL$3
11 - SEL$4 / W@SEL$4
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (VARCHAR2(30), CSID=873): 'xxxxxx-xxx-SERVICEREQUEST-WORK-BD-DOCUMENTREQUEST ACE_BD_DR-166646'
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
52 rows selected.
Select sql_id,plan_hash_value,old_hash_value,child_number,OUTLINE_CATEGORY,CPU_TIME,ELAPSED_TIME,SQL_PROFILE from gv$sql where sql_id='1um96ykvtwrh4';
SQL_ID PLAN_HASH_VALUE OLD_HASH_VALUE CHILD_NUMBER OUTLINE_CA CPU_TIME ELAPSED_TIME SQL_PROFILE
------------- --------------- -------------- ------------ ---------- ---------- ------------ ------------------------------
1um96ykvtwrh4 377865450 3763442152 2 81989 198629 SYS_SQLPROF_013fa7a18f7c0001
1um96ykvtwrh4 1122713586 3763442152 0 44147289 172322738
1um96ykvtwrh4 1122713586 3763442152 1 1905933242 6101663681
1um96ykvtwrh4 377865450 3763442152 3 55988 108690 SYS_SQLPROF_013fa7a18f7c0001
FOR QUERY 2
col BEGIN_INTERVAL_TIME for a26
col end_INTERVAL_TIME for a26
select s.begin_interval_time, s.end_interval_time , q.snap_id, q.dbid, q.sql_id,q.VERSION_COUNT, q.plan_hash_value, q.optimizer_cost, q.optimizer_mode
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.dbid = 4026476544 and q.sql_id = '4prk4w6sdtxa5'
and q.snap_id = s.snap_id
and s.begin_interval_time between sysdate-2 and sysdate
order by 1,s.snap_id desc;
BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_ID DBID SQL_ID PLAN_HASH_VALUE OPTIMIZER_COST OPTIMIZER_
-------------------------- -------------------------- ---------- ---------- ------------- --------------- -------------- ----------
04-JUL-13 12.00.01.427 PM 04-JUL-13 01.00.02.453 PM 17346 4026476544 4prk4w6sdtxa5 3168544609 56710 ALL_ROWS
04-JUL-13 12.00.01.430 PM 04-JUL-13 01.00.02.413 PM 17346 4026476544 4prk4w6sdtxa5 455675889 985 ALL_ROWS
04-JUL-13 12.00.01.430 PM 04-JUL-13 01.00.02.413 PM 17346 4026476544 4prk4w6sdtxa5 3168544609 56710 ALL_ROWS
04-JUL-13 12.00.01.430 PM 04-JUL-13 01.00.02.413 PM 17346 4026476544 4prk4w6sdtxa5 455675889 985 ALL_ROWS
04-JUL-13 12.00.01.430 PM 04-JUL-13 01.00.02.413 PM 17346 4026476544 4prk4w6sdtxa5 3168544609 56710 ALL_ROWS
OLD EXPLAIN PAL
select * from table(dbms_xplan.display_awr('4prk4w6sdtxa5',3168544609,4026476544, 'ALL +peeked_binds +ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4prk4w6sdtxa5
--------------------
SELECT PYID AS "pyID" , PXOBJCLASS AS "pxObjClass" , PZINSKEY AS
"pzInsKey", PZINSKEY as "pxInsHandle" FROM V_WORK_R_1_0 WHERE (
PYID = :1 ) AND ( pxObjClass = :2 )
Plan hash value: 3168544609
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 53846 (100)| |
| 1 | VIEW | V_WORK_R_1_0 | 2 | 1238 | 53846 (1)| 00:10:47 |
| 2 | UNION-ALL | | | | | |
| 3 | FILTER | | | | | |
| 4 | TABLE ACCESS FULL | WORK_BATCH | 1 | 67 | 53844 (1)| 00:10:47 |
| 5 | FILTER | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| WORK_COMMON | 1 | 578 | 2 (0)| 00:00:01 |
| 7 | INDEX RANGE SCAN | AK1_WORK_COMMON | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1 / V_WORK_R_1_0@SEL$1
2 - SET$1
3 - SEL$2
4 - SEL$2 / WORK_BATCH@SEL$2
5 - SEL$3
6 - SEL$3 / W@SEL$3
7 - SEL$3 / W@SEL$3
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (VARCHAR2(30), CSID=873): 'CM-22180'
Note
-----
- dynamic sampling used for this statement (level=2)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
NEW EXPLAIN PLAN IS
select * from table(dbms_xplan.display_awr('4prk4w6sdtxa5',455675889,4026476544, 'ALL +peeked_binds +ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4prk4w6sdtxa5
--------------------
SELECT PYID AS "pyID" , PXOBJCLASS AS "pxObjClass" , PZINSKEY AS
"pzInsKey", PZINSKEY as "pxInsHandle" FROM V_WORK_R_1_0 WHERE (
PYID = :1 ) AND ( pxObjClass = :2 )
Plan hash value: 455675889
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 985 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 2 | 1238 | 985 (1)| 00:00:12 | Q1,01 | P->S | QC (RAND) |
| 3 | BUFFER SORT | | 2 | 1238 | | | Q1,01 | PCWP | |
| 4 | VIEW | V_WORK_R_1_0 | 2 | 1238 | 985 (1)| 00:00:12 | Q1,01 | PCWP | |
| 5 | UNION-ALL | | | | | | Q1,01 | PCWP | |
| 6 | FILTER | | | | | | Q1,01 | PCWC | |
| 7 | PX BLOCK ITERATOR | | 1 | 67 | 983 (1)| 00:00:12 | Q1,01 | PCWC | |
| 8 | TABLE ACCESS FULL | WORK_BATCH | 1 | 67 | 983 (1)| 00:00:12 | Q1,01 | PCWP | |
| 9 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 10 | PX RECEIVE | | 1 | 59 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 11 | PX SEND ROUND-ROBIN | :TQ10000 | 1 | 59 | 2 (0)| 00:00:01 | | S->P | RND-ROBIN |
| 12 | FILTER | | | | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| WORK_COMMON | 1 | 59 | 2 (0)| 00:00:01 | | | |
| 14 | INDEX RANGE SCAN | AK1_WORK_COMMON | 1 | | 1 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SET$1 / V_WORK_R_1_0@SEL$1
5 - SET$1
6 - SEL$2
8 - SEL$2 / WORK_BATCH@SEL$2
12 - SEL$3
13 - SEL$3 / W@SEL$3
14 - SEL$3 / W@SEL$3
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (VARCHAR2(30), CSID=873): 'AM-15042'
Note
-----
- automatic DOP: Computed Degree of Parallelism is 64
- SQL profile "SYS_SQLPROF_013fa79ff2ee0000" used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
53 rows selected.
HINT USED IN EXPLAIN PLAN GIVEN BY SQL_PROFILE
select
extractvalue(value(d), '/hint') as outline_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id = '&sql_id'
and plan_hash_value = &plan_hash_value
and other_xml is not null
)) d;
OUTLINE_HINTS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
OPT_PARAM('optimizer_dynamic_sampling' 5)
ALL_ROWS
SHARED(64)
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "V_WORK_R_1_0"@"SEL$1")
INDEX_RS_ASC(@"SEL$3" "W"@"SEL$3" ("WORK_COMMON"."PYID"))
FULL(@"SEL$2" "WORK_BATCH"@"SEL$2")
col OUTLINE_CATEGORY for a10
col SQL_PROFILE for a30
select sql_id,plan_hash_value,old_hash_value,child_number,OUTLINE_CATEGORY,CPU_TIME,ELAPSED_TIME,SQL_PROFILE from gv$sql where sql_id='4prk4w6sdtxa5';
SQL_ID PLAN_HASH_VALUE OLD_HASH_VALUE CHILD_NUMBER OUTLINE_CA CPU_TIME ELAPSED_TIME SQL_PROFILE
------------- --------------- -------------- ------------ ---------- ---------- ------------ ------------------------------
4prk4w6sdtxa5 3168544609 353644632 0 2633226703 4.4271E+10
4prk4w6sdtxa5 455675889 353644632 1 27225852 759830164 SYS_SQLPROF_013fa79ff2ee0000
4prk4w6sdtxa5 455675889 353644632 2 119988890 9664144040 SYS_SQLPROF_013fa79ff2ee0000
4prk4w6sdtxa5 455675889 353644632 3 85872011 5942746451 SYS_SQLPROF_013fa79ff2ee0000
4prk4w6sdtxa5 3168544609 353644632 0 335776954 337580538
4prk4w6sdtxa5 3168544609 353644632 1 399056329 401261240
4prk4w6sdtxa5 3168544609 353644632 2 484240383 486679352
4prk4w6sdtxa5 3168544609 353644632 3 401375979 492360355
4prk4w6sdtxa5 3168544609 353644632 4 930830490 2.2156E+10
4prk4w6sdtxa5 3168544609 353644632 5 403837610 472368680
4prk4w6sdtxa5 3168544609 353644632 6 1136393235 2.5397E+10
4prk4w6sdtxa5 3168544609 353644632 7 858015552 1.6636E+10
4prk4w6sdtxa5 455675889 353644632 8 68884598 6001624174 SYS_SQLPROF_013fa79ff2ee0000
4prk4w6sdtxa5 455675889 353644632 9 21730702 629077530 SYS_SQLPROF_013fa79ff2ee0000
4prk4w6sdtxa5 455675889 353644632 10 93504893 8216578447 SYS_SQLPROF_013fa79ff2ee0000
OTHER INFO
SQL> select table_name,owner,index_name,INDEX_TYPE from dba_indexes where table_name in ('WORK_BATCH','WORK_COMMON','LINK_RELATIONSHIP');
TABLE_NAME OWNER INDEX_NAME INDEX_TYPE
------------------------------ ------------------------------ ------------------------------ ---------------------------
LINK_RELATIONSHIP ACE SYS_IL0000120332C00018$$ LOB
WORK_BATCH ACE SYS_IL0000120308C00085$$ LOB
WORK_COMMON ACE SYS_IL0000243612C00085$$ LOB
LINK_RELATIONSHIP ACE LINK_RELATIONSHIP_PK NORMAL
LINK_RELATIONSHIP ACE AK1_LINK_RELATIONSHIP NORMAL
LINK_RELATIONSHIP ACE RELATIONSHIP_REFFROM NORMAL
WORK_BATCH ACE WORK_BATCH_PK NORMAL
WORK_BATCH ACE IDX1_WORK_BATCH NORMAL
WORK_COMMON ACE AK2_WORK_COMMON NORMAL
WORK_COMMON ACE AK1_WORK_COMMON NORMAL
WORK_COMMON ACE WORK_COMMON_PK NORMAL
col object_name for a25
select created,object_name,object_type,owner,LAST_DDL_TIME from dba_objects where object_name in ('WORK_BATCH','WORK_COMMON','WORK_BATCH_PK','WORK_COMMON_PK','AK1_LINK_RELATIONSHIP','RELATIONSHIP_REFFROM')
;
2
CREATED OBJECT_NAME OBJECT_TYPE OWNER LAST_DDL_TI
----------- ------------------------- ------------------- ------------------------------ -----------
28-JUN-2012 WORK_BATCH TABLE ACE 15-JUN-2013
28-JUN-2012 WORK_BATCH_PK INDEX ACE 29-MAY-2013
30-NOV-2012 RELATIONSHIP_REFFROM INDEX ACE 03-JUL-2013
23-FEB-2013 AK1_LINK_RELATIONSHIP INDEX ACE 23-FEB-2013
15-JUN-2013 WORK_COMMON TABLE ACE 15-JUN-2013
15-JUN-2013 WORK_COMMON_PK INDEX ACE 15-JUN-2013
09-MAR-2013 WORK_BATCH SYNONYM ACE_USER 09-MAR-2013
col COLUMN_NAME for a20
Select INDEX_NAME,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name in ('WORK_BATCH','WORK_COMMON','LINK_RELATIONSHIP');
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ -------------------- ---------------
RELATIONSHIP_REFFROM LINK_RELATIONSHIP PXLINKEDREFFROM 1
AK1_LINK_RELATIONSHIP LINK_RELATIONSHIP PXLINKEDREFTO 1
LINK_RELATIONSHIP_PK LINK_RELATIONSHIP PZINSKEY 1
IDX1_WORK_BATCH WORK_BATCH CAPTUREUNIQUEID 1
WORK_BATCH_PK WORK_BATCH PZINSKEY 1
WORK_COMMON_PK WORK_COMMON PZINSKEY 1
AK1_WORK_COMMON WORK_COMMON PYID 1
AK2_WORK_COMMON WORK_COMMON PXOBJCLASS 1
SQL> select distinct tablespace_name from dba_tablespaces where tablespace_name like '%INDEX%';
TABLESPACE_NAME
------------------------------