Home » RDBMS Server » Performance Tuning » Expplain plan output (10.1.0.1)
Expplain plan output [message #553904] |
Wed, 09 May 2012 04:19 |
|
guddu_12
Messages: 227 Registered: April 2012 Location: UK
|
Senior Member |
|
|
Hi
I have a situation where i want to know if my materialized view is being hit or not.
I have a view ABC which is based on table A,B. i have created a materialized view with same definition as ABC.
when i run the expalin plan it shows that the materialized view is being hit.
Now when i join view ABC with other table say XYZ, the expalin paln shows that it is using views not the materialized view.
Can you please help me to construct the final query so that expaln plan will hit the materialized view.
Thanks
Rajesh
|
|
|
Re: Expplain plan output [message #553905 is a reply to message #553904] |
Wed, 09 May 2012 04:23 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
And how do you expect us to help construct a query when we don't have:
1) the table defintions
2) the view definitions
3) the query you are trying to write
Seriously, how do you expect us to answer that with the information you have provided?
|
|
|
Re: Expplain plan output [message #553919 is a reply to message #553905] |
Wed, 09 May 2012 05:39 |
|
guddu_12
Messages: 227 Registered: April 2012 Location: UK
|
Senior Member |
|
|
Ok So sorry about that
My main view is CREATE OR REPLACE FORCE VIEW "OVSD"."GSP_STATUS" ("STA_SER_ID", "STA_NAME")
AS
SELECT DISTINCT ser_id AS STA_ser_id,
rct_name AS STA_name
FROM sd_servicecalls,
rep_codes,
rep_codes_text
WHERE ser_sta_oid = rcd_oid
AND rcd_oid = rct_rcd_oid
AND rct_name IN ('New','Awaiting Approval','Approved','In Progress','Awaiting Supplier','Awaiting RFC','Awaiting Release','On Hold','Resolved','Implemented','Closed');
materialized view is as below
SELECT
/*+ APPEND */
DISTINCT ser_id AS STA_ser_id,
rct_name AS STA_name
FROM sd_servicecalls A,
rep_codes B ,
rep_codes_text C
WHERE A.ser_sta_oid = rcd_oid
AND rcd_oid = rct_rcd_oid
AND RCT_NAME IN ('New', 'Awaiting Approval', 'Approved', 'In Progress', 'Awaiting Supplier' , 'Awaiting RFC', 'Awaiting Release', 'Pending Release', 'On Hold', 'Resolved', 'Implemented', 'Closed');
ALTER MATERIALIZED VIEW "OVSD"."GSP_STATUS_1" COMPILE;
when i fire a wery on the normal view it hit materialized view as expected
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------- --------------------
| 0 | SELECT STATEMENT | | 64108 | 63M| | 26 (58)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 64108 | 63M| | 26 (58)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 3 | VIEW | GSP_STATUS | 64108 | 63M| | 26 (58)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | SORT UNIQUE | | 64108 | 688K| 2536K| 26 (58)| 00:00:01 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 64108 | 688K| | 17 (36)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 64108 | 688K| | 17 (36)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 7 | PX BLOCK ITERATOR | | 64108 | 688K| | 17 (36)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | |
|* 8 | MAT_VIEW REWRITE ACCESS FULL| GSP_STATUS_1 | 64108 | 688K| | 17 (36)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------- --------------------
Predicate Information (identified by operation id):
---------------------------------------------------
Now when i join the normal view with any table the query is as bel0W
explain plan for
SELECT ser_id,
STA_NAME,
CAT_NAME,
PER1_NAME,
REF_NAME,
CAL_NAME,
ESC_NAME,
SER_NAME,
ser_deadline,
ser_description,
sd_servicecalls.reg_created,
org_name1,
IMP_NAME,
CAR_NAME,
REL_NAME,
INC_NAME
FROM sd_servicecalls,
cdm_organizations,
GSP_STATUS,----------------------------------THIS SHOULD BE HIT BY MV WHICH IS NOT HAPPENING
GSP_CATEGORY,
GSP_REPORTINGUSER,
GSP_REFERENCE,
GSP_AFFECTEDUSER,
GSP_ESCALATION,
GSP_SERVICE,
GSP_IMPACT,
GSP_CARESETTING,
GSP_RELATED,
GSP_INCSUM
WHERE STA_SER_ID = ser_id
AND CAT_SER_ID = ser_id
AND PER1_SER_ID = ser_id
AND REF_SER_ID = ser_id
AND CAL_SER_ID = ser_id
AND ESC_SER_ID = ser_id
AND SER_SER_ID = ser_id
AND ser_caller_org = org_oid
AND IMP_SER_ID = ser_id
AND CAR_SER_ID = ser_id
AND REL_SER_ID = ser_id
AND INC_SER_ID = ser_id
ORDER BY SER_ID DESC;
EXPLAIN PALN
Plan hash value: 164656308
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 265 | 1522K| | 23738 (5)| 00:04:45 |
| 1 | SORT ORDER BY | | 265 | 1522K| 4248K| 23738 (5)| 00:04:45 |
| 2 | NESTED LOOPS OUTER | | 265 | 1522K| | 23406 (6)| 00:04:41 |
| 3 | NESTED LOOPS | | 265 | 1520K| | 23376 (6)| 00:04:41 |
|* 4 | HASH JOIN OUTER | | 265 | 1515K| | 23109 (6)| 00:04:38 |
| 5 | NESTED LOOPS | | 265 | 996K| | 17750 (5)| 00:03:33 |
|* 6 | HASH JOIN | | 265 | 995K| | 17748 (5)| 00:03:33 |
|* 7 | HASH JOIN | | 265 | 990K| | 15622 (5)| 00:03:08 |
|* 8 | HASH JOIN | | 265 | 968K| | 13511 (5)| 00:02:43 |
|* 9 | HASH JOIN | | 265 | 924K| | 10806 (5)| 00:02:10 |
|* 10 | HASH JOIN | | 265 | 881K| | 9143 (4)| 00:01:50 |
|* 11 | HASH JOIN | | 265 | 747K| | 7193 (4)| 00:01:27 |
|* 12 | HASH JOIN | | 265 | 704K| | 5488 (4)| 00:01:06 |
| 13 | NESTED LOOPS | | 326 | 702K| | 4044 (3)| 00:00:49 |
|* 14 | HASH JOIN | | 326 | 691K| 3480K| 3715 (4)| 00:00:45 |
|* 15 | HASH JOIN | | 3088 | 3437K| 3152K| 2230 (4)| 00:00:27 |
| 16 | VIEW | GSP_STATUS | 3088 | 3109K| | 859 (4)| 00:00:11 |
| 17 | SORT UNIQUE | | 3088 | 165K| | 859 (4)| 00:00:11 |
|* 18 | HASH JOIN | | 3088 | 165K| | 857 (4)| 00:00:11 |
| 19 | NESTED LOOPS | | 53 | 2067 | | 15 (14)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | REP_CODES_TEXT | 53 | 1484 | | 15 (14)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | OBS_RCD_PK | 1 | 11 | | 0 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 23 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 6822K| | 837 (3)| 00:00:11 |
| 24 | VIEW | GSP_CATEGORY | 6763 | 6809K| | 976 (5)| 00:00:12 |
| 25 | SORT UNIQUE | | 6763 | 435K| 1032K| 976 (5)| 00:00:12 |
|* 26 | HASH JOIN | | 6763 | 435K| | 865 (5)| 00:00:11 |
|* 27 | TABLE ACCESS FULL | REP_CODES_TEXT | 34 | 952 | | 15 (14)| 00:00:01 |
|* 28 | HASH JOIN | | 64092 | 2378K| | 845 (4)| 00:00:11 |
| 29 | INDEX FAST FULL SCAN | OBS_RCD_PK | 1108 | 12188 | | 3 (0)| 00:00:01 |
| 30 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1689K| | 837 (3)| 00:00:11 |
| 31 | TABLE ACCESS BY INDEX ROWID| CDM_ORGANIZATIONS | 1 | 35 | | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | CDM_ORG_PK | 1 | | | 0 (0)| 00:00:01 |
| 33 | VIEW | GSP_CARESETTING | 45320 | 22M| | 1440 (6)| 00:00:18 |
| 34 | SORT UNIQUE | | 45320 | 1150K| 3600K| 1440 (6)| 00:00:18 |
|* 35 | HASH JOIN | | 56006 | 1422K| 1208K| 1044 (5)| 00:00:13 |
| 36 | TABLE ACCESS FULL | SD_SCF_TEXT | 56006 | 546K| | 47 (13)| 00:00:01 |
| 37 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 38 | VIEW | GSP_SERVICE | 64034 | 10M| | 1701 (5)| 00:00:21 |
| 39 | SORT UNIQUE | | 64034 | 3189K| 7544K| 1701 (5)| 00:00:21 |
|* 40 | HASH JOIN | | 64034 | 3189K| | 847 (4)| 00:00:11 |
| 41 | TABLE ACCESS FULL | CDM_SERVICES | 250 | 8750 | | 5 (0)| 00:00:01 |
| 42 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 43 | VIEW | GSP_IMPACT | 64057 | 31M| | 1944 (4)| 00:00:24 |
| 44 | SORT UNIQUE | | 64057 | 3878K| 9080K| 1944 (4)| 00:00:24 |
|* 45 | HASH JOIN | | 64057 | 3878K| | 943 (4)| 00:00:12 |
|* 46 | TABLE ACCESS FULL | SD_CODES_LOCALE | 56 | 2800 | | 101 (5)| 00:00:02 |
| 47 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 751K| | 837 (3)| 00:00:11 |
| 48 | VIEW | GSP_AFFECTEDUSER | 64081 | 10M| | 1659 (5)| 00:00:20 |
| 49 | SORT UNIQUE | | 64081 | 2690K| 6552K| 1659 (5)| 00:00:20 |
|* 50 | HASH JOIN | | 64081 | 2690K| | 914 (4)| 00:00:11 |
| 51 | TABLE ACCESS FULL | CDM_PERSONS | 9730 | 256K| | 71 (3)| 00:00:01 |
| 52 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 53 | VIEW | GSP_REPORTINGUSER | 64092 | 10M| | 2700 (5)| 00:00:33 |
| 54 | SORT UNIQUE | | 64092 | 4068K| 9592K| 2700 (5)| 00:00:33 |
|* 55 | HASH JOIN | | 64092 | 4068K| | 1662 (5)| 00:00:20 |
| 56 | TABLE ACCESS FULL | CDM_PERSONS | 9730 | 256K| | 71 (3)| 00:00:01 |
|* 57 | HASH JOIN | | 64092 | 2378K| 1760K| 1585 (4)| 00:00:20 |
| 58 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 59 | TABLE ACCESS FULL | SD_SER_CUSTOM_FIELDS | 64093 | 1376K| | 541 (4)| 00:00:07 |
| 60 | VIEW | GSP_REFERENCE | 64092 | 5445K| | 2106 (5)| 00:00:26 |
| 61 | SORT UNIQUE | | 64092 | 1815K| 5048K| 2106 (5)| 00:00:26 |
|* 62 | HASH JOIN | | 64092 | 1815K| 1568K| 1558 (4)| 00:00:19 |
| 63 | TABLE ACCESS FULL | SD_SER_CUSTOM_FIELDS | 64093 | 813K| | 541 (4)| 00:00:07 |
| 64 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 65 | VIEW | GSP_ESCALATION | 64092 | 1126K| | 2121 (5)| 00:00:26 |
| 66 | SORT UNIQUE | | 64092 | 1877K| 5048K| 2121 (5)| 00:00:26 |
|* 67 | HASH JOIN | | 64092 | 1877K| 1632K| 1561 (4)| 00:00:19 |
| 68 | TABLE ACCESS FULL | SD_SER_CUSTOM_FIELDS | 64093 | 876K| | 541 (4)| 00:00:07 |
| 69 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
|* 70 | INDEX UNIQUE SCAN | SD_SER_ID_UIX | 1 | 5 | | 0 (0)| 00:00:01 |
| 71 | VIEW | GSP_RELATEDEVENTS | 64092 | 122M| | 5354 (8)| 00:01:05 |
| 72 | SORT GROUP BY | | 64092 | 3880K| 36M| 5354 (8)| 00:01:05 |
|* 73 | HASH JOIN | | 460K| 27M| | 2006 (5)| 00:00:25 |
| 74 | VIEW | GSP_EVENTS | 26624 | 624K| | 1083 (5)| 00:00:14 |
| 75 | SORT UNIQUE | | 26624 | 702K| 1896K| 1083 (5)| 00:00:14 |
|* 76 | HASH JOIN | | 26624 | 702K| | 865 (4)| 00:00:11 |
| 77 | INDEX FAST FULL SCAN | CDM_SRE_TO_IX | 26624 | 286K| | 20 (0)| 00:00:01 |
| 78 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
|* 79 | HASH JOIN | | 26624 | 988K| | 918 (4)| 00:00:12 |
| 80 | TABLE ACCESS FULL | CDM_SERV_EVT_RELATIONS | 26624 | 572K| | 73 (6)| 00:00:01 |
| 81 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 82 | TABLE ACCESS BY INDEX ROWID | SD_SERVICECALLS | 1 | 16 | | 1 (0)| 00:00:01 |
|* 83 | INDEX UNIQUE SCAN | SD_SER_ID_UIX | 1 | | | 0 (0)| 00:00:01 |
| 84 | TABLE ACCESS BY INDEX ROWID | SD_SER_4K5 | 1 | 10 | | 1 (0)| 00:00:01 |
|* 85 | INDEX UNIQUE SCAN | SD_SE5_PK | 1 | | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
Can you please guide me.
|
|
|
|
Re: Expplain plan output [message #553932 is a reply to message #553922] |
Wed, 09 May 2012 06:14 |
|
guddu_12
Messages: 227 Registered: April 2012 Location: UK
|
Senior Member |
|
|
explain plan for
SELECT /*+ rewrite(gsp_status_1) */ ser_id,
STA_NAME,
CAT_NAME,
PER1_NAME,
REF_NAME,
CAL_NAME,
ESC_NAME,
SER_NAME,
ser_deadline,
ser_description,
sd_servicecalls.reg_created,
org_name1,
IMP_NAME,
CAR_NAME,
REL_NAME,
INC_NAME
FROM sd_servicecalls,
cdm_organizations,
GSP_STATUS,
GSP_CATEGORY,
GSP_REPORTINGUSER,
GSP_REFERENCE,
GSP_AFFECTEDUSER,
GSP_ESCALATION,
GSP_SERVICE,
GSP_IMPACT,
GSP_CARESETTING,
GSP_RELATED,
GSP_INCSUM
WHERE STA_SER_ID = ser_id
AND CAT_SER_ID = ser_id
AND PER1_SER_ID = ser_id
AND REF_SER_ID = ser_id
AND CAL_SER_ID = ser_id
AND ESC_SER_ID = ser_id
AND SER_SER_ID = ser_id
AND ser_caller_org = org_oid
AND IMP_SER_ID = ser_id
AND CAR_SER_ID = ser_id
AND REL_SER_ID = ser_id
AND INC_SER_ID = ser_id
ORDER BY SER_ID DESC;
explain plan output
Plan hash value: 164656308
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 265 | 1522K| | 23738 (5)| 00:04:45 |
| 1 | SORT ORDER BY | | 265 | 1522K| 4248K| 23738 (5)| 00:04:45 |
| 2 | NESTED LOOPS OUTER | | 265 | 1522K| | 23406 (6)| 00:04:41 |
| 3 | NESTED LOOPS | | 265 | 1520K| | 23376 (6)| 00:04:41 |
|* 4 | HASH JOIN OUTER | | 265 | 1515K| | 23109 (6)| 00:04:38 |
| 5 | NESTED LOOPS | | 265 | 996K| | 17750 (5)| 00:03:33 |
|* 6 | HASH JOIN | | 265 | 995K| | 17748 (5)| 00:03:33 |
|* 7 | HASH JOIN | | 265 | 990K| | 15622 (5)| 00:03:08 |
|* 8 | HASH JOIN | | 265 | 968K| | 13511 (5)| 00:02:43 |
|* 9 | HASH JOIN | | 265 | 924K| | 10806 (5)| 00:02:10 |
|* 10 | HASH JOIN | | 265 | 881K| | 9143 (4)| 00:01:50 |
|* 11 | HASH JOIN | | 265 | 747K| | 7193 (4)| 00:01:27 |
|* 12 | HASH JOIN | | 265 | 704K| | 5488 (4)| 00:01:06 |
| 13 | NESTED LOOPS | | 326 | 702K| | 4044 (3)| 00:00:49 |
|* 14 | HASH JOIN | | 326 | 691K| 3480K| 3715 (4)| 00:00:45 |
|* 15 | HASH JOIN | | 3088 | 3437K| 3152K| 2230 (4)| 00:00:27 |
| 16 | VIEW | GSP_STATUS | 3088 | 3109K| | 859 (4)| 00:00:11 |
| 17 | SORT UNIQUE | | 3088 | 165K| | 859 (4)| 00:00:11 |
|* 18 | HASH JOIN | | 3088 | 165K| | 857 (4)| 00:00:11 |
| 19 | NESTED LOOPS | | 53 | 2067 | | 15 (14)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | REP_CODES_TEXT | 53 | 1484 | | 15 (14)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | OBS_RCD_PK | 1 | 11 | | 0 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 23 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 6822K| | 837 (3)| 00:00:11 |
| 24 | VIEW | GSP_CATEGORY | 6763 | 6809K| | 976 (5)| 00:00:12 |
| 25 | SORT UNIQUE | | 6763 | 435K| 1032K| 976 (5)| 00:00:12 |
|* 26 | HASH JOIN | | 6763 | 435K| | 865 (5)| 00:00:11 |
|* 27 | TABLE ACCESS FULL | REP_CODES_TEXT | 34 | 952 | | 15 (14)| 00:00:01 |
|* 28 | HASH JOIN | | 64092 | 2378K| | 845 (4)| 00:00:11 |
| 29 | INDEX FAST FULL SCAN | OBS_RCD_PK | 1108 | 12188 | | 3 (0)| 00:00:01 |
| 30 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1689K| | 837 (3)| 00:00:11 |
| 31 | TABLE ACCESS BY INDEX ROWID| CDM_ORGANIZATIONS | 1 | 35 | | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | CDM_ORG_PK | 1 | | | 0 (0)| 00:00:01 |
| 33 | VIEW | GSP_CARESETTING | 45320 | 22M| | 1440 (6)| 00:00:18 |
| 34 | SORT UNIQUE | | 45320 | 1150K| 3600K| 1440 (6)| 00:00:18 |
|* 35 | HASH JOIN | | 56006 | 1422K| 1208K| 1044 (5)| 00:00:13 |
| 36 | TABLE ACCESS FULL | SD_SCF_TEXT | 56006 | 546K| | 47 (13)| 00:00:01 |
| 37 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 38 | VIEW | GSP_SERVICE | 64034 | 10M| | 1701 (5)| 00:00:21 |
| 39 | SORT UNIQUE | | 64034 | 3189K| 7544K| 1701 (5)| 00:00:21 |
|* 40 | HASH JOIN | | 64034 | 3189K| | 847 (4)| 00:00:11 |
| 41 | TABLE ACCESS FULL | CDM_SERVICES | 250 | 8750 | | 5 (0)| 00:00:01 |
| 42 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 43 | VIEW | GSP_IMPACT | 64057 | 31M| | 1944 (4)| 00:00:24 |
| 44 | SORT UNIQUE | | 64057 | 3878K| 9080K| 1944 (4)| 00:00:24 |
|* 45 | HASH JOIN | | 64057 | 3878K| | 943 (4)| 00:00:12 |
|* 46 | TABLE ACCESS FULL | SD_CODES_LOCALE | 56 | 2800 | | 101 (5)| 00:00:02 |
| 47 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 751K| | 837 (3)| 00:00:11 |
| 48 | VIEW | GSP_AFFECTEDUSER | 64081 | 10M| | 1659 (5)| 00:00:20 |
| 49 | SORT UNIQUE | | 64081 | 2690K| 6552K| 1659 (5)| 00:00:20 |
|* 50 | HASH JOIN | | 64081 | 2690K| | 914 (4)| 00:00:11 |
| 51 | TABLE ACCESS FULL | CDM_PERSONS | 9730 | 256K| | 71 (3)| 00:00:01 |
| 52 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 53 | VIEW | GSP_REPORTINGUSER | 64092 | 10M| | 2700 (5)| 00:00:33 |
| 54 | SORT UNIQUE | | 64092 | 4068K| 9592K| 2700 (5)| 00:00:33 |
|* 55 | HASH JOIN | | 64092 | 4068K| | 1662 (5)| 00:00:20 |
| 56 | TABLE ACCESS FULL | CDM_PERSONS | 9730 | 256K| | 71 (3)| 00:00:01 |
|* 57 | HASH JOIN | | 64092 | 2378K| 1760K| 1585 (4)| 00:00:20 |
| 58 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 59 | TABLE ACCESS FULL | SD_SER_CUSTOM_FIELDS | 64093 | 1376K| | 541 (4)| 00:00:07 |
| 60 | VIEW | GSP_REFERENCE | 64092 | 5445K| | 2106 (5)| 00:00:26 |
| 61 | SORT UNIQUE | | 64092 | 1815K| 5048K| 2106 (5)| 00:00:26 |
|* 62 | HASH JOIN | | 64092 | 1815K| 1568K| 1558 (4)| 00:00:19 |
| 63 | TABLE ACCESS FULL | SD_SER_CUSTOM_FIELDS | 64093 | 813K| | 541 (4)| 00:00:07 |
| 64 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 65 | VIEW | GSP_ESCALATION | 64092 | 1126K| | 2121 (5)| 00:00:26 |
| 66 | SORT UNIQUE | | 64092 | 1877K| 5048K| 2121 (5)| 00:00:26 |
|* 67 | HASH JOIN | | 64092 | 1877K| 1632K| 1561 (4)| 00:00:19 |
| 68 | TABLE ACCESS FULL | SD_SER_CUSTOM_FIELDS | 64093 | 876K| | 541 (4)| 00:00:07 |
| 69 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
|* 70 | INDEX UNIQUE SCAN | SD_SER_ID_UIX | 1 | 5 | | 0 (0)| 00:00:01 |
| 71 | VIEW | GSP_RELATEDEVENTS | 64092 | 122M| | 5354 (8)| 00:01:05 |
| 72 | SORT GROUP BY | | 64092 | 3880K| 36M| 5354 (8)| 00:01:05 |
|* 73 | HASH JOIN | | 460K| 27M| | 2006 (5)| 00:00:25 |
| 74 | VIEW | GSP_EVENTS | 26624 | 624K| | 1083 (5)| 00:00:14 |
| 75 | SORT UNIQUE | | 26624 | 702K| 1896K| 1083 (5)| 00:00:14 |
|* 76 | HASH JOIN | | 26624 | 702K| | 865 (4)| 00:00:11 |
| 77 | INDEX FAST FULL SCAN | CDM_SRE_TO_IX | 26624 | 286K| | 20 (0)| 00:00:01 |
| 78 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
|* 79 | HASH JOIN | | 26624 | 988K| | 918 (4)| 00:00:12 |
| 80 | TABLE ACCESS FULL | CDM_SERV_EVT_RELATIONS | 26624 | 572K| | 73 (6)| 00:00:01 |
| 81 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 |
| 82 | TABLE ACCESS BY INDEX ROWID | SD_SERVICECALLS | 1 | 16 | | 1 (0)| 00:00:01 |
|* 83 | INDEX UNIQUE SCAN | SD_SER_ID_UIX | 1 | | | 0 (0)| 00:00:01 |
| 84 | TABLE ACCESS BY INDEX ROWID | SD_SER_4K5 | 1 | 10 | | 1 (0)| 00:00:01 |
|* 85 | INDEX UNIQUE SCAN | SD_SE5_PK | 1 | | | 0 (0)| 00:00:01 |
but when i apply parallel to MV the MV are getting hit
|
|
|
Re: Expplain plan output [message #553934 is a reply to message #553932] |
Wed, 09 May 2012 06:17 |
|
guddu_12
Messages: 227 Registered: April 2012 Location: UK
|
Senior Member |
|
|
i just made one MV to parallel
alter materialized view gsp_caresetting_1 parallel;
explain plan for
SELECT /*+ rewrite(gsp_status_1) */ ser_id,
STA_NAME,
CAT_NAME,
PER1_NAME,
REF_NAME,
CAL_NAME,
ESC_NAME,
SER_NAME,
ser_deadline,
ser_description,
sd_servicecalls.reg_created,
org_name1,
IMP_NAME,
CAR_NAME,
REL_NAME,
INC_NAME
FROM sd_servicecalls,
cdm_organizations,
GSP_STATUS,
GSP_CATEGORY,
GSP_REPORTINGUSER,
GSP_REFERENCE,
GSP_AFFECTEDUSER,
GSP_ESCALATION,
GSP_SERVICE,
GSP_IMPACT,
GSP_CARESETTING,
GSP_RELATED,
GSP_INCSUM
WHERE STA_SER_ID = ser_id
AND CAT_SER_ID = ser_id
AND PER1_SER_ID = ser_id
AND REF_SER_ID = ser_id
AND CAL_SER_ID = ser_id
AND ESC_SER_ID = ser_id
AND SER_SER_ID = ser_id
AND ser_caller_org = org_oid
AND IMP_SER_ID = ser_id
AND CAR_SER_ID = ser_id
AND REL_SER_ID = ser_id
AND INC_SER_ID = ser_id
ORDER BY SER_ID DESC;
plain output
Plan hash value: 2393644518
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31149 | 175M| | 21178 (3)| 00:04:15 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10022 | 31149 | 175M| | 21178 (3)| 00:04:15 | | | Q1,22 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 31149 | 175M| 486M| 21178 (3)| 00:04:15 | | | Q1,22 | PCWP | |
| 4 | PX RECEIVE | | 31149 | 175M| | 10451 (5)| 00:02:06 | | | Q1,22 | PCWP | |
| 5 | PX SEND RANGE | :TQ10021 | 31149 | 175M| | 10451 (5)| 00:02:06 | | | Q1,21 | P->P | RANGE |
|* 6 | HASH JOIN RIGHT OUTER BUFFERED | | 31149 | 175M| | 10451 (5)| 00:02:06 | | | Q1,21 | PCWP | |
| 7 | BUFFER SORT | | | | | | | | | Q1,21 | PCWC | |
| 8 | PX RECEIVE | | 6815 | 68150 | | 142 (3)| 00:00:02 | | | Q1,21 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10008 | 6815 | 68150 | | 142 (3)| 00:00:02 | | | | S->P | BROADCAST |
| 10 | TABLE ACCESS FULL | SD_SER_4K5 | 6815 | 68150 | | 142 (3)| 00:00:02 | | | | | |
|* 11 | HASH JOIN | | 31149 | 174M| | 10308 (5)| 00:02:04 | | | Q1,21 | PCWP | |
| 12 | BUFFER SORT | | | | | | | | | Q1,21 | PCWC | |
| 13 | PX RECEIVE | | 64092 | 1001K| | 632 (7)| 00:00:08 | | | Q1,21 | PCWP | |
| 14 | PX SEND BROADCAST | :TQ10009 | 64092 | 1001K| | 632 (7)| 00:00:08 | | | | S->P | BROADCAST |
| 15 | VIEW | index$_join$_014 | 64092 | 1001K| | 632 (7)| 00:00:08 | | | | | |
|* 16 | HASH JOIN | | | | | | | | | | | |
| 17 | INDEX FAST FULL SCAN | SD_SER_ID_UIX | 64092 | 1001K| | 179 (7)| 00:00:03 | | | | | |
| 18 | INDEX FAST FULL SCAN | SD_SER_PK | 64092 | 1001K| | 252 (5)| 00:00:04 | | | | | |
|* 19 | HASH JOIN | | 31149 | 174M| | 9674 (5)| 00:01:57 | | | Q1,21 | PCWP | |
| 20 | BUFFER SORT | | | | | | | | | Q1,21 | PCWC | |
| 21 | PX RECEIVE | | 36764 | 1256K| | 172 (5)| 00:00:03 | | | Q1,21 | PCWP | |
| 22 | PX SEND BROADCAST | :TQ10010 | 36764 | 1256K| | 172 (5)| 00:00:03 | | | | S->P | BROADCAST |
| 23 | TABLE ACCESS FULL | CDM_ORGANIZATIONS | 36764 | 1256K| | 172 (5)| 00:00:03 | | | | | |
|* 24 | HASH JOIN RIGHT OUTER | | 31154 | 173M| | 9500 (5)| 00:01:54 | | | Q1,21 | PCWP | |
| 25 | BUFFER SORT | | | | | | | | | Q1,21 | PCWC | |
| 26 | PX RECEIVE | | 11993 | 22M| | 1121 (5)| 00:00:14 | | | Q1,21 | PCWP | |
| 27 | PX SEND HASH | :TQ10011 | 11993 | 22M| | 1121 (5)| 00:00:14 | | | | S->P | HASH |
| 28 | VIEW | GSP_RELATEDEVENTS | 11993 | 22M| | 1121 (5)| 00:00:14 | | | | | |
| 29 | SORT GROUP BY | | 11993 | 714K| 1720K| 1121 (5)| 00:00:14 | | | | | |
|* 30 | HASH JOIN | | 11993 | 714K| | 934 (5)| 00:00:12 | | | | | |
|* 31 | HASH JOIN | | 11993 | 527K| | 90 (13)| 00:00:02 | | | | | |
| 32 | VIEW | GSP_EVENTS | 6759 | 151K| | 14 (29)| 00:00:01 | | | | | |
| 33 | SORT UNIQUE | | 6759 | 94626 | | 14 (29)| 00:00:01 | | | | | |
| 34 | PARTITION RANGE ALL | | 6759 | 94626 | | 11 (10)| 00:00:01 | 1 | 8 | | | |
| 35 | MAT_VIEW REWRITE ACCESS FULL | GSP_EVENTS_1 | 6759 | 94626 | | 11 (10)| 00:00:01 | 1 | 8 | | | |
| 36 | TABLE ACCESS FULL | CDM_SERV_EVT_RELATIONS | 26624 | 572K| | 73 (6)| 00:00:01 | | | | | |
| 37 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 1001K| | 837 (3)| 00:00:11 | | | | | |
| 38 | PX RECEIVE | | 31154 | 113M| | 8378 (5)| 00:01:41 | | | Q1,21 | PCWP | |
| 39 | PX SEND HASH | :TQ10020 | 31154 | 113M| | 8378 (5)| 00:01:41 | | | Q1,20 | P->P | HASH |
| 40 | BUFFER SORT | | 31149 | 175M| | | | | | Q1,20 | PCWP | |
|* 41 | HASH JOIN | | 31154 | 113M| | 8378 (5)| 00:01:41 | | | Q1,20 | PCWP | |
| 42 | PX RECEIVE | | 64054 | 31M| | 29 (35)| 00:00:01 | | | Q1,20 | PCWP | |
| 43 | PX SEND HASH | :TQ10018 | 64054 | 31M| | 29 (35)| 00:00:01 | | | Q1,18 | P->P | HASH |
| 44 | VIEW | GSP_IMPACT | 64054 | 31M| | 29 (35)| 00:00:01 | | | Q1,18 | PCWP | |
| 45 | SORT UNIQUE | | 64054 | 875K| 3032K| 29 (35)| 00:00:01 | | | Q1,18 | PCWP | |
| 46 | PX RECEIVE | | 64054 | 875K| | 20 (5)| 00:00:01 | | | Q1,18 | PCWP | |
| 47 | PX SEND HASH | :TQ10015 | 64054 | 875K| | 20 (5)| 00:00:01 | | | Q1,15 | P->P | HASH |
| 48 | PX BLOCK ITERATOR | | 64054 | 875K| | 20 (5)| 00:00:01 | 1 | 8 | Q1,15 | PCWC | |
| 49 | MAT_VIEW REWRITE ACCESS FULL | GSP_IMPACT_1 | 64054 | 875K| | 20 (5)| 00:00:01 | 1 | 8 | Q1,15 | PCWP | |
| 50 | PX RECEIVE | | 31154 | 98M| | 8346 (5)| 00:01:41 | | | Q1,20 | PCWP | |
| 51 | PX SEND HASH | :TQ10019 | 31154 | 98M| | 8346 (5)| 00:01:41 | | | Q1,19 | P->P | HASH |
| 52 | BUFFER SORT | | 31149 | 175M| | | | | | Q1,19 | PCWP | |
|* 53 | HASH JOIN | | 31154 | 98M| | 8346 (5)| 00:01:41 | | | Q1,19 | PCWP | |
| 54 | BUFFER SORT | | | | | | | | | Q1,19 | PCWC | |
| 55 | PX RECEIVE | | 64092 | 312K| | 32 (4)| 00:00:01 | | | Q1,19 | PCWP | |
| 56 | PX SEND BROADCAST | :TQ10006 | 64092 | 312K| | 32 (4)| 00:00:01 | | | | S->P | BROADCAST |
| 57 | INDEX FAST FULL SCAN | SD_SER_ID_UIX | 64092 | 312K| | 32 (4)| 00:00:01 | | | | | |
|* 58 | HASH JOIN | | 31154 | 98M| | 8311 (5)| 00:01:40 | | | Q1,19 | PCWP | |
| 59 | BUFFER SORT | | | | | | | | | Q1,19 | PCWC | |
| 60 | PX RECEIVE | | 64069 | 10M| | 499 (9)| 00:00:06 | | | Q1,19 | PCWP | |
| 61 | PX SEND HASH | :TQ10007 | 64069 | 10M| | 499 (9)| 00:00:06 | | | | S->P | HASH |
| 62 | VIEW | GSP_AFFECTEDUSER | 64069 | 10M| | 499 (9)| 00:00:06 | | | | | |
| 63 | SORT UNIQUE | | 64069 | 1188K| 3544K| 499 (9)| 00:00:06 | | | | | |
| 64 | PARTITION RANGE ALL | | 64069 | 1188K| | 84 (9)| 00:00:02 | 1 | 8 | | | |
| 65 | MAT_VIEW REWRITE ACCESS FULL | GSP_AFFECTEDUSER_1 | 64069 | 1188K| | 84 (9)| 00:00:02 | 1 | 8 | | | |
|* 66 | HASH JOIN | | 31154 | 93M| | 7809 (5)| 00:01:34 | | | Q1,19 | PCWP | |
| 67 | PX RECEIVE | | 64086 | 10M| | 32 (35)| 00:00:01 | | | Q1,19 | PCWP | |
| 68 | PX SEND HASH | :TQ10016 | 64086 | 10M| | 32 (35)| 00:00:01 | | | Q1,16 | P->P | HASH |
| 69 | VIEW | GSP_REPORTINGUSER | 64086 | 10M| | 32 (35)| 00:00:01 | | | Q1,16 | PCWP | |
| 70 | SORT UNIQUE | | 64086 | 1189K| 3544K| 32 (35)| 00:00:01 | | | Q1,16 | PCWP | |
| 71 | PX RECEIVE | | 64086 | 1189K| | 23 (9)| 00:00:01 | | | Q1,16 | PCWP | |
| 72 | PX SEND HASH | :TQ10013 | 64086 | 1189K| | 23 (9)| 00:00:01 | | | Q1,13 | P->P | HASH |
| 73 | PX BLOCK ITERATOR | | 64086 | 1189K| | 23 (9)| 00:00:01 | 1 | 8 | Q1,13 | PCWC | |
| 74 | MAT_VIEW REWRITE ACCESS FULL | GSP_REPORTINGUSER_1 | 64086 | 1189K| | 23 (9)| 00:00:01 | 1 | 8 | Q1,13 | PCWP | |
| 75 | PX RECEIVE | | 31154 | 88M| | 7775 (4)| 00:01:34 | | | Q1,19 | PCWP | |
| 76 | PX SEND HASH | :TQ10017 | 31154 | 88M| | 7775 (4)| 00:01:34 | | | Q1,17 | P->P | HASH |
|* 77 | HASH JOIN | | 31154 | 88M| | 7775 (4)| 00:01:34 | | | Q1,17 | PCWP | |
| 78 | BUFFER SORT | | | | | | | | | Q1,17 | PCWC | |
| 79 | PX RECEIVE | | 64092 | 5382K| | 285 (15)| 00:00:04 | | | Q1,17 | PCWP | |
| 80 | PX SEND BROADCAST | :TQ10000 | 64092 | 5382K| | 285 (15)| 00:00:04 | | | | S->P | BROADCAST |
| 81 | VIEW | GSP_REFERENCE | 64092 | 5382K| | 285 (15)| 00:00:04 | | | | | |
| 82 | SORT UNIQUE | | 64092 | 312K| 2024K| 285 (15)| 00:00:04 | | | | | |
| 83 | PARTITION RANGE ALL | | 64092 | 312K| | 54 (13)| 00:00:01 | 1 | 8 | | | |
| 84 | MAT_VIEW REWRITE ACCESS FULL | GSP_REFERENCE_1 | 64092 | 312K| | 54 (13)| 00:00:01 | 1 | 8 | | | |
|* 85 | HASH JOIN | | 31154 | 85M| | 7487 (4)| 00:01:30 | | | Q1,17 | PCWP | |
| 86 | BUFFER SORT | | | | | | | | | Q1,17 | PCWC | |
| 87 | PX RECEIVE | | 64092 | 1064K| | 302 (14)| 00:00:04 | | | Q1,17 | PCWP | |
| 88 | PX SEND BROADCAST | :TQ10001 | 64092 | 1064K| | 302 (14)| 00:00:04 | | | | S->P | BROADCAST |
| 89 | VIEW | GSP_ESCALATION | 64092 | 1064K| | 302 (14)| 00:00:04 | | | | | |
| 90 | SORT UNIQUE | | 64092 | 375K| 2024K| 302 (14)| 00:00:04 | | | | | |
| 91 | PARTITION RANGE ALL | | 64092 | 375K| | 59 (12)| 00:00:01 | 1 | 8 | | | |
| 92 | MAT_VIEW REWRITE ACCESS FULL | GSP_ESCALATION_1 | 64092 | 375K| | 59 (12)| 00:00:01 | 1 | 8 | | | |
|* 93 | HASH JOIN | | 31154 | 85M| | 7183 (4)| 00:01:27 | | | Q1,17 | PCWP | |
| 94 | BUFFER SORT | | | | | | | | | Q1,17 | PCWC | |
| 95 | PX RECEIVE | | 56035 | 9576K| | 2273 (3)| 00:00:28 | | | Q1,17 | PCWP | |
| 96 | PX SEND HASH | :TQ10002 | 56035 | 9576K| | 2273 (3)| 00:00:28 | | | | S->P | HASH |
| 97 | VIEW | GSP_SERVICE | 56035 | 9576K| | 2273 (3)| 00:00:28 | | | | | |
| 98 | SORT UNIQUE | | 56035 | 9576K| 19M| 2273 (3)| 00:00:28 | | | | | |
| 99 | PARTITION RANGE ALL | | 56035 | 9576K| | 71 (9)| 00:00:01 | 1 | 8 | | | |
| 100 | MAT_VIEW REWRITE ACCESS FULL | GSP_SERVICE_1 | 56035 | 9576K| | 71 (9)| 00:00:01 | 1 | 8 | | | |
|*101 | HASH JOIN | | 35634 | 91M| | 4907 (4)| 00:00:59 | | | Q1,17 | PCWP | |
| 102 | BUFFER SORT | | | | | | | | | Q1,17 | PCWC | |
| 103 | PX RECEIVE | | 34838 | 34M| | 317 (8)| 00:00:04 | | | Q1,17 | PCWP | |
| 104 | PX SEND HASH | :TQ10003 | 34838 | 34M| | 317 (8)| 00:00:04 | | | | S->P | HASH |
| 105 | VIEW | GSP_CATEGORY | 34838 | 34M| | 317 (8)| 00:00:04 | | | | | |
| 106 | SORT UNIQUE | | 34838 | 850K| 2488K| 317 (8)| 00:00:04 | | | | | |
| 107 | PARTITION RANGE ALL | | 34838 | 850K| | 44 (10)| 00:00:01 | 1 | 8 | | | |
| 108 | MAT_VIEW REWRITE ACCESS FULL | GSP_CATEGORY_1 | 34838 | 850K| | 44 (10)| 00:00:01 | 1 | 8 | | | |
|*109 | HASH JOIN | | 65420 | 103M| 10M| 4588 (3)| 00:00:56 | | | Q1,17 | PCWP | |
|*110 | HASH JOIN | | 65404 | 39M| | 2906 (3)| 00:00:35 | | | Q1,17 | PCWP | |
| 111 | BUFFER SORT | | | | | | | | | Q1,17 | PCWC | |
| 112 | PX RECEIVE | | 64092 | 6822K| | 837 (3)| 00:00:11 | | | Q1,17 | PCWP | |
| 113 | PX SEND HASH | :TQ10004 | 64092 | 6822K| | 837 (3)| 00:00:11 | | | | S->P | HASH |
| 114 | TABLE ACCESS FULL | SD_SERVICECALLS | 64092 | 6822K| | 837 (3)| 00:00:11 | | | | | |
| 115 | PX RECEIVE | | 65404 | 32M| | 2066 (2)| 00:00:25 | | | Q1,17 | PCWP | |
| 116 | PX SEND HASH | :TQ10014 | 65404 | 32M| | 2066 (2)| 00:00:25 | | | Q1,14 | P->P | HASH |
| 117 | VIEW | GSP_CARESETTING | 65404 | 32M| | 2066 (2)| 00:00:25 | | | Q1,14 | PCWP | |
| 118 | SORT UNIQUE | | 65404 | 32M| 68M| 2066 (2)| 00:00:25 | | | Q1,14 | PCWP | |
| 119 | PX RECEIVE | | 65404 | 32M| | 14 (8)| 00:00:01 | | | Q1,14 | PCWP | |
| 120 | PX SEND HASH | :TQ10012 | 65404 | 32M| | 14 (8)| 00:00:01 | | | Q1,12 | P->P | HASH |
| 121 | PX BLOCK ITERATOR | | 65404 | 32M| | 14 (8)| 00:00:01 | 1 | 8 | Q1,12 | PCWC | |
| 122 | MAT_VIEW REWRITE ACCESS FU| GSP_CARESETTING_1 | 65404 | 32M| | 14 (8)| 00:00:01 | 1 | 8 | Q1,12 | PCWP | |
| 123 | BUFFER SORT | | | | | | | | | Q1,17 | PCWC | |
| 124 | PX RECEIVE | | 64108 | 62M| | 379 (16)| 00:00:05 | | | Q1,17 | PCWP | |
| 125 | PX SEND HASH | :TQ10005 | 64108 | 62M| | 379 (16)| 00:00:05 | | | | S->P | HASH |
| 126 | VIEW | GSP_STATUS | 64108 | 62M| | 379 (16)| 00:00:05 | | | | | |
| 127 | SORT UNIQUE | | 64108 | 688K| 2536K| 379 (16)| 00:00:05 | | | | | |
| 128 | PARTITION RANGE ALL | | 64108 | 688K| | 62 (39)| 00:00:01 | 1 | 8 | | | |
|*129 | MAT_VIEW REWRITE ACCESS FULL| GSP_STATUS_1 | 64108 | 688K| | 62 (39)| 00:00:01 | 1 | 8 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
|
|
|
Goto Forum:
Current Time: Thu Jul 04 18:38:04 CDT 2024
|