Home » RDBMS Server » Performance Tuning » Expplain plan output (10.1.0.1)
Expplain plan output [message #553904] Wed, 09 May 2012 04:19 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 #553922 is a reply to message #553919] Wed, 09 May 2012 05:48 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please repost the code and explain plans using code tags as described here: How to use [code] tags and make your code easier to read?
What you've posted above isn't readable.
Re: Expplain plan output [message #553932 is a reply to message #553922] Wed, 09 May 2012 06:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 |        |      |            |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Re: Expplain plan output [message #553955 is a reply to message #553934] Wed, 09 May 2012 07:29 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I find that the DBMS_MVIEW.EXPLAIN_REWRITE procedure is useful for diagnosing query re-write issues.
Previous Topic: how to set the sql_profile
Next Topic: Slow İnsert Fast Select problem
Goto Forum:
  


Current Time: Fri Mar 29 04:23:36 CDT 2024