WORKLOAD REPOSITORY SQL Report Snapshot Period Summary DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ O06SLD1 2922245920 O06SLD1A 1 10.2.0.4.0 YES svdg0019 Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 18979 13-Mar-12 22:00:05 63 15.1 End Snap: 18980 13-Mar-12 23:00:20 70 18.7 Elapsed: 60.25 (mins) DB Time: 54.44 (mins) SQL Summary DB/Inst: O06SLD1/O06SLD1A Snaps: 18979-18980 Elapsed SQL Id Time (ms) ------------- ---------- 08w3thdwdc6j9 886,877 Module: SQL*Plus SELECT :B1 ,ALD_RECORD_SQ.NEXTVAL AS REC_SQ, REC_TYPE_CD, LOAN_ID, LOAN_ALLOC_ID , OPEN_DT,'00000997' AS SENDING_FIRM_ID, CUSIP_ID, SEDOL_ID, ISIN_ID, QUICK_ID, LENDER_DTC_ID, LOCAL_TAX_CTRY_CD, LOCAL_TAX_ID, CLIENT_ENTITY_ID, ALD_ENTITY_ID, CTPY_SETTLE_CD, ALD_DLF_COLL_TYPE_ID, ASSET_ID, ASSET_ID_TYPE_CD, DISCLOSED_FLG ------------------------------------------------------------- SQL ID: 08w3thdwdc6j9 DB/Inst: O06SLD1/O06SLD1A Snaps: 18979-18980 -> 1st Capture and Last Capture Snap IDs refer to Snapshot IDs witin the snapshot range -> SELECT :B1 ,ALD_RECORD_SQ.NEXTVAL AS REC_SQ, REC_TYPE_CD, LOAN_ID, LOA... Plan Hash Total Elapsed 1st Capture Last Capture # Value Time(ms) Executions Snap ID Snap ID --- ---------------- ---------------- ------------- ------------- -------------- 1 3028225301 886,877 1 18980 18980 ------------------------------------------------------------- Plan 1(PHV: 3028225301) ----------------------- Plan Statistics DB/Inst: O06SLD1/O06SLD1A Snaps: 18979-18980 -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 Stat Name Statement Per Execution % Snap ---------------------------------------- ---------- -------------- ------- Elapsed Time (ms) 886,877 886,876.8 27.2 CPU Time (ms) 448,495 448,495.0 20.2 Executions 1 N/A N/A Buffer Gets 2,011,759 2,011,759.0 4.5 Disk Reads 13 13.0 0.0 Parse Calls 1 1.0 0.0 Rows 481,019 481,019.0 N/A User I/O Wait Time (ms) 114 N/A N/A Cluster Wait Time (ms) 60 N/A N/A Application Wait Time (ms) 0 N/A N/A Concurrency Wait Time (ms) 453,723 N/A N/A Invalidations 0 N/A N/A Version Count 1 N/A N/A Sharable Mem(KB) 102 N/A N/A ------------------------------------------------------------- Execution Plan -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 19414 (100)| | | 1 | SEQUENCE | ALD_RECORD_SQ | | | | | | | 2 | VIEW | | 40190 | 7614K| | 19414 (3)| 00:03:53 | | 3 | SORT ORDER BY | | 40190 | 7614K| 20M| 19414 (3)| 00:03:53 | | 4 | VIEW | | 40190 | 7614K| | 17705 (3)| 00:03:33 | | 5 | SORT UNIQUE | | 40190 | 5411K| 15M| 17705 (52)| 00:03:33 | | 6 | UNION-ALL | | | | | | | | 7 | TABLE ACCESS FULL | STAGE_DML_LOAN_EOD | 24857 | 3883K| | 7963 (3)| 00:01:36 | | 8 | SORT GROUP BY | | 15333 | 1527K| 5560K| 8862 (3)| 00:01:47 | | 9 | NESTED LOOPS | | 24857 | 2475K| | 7984 (4)| 00:01:36 | | 10 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | 11 | TABLE ACCESS FULL| STAGE_DML_LOAN_EOD | 24857 | 2475K| | 7982 (4)| 00:01:36 | -------------------------------------------------------------------------------------------------------- Full SQL Text SQL ID SQL Text ------------ ----------------------------------------------------------------- 08w3thdwdc6j SELECT :B1 , ALD_RECORD_SQ.NEXTVAL AS REC_SQ, REC_TYPE_CD, LOAN_I ID, LOAN_ALLOC_ID, OPEN_DT, '00000997' AS SENDING_FIRM_ID, CUSIP_ ID, SEDOL_ID, ISIN_ID, QUICK_ID, LENDER_DTC_ID, LOCAL_TAX_CTRY_CD , LOCAL_TAX_ID, CLIENT_ENTITY_ID, ALD_ENTITY_ID, CTPY_SETTLE_CD, ALD_DLF_COLL_TYPE_ID, ASSET_ID, ASSET_ID_TYPE_CD, DISCLOSED_FLG, SHARES_QTY, CONTRACT_AMT, COLL_CCY_ID, MARKET_VAL_AMT, MARKET_VAL UE_DT, SEC_SETTLE_STATUS_CD, COLL_SETTLE_STATUS_CD, REBATE_RT, LE NDER_DIVIDEND_RT, TERM_DT, ALLOCATION_CT, REALLOCATED_FLG, MARKED _FLG, RETURNED_FLG, MARK_AMT, BAD_REC_FLG, USER AS LAST_MOD_SIGNO N_ID, SYSDATE AS LAST_MOD_DATE_TIME FROM (SELECT REC_TYPE_CD, SHA RES_QTY, CONTRACT_AMT, ALD_ENTITY_ID, CUSIP_ID, SEDOL_ID, ISIN_ID , QUICK_ID, LENDER_DTC_ID, LOCAL_TAX_CTRY_CD, LOCAL_TAX_ID, CLIEN T_ENTITY_ID, LOAN_ID, OPEN_DT, CTPY_SETTLE_CD, LOAN_ALLOC_ID, ALD _DLF_COLL_TYPE_ID, ASSET_ID, ASSET_ID_TYPE_CD, DISCLOSED_FLG, COL L_CCY_ID, MARKET_VAL_AMT, MARKET_VALUE_DT, SEC_SETTLE_STATUS_CD, COLL_SETTLE_STATUS_CD, REBATE_RT, LENDER_DIVIDEND_RT, TERM_DT, AL LOCATION_CT, REALLOCATED_FLG, MARKED_FLG, RETURNED_FLG, MARK_AMT, BAD_REC_FLG, USER, SYSDATE FROM (SELECT '1' AS REC_TYPE_CD, LNC_ UNIT_ON_LOAN AS SHARES_QTY, DECODE(ALD_DLF_COLL_TYPE_ID, 'N', 0, DECODE(LNC_LOCAL_COLLAT_AMT, 0.0, 0.01, LNC_LOCAL_COLLAT_AMT)) AS CONTRACT_AMT, ALD_ENTITY_ID, LNC_CUSIP AS CUSIP_ID, SEDOL_ID, IS IN_ID, QUICK_ID, NULL AS LENDER_DTC_ID, NULL AS LOCAL_TAX_CTRY_CD , NULL AS LOCAL_TAX_ID, NULL AS CLIENT_ENTITY_ID, LNC_AREA_ID||LN C_SEQ_NO AS LOAN_ID, LNC_SETTLE_DATE AS OPEN_DT, DECODE (RESIDENC E_CTRY_CD, 'US', DECODE (LNC_SETTLEMENT_CODE, 'USD', DECODE(LNC_B USINESS_LINE, 'G', 'FEDW', SUBSTR(LNC_BORROWER_ID, 3, 4)), 'INTL' ), LNC_BORROWER_ID) AS CTPY_SETTLE_CD, NULL AS LOAN_ALLOC_ID, ALD _DLF_COLL_TYPE_ID, ASSET_ID, ASSET_ID_TYPE_CD, NULL AS DISCLOSED_ FLG, ISO_CCY_CD AS COLL_CCY_ID, NULL AS MARKET_VAL_AMT, NULL AS M ARKET_VALUE_DT, DECODE(LNC_SETTLED_FLAG, 'Y', 'S', 'P') AS SEC_SE TTLE_STATUS_CD, 'S' AS COLL_SETTLE_STATUS_CD, DECODE(NVL(LNC_REB_ PREM_RATE, 0), 0, ((-1)*LNC_COMMISSION_RATE), LNC_REB_PREM_RATE) AS REBATE_RT, NULL AS LENDER_DIVIDEND_RT, NULL AS TERM_DT, NULL A S ALLOCATION_CT, NULL AS REALLOCATED_FLG, NULL AS MARKED_FLG, NUL L AS RETURNED_FLG, NULL AS MARK_AMT, 'N' AS BAD_REC_FLG FROM STAG E_DML_LOAN_EOD SDLE1 WHERE SDLE1.LNC_ALLOC_FLAG = 2 AND SDLE1.BAD _REC_FLG = 'N' AND SDLE1.ALD_ENTITY_ID IS NOT NULL UNION SELECT O FD.REC_TYPE_CD, SUM(LNC_UNIT_ON_LOAN) AS SHARES_QTY, SUM(DECODE(S DLE.ALD_DLF_COLL_TYPE_ID, 'N', 0, DECODE(LNC_LOCAL_COLLAT_AMT, 0. 0, 0.01, LNC_LOCAL_COLLAT_AMT))) AS CONTRACT_AMT, ALD_ENTITY_ID, OFD.CUSIP_ID, OFD.SEDOL_ID, OFD.ISIN_ID, OFD.QUICK_ID, LENDER_DTC _ID, LOCAL_TAX_CTRY_CD, LOCAL_TAX_ID, CLIENT_ENTITY_ID, LNC_AREA_ ID||LNC_SEQ_NO AS LOAN_ID, OFD.OPEN_DT , OFD.CTPY_SETTLE_CD, OFD. LOAN_ALLOC_ID, OFD.ALD_DLF_COLL_TYPE_ID, OFD.ASSET_ID, OFD.ASSET_ ID_TYPE_CD, SUBSTR(MAX(DECODE(LNC_EXCLUSIVE_FLAG, 'Y', 'E', 'N')) , 1, 1) AS DISCLOSED_FLG, OFD.COLL_CCY_ID, OFD.MARKET_VAL_AMT, OF D.MARKET_VALUE_DT, OFD.SEC_SETTLE_STATUS_CD, OFD.COLL_SETTLE_STAT US_CD, OFD.REBATE_RT, OFD.LENDER_DIVIDEND_RT, OFD.TERM_DT, OFD.AL LOCATION_CT, SUBSTR(MAX(LNC_REALLOC_FLAG), 1, 1) AS REALLOCATED_F LG, DECODE(SIGN(SUM(LNC_MARK_AMT)), 0, 'N', 'Y') AS MARKED_FLG, S UBSTR(MAX(LNC_RETURN_FLAG), 1, 1) AS RETURNED_FLG, SUM(LNC_MARK_A MT), SDLE.BAD_REC_FLG FROM STAGE_DML_LOAN_EOD SDLE, (SELECT '2' A S REC_TYPE_CD, NULL AS OPEN_DT , NULL AS CTPY_SETTLE_CD, NULL AS ALLOCATION_CT, NULL AS CUSIP_ID, NULL AS SEDOL_ID, NULL AS ISIN_I D, NULL AS QUICK_ID, NULL AS LOAN_ALLOC_ID, NULL AS ALD_DLF_COLL_ TYPE_ID, NULL AS ASSET_ID, NULL AS ASSET_ID_TYPE_CD, NULL AS DISC LOSED_FLG, NULL AS MARKET_VAL_AMT, NULL AS COLL_CCY_ID, NULL AS M ARKET_VALUE_DT, NULL AS SEC_SETTLE_STATUS_CD, NULL AS COLL_SETTLE _STATUS_CD, NULL AS REBATE_RT, NULL AS LENDER_DIVIDEND_RT, NULL A S TERM_DT FROM DUAL) OFD WHERE SDLE.LNC_ALLOC_FLAG = 3 AND SDLE.B AD_REC_FLG = 'N' AND SDLE.ALD_ENTITY_ID IS NOT NULL GROUP BY OFD. REC_TYPE_CD, ALD_ENTITY_ID, LENDER_DTC_ID, LOCAL_TAX_CTRY_CD, LOC AL_TAX_ID, CLIENT_ENTITY_ID, (LNC_AREA_ID||LNC_SEQ_NO), OFD.OPEN_ DT, OFD.CTPY_SETTLE_CD, OFD.LOAN_ALLOC_ID, OFD.ALD_DLF_COLL_TYPE_ ID, OFD.ASSET_ID, OFD.ASSET_ID_TYPE_CD, OFD.CUSIP_ID, OFD.SEDOL_I D, OFD.ISIN_ID, OFD.QUICK_ID, ISO_CCY_CD, OFD.MARKET_VAL_AMT, OFD .MARKET_VALUE_DT, OFD.SEC_SETTLE_STATUS_CD, OFD.COLL_SETTLE_STATU S_CD, REBATE_RT, OFD.LENDER_DIVIDEND_RT, OFD.TERM_DT, BAD_REC_FLG ) AGGREGATED_DATA ORDER BY ALD_ENTITY_ID, LOAN_ID, REC_TYPE_CD )