Home » RDBMS Server » Performance Tuning » Query tuning help (11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production)
Query tuning help [message #563227] Fri, 10 August 2012 04:29 Go to next message
knw15pwr
Messages: 130
Registered: March 2010
Senior Member
Hello All,

The below query takes more than 30 minutes to return data.
All the objects used are views. There is no direct reference to any table.
The views with _mnth_ have data for 7 distinct months.
The base table for all the views have a composite PK on the columns AR_ID (or ACCT_AR_ID),MSRMNT_PRD_ID

I need the order by, as the query is part of informatica code, and the order by helps in the further processing.

SELECT   ac.ar_id AS acct_ar_id, m.msrmnt_prd_dt AS msrmnt_prd_dt 
--removed the rest of column list to reduce size of code.
    FROM edxf.ar_rsrv_mnth_v ac,
         edxf.crdt_acct_mnth_v c,
         edxf.crdt_acct_v ca,
         (SELECT msrmnt_prd_id, msrmnt_prd_dt
            FROM edxf.msrmnt_prd_v
           WHERE msrmnt_prd_id =
                    (SELECT MAX (msrmnt_prd_id)
                       FROM edxf.ar_rsrv_mnth_v
                      WHERE msrmnt_prd_mnth =
                               TO_CHAR (ADD_MONTHS (TO_DATE ('20120731','yyyymmdd'),-1),'YYYYMM'))) m
   WHERE ac.src_syst_code = 'ABC'
     AND ac.msrmnt_prd_id = m.msrmnt_prd_id
     AND ac.ar_id = c.acct_ar_id
     AND ac.msrmnt_prd_mnth = c.msrmnt_prd_mnth
     AND ac.ar_id = ca.acct_ar_id(+)
     AND (ca.end_dt IS NULL OR ca.end_dt > m.msrmnt_prd_dt)
ORDER BY ac.ar_id


Explain Plan :
PLAN_TABLE_OUTPUT

Plan hash value: 124769705
 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                            |    25 |  2800 | 11436   (1)| 00:02:18 |       |       |        |      |            |
|   1 |  PX COORDINATOR                                              |                            |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (ORDER)                                         | :TQ20009                   |    25 |  2800 | 11436   (1)| 00:02:18 |       |       |  Q2,09 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY                                             |                            |    25 |  2800 | 11436   (1)| 00:02:18 |       |       |  Q2,09 | PCWP |            |
|   4 |     PX RECEIVE                                               |                            |       |       |            |          |       |       |  Q2,09 | PCWP |            |
|   5 |      PX SEND RANGE                                           | :TQ20008                   |       |       |            |          |       |       |  Q2,08 | P->P | RANGE      |
|   6 |       BUFFER SORT                                            |                            |    25 |  2800 |            |          |       |       |  Q2,08 | PCWP |            |
|   7 |        NESTED LOOPS                                          |                            |       |       |            |          |       |       |  Q2,08 | PCWP |            |
|   8 |         NESTED LOOPS                                         |                            |    25 |  2800 | 11402   (1)| 00:02:17 |       |       |  Q2,08 | PCWP |            |
|*  9 |          FILTER                                              |                            |       |       |            |          |       |       |  Q2,08 | PCWC |            |
|  10 |           NESTED LOOPS OUTER                                 |                            |    25 |  2600 | 11402   (1)| 00:02:17 |       |       |  Q2,08 | PCWP |            |
|* 11 |            HASH JOIN                                         |                            |    30 |  2850 | 11402   (1)| 00:02:17 |       |       |  Q2,08 | PCWP |            |
|  12 |             PX RECEIVE                                       |                            |  3274 |   239K| 11398   (1)| 00:02:17 |       |       |  Q2,08 | PCWP |            |
|  13 |              PX SEND HASH                                    | :TQ20006                   |  3274 |   239K| 11398   (1)| 00:02:17 |       |       |  Q2,06 | P->P | HASH       |
|* 14 |               HASH JOIN BUFFERED                             |                            |  3274 |   239K| 11398   (1)| 00:02:17 |       |       |  Q2,06 | PCWP |            |
|  15 |                JOIN FILTER CREATE                            | :BF0000                    |  7671 | 84381 |     2   (0)| 00:00:01 |       |       |  Q2,06 | PCWP |            |
|  16 |                 PX RECEIVE                                   |                            |  7671 | 84381 |     2   (0)| 00:00:01 |       |       |  Q2,06 | PCWP |            |
|  17 |                  PX SEND HASH                                | :TQ20003                   |  7671 | 84381 |     2   (0)| 00:00:01 |       |       |  Q2,03 | P->P | HASH       |
|  18 |                   PX BLOCK ITERATOR                          |                            |  7671 | 84381 |     2   (0)| 00:00:01 |       |       |  Q2,03 | PCWC |            |
|  19 |                    TABLE ACCESS STORAGE FULL                 | MSRMNT_PRD                 |  7671 | 84381 |     2   (0)| 00:00:01 |       |       |  Q2,03 | PCWP |            |
|  20 |                PX RECEIVE                                    |                            | 39291 |  2455K| 11396   (1)| 00:02:17 |       |       |  Q2,06 | PCWP |            |
|  21 |                 PX SEND HASH                                 | :TQ20004                   | 39291 |  2455K| 11396   (1)| 00:02:17 |       |       |  Q2,04 | P->P | HASH       |
|  22 |                  JOIN FILTER USE                             | :BF0000                    | 39291 |  2455K| 11396   (1)| 00:02:17 |       |       |  Q2,04 | PCWP |            |
|* 23 |                   HASH JOIN                                  |                            | 39291 |  2455K| 11396   (1)| 00:02:17 |       |       |  Q2,04 | PCWP |            |
|  24 |                    PART JOIN FILTER CREATE                   | :BF0001                    |  2949 | 26541 |     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
|  25 |                     PX RECEIVE                               |                            |  2949 | 26541 |     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
|  26 |                      PX SEND BROADCAST                       | :TQ20001                   |  2949 | 26541 |     2   (0)| 00:00:01 |       |       |  Q2,01 | P->P | BROADCAST  |
|  27 |                       PX BLOCK ITERATOR                      |                            |  2949 | 26541 |     2   (0)| 00:00:01 |       |       |  Q2,01 | PCWC |            |
|* 28 |                        TABLE ACCESS STORAGE FULL             | SRC_SYST_CLNDR_DAY         |  2949 | 26541 |     2   (0)| 00:00:01 |       |       |  Q2,01 | PCWP |            |
|* 29 |                    HASH JOIN                                 |                            |  1071K|    56M| 11393   (1)| 00:02:17 |       |       |  Q2,04 | PCWP |            |
|  30 |                     PX RECEIVE                               |                            |   153K|  5528K|    29   (4)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
|  31 |                      PX SEND BROADCAST                       | :TQ20002                   |   153K|  5528K|    29   (4)| 00:00:01 |       |       |  Q2,02 | P->P | BROADCAST  |
|* 32 |                       HASH JOIN                              |                            |   153K|  5528K|    29   (4)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
|  33 |                        PX RECEIVE                            |                            |     1 |    24 |     2   (0)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
|  34 |                         PX SEND BROADCAST                    | :TQ20000                   |     1 |    24 |     2   (0)| 00:00:01 |       |       |  Q2,00 | P->P | BROADCAST  |
|  35 |                          NESTED LOOPS                        |                            |     1 |    24 |     2   (0)| 00:00:01 |       |       |  Q2,00 | PCWP |            |
|  36 |                           PX BLOCK ITERATOR                  |                            |       |       |            |          |       |       |  Q2,00 | PCWC |            |
|* 37 |                            TABLE ACCESS STORAGE FULL         | MSRMNT_PRD                 |     1 |    18 |     2   (0)| 00:00:01 |       |       |  Q2,00 | PCWP |            |
|  38 |                             SORT AGGREGATE                   |                            |     1 |    60 |            |          |       |       |  Q2,00 | PCWP |            |
|  39 |                              PX COORDINATOR                  |                            |       |       |            |          |       |       |        |      |            |
|  40 |                               PX SEND QC (RANDOM)            | :TQ10003                   |     1 |    60 |            |          |       |       |  Q1,03 | P->S | QC (RAND)  |
|  41 |                                SORT AGGREGATE                |                            |     1 |    60 |            |          |       |       |  Q1,03 | PCWP |            |
|  42 |                                 NESTED LOOPS                 |                            | 26850 |  1573K|    32   (7)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|* 43 |                                  HASH JOIN                   |                            | 26850 |  1494K|    32   (7)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|* 44 |                                   HASH JOIN                  |                            | 26850 |  1206K|    30   (7)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  45 |                                    PX RECEIVE                |                            |   743 | 28977 |     3  (34)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  46 |                                     PX SEND BROADCAST        | :TQ10001                   |   743 | 28977 |     3  (34)| 00:00:01 |       |       |  Q1,01 | P->P | BROADCAST  |
|* 47 |                                      VIEW                    |                            |   743 | 28977 |     3  (34)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|* 48 | ANK                                   WINDOW BUFFER PUSHED R |                            |   743 | 16346 |     3  (34)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  49 |                                        SORT GROUP BY         |                            |   743 | 16346 |     3  (34)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  50 |                                         PX RECEIVE           |                            |   743 | 16346 |     3  (34)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  51 |                                          PX SEND HASH        | :TQ10000                   |   743 | 16346 |     3  (34)| 00:00:01 |       |       |  Q1,00 | P->P | HASH       |
|  52 |                                           HASH GROUP BY      |                            |   743 | 16346 |     3  (34)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  53 |                                            NESTED LOOPS      |                            |  2648 | 58256 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  54 | R                                           PX BLOCK ITERATO |                            |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|* 55 | ORAGE FULL                                   TABLE ACCESS ST | SRC_SYST_CLNDR_DAY         |  2949 | 26541 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|* 56 | N                                           INDEX UNIQUE SCA | UQ_SRC_SYST_SCHEMA_LOAD_EV |     1 |    13 |     0   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  57 |                                    PX BLOCK ITERATOR         |                            |  2906K|    19M|    26   (0)| 00:00:01 |       |       |  Q1,03 | PCWC |            |
|  58 | L                                   TABLE ACCESS STORAGE FUL | AR_RSRV_SUMRY              |  2906K|    19M|    26   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  59 |                                   PX RECEIVE                 |                            |    30 |   330 |     2   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  60 |                                    PX SEND BROADCAST         | :TQ10002                   |    30 |   330 |     2   (0)| 00:00:01 |       |       |  Q1,02 | P->P | BROADCAST  |
|  61 |                                     PX BLOCK ITERATOR        |                            |    30 |   330 |     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWC |            |
|* 62 | LL                                   TABLE ACCESS STORAGE FU | MSRMNT_PRD                 |    30 |   330 |     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|* 63 |                                  INDEX UNIQUE SCAN           | UQ_SRC_SYST                |     1 |     3 |     0   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|* 64 |                           INDEX UNIQUE SCAN                  | UQ_CL                      |     1 |     6 |     0   (0)| 00:00:01 |       |       |  Q2,00 | PCWP |            |
|  65 |                        PX BLOCK ITERATOR                     |                            |  2906K|    36M|    26   (0)| 00:00:01 |       |       |  Q2,02 | PCWC |            |
|  66 |                         TABLE ACCESS STORAGE FULL            | AR_RSRV_SUMRY              |  2906K|    36M|    26   (0)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
|  67 |                     PX BLOCK ITERATOR                        |                            |    72M|  1241M| 11363   (1)| 00:02:17 |     1 |  LAST |  Q2,04 | PCWC |            |
|  68 |                      TABLE ACCESS STORAGE FULL               | CRDT_ACCT_PRD              |    72M|  1241M| 11363   (1)| 00:02:17 |     1 |   150 |  Q2,04 | PCWP |            |
|  69 |             PX RECEIVE                                       |                            |   743 | 14860 |     3  (34)| 00:00:01 |       |       |  Q2,08 | PCWP |            |
|  70 |              PX SEND HASH                                    | :TQ20007                   |   743 | 14860 |     3  (34)| 00:00:01 |       |       |  Q2,07 | P->P | HASH       |
|* 71 |               VIEW                                           |                            |   743 | 14860 |     3  (34)| 00:00:01 |       |       |  Q2,07 | PCWP |            |
|* 72 |                WINDOW NOSORT                                 |                            |   743 | 16346 |     3  (34)| 00:00:01 |       |       |  Q2,07 | PCWP |            |
|  73 |                 SORT GROUP BY                                |                            |   743 | 16346 |     3  (34)| 00:00:01 |       |       |  Q2,07 | PCWP |            |
|  74 |                  PX RECEIVE                                  |                            |   743 | 16346 |     3  (34)| 00:00:01 |       |       |  Q2,07 | PCWP |            |
|  75 |                   PX SEND HASH                               | :TQ20005                   |   743 | 16346 |     3  (34)| 00:00:01 |       |       |  Q2,05 | P->P | HASH       |
|  76 |                    HASH GROUP BY                             |                            |   743 | 16346 |     3  (34)| 00:00:01 |       |       |  Q2,05 | PCWP |            |
|  77 |                     NESTED LOOPS                             |                            |  2648 | 58256 |     2   (0)| 00:00:01 |       |       |  Q2,05 | PCWP |            |
|  78 |                      PX BLOCK ITERATOR                       |                            |       |       |            |          |       |       |  Q2,05 | PCWC |            |
|* 79 |                       TABLE ACCESS STORAGE FULL              | SRC_SYST_CLNDR_DAY         |  2949 | 26541 |     2   (0)| 00:00:01 |       |       |  Q2,05 | PCWP |            |
|* 80 |                      INDEX UNIQUE SCAN                       | UQ_SRC_SYST_SCHEMA_LOAD_EV |     1 |    13 |     0   (0)| 00:00:01 |       |       |  Q2,05 | PCWP |            |
|  81 |            TABLE ACCESS BY INDEX ROWID                       | CRDT_ACCT                  |     1 |     9 |     0   (0)| 00:00:01 |       |       |  Q2,08 | PCWP |            |
|* 82 |             INDEX UNIQUE SCAN                                | UQ_CRDT_ACCT               |     1 |       |     0   (0)| 00:00:01 |       |       |  Q2,08 | PCWP |            |
|* 83 |          INDEX UNIQUE SCAN                                   | UQ_SRC_SYST                |     1 |       |     0   (0)| 00:00:01 |       |       |  Q2,08 | PCWP |            |
|* 84 |         TABLE ACCESS BY INDEX ROWID                          | SRC_SYST                   |     1 |     8 |     1   (0)| 00:00:01 |       |       |  Q2,08 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - filter("END_DT" IS NULL OR "END_DT">"M"."MSRMNT_PRD_DT")
  11 - access("A"."SRC_SYST_ID"="SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="MSRMNT_PRD_ID")
  14 - access("C"."MSRMNT_PRD_MNTH"=TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0')) AND "A"."MSRMNT_PRD_ID"="C"."MSRMNT_PRD_ID")
  23 - access("C"."SRC_SYST_ID"="A"."SRC_SYST_ID" AND "C"."MSRMNT_PRD_ID"="A"."MSRMNT_PRD_ID")
  28 - storage("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y')
       filter("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y')
  29 - access("A"."AR_ID"="C"."ACCT_AR_ID")
  32 - access("A"."MSRMNT_PRD_ID"="M"."MSRMNT_PRD_ID")
  37 - storage("M"."MSRMNT_PRD_ID"= (SELECT MAX(SYS_OP_CSR(SYS_OP_MSR(MAX("A"."MSRMNT_PRD_ID")),0)) FROM  (SELECT "A"."MSRMNT_PRD_ID" "MSRMNT_PRD_ID","A"."SRC_SYST_ID" 
              "SRC_SYST_ID",RANK() OVER ( PARTITION BY "A"."SRC_SYST_ID" ORDER BY INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID") DESC ) "RANK" FROM "BDW"."SRC_SYST_SCHEMA_LOAD_EV" 
              "B","BDW"."SRC_SYST_CLNDR_DAY" "A" WHERE "A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y' AND "B"."SCHEMA_NAME"='EDXF' AND "A"."MSRMNT_PRD_ID"="B"."MSRMNT_PRD_ID" AND 
              "A"."SRC_SYST_ID"="B"."SRC_SYST_ID" GROUP BY "A"."SRC_SYST_ID",INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID")) "from$_subquery$_013","BDW"."MSRMNT_PRD" "C","BDW"."SRC_SYST" 
              "B","BDW"."AR_RSRV_SUMRY" "A" WHERE "A"."MSRMNT_PRD_ID"="MSRMNT_PRD_ID" AND "A"."SRC_SYST_ID"="SRC_SYST_ID" AND "A"."SRC_SYST_ID"="B"."SRC_SYST_ID" AND 
              "A"."MSRMNT_PRD_ID"="C"."MSRMNT_PRD_ID" AND TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0'))=201206 AND "RANK"<=7))
       filter("M"."MSRMNT_PRD_ID"= (SELECT MAX(SYS_OP_CSR(SYS_OP_MSR(MAX("A"."MSRMNT_PRD_ID")),0)) FROM  (SELECT "A"."MSRMNT_PRD_ID" "MSRMNT_PRD_ID","A"."SRC_SYST_ID" 
              "SRC_SYST_ID",RANK() OVER ( PARTITION BY "A"."SRC_SYST_ID" ORDER BY INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID") DESC ) "RANK" FROM "BDW"."SRC_SYST_SCHEMA_LOAD_EV" 
              "B","BDW"."SRC_SYST_CLNDR_DAY" "A" WHERE "A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y' AND "B"."SCHEMA_NAME"='EDXF' AND "A"."MSRMNT_PRD_ID"="B"."MSRMNT_PRD_ID" AND 
              "A"."SRC_SYST_ID"="B"."SRC_SYST_ID" GROUP BY "A"."SRC_SYST_ID",INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID")) "from$_subquery$_013","BDW"."MSRMNT_PRD" "C","BDW"."SRC_SYST" 
              "B","BDW"."AR_RSRV_SUMRY" "A" WHERE "A"."MSRMNT_PRD_ID"="MSRMNT_PRD_ID" AND "A"."SRC_SYST_ID"="SRC_SYST_ID" AND "A"."SRC_SYST_ID"="B"."SRC_SYST_ID" AND 
              "A"."MSRMNT_PRD_ID"="C"."MSRMNT_PRD_ID" AND TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0'))=201206 AND "RANK"<=7))
  43 - access("A"."MSRMNT_PRD_ID"="C"."MSRMNT_PRD_ID")
  44 - access("A"."SRC_SYST_ID"="SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="MSRMNT_PRD_ID")
  47 - filter("RANK"<=7)
  48 - filter(RANK() OVER ( PARTITION BY "A"."SRC_SYST_ID" ORDER BY INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID") DESC )<=7)
  55 - storage("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y')
       filter("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y')
  56 - access("A"."SRC_SYST_ID"="B"."SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="B"."MSRMNT_PRD_ID" AND "B"."SCHEMA_NAME"='EDXF')
  62 - storage(TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0'))=201206)
       filter(TO_NUMBER(TO_CHAR("C"."CLNDR_YR")||LPAD(TO_CHAR("C"."CLNDR_MNTH"),2,'0'))=201206)
  63 - access("A"."SRC_SYST_ID"="B"."SRC_SYST_ID")
  64 - access("CL"."CL_ID"="M"."MSRMNT_PRD_TYPE_ID")
  71 - filter("RANK"<=7)
  72 - filter(RANK() OVER ( PARTITION BY "A"."SRC_SYST_ID" ORDER BY INTERNAL_FUNCTION("A"."MSRMNT_PRD_ID") DESC )<=7)
  79 - storage("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y')
       filter("A"."LAST_PRCSG_DAY_OF_MNTH_FLG"='Y')
  80 - access("A"."SRC_SYST_ID"="B"."SRC_SYST_ID" AND "A"."MSRMNT_PRD_ID"="B"."MSRMNT_PRD_ID" AND "B"."SCHEMA_NAME"='EDXF')
  82 - access("A"."AR_ID"="ACCT_AR_ID"(+))
  83 - access("A"."SRC_SYST_ID"="B"."SRC_SYST_ID")
  84 - filter("B"."SRC_SYST_CODE"='ABC')
 
Note
-----
   - dynamic sampling used for this statement (level=7)



EDIT :
Sorry for the explain plan, i cant seem to get it pasted in line. Attached the plan as a text file.

Also, the count of data in the views is as below.

View		Total count	Count for 1 msrmnt_prd_id
---------------------------------------------------------
ar_rsrv_mnth_v		1841892		281945
crdt_acct_mnth_v	66494145	7087369
crdt_acct_v		12258728	NA


[Updated on: Fri, 10 August 2012 04:39]

Report message to a moderator

Re: Query tuning help [message #563229 is a reply to message #563227] Fri, 10 August 2012 05:02 Go to previous messageGo to next message
John Watson
Messages: 4611
Registered: January 2010
Location: Global Village
Senior Member
Is this running against Exadata? The presence of the STORAGE word in the plan suggests that it is.
In which case, you aren't getting much benefit from it at the moment: few scan offloads, fewer
predicate offloads, no Bloom filters at all. I suspect that the partitioning is not perfect, there
are no partitionwise joins. Lastly, views are being materialized, presumably because they
are non-mergeable.
So, if it is Exadata, you probably need to go through the process of tuning for Exadata, because
it isn't working well. Not an easy job. Apart from that, perhaps the partitioning strategy needs
assessing, and the design of the views. That isn't easy either! Those are the areas I would look
at first.
Hope this helps.
Re: Query tuning help [message #563234 is a reply to message #563229] Fri, 10 August 2012 05:23 Go to previous messageGo to next message
knw15pwr
Messages: 130
Registered: March 2010
Senior Member
Thanks for the quick reply John.

They were moving stuff to Exadata. So maybe this database too is in Exadata.
Its actually a production DB and we only have read access.

I will forward the points in your reply to the DBA (frankly I did not understand much from that - but i will read about)

If there is anything that can be done with the query, it would be great as I have 5 more such queries. Not expecting much of an improvement, but anything in the range of 15 minutes is also good for us now.

The query returns 281769 rows.
Re: Query tuning help [message #563251 is a reply to message #563234] Fri, 10 August 2012 08:42 Go to previous messageGo to next message
gazzag
Messages: 326
Registered: November 2010
Location: Bristol, UK
Senior Member
Views upon views are an optimizer's nightmare. The best approach is to rewrite the query to hit the underlying base tables. Good luck with that, mind Wink
Re: Query tuning help [message #563259 is a reply to message #563251] Fri, 10 August 2012 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Views upon views are an optimizer's nightmare


Wrong!

Quote:
The best approach is to rewrite the query to hit the underlying base tables


And, above all, remove the useless and duplicate call to tables.

Regards
Michel
Re: Query tuning help [message #563279 is a reply to message #563259] Fri, 10 August 2012 14:17 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
KNW,

I would run the following two sqls that take snapshots of what is being read logically and physically during the execution of your sql. Then without any doubt or complicated analysis you have the tables that are being read
in a loop that will benefit from better indexing. Then you can create indexes to reduce the physical reads and
you can create indexes to reduce the logical reads.
ENDOCP1P > @io605

INSTANCE  IO_PER_MINUTE STATISTIC_NAME          OBJECT_NAME
--------- ------------- ----------------------- -------------------------------
NDOCP2             1776 physical reads          NFLPROD.D_1F000D5D8000010A.
NDOCP2             1776 physical read requests  NFLPROD.D_1F000D5D8000010A.
NDOCP2             5040 physical read requests  NFLPROD.DM_RELATION_S.
NDOCP2           117900 physical read requests  NFLPROD.DM_SYSOBJECT_R_COMP2.
NDOCP2           119148 physical reads          NFLPROD.DM_SYSOBJECT_R_COMP2.
NDOCP2           639924 physical reads direct   NFLPROD.DM_RELATION_S.
NDOCP2           639936 physical reads          NFLPROD.DM_RELATION_S.
          -------------
sum             1525500

ENDOCP1P > @logical605

GIG_PER_MINUTE INSTANC STATISTIC_NAME TYPE  OBJECT_NAME
-------------- ------- -------------- ----- ----------------------------
          .001 NDOCP1  logical reads  TABLE SYS.SEG$
          .001 NDOCP1  logical reads  INDEX SYS.I_OBJ4
          .001 NDOCP1  logical reads  INDEX SYS.I_COL3
          .001 NDOCP1  logical reads  TABLE SYS.JOB$
          .001 NDOCP1  logical reads  INDEX SYS.I_SYN2
          .001 NDOCP1  logical reads  INDEX SYS.I_TS#
          .001 NDOCP1  logical reads  INDEX SYS.I_OBJ5
          .003 NDOCP1  logical reads  INDEX SYS.I_COL1
          .003 NDOCP1  logical reads  INDEX SYS.I_COL2
          .003 NDOCP1  logical reads  TABLE SYS.FILE$
          .004 NDOCP1  logical reads  INDEX SYS.I_OBJ#
          .025 NDOCP2  logical reads  TABLE SYS.IND$
          .034 NDOCP4  logical reads  TABLE SYS.IND$
          .042 NDOCP4  logical reads  INDEX SYS.I_IND1
          .045 NDOCP3  logical reads  TABLE SYS.IND$
          .048 NDOCP3  logical reads  INDEX SYS.I_IND1
          .048 NDOCP1  logical reads  INDEX SYS.I_IND1
          .053 NDOCP2  logical reads  INDEX SYS.I_IND1
          .104 NDOCP3  logical reads  TABLE SYS.OBJ$
          .104 NDOCP4  logical reads  TABLE SYS.OBJ$
          .107 NDOCP2  logical reads  TABLE SYS.OBJ$
          .116 NDOCP3  logical reads  TABLE SYS.USER$
          .123 NDOCP4  logical reads  TABLE SYS.USER$
          .135 NDOCP2  logical reads  TABLE SYS.USER$
          .170 NDOCP1  logical reads  TABLE SYS.OBJ$
          .217 NDOCP1  logical reads  TABLE SYS.USER$
          .258 NDOCP1  logical reads  TABLE SYS.TS$
          .267 NDOCP1  logical reads  INDEX SYS.I_OBJ1
          .422 NDOCP2  logical reads  TABLE NFLPROD.DM_SYSOBJECT_R
          .595 NDOCP2  logical reads  TABLE SYS.TS$
          .602 NDOCP2  logical reads  INDEX SYS.I_OBJ1
          .608 NDOCP3  logical reads  TABLE SYS.TS$
          .624 NDOCP4  logical reads  TABLE SYS.TS$
          .640 NDOCP3  logical reads  INDEX SYS.I_OBJ1
          .642 NDOCP4  logical reads  INDEX SYS.I_OBJ1
          .776 NDOCP2  logical reads  TABLE NFLPROD.DM_SYSOBJECT_S
          .844 NDOCP2  logical reads  INDEX NFLPROD.D_1F000D5D8000010A
         1.649 NDOCP2  logical reads  INDEX NFLPROD.D_1F000D5D80000109
         1.649 NDOCP2  logical reads  INDEX NFLPROD.D_1F000D5D80000146
         2.451 NDOCP2  logical reads  INDEX NFLPROD.DM_SYSOBJECT_R_COMP2
    ----------
sum     13.421

IO605.sql looks like the following:
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

logical605.sql looks like the following:
set termout off
drop table gv$segment_statistic1 purge;
drop table gv$segment_statistic2 purge;
column statistic_name format a14
column object_name format a50
column object_type format a11
column gig_per_minute format 9999.999
column type format a5
column instance format a7
SET WRAP OFF
SET TRUNC OFF
SET LINES 200
break on report
compute sum of gig_per_minute break on report
compute sum of buf_per_min break on report
compute sum of buf_per_sec break on report
create table gv$segment_statistic1 as
select * from gv$segment_statistics
where upper(statistic_name) like  '%LOGICAL%'
and (upper(statistic_name) like '%READ%' or upper(statistic_name) like '%WRITE%');
execute dbms_lock.sleep(5);
create table gv$segment_statistic2 as
select * from gv$segment_statistics
where upper(statistic_name) like  '%LOGICAL%'
and (upper(statistic_name) like '%READ%' or upper(statistic_name) like '%WRITE%');
set wrap off
set pages 50
set termout on
select b.value last, a.value first,(b.value-a.value)*12 buf_per_min,
(b.value-a.value)*12/60 buf_per_sec,
(b.value-a.value)*12*8192/1024/1024/1024*1 GIG_PER_MINUTE,
i.instance_name instance,a.statistic_name,a.object_type type,
a.owner||'.'||a.object_name||' '||a.subobject_name object_name
from gv$segment_statistic2 b,gv$segment_statistic1 a,gv$instance i
where a.statistic_name=b.statistic_name and a.inst_id=b.inst_id
and b.inst_id=i.inst_id and a.inst_id=i.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 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_statistic1 purge;
drop table gv$segment_statistic2 purge;
set termout on
Re: Query tuning help [message #563344 is a reply to message #563279] Mon, 13 August 2012 01:47 Go to previous messageGo to next message
knw15pwr
Messages: 130
Registered: March 2010
Senior Member
Hi Alan,
I wont be able to use the script you posted as it involves drop/create tables. Its a production DB and we got only read access.

I have repeatedly made requests to be able to query the base tables directly, but I am just a humble developer who does not understand the system or need for views (or so they say). There is not much being done in the views anyway ! But I am stuck with them for now.

Thanks.
Re: Query tuning help [message #563435 is a reply to message #563344] Mon, 13 August 2012 18:12 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
You can look at the physical and logical reads the last hour by the following with selects only but you will need "select any dictionary" privilege.
ECSCDAD3 > grant select any dictionary to alan;

Grant succeeded.

ECSCDAD3 > connect alan/alan@cscdad3;
Connected.

ALAN > @logical

DATE                 LOGICAL_READS_LAST_HOUR OBJECT_NAME
-------------------- ----------------------- --------------------
2012-08-14 Tuesday                       448 SCHEDULER$_EVENT_LOG
2012-08-14 Tuesday                       464 SYS_C0011404
2012-08-14 Tuesday                       576 OBJ$
2012-08-14 Tuesday                       864 TAG
2012-08-14 Tuesday                      1584 JOB$
2012-08-14 Tuesday                      1632 WRH$_SEG_STAT_OBJ_PK
2012-08-14 Tuesday                      1760 AUDIO_VIDEO
2012-08-14 Tuesday                      2048 I_SYSAUTH1
2012-08-14 Tuesday                     22160 CONTENT

ALAN > list
  1  select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
  2  sum(b.LOGICAL_READS_DELTA) LOGICAL_READS_LAST_HOUR,
  3  a.object_name
  4  from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
  5  where b.SNAP_ID =(select max(SNAP_ID) from sys.wRM$_SNAPSHOT)
  6  and a.object_id=b.OBJ#
  7  and b.LOGICAL_READS_DELTA>0
  8  and c.instance_number=(select instance_number from v$instance)
  9  and c.snap_id=b.snap_id
 10  group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
 11* order by 2

ALAN > @physical

DATE                 PHYSICAL_READS_LAST_HOUR OBJECT_NAME
-------------------- ------------------------ --------------------
2012-08-14 Tuesday                          1 WRH$_COMP_IOSTAT_PK

ALAN > list
  1  select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
  2  sum(b.PHYSICAL_READS_DELTA) PHYSICAL_READS_LAST_HOUR,
  3  a.object_name
  4  from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
  5  where b.SNAP_ID =(select max(SNAP_ID) from sys.wRM$_SNAPSHOT)
  6  and a.object_id=b.OBJ#
  7  and b.PHYSICAL_READS_DELTA>0
  8  and c.instance_number=(select instance_number from v$instance)
  9  and c.snap_id=b.snap_id
 10  group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
 11* order by 2
Previous Topic: Analyzed table
Next Topic: Need help with Queries
Goto Forum:
  


Current Time: Thu Oct 02 02:30:35 CDT 2014

Total time taken to generate the page: 0.32562 seconds