Home » RDBMS Server » Performance Tuning » Explain Plan Analysis - Execution ordering (Oralce 11g, CentOS5)
| Explain Plan Analysis - Execution ordering [message #466817] |
Wed, 21 July 2010 05:09  |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
Hi,
I have two Oracle instances that are setup identically.
When I run a query on one of them, it takes around 3 seconds, on the other it takes around 200 seconds.
I have looked at the explain plans, and it has shown me what I think is the problem. On one instance, it does a join on two tables, then runs the other filter/access predicates. On the other instance it runs the filter/access predicated first, then does the expensice join. The one that does the join first is the one that takes around 200 seconds. Does anyone know of how to tell Oracle to make this join after runnning the other predicates?
Thanks,
Vackar
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Explain Plan Analysis - Execution ordering [message #466859 is a reply to message #466817] |
Wed, 21 July 2010 09:22  |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
The query I am running is
Select Distinct tag0.tag_Value
,tag1.tag_Value
,tag2.tag_Value
, count(r.id) From TAGGER.TAGGABLE_RESOURCE r
, TAGGER.TAG tag0
, TAGGER.TAG_TYPE type0
, TAGGER.TAG tag1
, TAGGER.TAG_TYPE type1
, TAGGER.TAG tag2
, TAGGER.TAG_TYPE type2
where 1=1
AND r.ID = tag0.TAGGABLE_RESOURCE_ID
AND tag0.TAG_TYPE = type0.ID
AND UPPER(type0.TERM) = UPPER('Institution Name')
AND r.ID = tag1.TAGGABLE_RESOURCE_ID
AND tag1.TAG_TYPE = type1.ID
AND UPPER(type1.TERM) = UPPER('Study Description')
AND r.ID = tag2.TAGGABLE_RESOURCE_ID
AND tag2.TAG_TYPE = type2.ID
AND UPPER(type2.TERM) = UPPER('Protocol Name')
AND r.id in
(
Select distinct r.id From TAGGER.TAGGABLE_RESOURCE r
, TAGGER.TAG tag0
, TAGGER.TAG_TYPE type0
, TAGGER.TAG tag1
, TAGGER.TAG_TYPE type1
, TAGGER.TAG tag2
, TAGGER.TAG_TYPE type2
where 1=1
AND r.ID = tag0.TAGGABLE_RESOURCE_ID
AND tag0.TAG_TYPE = type0.ID
AND UPPER(type0.TERM) = UPPER('Patient Id')
AND tag0.TAG_VALUE = 'P001'
AND r.ID = tag1.TAGGABLE_RESOURCE_ID
AND tag1.TAG_TYPE = type1.ID
AND UPPER(type1.TERM) = UPPER('Patients Sex')
AND tag1.TAG_VALUE = 'M'
AND r.ID = tag2.TAGGABLE_RESOURCE_ID
AND tag2.TAG_TYPE = type2.ID
AND UPPER(type2.TERM) = UPPER('Patients Birth Date')
AND tag2.TAG_VALUE = '123456'
)
AND r.id in
(
Select distinct r.id From TAGGER.TAGGABLE_RESOURCE r
, TAGGER.TAG tag0
, TAGGER.TAG_TYPE type0
where 1=1
AND r.ID = tag0.TAGGABLE_RESOURCE_ID
AND tag0.TAG_TYPE = type0.ID
AND UPPER(type0.TERM) = UPPER('Project')
AND tag0.TAG_VALUE = 'WHMSB_AU_096'
)
AND REGEXP_SUBSTR(r.URL, '[^/]+',1,1) IN
('CVMD_AU_007','CVMD_DU_014','CVMD_EU_008','CVMD_EU_016','CVMD_GU_006','CVMD_DU_093','CVMD_DU_097','INF_DU_028','INF_AU_108','INF_AU_124','INF_DU_106','INF_GU_090','NS_AU_044','NS_EU_082','NS_GU_039','ONC_AU_061','ONC_DU_065','ONC_DU_067','WHMSB_AU_075','WHMSB_GU_080','WHMSB_AU_096','WHMSB_AU_118','CXR','INF_GU_022','Z_TEST_PROJ','Z_TEST_PROJ_RESTRICTED','LIMS','GRSA_INF_004')
Group By
tag0.tag_Value
,tag1.tag_Value
,tag2.tag_Value
Order By
tag0.tag_Value
,tag1.tag_Value
,tag2.tag_Value
The explain plan for the slow db is
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2173812249
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 48 (5)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 39 | 48 (5)| 00:00:01 |
| 2 | VIEW | VM_NWVW_2 | 1 | 39 | 48 (5)| 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 390 | 48 (5)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 390 | 40 (3)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 363 | 39 (3)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 336 | 38 (3)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 309 | 37 (3)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 278 | 35 (3)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 247 | 33 (4)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 216 | 28 (4)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 194 | 27 (4)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 172 | 26 (4)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 86 | 25 (4)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 66 | 20 (5)| 00:00:01 |
|* 15 | HASH JOIN | | 1 | 44 | 19 (6)| 00:00:01 |
| 16 | NESTED LOOPS | | 12 | 288 | 9 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | TAG | 12 | 240 | 9 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | IDX_TAG_VAL | 12 | | 3 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | TABLE1_PK | 1 | 4 | 0 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | TAG | 12 | 240 | 9 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | IDX_TAG_VAL | 12 | | 3 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | TAG_TYPE | 1 | 22 | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | TAG_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | TAG | 1 | 20 | 5 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | IDX_FK_TAG_RES_TYPE | 35 | | 2 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID | TAGGABLE_RESOURCE | 1 | 86 | 1 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | TABLE1_PK | 1 | | 0 (0)| 00:00:01 |
| 28 | NESTED LOOPS | | | | | |
| 29 | NESTED LOOPS | | 1 | 46 | 7 (0)| 00:00:01 |
| 30 | NESTED LOOPS | | 1 | 24 | 6 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | TABLE1_PK | 1 | 4 | 1 (0)| 00:00:01 |
|* 32 | TABLE ACCESS BY INDEX ROWID| TAG | 1 | 20 | 5 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | IDX_FK_TAG_RES_TYPE | 35 | | 2 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | TAG_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
|* 35 | TABLE ACCESS BY INDEX ROWID | TAG_TYPE | 1 | 22 | 1 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID | TAG_TYPE | 1 | 22 | 1 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | TAG_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
|* 38 | TABLE ACCESS BY INDEX ROWID | TAG_TYPE | 1 | 22 | 1 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | TAG_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
| 40 | TABLE ACCESS BY INDEX ROWID | TAG | 35 | 1085 | 5 (0)| 00:00:01 |
|* 41 | INDEX RANGE SCAN | IDX_FK_TAG_RES_TYPE | 35 | | 2 (0)| 00:00:01 |
| 42 | TABLE ACCESS BY INDEX ROWID | TAG | 35 | 1085 | 2 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | IDX_FK_TAG_RES_TYPE | 35 | | 2 (0)| 00:00:01 |
| 44 | TABLE ACCESS BY INDEX ROWID | TAG | 35 | 1085 | 2 (0)| 00:00:01 |
|* 45 | INDEX RANGE SCAN | IDX_FK_TAG_RES_TYPE | 35 | | 2 (0)| 00:00:01 |
|* 46 | TABLE ACCESS BY INDEX ROWID | TAG_TYPE | 1 | 27 | 1 (0)| 00:00:01 |
|* 47 | INDEX UNIQUE SCAN | TAG_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
|* 48 | TABLE ACCESS BY INDEX ROWID | TAG_TYPE | 1 | 27 | 1 (0)| 00:00:01 |
|* 49 | INDEX UNIQUE SCAN | TAG_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
|* 50 | TABLE ACCESS BY INDEX ROWID | TAG_TYPE | 1 | 27 | 1 (0)| 00:00:01 |
|* 51 | INDEX UNIQUE SCAN | TAG_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
15 - access("R"."ID"="TAG2"."TAGGABLE_RESOURCE_ID")
18 - access("TAG1"."TAG_VALUE"='M')
19 - access("R"."ID"="TAG1"."TAGGABLE_RESOURCE_ID")
21 - access("TAG2"."TAG_VALUE"='123456')
22 - filter(UPPER("TYPE2"."TERM")='PATIENTS BIRTH DATE')
23 - access("TAG2"."TAG_TYPE"="TYPE2"."ID")
24 - filter("TAG0"."TAG_VALUE"='P001')
25 - access("R"."ID"="TAG0"."TAGGABLE_RESOURCE_ID")
26 - filter( REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='CVMD_AU_007' OR REGEXP_SUBSTR
("R"."URL",'[^/]+',1,1)='CVMD_DU_014' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='CVMD_EU_008' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='CVMD_EU_016' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='CVMD_GU_006'
OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='CVMD_DU_093' OR REGEXP_SUBSTR
("R"."URL",'[^/]+',1,1)='CVMD_DU_097' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='INF_DU_028' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='INF_AU_108' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='INF_AU_124'
OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='INF_DU_106' OR REGEXP_SUBSTR
("R"."URL",'[^/]+',1,1)='INF_GU_090' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='NS_AU_044' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='NS_EU_082' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='NS_GU_039' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='ONC_AU_061' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='ONC_DU_065'
OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='ONC_DU_067' OR REGEXP_SUBSTR
("R"."URL",'[^/]+',1,1)='WHMSB_AU_075' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='WHMSB_GU_080' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='WHMSB_AU_096' OR REGEXP_SUBSTR
("R"."URL",'[^/]+',1,1)='WHMSB_AU_118' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='CXR' OR REGEXP_SUBSTR
("R"."URL",'[^/]+',1,1)='INF_GU_022' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='Z_TEST_PROJ' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='Z_TEST_PROJ_RESTRICTED' OR REGEXP_SUBSTR
("R"."URL",'[^/]+',1,1)='LIMS' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='GRSA_INF_004')
27 - access("R"."ID"="R"."ID")
filter( EXISTS (SELECT 0 FROM "TAGGER"."TAG_TYPE" "TYPE0","TAGGER"."TAG"
"TAG0","TAGGER"."TAGGABLE_RESOURCE" "R" WHERE "R"."ID"=:B1 AND "TAG0"."TAGGABLE_RESOURCE_ID"=:B2 AND
"TAG0"."TAG_VALUE"='WHMSB_AU_096' AND "TAG0"."TAG_TYPE"="TYPE0"."ID" AND UPPER("TYPE0"."TERM")='PROJECT'))
31 - access("R"."ID"=:B1)
32 - filter("TAG0"."TAG_VALUE"='WHMSB_AU_096')
33 - access("TAG0"."TAGGABLE_RESOURCE_ID"=:B1)
34 - access("TAG0"."TAG_TYPE"="TYPE0"."ID")
35 - filter(UPPER("TYPE0"."TERM")='PROJECT')
36 - filter(UPPER("TYPE1"."TERM")='PATIENTS SEX')
37 - access("TAG1"."TAG_TYPE"="TYPE1"."ID")
38 - filter(UPPER("TYPE0"."TERM")='PATIENT ID')
39 - access("TAG0"."TAG_TYPE"="TYPE0"."ID")
41 - access("R"."ID"="TAG2"."TAGGABLE_RESOURCE_ID")
43 - access("R"."ID"="TAG1"."TAGGABLE_RESOURCE_ID")
45 - access("TAG0"."TAGGABLE_RESOURCE_ID"="R"."ID")
46 - filter(UPPER("TYPE2"."TERM")='PROTOCOL NAME')
47 - access("TAG2"."TAG_TYPE"="TYPE2"."ID")
48 - filter(UPPER("TYPE1"."TERM")='STUDY DESCRIPTION')
49 - access("TAG1"."TAG_TYPE"="TYPE1"."ID")
50 - filter(UPPER("TYPE0"."TERM")='INSTITUTION NAME')
51 - access("TAG0"."TAG_TYPE"="TYPE0"."ID")
109 rows selected
And the explain plan for the fast db is
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4207488897
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 46 (3)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 48 | 46 (3)| 00:00:01 |
| 2 | VIEW | VM_NWVW_2 | 1 | 48 | 46 (3)| 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 435 | 46 (3)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 435 | 40 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 411 | 39 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 375 | 36 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 351 | 35 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 315 | 32 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 291 | 31 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 255 | 28 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 231 | 27 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 207 | 24 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 183 | 23 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 159 | 20 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 5 | 265 | 15 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 5 | 240 | 15 (0)| 00:00:01 |
|* 17 | TABLE ACCESS FULL | TAG_TYPE | 4 | 96 | 3 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | TAG | 1 | 24 | 3 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | IDX_TAG_TYPE_VAL | 1 | | 2 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | TABLE1_PK | 1 | 5 | 0 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | TAGGABLE_RESOURCE | 1 | 106 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | TABLE1_PK | 1 | | 0 (0)| 00:00:01 |
| 23 | NESTED LOOPS | | | | | |
| 24 | NESTED LOOPS | | 1 | 53 | 5 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | 1 | 29 | 4 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | TABLE1_PK | 1 | 5 | 1 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID| TAG | 1 | 24 | 3 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | IDX_FK_TAG_RES_TYPE | 139 | | 2 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | TAG_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | TAG_TYPE | 1 | 24 | 1 (0)| 00:00:01 |
|* 31 | TABLE ACCESS BY INDEX ROWID | TAG | 1 | 24 | 3 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | IDX_FK_TAG_RES_TYPE | 139 | | 2 (0)| 00:00:01 |
|* 33 | TABLE ACCESS BY INDEX ROWID | TAG_TYPE | 1 | 24 | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | TAG_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
|* 35 | TABLE ACCESS BY INDEX ROWID | TAG | 1 | 24 | 3 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | IDX_FK_TAG_RES_TYPE | 139 | | 2 (0)| 00:00:01 |
|* 37 | TABLE ACCESS BY INDEX ROWID | TAG_TYPE | 1 | 24 | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | TAG_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | TAG | 139 | 5004 | 3 (0)| 00:00:01 |
|* 40 | INDEX RANGE SCAN | IDX_FK_TAG_RES_TYPE | 139 | | 2 (0)| 00:00:01 |
|* 41 | TABLE ACCESS BY INDEX ROWID | TAG_TYPE | 1 | 24 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | TAG_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID | TAG | 139 | 5004 | 3 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | IDX_FK_TAG_RES_TYPE | 139 | | 2 (0)| 00:00:01 |
|* 45 | TABLE ACCESS BY INDEX ROWID | TAG_TYPE | 1 | 24 | 1 (0)| 00:00:01 |
|* 46 | INDEX UNIQUE SCAN | TAG_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
| 47 | TABLE ACCESS BY INDEX ROWID | TAG | 139 | 5004 | 3 (0)| 00:00:01 |
|* 48 | INDEX RANGE SCAN | IDX_FK_TAG_RES_TYPE | 139 | | 2 (0)| 00:00:01 |
|* 49 | TABLE ACCESS BY INDEX ROWID | TAG_TYPE | 1 | 24 | 1 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | TAG_TYPE_PK | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
17 - filter(UPPER("TYPE2"."TERM")='PATIENTS BIRTH DATE')
19 - access("TAG2"."TAG_VALUE"='123456' AND "TAG2"."TAG_TYPE"="TYPE2"."ID")
20 - access("R"."ID"="TAG2"."TAGGABLE_RESOURCE_ID")
21 - filter( REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='CVMD_AU_007' OR REGEXP_SUBSTR
("R"."URL",'[^/]+',1,1)='CVMD_DU_014' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='CVMD_EU_008' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='CVMD_EU_016' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='CVMD_GU_006'
OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='CVMD_DU_093' OR REGEXP_SUBSTR
("R"."URL",'[^/]+',1,1)='CVMD_DU_097' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='INF_DU_028' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='INF_AU_108' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='INF_AU_124' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='INF_DU_106' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='INF_GU_090' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='NS_AU_044' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='NS_EU_082' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='NS_GU_039' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='ONC_AU_061' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='ONC_DU_065' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='ONC_DU_067' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='WHMSB_AU_075' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='WHMSB_GU_080'
OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='WHMSB_AU_096' OR REGEXP_SUBSTR
("R"."URL",'[^/]+',1,1)='WHMSB_AU_118' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='CXR' OR REGEXP_SUBSTR
("R"."URL",'[^/]+',1,1)='INF_GU_022' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='Z_TEST_PROJ' OR
REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='Z_TEST_PROJ_RESTRICTED' OR REGEXP_SUBSTR
("R"."URL",'[^/]+',1,1)='LIMS' OR REGEXP_SUBSTR ("R"."URL",'[^/]+',1,1)='GRSA_INF_004')
22 - access("R"."ID"="R"."ID")
filter( EXISTS (SELECT 0 FROM "TAGGER"."TAG_TYPE" "TYPE0","TAGGER"."TAG"
"TAG0","TAGGER"."TAGGABLE_RESOURCE" "R" WHERE "R"."ID"=:B1 AND "TAG0"."TAGGABLE_RESOURCE_ID"=:B2 AND
"TAG0"."TAG_VALUE"='WHMSB_AU_096' AND "TAG0"."TAG_TYPE"="TYPE0"."ID" AND UPPER("TYPE0"."TERM")='PROJECT'))
26 - access("R"."ID"=:B1)
27 - filter("TAG0"."TAG_VALUE"='WHMSB_AU_096')
28 - access("TAG0"."TAGGABLE_RESOURCE_ID"=:B1)
29 - access("TAG0"."TAG_TYPE"="TYPE0"."ID")
30 - filter(UPPER("TYPE0"."TERM")='PROJECT')
31 - filter("TAG1"."TAG_VALUE"='M')
32 - access("R"."ID"="TAG1"."TAGGABLE_RESOURCE_ID")
33 - filter(UPPER("TYPE1"."TERM")='PATIENTS SEX')
34 - access("TAG1"."TAG_TYPE"="TYPE1"."ID")
35 - filter("TAG0"."TAG_VALUE"='P001')
36 - access("R"."ID"="TAG0"."TAGGABLE_RESOURCE_ID")
37 - filter(UPPER("TYPE0"."TERM")='PATIENT ID')
38 - access("TAG0"."TAG_TYPE"="TYPE0"."ID")
40 - access("R"."ID"="TAG2"."TAGGABLE_RESOURCE_ID")
41 - filter(UPPER("TYPE2"."TERM")='PROTOCOL NAME')
42 - access("TAG2"."TAG_TYPE"="TYPE2"."ID")
44 - access("R"."ID"="TAG1"."TAGGABLE_RESOURCE_ID")
45 - filter(UPPER("TYPE1"."TERM")='STUDY DESCRIPTION')
46 - access("TAG1"."TAG_TYPE"="TYPE1"."ID")
48 - access("TAG0"."TAGGABLE_RESOURCE_ID"="R"."ID")
49 - filter(UPPER("TYPE0"."TERM")='INSTITUTION NAME')
50 - access("TAG0"."TAG_TYPE"="TYPE0"."ID")
106 rows selected
|
|
|
|
| Re: Explain Plan Analysis - Execution ordering [message #466861 is a reply to message #466859] |
Wed, 21 July 2010 09:23 |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
Quick note:
I get an error when trying to run the sql to get additional info:
SELECT DBMS_METADATA.GET_DDL (object_type, object_name, object_owner)
FROM plan_table
WHERE object_type IN ('TABLE','VIEW');
results in
Error starting at line 1 in command:
SELECT DBMS_METADATA.GET_DDL (object_type, object_name, object_owner)
FROM plan_table
WHERE object_type IN ('TABLE','VIEW')
Error report:
SQL Error: ORA-31603: object "VM_NWVW_2" of type VIEW not found in schema "SYS"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3261
ORA-06512: at "SYS.DBMS_METADATA", line 4863
ORA-06512: at line 1
31603. 00000 - "object \"%s\" of type %s not found in schema \"%s\""
*Cause: The specified object was not found in the database.
*Action: Correct the object specification and try the call again.
|
|
|
|
|
|
| Re: Explain Plan Analysis - Execution ordering [message #466866 is a reply to message #466861] |
Wed, 21 July 2010 09:29 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That's because oracle sometimes generates temporary views to join/sort data. They don't exist in the data dictionary.
Don't use plan table to do this. You know tables and views are referenced by the query so just run through them manually.
|
|
|
|
|
|
|
|
| Re: Explain Plan Analysis - Execution ordering [message #466871 is a reply to message #466867] |
Wed, 21 July 2010 09:40 |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
Ok, if I run
SELECT CAST(DBMS_METADATA.GET_DDL (object_type, object_name, object_owner) as VARCHAR2(1000))
FROM plan_table
WHERE object_type IN ('TABLE','VIEW') and object_owner not in ('SYS');
I get:
CAST(DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER)ASVARCHAR2(1000))
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE "TAGGER"."TAG"
( "ID" NUMBER NOT NULL ENABLE,
"TAGGABLE_RESOURCE_ID" NUMBER NOT NULL ENABLE,
"TAG_TYPE" NUMBER,
"TAG_VALUE" VARCHAR2(4000),
CONSTRAINT "TAG_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "FK_TAG_TYPE" FOREIGN KEY ("TAG_TYPE")
REFERENCES "TAGGER"."TAG_TYPE" ("ID") ENABLE,
CONSTRAINT "FK_TAGGABLE_RES" FOREIGN KEY ("TAGGABLE_RESOURCE_ID")
REFERENCES "TAGGER"."TAGGABLE_RESOURCE" ("ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
CREATE TABLE "TAGGER"."TAG"
( "ID" NUMBER NOT NULL ENABLE,
"TAGGABLE_RESOURCE_ID" NUMBER NOT NULL ENABLE,
"TAG_TYPE" NUMBER,
"TAG_VALUE" VARCHAR2(4000),
CONSTRAINT "TAG_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "FK_TAG_TYPE" FOREIGN KEY ("TAG_TYPE")
REFERENCES "TAGGER"."TAG_TYPE" ("ID") ENABLE,
CONSTRAINT "FK_TAGGABLE_RES" FOREIGN KEY ("TAGGABLE_RESOURCE_ID")
REFERENCES "TAGGER"."TAGGABLE_RESOURCE" ("ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
CREATE TABLE "TAGGER"."TAG_TYPE"
( "ID" NUMBER NOT NULL ENABLE,
"TERM" VARCHAR2(4000) NOT NULL ENABLE,
CONSTRAINT "TAG_TYPE_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "TAG_TYPE_TERM_UNIQUE" UNIQUE ("TERM")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
CREATE TABLE "TAGGER"."TAG"
( "ID" NUMBER NOT NULL ENABLE,
"TAGGABLE_RESOURCE_ID" NUMBER NOT NULL ENABLE,
"TAG_TYPE" NUMBER,
"TAG_VALUE" VARCHAR2(4000),
CONSTRAINT "TAG_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "FK_TAG_TYPE" FOREIGN KEY ("TAG_TYPE")
REFERENCES "TAGGER"."TAG_TYPE" ("ID") ENABLE,
CONSTRAINT "FK_TAGGABLE_RES" FOREIGN KEY ("TAGGABLE_RESOURCE_ID")
REFERENCES "TAGGER"."TAGGABLE_RESOURCE" ("ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
CREATE TABLE "TAGGER"."TAGGABLE_RESOURCE"
( "ID" NUMBER NOT NULL ENABLE,
"URL" VARCHAR2(4000),
"RESOURCE_TYPE" NUMBER,
CONSTRAINT "TABLE1_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "TAGGABLE_RESOURCE_URL_UNIQUE" UNIQUE ("URL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "FK_TAGGABLE_RESOURCE_TYPE" FOREIGN KEY ("RESOURCE_TYPE")
REFERENCES "TAGGER"."TAGGABLE_RESOURCE_TYPE" ("ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREA
CREATE TABLE "TAGGER"."TAG"
( "ID" NUMBER NOT NULL ENABLE,
"TAGGABLE_RESOURCE_ID" NUMBER NOT NULL ENABLE,
"TAG_TYPE" NUMBER,
"TAG_VALUE" VARCHAR2(4000),
CONSTRAINT "TAG_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "FK_TAG_TYPE" FOREIGN KEY ("TAG_TYPE")
REFERENCES "TAGGER"."TAG_TYPE" ("ID") ENABLE,
CONSTRAINT "FK_TAGGABLE_RES" FOREIGN KEY ("TAGGABLE_RESOURCE_ID")
REFERENCES "TAGGER"."TAGGABLE_RESOURCE" ("ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
CREATE TABLE "TAGGER"."TAG_TYPE"
( "ID" NUMBER NOT NULL ENABLE,
"TERM" VARCHAR2(4000) NOT NULL ENABLE,
CONSTRAINT "TAG_TYPE_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "TAG_TYPE_TERM_UNIQUE" UNIQUE ("TERM")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
CREATE TABLE "TAGGER"."TAG_TYPE"
( "ID" NUMBER NOT NULL ENABLE,
"TERM" VARCHAR2(4000) NOT NULL ENABLE,
CONSTRAINT "TAG_TYPE_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "TAG_TYPE_TERM_UNIQUE" UNIQUE ("TERM")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
CREATE TABLE "TAGGER"."TAG_TYPE"
( "ID" NUMBER NOT NULL ENABLE,
"TERM" VARCHAR2(4000) NOT NULL ENABLE,
CONSTRAINT "TAG_TYPE_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "TAG_TYPE_TERM_UNIQUE" UNIQUE ("TERM")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
CREATE TABLE "TAGGER"."TAG"
( "ID" NUMBER NOT NULL ENABLE,
"TAGGABLE_RESOURCE_ID" NUMBER NOT NULL ENABLE,
"TAG_TYPE" NUMBER,
"TAG_VALUE" VARCHAR2(4000),
CONSTRAINT "TAG_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "FK_TAG_TYPE" FOREIGN KEY ("TAG_TYPE")
REFERENCES "TAGGER"."TAG_TYPE" ("ID") ENABLE,
CONSTRAINT "FK_TAGGABLE_RES" FOREIGN KEY ("TAGGABLE_RESOURCE_ID")
REFERENCES "TAGGER"."TAGGABLE_RESOURCE" ("ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
CREATE TABLE "TAGGER"."TAG"
( "ID" NUMBER NOT NULL ENABLE,
"TAGGABLE_RESOURCE_ID" NUMBER NOT NULL ENABLE,
"TAG_TYPE" NUMBER,
"TAG_VALUE" VARCHAR2(4000),
CONSTRAINT "TAG_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "FK_TAG_TYPE" FOREIGN KEY ("TAG_TYPE")
REFERENCES "TAGGER"."TAG_TYPE" ("ID") ENABLE,
CONSTRAINT "FK_TAGGABLE_RES" FOREIGN KEY ("TAGGABLE_RESOURCE_ID")
REFERENCES "TAGGER"."TAGGABLE_RESOURCE" ("ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
CREATE TABLE "TAGGER"."TAG"
( "ID" NUMBER NOT NULL ENABLE,
"TAGGABLE_RESOURCE_ID" NUMBER NOT NULL ENABLE,
"TAG_TYPE" NUMBER,
"TAG_VALUE" VARCHAR2(4000),
CONSTRAINT "TAG_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "FK_TAG_TYPE" FOREIGN KEY ("TAG_TYPE")
REFERENCES "TAGGER"."TAG_TYPE" ("ID") ENABLE,
CONSTRAINT "FK_TAGGABLE_RES" FOREIGN KEY ("TAGGABLE_RESOURCE_ID")
REFERENCES "TAGGER"."TAGGABLE_RESOURCE" ("ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
CREATE TABLE "TAGGER"."TAG_TYPE"
( "ID" NUMBER NOT NULL ENABLE,
"TERM" VARCHAR2(4000) NOT NULL ENABLE,
CONSTRAINT "TAG_TYPE_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "TAG_TYPE_TERM_UNIQUE" UNIQUE ("TERM")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
CREATE TABLE "TAGGER"."TAG_TYPE"
( "ID" NUMBER NOT NULL ENABLE,
"TERM" VARCHAR2(4000) NOT NULL ENABLE,
CONSTRAINT "TAG_TYPE_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "TAG_TYPE_TERM_UNIQUE" UNIQUE ("TERM")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
CREATE TABLE "TAGGER"."TAG_TYPE"
( "ID" NUMBER NOT NULL ENABLE,
"TERM" VARCHAR2(4000) NOT NULL ENABLE,
CONSTRAINT "TAG_TYPE_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE,
CONSTRAINT "TAG_TYPE_TERM_UNIQUE" UNIQUE ("TERM")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TAGGER"
15 rows selected
[Updated on: Wed, 21 July 2010 09:42] Report message to a moderator |
|
|
|
| Re: Explain Plan Analysis - Execution ordering [message #466873 is a reply to message #466871] |
Wed, 21 July 2010 09:45 |
Vackar
Messages: 81
Registered: October 2007
|
Member |
|
|
And table index info:
TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------
TAG IDX_FK_TAG_RES_TYPE TAGGABLE_RESOURCE_ID 1
TAG IDX_FK_TAG_TYPE TAG_TYPE 1
TAG IDX_TAG_TYPE_VAL TAG_VALUE 1
TAG IDX_TAG_TYPE_VAL TAG_TYPE 2
TAG IDX_TAG_VAL TAG_VALUE 1
TAG TAG_PK ID 1
TAG TAG_VAL_UPPER SYS_NC00005$ 1
TAGGABLE_RESOURCE TABLE1_PK ID 1
TAGGABLE_RESOURCE TAGGABLE_RESOURCE_URL_UNIQUE URL 1
TAG_TYPE TAG_TYPE_PK ID 1
TAG_TYPE TAG_TYPE_TERM_UNIQUE TERM 1
11 rows selected
|
|
|
|
| Re: Explain Plan Analysis - Execution ordering [message #466974 is a reply to message #466817] |
Wed, 21 July 2010 18:56 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Looks like the birthdate is the most selective predicate, so we want to drive off that one and not the others.
Problem is that you have a couple of IN sub-queries and it is hard to instruct Oracle which one to use first. It is much easier if you reconstruct it as a join, then you can use the ORDERED hint.
Below is an alternate SQL. The ORDERED hint should execute the first row source in the FROM clause first, which will be the birth date subquery, and then each other table in order listed in the FROM. You can try promoting the PROJECT sub-query higher up the FROM clause if it is more selective than the REGEXP.
I have also added a PUSH_PRED, which should permit indexed lookup in the PROJECT sub-query. Without this, the DISTINCT operator may prevent the join condition from being pushed into the subquery.
SELECT /*+ ORDERED PUSH_PRED */
DISTINCT tag0.tag_value,
tag1.tag_value,
tag2.tag_value,
COUNT(r.id)
FROM (
SELECT DISTINCT r.id
FROM tagger.taggable_resource r,
tagger.tag tag0,
tagger.tag_type type0,
tagger.tag tag1,
tagger.tag_type type1,
tagger.tag tag2,
tagger.tag_type type2
WHERE 1 = 1
AND r.id = tag0.taggable_resource_id
AND tag0.tag_type = type0.id
AND Upper(type0.term) = Upper('Patient Id')
AND tag0.tag_value = 'P001'
AND r.id = tag1.taggable_resource_id
AND tag1.tag_type = type1.id
AND Upper(type1.term) = Upper('Patients Sex')
AND tag1.tag_value = 'M'
AND r.id = tag2.taggable_resource_id
AND tag2.tag_type = type2.id
AND Upper(type2.term) = Upper('Patients Birth Date')
AND tag2.tag_value = '123456')
) bd,
tagger.taggable_resource r,
tagger.tag tag0,
tagger.tag_type type0,
tagger.tag tag1,
tagger.tag_type type1,
tagger.tag tag2,
tagger.tag_type type2,
( SELECT DISTINCT r.id
FROM tagger.taggable_resource r,
tagger.tag tag0,
tagger.tag_type type0
WHERE 1 = 1
AND r.id = tag0.taggable_resource_id
AND tag0.tag_type = type0.id
AND Upper(type0.term) = Upper('Project')
AND tag0.tag_value = 'WHMSB_AU_096'
) proj
WHERE 1 = 1
AND r.id = bd.id
AND r.id = proj.id
AND r.id = tag0.taggable_resource_id
AND tag0.tag_type = type0.id
AND Upper(type0.term) = Upper('Institution Name')
AND r.id = tag1.taggable_resource_id
AND tag1.tag_type = type1.id
AND Upper(type1.term) = Upper('Study Description')
AND r.id = tag2.taggable_resource_id
AND tag2.tag_type = type2.id
AND Upper(type2.term) = Upper('Protocol Name')
AND Regexp_substr(r.url, '[^/]+', 1, 1) IN (
'CVMD_AU_007', 'CVMD_DU_014', 'CVMD_EU_008', 'CVMD_EU_016',
'CVMD_GU_006', 'CVMD_DU_093'
,
'CVMD_DU_097'
, 'INF_DU_028',
'INF_AU_108', 'INF_AU_124',
'INF_DU_106', 'INF_GU_090',
'NS_AU_044', 'NS_EU_082',
'NS_GU_039',
'ONC_AU_061',
'ONC_DU_065', 'ONC_DU_067',
'WHMSB_AU_075', 'WHMSB_GU_080',
'WHMSB_AU_096',
'WHMSB_AU_118',
'CXR',
'INF_GU_022',
'Z_TEST_PROJ',
'Z_TEST_PROJ_RESTRICTED'
, 'LIMS', 'GRSA_INF_004' )
GROUP BY tag0.tag_value,
tag1.tag_value,
tag2.tag_value
ORDER BY tag0.tag_value,
tag1.tag_value,
tag2.tag_value
Ross Leishman
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Nov 25 05:55:12 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
