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  |
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 #437683 is a reply to message #436642] |
Tue, 05 January 2010 08:50  |
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 #439434 is a reply to message #436634] |
Sat, 16 January 2010 15:53 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
Goto Forum:
Current Time: Sun May 12 23:45:54 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
