Home » RDBMS Server » Performance Tuning » understanding a SQL plan (Oracle 11.2.0.2 Solaris 2.10)
understanding a SQL plan [message #561102] Wed, 18 July 2012 16:31 Go to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I have the following SQL plan. Can somebody break this down for me and let me know where improvements can be made? I see multiple table scans, which I think maybe an issue. In addition, I am looking at the constent gets at the end of the report and does this mean I had to go through 298880 to retrieve 75258


Execution Plan
----------------------------------------------------------
Plan hash value: 518188589

--------------------------------------------------------------------------------
-------------------------------------

| Id  | Operation                      | Name                       | Rows  | By
tes |TempSpc| Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
-------------------------------------

|   0 | SELECT STATEMENT               |                            | 80235 |
218M|       | 87089   (1)| 00:17:26 |

|*  1 |  HASH JOIN OUTER               |                            | 80235 |
218M|    44M| 87089   (1)| 00:17:26 |

|   2 |   NESTED LOOPS OUTER           |                            | 76542 |
43M|       | 10598   (1)| 00:02:08 |

|*  3 |    HASH JOIN RIGHT OUTER       |                            | 76542 |
41M|       | 10598   (1)| 00:02:08 |

|   4 |     TABLE ACCESS FULL          | BROKER_LIMITS              |  1181 |
98K|       |    17   (0)| 00:00:01 |

|*  5 |     HASH JOIN RIGHT OUTER      |                            | 76542 |
35M|  5760K| 10579   (1)| 00:02:07 |

|*  6 |      TABLE ACCESS FULL         | PARTY_IDENTIFICATION       | 51222 |  5
152K|       |  2551   (1)| 00:00:31 |

|*  7 |      HASH JOIN OUTER           |                            | 76542 |
27M|    20M|  6330   (1)| 00:01:16 |

|*  8 |       TABLE ACCESS FULL        | PARTY                      | 73633 |
19M|       |  2356   (1)| 00:00:29 |

|*  9 |       TABLE ACCESS FULL        | PARTY_IDENTIFICATION       | 76543 |  7
699K|       |  2551   (1)| 00:00:31 |

|  10 |    TABLE ACCESS BY INDEX ROWID | LANGUAGE                   |     1 |
32 |       |     0   (0)| 00:00:01 |

|* 11 |     INDEX UNIQUE SCAN          | PK_LANGUAGE                |     1 |
    |       |     0   (0)| 00:00:01 |

|  12 |   VIEW                         |                            | 78639 |
168M|       | 65859   (1)| 00:13:11 |

|  13 |    NESTED LOOPS OUTER          |                            | 78639 |
91M|       | 65859   (1)| 00:13:11 |

|* 14 |     HASH JOIN RIGHT OUTER      |                            | 78639 |
89M|    11M| 65859   (1)| 00:13:11 |

|* 15 |      TABLE ACCESS FULL         | ORGANIZATION_PTY_NM        | 78510 |
10M|       |  7681   (1)| 00:01:33 |

|* 16 |      HASH JOIN RIGHT OUTER     |                            | 75143 |
75M|    10M| 53847   (1)| 00:10:47 |

|* 17 |       TABLE ACCESS FULL        | ORGANIZATION_PTY_NM        | 75143 |  9
906K|       |  7681   (1)| 00:01:33 |

|* 18 |       HASH JOIN RIGHT OUTER    |                            | 75020 |
65M|    10M| 42343   (1)| 00:08:29 |

|* 19 |        TABLE ACCESS FULL       | ORGANIZATION_PTY_NM        | 74440 |  9
813K|       |  7681   (1)| 00:01:33 |

|* 20 |        HASH JOIN RIGHT OUTER   |                            | 75020 |
55M|  8152K| 31323   (1)| 00:06:16 |

|* 21 |         TABLE ACCESS FULL      | ORGANIZATION_PTY_NM        | 56755 |  7
482K|       |  7681   (1)| 00:01:33 |

|* 22 |         HASH JOIN RIGHT OUTER  |                            | 75020 |
46M|    10M| 20906   (1)| 00:04:11 |

|* 23 |          TABLE ACCESS FULL     | ORGANIZATION_PTY_NM        | 71998 |  9
491K|       |  7681   (1)| 00:01:33 |

|* 24 |          HASH JOIN RIGHT OUTER |                            | 75020 |
36M|  7824K| 10862   (1)| 00:02:11 |

|* 25 |           TABLE ACCESS FULL    | ORGANIZATION_PTY_NM        | 54461 |  7
179K|       |  7681   (1)| 00:01:33 |

|* 26 |           HASH JOIN RIGHT OUTER|                            | 75020 |
26M|       |  1419   (1)| 00:00:18 |

|  27 |            TABLE ACCESS FULL   | PARTY_LEGAL_CLASSIFICATION |    61 |  2
501 |       |     3   (0)| 00:00:01 |

|* 28 |            TABLE ACCESS FULL   | ORGANIZATION_PARTY         | 75020 |
24M|       |  1415   (1)| 00:00:17 |

|  29 |     TABLE ACCESS BY INDEX ROWID| COUNTRY                    |     1 |
27 |       |     0   (0)| 00:00:01 |

|* 30 |      INDEX UNIQUE SCAN         | PK_COUNTRY                 |     1 |
    |       |     0   (0)| 00:00:01 |

--------------------------------------------------------------------------------
-------------------------------------


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

   1 - access("P_P"."PARTY_ID"="P_OP"."PARTY_ID"(+))
   3 - access("P_P"."PARTY_ID"="P_BL"."COUNTERPARTY_BROKER_PARTY_ID"(+))
   5 - access("P_P"."PARTY_ID"="P_PI2"."PARTY_ID"(+))
   6 - filter("P_PI2"."PARTY_IDENTIFICATION_TYPE_CD"(+)=U'SPN')
   7 - access("P_P"."PARTY_ID"="P_PI"."PARTY_ID"(+))
   8 - filter("P_P"."PARTY_TYPE_IN"=U'O')
   9 - filter("P_PI"."PARTY_IDENTIFICATION_TYPE_CD"(+)=U'LGCY_PRTY_ID')
  11 - access("P_P"."PREFERRED_LANGUAGE_CD"="R_L"."LANGUAGE_CD"(+))
  14 - access("P_OPN2"."PARTY_ID"(+)="P_OP"."PARTY_ID")
  15 - filter("P_OPN2"."ORGANIZATION_PARTY_NM_TYPE_CD"(+)=U'SHORT_NM')
  16 - access("P_OPN3"."PARTY_ID"(+)="P_OP"."PARTY_ID")
  17 - filter("P_OPN3"."ORGANIZATION_PARTY_NM_TYPE_CD"(+)=U'UPPER_LONG_NM')
  18 - access("P_OPN1"."PARTY_ID"(+)="P_OP"."PARTY_ID")
  19 - filter("P_OPN1"."ORGANIZATION_PARTY_NM_TYPE_CD"(+)=U'PARTY_CD')
  20 - access("P_OPN6"."PARTY_ID"(+)="P_OP"."PARTY_ID")
  21 - filter("P_OPN6"."ORGANIZATION_PARTY_NM_TYPE_CD"(+)=U'STRIPPED_SHORT_NM')
  22 - access("P_OPN5"."PARTY_ID"(+)="P_OP"."PARTY_ID")
  23 - filter("P_OPN5"."ORGANIZATION_PARTY_NM_TYPE_CD"(+)=U'SORT_NM')
  24 - access("P_OPN4"."PARTY_ID"(+)="P_OP"."PARTY_ID")
  25 - filter("P_OPN4"."ORGANIZATION_PARTY_NM_TYPE_CD"(+)=U'BRIEF_NM')
  26 - access("P_PLC"."PARTY_LEGAL_CLASS_CD"(+)="P_OP"."PARTY_LEGAL_CLASS_CD")
  28 - filter(UPPER("LEGAL_ENTITY_IN")=U'Y' OR UPPER("LEGAL_ENTITY_IN")=U'N')
  30 - access("P_C"."COUNTRY_CD"(+)="P_OP"."INCORPORATION_COUNTRY_CD")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     298880  consistent gets
     282276  physical reads
      25872  redo size
   44403445  bytes sent via SQL*Net to client
      55551  bytes received via SQL*Net from client
       5019  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      75258  rows processed

SQL that generated plan

CREATE OR REPLACE VIEW PARTY_VIEWS.V_PARTY_DETAILS
(
    ORA_PARTY_ID,
    SYB_PARTY_ID,
    PARTY_NM,
    PARTY_TYPE_IN,
    PARTY_TYPE_NM,
    PARTY_STATUS_CD,
    PARTY_STATUS_NM,
    RISK_LEGAL_ID,
    PREFERRED_LANGUAGE_CD,
    PREFERRED_LANGUAGE_NM,
    PARTY_COMMENT,
    PARTY_AUDIT_CREATE_TS,
    PARTY_AUDIT_UPDATE_TS,
    PARTY_AUDIT_APP_TX,
    PARTY_AUDIT_APP_USER_TX,
    PARTY_LEGAL_CLASS_CD,
    ORGANIZATION_NM,
    INCORPORATION_DT,
    INCORPORATION_COUNTRY_CD,
    LEGAL_ENTITY_IN,
    ORG_AUDIT_CREATE_TS,
    ORG_AUDIT_UPDATE_TS,
    ORG_AUDIT_APP_TX,
    ORG_AUDIT_APP_USER_TX,
    APPROVED_BROKER_IN,
    PARTY_LEGAL_CLASS_NM,
    INCORPORATION_COUNTRY_NM,
    ORG_SPN,
    ORG_SHORT_NM,
    ORG_SORT_NM,
    ORG_BRIEF_NM,
    ORG_UPPER_LONG_NM,
    ORG_STRIPPED_SHORT_NM,
    ORG_PARTY_CD,
    TITLE_CD,
    FIRST_NM,
    MIDDLE_NM,
    LAST_NM,
    SUFFIX,
    INITIALS,
    GENDER,
    DATE_OF_BIRTH,
    DATE_OF_DEATH,
    RESIDENCY_COUNTRY_CD,
    DOMICILE_COUNTRY_CD,
    INDIV_AUDIT_CREATE_TS,
    INDIV_AUDIT_UPDATE_TS,
    INDIV_AUDIT_APP_TX,
    INDIV_AUDIT_APP_USER_TX,
    RESIDENCY_COUNTRY_NM,
    DOMICILE_COUNTRY_NM,
    INDIVIDUAL_SID,
    INDIV_NICK_NM
)
AS
SELECT /*+ ORDERED */
       P_P.PARTY_ID AS ORA_PARTY_ID,
       P_PI.PARTY_IDENTIFICATION_NB AS SYB_PARTY_ID,
       P_OP.ORGANIZATION_NM AS PARTY_NM,
       P_P.PARTY_TYPE_IN,
       'Organization' AS PARTY_TYPE_NM,
       P_P.PARTY_STATUS_CD,
       DECODE(P_P.PARTY_STATUS_CD, 'A', 'Active', 'I', 'Inactive', 'F', 'Frozen', 'T', 'Terminated') AS PARTY_STATUS_NM,
       P_P.RISK_LEGAL_ID,
       P_P.PREFERRED_LANGUAGE_CD,
       R_L.LANGUAGE_NM AS PREFERRED_LANGUAGE_NM,
       P_P.PARTY_COMMENT,
       P_P.AUDIT_CREATE_TS AS PARTY_AUDIT_CREATE_TS,
       P_P.AUDIT_UPDATE_TS AS PARTY_AUDIT_UPDATE_TS,
       P_P.AUDIT_APP_TX AS PARTY_AUDIT_APP_TX,
       P_P.AUDIT_APP_USER_TX AS PARTY_AUDIT_APP_USER_TX,
       P_OP.PARTY_LEGAL_CLASS_CD,
       P_OP.ORGANIZATION_NM,
       P_OP.INCORPORATION_DT,
       P_OP.INCORPORATION_COUNTRY_CD,
       P_OP.LEGAL_ENTITY_IN,
       P_OP.AUDIT_CREATE_TS AS ORG_AUDIT_CREATE_TS,
       P_OP.AUDIT_UPDATE_TS AS ORG_AUDIT_UPDATE_TS,
       P_OP.AUDIT_APP_TX AS ORG_AUDIT_APP_TX,
       P_OP.AUDIT_APP_USER_TX AS ORG_AUDIT_APP_USER_TX,
       CASE
           WHEN ( P_BL.BROKER_LIMIT_APRVL_STTS_CD = 'APP' AND
                  P_BL.EXPOSURE_TYPE_CD = 'MAR' AND
                  ( P_BL.JPM_OFFICE_CD = 'NY' OR P_BL.JPM_OFFICE_CD = 'CFNY' )
           ) THEN 'Y'
       END AS APPROVED_BROKER_IN,
       P_PLC.PARTY_LEGAL_CLASS_NM,
       P_C.COUNTRY_NM AS INCORPORATION_COUNTRY_NM,
       P_PI2.PARTY_IDENTIFICATION_NB AS ORG_SPN,
       P_OPN2.ORGANIZATION_PARTY_NM AS ORG_SHORT_NM,
       P_OPN5.ORGANIZATION_PARTY_NM AS ORG_SORT_NM,
       P_OPN4.ORGANIZATION_PARTY_NM AS ORG_BRIEF_NM,
       P_OPN3.ORGANIZATION_PARTY_NM AS ORG_UPPER_LONG_NM,
       P_OPN6.ORGANIZATION_PARTY_NM AS ORG_STRIPPED_SHORT_NM,
       P_OPN1.ORGANIZATION_PARTY_NM AS ORG_PARTY_CD,
       /* Individual Details */
       NULL AS TITLE_CD,
       NULL AS FIRST_NM,
       NULL AS MIDDLE_NM,
       NULL AS LAST_NM,
       NULL AS SUFFIX,
       NULL AS INITIALS,
       NULL AS GENDER,
       NULL AS DATE_OF_BIRTH,
       NULL AS DATE_OF_DEATH,
       NULL AS RESIDENCY_COUNTRY_CD,
       NULL AS DOMICILE_COUNTRY_CD,
       NULL AS INDIV_AUDIT_CREATE_TS,
       NULL AS INDIV_AUDIT_UPDATE_TS,
       NULL AS INDIV_AUDIT_APP_TX,
       NULL AS INDIV_AUDIT_APP_USER_TX,
       NULL AS RESIDENCY_COUNTRY_NM,
       NULL AS DOMICILE_COUNTRY_NM,
       NULL AS INDIVIDUAL_SID,
       NULL AS INDIV_NICK_NM
  FROM
       PARTY.PARTY P_P
       LEFT OUTER JOIN PARTY.PARTY_IDENTIFICATION P_PI ON P_P.PARTY_ID = P_PI.PARTY_ID
            AND P_PI.PARTY_IDENTIFICATION_TYPE_CD = 'LGCY_PRTY_ID'
       LEFT OUTER JOIN PARTY.PARTY_IDENTIFICATION P_PI2 ON P_P.PARTY_ID = P_PI2.PARTY_ID
            AND P_PI2.PARTY_IDENTIFICATION_TYPE_CD = 'SPN'
       LEFT OUTER JOIN PARTY.BROKER_LIMITS P_BL ON P_P.PARTY_ID = P_BL.COUNTERPARTY_BROKER_PARTY_ID
       LEFT OUTER JOIN REFERENCE.LANGUAGE R_L ON P_P.PREFERRED_LANGUAGE_CD = R_L.LANGUAGE_CD
       LEFT OUTER JOIN (PARTY.ORGANIZATION_PARTY P_OP
           LEFT OUTER JOIN PARTY.ORGANIZATION_PTY_NM P_OPN1 ON P_OPN1.PARTY_ID = P_OP.PARTY_ID
                AND P_OPN1.ORGANIZATION_PARTY_NM_TYPE_CD = 'PARTY_CD'
           LEFT OUTER JOIN PARTY.ORGANIZATION_PTY_NM P_OPN2 ON P_OPN2.PARTY_ID = P_OP.PARTY_ID
                AND P_OPN2.ORGANIZATION_PARTY_NM_TYPE_CD = 'SHORT_NM'
           LEFT OUTER JOIN PARTY.ORGANIZATION_PTY_NM P_OPN3 ON P_OPN3.PARTY_ID = P_OP.PARTY_ID
                AND P_OPN3.ORGANIZATION_PARTY_NM_TYPE_CD = 'UPPER_LONG_NM'
           LEFT OUTER JOIN PARTY.ORGANIZATION_PTY_NM P_OPN4 ON P_OPN4.PARTY_ID = P_OP.PARTY_ID
                AND P_OPN4.ORGANIZATION_PARTY_NM_TYPE_CD = 'BRIEF_NM'
           LEFT OUTER JOIN PARTY.ORGANIZATION_PTY_NM P_OPN5 ON P_OPN5.PARTY_ID = P_OP.PARTY_ID
                AND P_OPN5.ORGANIZATION_PARTY_NM_TYPE_CD = 'SORT_NM'
           LEFT OUTER JOIN PARTY.ORGANIZATION_PTY_NM P_OPN6 ON P_OPN6.PARTY_ID = P_OP.PARTY_ID
                AND P_OPN6.ORGANIZATION_PARTY_NM_TYPE_CD = 'STRIPPED_SHORT_NM'
           LEFT OUTER JOIN REFERENCE.PARTY_LEGAL_CLASSIFICATION P_PLC ON P_PLC.PARTY_LEGAL_CLASS_CD = P_OP.PARTY_LEGAL_CLASS_CD
           LEFT OUTER JOIN REFERENCE.COUNTRY P_C ON P_C.COUNTRY_CD = P_OP.INCORPORATION_COUNTRY_CD)
         ON P_P.PARTY_ID = P_OP.PARTY_ID
WHERE
       P_P.PARTY_TYPE_IN = 'O'
--
UNION ALL
--
SELECT
/*  Lists  details for the Individual Party */
       P_P.PARTY_ID AS ORA_PARTY_ID,
       P_PI.PARTY_IDENTIFICATION_NB AS SYB_PARTY_ID,
       TRIM(NVL2(P_IP.TITLE_CD, TRIM(P_IP.TITLE_CD) || ' ', NULL) || NVL2(P_IP.FIRST_NM, TRIM(P_IP.FIRST_NM) || ' ', NULL) ||   NVL2(P_IP.MIDDLE_NM, TRIM(P_IP.MIDDLE_NM) || ' ', NULL) || NVL2(P_IP.LAST_NM, TRIM(P_IP.LAST_NM) || ' ', NULL) || NVL2(P_IP.SUFFIX, TRIM(P_IP.SUFFIX), NULL)) AS PARTY_NM,
       P_P.PARTY_TYPE_IN,
       'Individual' AS PARTY_TYPE_NM,
       P_P.PARTY_STATUS_CD,
       DECODE(P_P.PARTY_STATUS_CD, 'A', 'Active', 'I', 'Inactive', 'F', 'Frozen', 'T', 'Terminated') AS PARTY_STATUS_NM,
       P_P.RISK_LEGAL_ID,
       P_P.PREFERRED_LANGUAGE_CD,
       R_L.LANGUAGE_NM AS PREFERRED_LANGUAGE_NM,
       P_P.PARTY_COMMENT,
       P_P.AUDIT_CREATE_TS AS PARTY_AUDIT_CREATE_TS,
       P_P.AUDIT_UPDATE_TS AS PARTY_AUDIT_UPDATE_TS,
       P_P.AUDIT_APP_TX AS PARTY_AUDIT_APP_TX,
       P_P.AUDIT_APP_USER_TX AS PARTY_AUDIT_APP_USER_TX,
       NULL AS PARTY_LEGAL_CLASS_CD,
       NULL AS ORGANIZATION_NM,
       NULL AS INCORPORATION_DT,
       NULL AS INCORPORATION_COUNTRY_CD,
       NULL AS LEGAL_ENTITY_IN,
       NULL AS ORG_AUDIT_CREATE_TS,
       NULL AS ORG_AUDIT_UPDATE_TS,
       NULL AS ORG_AUDIT_APP_TX,
       NULL AS ORG_AUDIT_APP_USER_TX,
       NULL AS APPROVED_BROKER_IN,
       NULL AS PARTY_LEGAL_CLASS_NM,
       NULL AS INCORPORATION_COUNTRY_NM,
       NULL AS ORG_SPN,
       NULL AS ORG_SHORT_NM,
       NULL AS ORG_SORT_NM,
       NULL AS ORG_BRIEF_NM,
       NULL AS ORG_UPPER_LONG_NM,
       NULL AS ORG_STRIPPED_SHORT_NM,
       NULL AS ORG_PARTY_CD,
/* Individual Details */
       P_IP.TITLE_CD,
       P_IP.FIRST_NM,
       P_IP.MIDDLE_NM,
       P_IP.LAST_NM,
       P_IP.SUFFIX,
       P_IP.INITIALS,
       P_IP.GENDER,
       P_IP.DATE_OF_BIRTH,
       P_IP.DATE_OF_DEATH,
       P_IP.RESIDENCY_COUNTRY_CD,
       P_IP.DOMICILE_COUNTRY_CD,
       P_IP.AUDIT_CREATE_TS AS INDIV_AUDIT_CREATE_TS,
       P_IP.AUDIT_UPDATE_TS AS INDIV_AUDIT_UPDATE_TS,
       P_IP.AUDIT_APP_TX AS INDIV_AUDIT_APP_TX,
       P_IP.AUDIT_APP_USER_TX AS INDIV_AUDIT_APP_USER_TX,
       P_C2.COUNTRY_NM AS RESIDENCY_COUNTRY_NM,
       P_C.COUNTRY_NM AS DOMICILE_COUNTRY_NM,
       P_PI3.PARTY_IDENTIFICATION_NB AS INDIVIDUAL_SID,
       P_IPN.INDIVIDUAL_PARTY_NM AS INDIV_NICK_NM
  FROM
       PARTY.PARTY P_P
       LEFT OUTER JOIN PARTY.PARTY_IDENTIFICATION P_PI ON P_P.PARTY_ID = P_PI.PARTY_ID
            AND P_PI.PARTY_IDENTIFICATION_TYPE_CD = 'LGCY_PERSON_ID'
       LEFT OUTER JOIN PARTY.PARTY_IDENTIFICATION P_PI2 ON P_P.PARTY_ID = P_PI2.PARTY_ID
            AND P_PI2.PARTY_IDENTIFICATION_TYPE_CD = 'SPN'
       LEFT OUTER JOIN PARTY.PARTY_IDENTIFICATION P_PI3 ON P_P.PARTY_ID = P_PI3.PARTY_ID
            AND P_PI3.PARTY_IDENTIFICATION_TYPE_CD = 'JPM_SID'
       LEFT OUTER JOIN REFERENCE.LANGUAGE R_L ON P_P.PREFERRED_LANGUAGE_CD = R_L.LANGUAGE_CD
       LEFT OUTER JOIN (PARTY.INDIVIDUAL_PARTY P_IP
           LEFT OUTER JOIN PARTY.INDIVIDUAL_PARTY_NM P_IPN ON P_IPN.PARTY_ID = P_IP.PARTY_ID
                AND P_IPN.INDIVIDUAL_PARTY_NM_TYPE_CD = 'NICK_NM'
           LEFT OUTER JOIN REFERENCE.COUNTRY P_C ON P_C.COUNTRY_CD = P_IP.DOMICILE_COUNTRY_CD
           LEFT OUTER JOIN REFERENCE.COUNTRY P_C2 ON P_C2.COUNTRY_CD = P_IP.RESIDENCY_COUNTRY_CD)
         ON P_P.PARTY_ID = P_IP.PARTY_ID
WHERE
       P_P.PARTY_TYPE_IN = 'I'
ORDER BY
          1 ASC,
          3 ASC,
          4 ASC,
          6 ASC

[Updated on: Wed, 18 July 2012 18:20]

Report message to a moderator

Re: understanding a SQL plan [message #561191 is a reply to message #561102] Thu, 19 July 2012 06:35 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It'd help if you formatted the plan so each step appears on a single line. It's hard to read at the moment.
Does every join have to be an outer? Cause that really limits your options.

And what is the actual query that run against the view?
Re: understanding a SQL plan [message #561248 is a reply to message #561102] Thu, 19 July 2012 16:16 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Thanks for the reply.

BY looking at the plan above I see multiple tables scans on one table (ORGANIZATION_PTY_NM) is this caused by the outer joins? How can one tell where its coming from based on the explain plan output. If caused by the outer joins is there a way to re-write this query to not do scans?



Re: understanding a SQL plan [message #561262 is a reply to message #561248] Thu, 19 July 2012 17:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Of course it's the outer joins, what else could it be?
You outer join to the table multiple times, it appears in the plan multiple times.
The predicate information proves it, but in this case there's really no need to look at that to know.

Alternatives would depend on the data model, which we know nothing about.
Re: understanding a SQL plan [message #561554 is a reply to message #561262] Mon, 23 July 2012 17:07 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
You consistent gets are not all that bad, it is the physical reads that are slowing you down. In the following example the query with the more physical reads runs 9 times slower (.958344163 seconds per execution) then that query processing more from memory (.109996108 seconds per execution). The AVG_BUFFERS are about 50% more for the first query so I would expect it to run about 50% slower, but it runs about 9 times slower.
ENWEBP1P > @v$sqlarea_buffer_gets.sql
Enter value for sql_to_search_for: %

BUFFER_GETS DISK_READS EXECUTIONS HASH_VALUE AVG_BUFFERS AVG_SECONDS SQL_TEXT
----------- ---------- ---------- ---------- ----------- ----------- --------
  502012810  501642577      14697  253992633  34157.5022  .958344163 /* load
 1062961656        126      46119 2405608554  23048.2373  .109996108 /* selec
                       ----------
sum                        137756

ENWEBP1P > list
  1  select buffer_gets,disk_reads,executions,
  2  hash_value,buffer_gets/executions avg_buffers,
  3  elapsed_time/executions/1000000 Avg_seconds,
  4  sql_text
  5  from v$sqlarea
  6  where executions>0
  7  and upper(sql_text)
  8  like upper('%&sql_to_search_for%')
  9  and buffer_gets > 100000000
 10* order by buffer_gets
Re: understanding a SQL plan [message #561556 is a reply to message #561554] Mon, 23 July 2012 17:31 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I usually tackle the physical reads by adding indexes or caching the objects. First you have to identify the objects that are being physically read and most likely these objects are going to be the tables identified by the plan as having "TABLE ACCESS FULL" because tables are cached less time than indexes because they are larger. This can be done by taking snapshots of dba_hist_seg_stat as in the following example.
ENDOCP1P > @io605.sql

INSTANCE  IO_PER_MINUTE STATISTIC_NAME          OBJECT_NAME
--------- ------------- ----------------------- -----------------------
NDOCP3               12 physical write requests NFLPROD.NFL_JOB_STATUS.
NDOCP3               12 physical writes         NFLPROD.NFL_JOB_STATUS.
NDOCP3               36 physical writes         NFLPROD.NFL_CONTENT_S.
NDOCP3               36 physical write requests NFLPROD.NFL_CONTENT_S.
NDOCP2               96 physical read requests  NFLPROD.NFL_CONTENT_S.
NDOCP2              252 physical read requests  NFLPROD.DM_SYSOBJECT_S.
NDOCP2              636 physical reads          NFLPROD.DM_SYSOBJECT_S.
NDOCP3             1080 physical read requests  NFLPROD.NFL_CONTENT_S.
NDOCP3             4104 physical read requests  NFLPROD.DM_SYSOBJECT_R.
NDOCP2            12072 physical reads direct   NFLPROD.NFL_CONTENT_S.
NDOCP2            12072 physical reads          NFLPROD.NFL_CONTENT_S.
NDOCP3           136560 physical reads          NFLPROD.NFL_CONTENT_S.
NDOCP3           136560 physical reads direct   NFLPROD.NFL_CONTENT_S.
NDOCP3           523260 physical reads          NFLPROD.DM_SYSOBJECT_R.
NDOCP3           523260 physical reads direct   NFLPROD.DM_SYSOBJECT_R.
          -------------
sum             1350048

The full io605.sql follows:
set termout off
set lines 160
break on report
col instance for a9
compute sum of io_per_minute break on report
drop table gv$segment_statistics1;
drop table gv$segment_statistics2;
column statistic_name format a23
create table gv$segment_statistics1 as select * from gv$segment_statistics 
where upper(statistic_name) like '%PHYSICAL%' and owner not like 'E%';
execute dbms_lock.sleep(5);
create table gv$segment_statistics2 as select * from gv$segment_statistics 
where upper(statistic_name) like '%PHYSICAL%' and owner not like 'E%';
set wrap off
set termout on
select i.instance_name instance,(b.value-a.value)*12 IO_PER_MINUTE,
a.statistic_name,
a.owner||'.'||a.object_name||'.'||a.subobject_name object_name
from gv$segment_statistics2 b,gv$segment_statistics1 a,gv$instance i
where a.statistic_name=b.statistic_name 
and a.inst_id=i.inst_id and b.inst_id=i.inst_id
and a.inst_id=b.inst_id and a.object_type=b.object_type
and a.owner||'.'||a.object_name||'.'||a.subobject_name=
b.owner||'.'||b.object_name||'.'||b.subobject_name
and upper(a.statistic_name) like '%PHYSICAL%'
and a.object_name not like 'GV$SEGMENT_STATISTICS%'
and b.value-a.value > 0
order by b.value-a.value;
set termout off
drop table gv$segment_statistics1 purge;
drop table gv$segment_statistics2 purge;
set termout on 

Previous Topic: Undo advice.
Next Topic: Oracle 11g high memory usage dbw0 and dbw1
Goto Forum:
  


Current Time: Thu Mar 28 03:08:33 CDT 2024