DETAILED ADDM REPORT FOR TASK 'TASK_271872' WITH ID 271872 ---------------------------------------------------------- Analysis Period: 31-MAR-2011 from 11:00:38 to 13:00:09 Database ID/Instance: 4051413816/1 Database/Instance Names: UDAS2PDB/udas2p1 Host Name: fipd329 Database Version: 10.2.0.4.0 Snapshot Range: from 20619 to 20621 Database Time: 15481 seconds Average Database Load: 2.2 active sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FINDING 1: 27% impact (4255 seconds) ------------------------------------ SQL statements consuming significant database time were found. RECOMMENDATION 1: SQL Tuning, 7.6% benefit (1178 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "b5dgb0j12ndtq". RELEVANT OBJECT: SQL statement with SQL_ID b5dgb0j12ndtq INSERT INTO STG_IPTV_CHMAP_SVC_SVCL(VHO,SERVICECOLLECIONID,SERVICECOL LECTIONNAME,EPGID,CHANNELMAPSID,CHANNELMAPSNAME,SERVICEID,NAME,MAPID) VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9) ACTION: Investigate the SQL statement with SQL_ID "b5dgb0j12ndtq" for possible performance improvements. RELEVANT OBJECT: SQL statement with SQL_ID b5dgb0j12ndtq INSERT INTO STG_IPTV_CHMAP_SVC_SVCL(VHO,SERVICECOLLECIONID,SERVICECOL LECTIONNAME,EPGID,CHANNELMAPSID,CHANNELMAPSNAME,SERVICEID,NAME,MAPID) VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9) RATIONALE: SQL statement with SQL_ID "b5dgb0j12ndtq" was executed 20672 times and had an average elapsed time of 0.046 seconds. RATIONALE: Waiting for event "db file sequential read" in wait class "User I/O" accounted for 18% of the database time spent in processing the SQL statement with SQL_ID "b5dgb0j12ndtq". RECOMMENDATION 2: SQL Tuning, 6.3% benefit (969 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "0sa5cmb8p9qsx". RELEVANT OBJECT: SQL statement with SQL_ID 0sa5cmb8p9qsx and PLAN_HASH 3894422301 select B.VHO_CLLI_CODE||','||B.ASSET_BILLING_EVENT_ID||','||A.BE_STAT E_ID||','||A.RESOURCE_TYPE||','||A.REJECT_REASON||','||A.RESOURCE_NAM E||','||A.PURCHASE_AMOUNT||','||A.CURRENCY||','||B.RESOURCE_TYPE||',' ||B.BE_STATE_ID||','||to_char(PE_LAST_UPDATE_TS,'yyyy-mm-dd hh24:mi:ssxff')||','||to_char(PE_INSERT_TS,'yyyy-mm-dd hh24:mi:ssxff')||','||VERSION_NUM||','||DISCOUNT_AMOUNT from BF_ALRT_PRCH_HSTRY A, BF_AUDIT_TRAIL B where B.purchase_dt > :z and B.PE_INSERT_TS <= :y and B.PE_INSERT_TS >= :x and A.ASSET_BILLING_EVENT_ID(+) = B.ASSET_BILLING_EVENT_ID and A.VHO_CLLI_CODE(+)= B.VHO_CLLI_CODE RATIONALE: SQL statement with SQL_ID "0sa5cmb8p9qsx" was executed 1 times and had an average elapsed time of 987 seconds. RECOMMENDATION 3: SQL Tuning, 5.5% benefit (851 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "35ku6489jtxpa". RELEVANT OBJECT: SQL statement with SQL_ID 35ku6489jtxpa and PLAN_HASH 2927654601 SELECT BF_MART_PRCH_HSTRY.PRINCIPAL_EXTERNAL_ID, BF_MART_PRCH_HSTRY.ASSET_BILLING_EVENT_ID, BF_MART_PRCH_HSTRY.ASSET_DEPLOYMENT_ID, BF_MART_PRCH_HSTRY.PURCHASE_DT, BF_MART_PRCH_HSTRY.PPV_ASSET_START_DT, BF_MART_PRCH_HSTRY.PPV_ASSET_END_DT, BF_MART_PRCH_HSTRY.RESOURCE_NAME, BF_MART_PRCH_HSTRY.PROVIDER_ASSET_ID, BF_MART_PRCH_HSTRY.PURCHASE_AMOUNT, BF_MART_PRCH_HSTRY.GENRE, BF_MART_PRCH_HSTRY.RESOURCE_TYPE, BF_MART_PRCH_HSTRY.ADULT_CONTENT_IND, BF_MART_PRCH_HSTRY.DISCOUNT_AMOUNT, BF_MART_PRCH_HSTRY.BILLED_AMOUNT, BF_MART_PRCH_HSTRY.VERSION_NUM, BF_MART_PRCH_HSTRY.OVERLAY_ASSET_TITLE FROM BF_MART_PRCH_HSTRY WHERE BF_MART_PRCH_HSTRY.BE_STATE_ID=9 AND BF_MART_PRCH_HSTRY.RESOURCE_TYPE='PPV' AND BF_MART_PRCH_HSTRY.UAR_SRC_STATUS='N' AND BF_MART_PRCH_HSTRY.CSF_SRC_STATUS=( CASE WHEN BF_MART_PRCH_HSTRY.BILLED_AMOUNT=0 THEN 0 ELSE 1 END) RATIONALE: SQL statement with SQL_ID "35ku6489jtxpa" was executed 11 times and had an average elapsed time of 77 seconds. RECOMMENDATION 4: SQL Tuning, 4.8% benefit (740 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "15zytu14qzw6p". RELEVANT OBJECT: SQL statement with SQL_ID 15zytu14qzw6p insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,userhost,terminal,action#,returncode, obj$creator,obj$name,auth$privileges,auth$grantee, new$owner,new$name,ses$actions,ses$tid,logoff$pread, logoff$lwrite,logoff$dead,comment$text,spare1,spare2, priv$used,clientid,sessioncpu,proxy$sid,user$guid, instance#,process#,xid,scn,auditid,sqlbind,sqltext) values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP), :4,:5,:6,:7,:8, :9,:10,:11,:12, :13,:14,:15,:16,:17, :18,:19,:20,:21,:22, :23,:24,:25,:26,:27, :28,:29,:30,:31,:32,:33,:34) RATIONALE: SQL statement with SQL_ID "15zytu14qzw6p" was executed 11010 times and had an average elapsed time of 0.067 seconds. RECOMMENDATION 5: SQL Tuning, 4% benefit (614 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "cdk073dvj8vua". RELEVANT OBJECT: SQL statement with SQL_ID cdk073dvj8vua and PLAN_HASH 839400591 SELECT 1 FROM DUAL WHERE EXISTS (SELECT * FROM SC_SVC_CHANGE WHERE SVCH_MOD_REC_SK_VAL = :B3 AND SVCH_MOD_REC_COL_SK_NM = :B2 AND SVCH_STATUS = 'Pending' AND SVCH_MOD_TABLE_NM = :B1 ) RATIONALE: SQL statement with SQL_ID "cdk073dvj8vua" was executed 1561 times and had an average elapsed time of 0.46 seconds. FINDING 2: 21% impact (3304 seconds) ------------------------------------ Waits on event "log file sync" while performing COMMIT and ROLLBACK operations were consuming significant database time. RECOMMENDATION 1: Host Configuration, 21% benefit (3304 seconds) ACTION: Investigate the possibility of improving the performance of I/O to the online redo log files. RATIONALE: The average size of writes to the online redo log files was 80 K and the average time per write was 1 milliseconds. ADDITIONAL INFORMATION: Waits on event "log file sync" were the cause of significant database wait on "gc buffer busy" when releasing a data block. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Commit" was consuming significant database time. (19% impact [2945 seconds]) FINDING 3: 20% impact (3098 seconds) ------------------------------------ Wait class "Other" was consuming significant database time. NO RECOMMENDATIONS AVAILABLE ADDITIONAL INFORMATION: Database latches in the "Other" wait class were not consuming significant database time. FINDING 4: 17% impact (2676 seconds) ------------------------------------ Read and write contention on database blocks was consuming significant database time. NO RECOMMENDATIONS AVAILABLE SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Inter-instance messaging was consuming significant database time on this instance. (22% impact [3347 seconds]) SYMPTOM: Wait class "Cluster" was consuming significant database time. (24% impact [3717 seconds]) FINDING 5: 16% impact (2447 seconds) ------------------------------------ SQL statements responsible for significant inter-instance messaging were found RECOMMENDATION 1: SQL Tuning, 5.5% benefit (851 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "35ku6489jtxpa". RELEVANT OBJECT: SQL statement with SQL_ID 35ku6489jtxpa and PLAN_HASH 2927654601 SELECT BF_MART_PRCH_HSTRY.PRINCIPAL_EXTERNAL_ID, BF_MART_PRCH_HSTRY.ASSET_BILLING_EVENT_ID, BF_MART_PRCH_HSTRY.ASSET_DEPLOYMENT_ID, BF_MART_PRCH_HSTRY.PURCHASE_DT, BF_MART_PRCH_HSTRY.PPV_ASSET_START_DT, BF_MART_PRCH_HSTRY.PPV_ASSET_END_DT, BF_MART_PRCH_HSTRY.RESOURCE_NAME, BF_MART_PRCH_HSTRY.PROVIDER_ASSET_ID, BF_MART_PRCH_HSTRY.PURCHASE_AMOUNT, BF_MART_PRCH_HSTRY.GENRE, BF_MART_PRCH_HSTRY.RESOURCE_TYPE, BF_MART_PRCH_HSTRY.ADULT_CONTENT_IND, BF_MART_PRCH_HSTRY.DISCOUNT_AMOUNT, BF_MART_PRCH_HSTRY.BILLED_AMOUNT, BF_MART_PRCH_HSTRY.VERSION_NUM, BF_MART_PRCH_HSTRY.OVERLAY_ASSET_TITLE FROM BF_MART_PRCH_HSTRY WHERE BF_MART_PRCH_HSTRY.BE_STATE_ID=9 AND BF_MART_PRCH_HSTRY.RESOURCE_TYPE='PPV' AND BF_MART_PRCH_HSTRY.UAR_SRC_STATUS='N' AND BF_MART_PRCH_HSTRY.CSF_SRC_STATUS=( CASE WHEN BF_MART_PRCH_HSTRY.BILLED_AMOUNT=0 THEN 0 ELSE 1 END) RATIONALE: SQL statement with SQL_ID "35ku6489jtxpa" was executed 11 times and had an average elapsed time of 77 seconds. RATIONALE: Average time spent in Cluster wait events per execution was 19 seconds. RECOMMENDATION 2: SQL Tuning, 4.8% benefit (740 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "15zytu14qzw6p". RELEVANT OBJECT: SQL statement with SQL_ID 15zytu14qzw6p insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,userhost,terminal,action#,returncode, obj$creator,obj$name,auth$privileges,auth$grantee, new$owner,new$name,ses$actions,ses$tid,logoff$pread, logoff$lwrite,logoff$dead,comment$text,spare1,spare2, priv$used,clientid,sessioncpu,proxy$sid,user$guid, instance#,process#,xid,scn,auditid,sqlbind,sqltext) values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP), :4,:5,:6,:7,:8, :9,:10,:11,:12, :13,:14,:15,:16,:17, :18,:19,:20,:21,:22, :23,:24,:25,:26,:27, :28,:29,:30,:31,:32,:33,:34) RATIONALE: SQL statement with SQL_ID "15zytu14qzw6p" was executed 11010 times and had an average elapsed time of 0.067 seconds. RATIONALE: Average time spent in Cluster wait events per execution was 0.063 seconds. RECOMMENDATION 3: SQL Tuning, 4.4% benefit (680 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "5pdgmjpnacak4". RELEVANT OBJECT: SQL statement with SQL_ID 5pdgmjpnacak4 INSERT INTO CODIE_TRANSACTION_LOG (CODIE_CLIENT_APP, CODIE_TRANS_STATUS, CODIE_CLIENT_REQUEST_TIME, CODIE_TRANS_DESCRIPTION, CODIE_METHOD_ID, CODIE_START_TIME, CODIE_END_TIME, SRC_IP_ADDR, SRC_MESSAGE_ID, METHOD_VERSION, FAILURE_SYSTEM, RESPONSE_CODE, REQUEST_PARAMETER, RESPONSE_DURATION,CODIE_TRANS_ID,HOST_NAME) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15,:16) RATIONALE: SQL statement with SQL_ID "5pdgmjpnacak4" was executed 8539 times and had an average elapsed time of 0.079 seconds. RATIONALE: Average time spent in Cluster wait events per execution was 0.07 seconds. RECOMMENDATION 4: SQL Tuning, 4.3% benefit (657 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "1rd8um1taq1fn". RELEVANT OBJECT: SQL statement with SQL_ID 1rd8um1taq1fn and PLAN_HASH 279351612 update sys.aud$ set action#=:2, returncode=:3, logoff$time=cast(systimestamp as date), logoff$pread=:4, logoff$lread=:5, logoff$lwrite=:6, logoff$dead=:7, sessioncpu=:8 where sessionid=:1 and entryid=1 and action#=100 RATIONALE: SQL statement with SQL_ID "1rd8um1taq1fn" was executed 9210 times and had an average elapsed time of 0.071 seconds. RATIONALE: Average time spent in Cluster wait events per execution was 0.069 seconds. RECOMMENDATION 5: SQL Tuning, 2.1% benefit (330 seconds) ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "fvk5x826hw6gg". RELEVANT OBJECT: SQL statement with SQL_ID fvk5x826hw6gg INSERT INTO BF_STG_PRCH_HSTRY(PRINCIPAL_EXTERNAL_ID,ASSET_BILLING_EVE NT_ID,ASSET_DEPLOYMENT_ID,VHO_CLLI_CODE,PURCHASE_DT,PPV_ASSET_START_D T,PPV_ASSET_END_DT,SRC_VAL_STATUS,SRC_VAL_FAILURE_COUNT,IPTV_UPD_STAT US,IPTV_UPD_FAILURE_COUNT,BE_STATE_ID,LAST_UPDATE_TS,WORKFLOW_RUN_ID, PRICE) VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15) RATIONALE: SQL statement with SQL_ID "fvk5x826hw6gg" was executed 301 times and had an average elapsed time of 1.1 seconds. RATIONALE: Average time spent in Cluster wait events per execution was 0.97 seconds. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Cluster" was consuming significant database time. (24% impact [3717 seconds]) FINDING 6: 15% impact (2323 seconds) ------------------------------------ Individual database segments responsible for significant user I/O wait were found. RECOMMENDATION 1: Segment Tuning, 7.7% benefit (1185 seconds) ACTION: Run "Segment Advisor" on TABLE "UDAS_CODIE.BF_MART_PRCH_HSTRY" with object id 735703. RELEVANT OBJECT: database object with id 735703 ACTION: Investigate application logic involving I/O on TABLE "UDAS_CODIE.BF_MART_PRCH_HSTRY" with object id 735703. RELEVANT OBJECT: database object with id 735703 RATIONALE: The I/O usage statistics for the object are: 26 full object scans, 8382547 physical reads, 1013 physical writes and 0 direct reads. RATIONALE: The SQL statement with SQL_ID "35ku6489jtxpa" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 35ku6489jtxpa SELECT BF_MART_PRCH_HSTRY.PRINCIPAL_EXTERNAL_ID, BF_MART_PRCH_HSTRY.ASSET_BILLING_EVENT_ID, BF_MART_PRCH_HSTRY.ASSET_DEPLOYMENT_ID, BF_MART_PRCH_HSTRY.PURCHASE_DT, BF_MART_PRCH_HSTRY.PPV_ASSET_START_DT, BF_MART_PRCH_HSTRY.PPV_ASSET_END_DT, BF_MART_PRCH_HSTRY.RESOURCE_NAME, BF_MART_PRCH_HSTRY.PROVIDER_ASSET_ID, BF_MART_PRCH_HSTRY.PURCHASE_AMOUNT, BF_MART_PRCH_HSTRY.GENRE, BF_MART_PRCH_HSTRY.RESOURCE_TYPE, BF_MART_PRCH_HSTRY.ADULT_CONTENT_IND, BF_MART_PRCH_HSTRY.DISCOUNT_AMOUNT, BF_MART_PRCH_HSTRY.BILLED_AMOUNT, BF_MART_PRCH_HSTRY.VERSION_NUM, BF_MART_PRCH_HSTRY.OVERLAY_ASSET_TITLE FROM BF_MART_PRCH_HSTRY WHERE BF_MART_PRCH_HSTRY.BE_STATE_ID=9 AND BF_MART_PRCH_HSTRY.RESOURCE_TYPE='PPV' AND BF_MART_PRCH_HSTRY.UAR_SRC_STATUS='N' AND BF_MART_PRCH_HSTRY.CSF_SRC_STATUS=( CASE WHEN BF_MART_PRCH_HSTRY.BILLED_AMOUNT=0 THEN 0 ELSE 1 END) RATIONALE: The SQL statement with SQL_ID "g527v48drgf0j" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID g527v48drgf0j SELECT BF_MART_PRCH_HSTRY.PRINCIPAL_EXTERNAL_ID, BF_MART_PRCH_HSTRY.ASSET_BILLING_EVENT_ID, BF_MART_PRCH_HSTRY.ASSET_DEPLOYMENT_ID, BF_MART_PRCH_HSTRY.PURCHASE_DT, BF_MART_PRCH_HSTRY.PPV_ASSET_START_DT, BF_MART_PRCH_HSTRY.PPV_ASSET_END_DT, BF_MART_PRCH_HSTRY.RESOURCE_NAME, BF_MART_PRCH_HSTRY.PROVIDER_ASSET_ID, BF_MART_PRCH_HSTRY.PURCHASE_AMOUNT, BF_MART_PRCH_HSTRY.GENRE, BF_MART_PRCH_HSTRY.RESOURCE_TYPE, BF_MART_PRCH_HSTRY.ADULT_CONTENT_IND, BF_MART_PRCH_HSTRY.DISCOUNT_AMOUNT, BF_MART_PRCH_HSTRY.BILLED_AMOUNT, BF_MART_PRCH_HSTRY.VERSION_NUM, BF_MART_PRCH_HSTRY.OVERLAY_ASSET_TITLE, DISCOUNT_COUPON.COUPON_CD, DISCOUNT_COUPON.COUPON_DESC, DISCOUNT_COUPON.COUPON_EXTERNAL_ID, DISCOUNT_COUPON.COUPON_NM, DISCOUNT_COUPON.COUPON_TYPE, DISCOUNT_COUPON.COUPON_VALUE, ASSET_COUPON_ASSOC.COUPON_APPLIED_DT FROM BF_MART_PRCH_HSTRY LEFT OUTER JOIN ASSET_COUPON_ASSOC ON BF_MART_PRCH_HSTRY.PRINCIPAL_EXTERNAL_ID=ASSET_COUPON_ASSOC.PRINCIPAL _EXTERNAL_ID AND BF_MART_PRCH_HSTRY.ASSET_BILLING_EVENT_ID=ASSET_COUPON_ASSOC.ASSET_BI LLING_EVENT_ID AND BF_MART_PRCH_HSTRY.ASSET_DEPLOYMENT_ID=ASSET_COUPON_ASSOC.ASSET_DEPLO YMENT_ID LEFT OUTER JOIN DISCOUNT_COUPON ON ASSET_COUPON_ASSOC.SK_COUPON_ID=DISCOUNT_COUPON.SK_COUPON_ID WHERE BF_MART_PRCH_HSTRY.BE_STATE_ID=9 AND BF_MART_PRCH_HSTRY.RESOURCE_TYPE='VOD' AND BF_MART_PRCH_HSTRY.UAR_SRC_STATUS='N'AND BF_MART_PRCH_HSTRY.CSF_SRC_STATUS=( CASE WHEN BF_MART_PRCH_HSTRY.BILLED_AMOUNT=0 THEN 0 ELSE 1 END) RATIONALE: The SQL statement with SQL_ID "1kzvabr5b36aj" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 1kzvabr5b36aj UPDATE BF_MART_PRCH_HSTRY SET BE_STATE_ID=9,VERSION_NUM=VERSION_NUM+1, LAST_UPDATE_TS=SYSDATE, WORKFLOW_RUN_ID='667347' WHERE BILLING_FILE_ID=(SELECT SK_ID FROM BF_FWD_FILE_CTRL WHERE trim(BE_FILE_NAME)='UDAS_2_FWD_IPTV_SEQ056249_20110331114419.txt') RATIONALE: The SQL statement with SQL_ID "bk8r7u00nb9an" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID bk8r7u00nb9an UPDATE BF_MART_PRCH_HSTRY SET BE_STATE_ID=9,VERSION_NUM=VERSION_NUM+1, LAST_UPDATE_TS=SYSDATE, WORKFLOW_RUN_ID='667189' WHERE BILLING_FILE_ID=(SELECT SK_ID FROM BF_FWD_FILE_CTRL WHERE trim(BE_FILE_NAME)='UDAS_2_FWD_IPTV_SEQ056242_20110331111911.txt') RATIONALE: The SQL statement with SQL_ID "cd9fra0b9w9v4" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID cd9fra0b9w9v4 UPDATE BF_MART_PRCH_HSTRY SET BE_STATE_ID=9,VERSION_NUM=VERSION_NUM+1, LAST_UPDATE_TS=SYSDATE, WORKFLOW_RUN_ID='667067' WHERE BILLING_FILE_ID=(SELECT SK_ID FROM BF_FWD_FILE_CTRL WHERE trim(BE_FILE_NAME)='UDAS_2_FWD_IPTV_SEQ056237_20110331105923.txt') RECOMMENDATION 2: Segment Tuning, 4% benefit (612 seconds) ACTION: Run "Segment Advisor" on TABLE PARTITION "UDAS_CODIE.BF_AUDIT_TRAIL.P2011MAR" with object id 1332689. RELEVANT OBJECT: database object with id 1332689 ACTION: Investigate application logic involving I/O on TABLE PARTITION "UDAS_CODIE.BF_AUDIT_TRAIL.P2011MAR" with object id 1332689. RELEVANT OBJECT: database object with id 1332689 RATIONALE: The I/O usage statistics for the object are: 1 full object scans, 2595099 physical reads, 1793 physical writes and 0 direct reads. RATIONALE: The SQL statement with SQL_ID "0sa5cmb8p9qsx" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 0sa5cmb8p9qsx select B.VHO_CLLI_CODE||','||B.ASSET_BILLING_EVENT_ID||','||A.BE_STAT E_ID||','||A.RESOURCE_TYPE||','||A.REJECT_REASON||','||A.RESOURCE_NAM E||','||A.PURCHASE_AMOUNT||','||A.CURRENCY||','||B.RESOURCE_TYPE||',' ||B.BE_STATE_ID||','||to_char(PE_LAST_UPDATE_TS,'yyyy-mm-dd hh24:mi:ssxff')||','||to_char(PE_INSERT_TS,'yyyy-mm-dd hh24:mi:ssxff')||','||VERSION_NUM||','||DISCOUNT_AMOUNT from BF_ALRT_PRCH_HSTRY A, BF_AUDIT_TRAIL B where B.purchase_dt > :z and B.PE_INSERT_TS <= :y and B.PE_INSERT_TS >= :x and A.ASSET_BILLING_EVENT_ID(+) = B.ASSET_BILLING_EVENT_ID and A.VHO_CLLI_CODE(+)= B.VHO_CLLI_CODE RECOMMENDATION 3: Segment Tuning, 3.4% benefit (526 seconds) ACTION: Run "Segment Advisor" on TABLE "UDAS_CODIE.PCT_VSDA_USD_LOAD" with object id 462695. RELEVANT OBJECT: database object with id 462695 ACTION: Investigate application logic involving I/O on TABLE "UDAS_CODIE.PCT_VSDA_USD_LOAD" with object id 462695. RELEVANT OBJECT: database object with id 462695 RATIONALE: The I/O usage statistics for the object are: 3 full object scans, 1386885 physical reads, 8 physical writes and 0 direct reads. RATIONALE: The SQL statement with SQL_ID "gyf7wmznp1x8p" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID gyf7wmznp1x8p SELECT DISTINCT PCT_VSDA_USD_LOAD.PROC_ID FROM PCT_VSDA_USD_LOAD WHERE PCT_VSDA_USD_LOAD.PROC_ID=16897001.000000000000000 AND PCT_VSDA_USD_LOAD.MODULE_NM='OMCM' RATIONALE: The SQL statement with SQL_ID "1s0m6vd2ayn5m" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 1s0m6vd2ayn5m SELECT DISTINCT PCT_VSDA_USD_LOAD.PROC_ID FROM PCT_VSDA_USD_LOAD WHERE PCT_VSDA_USD_LOAD.PROC_ID=16898001.000000000000000 AND PCT_VSDA_USD_LOAD.MODULE_NM='INC' RATIONALE: The SQL statement with SQL_ID "26b87mkmvjkkn" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID 26b87mkmvjkkn UPDATE PCT_VSDA_USD_LOAD SET LOAD_STATUS=:1, LOAD_END_TS=:2 WHERE PROC_ID=:3 AND MODULE_NM='INC' RATIONALE: The SQL statement with SQL_ID "g15ps9u9ws3a9" spent significant time waiting for User I/O on the hot object. RELEVANT OBJECT: SQL statement with SQL_ID g15ps9u9ws3a9 SELECT DISTINCT PCT_VSDA_USD_LOAD.PROC_ID FROM PCT_VSDA_USD_LOAD WHERE PCT_VSDA_USD_LOAD.PROC_ID=16896001.000000000000000 AND PCT_VSDA_USD_LOAD.MODULE_NM='INC' SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "User I/O" was consuming significant database time. (21% impact [3327 seconds]) FINDING 7: 14% impact (2200 seconds) ------------------------------------ Session connect and disconnect calls were consuming significant database time. RECOMMENDATION 1: Application Analysis, 14% benefit (2200 seconds) ACTION: Investigate application logic for possible reduction of connect and disconnect calls. For example, you might use a connection pool scheme in the middle tier. FINDING 8: 13% impact (2063 seconds) ------------------------------------ Wait event "LGWR wait on LNS" in wait class "Network" was consuming significant database time. RECOMMENDATION 1: Application Analysis, 13% benefit (2063 seconds) ACTION: Investigate the cause for high "LGWR wait on LNS" waits. Refer to Oracle's "Database Reference" for the description of this wait event. SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Network" was consuming significant database time. (27% impact [4136 seconds]) FINDING 9: 13% impact (2060 seconds) ------------------------------------ Wait event "LGWR-LNS wait on channel" in wait class "Other" was consuming significant database time. RECOMMENDATION 1: Application Analysis, 13% benefit (2060 seconds) ACTION: Investigate the cause for high "LGWR-LNS wait on channel" waits. Refer to Oracle's "Database Reference" for the description of this wait event. RECOMMENDATION 2: Application Analysis, 13% benefit (2060 seconds) ACTION: Investigate the cause for high "LGWR-LNS wait on channel" waits in Service "SYS$BACKGROUND". SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Other" was consuming significant database time. (20% impact [3098 seconds]) INFO: Database latches in the "Other" wait class were not consuming significant database time. FINDING 10: 13% impact (2054 seconds) ------------------------------------- Wait event "LNS wait on SENDREQ" in wait class "Network" was consuming significant database time. RECOMMENDATION 1: Application Analysis, 13% benefit (2054 seconds) ACTION: Investigate the cause for high "LNS wait on SENDREQ" waits. Refer to Oracle's "Database Reference" for the description of this wait event. RECOMMENDATION 2: Application Analysis, 13% benefit (2054 seconds) ACTION: Investigate the cause for high "LNS wait on SENDREQ" waits in Service "SYS$BACKGROUND". SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Network" was consuming significant database time. (27% impact [4136 seconds]) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ADDITIONAL INFORMATION ---------------------- Wait class "Application" was not consuming significant database time. Wait class "Concurrency" was not consuming significant database time. Wait class "Configuration" was not consuming significant database time. The analysis of I/O performance is based on the default assumption that the average read time for one database block is 10000 micro-seconds. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ TERMINOLOGY ----------- DATABASE TIME: This is the ADDM's measurement of throughput. From the user's point of view: this is the total amount of time spent by users waiting for a response from the database after issuing a call (not including networking). From the database instance point of view: this is the total time spent by forground processes waiting for a database resource (e.g., read I/O), running on the CPU and waiting for a free CPU (run-queue). The target of ADDM analysis is to reduce this metric as much as possible, thereby reducing the instance's response time. AVERAGE DATABASE LOAD: At any given time we can count how many users (also called 'Active Sessions') are waiting for an answer from the instance. This is the ADDM's measurement for instance load. The 'Average Database Load' is the average of the the load measurement taken over the entire analysis period. We get this number by dividing the 'Database Time' by the analysis period. For example, if the analysis period is 30 minutes and the 'Database Time' is 90 minutes, we have an average of 3 users waiting for a response. IMPACT: Each finding has an 'Impact' associated with it. The impact is the portion of the 'Database Time' the finding deals with. If we assume that the problem described by the finding is completely solved, then the 'Database Time' will be reduced by the amount of the 'Impact'. BENEFIT: Each recommendation has a 'benefit' associated with it. The ADDM analysis estimates that the 'Database Time' can be reduced by the 'benefit' amount if all the actions of the recommendation are performed.