Home » RDBMS Server » Performance Tuning » query tuning (10g/windows)
query tuning [message #426042] Tue, 13 October 2009 23:39 Go to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Here is the query

SELECT
  HR.HR_ALL_ORGANIZATION_UNITS.ATTRIBUTE2,
  GL.GL_CODE_COMBINATIONS.SEGMENT1,
  GL.GL_CODE_COMBINATIONS.SEGMENT2,
  GL.GL_CODE_COMBINATIONS.SEGMENT3,
  GL.GL_CODE_COMBINATIONS.SEGMENT4,
  GL.GL_CODE_COMBINATIONS.SEGMENT5,
  GL.GL_CODE_COMBINATIONS.segment6,
  INV.MTL_SYSTEM_ITEMS_B.DESCRIPTION,
  INV.MTL_SYSTEM_ITEMS_B.SEGMENT1,
  AR.RA_CUSTOMER_TRX_ALL.INVOICE_CURRENCY_CODE,
  AR.RA_CUST_TRX_LINE_GL_DIST_ALL.AMOUNT,
  AR.RA_CUST_TRX_LINE_GL_DIST_ALL.ACCTD_AMOUNT,
  NULL,
  NULL,
  '',
  '',
  GL.GL_SETS_OF_BOOKS.CURRENCY_CODE,
  AR.RA_CUSTOMER_TRX_LINES_ALL.QUANTITY_ORDERED,
  (SELECT ATTRIBUTE_VALUE FROM APPS.XXG_GAAP_ITEM_ATTRIBUTE 
                             WHERE SKU_NUM = INV.MTL_SYSTEM_ITEMS_B.SEGMENT1 AND ATTRIBUTE_CODE = 'FINDURATIN' AND EFFECTIVE_END_DATE IS NULL),
  (SELECT ATTRIBUTE_VALUE FROM APPS.XXG_GAAP_ITEM_ATTRIBUTE 
                     WHERE SKU_NUM = INV.MTL_SYSTEM_ITEMS_B.SEGMENT1 AND ATTRIBUTE_CODE = 'FINSTRTDAT' AND EFFECTIVE_END_DATE IS NULL),
  XXG.XXG_GAAP_ITEM_ATTRIBUTE.ATTRIBUTE_VALUE,
  (SELECT XXG.ATTRIBUTE_VALUE FROM 
APPS.XXG_GAAP_ITEM_ATTRIBUTE XXG
WHERE XXG.SKU_NUM = INV.MTL_SYSTEM_ITEMS_B.SEGMENT1 AND XXG.ATTRIBUTE_CODE = 'FINCOGSTRE' and XXG.effective_end_date is null),
  AR.RA_CUSTOMER_TRX_ALL.CT_REFERENCE
FROM
  HR.HR_ALL_ORGANIZATION_UNITS,
  GL.GL_CODE_COMBINATIONS,
  INV.MTL_SYSTEM_ITEMS_B,
  AR.RA_CUSTOMER_TRX_ALL,
  AR.RA_CUST_TRX_LINE_GL_DIST_ALL,
  GL.GL_SETS_OF_BOOKS,
  AR.RA_CUSTOMER_TRX_LINES_ALL,
  XXG.XXG_GAAP_ITEM_ATTRIBUTE,
  ( 
  SELECT 'OP' Status_flag
             ,Current_Flag  Current_Previous
             ,period_num
             ,Period
            ,period_year
            ,month_start_date
            ,month_end_date
            ,quarter_num
            ,quarter_end_date
FROM  (SELECT case
             when TRUNC(sysdate) between TRUNC(glp.start_date)  and TRUNC(glp.end_date) then 'C' -- current period
             when TRUNC(glp.end_date) = (Select TRUNC(MAX(al2.start_date) - 1)
                                         FROM   gl.gl_periods al2 
                                         WHERE (NOT al2.period_num = 13) 
                                         AND   al2.period_set_name = 'GlobalCalendar'
                                         AND   TRUNC(sysdate) >= TRUNC(al2.start_date))  then 'P'  -- previous period
             else null
             end current_flag
            ,glp.period_num
            ,glp.period_name    Period
            ,glp.period_year
            ,glp.start_date       month_start_date
            ,glp.end_date         month_end_date
            ,glp.quarter_num
            ,glq.quarter_end_date
        FROM gl.gl_periods glp
             ,(SELECT  MAX(end_date) quarter_end_date
                       ,period_year
                       ,quarter_num
                       ,period_set_name
                FROM   gl.gl_periods
                GROUP BY period_year
                        ,quarter_num
                        ,quarter_start_date
                        ,period_set_name
              ) glq  
         WHERE glp.period_year = glq.period_year
         AND   glp.quarter_num = glq.quarter_num
         AND   glp.period_set_name = glq.period_set_name
         AND   (NOT glp.period_num = 13)
         AND   glp.period_set_name = 'GlobalCalendar'
         )
 WHERE current_flag IN ('P')
  )  PERIODS_DETAILS_CURR_PREV
WHERE
  ( AR.RA_CUST_TRX_LINE_GL_DIST_ALL.CODE_COMBINATION_ID=GL.GL_CODE_COMBINATIONS.CODE_COMBINATION_ID  )
  AND  ( AR.RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE between PERIODS_DETAILS_CURR_PREV.MONTH_START_DATE and PERIODS_DETAILS_CURR_PREV.MONTH_END_DATE  )
  AND  ( AR.RA_CUST_TRX_LINE_GL_DIST_ALL.ORG_ID=HR.HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID  )
  AND  ( AR.RA_CUSTOMER_TRX_ALL.SET_OF_BOOKS_ID=GL.GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID 
  )
  AND  ( AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID(+)=AR.RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_LINE_ID  )
  AND  ( AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID=AR.RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID  )
  AND  ( AR.RA_CUSTOMER_TRX_LINES_ALL.INVENTORY_ITEM_ID=INV.MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID  )
  AND  ( AR.RA_CUSTOMER_TRX_LINES_ALL.ORG_ID=INV.MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID  )
  AND  ( XXG.XXG_GAAP_ITEM_ATTRIBUTE.SKU_NUM=INV.MTL_SYSTEM_ITEMS_B.SEGMENT1  )
  AND  ( XXG.XXG_GAAP_ITEM_ATTRIBUTE.INVENTORY_ITEM_ID=INV.MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID  )
  AND  
  (
   HR.HR_ALL_ORGANIZATION_UNITS.ATTRIBUTE11  In  ('APJ')
   AND
   PERIODS_DETAILS_CURR_PREV.CURRENT_PREVIOUS  In  ('P')
   AND
   AR.RA_CUST_TRX_LINE_GL_DIST_ALL.ATTRIBUTE13  =  'Initial'
   AND
   AR.RA_CUST_TRX_LINE_GL_DIST_ALL.ATTRIBUTE11  In  ( 'R77','R83','R79','R101','R104'  )
   AND
   AR.RA_CUSTOMER_TRX_ALL.interface_header_context  =  'ORDER ENTRY'
   AND
   AR.RA_CUSTOMER_TRX_ALL.CREATED_FROM  =  'RAXTRX'
   AND
   XXG.XXG_GAAP_ITEM_ATTRIBUTE.attribute_code  =  'FINREVCLS'
  )


the execution plan is
Execution Plan
----------------------------------------------------------
Plan hash value: 3503208966

----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                              |     1 |   454 | 75210   (1)| 00:15:03 |       |       |
|*  1 |  TABLE ACCESS BY INDEX ROWID          | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    35 |     4   (0)
|*  2 |   INDEX RANGE SCAN                    | XXG_GAAP_ITEM_ATTRIBUTE_N1   |     1 |       |     3   (0)| 00:00:0
|*  3 |  TABLE ACCESS BY INDEX ROWID          | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    35 |     4   (0)
|*  4 |   INDEX RANGE SCAN                    | XXG_GAAP_ITEM_ATTRIBUTE_N1   |     1 |       |     3   (0)| 00:00:0
|*  5 |  TABLE ACCESS BY INDEX ROWID          | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    35 |     4   (0)
|*  6 |   INDEX RANGE SCAN                    | XXG_GAAP_ITEM_ATTRIBUTE_N1   |     1 |       |     3   (0)| 00:00:0
|   7 |  HASH GROUP BY                        |                              |     1 |   454 | 75210   (1)| 00:15:03 |       |       |
|*  8 |   TABLE ACCESS BY INDEX ROWID         | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    51 |     3   (0
|   9 |    NESTED LOOPS                       |                              |     1 |   454 | 75205   (1)| 00:15:03 |       |       |
|  10 |     NESTED LOOPS                      |                              |     1 |   403 | 75202   (1)| 00:15:03 |       |       |
|  11 |      NESTED LOOPS                     |                              |     1 |   379 | 75201   (1)| 00:15:03 |       |       |
|  12 |       NESTED LOOPS                    |                              |     1 |   330 | 75200   (1)| 00:15:03 |       |       |
|  13 |        NESTED LOOPS                   |                              |     1 |   245 | 75198   (1)| 00:15:03 |       |       |
|  14 |         NESTED LOOPS                  |                              |     1 |   225 | 75197   (1)| 00:15:03 |       |       |
|  15 |          NESTED LOOPS                 |                              |     1 |   162 | 75195   (1)| 00:15:03 |       |       |
|  16 |           NESTED LOOPS                |                              |     1 |   130 | 75193   (1)| 00:15:03 |       |       |
|  17 |            MERGE JOIN                 |                              |     1 |   100 | 75190   (1)| 00:15:03 |       |       |
|  18 |             SORT JOIN                 |                              |    63 |  2961 | 75184   (1)| 00:15:03 |       |       |
|* 19 |              TABLE ACCESS FULL        | RA_CUST_TRX_LINE_GL_DIST_ALL |    63 |  2961 | 75183   (1)| 00
|* 20 |             FILTER                    |                              |       |       |            |          |       |       |
|* 21 |              SORT JOIN                |                              |     2 |   106 |     5  (20)| 00:00:01 |       |       |
|* 22 |               TABLE ACCESS FULL       | GL_PERIODS                   |     2 |   106 |     4   (0)| 00:00:01 |     
|  23 |                SORT AGGREGATE         |                              |     1 |    26 |            |          |       |       |
|* 24 |                 TABLE ACCESS FULL     | GL_PERIODS                   |    12 |   312 |     4   (0)| 00:00:01 |       |     
|* 25 |            TABLE ACCESS BY INDEX ROWID| GL_PERIODS                   |     3 |    90 |     3   (0)| 00:00:01 |     
|* 26 |             INDEX RANGE SCAN          | GL_PERIODS_U2                |    13 |       |     2   (0)| 00:00:01 |     
|  27 |           PARTITION HASH ITERATOR     |                              |     1 |    32 |     2   (0)| 00:00:01 |   KEY |   KEY 
|* 28 |            TABLE ACCESS BY INDEX ROWID| RA_CUSTOMER_TRX_LINES_ALL    |     1 |    32 |     2   (0)| 00
|* 29 |             INDEX UNIQUE SCAN         | RA_CUSTOMER_TRX_LINES_U1     |     1 |       |     1   (0)| 00:0
|  30 |          PARTITION HASH ITERATOR      |                              |     1 |    63 |     2   (0)| 00:00:01 |   KEY |   KEY 
|* 31 |           TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_ALL          |     1 |    63 |     2   (0)| 00:00:
|* 32 |            INDEX UNIQUE SCAN          | RA_CUSTOMER_TRX_U1           |     1 |       |     1   (0)| 00:00:01 |
|  33 |         TABLE ACCESS BY INDEX ROWID   | GL_SETS_OF_BOOKS             |     1 |    20 |     1   (0)| 00:00:01 
|* 34 |          INDEX UNIQUE SCAN            | GL_SETS_OF_BOOKS_U2          |     1 |       |     0   (0)| 00:00:01 |  
|  35 |        TABLE ACCESS BY INDEX ROWID    | MTL_SYSTEM_ITEMS_B           |     1 |    85 |     2   (0)| 
|* 36 |         INDEX UNIQUE SCAN             | MTL_SYSTEM_ITEMS_B_U1        |     1 |       |     1   (0)| 00:00:01 |
|  37 |       TABLE ACCESS BY INDEX ROWID     | GL_CODE_COMBINATIONS         |     1 |    49 |     2   (0)
|* 38 |        INDEX UNIQUE SCAN              | GL_CODE_COMBINATIONS_U1      |     1 |       |     0   (0)| 00
|* 39 |      TABLE ACCESS BY INDEX ROWID      | HR_ALL_ORGANIZATION_UNITS    |     1 |    24 |     1   
|* 40 |       INDEX UNIQUE SCAN               | HR_ORGANIZATION_UNITS_PK     |     1 |       |     0   (0)| 00
|* 41 |     INDEX RANGE SCAN                  | XXG_GAAP_ITEM_ATTRIBUTE_UN   |     1 |       |     2   (0)| 00:00
----------------------------------------------------------------------------------------------------

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

   1 - filter("EFFECTIVE_END_DATE" IS NULL)
   2 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINDURATIN')
   3 - filter("EFFECTIVE_END_DATE" IS NULL)
   4 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINSTRTDAT')
   5 - filter("XXG"."EFFECTIVE_END_DATE" IS NULL)
   6 - access("XXG"."SKU_NUM"=:B1 AND "XXG"."ATTRIBUTE_CODE"='FINCOGSTRE')
   8 - filter("XXG_GAAP_ITEM_ATTRIBUTE"."SKU_NUM"="MTL_SYSTEM_ITEMS_B"."SEGMENT1")
  19 - filter("RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE13"='Initial' AND
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."CUSTOMER_TRX_LINE_ID" IS NOT NULL AND ("RA_CUST_TRX_LINE_GL_D
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R104' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIB
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R79' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBU
  20 - filter("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE"<="GLP"."END_DATE")
  21 - access(INTERNAL_FUNCTION("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE")>=INTERNAL_FUNCTION("GLP"."
       filter(INTERNAL_FUNCTION("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE")>=INTERNAL_FUNCTION("GLP"."
  22 - filter("GLP"."PERIOD_NUM"<>13 AND "GLP"."PERIOD_SET_NAME"='GlobalCalendar' AND CASE  WHEN
              (TRUNC(SYSDATE@!)>=TRUNC(INTERNAL_FUNCTION("GLP"."START_DATE")) AND
              TRUNC(SYSDATE@!)<=TRUNC(INTERNAL_FUNCTION("GLP"."END_DATE"))) THEN 'C' WHEN TRUNC(INTERNAL_FU
              (SELECT TRUNC(MAX("AL2"."START_DATE")-1) FROM "GL"."GL_PERIODS" "AL2" WHERE "AL2"."PERIOD_NUM
              TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SYSDATE@!) AND "AL2"."PERIOD_SET_NAME"='G
              NULL END ='P')
  24 - filter("AL2"."PERIOD_NUM"<>13 AND TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SYSDATE
              "AL2"."PERIOD_SET_NAME"='GlobalCalendar')
  25 - filter("GLP"."QUARTER_NUM"="QUARTER_NUM")
  26 - access("PERIOD_SET_NAME"='GlobalCalendar' AND "GLP"."PERIOD_YEAR"="PERIOD_YEAR")
       filter("GLP"."PERIOD_YEAR"="PERIOD_YEAR")
  28 - filter("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID" IS NOT NULL)
  29 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_LINE_ID"="RA_CUST_TRX_LINE_GL_DIST_ALL"."CUS
  31 - filter("RA_CUSTOMER_TRX_ALL"."INTERFACE_HEADER_CONTEXT"='ORDER ENTRY' AND
              "RA_CUSTOMER_TRX_ALL"."CREATED_FROM"='RAXTRX')
  32 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_ID"="RA_CUSTOMER_TRX_ALL"."CUSTOMER_TRX_ID")
  34 - access("RA_CUSTOMER_TRX_ALL"."SET_OF_BOOKS_ID"="GL_SETS_OF_BOOKS"."SET_OF_BOOKS_ID")
  36 - access("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_I
              "RA_CUSTOMER_TRX_LINES_ALL"."ORG_ID"="MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID")
  38 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."CODE_COMBINATION_ID"="GL_CODE_COMBINATIONS"."CODE_COMB
  39 - filter("HR_ALL_ORGANIZATION_UNITS"."ATTRIBUTE11"='APJ')
  40 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."ORG_ID"="HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID")
  41 - access("XXG_GAAP_ITEM_ATTRIBUTE"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID"
              "XXG_GAAP_ITEM_ATTRIBUTE"."ATTRIBUTE_CODE"='FINREVCLS')


Re: query tuning [message #426045 is a reply to message #426042] Tue, 13 October 2009 23:43 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
the problem is if I replace

AND  ( AR.RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE between PERIODS_DETAILS_CURR_PREV.MONTH_START_DATE and PERIODS_DETAILS_CURR_PREV.MONTH_END_DATE  )

with
  AND  ( AR.RA_CUST_TRX_LINE_GL_DIST_ALL.GL_DATE between SYSDATE-30 and SYSDATE  )


the excution plan becomes lot more better to look at
Execution Plan
----------------------------------------------------------
Plan hash value: 573003422

----------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                              |     1 |   454 |  4996   (1)| 00:01:00 |       |       |
|*  1 |  TABLE ACCESS BY INDEX ROWID             | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    35 |     4   (0)| 00:0
|*  2 |   INDEX RANGE SCAN                       | XXG_GAAP_ITEM_ATTRIBUTE_N1   |     1 |       |     3   (0)| 00:00:01 |       |       |
|*  3 |  TABLE ACCESS BY INDEX ROWID             | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    35 |     4   (0)| 00:0
|*  4 |   INDEX RANGE SCAN                       | XXG_GAAP_ITEM_ATTRIBUTE_N1   |     1 |       |     3   (0)| 00:00:01 |       |       |
|*  5 |  TABLE ACCESS BY INDEX ROWID             | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    35 |     4   (0)| 00:0
|*  6 |   INDEX RANGE SCAN                       | XXG_GAAP_ITEM_ATTRIBUTE_N1   |     1 |       |     3   (0)| 00:00:01 |       |       |
|   7 |  HASH GROUP BY                           |                              |     1 |   454 |  4996   (1)| 00:01:00 |       |       |
|*  8 |   FILTER                                 |                              |       |       |            |          |       |       |
|*  9 |    TABLE ACCESS BY INDEX ROWID           | GL_PERIODS                   |     1 |    53 |     3   (0)| 00:00:01 |       |       
|  10 |     NESTED LOOPS                         |                              |     1 |   454 |  4991   (1)| 00:01:00 |       |       |
|  11 |      MERGE JOIN CARTESIAN                |                              |     1 |   401 |  4988   (1)| 00:01:00 |       |       |
|* 12 |       TABLE ACCESS BY INDEX ROWID        | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    51 |     3   (0)| 
|  13 |        NESTED LOOPS                      |                              |     1 |   371 |  4984   (1)| 00:01:00 |       |       |
|  14 |         NESTED LOOPS                     |                              |     1 |   320 |  4981   (1)| 00:01:00 |       |       |
|  15 |          NESTED LOOPS                    |                              |     1 |   300 |  4980   (1)| 00:01:00 |       |       |
|  16 |           NESTED LOOPS                   |                              |     1 |   237 |  4978   (1)| 00:01:00 |       |       |
|  17 |            NESTED LOOPS                  |                              |     1 |   152 |  4976   (1)| 00:01:00 |       |       |
|  18 |             NESTED LOOPS                 |                              |     1 |   120 |  4974   (1)| 00:01:00 |       |       |
|  19 |              NESTED LOOPS                |                              |     1 |    71 |  4972   (1)| 00:01:00 |       |       |
|* 20 |               TABLE ACCESS BY INDEX ROWID| RA_CUST_TRX_LINE_GL_DIST_ALL |     1 |    47 |  4971   (
|* 21 |                INDEX RANGE SCAN          | RA_CUST_TRX_LINE_GL_DIST_N2  | 88150 |       |   549   (1)| 00:00:07 |
|* 22 |               TABLE ACCESS BY INDEX ROWID| HR_ALL_ORGANIZATION_UNITS    |     1 |    24 |     1   (0)|
|* 23 |                INDEX UNIQUE SCAN         | HR_ORGANIZATION_UNITS_PK     |     1 |       |     0   (0)| 00:00:01 |       |       
|  24 |              TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS         |     1 |    49 |     2   (0)| 00:
|* 25 |               INDEX UNIQUE SCAN          | GL_CODE_COMBINATIONS_U1      |     1 |       |     0   (0)| 00:00:01 |       |       |
|  26 |             PARTITION HASH ITERATOR      |                              |     1 |    32 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 27 |              TABLE ACCESS BY INDEX ROWID | RA_CUSTOMER_TRX_LINES_ALL    |     1 |    32 |     2   (0)|
|* 28 |               INDEX UNIQUE SCAN          | RA_CUSTOMER_TRX_LINES_U1     |     1 |       |     1   (0)| 00:00:01 |   
|  29 |            TABLE ACCESS BY INDEX ROWID   | MTL_SYSTEM_ITEMS_B           |     1 |    85 |     2   (0)| 00:00:
|* 30 |             INDEX UNIQUE SCAN            | MTL_SYSTEM_ITEMS_B_U1        |     1 |       |     1   (0)| 00:00:01 |       |       |
|  31 |           PARTITION HASH ITERATOR        |                              |     1 |    63 |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 32 |            TABLE ACCESS BY INDEX ROWID   | RA_CUSTOMER_TRX_ALL          |     1 |    63 |     2   (0)| 00:00
|* 33 |             INDEX UNIQUE SCAN            | RA_CUSTOMER_TRX_U1           |     1 |       |     1   (0)| 00:00:01 |   KEY |  
|  34 |          TABLE ACCESS BY INDEX ROWID     | GL_SETS_OF_BOOKS             |     1 |    20 |     1   (0)| 00:00:01 |
|* 35 |           INDEX UNIQUE SCAN              | GL_SETS_OF_BOOKS_U2          |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 36 |         INDEX RANGE SCAN                 | XXG_GAAP_ITEM_ATTRIBUTE_UN   |     1 |       |     2   (0)| 00:00:01 |       |       |
|  37 |       BUFFER SORT                        |                              |   260 |  7800 |  4985   (1)| 00:01:00 |       |       |
|* 38 |        TABLE ACCESS FULL                 | GL_PERIODS                   |   260 |  7800 |     4   (0)| 00:00:01 |       |       |
|* 39 |      INDEX RANGE SCAN                    | GL_PERIODS_U2                |    12 |       |     2   (0)| 00:00:01 |       |       |
|  40 |     SORT AGGREGATE                       |                              |     1 |    26 |            |          |       |       |
|* 41 |      TABLE ACCESS FULL                   | GL_PERIODS                   |    12 |   312 |     4   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------

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

   1 - filter("EFFECTIVE_END_DATE" IS NULL)
   2 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINDURATIN')
   3 - filter("EFFECTIVE_END_DATE" IS NULL)
   4 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINSTRTDAT')
   5 - filter("XXG"."EFFECTIVE_END_DATE" IS NULL)
   6 - access("XXG"."SKU_NUM"=:B1 AND "XXG"."ATTRIBUTE_CODE"='FINCOGSTRE')
   8 - filter(SYSDATE@!-30<=SYSDATE@!)
   9 - filter("GLP"."QUARTER_NUM"="QUARTER_NUM" AND CASE  WHEN (TRUNC(SYSDATE@!)>=TRUNC(INTERNAL_FUN
              AND TRUNC(SYSDATE@!)<=TRUNC(INTERNAL_FUNCTION("GLP"."END_DATE"))) THEN 'C' WHEN TRUNC(INTERNA
              (SELECT TRUNC(MAX("AL2"."START_DATE")-1) FROM "GL"."GL_PERIODS" "AL2" WHERE "AL2"."PERIOD_NUM
              TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SYSDATE@!) AND "AL2"."PERIOD_SET_NAME"='G
              END ='P')
  12 - filter("XXG_GAAP_ITEM_ATTRIBUTE"."SKU_NUM"="MTL_SYSTEM_ITEMS_B"."SEGMENT1")
  20 - filter("RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE13"='Initial' AND "RA_CUST_TRX_LINE_GL_DIST_A
              IS NOT NULL AND ("RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R101' OR "RA_CUST_TRX_LINE_GL_
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R77' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBU
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R83'))
  21 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE">=SYSDATE@!-30 AND "RA_CUST_TRX_LINE_GL_DIST_A
  22 - filter("HR_ALL_ORGANIZATION_UNITS"."ATTRIBUTE11"='APJ')
  23 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."ORG_ID"="HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID")
  25 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."CODE_COMBINATION_ID"="GL_CODE_COMBINATIONS"."CODE_COMB
  27 - filter("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID" IS NOT NULL)
  28 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_LINE_ID"="RA_CUST_TRX_LINE_GL_DIST_ALL"."CUS
  30 - access("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_I
              "RA_CUSTOMER_TRX_LINES_ALL"."ORG_ID"="MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID")
  32 - filter("RA_CUSTOMER_TRX_ALL"."INTERFACE_HEADER_CONTEXT"='ORDER ENTRY' AND "RA_CUSTOMER_TRX_AL
  33 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_ID"="RA_CUSTOMER_TRX_ALL"."CUSTOMER_TRX_ID")
  35 - access("RA_CUSTOMER_TRX_ALL"."SET_OF_BOOKS_ID"="GL_SETS_OF_BOOKS"."SET_OF_BOOKS_ID")
  36 - access("XXG_GAAP_ITEM_ATTRIBUTE"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID"
              "XXG_GAAP_ITEM_ATTRIBUTE"."ATTRIBUTE_CODE"='FINREVCLS')
  38 - filter("PERIOD_SET_NAME"='GlobalCalendar')
  39 - access("GLP"."PERIOD_SET_NAME"='GlobalCalendar' AND "GLP"."PERIOD_YEAR"="PERIOD_YEAR")
       filter("GLP"."PERIOD_NUM"<>13 AND "GLP"."PERIOD_YEAR"="PERIOD_YEAR")
  41 - filter("AL2"."PERIOD_NUM"<>13 AND TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SYSDATE
              "AL2"."PERIOD_SET_NAME"='GlobalCalendar')


Can you please suggest what I am missing here
Re: query tuning [message #426050 is a reply to message #426045] Wed, 14 October 2009 00:25 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
In production these are the exec plans

date coming from subquery
Execution Plan
----------------------------------------------------------
Plan hash value: 2701487423

----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                              |     1 |   520 |  2177K  (1)| 07:15:29 |
|*  1 |  TABLE ACCESS BY INDEX ROWID           | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    33 |     4   (
|*  2 |   INDEX RANGE SCAN                     | XXG_GAAP_ITEM_ATTRIBUTE_N1   |     1 |       |     3   (0)| 00:00
|*  3 |  TABLE ACCESS BY INDEX ROWID           | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    33 |     4   (
|*  4 |   INDEX RANGE SCAN                     | XXG_GAAP_ITEM_ATTRIBUTE_N1   |     1 |       |     3   (0)| 00:00
|*  5 |  TABLE ACCESS BY INDEX ROWID           | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    33 |     4   (
|*  6 |   INDEX RANGE SCAN                     | XXG_GAAP_ITEM_ATTRIBUTE_N1   |     1 |       |     3   (0)| 00:00
|   7 |  HASH GROUP BY                         |                              |     1 |   520 |  2177K  (1)| 07:15:29 |
|*  8 |   TABLE ACCESS BY INDEX ROWID          | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    49 |     3   
|   9 |    NESTED LOOPS                        |                              |     1 |   520 |  2177K  (1)| 07:15:29 |
|  10 |     NESTED LOOPS                       |                              |     1 |   471 |  2177K  (1)| 07:15:29 |
|  11 |      NESTED LOOPS                      |                              |     1 |   441 |  2177K  (1)| 07:15:29 |
|  12 |       NESTED LOOPS                     |                              |     1 |   354 |  2177K  (1)| 07:15:29 |
|  13 |        NESTED LOOPS                    |                              |     1 |   334 |  2177K  (1)| 07:15:29 |
|  14 |         NESTED LOOPS                   |                              |     1 |   278 |  2177K  (1)| 07:15:29 |
|  15 |          NESTED LOOPS                  |                              |     1 |   243 |  2177K  (1)| 07:15:29 |
|  16 |           NESTED LOOPS                 |                              |     1 |   194 |  2177K  (1)| 07:15:29 |
|  17 |            NESTED LOOPS                |                              |     1 |   173 |  2177K  (1)| 07:15:29 |
|* 18 |             TABLE ACCESS FULL          | GL_PERIODS                   |     1 |    53 |     4   (0)| 00:00:01 |
|  19 |              SORT AGGREGATE            |                              |     1 |    26 |            |          |
|* 20 |               TABLE ACCESS FULL        | GL_PERIODS                   |     5 |   130 |     4   (0)| 00:00:01 |
|* 21 |             TABLE ACCESS BY INDEX ROWID| RA_CUST_TRX_LINE_GL_DIST_ALL |     1 |   120 |  2177K  (1)|
|* 22 |              INDEX RANGE SCAN          | RA_CUST_TRX_LINE_GL_DIST_N2  |    48M|       |   315K  (1)| 0
|* 23 |            TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS    |     1 |    21 |     1   (0)| 
|* 24 |             INDEX UNIQUE SCAN          | HR_ORGANIZATION_UNITS_PK     |     1 |       |     0   (0)| 00
|  25 |           TABLE ACCESS BY INDEX ROWID  | GL_CODE_COMBINATIONS         |     1 |    49 |     2   (0)| 00
|* 26 |            INDEX UNIQUE SCAN           | GL_CODE_COMBINATIONS_U1      |     1 |       |     1   (0)| 00:
|* 27 |          TABLE ACCESS BY INDEX ROWID   | RA_CUSTOMER_TRX_LINES_ALL    |     1 |    35 |     3   (0)| 
|* 28 |           INDEX UNIQUE SCAN            | RA_CUSTOMER_TRX_LINES_U1     |     1 |       |     2   (0)| 00:0
|* 29 |         TABLE ACCESS BY INDEX ROWID    | RA_CUSTOMER_TRX_ALL          |     1 |    56 |     2   (0)| 00:
|* 30 |          INDEX UNIQUE SCAN             | RA_CUSTOMER_TRX_U1           |     1 |       |     1   (0)| 00:00:01 
|  31 |        TABLE ACCESS BY INDEX ROWID     | GL_SETS_OF_BOOKS             |     1 |    20 |     1   (0)|
|* 32 |         INDEX UNIQUE SCAN              | GL_SETS_OF_BOOKS_U2          |     1 |       |     0   (0)| 00:00:01 
|  33 |       TABLE ACCESS BY INDEX ROWID      | MTL_SYSTEM_ITEMS_B           |     1 |    87 |     2   (0
|* 34 |        INDEX UNIQUE SCAN               | MTL_SYSTEM_ITEMS_B_U1        |     1 |       |     1   (0)| 0
|* 35 |      TABLE ACCESS BY INDEX ROWID       | GL_PERIODS                   |     3 |    90 |     2   (0)| 00:0
|* 36 |       INDEX RANGE SCAN                 | GL_PERIODS_U2                |    13 |       |     1   (0)| 00:00:01 
|* 37 |     INDEX RANGE SCAN                   | XXG_GAAP_ITEM_ATTRIBUTE_UN   |     1 |       |     2   (0)| 00:
----------------------------------------------------------------------------------------------------

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

   1 - filter("EFFECTIVE_END_DATE" IS NULL)
   2 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINDURATIN')
   3 - filter("EFFECTIVE_END_DATE" IS NULL)
   4 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINSTRTDAT')
   5 - filter("XXG"."EFFECTIVE_END_DATE" IS NULL)
   6 - access("XXG"."SKU_NUM"=:B1 AND "XXG"."ATTRIBUTE_CODE"='FINCOGSTRE')
   8 - filter("XXG_GAAP_ITEM_ATTRIBUTE"."SKU_NUM"="MTL_SYSTEM_ITEMS_B"."SEGMENT1")
  18 - filter("GLP"."PERIOD_NUM"<>13 AND "GLP"."PERIOD_SET_NAME"='GlobalCalendar' AND CASE  WHEN
              (TRUNC(SYSDATE@!)>=TRUNC(INTERNAL_FUNCTION("GLP"."START_DATE")) AND
              TRUNC(SYSDATE@!)<=TRUNC(INTERNAL_FUNCTION("GLP"."END_DATE"))) THEN 'C' WHEN
              TRUNC(INTERNAL_FUNCTION("GLP"."END_DATE"))= (SELECT TRUNC(MAX("AL2"."START_DATE")-1) FROM "GL
              "AL2" WHERE "AL2"."PERIOD_NUM"<>13 AND TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SY
              "AL2"."PERIOD_SET_NAME"='GlobalCalendar') THEN 'P' ELSE NULL END ='P')
  20 - filter("AL2"."PERIOD_NUM"<>13 AND TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SYSDATE
              "AL2"."PERIOD_SET_NAME"='GlobalCalendar')
  21 - filter(("RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R101' OR
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R104' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIB
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R79' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBU
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE13"='Initial')
  22 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE">="GLP"."START_DATE" AND
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE"<="GLP"."END_DATE")
  23 - filter("HR_ALL_ORGANIZATION_UNITS"."ATTRIBUTE11"='APJ')
  24 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."ORG_ID"="HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID")
  26 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."CODE_COMBINATION_ID"="GL_CODE_COMBINATIONS"."CODE_COMB
              )
  27 - filter("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID" IS NOT NULL)
  28 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_LINE_ID"="RA_CUST_TRX_LINE_GL_DIST_ALL"."CUS
              INE_ID")
  29 - filter("RA_CUSTOMER_TRX_ALL"."INTERFACE_HEADER_CONTEXT"='ORDER ENTRY' AND
              "RA_CUSTOMER_TRX_ALL"."CREATED_FROM"='RAXTRX')
  30 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_ID"="RA_CUSTOMER_TRX_ALL"."CUSTOMER_TRX_ID")
  32 - access("RA_CUSTOMER_TRX_ALL"."SET_OF_BOOKS_ID"="GL_SETS_OF_BOOKS"."SET_OF_BOOKS_ID")
  34 - access("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_I
              "RA_CUSTOMER_TRX_LINES_ALL"."ORG_ID"="MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID")
  35 - filter("GLP"."QUARTER_NUM"="QUARTER_NUM")
  36 - access("PERIOD_SET_NAME"='GlobalCalendar' AND "GLP"."PERIOD_YEAR"="PERIOD_YEAR")
       filter("GLP"."PERIOD_YEAR"="PERIOD_YEAR")
  37 - access("XXG_GAAP_ITEM_ATTRIBUTE"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID"
              "XXG_GAAP_ITEM_ATTRIBUTE"."ATTRIBUTE_CODE"='FINREVCLS')


date explicitly given
Execution Plan
----------------------------------------------------------
Plan hash value: 3535227385

----------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                              |     1 |   520 |  5535   (1)| 00:01:07 |
|*  1 |  TABLE ACCESS BY INDEX ROWID             | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    33 |     4   (0)| 00:0
|*  2 |   INDEX RANGE SCAN                       | XXG_GAAP_ITEM_ATTRIBUTE_N1   |     1 |       |     3   (0)| 00:00:01 |
|*  3 |  TABLE ACCESS BY INDEX ROWID             | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    33 |     4   (0)| 00:0
|*  4 |   INDEX RANGE SCAN                       | XXG_GAAP_ITEM_ATTRIBUTE_N1   |     1 |       |     3   (0)| 00:00:01 |
|*  5 |  TABLE ACCESS BY INDEX ROWID             | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    33 |     4   (0)| 00:0
|*  6 |   INDEX RANGE SCAN                       | XXG_GAAP_ITEM_ATTRIBUTE_N1   |     1 |       |     3   (0)| 00:00:01 |
|   7 |  HASH GROUP BY                           |                              |     1 |   520 |  5535   (1)| 00:01:07 |
|*  8 |   FILTER                                 |                              |       |       |            |          |
|*  9 |    TABLE ACCESS BY INDEX ROWID           | GL_PERIODS                   |     1 |    53 |     2   (0)| 00:00:01 |
|  10 |     NESTED LOOPS                         |                              |     1 |   520 |  5530   (1)| 00:01:07 |
|  11 |      MERGE JOIN CARTESIAN                |                              |     1 |   467 |  5528   (1)| 00:01:07 |
|* 12 |       TABLE ACCESS BY INDEX ROWID        | XXG_GAAP_ITEM_ATTRIBUTE      |     1 |    49 |     3   (0)| 
|  13 |        NESTED LOOPS                      |                              |     1 |   437 |  5524   (1)| 00:01:07 |
|  14 |         NESTED LOOPS                     |                              |     1 |   388 |  5521   (1)| 00:01:07 |
|  15 |          NESTED LOOPS                    |                              |     1 |   368 |  5520   (1)| 00:01:07 |
|  16 |           NESTED LOOPS                   |                              |     1 |   312 |  5518   (1)| 00:01:07 |
|  17 |            NESTED LOOPS                  |                              |     1 |   225 |  5516   (1)| 00:01:07 |
|  18 |             NESTED LOOPS                 |                              |     1 |   190 |  5513   (1)| 00:01:07 |
|  19 |              NESTED LOOPS                |                              |     1 |   141 |  5511   (1)| 00:01:07 |
|* 20 |               TABLE ACCESS FULL          | HR_ALL_ORGANIZATION_UNITS    |     1 |    21 |     3   (0)| 00:00:
|* 21 |               TABLE ACCESS BY INDEX ROWID| RA_CUST_TRX_LINE_GL_DIST_ALL |     1 |   120 |  5508   (
|* 22 |                INDEX RANGE SCAN          | RA_CUST_TRX_LINE_GL_DIST_N2  | 25095 |       |  4549   (1)| 00:00:55 |
|  23 |              TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS         |     1 |    49 |     2   (0)| 00:
|* 24 |               INDEX UNIQUE SCAN          | GL_CODE_COMBINATIONS_U1      |     1 |       |     1   (0)| 00:00:01 |
|* 25 |             TABLE ACCESS BY INDEX ROWID  | RA_CUSTOMER_TRX_LINES_ALL    |     1 |    35 |     3   (0)|
|* 26 |              INDEX UNIQUE SCAN           | RA_CUSTOMER_TRX_LINES_U1     |     1 |       |     2   (0)| 00:00:01 |
|  27 |            TABLE ACCESS BY INDEX ROWID   | MTL_SYSTEM_ITEMS_B           |     1 |    87 |     2   (0)| 00:00:
|* 28 |             INDEX UNIQUE SCAN            | MTL_SYSTEM_ITEMS_B_U1        |     1 |       |     1   (0)| 00:00:01 |
|* 29 |           TABLE ACCESS BY INDEX ROWID    | RA_CUSTOMER_TRX_ALL          |     1 |    56 |     2   (0)| 00:00:
|* 30 |            INDEX UNIQUE SCAN             | RA_CUSTOMER_TRX_U1           |     1 |       |     1   (0)| 00:00:01 |
|  31 |          TABLE ACCESS BY INDEX ROWID     | GL_SETS_OF_BOOKS             |     1 |    20 |     1   (0)| 00:00:01 |
|* 32 |           INDEX UNIQUE SCAN              | GL_SETS_OF_BOOKS_U2          |     1 |       |     0   (0)| 00:00:01 |
|* 33 |         INDEX RANGE SCAN                 | XXG_GAAP_ITEM_ATTRIBUTE_UN   |     1 |       |     2   (0)| 00:00:01 |
|  34 |       BUFFER SORT                        |                              |   104 |  3120 |  5525   (1)| 00:01:07 |
|* 35 |        TABLE ACCESS FULL                 | GL_PERIODS                   |   104 |  3120 |     4   (0)| 00:00:01 |
|* 36 |      INDEX RANGE SCAN                    | GL_PERIODS_U2                |    12 |       |     1   (0)| 00:00:01 |
|  37 |     SORT AGGREGATE                       |                              |     1 |    26 |            |          |
|* 38 |      TABLE ACCESS FULL                   | GL_PERIODS                   |     5 |   130 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   1 - filter("EFFECTIVE_END_DATE" IS NULL)
   2 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINDURATIN')
   3 - filter("EFFECTIVE_END_DATE" IS NULL)
   4 - access("SKU_NUM"=:B1 AND "ATTRIBUTE_CODE"='FINSTRTDAT')
   5 - filter("XXG"."EFFECTIVE_END_DATE" IS NULL)
   6 - access("XXG"."SKU_NUM"=:B1 AND "XXG"."ATTRIBUTE_CODE"='FINCOGSTRE')
   8 - filter(SYSDATE@!-30<=SYSDATE@!)
   9 - filter("GLP"."QUARTER_NUM"="QUARTER_NUM" AND CASE  WHEN
              (TRUNC(SYSDATE@!)>=TRUNC(INTERNAL_FUNCTION("GLP"."START_DATE")) AND
              TRUNC(SYSDATE@!)<=TRUNC(INTERNAL_FUNCTION("GLP"."END_DATE"))) THEN 'C' WHEN
              TRUNC(INTERNAL_FUNCTION("GLP"."END_DATE"))= (SELECT TRUNC(MAX("AL2"."START_DATE")-1) FROM "GL
              "AL2" WHERE "AL2"."PERIOD_NUM"<>13 AND TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SY
              "AL2"."PERIOD_SET_NAME"='GlobalCalendar') THEN 'P' ELSE NULL END ='P')
  12 - filter("XXG_GAAP_ITEM_ATTRIBUTE"."SKU_NUM"="MTL_SYSTEM_ITEMS_B"."SEGMENT1")
  20 - filter("HR_ALL_ORGANIZATION_UNITS"."ATTRIBUTE11"='APJ')
  21 - filter(("RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R101' OR
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R104' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIB
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE11"='R79' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBU
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."ATTRIBUTE13"='Initial')
  22 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE">=SYSDATE@!-30 AND
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."ORG_ID"="HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID" AND
              "RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE"<=SYSDATE@!)
       filter("RA_CUST_TRX_LINE_GL_DIST_ALL"."ORG_ID"="HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID")
  24 - access("RA_CUST_TRX_LINE_GL_DIST_ALL"."CODE_COMBINATION_ID"="GL_CODE_COMBINATIONS"."CODE_COMB
  25 - filter("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID" IS NOT NULL)
  26 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_LINE_ID"="RA_CUST_TRX_LINE_GL_DIST_ALL"."CUS
              E_ID")
  28 - access("RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_I
              "RA_CUSTOMER_TRX_LINES_ALL"."ORG_ID"="MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID")
  29 - filter("RA_CUSTOMER_TRX_ALL"."INTERFACE_HEADER_CONTEXT"='ORDER ENTRY' AND
              "RA_CUSTOMER_TRX_ALL"."CREATED_FROM"='RAXTRX')
  30 - access("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_ID"="RA_CUSTOMER_TRX_ALL"."CUSTOMER_TRX_ID")
  32 - access("RA_CUSTOMER_TRX_ALL"."SET_OF_BOOKS_ID"="GL_SETS_OF_BOOKS"."SET_OF_BOOKS_ID")
  33 - access("XXG_GAAP_ITEM_ATTRIBUTE"."INVENTORY_ITEM_ID"="MTL_SYSTEM_ITEMS_B"."INVENTORY_ITEM_ID"
              "XXG_GAAP_ITEM_ATTRIBUTE"."ATTRIBUTE_CODE"='FINREVCLS')
  35 - filter("PERIOD_SET_NAME"='GlobalCalendar')
  36 - access("GLP"."PERIOD_SET_NAME"='GlobalCalendar' AND "GLP"."PERIOD_YEAR"="PERIOD_YEAR")
       filter("GLP"."PERIOD_NUM"<>13 AND "GLP"."PERIOD_YEAR"="PERIOD_YEAR")
  38 - filter("AL2"."PERIOD_NUM"<>13 AND TRUNC(INTERNAL_FUNCTION("AL2"."START_DATE"))<=TRUNC(SYSDATE
              "AL2"."PERIOD_SET_NAME"='GlobalCalendar')   
Re: query tuning [message #426246 is a reply to message #426042] Wed, 14 October 2009 10:02 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well the first thing I'd do is simplify that inline view, because you appear do selecting far more things in it than you actually use. As far as I can see you need to select only 2 columns from it.
And the case statement in the inline view should just be part of its where clause.

So simplify all that and run it again - probably won't speed it up but the more concise it is the easier it'll be for the rest of us to help you.

Also pretty sure you're outer join isn't actually outer-joining since you haven't specified it in enough places - so you might want to remove that or fix it so it works.
Re: query tuning [message #426966 is a reply to message #426042] Tue, 20 October 2009 07:33 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post TKPROF report.
Previous Topic: Execution plan for every SQL, executed by user
Next Topic: How to view Oracle counters in windows performance monitor
Goto Forum:
  


Current Time: Sat Sep 24 16:21:36 CDT 2016

Total time taken to generate the page: 0.09935 seconds