Query which is running slow --------------------------- INSERT INTO wk_acct_wstore (acct_tdlinx, related_tdlinx, acct_type_cd, lvl, mkt_grp_tdlinx, trade_channel_cd, sub_channel_cd, mkt_grp_count, trade_channel_count, sub_channel_count) SELECT acct_tdlinx, related_tdlinx, acct_type_cd, lvl, mkt_grp_tdlinx, trade_channel_cd, sub_channel_cd, mkt_grp_count, trade_channel_count, sub_channel_count FROM (SELECT connect_by_root acct_tdlinx_cd AS acct_tdlinx, irt_tdlinx_cd AS related_tdlinx, connect_by_isleaf isleaf, ( LEVEL ) AS lvl FROM dlvry_acct_dim mad WHERE mad.orig_status_cd IN ( 'FO', 'OP' ) AND mad.end_dt = '31 dec 2500' START WITH mad.orig_status_cd IN ( 'FO', 'OP' ) AND mad.end_dt = '31 dec 2500' AND mad.acct_tdlinx_cd <> mad.irt_tdlinx_cd CONNECT BY nocycle PRIOR mad.irt_tdlinx_cd = mad.acct_tdlinx_cd UNION ALL SELECT acct_tdlinx_cd, acct_tdlinx_cd, 0, 0 FROM dlvry_acct_dim mad WHERE mad.orig_status_cd IN ( 'FO', 'OP' ) AND mad.end_dt = '31 dec 2500' UNION ALL SELECT connect_by_root irt_tdlinx_cd, acct_tdlinx_cd, connect_by_isleaf isleaf, ( 0 - LEVEL ) AS lvl FROM dlvry_acct_dim mad WHERE mad.orig_status_cd IN ( 'FO', 'OP' ) AND mad.end_dt = '31 dec 2500' START WITH mad.orig_status_cd IN ( 'FO', 'OP' ) AND mad.end_dt = '31 dec 2500' AND mad.acct_tdlinx_cd <> mad.irt_tdlinx_cd CONNECT BY nocycle PRIOR mad.acct_tdlinx_cd = mad.irt_tdlinx_cd) hier, (SELECT DISTINCT acct_tdlinx_cd, acct_type_cd, mkt_grp_tdlinx, trade_channel_cd, sub_channel_cd, COUNT(store_tdlinx_cd) over (PARTITION BY acct_tdlinx_cd , mkt_grp_tdlinx) mkt_grp_count, COUNT(store_tdlinx_cd) over (PARTITION BY acct_tdlinx_cd , trade_channel_cd) trade_channel_count, COUNT(store_tdlinx_cd) over (PARTITION BY acct_tdlinx_cd , trade_channel_cd, sub_channel_cd) sub_channel_count FROM (SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd AS mkt_grp_tdlinx, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd IN ( 'B', 'O' ) AND mad.acct_tdlinx_cd = msd.owner_tdlinx_cd AND mad.acct_trade_channel_cd = msd.trade_channel_cd UNION SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd = 'S' AND mad.acct_tdlinx_cd = msd.pri_sup_tdlinx_cd AND ( mad.acct_trade_channel_cd = msd.trade_channel_cd OR mad.acct_trade_channel_cd = '12' ) UNION SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd = 'S' AND mad.acct_tdlinx_cd = grocery_acct_tdlinx_cd AND ( mad.acct_trade_channel_cd = msd.trade_channel_cd OR mad.acct_trade_channel_cd = '12' ) UNION SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd = 'S' AND mad.acct_tdlinx_cd = hbc_acct_tdlinx_cd AND ( mad.acct_trade_channel_cd = msd.trade_channel_cd OR mad.acct_trade_channel_cd = '12' ) UNION SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd = 'S' AND mad.acct_tdlinx_cd = gm_acct_tdlinx_cd AND ( mad.acct_trade_channel_cd = msd.trade_channel_cd OR mad.acct_trade_channel_cd = '12' ) UNION SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd = 'S' AND mad.acct_tdlinx_cd = fr_acct_tdlinx_cd AND ( mad.acct_trade_channel_cd = msd.trade_channel_cd OR mad.acct_trade_channel_cd = '12' ) UNION SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd = 'S' AND mad.acct_tdlinx_cd = conf_acct_tdlinx_cd AND ( mad.acct_trade_channel_cd = msd.trade_channel_cd OR mad.acct_trade_channel_cd = '12' ))) cnt WHERE hier.related_tdlinx = cnt.acct_tdlinx_cd ORDER BY acct_tdlinx, lvl DESC, related_tdlinx; ==================================================================================================================================================================== ******************************************************************************************************************************************************************** EXplain Plan ------------ PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2356927141 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 285 | 22515 | 266K (1)| 00:12:24 | | 1 | SORT ORDER BY | | 285 | 22515 | 266K (1)| 00:12:24 | |* 2 | HASH JOIN | | 285 | 22515 | 266K (1)| 00:12:24 | | 3 | VIEW | | 178 | 4094 | 815 (14)| 00:00:03 | | 4 | UNION-ALL | | | | | | |* 5 | FILTER | | | | | | |* 6 | CONNECT BY WITH FILTERING | | | | | | |* 7 | TABLE ACCESS FULL | MSTR_ACCT_DIM | 176 | 17776 | 815 (14)| 00:00:03 | | 8 | NESTED LOOPS | | | | | | | 9 | BUFFER SORT | | | | | | | 10 | CONNECT BY PUMP | | | | | | | 11 | TABLE ACCESS BY INDEX ROWID | MSTR_ACCT_DIM | 9 | 252 | 12 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | XIE02MSTR_ACCT_DIM | 9 | | 3 (0)| 00:00:01 | | 13 | TABLE ACCESS FULL | MSTR_ACCT_DIM | 269K| 15M| 809 (13)| 00:00:03 | |* 14 | TABLE ACCESS FULL | MSTR_ACCT_DIM | 176 | 3344 | 815 (14)| 00:00:03 | |* 15 | FILTER | | | | | | |* 16 | CONNECT BY WITH FILTERING | | | | | | |* 17 | TABLE ACCESS FULL | MSTR_ACCT_DIM | 176 | 17776 | 815 (14)| 00:00:03 | |* 18 | HASH JOIN | | | | | | | 19 | CONNECT BY PUMP | | | | | | | 20 | TABLE ACCESS FULL | MSTR_ACCT_DIM | 2692 | 75376 | 818 (14)| 00:00:03 | | 21 | TABLE ACCESS FULL | MSTR_ACCT_DIM | 269K| 15M| 809 (13)| 00:00:03 | | 22 | VIEW | | 160 | 8960 | 265K (1)| 00:12:22 | | 23 | SORT UNIQUE | | 160 | 3520 | 265K (1)| 00:12:22 | | 24 | WINDOW SORT | | 160 | 3520 | 265K (1)| 00:12:22 | | 25 | WINDOW SORT | | 160 | 3520 | 265K (1)| 00:12:22 | | 26 | VIEW | | 160 | 3520 | 265K (1)| 00:12:22 | | 27 | SORT UNIQUE | | 160 | 8906 | 265K (86)| 00:12:22 | | 28 | UNION-ALL | | | | | | |* 29 | HASH JOIN | | 34 | 1904 | 37941 (1)| 00:01:46 | |* 30 | TABLE ACCESS FULL | MSTR_ACCT_DIM | 118 | 2596 | 812 (13)| 00:00:03 | |* 31 | TABLE ACCESS BY INDEX ROWID| MSTR_STORE_DIM | 8095 | 268K| 37127 (1)| 00:01:44 | |* 32 | INDEX SKIP SCAN | XIE02MSTR_STORE_DIM | 50126 | | 311 (2)| 00:00:01 | |* 33 | HASH JOIN | | 24 | 1344 | 37941 (1)| 00:01:46 | |* 34 | TABLE ACCESS FULL | MSTR_ACCT_DIM | 38 | 836 | 812 (13)| 00:00:03 | |* 35 | TABLE ACCESS BY INDEX ROWID| MSTR_STORE_DIM | 6496 | 215K| 37127 (1)| 00:01:44 | |* 36 | INDEX SKIP SCAN | XIE02MSTR_STORE_DIM | 50126 | | 311 (2)| 00:00:01 | |* 37 | HASH JOIN | | 26 | 1456 | 37946 (1)| 00:01:46 | |* 38 | TABLE ACCESS FULL | MSTR_ACCT_DIM | 38 | 836 | 812 (13)| 00:00:03 | |* 39 | TABLE ACCESS BY INDEX ROWID| MSTR_STORE_DIM | 6516 | 216K| 37132 (1)| 00:01:44 | |* 40 | INDEX SKIP SCAN | XIE02MSTR_STORE_DIM | 50126 | | 311 (2)| 00:00:01 | |* 41 | HASH JOIN | | 17 | 935 | 37948 (1)| 00:01:46 | |* 42 | TABLE ACCESS FULL | MSTR_ACCT_DIM | 38 | 836 | 812 (13)| 00:00:03 | |* 43 | TABLE ACCESS BY INDEX ROWID| MSTR_STORE_DIM | 4027 | 129K| 37135 (1)| 00:01:44 | |* 44 | INDEX SKIP SCAN | XIE02MSTR_STORE_DIM | 50126 | | 311 (2)| 00:00:01 | |* 45 | HASH JOIN | | 15 | 825 | 37951 (1)| 00:01:46 | |* 46 | TABLE ACCESS FULL | MSTR_ACCT_DIM | 38 | 836 | 812 (13)| 00:00:03 | |* 47 | TABLE ACCESS BY INDEX ROWID| MSTR_STORE_DIM | 3743 | 120K| 37138 (1)| 00:01:44 | |* 48 | INDEX SKIP SCAN | XIE02MSTR_STORE_DIM | 50126 | | 311 (2)| 00:00:01 | |* 49 | HASH JOIN | | 11 | 594 | 37953 (1)| 00:01:46 | |* 50 | TABLE ACCESS FULL | MSTR_ACCT_DIM | 38 | 836 | 812 (13)| 00:00:03 | |* 51 | TABLE ACCESS BY INDEX ROWID| MSTR_STORE_DIM | 1666 | 53312 | 37140 (1)| 00:01:44 | |* 52 | INDEX SKIP SCAN | XIE02MSTR_STORE_DIM | 50126 | | 311 (2)| 00:00:01 | |* 53 | HASH JOIN | | 33 | 1848 | 37956 (1)| 00:01:46 | |* 54 | TABLE ACCESS FULL | MSTR_ACCT_DIM | 38 | 836 | 812 (13)| 00:00:03 | |* 55 | TABLE ACCESS BY INDEX ROWID| MSTR_STORE_DIM | 7394 | 245K| 37143 (1)| 00:01:44 | |* 56 | INDEX SKIP SCAN | XIE02MSTR_STORE_DIM | 50126 | | 311 (2)| 00:00:01 | ------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------------------------------------------------------------------------- 2 - access("HIER"."RELATED_TDLINX"="CNT"."ACCT_TDLINX_CD") 5 - filter(("ORIG_STATUS_CD"='FO' OR "ORIG_STATUS_CD"='OP') AND "END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 6 - access("ACCT_TDLINX_CD"=PRIOR DECODE("ACCT_TYPE_CD",'G',"ACCT_TDLINX_CD","IRT_TDLINX_CD")) 7 - filter("END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND ("ORIG_STATUS_CD"='FO' OR "ORIG_STATUS_CD"='OP') AND "ACCT_TDLINX_CD"<>DECODE("ACCT_TYPE_CD",'G',"ACCT_TDLINX_CD","IRT_TDLINX_CD")) 12 - access("ACCT_TDLINX_CD"=PRIOR DECODE("ACCT_TYPE_CD",'G',"ACCT_TDLINX_CD","IRT_TDLINX_CD")) 14 - filter("END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND ("ORIG_STATUS_CD"='FO' OR "ORIG_STATUS_CD"='OP')) 15 - filter(("ORIG_STATUS_CD"='FO' OR "ORIG_STATUS_CD"='OP') AND "END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 16 - access(DECODE("ACCT_TYPE_CD",'G',"ACCT_TDLINX_CD","IRT_TDLINX_CD")=PRIOR "ACCT_TDLINX_CD") 17 - filter("END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND ("ORIG_STATUS_CD"='FO' OR "ORIG_STATUS_CD"='OP') AND "ACCT_TDLINX_CD"<>DECODE("ACCT_TYPE_CD",'G',"ACCT_TDLINX_CD","IRT_TDLINX_CD")) 18 - access(DECODE("ACCT_TYPE_CD",'G',"ACCT_TDLINX_CD","IRT_TDLINX_CD")=PRIOR "ACCT_TDLINX_CD") 29 - access("ACCT_TDLINX_CD"="MSD"."OWNER_TDLINX_CD" AND "MSD"."TRADE_CHANNEL_CD"=DECODE("ACCT_TRADE_CLASS_CD",'L','12',"ACCT_TRADE_CHANNEL_CD")) 30 - filter("END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND ("ACCT_TYPE_CD"='B' OR "ACCT_TYPE_CD"='O')) 31 - filter("MSD"."OWNER_TDLINX_CD" IS NOT NULL) 32 - access("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) filter("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND ("MSD"."ORIG_STATUS_CD"='FO' OR "MSD"."ORIG_STATUS_CD"='OP')) 33 - access("ACCT_TDLINX_CD"="MSD"."PRI_SUP_TDLINX_CD") filter("MSD"."TRADE_CHANNEL_CD"=DECODE("ACCT_TRADE_CLASS_CD",'L','12',"ACCT_TRADE_CHANNEL_CD") OR DECODE("ACCT_TRADE_CLASS_CD",'L','12',"ACCT_TRADE_CHANNEL_CD")='12') 34 - filter("END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "ACCT_TYPE_CD"='S') 35 - filter("MSD"."PRI_SUP_TDLINX_CD" IS NOT NULL) 36 - access("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) filter("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND ("MSD"."ORIG_STATUS_CD"='FO' OR "MSD"."ORIG_STATUS_CD"='OP')) 37 - access("ACCT_TDLINX_CD"="GROCERY_ACCT_TDLINX_CD") filter("MSD"."TRADE_CHANNEL_CD"=DECODE("ACCT_TRADE_CLASS_CD",'L','12',"ACCT_TRADE_CHANNEL_CD") OR DECODE("ACCT_TRADE_CLASS_CD",'L','12',"ACCT_TRADE_CHANNEL_CD")='12') 38 - filter("END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "ACCT_TYPE_CD"='S') 39 - filter("GROCERY_ACCT_TDLINX_CD" IS NOT NULL) 40 - access("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) filter("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND ("MSD"."ORIG_STATUS_CD"='FO' OR "MSD"."ORIG_STATUS_CD"='OP')) 41 - access("ACCT_TDLINX_CD"="HBC_ACCT_TDLINX_CD") filter("MSD"."TRADE_CHANNEL_CD"=DECODE("ACCT_TRADE_CLASS_CD",'L','12',"ACCT_TRADE_CHANNEL_CD") OR DECODE("ACCT_TRADE_CLASS_CD",'L','12',"ACCT_TRADE_CHANNEL_CD")='12') 42 - filter("END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "ACCT_TYPE_CD"='S') 43 - filter("HBC_ACCT_TDLINX_CD" IS NOT NULL) 44 - access("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) filter("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND ("MSD"."ORIG_STATUS_CD"='FO' OR "MSD"."ORIG_STATUS_CD"='OP')) 45 - access("ACCT_TDLINX_CD"="GM_ACCT_TDLINX_CD") filter("MSD"."TRADE_CHANNEL_CD"=DECODE("ACCT_TRADE_CLASS_CD",'L','12',"ACCT_TRADE_CHANNEL_CD") OR DECODE("ACCT_TRADE_CLASS_CD",'L','12',"ACCT_TRADE_CHANNEL_CD")='12') 46 - filter("END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "ACCT_TYPE_CD"='S') 47 - filter("GM_ACCT_TDLINX_CD" IS NOT NULL) 48 - access("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) filter("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND ("MSD"."ORIG_STATUS_CD"='FO' OR "MSD"."ORIG_STATUS_CD"='OP')) 49 - access("ACCT_TDLINX_CD"="FR_ACCT_TDLINX_CD") filter("MSD"."TRADE_CHANNEL_CD"=DECODE("ACCT_TRADE_CLASS_CD",'L','12',"ACCT_TRADE_CHANNEL_CD") OR DECODE("ACCT_TRADE_CLASS_CD",'L','12',"ACCT_TRADE_CHANNEL_CD")='12') 50 - filter("END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "ACCT_TYPE_CD"='S') 51 - filter("FR_ACCT_TDLINX_CD" IS NOT NULL) 52 - access("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) filter("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND ("MSD"."ORIG_STATUS_CD"='FO' OR "MSD"."ORIG_STATUS_CD"='OP')) 53 - access("ACCT_TDLINX_CD"="CONF_ACCT_TDLINX_CD") filter("MSD"."TRADE_CHANNEL_CD"=DECODE("ACCT_TRADE_CLASS_CD",'L','12',"ACCT_TRADE_CHANNEL_CD") OR DECODE("ACCT_TRADE_CLASS_CD",'L','12',"ACCT_TRADE_CHANNEL_CD")='12') 54 - filter("END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "ACCT_TYPE_CD"='S') 55 - filter("CONF_ACCT_TDLINX_CD" IS NOT NULL) 56 - access("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) filter("MSD"."END_DT"=TO_DATE('2500-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND ("MSD"."ORIG_STATUS_CD"='FO' OR "MSD"."ORIG_STATUS_CD"='OP')) ====================================================================================================================================================================== ********************************************************************************************************************************************************************** DDL Statements - MSTR_ACCT_DIM (DLVRY_ACCT_DIM view is created from this table) ------------------------------------------------------------------------------- CREATE TABLE TDH.MSTR_ACCT_DIM ( ACCT_ID NUMBER NOT NULL, START_DT DATE NOT NULL, END_DT DATE NOT NULL, ACCT_CRC NUMBER NOT NULL, ACCT_PARTY_ID NUMBER NOT NULL, ACCT_TDLINX_CD VARCHAR2(6 BYTE) NOT NULL, ACCT_FAMILY_CD VARCHAR2(10 BYTE), ACCT_TYPE_CD VARCHAR2(1 BYTE), ACCT_NM VARCHAR2(32 BYTE), ACCT_ADDR VARCHAR2(50 BYTE), ACCT_CITY VARCHAR2(32 BYTE), ACCT_STATE VARCHAR2(2 BYTE), ACCT_ZIP VARCHAR2(9 BYTE), ACCT_STATE_FIPS_CD VARCHAR2(2 BYTE), ACCT_CNTY_FIPS_CD VARCHAR2(3 BYTE), COMMON_OPERATING_NMS VARCHAR2(40 BYTE), ACCT_TRADE_CHANNEL_CD VARCHAR2(2 BYTE), COMPANY_SIZE_CD VARCHAR2(1 BYTE), IRT_TDLINX_CD VARCHAR2(6 BYTE), IRT_NM VARCHAR2(32 BYTE), IRT_CITY VARCHAR2(32 BYTE), IRT_STATE VARCHAR2(2 BYTE), ULT_PARENT_TDLINX_CD VARCHAR2(6 BYTE), ULT_PARENT_NM VARCHAR2(32 BYTE), ULT_PARENT_CITY VARCHAR2(32 BYTE), ULT_PARENT_STATE VARCHAR2(2 BYTE), RPL_TDLINX_CD VARCHAR2(7 BYTE), ACCT_CNTRY VARCHAR2(10 BYTE), ACCT_TRADE_CLASS_CD VARCHAR2(2 BYTE), ORIG_STATUS_CD VARCHAR2(3 BYTE), ZCODE VARCHAR2(20 BYTE), ACCT_LATITUDE NUMBER(11,6), ACCT_LONGITUDE NUMBER(11,6), ACCT_GEO_PRECISION_CD VARCHAR2(1 BYTE), ACCT_BLOCK_ID VARCHAR2(15 BYTE), STATUS_VERIFY_DT DATE ) TABLESPACE TDH_DEFAULT_TAB PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOLOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; COMMENT ON TABLE TDH.MSTR_ACCT_DIM IS 'Account information used in the Account File'; CREATE INDEX TDH.XIE01MSTR_ACCT_DIM ON TDH.MSTR_ACCT_DIM (ACCT_PARTY_ID, END_DT) NOLOGGING TABLESPACE TDH_DEFAULT_IND PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE INDEX TDH.XIE02MSTR_ACCT_DIM ON TDH.MSTR_ACCT_DIM (ACCT_TDLINX_CD, END_DT) NOLOGGING TABLESPACE TDH_DEFAULT_IND PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE UNIQUE INDEX TDH.XPKMSTR_ACCT_DIM ON TDH.MSTR_ACCT_DIM (ACCT_ID) NOLOGGING TABLESPACE TDH_DEFAULT_IND PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 128K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; DROP PUBLIC SYNONYM MSTR_ACCT_DIM; CREATE OR REPLACE PUBLIC SYNONYM MSTR_ACCT_DIM FOR TDH.MSTR_ACCT_DIM; ALTER TABLE TDH.MSTR_ACCT_DIM ADD ( CONSTRAINT XPKMSTR_ACCT_DIM PRIMARY KEY (ACCT_ID) USING INDEX TDH.XPKMSTR_ACCT_DIM); GRANT DELETE, INSERT, SELECT, UPDATE ON TDH.MSTR_ACCT_DIM TO TDH_DEVELOPER; GRANT SELECT ON TDH.MSTR_ACCT_DIM TO TDH_DEVELOPER_ROLE; GRANT SELECT ON TDH.MSTR_ACCT_DIM TO TDH_SELECT; ====================================================================================================================================================================== ********************************************************************************************************************************************************************** DDL Statements - DLVRY_ACCT_DIM (View) -------------------------------------- CREATE OR REPLACE FORCE VIEW TDH.DLVRY_ACCT_DIM ( ACCT_ID, START_DT, END_DT, ACCT_CRC, ACCT_PARTY_ID, ACCT_TDLINX_CD, ACCT_FAMILY_CD, ACCT_TYPE_CD, ACCT_NM, ACCT_ADDR, ACCT_CITY, ACCT_STATE, ACCT_ZIP, ACCT_STATE_FIPS_CD, ACCT_CNTY_FIPS_CD, COMMON_OPERATING_NMS, ACCT_TRADE_CHANNEL_CD, COMPANY_SIZE_CD, IRT_TDLINX_CD, IRT_NM, IRT_CITY, IRT_STATE, ULT_PARENT_TDLINX_CD, ULT_PARENT_NM, ULT_PARENT_CITY, ULT_PARENT_STATE, RPL_TDLINX_CD, ACCT_CNTRY, ACCT_TRADE_CLASS_CD, ORIG_STATUS_CD, ZCODE, ACCT_LATITUDE, ACCT_LONGITUDE, ACCT_GEO_PRECISION_CD, ACCT_BLOCK_ID ) AS SELECT acct_id, start_dt, end_dt, acct_crc, acct_party_id, acct_tdlinx_cd, acct_family_cd, acct_type_cd, acct_nm, DECODE (acct_type_cd, 'G', 'Group', acct_addr), DECODE (acct_type_cd, 'G', 'Group', acct_city), DECODE (acct_type_cd, 'G', NULL, acct_state), DECODE (acct_type_cd, 'G', NULL, acct_zip), DECODE (acct_type_cd, 'G', '00', acct_state_fips_cd), DECODE (acct_type_cd, 'G', '000', acct_cnty_fips_cd), common_operating_nms, DECODE (acct_trade_class_cd, 'L', '12', acct_trade_channel_cd), company_size_cd, DECODE (acct_type_cd, 'G', acct_tdlinx_cd, irt_tdlinx_cd), DECODE (acct_type_cd, 'G', acct_nm, irt_nm), DECODE (acct_type_cd, 'G', 'Group', irt_city), DECODE (acct_type_cd, 'G', NULL, irt_state), DECODE (acct_type_cd, 'G', acct_tdlinx_cd, ult_parent_tdlinx_cd), DECODE (acct_type_cd, 'G', acct_nm, ult_parent_nm), DECODE (acct_type_cd, 'G', 'Group', ult_parent_city), DECODE (acct_type_cd, 'G', NULL, ult_parent_state), rpl_tdlinx_cd, DECODE (acct_cntry, 'CANADA', 'CAN', acct_cntry), acct_trade_class_cd, orig_status_cd, zcode, TO_CHAR (ROUND (acct_latitude, 4), '999999999D0000') acct_latitude, TO_CHAR (ROUND (acct_longitude, 4), '999999999D0000') acct_longitude, DECODE (acct_geo_precision_cd, 'I', 'A', 'X', 'A', 'C', 'Z', 'E', 'B', acct_geo_precision_cd) acct_geo_precision_cd, acct_block_id FROM MSTR_ACCT_DIM; DROP PUBLIC SYNONYM DLVRY_ACCT_DIM; CREATE OR REPLACE PUBLIC SYNONYM DLVRY_ACCT_DIM FOR TDH.DLVRY_ACCT_DIM; GRANT SELECT ON TDH.DLVRY_ACCT_DIM TO TDH_DEVELOPER; GRANT SELECT ON TDH.DLVRY_ACCT_DIM TO TDH_SELECT; ====================================================================================================================================================================== ********************************************************************************************************************************************************************** DDL Statements - MSTR_STORE_DIM ------------------------------- CREATE TABLE TDH.MSTR_STORE_DIM ( STORE_ID NUMBER NOT NULL, START_DT DATE NOT NULL, END_DT DATE, MODULE_1_DT DATE, MOD_1_CRC NUMBER, MODULE_2_DT DATE, MOD_2_CRC NUMBER, MODULE_3_DT DATE, MOD_3_CRC NUMBER, MODULE_4_DT DATE, MOD_4_CRC NUMBER, MODULE_5_DT DATE, MOD_5_CRC NUMBER, MODULE_6_DT DATE, MOD_6_CRC NUMBER, MODULE_7_DT DATE, MOD_7_CRC NUMBER, MODULE_8_DT DATE, MOD_8_CRC NUMBER, STORE_PARTY_ID NUMBER, STORE_TDLINX_CD VARCHAR2(7 BYTE) NOT NULL, ORIG_STATUS_CD VARCHAR2(3 BYTE), STORE_NM VARCHAR2(32 BYTE), STORE_NUM VARCHAR2(8 BYTE), STORE_ADDR VARCHAR2(50 BYTE), STORE_CITY VARCHAR2(32 BYTE), STORE_STATE VARCHAR2(2 BYTE), STORE_ZIP VARCHAR2(9 BYTE), STORE_PLACE_NM VARCHAR2(32 BYTE), STORE_STATE_FIPS_CD VARCHAR2(2 BYTE), STORE_CNTY_FIPS_CD VARCHAR2(3 BYTE), STORE_CNTRY VARCHAR2(10 BYTE), CAT_ID NUMBER, TRADE_CHANNEL_CD VARCHAR2(2 BYTE), SUB_CHANNEL_CD VARCHAR2(1 BYTE), COMPANY_SIZE_CD VARCHAR2(1 BYTE), CHAIN_IND VARCHAR2(1 BYTE), LATITUDE NUMBER(11,6), LONGITUDE NUMBER(11,6), GEO_PRECISION_CD VARCHAR2(1 BYTE), CENSUS_BLOCK_ID VARCHAR2(15 BYTE), AREA_CD VARCHAR2(3 BYTE), PHONE_NUM VARCHAR2(7 BYTE), STORE_VOL_CD VARCHAR2(2 BYTE), WKLY_VOL NUMBER, SQUARE_FOOTAGE NUMBER, FULL_TM_EMP_CNT NUMBER, NUM_OF_CHKOUTS NUMBER, OWNER_TDLINX_CD VARCHAR2(6 BYTE), OWNER_FAMILY_CD VARCHAR2(5 BYTE), OWNER_NM VARCHAR2(32 BYTE), OWNER_CITY VARCHAR2(25 BYTE), OWNER_STATE VARCHAR2(2 BYTE), OWNER_STATE_FIPS_CD VARCHAR2(2 BYTE), OWNER_CNTY_FIPS_CD VARCHAR2(3 BYTE), PRI_SUP_TDLINX_CD VARCHAR2(6 BYTE), PRI_SUP_FAMILY_CD VARCHAR2(5 BYTE), PRI_SUP_NM VARCHAR2(32 BYTE), PRI_SUP_CITY VARCHAR2(25 BYTE), PRI_SUP_STATE VARCHAR2(2 BYTE), PRI_SUP_STATE_FIPS_CD VARCHAR2(2 BYTE), PRI_SUP_CNTY_FIPS_CD VARCHAR2(3 BYTE), MARKETING_GRP_TDLINX_CD VARCHAR2(6 BYTE), MARKETING_GRP_NM VARCHAR2(32 BYTE), NON_CORP_OWNED_IND VARCHAR2(1 BYTE), GAS_IND VARCHAR2(1 BYTE), CIG_IND VARCHAR2(1 BYTE), PHARM_IND VARCHAR2(1 BYTE), LIQUOR_IND VARCHAR2(1 BYTE), WINE_IND VARCHAR2(1 BYTE), BEER_IND VARCHAR2(1 BYTE), ON_PREMISE_IND VARCHAR2(1 BYTE), PRI_FOOD_TYPE_CD VARCHAR2(2 BYTE), RPL_TDLINX_CD VARCHAR2(7 BYTE), GAS_VOL NUMBER, GROCERY_ACCT_TDLINX_CD VARCHAR2(10 BYTE), GROCERY_FAMILY_CD VARCHAR2(10 BYTE), GROCERY_NM VARCHAR2(32 BYTE), GROCERY_CITY VARCHAR2(32 BYTE), GROCERY_STATE VARCHAR2(2 BYTE), GROCERY_STATE_FIPS_CD VARCHAR2(2 BYTE), GROCERY_CNTY_FIPS_CD VARCHAR2(3 BYTE), HBC_ACCT_TDLINX_CD VARCHAR2(10 BYTE), HBC_FAMILY_CD VARCHAR2(10 BYTE), HBC_NM VARCHAR2(32 BYTE), HBC_CITY VARCHAR2(32 BYTE), HBC_STATE VARCHAR2(2 BYTE), HBC_STATE_FIPS_CD VARCHAR2(2 BYTE), HBC_CNTY_FIPS_CD VARCHAR2(3 BYTE), GM_ACCT_TDLINX_CD VARCHAR2(10 BYTE), GM_FAMILY_CD VARCHAR2(10 BYTE), GM_NM VARCHAR2(32 BYTE), GM_CITY VARCHAR2(32 BYTE), GM_STATE VARCHAR2(2 BYTE), GM_STATE_FIPS_CD VARCHAR2(2 BYTE), GM_CNTY_FIPS_CD VARCHAR2(3 BYTE), FR_ACCT_TDLINX_CD VARCHAR2(10 BYTE), FR_FAMILY_CD VARCHAR2(10 BYTE), FR_NM VARCHAR2(32 BYTE), FR_CITY VARCHAR2(32 BYTE), FR_STATE VARCHAR2(2 BYTE), FR_STATE_FIPS_CD VARCHAR2(2 BYTE), FR_CNTY_FIPS_CD VARCHAR2(3 BYTE), CONF_ACCT_TDLINX_CD VARCHAR2(10 BYTE), CONF_FAMILY_CD VARCHAR2(10 BYTE), CONF_NM VARCHAR2(32 BYTE), CONF_CITY VARCHAR2(32 BYTE), CONF_STATE VARCHAR2(2 BYTE), CONF_STATE_FIPS_CD VARCHAR2(2 BYTE), CONF_CNTY_FIPS_CD VARCHAR2(3 BYTE), NAICS_CD VARCHAR2(10 BYTE), STATUS_VERIFY_DT DATE, TRADE_CLASS_CD VARCHAR2(2 BYTE), ZCODE VARCHAR2(20 BYTE), NAM_PRICEPOP_PGM_IND VARCHAR2(1 BYTE), NAM_RADIO_PGM_IND VARCHAR2(1 BYTE), NAM_AISLEVISION_PGM_IND VARCHAR2(1 BYTE), NAM_CARTS_PGM_IND VARCHAR2(1 BYTE), NAM_ADSTICKS_PGM_IND VARCHAR2(1 BYTE), NAM_COUPON_MACHINE_IND VARCHAR2(1 BYTE), NAM_FLOORTALK_PGM_IND VARCHAR2(1 BYTE), NAM_SHELFTALK_PGM_IND VARCHAR2(1 BYTE), CATALINA_COUPON_PGM_IND VARCHAR2(1 BYTE), FRONTLINE_IND VARCHAR2(1 BYTE), FLOOR_AD_IND VARCHAR2(1 BYTE), COUPON_PLUS_IND VARCHAR2(1 BYTE), CART_PLUS_IND VARCHAR2(1 BYTE), SHELF_PLUS_IND VARCHAR2(1 BYTE), GRO_WKLY_VOL NUMBER, GRO_SQ_FOOTAGE NUMBER, RPT_WKLY_VOL NUMBER, RPT_SQ_FOOTAGE NUMBER, RPT_NAICS_CD VARCHAR2(10 BYTE), GROCERY_CRC NUMBER, HBC_CRC NUMBER, GM_CRC NUMBER, FR_CRC NUMBER, CONF_CRC NUMBER, CLINIC_IND VARCHAR2(1 BYTE), CLINIC_NAME VARCHAR2(32 BYTE) ) TABLESPACE MSTR_STORE_DIM_TAB_B PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 4M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOLOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; COMMENT ON TABLE TDH.MSTR_STORE_DIM IS 'Store information used in the Store File'; COMMENT ON COLUMN TDH.MSTR_STORE_DIM.STATUS_VERIFY_DT IS 'Date the Store status was last verified'; COMMENT ON COLUMN TDH.MSTR_STORE_DIM.RPT_WKLY_VOL IS 'Weekly vol for Cat ID, ACV Code and report'; COMMENT ON COLUMN TDH.MSTR_STORE_DIM.RPT_SQ_FOOTAGE IS 'Seeling Area to use for report'; COMMENT ON COLUMN TDH.MSTR_STORE_DIM.RPT_NAICS_CD IS 'NAICS code used for Category ID determination'; CREATE INDEX TDH.XIE01MSTR_STORE_DIM ON TDH.MSTR_STORE_DIM (STORE_PARTY_ID) NOLOGGING TABLESPACE MSTR_STORE_DIM_IND_B PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE INDEX TDH.XIE02MSTR_STORE_DIM ON TDH.MSTR_STORE_DIM (CAT_ID, STORE_CNTRY, ORIG_STATUS_CD, END_DT, ZCODE) NOLOGGING TABLESPACE MSTR_STORE_DIM_IND_B PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 128K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE INDEX TDH.XIE03MSTR_STORE_DIM ON TDH.MSTR_STORE_DIM (STORE_PARTY_ID, END_DT) NOLOGGING TABLESPACE MSTR_STORE_DIM_IND_B PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE INDEX TDH.XIE04MSTR_STORE_DIM ON TDH.MSTR_STORE_DIM (STORE_PARTY_ID, START_DT, END_DT) NOLOGGING TABLESPACE MSTR_STORE_DIM_IND_B PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE INDEX TDH.XIE05MSTR_STORE_DIM ON TDH.MSTR_STORE_DIM (STORE_TDLINX_CD, END_DT) NOLOGGING TABLESPACE MSTR_STORE_DIM_IND_B PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE UNIQUE INDEX TDH.XPKMSTR_STORE_DIM ON TDH.MSTR_STORE_DIM (STORE_ID) NOLOGGING TABLESPACE MSTR_STORE_DIM_IND_B PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; DROP PUBLIC SYNONYM MSTR_STORE_DIM; CREATE OR REPLACE PUBLIC SYNONYM MSTR_STORE_DIM FOR TDH.MSTR_STORE_DIM; ALTER TABLE TDH.MSTR_STORE_DIM ADD ( CONSTRAINT XPKMSTR_STORE_DIM PRIMARY KEY (STORE_ID) USING INDEX TDH.XPKMSTR_STORE_DIM); GRANT DELETE, INSERT, SELECT, UPDATE ON TDH.MSTR_STORE_DIM TO TDH_DEVELOPER; GRANT SELECT ON TDH.MSTR_STORE_DIM TO TDH_DEVELOPER_ROLE; GRANT SELECT ON TDH.MSTR_STORE_DIM TO TDH_SELECT; ====================================================================================================================================================================== ********************************************************************************************************************************************************************** Trace Results ------------- TKPROF: Release 10.2.0.2.0 - Production on Fri Nov 18 14:18:42 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Trace file: tdhq_ora_2187314.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** ALTER SESSION SET SQL_TRACE=TRUE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 61 ******************************************************************************** select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 5 0.00 0.00 0 0 0 0 Execute 87 0.00 0.00 0 0 0 0 Fetch 87 0.02 0.17 29 256 0 82 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 179 0.02 0.17 29 256 0 82 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=2 pw=0 time=16568 us) 1 INDEX RANGE SCAN I_OBJ2 (cr=2 pr=1 pw=0 time=10148 us)(object id 37) ******************************************************************************** select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols, nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans, t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln, t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1), nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0), nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit, ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 7 0.00 0.00 0 0 0 0 Execute 25 0.00 0.00 0 0 0 0 Fetch 25 0.00 0.00 0 100 0 25 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 57 0.00 0.01 0 100 0 25 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=30 us) 1 TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=23 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=12 us)(object id 3) 0 TABLE ACCESS BY INDEX ROWID TAB_STATS$ (cr=1 pr=0 pw=0 time=5 us) 0 INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=1 pr=0 pw=0 time=4 us)(object id 709) ******************************************************************************** select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 6 0.00 0.00 0 0 0 0 Execute 237 0.01 0.00 0 0 0 0 Fetch 237 0.04 0.09 20 705 0 231 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 480 0.05 0.11 20 705 0 231 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: RULE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=1 pw=0 time=7855 us) 1 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=22 us)(object id 257) ******************************************************************************** select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 115 0.00 0.00 0 0 0 0 Fetch 115 0.08 0.26 54 345 0 1559 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 234 0.08 0.27 54 345 0 1559 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: RULE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 20 SORT ORDER BY (cr=3 pr=1 pw=0 time=4827 us) 20 TABLE ACCESS CLUSTER HISTGRM$ (cr=3 pr=1 pw=0 time=4781 us) 1 INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=17 us)(object id 252) ******************************************************************************** select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property, i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey, i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256), i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0), nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null, null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 7 0.00 0.00 0 0 0 0 Execute 29 0.01 0.01 0 0 0 0 Fetch 68 0.01 0.04 7 240 0 39 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 104 0.02 0.05 7 240 0 39 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT ORDER BY (cr=8 pr=1 pw=0 time=12972 us) 1 HASH JOIN OUTER (cr=8 pr=1 pw=0 time=12936 us) 1 NESTED LOOPS OUTER (cr=5 pr=0 pw=0 time=44 us) 1 TABLE ACCESS CLUSTER IND$ (cr=4 pr=0 pw=0 time=38 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=17 us)(object id 3) 0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=1 pr=0 pw=0 time=5 us) 0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=1 pr=0 pw=0 time=4 us)(object id 711) 0 VIEW (cr=3 pr=1 pw=0 time=12750 us) 0 SORT GROUP BY (cr=3 pr=1 pw=0 time=12748 us) 0 TABLE ACCESS CLUSTER CDEF$ (cr=3 pr=1 pw=0 time=12731 us) 1 INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=0 pw=0 time=9 us)(object id 30) ******************************************************************************** select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 6 0.00 0.00 0 0 0 0 Execute 39 0.00 0.00 0 0 0 0 Fetch 113 0.00 0.00 0 224 0 74 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 158 0.00 0.01 0 224 0 74 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID ICOL$ (cr=4 pr=0 pw=0 time=30 us) 1 INDEX RANGE SCAN I_ICOL1 (cr=3 pr=0 pw=0 time=25 us)(object id 40) ******************************************************************************** select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2, nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182, scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$, rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2, nvl(spare3,0) from col$ where obj#=:1 order by intcol# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 8 0.00 0.00 0 0 0 0 Execute 40 0.00 0.00 0 0 0 0 Fetch 505 0.00 0.01 1 123 0 465 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 553 0.00 0.02 1 123 0 465 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 8 SORT ORDER BY (cr=3 pr=0 pw=0 time=74 us) 8 TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=39 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=18 us)(object id 3) ******************************************************************************** select cols,audit$,textlength,intcols,property,flags,rowid from view$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 9 0.00 0.00 0 0 0 0 Fetch 9 0.00 0.03 9 27 0 9 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 22 0.00 0.04 9 27 0 9 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID VIEW$ (cr=3 pr=3 pw=0 time=12838 us) 1 INDEX UNIQUE SCAN I_VIEW1 (cr=2 pr=2 pw=0 time=7281 us)(object id 99) ******************************************************************************** select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 40 0.00 0.00 0 0 0 0 Execute 40 0.00 0.00 0 0 0 0 Fetch 213 0.02 0.24 32 522 0 173 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 293 0.02 0.24 32 522 0 173 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT ORDER BY (cr=6 pr=2 pw=0 time=16668 us) 1 NESTED LOOPS OUTER (cr=6 pr=2 pw=0 time=16641 us) 1 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=3 pr=2 pw=0 time=16615 us) 1 INDEX RANGE SCAN I_DEPENDENCY1 (cr=2 pr=1 pw=0 time=9985 us)(object id 122) 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=22 us) 1 INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=14 us)(object id 36) ******************************************************************************** select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname, o.dataobj#,o.flags from obj$ o where o.obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 38 0.00 0.00 0 0 0 0 Fetch 38 0.01 0.00 0 114 0 38 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 80 0.01 0.00 0 114 0 38 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=52 us) 1 INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=27 us)(object id 36) ******************************************************************************** select order#,columns,types from access$ where d_obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 40 0.00 0.00 0 0 0 0 Execute 40 0.00 0.00 0 0 0 0 Fetch 135 0.00 0.14 21 270 0 95 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 215 0.00 0.15 21 270 0 95 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID ACCESS$ (cr=4 pr=2 pw=0 time=20031 us) 1 INDEX RANGE SCAN I_ACCESS1 (cr=3 pr=1 pw=0 time=7076 us)(object id 124) ******************************************************************************** select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 21 0.00 0.00 0 0 0 0 Fetch 21 0.00 0.00 0 21 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 46 0.00 0.00 0 21 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=1 pr=0 pw=0 time=18 us) 0 INDEX RANGE SCAN I_CDEF3 (cr=1 pr=0 pw=0 time=15 us)(object id 52) ******************************************************************************** select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0), rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from cdef$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 21 0.00 0.00 0 0 0 0 Fetch 120 0.00 0.01 4 157 0 99 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 145 0.00 0.01 4 157 0 99 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS CLUSTER CDEF$ (cr=2 pr=1 pw=0 time=739 us) 0 INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=1 pw=0 time=734 us)(object id 30) ******************************************************************************** select text from view$ where rowid=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 37 0.00 0.00 0 0 0 0 Execute 37 0.00 0.00 0 0 0 0 Fetch 37 0.00 0.00 0 74 0 37 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 111 0.00 0.00 0 74 0 37 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=15 us) ******************************************************************************** select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 99 0.00 0.00 0 0 0 0 Fetch 198 0.01 0.01 3 396 0 99 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 300 0.01 0.02 3 396 0 99 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID CCOL$ (cr=4 pr=1 pw=0 time=5951 us) 1 INDEX RANGE SCAN I_CCOL1 (cr=3 pr=1 pw=0 time=5947 us)(object id 54) ******************************************************************************** select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts, NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0), NVL(scanhint,0) from seg$ where ts#=:1 and file#=:2 and block#=:3 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 9 0.00 0.00 0 0 0 0 Execute 9 0.00 0.00 0 0 0 0 Fetch 9 0.01 0.03 6 27 0 9 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 27 0.01 0.04 6 27 0 9 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS CLUSTER SEG$ (cr=3 pr=1 pw=0 time=6801 us) 1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=1 pw=0 time=6785 us)(object id 9) ******************************************************************************** select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 8 0.00 0.00 0 0 0 0 Execute 21 0.00 0.00 0 0 0 0 Fetch 21 0.01 0.02 3 42 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 50 0.01 0.02 3 42 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT GROUP BY (cr=2 pr=1 pw=0 time=5663 us) 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=1 pw=0 time=5642 us) 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=1 pw=0 time=5639 us)(object id 103) ******************************************************************************** select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 8 0.00 0.00 0 0 0 0 Execute 21 0.00 0.00 0 0 0 0 Fetch 32 0.00 0.00 1 48 0 11 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 61 0.00 0.01 1 48 0 11 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled from obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.obj#=o.obj# and o.owner#=u.user# and bitand(property,16)=0 and bitand(property,8)=0 order by o.obj# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 1 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=1 pr=0 pw=0 time=43 us) 0 NESTED LOOPS (cr=1 pr=0 pw=0 time=32 us) 0 NESTED LOOPS (cr=1 pr=0 pw=0 time=31 us) 0 TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=1 pr=0 pw=0 time=29 us) 0 INDEX RANGE SCAN I_TRIGGER1 (cr=1 pr=0 pw=0 time=18 us)(object id 125) 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)(object id 36) 0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us)(object id 11) ******************************************************************************** INSERT INTO wk_acct_wstore (acct_tdlinx, related_tdlinx, acct_type_cd, lvl, mkt_grp_tdlinx, trade_channel_cd, sub_channel_cd, mkt_grp_count, trade_channel_count, sub_channel_count) SELECT acct_tdlinx, related_tdlinx, acct_type_cd, lvl, mkt_grp_tdlinx, trade_channel_cd, sub_channel_cd, mkt_grp_count, trade_channel_count, sub_channel_count FROM (SELECT connect_by_root acct_tdlinx_cd AS acct_tdlinx, irt_tdlinx_cd AS related_tdlinx, connect_by_isleaf isleaf, ( LEVEL ) AS lvl FROM dlvry_acct_dim mad WHERE mad.orig_status_cd IN ( 'FO', 'OP' ) AND mad.end_dt = '31 dec 2500' START WITH mad.orig_status_cd IN ( 'FO', 'OP' ) AND mad.end_dt = '31 dec 2500' AND mad.acct_tdlinx_cd <> mad.irt_tdlinx_cd CONNECT BY nocycle PRIOR mad.irt_tdlinx_cd = mad.acct_tdlinx_cd UNION ALL SELECT acct_tdlinx_cd, acct_tdlinx_cd, 0, 0 FROM dlvry_acct_dim mad WHERE mad.orig_status_cd IN ( 'FO', 'OP' ) AND mad.end_dt = '31 dec 2500' UNION ALL SELECT connect_by_root irt_tdlinx_cd, acct_tdlinx_cd, connect_by_isleaf isleaf, ( 0 - LEVEL ) AS lvl FROM dlvry_acct_dim mad WHERE mad.orig_status_cd IN ( 'FO', 'OP' ) AND mad.end_dt = '31 dec 2500' START WITH mad.orig_status_cd IN ( 'FO', 'OP' ) AND mad.end_dt = '31 dec 2500' AND mad.acct_tdlinx_cd <> mad.irt_tdlinx_cd CONNECT BY nocycle PRIOR mad.acct_tdlinx_cd = mad.irt_tdlinx_cd) hier, (SELECT DISTINCT acct_tdlinx_cd, acct_type_cd, mkt_grp_tdlinx, trade_channel_cd, sub_channel_cd, COUNT(store_tdlinx_cd) over (PARTITION BY acct_tdlinx_cd , mkt_grp_tdlinx) mkt_grp_count, COUNT(store_tdlinx_cd) over (PARTITION BY acct_tdlinx_cd , trade_channel_cd) trade_channel_count, COUNT(store_tdlinx_cd) over (PARTITION BY acct_tdlinx_cd , trade_channel_cd, sub_channel_cd) sub_channel_count FROM (SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd AS mkt_grp_tdlinx, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd IN ( 'B', 'O' ) AND mad.acct_tdlinx_cd = msd.owner_tdlinx_cd AND mad.acct_trade_channel_cd = msd.trade_channel_cd UNION SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd = 'S' AND mad.acct_tdlinx_cd = msd.pri_sup_tdlinx_cd AND ( mad.acct_trade_channel_cd = msd.trade_channel_cd OR mad.acct_trade_channel_cd = '12' ) UNION SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd = 'S' AND mad.acct_tdlinx_cd = grocery_acct_tdlinx_cd AND ( mad.acct_trade_channel_cd = msd.trade_channel_cd OR mad.acct_trade_channel_cd = '12' ) UNION SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd = 'S' AND mad.acct_tdlinx_cd = hbc_acct_tdlinx_cd AND ( mad.acct_trade_channel_cd = msd.trade_channel_cd OR mad.acct_trade_channel_cd = '12' ) UNION SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd = 'S' AND mad.acct_tdlinx_cd = gm_acct_tdlinx_cd AND ( mad.acct_trade_channel_cd = msd.trade_channel_cd OR mad.acct_trade_channel_cd = '12' ) UNION SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd = 'S' AND mad.acct_tdlinx_cd = fr_acct_tdlinx_cd AND ( mad.acct_trade_channel_cd = msd.trade_channel_cd OR mad.acct_trade_channel_cd = '12' ) UNION SELECT mad.acct_tdlinx_cd, mad.acct_type_cd, msd.store_tdlinx_cd, msd.marketing_grp_tdlinx_cd, msd.trade_channel_cd, msd.sub_channel_cd FROM dlvry_acct_dim mad, mstr_store_dim msd WHERE mad.end_dt = '31 dec 2500' AND msd.end_dt = '31 dec 2500' AND msd.orig_status_cd IN ( 'OP', 'FO' ) AND mad.acct_type_cd = 'S' AND mad.acct_tdlinx_cd = conf_acct_tdlinx_cd AND ( mad.acct_trade_channel_cd = msd.trade_channel_cd OR mad.acct_trade_channel_cd = '12' ))) cnt WHERE hier.related_tdlinx = cnt.acct_tdlinx_cd ORDER BY acct_tdlinx, lvl DESC, related_tdlinx call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.06 0 0 0 0 Execute 1 81.28 127.60 10227 50159 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 81.31 127.66 10227 50159 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 61 Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=0 pr=0 pw=0 time=28 us) 0 HASH JOIN (cr=0 pr=0 pw=0 time=22 us) 243873 VIEW (cr=50159 pr=10227 pw=0 time=127738154 us) 243873 UNION-ALL (cr=50159 pr=10227 pw=0 time=127494280 us) 243873 FILTER (cr=50159 pr=10227 pw=0 time=126762657 us) 4779056 CONNECT BY WITH FILTERING (cr=50159 pr=10227 pw=0 time=125424002 us) 4171 TABLE ACCESS FULL MSTR_ACCT_DIM (cr=9665 pr=9654 pw=0 time=257270 us) 36168 NESTED LOOPS (cr=40494 pr=573 pw=0 time=238201 us) 2091 BUFFER SORT (cr=0 pr=0 pw=0 time=2486 us) 2091 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=13 us) 36168 TABLE ACCESS BY INDEX ROWID MSTR_ACCT_DIM (cr=40494 pr=573 pw=0 time=381875 us) 36168 INDEX RANGE SCAN XIE02MSTR_ACCT_DIM (cr=4334 pr=573 pw=0 time=237188 us)(object id 282849) 0 TABLE ACCESS FULL MSTR_ACCT_DIM (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL MSTR_ACCT_DIM (cr=0 pr=0 pw=0 time=0 us) 0 FILTER (cr=0 pr=0 pw=0 time=0 us) 0 CONNECT BY WITH FILTERING (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL MSTR_ACCT_DIM (cr=0 pr=0 pw=0 time=0 us) 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us) 0 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL MSTR_ACCT_DIM (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL MSTR_ACCT_DIM (cr=0 pr=0 pw=0 time=0 us) 0 VIEW (cr=0 pr=0 pw=0 time=0 us) 0 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us) 0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us) 0 WINDOW SORT (cr=0 pr=0 pw=0 time=0 us) 0 VIEW (cr=0 pr=0 pw=0 time=0 us) 0 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us) 0 UNION-ALL (cr=0 pr=0 pw=0 time=0 us) 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL MSTR_ACCT_DIM (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS BY INDEX ROWID MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us) 0 INDEX SKIP SCAN XIE02MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us)(object id 282852) 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL MSTR_ACCT_DIM (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS BY INDEX ROWID MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us) 0 INDEX SKIP SCAN XIE02MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us)(object id 282852) 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL MSTR_ACCT_DIM (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS BY INDEX ROWID MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us) 0 INDEX SKIP SCAN XIE02MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us)(object id 282852) 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL MSTR_ACCT_DIM (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS BY INDEX ROWID MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us) 0 INDEX SKIP SCAN XIE02MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us)(object id 282852) 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL MSTR_ACCT_DIM (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS BY INDEX ROWID MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us) 0 INDEX SKIP SCAN XIE02MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us)(object id 282852) 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL MSTR_ACCT_DIM (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS BY INDEX ROWID MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us) 0 INDEX SKIP SCAN XIE02MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us)(object id 282852) 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL MSTR_ACCT_DIM (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS BY INDEX ROWID MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us) 0 INDEX SKIP SCAN XIE02MSTR_STORE_DIM (cr=0 pr=0 pw=0 time=0 us)(object id 282852) ******************************************************************************** select u.name, o.name, o.namespace, o.type#, decode(bitand(i.property,1024),0, 0,1) from ind$ i,obj$ o,user$ u where i.obj#=:1 and o.obj#=i.bo# and o.owner#=u.user# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 4 0.00 0.00 0 32 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.00 0.00 0 32 0 4 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=8 pr=0 pw=0 time=61 us) 1 NESTED LOOPS (cr=6 pr=0 pw=0 time=38 us) 1 TABLE ACCESS BY INDEX ROWID IND$ (cr=3 pr=0 pw=0 time=27 us) 1 INDEX UNIQUE SCAN I_IND1 (cr=2 pr=0 pw=0 time=16 us)(object id 39) 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=10 us) 1 INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=6 us)(object id 36) 1 TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=18 us) 1 INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=5 us)(object id 11) ******************************************************************************** select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused, definitrans, defmaxtrans, deftiniexts, defextsize, defminexts, defmaxexts, defextpct, deflists, defgroups, deflogging, spare1, mod(spare2, 256) subparttype, mod(trunc(spare2/256), 256) subpartkeycols, mod(trunc(spare2/65536), 65536) defsubpartcnt, mod(trunc(spare2/4294967296), 256) defhscflags from partobj$ where obj# = :1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 1 2 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.01 1 2 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID PARTOBJ$ (cr=1 pr=1 pw=0 time=5506 us) 0 INDEX UNIQUE SCAN I_PARTOBJ$ (cr=1 pr=1 pw=0 time=5500 us)(object id 263) ******************************************************************************** DROP INDEX tdh.idx_wstore_bm_lvl call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.13 8 3 245 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.13 8 3 245 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 61 ******************************************************************************** select audit$,options from procedure$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 22 0.00 0.00 0 0 0 0 Execute 22 0.00 0.00 0 0 0 0 Fetch 22 0.00 0.04 9 66 0 22 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 66 0.00 0.04 9 66 0 22 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID PROCEDURE$ (cr=3 pr=2 pw=0 time=8062 us) 1 INDEX UNIQUE SCAN I_PROCEDURE1 (cr=2 pr=1 pw=0 time=2490 us)(object id 109) ******************************************************************************** select actionsize from trigger$ where obj# = :1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 11 0.00 0.00 0 0 0 0 Execute 11 0.01 0.00 0 0 0 0 Fetch 11 0.00 0.03 5 22 0 11 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 33 0.01 0.03 5 22 0 11 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=2 pr=2 pw=0 time=16761 us) 1 INDEX UNIQUE SCAN I_TRIGGER2 (cr=1 pr=1 pw=0 time=11150 us)(object id 126) ******************************************************************************** select action# from trigger$ where obj# = :1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 11 0.00 0.00 0 0 0 0 Execute 11 0.00 0.00 0 0 0 0 Fetch 11 0.00 0.00 0 33 0 11 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 33 0.00 0.00 0 33 0 11 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=2 pr=0 pw=0 time=38 us) 1 INDEX UNIQUE SCAN I_TRIGGER2 (cr=1 pr=0 pw=0 time=14 us)(object id 126) ******************************************************************************** select baseobject,type#,update$,insert$,delete$,refnewname,refoldname, whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt, refprtname,rowid from trigger$ where obj# =:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 11 0.00 0.00 0 0 0 0 Execute 11 0.00 0.00 0 0 0 0 Fetch 11 0.00 0.00 0 22 0 11 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 33 0.00 0.00 0 22 0 11 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=2 pr=0 pw=0 time=18 us) 1 INDEX UNIQUE SCAN I_TRIGGER2 (cr=1 pr=0 pw=0 time=7 us)(object id 126) ******************************************************************************** select tc.type#,tc.intcol#,tc.position#,c.type#, c.length,c.scale, c.precision#,c.charsetid,c.charsetform from triggercol$ tc,col$ c ,trigger$ tr where tc.obj#=:1 and c.obj#=:2 and tc.intcol#=c.intcol# and tr.obj# = tc.obj# and (bitand(tr.property,32) != 32 or bitand(tc.type#,20) = 20) union select type#,intcol#,position#,69,0,0, 0,0,0 from triggercol$ where obj#=:3 and intcol#=1001 union select tc.type#, tc.intcol#,tc.position#,121,0,0,0,0,0 from triggercol$ tc,trigger$ tr where tr.obj# = tc.obj# and bitand(tr.property,32) = 32 and tc.obj# = :4 and bitand(tc.type#,20) != 20 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 11 0.00 0.00 0 0 0 0 Execute 11 0.00 0.00 0 0 0 0 Fetch 11 0.00 0.00 1 77 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 33 0.00 0.02 1 77 0 0 Misses in library cache during parse: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT UNIQUE (cr=7 pr=1 pw=0 time=7404 us) 0 UNION-ALL (cr=7 pr=1 pw=0 time=7392 us) 0 HASH JOIN (cr=4 pr=0 pw=0 time=89 us) 0 NESTED LOOPS (cr=4 pr=0 pw=0 time=36 us) 1 TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=2 pr=0 pw=0 time=24 us) 1 INDEX UNIQUE SCAN I_TRIGGER2 (cr=1 pr=0 pw=0 time=11 us)(object id 126) 0 TABLE ACCESS CLUSTER COL$ (cr=2 pr=0 pw=0 time=9 us) 0 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=7 us)(object id 3) 0 INDEX RANGE SCAN I_TRIGGERCOL2 (cr=0 pr=0 pw=0 time=0 us)(object id 128) 0 INDEX RANGE SCAN I_TRIGGERCOL2 (cr=1 pr=1 pw=0 time=7276 us)(object id 128) 0 NESTED LOOPS (cr=2 pr=0 pw=0 time=18 us) 0 TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=2 pr=0 pw=0 time=18 us) 1 INDEX UNIQUE SCAN I_TRIGGER2 (cr=1 pr=0 pw=0 time=6 us)(object id 126) 0 INDEX RANGE SCAN I_TRIGGERCOL2 (cr=0 pr=0 pw=0 time=0 us)(object id 128) ******************************************************************************** select user#,type# from user$ where name=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 4 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=23 us) 1 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=13 us)(object id 44) ******************************************************************************** select name,password,datats#,tempts#,type#,defrole,resource$, ptime, exptime, ltime, astatus, lcount, decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP', defschclass),spare1 from user$ where user#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 0 4 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=35 us) 1 INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=15 us)(object id 11) ******************************************************************************** select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 21 0.00 0.00 0 0 0 0 Execute 21 0.00 0.00 0 0 0 0 Fetch 45 0.00 0.04 13 114 0 24 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 87 0.00 0.05 13 114 0 24 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS BY INDEX ROWID IDL_SB4$ (cr=6 pr=2 pw=0 time=9145 us) 2 INDEX RANGE SCAN I_IDL_SB41 (cr=4 pr=1 pw=0 time=78 us)(object id 117) ******************************************************************************** select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 21 0.00 0.00 0 0 0 0 Execute 21 0.00 0.00 0 0 0 0 Fetch 45 0.02 0.12 33 143 0 34 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 87 0.02 0.12 33 143 0 34 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID IDL_UB1$ (cr=4 pr=2 pw=0 time=14347 us) 1 INDEX RANGE SCAN I_IDL_UB11 (cr=3 pr=1 pw=0 time=5572 us)(object id 114) ******************************************************************************** select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 21 0.00 0.00 0 0 0 0 Execute 21 0.00 0.00 0 0 0 0 Fetch 28 0.00 0.03 8 63 0 7 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 70 0.00 0.03 8 63 0 7 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID IDL_CHAR$ (cr=4 pr=2 pw=0 time=4639 us) 1 INDEX RANGE SCAN I_IDL_CHAR1 (cr=3 pr=1 pw=0 time=62 us)(object id 115) ******************************************************************************** select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 21 0.00 0.00 0 0 0 0 Execute 21 0.00 0.00 0 0 0 0 Fetch 28 0.00 0.10 11 77 0 14 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 70 0.00 0.11 11 77 0 14 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS BY INDEX ROWID IDL_UB2$ (cr=5 pr=2 pw=0 time=61792 us) 2 INDEX RANGE SCAN I_IDL_UB21 (cr=3 pr=1 pw=0 time=56931 us)(object id 116) ******************************************************************************** select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and grantee#=:2 group by privilege#,nvl(col#,0) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 6 0.00 0.00 0 0 0 0 Fetch 9 0.00 0.03 4 15 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 17 0.00 0.03 4 15 0 3 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT GROUP BY (cr=2 pr=2 pw=0 time=6233 us) 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=2 pw=0 time=6211 us) 0 INDEX RANGE SCAN I_OBJAUTH2 (cr=2 pr=2 pw=0 time=6206 us)(object id 104) ******************************************************************************** BEGIN BEGIN IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) THEN xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name); END IF; EXCEPTION WHEN OTHERS THEN null; END; BEGIN IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name); END IF; EXCEPTION WHEN OTHERS THEN null; END; END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.01 0.02 0 0 0 0 Execute 3 0.00 0.02 1 14 0 3 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.01 0.04 1 14 0 3 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 35 (recursive depth: 1) ******************************************************************************** select obj# from oid$ where user#=:1 and oid$=:2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.01 2 2 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 2 2 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID OID$ (cr=2 pr=2 pw=0 time=13104 us) 0 INDEX UNIQUE SCAN I_OID1 (cr=2 pr=2 pw=0 time=13099 us)(object id 179) ******************************************************************************** SELECT USER_ID FROM ALL_USERS WHERE USERNAME = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 6 0.00 0.00 0 0 0 0 Fetch 6 0.00 0.00 0 42 0 6 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 13 0.00 0.00 0 42 0 6 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 35 (recursive depth: 2) ******************************************************************************** select node,owner,name from syn$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 5 0.00 0.00 0 0 0 0 Execute 7 0.00 0.00 0 0 0 0 Fetch 7 0.01 0.00 3 21 0 7 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 19 0.01 0.01 3 21 0 7 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID SYN$ (cr=3 pr=0 pw=0 time=25 us) 1 INDEX UNIQUE SCAN I_SYN1 (cr=2 pr=0 pw=0 time=15 us)(object id 101) ******************************************************************************** select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#, nvl(typidcol#, 0) from coltype$ where obj#=:1 order by intcol# desc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.01 0.00 0 0 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 4 0.00 0.00 0 12 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.01 0.00 0 12 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=3 pr=0 pw=0 time=31 us) 0 TABLE ACCESS CLUSTER COLTYPE$ (cr=3 pr=0 pw=0 time=20 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=13 us)(object id 3) ******************************************************************************** select intcol#, toid, version#, intcols, intcol#s, flags, synobj# from subcoltype$ where obj#=:1 order by intcol# asc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 4 0.00 0.00 0 12 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.00 0.00 0 12 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=3 pr=0 pw=0 time=30 us) 0 TABLE ACCESS CLUSTER SUBCOLTYPE$ (cr=3 pr=0 pw=0 time=20 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=11 us)(object id 3) ******************************************************************************** select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.01 0.00 0 0 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 4 0.00 0.00 0 4 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.01 0.00 0 4 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID NTAB$ (cr=1 pr=0 pw=0 time=16 us) 0 INDEX RANGE SCAN I_NTAB2 (cr=1 pr=0 pw=0 time=14 us)(object id 200) ******************************************************************************** select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk, l.pctversion$, l.flags, l.property, l.retention, l.freepools from lob$ l where l.obj# = :1 order by l.intcol# asc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 5 0.00 0.00 0 12 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 13 0.00 0.01 0 12 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=3 pr=0 pw=0 time=27 us) 0 TABLE ACCESS CLUSTER LOB$ (cr=3 pr=0 pw=0 time=20 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=11 us)(object id 3) ******************************************************************************** select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order by intcol# asc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.01 0 0 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 4 0.00 0.00 0 12 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.00 0.01 0 12 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=3 pr=0 pw=0 time=33 us) 0 TABLE ACCESS CLUSTER REFCON$ (cr=3 pr=0 pw=0 time=21 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=12 us)(object id 3) ******************************************************************************** select col#,intcol#,charsetid,charsetform from col$ where obj#=:1 order by intcol# asc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 44 0.00 0.00 0 12 0 40 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 52 0.00 0.00 0 12 0 40 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 3 SORT ORDER BY (cr=3 pr=0 pw=0 time=47 us) 3 TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=27 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=12 us)(object id 3) ******************************************************************************** select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum from opqtype$ where obj# = :1 order by intcol# asc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 4 0.00 0.00 0 0 0 0 Fetch 4 0.00 0.00 0 4 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 11 0.00 0.00 0 4 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID OPQTYPE$ (cr=1 pr=0 pw=0 time=23 us) 0 INDEX RANGE SCAN I_OPQTYPE1 (cr=1 pr=0 pw=0 time=20 us)(object id 206) ******************************************************************************** SELECT /*+ ALL_ROWS */ COUNT(*) FROM ALL_POLICIES V WHERE V.OBJECT_OWNER = :B3 AND V.OBJECT_NAME = :B2 AND (POLICY_NAME LIKE '%xdbrls%' OR POLICY_NAME LIKE '%$xd_%') AND V.FUNCTION = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 6 0.05 0.10 0 0 0 0 Fetch 6 0.00 0.02 4 192 0 6 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 13 0.05 0.13 4 192 0 6 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 35 (recursive depth: 2) ******************************************************************************** SELECT count(*) FROM user_policies o WHERE o.object_name = :tablename AND (policy_name LIKE '%xdbrls%' OR policy_name LIKE '%$xd_%') AND o.function= 'CHECKPRIVRLS_SELECTPF' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.01 0.01 0 0 0 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 18 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.01 0.01 0 18 0 3 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 61 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=6 pr=0 pw=0 time=89 us) 0 TABLE ACCESS BY INDEX ROWID RLS$ (cr=6 pr=0 pw=0 time=77 us) 2 NESTED LOOPS (cr=6 pr=0 pw=0 time=66 us) 1 NESTED LOOPS (cr=5 pr=0 pw=0 time=56 us) 1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=28 us) 1 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=22 us)(object id 44) 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=26 us) 1 INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=15 us)(object id 37) 0 INDEX RANGE SCAN I_RLS2 (cr=1 pr=0 pw=0 time=8 us)(object id 440) ******************************************************************************** SELECT count(*) FROM user_policies o WHERE o.object_name = :tablename AND (policy_name LIKE '%xdbrls%' OR policy_name LIKE '%$xd_%') AND o.function= 'CHECKPRIVRLS_SELECTPROPF' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 3 0.01 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 15 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.01 0.01 0 15 0 3 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 61 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=5 pr=0 pw=0 time=84 us) 0 NESTED LOOPS (cr=5 pr=0 pw=0 time=70 us) 0 NESTED LOOPS (cr=5 pr=0 pw=0 time=68 us) 1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=31 us) 1 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=20 us)(object id 44) 0 TABLE ACCESS FULL RLS$ (cr=3 pr=0 pw=0 time=35 us) 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)(object id 36) ******************************************************************************** declare stmt varchar2(200); rdf_exception EXCEPTION; pragma exception_init(rdf_exception, -20000); BEGIN if dictionary_obj_type = 'USER' THEN BEGIN EXECUTE IMMEDIATE 'begin ' || 'mdsys.rdf_apis_internal.' || 'notify_drop_user(''' || dictionary_obj_name || '''); ' || 'end;'; EXCEPTION WHEN rdf_exception THEN RAISE; WHEN OTHERS THEN NULL; END; end if; end; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 2 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 43 (recursive depth: 1) ******************************************************************************** select dummy from dual where ora_dict_obj_type = 'TABLE' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.01 0 0 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 43 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 FILTER (cr=58 pr=11 pw=0 time=100340 us) 0 TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=0 us) ******************************************************************************** select procedure#,procedurename,properties,itypeobj# from procedureinfo$ where obj#=:1 order by procedurename desc, overload# desc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.02 2 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.02 2 4 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID PROCEDUREINFO$ (cr=4 pr=2 pw=0 time=23961 us) 1 INDEX RANGE SCAN DESCENDING I_PROCEDUREINFO1 (cr=3 pr=2 pw=0 time=23948 us)(object id 110) ******************************************************************************** select position#,sequence#,level#,argument,type#,charsetid,charsetform, properties,nvl(length, 0), nvl(precision#, 0),nvl(scale, 0),nvl(radix, 0), type_owner,type_name,type_subname,type_linkname,pls_type from argument$ where obj#=:1 and procedure#=:2 order by sequence# desc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 3 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.01 3 4 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID ARGUMENT$ (cr=4 pr=3 pw=0 time=9599 us) 1 INDEX RANGE SCAN DESCENDING I_ARGUMENT2 (cr=3 pr=2 pw=0 time=1774 us)(object id 112) ******************************************************************************** select max(procedure#) from procedurec$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.01 0.00 2 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.00 2 2 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=2 pr=2 pw=0 time=1412 us) 0 FIRST ROW (cr=2 pr=2 pw=0 time=1398 us) 0 INDEX RANGE SCAN (MIN/MAX) I_PROCEDUREC$ (cr=2 pr=2 pw=0 time=1396 us)(object id 329) ******************************************************************************** select max(procedure#) from procedureplsql$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.01 2 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.02 2 2 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=2 pr=2 pw=0 time=17047 us) 1 FIRST ROW (cr=2 pr=2 pw=0 time=17033 us) 1 INDEX RANGE SCAN (MIN/MAX) I_PROCEDUREPLSQL$ (cr=2 pr=2 pw=0 time=17030 us)(object id 331) ******************************************************************************** select max(procedure#) from procedurejava$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 1 1 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 1 1 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1 pr=1 pw=0 time=7696 us) 0 FIRST ROW (cr=1 pr=1 pw=0 time=7685 us) 0 INDEX RANGE SCAN (MIN/MAX) I_PROCEDUREJAVA$ (cr=1 pr=1 pw=0 time=7683 us)(object id 327) ******************************************************************************** select procedure#,entrypoint# from procedurec$ where obj#=:1 order by procedure# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID PROCEDUREC$ (cr=2 pr=0 pw=0 time=23 us) 0 INDEX RANGE SCAN I_PROCEDUREC$ (cr=2 pr=0 pw=0 time=21 us)(object id 329) ******************************************************************************** select procedure#,entrypoint# from procedureplsql$ where obj#=:1 order by procedure# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 1 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.00 1 4 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID PROCEDUREPLSQL$ (cr=4 pr=1 pw=0 time=797 us) 1 INDEX RANGE SCAN I_PROCEDUREPLSQL$ (cr=3 pr=0 pw=0 time=26 us)(object id 331) ******************************************************************************** select procedure#,ownerlength,classlength,methodlength,siglength, flagslength, cookiesize from procedurejava$ where obj#=:1 order by procedure# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 1 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID PROCEDUREJAVA$ (cr=1 pr=0 pw=0 time=21 us) 0 INDEX RANGE SCAN I_PROCEDUREJAVA$ (cr=1 pr=0 pw=0 time=17 us)(object id 327) ******************************************************************************** select ownername,classname,methodname,signature,flags from procedurejava$ where obj#=:1 and procedure#=:2 order by procedure# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) ******************************************************************************** delete from sys.cache_stats_1$ where dataobj# = :1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.02 1 2 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.03 1 2 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE CACHE_STATS_1$ (cr=1 pr=1 pw=0 time=6830 us) 0 INDEX RANGE SCAN I_CACHE_STATS_1 (cr=1 pr=1 pw=0 time=6827 us)(object id 792) ******************************************************************************** delete from object_usage where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 1 2 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 1 2 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE OBJECT_USAGE (cr=1 pr=1 pw=0 time=706 us) 0 INDEX RANGE SCAN I_STATS_OBJ# (cr=1 pr=1 pw=0 time=704 us)(object id 570) ******************************************************************************** BEGIN aw_drop_proc(ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner) ; END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.01 0.01 0 0 0 0 Execute 2 0.00 0.00 0 0 0 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.01 0 0 0 2 Misses in library cache during parse: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** declare stmt varchar2(200); BEGIN if dictionary_obj_type = 'USER' THEN stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' || ' WHERE ''"''||SDO_OWNER||''"'' = ''"' || dictionary_obj_name || '"'' '; EXECUTE IMMEDIATE stmt; stmt := 'DELETE FROM SDO_MAPS_TABLE ' || ' WHERE ''"''||SDO_OWNER||''"'' = ''"' || dictionary_obj_name || '"'' '; EXECUTE IMMEDIATE stmt; stmt := 'DELETE FROM SDO_STYLES_TABLE ' || ' WHERE ''"''||SDO_OWNER||''"'' = ''"' || dictionary_obj_name || '"'' '; EXECUTE IMMEDIATE stmt; stmt := 'DELETE FROM SDO_THEMES_TABLE ' || ' WHERE ''"''||SDO_OWNER||''"'' = ''"' || dictionary_obj_name || '"'' '; EXECUTE IMMEDIATE stmt; stmt := 'DELETE FROM SDO_LRS_METADATA_TABLE ' || ' WHERE ''"''||SDO_OWNER||''"'' = ''"' || dictionary_obj_name || '"'' '; EXECUTE IMMEDIATE stmt; stmt := 'DELETE FROM SDO_TOPO_METADATA_TABLE ' || ' WHERE ''"''||SDO_OWNER||''"'' = ''"' || dictionary_obj_name || '"'' '; EXECUTE IMMEDIATE stmt; end if; end; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 2 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 43 (recursive depth: 1) ******************************************************************************** delete from idl_ub1$ where obj#=:1 and part=:2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 6 0.00 0.00 0 0 0 0 Execute 6 0.01 0.00 1 12 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.01 0.01 1 12 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE IDL_UB1$ (cr=2 pr=1 pw=0 time=6372 us) 0 INDEX RANGE SCAN I_IDL_UB11 (cr=2 pr=1 pw=0 time=6367 us)(object id 114) ******************************************************************************** delete from idl_char$ where obj#=:1 and part=:2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 6 0.00 0.00 0 0 0 0 Execute 6 0.00 0.00 1 12 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.00 0.01 1 12 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE IDL_CHAR$ (cr=2 pr=1 pw=0 time=885 us) 0 INDEX RANGE SCAN I_IDL_CHAR1 (cr=2 pr=1 pw=0 time=882 us)(object id 115) ******************************************************************************** delete from idl_ub2$ where obj#=:1 and part=:2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 6 0.00 0.00 0 0 0 0 Execute 6 0.01 0.01 1 12 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.01 0.01 1 12 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE IDL_UB2$ (cr=2 pr=1 pw=0 time=9042 us) 0 INDEX RANGE SCAN I_IDL_UB21 (cr=2 pr=1 pw=0 time=9038 us)(object id 116) ******************************************************************************** delete from ncomp_dll$ where obj#=:1 returning dllname into :2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 6 0.00 0.00 0 0 0 0 Execute 6 0.01 0.01 1 6 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.01 0.02 1 6 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE NCOMP_DLL$ (cr=1 pr=1 pw=0 time=7588 us) 0 INDEX RANGE SCAN I_NCOMP_DLL1 (cr=1 pr=1 pw=0 time=7584 us)(object id 730) ******************************************************************************** delete from idl_sb4$ where obj#=:1 and part=:2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 6 0.00 0.00 0 0 0 0 Execute 6 0.00 0.00 1 12 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.00 0.01 1 12 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE IDL_SB4$ (cr=2 pr=0 pw=0 time=13 us) 0 INDEX RANGE SCAN I_IDL_SB41 (cr=2 pr=0 pw=0 time=11 us)(object id 117) ******************************************************************************** delete from objauth$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 4 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 4 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE OBJAUTH$ (cr=2 pr=0 pw=0 time=17 us) 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=14 us)(object id 103) ******************************************************************************** delete from icol$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 4 8 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 4 8 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE ICOL$ (cr=2 pr=0 pw=0 time=288 us) 1 INDEX RANGE SCAN I_ICOL1 (cr=2 pr=0 pw=0 time=22 us)(object id 40) ******************************************************************************** delete from ind$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.01 0.00 0 0 0 0 Execute 2 0.00 0.00 0 4 6 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.00 0 4 6 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 DELETE IND$ (cr=2 pr=0 pw=0 time=161 us) 1 INDEX UNIQUE SCAN I_IND1 (cr=2 pr=0 pw=0 time=14 us)(object id 39) ******************************************************************************** update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize= :9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13), groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1= DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 223 0.00 0.00 0 0 0 0 Execute 223 0.03 0.03 0 1115 241 223 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 446 0.03 0.04 0 1115 241 223 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE SEG$ (cr=5 pr=0 pw=0 time=241 us) 1 TABLE ACCESS CLUSTER SEG$ (cr=5 pr=0 pw=0 time=123 us) 1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=14 us)(object id 9) ******************************************************************************** delete from obj$ where obj# = :1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 4 14 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 4 14 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 DELETE OBJ$ (cr=2 pr=0 pw=0 time=415 us) 1 INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=12 us)(object id 36) ******************************************************************************** update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11, dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null) and(subname=:12 or subname is null and :12 is null) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 2 0 Execute 3 0.01 0.00 0 6 3 3 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.01 0.00 0 6 5 3 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE OBJ$ (cr=2 pr=0 pw=0 time=96 us) 1 INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=23 us)(object id 37) ******************************************************************************** select blocks,maxblocks,grantor#,priv1,priv2,priv3 from tsq$ where ts#=:1 and user#=:2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.01 0 4 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS CLUSTER TSQ$ (cr=2 pr=0 pw=0 time=33 us) 1 INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=14 us)(object id 11) ******************************************************************************** delete from seg$ where ts#=:1 and file#=:2 and block#=:3 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.01 0.00 0 0 0 0 Execute 2 0.00 0.00 0 10 4 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.00 0 10 4 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE SEG$ (cr=5 pr=0 pw=0 time=216 us) 1 TABLE ACCESS CLUSTER SEG$ (cr=5 pr=0 pw=0 time=96 us) 1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=17 us)(object id 9) ******************************************************************************** DROP INDEX tdh.idx_wstore_accttdlink call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.12 0.53 13 1 37302 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.12 0.54 13 1 37302 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 61 ******************************************************************************** truncate table WK_ACCT_WSTORE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.22 6.95 2553 1110 10077 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.23 6.95 2553 1110 10077 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 61 ******************************************************************************** select 1 from sys.cdc_change_tables$ where source_schema_name = :1 and source_table_name = :2 and bitand(mvl_flag, 128)=128 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 1 3 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 1 3 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS FULL CDC_CHANGE_TABLES$ (cr=3 pr=1 pw=0 time=6181 us) ******************************************************************************** select ts#,file#,block#,cols,nvl(size$,-1),pctfree$,pctused$,initrans, maxtrans,hashkeys,func,extind,avgchn,nvl(degree,1),nvl(instances,1), nvl(flags,0),nvl(spare1,0) from clu$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.01 0 3 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS CLUSTER CLU$ (cr=3 pr=0 pw=0 time=45 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=22 us)(object id 3) ******************************************************************************** select log, sysdate, sysdate+1/86400, flag from sys.mlog$ where mowner = :1 and master = :2 for update call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.04 1 1 0 0 Fetch 1 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.05 1 1 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 FOR UPDATE (cr=1 pr=1 pw=0 time=6342 us) 0 TABLE ACCESS CLUSTER MLOG$ (cr=1 pr=1 pw=0 time=6336 us) 0 INDEX UNIQUE SCAN I_MLOG# (cr=1 pr=1 pw=0 time=6330 us)(object id 164) ******************************************************************************** select n.intcol# from ntab$ n, col$ c where n.obj#=:1 and c.obj#=:1 and c.intcol#=n.intcol# and bitand(c.property, 32768)!=32768 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.01 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 30 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.01 0.00 0 30 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS (cr=15 pr=0 pw=0 time=97 us) 10 TABLE ACCESS CLUSTER COL$ (cr=13 pr=0 pw=0 time=71 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=22 us)(object id 3) 0 INDEX UNIQUE SCAN I_NTAB2 (cr=2 pr=0 pw=0 time=19 us)(object id 200) ******************************************************************************** lock table sys.mon_mods$ in exclusive mode nowait call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS FULL MON_MODS$ (cr=0 pr=0 pw=0 time=0 us) ******************************************************************************** update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 1 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE MON_MODS$ (cr=1 pr=0 pw=0 time=10 us) 0 INDEX UNIQUE SCAN I_MON_MODS$_OBJ (cr=1 pr=0 pw=0 time=7 us)(object id 483) ******************************************************************************** insert into sys.mon_mods$ values (:1, :2, :3, :4, :5, :6, :7) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 1 1 5 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.01 1 1 5 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** select o.owner#, u.name, o.name, o.namespace, o.obj#, d.d_timestamp, nvl(d.property,0), o.type#, o.subname, d.d_attrs from dependency$ d, obj$ o, user$ u where d.p_obj#=:1 and (d.p_timestamp=:2 or d.property=2) and d.d_obj#=o.obj# and o.owner#=u.user# order by o.obj# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 1 4 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.01 1 4 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=2 pr=1 pw=0 time=6002 us) 0 NESTED LOOPS (cr=2 pr=1 pw=0 time=5986 us) 0 NESTED LOOPS (cr=2 pr=1 pw=0 time=5985 us) 0 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=2 pr=1 pw=0 time=5984 us) 0 INDEX RANGE SCAN I_DEPENDENCY2 (cr=2 pr=1 pw=0 time=5971 us)(object id 123) 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)(object id 36) 0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us)(object id 11) ******************************************************************************** delete from superobj$ where subobj# = :1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 1 1 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.02 1 1 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE SUPEROBJ$ (cr=1 pr=1 pw=0 time=10846 us) 0 INDEX UNIQUE SCAN I_SUPEROBJ1 (cr=1 pr=1 pw=0 time=10842 us)(object id 66) ******************************************************************************** delete from tab_stats$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 1 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE TAB_STATS$ (cr=1 pr=0 pw=0 time=14 us) 0 INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=1 pr=0 pw=0 time=11 us)(object id 709) ******************************************************************************** update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#= decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9), audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15, rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21, analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1, null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29, flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34, spare6=:35 where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 5 1 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 5 1 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE TAB$ (cr=5 pr=0 pw=0 time=206 us) 1 TABLE ACCESS CLUSTER TAB$ (cr=5 pr=0 pw=0 time=92 us) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=13 us)(object id 3) ******************************************************************************** update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv2=:7,priv3=:8 where ts#=:1 and user#=:2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 218 0.00 0.00 0 0 0 0 Execute 218 0.00 0.03 0 674 228 218 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 436 0.00 0.04 0 674 228 218 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE TSQ$ (cr=3 pr=0 pw=0 time=144 us) 1 TABLE ACCESS CLUSTER TSQ$ (cr=3 pr=0 pw=0 time=44 us) 1 INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=13 us)(object id 11) ******************************************************************************** ALTER SESSION SET SQL_TRACE=FALSE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 61 ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 5 0.04 0.08 0 0 0 0 Execute 6 81.62 135.22 12801 51273 47624 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 11 81.66 135.30 12801 51273 47624 0 Misses in library cache during parse: 5 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 931 0.11 0.35 0 0 2 0 Execute 1671 0.18 0.65 11 1903 510 463 Fetch 2332 0.25 1.66 297 4788 0 3265 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4934 0.54 2.66 308 6691 512 3728 Misses in library cache during parse: 86 Misses in library cache during execute: 79 23 user SQL statements in session. 1645 internal SQL statements in session. 1668 SQL statements in session. ******************************************************************************** Trace file: tdhq_ora_2187314.trc Trace file compatibility: 10.01.00 Sort options: default 1 session in tracefile. 23 user SQL statements in trace file. 1645 internal SQL statements in trace file. 1668 SQL statements in trace file. 93 unique SQL statements in trace file. 16493 lines in trace file. 254 elapsed seconds in trace file.