Home » RDBMS Server » Performance Tuning » Oracle audit view performance issue (Oracle Apps R12)
Oracle audit view performance issue [message #436634] Thu, 24 December 2009 16:45 Go to next message
sreejit
Messages: 3
Registered: December 2009
Junior Member
Hi All,

I am using oracle standard view in my query. This view used in oracle apps R12.

My sql query using view is taking long time and it is doing full scan on po_line_locations_all table. But when I am inserting the required data to new temporary table and run my query using that new table it is running fast. SO it seems that the problem is with view. Can anybody please help me to find is there any problem with my query or the problem with View?

Regards,
Sreejit

My sql query:

/* Formatted on 2009/12/24 22:40 (Formatter Plus v4.8.8) */
SELECT haou.NAME organisation_name
      ,hla.location_code location_name
      ,pha.segment1 po_number
      ,pla.line_num line_number
      ,pha.attribute1 contract_number
      ,msi.segment1 item
  FROM po_line_locations_all pll
      ,hr_all_organization_units haou
      ,hr_locations_all hla
      ,po_headers_all pha
      ,po_lines_all pla
      ,mtl_system_items_b msi
      ,financials_system_params_all fsp
      ,po_line_locations_all_ac1 plla
 WHERE pll.po_header_id = pha.po_header_id
   AND pha.po_header_id = pla.po_header_id
   AND pll.po_line_id = pla.po_line_id
   AND pla.org_id = fsp.org_id
   AND pla.item_id = msi.inventory_item_id
   AND msi.organization_id = fsp.inventory_organization_id
   AND pll.ship_to_organization_id = haou.organization_id(+)
   AND pll.ship_to_location_id = hla.location_id(+)
   AND pll.shipment_type = 'PRICE BREAK'
   AND plla.line_location_id = pll.line_location_id
   AND plla.audit_transaction_type <> 'C'
   AND plla.audit_timestamp BETWEEN TO_DATE (NVL ('01/01/2009 00:00:00', SYSDATE)
                                            ,'DD/MM/YYYY HH24:Mi:SS'
                                            )
                                AND TO_DATE (NVL ('01/12/2009 23:59:59', SYSDATE)
                                            ,'DD/MM/YYYY HH24:Mi:SS'
                                            )




The view is

/* Formatted on 2009/12/24 22:41 (Formatter Plus v4.8.8) */
DROP VIEW apps.po_line_locations_all_ac1;

CREATE OR REPLACE FORCE VIEW apps.po_line_locations_all_ac1 (line_location_id
                                                            ,audit_timestamp
                                                            ,audit_sequence_id
                                                            ,audit_session_id
                                                            ,audit_transaction_type
                                                            ,audit_user_name
                                                            ,audit_commit_id
                                                            ,row_key
                                                            ,start_date
                                                            ,end_date
                                                            ,price_discount
                                                            ,po_header_id
                                                            ,po_line_id
                                                            ,quantity
                                                            ,ship_to_location_id
                                                            ,price_override
                                                            ,ship_to_organization_id
                                                            ,shipment_num
                                                            )
AS
   SELECT /*+ LEADING(o) PUSH_SUBQ */
          o.line_location_id
         ,o.audit_timestamp
         ,o.audit_sequence_id
         ,o.audit_session_id
         ,o.audit_transaction_type
         ,o.audit_user_name
         ,o.audit_commit_id
         ,o.row_key
         , (SELECT n2.start_date
              FROM po_line_locations_all_av2 n2
             WHERE 1 = 1
               AND (n2.line_location_id = o.line_location_id)
               AND n2.row_key > o.row_key
               AND ROWNUM = 1) start_date
         , (SELECT n3.end_date
              FROM po_line_locations_all_av3 n3
             WHERE 1 = 1
               AND (n3.line_location_id = o.line_location_id)
               AND n3.row_key > o.row_key
               AND ROWNUM = 1) end_date
         , (SELECT n4.price_discount
              FROM po_line_locations_all_av4 n4
             WHERE 1 = 1
               AND (n4.line_location_id = o.line_location_id)
               AND n4.row_key > o.row_key
               AND ROWNUM = 1) price_discount
         , (SELECT n5.po_header_id
              FROM po_line_locations_all_av5 n5
             WHERE 1 = 1
               AND (n5.line_location_id = o.line_location_id)
               AND n5.row_key > o.row_key
               AND ROWNUM = 1) po_header_id
         , (SELECT n6.po_line_id
              FROM po_line_locations_all_av6 n6
             WHERE 1 = 1
               AND (n6.line_location_id = o.line_location_id)
               AND n6.row_key > o.row_key
               AND ROWNUM = 1) po_line_id
         , (SELECT n7.quantity
              FROM po_line_locations_all_av7 n7
             WHERE 1 = 1
               AND (n7.line_location_id = o.line_location_id)
               AND n7.row_key > o.row_key
               AND ROWNUM = 1) quantity
         , (SELECT n8.ship_to_location_id
              FROM po_line_locations_all_av8 n8
             WHERE 1 = 1
               AND (n8.line_location_id = o.line_location_id)
               AND n8.row_key > o.row_key
               AND ROWNUM = 1) ship_to_location_id
         , (SELECT n9.price_override
              FROM po_line_locations_all_av9 n9
             WHERE 1 = 1
               AND (n9.line_location_id = o.line_location_id)
               AND n9.row_key > o.row_key
               AND ROWNUM = 1) price_override
         , (SELECT n10.ship_to_organization_id
              FROM po_line_locations_all_av10 n10
             WHERE 1 = 1
               AND (n10.line_location_id = o.line_location_id)
               AND n10.row_key > o.row_key
               AND ROWNUM = 1) ship_to_organization_id
         , (SELECT n11.shipment_num
              FROM po_line_locations_all_av11 n11
             WHERE 1 = 1
               AND (n11.line_location_id = o.line_location_id)
               AND n11.row_key > o.row_key
               AND ROWNUM = 1) shipment_num
     FROM po_line_locations_all_av1 o
    WHERE 1 = 1
      AND EXISTS (SELECT 'C'
                    FROM po_line_locations_all_av2 e
                   WHERE 1 = 1
                     AND (e.line_location_id = o.line_location_id)
                     AND e.row_key > o.row_key)
      AND EXISTS (SELECT 'C'
                    FROM po_line_locations_all_av3 e
                   WHERE 1 = 1
                     AND (e.line_location_id = o.line_location_id)
                     AND e.row_key > o.row_key)
      AND EXISTS (SELECT 'C'
                    FROM po_line_locations_all_av4 e
                   WHERE 1 = 1
                     AND (e.line_location_id = o.line_location_id)
                     AND e.row_key > o.row_key)
      AND EXISTS (SELECT 'C'
                    FROM po_line_locations_all_av5 e
                   WHERE 1 = 1
                     AND (e.line_location_id = o.line_location_id)
                     AND e.row_key > o.row_key)
      AND EXISTS (SELECT 'C'
                    FROM po_line_locations_all_av6 e
                   WHERE 1 = 1
                     AND (e.line_location_id = o.line_location_id)
                     AND e.row_key > o.row_key)
      AND EXISTS (SELECT 'C'
                    FROM po_line_locations_all_av7 e
                   WHERE 1 = 1
                     AND (e.line_location_id = o.line_location_id)
                     AND e.row_key > o.row_key)
      AND EXISTS (SELECT 'C'
                    FROM po_line_locations_all_av8 e
                   WHERE 1 = 1
                     AND (e.line_location_id = o.line_location_id)
                     AND e.row_key > o.row_key)
      AND EXISTS (SELECT 'C'
                    FROM po_line_locations_all_av9 e
                   WHERE 1 = 1
                     AND (e.line_location_id = o.line_location_id)
                     AND e.row_key > o.row_key)
      AND EXISTS (SELECT 'C'
                    FROM po_line_locations_all_av10 e
                   WHERE 1 = 1
                     AND (e.line_location_id = o.line_location_id)
                     AND e.row_key > o.row_key)
      AND EXISTS (SELECT 'C'
                    FROM po_line_locations_all_av11 e
                   WHERE 1 = 1
                     AND (e.line_location_id = o.line_location_id)
                     AND e.row_key > o.row_key)
   UNION ALL
   SELECT d.line_location_id
         ,d.audit_timestamp
         ,d.audit_sequence_id
         ,d.audit_session_id
         ,d.audit_transaction_type
         ,d.audit_user_name
         ,d.audit_commit_id
         ,row_key
         ,d.start_date
         ,d.end_date
         ,d.price_discount
         ,d.po_header_id
         ,d.po_line_id
         ,d.quantity
         ,d.ship_to_location_id
         ,d.price_override
         ,d.ship_to_organization_id
         ,d.shipment_num
     FROM po_line_locations_all_a d
    WHERE d.audit_transaction_type = 'D'
   UNION ALL
   SELECT c.line_location_id
         ,SYSDATE
         ,0
         ,USERENV ('SESSIONID')
         ,'C'
         ,NULL
         ,0
         ,999912312359591111122222 row_key
         ,c.start_date
         ,c.end_date
         ,c.price_discount
         ,c.po_header_id
         ,c.po_line_id
         ,c.quantity
         ,c.ship_to_location_id
         ,c.price_override
         ,c.ship_to_organization_id
         ,c.shipment_num
     FROM po_line_locations_all c;



the view used inside it is
/* Formatted on 2009/12/24 22:42 (Formatter Plus v4.8.8) */
DROP VIEW apps.po_line_locations_all_av1;

CREATE OR REPLACE FORCE VIEW apps.po_line_locations_all_av1 (line_location_id
                                                            ,audit_timestamp
                                                            ,audit_sequence_id
                                                            ,audit_session_id
                                                            ,audit_transaction_type
                                                            ,audit_user_name
                                                            ,audit_commit_id
                                                            ,audit_true_nulls
                                                            ,row_key
                                                            )
AS
   SELECT line_location_id
         ,audit_timestamp
         ,audit_sequence_id
         ,audit_session_id
         ,audit_transaction_type
         ,audit_user_name
         ,audit_commit_id
         ,audit_true_nulls
         ,row_key
     FROM po_line_locations_all_a
    WHERE audit_transaction_type > 'D'
      AND (   audit_transaction_type = 'I'
           OR start_date IS NOT NULL
           OR end_date IS NOT NULL
           OR price_discount IS NOT NULL
           OR po_header_id IS NOT NULL
           OR po_line_id IS NOT NULL
           OR quantity IS NOT NULL
           OR ship_to_location_id IS NOT NULL
           OR price_override IS NOT NULL
           OR ship_to_organization_id IS NOT NULL
           OR shipment_num IS NOT NULL
           OR audit_true_nulls IS NOT NULL
          );
Re: Oracle audit view performance issue [message #436636 is a reply to message #436634] Thu, 24 December 2009 16:53 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Oracle audit view performance issue [message #436642 is a reply to message #436634] Thu, 24 December 2009 20:02 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
FROM hr_all_organization_units haou
,hr_locations_all hla
,po_headers_all pha
,po_lines_all pla
,mtl_system_items_b msi

The FROM clause should exist as modified above, because the removed tables provide no data to SELECT clause.

IF TABLES po_line_locations_all pll ,financials_system_params_all fsp ,po_line_locations_all_ac1 plla
are needed for data filtering they should be subordinated into the WHERE clause.
Re: Oracle audit view performance issue [message #437683 is a reply to message #436642] Tue, 05 January 2010 08:50 Go to previous messageGo to next message
sreejit
Messages: 3
Registered: December 2009
Junior Member
Hi Sorry for late reply.

The actual SQL query is
SELECT   TO_CHAR (plla.audit_timestamp, 'YYYYMMDDHH24MISS') eventtimestamp
        ,plla.audit_timestamp audit_timestamp
        ,plla.audit_transaction_type txntype
        ,SUBSTR (plla.audit_user_name, 1, 16) user_id
        ,xer.en_eventnumber eventnumber
        ,plla.line_location_id line_location_id
        ,plla.po_header_id po_header_id
        ,plla.po_line_id po_line_id
        ,plla.shipment_num shipment_number
        ,plla.price_override price_override
        ,haou.NAME organisation_name
        ,hla.location_code location_name
        ,plla.quantity quantity
        ,TO_CHAR (plla.start_date, 'DD-MON-YYYY') start_date
        ,TO_CHAR (plla.end_date, 'DD-MON-YYYY') end_date
        ,plla.price_discount price_discount
        ,plla.ship_to_location_id ship_to_location_id
        ,plla.ship_to_organization_id ship_to_organization_id
        ,pha.segment1 po_number
        ,pla.line_num line_number
        ,pha.attribute1 contract_number
        ,msi.segment1 item
    FROM po_line_locations_all_ac1 plla
        ,po_line_locations_all pll
        ,hr_all_organization_units haou
        ,hr_locations_all hla
        ,xxprp_event_ref xer
        ,po_headers_all pha
        ,po_lines_all pla
        ,mtl_system_items_b msi
        ,financials_system_params_all fsp
   WHERE plla.audit_transaction_type <> 'C'
     AND plla.line_location_id = pll.line_location_id
     AND pll.shipment_type = 'PRICE BREAK'
     AND plla.po_header_id = pll.po_header_id
     AND plla.po_header_id = pha.po_header_id
     AND plla.po_line_id = pll.po_line_id
     AND plla.po_header_id = pla.po_header_id
     AND plla.po_line_id = pla.po_line_id
     AND pla.org_id = fsp.org_id
     AND pla.item_id = msi.inventory_item_id
     AND msi.organization_id = fsp.inventory_organization_id
     AND DECODE (plla.audit_transaction_type
                ,'I', 'Create price breaks'
                ,'U', 'Update price breaks'
                ,NULL
                ) = xer.ed_eventdesc
     AND plla.audit_timestamp BETWEEN TO_DATE (NVL ('01/01/2009 00:00:00', SYSDATE)
                                              ,'DD/MM/YYYY HH24:Mi:SS'
                                              )
                                  AND TO_DATE (NVL ('01/12/2009 23:59:59', SYSDATE)
                                              ,'DD/MM/YYYY HH24:Mi:SS'
                                              )
     AND plla.ship_to_organization_id = haou.organization_id(+)
     AND plla.ship_to_location_id = hla.location_id(+)
ORDER BY plla.line_location_id, plla.audit_timestamp;


and the expalin plan is
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
| Id  | Operation                                | Name                         | Rows  | Bytes | Cost (%CPU)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   0 | SELECT STATEMENT                         |                              |     1 |   437 |    53  (14)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   1 |  SORT ORDER BY                           |                              |     1 |   437 |    53  (14)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|*  2 |   TABLE ACCESS BY INDEX ROWID            | PO_LINE_LOCATIONS_ALL        |     1 |    21 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   3 |    NESTED LOOPS                          |                              |     1 |   437 |    52  (12)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   4 |     NESTED LOOPS                         |                              |     1 |   416 |    51  (12)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|*  5 |      HASH JOIN                           |                              |     1 |   395 |    50  (12)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   6 |       NESTED LOOPS OUTER                 |                              |     1 |   337 |    47  (13)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|   7 |        NESTED LOOPS OUTER                |                              |     1 |   314 |    46  (14)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|*  8 |         HASH JOIN                        |                              |     1 |   269 |    46  (14)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

|*  9 |          TABLE ACCESS BY INDEX ROWID     | PO_LINES_ALL                 |     1 |    17 |     0   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  10 |           NESTED LOOPS                   |                              |     1 |    71 |     6   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  11 |            NESTED LOOPS                  |                              |     1 |    54 |     6   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  12 |             TABLE ACCESS FULL            | FINANCIALS_SYSTEM_PARAMS_ALL |     1 |     6 |     5   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  13 |             TABLE ACCESS BY INDEX ROWID  | MTL_SYSTEM_ITEMS_B           |     1 |    48 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 14 |              INDEX RANGE SCAN            | MTL_SYSTEM_ITEMS_B_N1        |     1 |       |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 15 |            INDEX RANGE SCAN              | PO_LINES_N1                  |    53 |       |     0   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  16 |          VIEW                            | PO_LINE_LOCATIONS_ALL_AC1    |     3 |   594 |    39  (13)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  17 |           UNION-ALL                      |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 18 |            HASH JOIN SEMI                |                              |     1 |   637 |    37  (14)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 19 |             HASH JOIN SEMI               |                              |     1 |   611 |    34  (15)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 20 |              HASH JOIN SEMI              |                              |     1 |   585 |    30  (14)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 21 |               HASH JOIN SEMI             |                              |     1 |   559 |    27  (15)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

|* 22 |                HASH JOIN SEMI            |                              |     1 |   533 |    23  (14)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 23 |                 HASH JOIN SEMI           |                              |     1 |   507 |    20  (15)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 24 |                  HASH JOIN SEMI          |                              |     1 |   481 |    16  (13)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 25 |                   HASH JOIN SEMI         |                              |     1 |   455 |    13  (16)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 26 |                    HASH JOIN SEMI        |                              |     1 |   429 |     9  (12)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 27 |                     HASH JOIN SEMI       |                              |     1 |   403 |     6  (17)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 28 |                      TABLE ACCESS FULL   | PO_LINE_LOCATIONS_ALL_A      |     1 |   377 |     2   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  29 |                      VIEW                | PO_LINE_LOCATIONS_ALL_AV11   |   205 |  5330 |     3   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  30 |                       UNION-ALL          |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 31 |                        FILTER            |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 32 |                         TABLE ACCESS FULL| PO_LINE_LOCATIONS_ALL_A      |     1 |   166 |     2   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 33 |                        FILTER            |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  34 |                         INDEX FULL SCAN  | PO_LINE_LOCATIONS_U1         |   204 |   816 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

|  35 |                     VIEW                 | PO_LINE_LOCATIONS_ALL_AV10   |   205 |  5330 |     3   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  36 |                      UNION-ALL           |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 37 |                       TABLE ACCESS FULL  | PO_LINE_LOCATIONS_ALL_A      |     1 |   166 |     2   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  38 |                       INDEX FULL SCAN    | PO_LINE_LOCATIONS_U1         |   204 |   816 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  39 |                    VIEW                  | PO_LINE_LOCATIONS_ALL_AV9    |   205 |  5330 |     3   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  40 |                     UNION-ALL            |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 41 |                      TABLE ACCESS FULL   | PO_LINE_LOCATIONS_ALL_A      |     1 |   166 |     2   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  42 |                      INDEX FULL SCAN     | PO_LINE_LOCATIONS_U1         |   204 |   816 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  43 |                   VIEW                   | PO_LINE_LOCATIONS_ALL_AV8    |   205 |  5330 |     3   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  44 |                    UNION-ALL             |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 45 |                     TABLE ACCESS FULL    | PO_LINE_LOCATIONS_ALL_A      |     1 |   166 |     2   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  46 |                     INDEX FULL SCAN      | PO_LINE_LOCATIONS_U1         |   204 |   816 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  47 |                  VIEW                    | PO_LINE_LOCATIONS_ALL_AV7    |   205 |  5330 |     3   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

|  48 |                   UNION-ALL              |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 49 |                    TABLE ACCESS FULL     | PO_LINE_LOCATIONS_ALL_A      |     1 |   166 |     2   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  50 |                    INDEX FULL SCAN       | PO_LINE_LOCATIONS_U1         |   204 |   816 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  51 |                 VIEW                     | PO_LINE_LOCATIONS_ALL_AV6    |   205 |  5330 |     3   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  52 |                  UNION-ALL               |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 53 |                   TABLE ACCESS FULL      | PO_LINE_LOCATIONS_ALL_A      |     1 |   166 |     2   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  54 |                   INDEX FULL SCAN        | PO_LINE_LOCATIONS_U1         |   204 |   816 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  55 |                VIEW                      | PO_LINE_LOCATIONS_ALL_AV5    |   205 |  5330 |     3   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  56 |                 UNION-ALL                |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 57 |                  TABLE ACCESS FULL       | PO_LINE_LOCATIONS_ALL_A      |     1 |   166 |     2   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  58 |                  INDEX FULL SCAN         | PO_LINE_LOCATIONS_U1         |   204 |   816 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  59 |               VIEW                       | PO_LINE_LOCATIONS_ALL_AV4    |   205 |  5330 |     3   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  60 |                UNION-ALL                 |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

|* 61 |                 TABLE ACCESS FULL        | PO_LINE_LOCATIONS_ALL_A      |     1 |   166 |     2   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  62 |                 INDEX FULL SCAN          | PO_LINE_LOCATIONS_U1         |   204 |   816 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  63 |              VIEW                        | PO_LINE_LOCATIONS_ALL_AV3    |   205 |  5330 |     3   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  64 |               UNION-ALL                  |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 65 |                TABLE ACCESS FULL         | PO_LINE_LOCATIONS_ALL_A      |     1 |   162 |     2   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  66 |                INDEX FULL SCAN           | PO_LINE_LOCATIONS_U1         |   204 |   816 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  67 |             VIEW                         | PO_LINE_LOCATIONS_ALL_AV2    |   205 |  5330 |     3   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  68 |              UNION-ALL                   |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 69 |               TABLE ACCESS FULL          | PO_LINE_LOCATIONS_ALL_A      |     1 |   162 |     2   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  70 |               INDEX FULL SCAN            | PO_LINE_LOCATIONS_U1         |   204 |   816 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 71 |            FILTER                        |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 72 |             TABLE ACCESS FULL            | PO_LINE_LOCATIONS_ALL_A      |     1 |   250 |     2   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 73 |            FILTER                        |                              |       |       |            |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

|  74 |             TABLE ACCESS FULL            | PO_LINE_LOCATIONS_ALL        |   204 |  8976 |     5   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  75 |         TABLE ACCESS BY INDEX ROWID      | HR_LOCATIONS_ALL             |     1 |    45 |     0   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 76 |          INDEX UNIQUE SCAN               | HR_LOCATIONS_PK              |     1 |       |     0   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  77 |        TABLE ACCESS BY INDEX ROWID       | HR_ALL_ORGANIZATION_UNITS    |     1 |    23 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 78 |         INDEX UNIQUE SCAN                | HR_ORGANIZATION_UNITS_PK     |     1 |       |     0   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  79 |       TABLE ACCESS FULL                  | XXPRP_EVENT_REF              |    17 |   986 |     3   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|  80 |      TABLE ACCESS BY INDEX ROWID         | PO_HEADERS_ALL               |     1 |    21 |     1   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 81 |       INDEX UNIQUE SCAN                  | PO_HEADERS_U1                |     1 |       |     0   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
|* 82 |     INDEX RANGE SCAN                     | PO_LINE_LOCATIONS_N1         |     1 |       |     0   (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
---------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
   2 - filter("PLLA"."LINE_LOCATION_ID"="PLL"."LINE_LOCATION_ID" AND                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
              "PLLA"."PO_HEADER_ID"="PLL"."PO_HEADER_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
   5 - access("XER"."ED_EVENTDESC"=DECODE("PLLA"."AUDIT_TRANSACTION_TYPE",'I','Create price                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
              breaks','U','Update price breaks',NULL))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
   8 - access("PLLA"."PO_HEADER_ID"="PLA"."PO_HEADER_ID" AND "PLLA"."PO_LINE_ID"="PLA"."PO_LINE_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
   9 - filter("PLA"."ORG_ID"="FSP"."ORG_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  14 - access("MSI"."ORGANIZATION_ID"="FSP"."INVENTORY_ORGANIZATION_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  15 - access("PLA"."ITEM_ID"="MSI"."INVENTORY_ITEM_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  18 - access("E"."LINE_LOCATION_ID"="LINE_LOCATION_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       filter("E"."ROW_KEY">"ROW_KEY")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  19 - access("E"."LINE_LOCATION_ID"="LINE_LOCATION_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       filter("E"."ROW_KEY">"ROW_KEY")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

  20 - access("E"."LINE_LOCATION_ID"="LINE_LOCATION_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       filter("E"."ROW_KEY">"ROW_KEY")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  21 - access("E"."LINE_LOCATION_ID"="LINE_LOCATION_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       filter("E"."ROW_KEY">"ROW_KEY")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  22 - access("E"."LINE_LOCATION_ID"="LINE_LOCATION_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       filter("E"."ROW_KEY">"ROW_KEY")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  23 - access("E"."LINE_LOCATION_ID"="LINE_LOCATION_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       filter("E"."ROW_KEY">"ROW_KEY")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  24 - access("E"."LINE_LOCATION_ID"="LINE_LOCATION_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       filter("E"."ROW_KEY">"ROW_KEY")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  25 - access("E"."LINE_LOCATION_ID"="LINE_LOCATION_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       filter("E"."ROW_KEY">"ROW_KEY")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  26 - access("E"."LINE_LOCATION_ID"="LINE_LOCATION_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

       filter("E"."ROW_KEY">"ROW_KEY")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  27 - access("E"."LINE_LOCATION_ID"="LINE_LOCATION_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
       filter("E"."ROW_KEY">"ROW_KEY")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  28 - filter("AUDIT_TRANSACTION_TYPE">'D' AND ("START_DATE" IS NOT NULL OR "END_DATE" IS NOT NULL OR                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
              "PRICE_DISCOUNT" IS NOT NULL OR "PO_HEADER_ID" IS NOT NULL OR "PO_LINE_ID" IS NOT NULL OR "QUANTITY"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
              IS NOT NULL OR "SHIP_TO_LOCATION_ID" IS NOT NULL OR "PRICE_OVERRIDE" IS NOT NULL OR                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
              "SHIP_TO_ORGANIZATION_ID" IS NOT NULL OR "SHIPMENT_NUM" IS NOT NULL OR "AUDIT_TRUE_NULLS" IS NOT NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
              OR "AUDIT_TRANSACTION_TYPE"='I') AND "AUDIT_TIMESTAMP">=TO_DATE(NVL('01/01/2009                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
              00:00:00',TO_CHAR(SYSDATE@!)),'DD/MM/YYYY HH24:Mi:SS') AND "AUDIT_TIMESTAMP"<=TO_DATE(NVL('01/12/2009                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
              23:59:59',TO_CHAR(SYSDATE@!)),'DD/MM/YYYY HH24:Mi:SS') AND "AUDIT_TRANSACTION_TYPE"<>'C')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
  31 - filter(TO_DATE(NVL('01/01/2009 00:00:00',TO_CHAR(SYSDATE@!)),'DD/MM/YYYY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
              HH24:Mi:SS')<=TO_DATE(NVL('01/12/2009 23:59:59',TO_CHAR(SYSDATE@!)),'DD/MM/YYYY HH24:Mi:SS'))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
  32 - filter("SHIPMENT_NUM" IS NOT NULL OR SUBSTR("AUDIT_TRUE_NULLS",11,1)='Y')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

  33 - filter(TO_DATE(NVL('01/01/2009 00:00:00',TO_CHAR(SYSDATE@!)),'DD/MM/YYYY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
              HH24:Mi:SS')<=TO_DATE(NVL('01/12/2009 23:59:59',TO_CHAR(SYSDATE@!)),'DD/MM/YYYY HH24:Mi:SS'))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
  37 - filter("SHIP_TO_ORGANIZATION_ID" IS NOT NULL OR SUBSTR("AUDIT_TRUE_NULLS",10,1)='Y')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
  41 - filter("PRICE_OVERRIDE" IS NOT NULL OR SUBSTR("AUDIT_TRUE_NULLS",9,1)='Y')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  45 - filter("SHIP_TO_LOCATION_ID" IS NOT NULL OR SUBSTR("AUDIT_TRUE_NULLS",8,1)='Y')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
  49 - filter("QUANTITY" IS NOT NULL OR SUBSTR("AUDIT_TRUE_NULLS",7,1)='Y')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
  53 - filter("PO_LINE_ID" IS NOT NULL OR SUBSTR("AUDIT_TRUE_NULLS",6,1)='Y')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  57 - filter("PO_HEADER_ID" IS NOT NULL OR SUBSTR("AUDIT_TRUE_NULLS",5,1)='Y')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
  61 - filter("PRICE_DISCOUNT" IS NOT NULL OR SUBSTR("AUDIT_TRUE_NULLS",4,1)='Y')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  65 - filter("END_DATE" IS NOT NULL OR SUBSTR("AUDIT_TRUE_NULLS",3,1)='Y')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
  69 - filter("START_DATE" IS NOT NULL OR SUBSTR("AUDIT_TRUE_NULLS",2,1)='Y')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  71 - filter(TO_DATE(NVL('01/01/2009 00:00:00',TO_CHAR(SYSDATE@!)),'DD/MM/YYYY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
              HH24:Mi:SS')<=TO_DATE(NVL('01/12/2009 23:59:59',TO_CHAR(SYSDATE@!)),'DD/MM/YYYY HH24:Mi:SS'))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             

  72 - filter("D"."AUDIT_TRANSACTION_TYPE"='D' AND "D"."AUDIT_TIMESTAMP">=TO_DATE(NVL('01/01/2009                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
              00:00:00',TO_CHAR(SYSDATE@!)),'DD/MM/YYYY HH24:Mi:SS') AND                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
              "D"."AUDIT_TIMESTAMP"<=TO_DATE(NVL('01/12/2009 23:59:59',TO_CHAR(SYSDATE@!)),'DD/MM/YYYY HH24:Mi:SS')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
              AND "D"."AUDIT_TRANSACTION_TYPE"<>'C')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  73 - filter(SYSDATE@!<=TO_DATE(NVL('01/12/2009 23:59:59',TO_CHAR(SYSDATE@!)),'DD/MM/YYYY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
              HH24:Mi:SS') AND SYSDATE@!>=TO_DATE(NVL('01/01/2009 00:00:00',TO_CHAR(SYSDATE@!)),'DD/MM/YYYY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
              HH24:Mi:SS') AND NULL IS NOT NULL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  76 - access("PLLA"."SHIP_TO_LOCATION_ID"="HLA"."LOCATION_ID"(+))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
  78 - access("PLLA"."SHIP_TO_ORGANIZATION_ID"="HAOU"."ORGANIZATION_ID"(+))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
  81 - access("PLLA"."PO_HEADER_ID"="PHA"."PO_HEADER_ID")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  82 - access("PLLA"."PO_LINE_ID"="PLL"."PO_LINE_ID" AND "PLL"."SHIPMENT_TYPE"='PRICE BREAK') 

Re: Oracle audit view performance issue [message #438630 is a reply to message #436634] Mon, 11 January 2010 05:09 Go to previous messageGo to next message
sreejit
Messages: 3
Registered: December 2009
Junior Member
Can anybody please help me to solve the performance issue with my query

Regards,
SReejit
Re: Oracle audit view performance issue [message #439434 is a reply to message #436634] Sat, 16 January 2010 15:53 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Are there INSTEAD-OF-TRIGGERS on the view?

To debug the time a view takes, you will need to do some work to figure out where the cost is located.

Try buidling the view one part at a time. They you can see how long each step takes. You will eventually reach a step that is killing you. Then you can consider rewrites of the view.

As with all tuning problems, first make sure your statistics are uptodate both tables and indexes.

Good luck, Kevin
Previous Topic: Wait event problem
Next Topic: Query optimisation, table partitionning and lack of index
Goto Forum:
  


Current Time: Wed Dec 07 03:20:56 CST 2016

Total time taken to generate the page: 0.09785 seconds