Hi
We have long running query and from the SQL monitor on line 50 ,we are seeing cardinality mis-estimations
SELECT *
FROM ( SELECT /*+ FIRST_ROWS(1)*/ rnm.*,
ROWNUM rnum
FROM ( SELECT ktb.trade_seq_id FFW$_4,
Fn_get_prud_qsn_response_count( ktb.trade_seq_id ) FFW$_555,
To_char( ktb.trade_dt, 'mm/dd/yyyy' ) FFW$_41,
( SELECT kcst.code_disp_tx
FROM KDD_CODE_SET_TRNLN kcst
WHERE kcst.code_set = 'TradeBlotterStatus' AND
kcst.code_val = ktb.trade_blttr_status_cd ) FFW$_113,
ktb.score_ct FFW$_8,
( CASE
WHEN ktb.score_ct IS NULL OR
ktb.score_ct <= 5 THEN '0'
WHEN ktb.score_ct >= 6 AND
ktb.score_ct <= 49 THEN '1'
WHEN ktb.score_ct > 49 THEN '2'
ELSE ''
END ) FFW$_35,
( SELECT Listagg( kta.attr_type_cd, ',' )
within GROUP (ORDER BY kta.attr_rank_nb)
FROM KDD_TRADE_ATTRIBUTE kta
WHERE kta.trade_seq_id = ktb.trade_seq_id ) FFW$_6,
( SELECT CASE
WHEN Count( ktr.review_id ) = 1 THEN To_number( Listagg( ktr.review_id, ',' )
within GROUP(ORDER BY 1) )
ELSE Count( ktr.review_id )
END
FROM KDD_TRADE_REVIEW ktr
WHERE ktr.trade_seq_id = ktb.trade_seq_id ) FFW$_101,
( SELECT EMP.full_nm
FROM EMP
WHERE ktb.trade_orig_id = EMP.emp_intrl_id ) FFW$_10,
( SELECT CUST.first_nm
||' '
||CUST.last_nm
FROM CUST
WHERE cust_intrl_id = ktb.cust_intrl_id ) FFW$_102,
ktb.last_cust_acct_intrl_id FFW$_18,
( SELECT kcst.code_disp_tx
FROM KDD_CODE_SET_TRNLN kcst
WHERE kcst.code_set = 'ProductCategory' AND
kcst.code_val = ktb.prdct_ctgry_cd ) FFW$_14,
ktb.scrty_shrt_nm FFW$_12,
CASE
WHEN ktb.cust_buy_sell_cd = '1' AND
ktb.tb_exchange = 1 THEN 'Buy Exchange'
WHEN ktb.cust_buy_sell_cd = '2' AND
ktb.tb_exchange = 1 THEN 'Sell Exchange'
WHEN ktb.cust_buy_sell_cd = '1' AND
( ktb.tb_exchange != 1 OR
ktb.tb_exchange IS NULL ) THEN ( SELECT kcst.code_disp_tx
FROM KDD_CODE_SET_TRNLN kcst
WHERE kcst.code_set = 'BuySellCode' AND
kcst.code_val = ktb.cust_buy_sell_cd )
WHEN ktb.cust_buy_sell_cd = '2' AND
( ktb.tb_exchange != 1 OR
ktb.tb_exchange IS NULL ) THEN ( SELECT kcst.code_disp_tx
FROM KDD_CODE_SET_TRNLN kcst
WHERE kcst.code_set = 'BuySellCode' AND
kcst.code_val = ktb.cust_buy_sell_cd )
ELSE ''
END FFW$_20,
ktb.tb_io FFW$_28,
ktb.tb_solcn_fl FFW$_111,
ktb.trade_intrl_id FFW$_30,
( SELECT ACCT.acct_dsply_nm
FROM ACCT
WHERE ACCT.acct_intrl_id = ktb.last_cust_acct_intrl_id ) FFW$_16,
ktb.prod_type_cd FFW$_103,
ktb.prod_sub_type_cd FFW$_104,
'-' FFW$_32,
'-' FFW$_106,
F_get_approver_id( ktb.trade_seq_id ) FFW$_107,
Decode( ktb.trade_blttr_status_cd, 'RVW', To_char ( ktb.status_dt, 'mm/dd/yyyy' ),
NULL ) FFW$_108,
ktb.scrty_cusip_id FFW$_109,
( SELECT scrty_isin_id
FROM business.SCRTY s
WHERE s.scrty_intrl_id = ktb.scrty_intrl_id ) FFW$_110,
ktb.scrty_desc_tx FFW$_36,
ktb.tb_account_class FFW$_42,
ktb.last_trade_unit_qt FFW$_22,
ktb.last_trade_prnpl_issng_am FFW$_26,
ktb.last_trade_issng_pr FFW$_24,
ktb.prdct_ctgry_cd FFW$_2,
ktb.trade_blttr_status_cd FFW$_3,
( SELECT CASE
WHEN Count( ktr.review_id ) = 1 THEN 1
ELSE 2
END
FROM KDD_TRADE_REVIEW ktr
WHERE ktr.trade_seq_id = ktb.trade_seq_id ) FFW$_112,
ktb.req_analysis_fl FFW$_5,
ktb.tb_last_cmsn_issng_am FFW$_37,
To_char( ktb.tb_last_stlmt_dt, 'mm/dd/yyyy' ) FFW$_40,
ktb.tb_solcn_fl FFW$_38,
'-' FFW$_34,
ktb.tb_account_class FFW$_501,
ktb.tb_order_type FFW$_502,
( SELECT EMP.full_nm
FROM EMP
WHERE ktb.trade_orig_id = EMP.emp_intrl_id ) FFW$_503,
ktb.tb_sales_charges FFW$_504,
'-' FFW$_505,
ktb.trade_seq_id FFW$_519,
ktb.mf_share_class FFW$_522,
ktb.tb_account_class_det FFW$_138,
ktb.tb_account_value FFW$_61,
ktb.activity_as_of_date FFW$_140,
Trunc( Months_between( ktb.trade_dt, ktb.tb_age ) / 12, 0 ) FFW$_45,
Decode( ktb.tb_has_comments, 'Y', 'Y',
'N' ) FFW$_64,
ktb.tb_entered_by FFW$_141,
( CASE
WHEN ktb.tb_executiontime IS NOT NULL THEN Substr( ktb.tb_executiontime, 0, 2 )
|| ':'
|| Substr( ktb.tb_executiontime, 3, 2 )
|| ':'
|| Substr( ktb.tb_executiontime, 5, 2 )
END ) FFW$_72,
ktb.tb_lqd_nt_wrth FFW$_43,
ktb.tb_discretionary FFW$_39,
( CASE
WHEN ktb.tb_orderdate IS NOT NULL THEN To_char( ktb.tb_orderdate, 'MM/DD/YYYY' )
END ) FFW$_71,
( CASE
WHEN ktb.tb_ordertime IS NOT NULL THEN Substr( ktb.tb_ordertime, 0, 2 )
|| ':'
|| Substr( ktb.tb_ordertime, 3, 2 )
|| ':'
|| Substr( ktb.tb_ordertime, 5, 2 )
END ) FFW$_73,
ktb.prod_sub_type_cd FFW$_62,
ktb.trade_orig_id FFW$_60,
ktb.tb_ria_discre FFW$_69,
F_get_scnro_name( ktb.trade_seq_id ) FFW$_65,
( SELECT code_disp_tx
FROM KDD_CODE_SET_TRNLN
WHERE code_set = 'TimeHorizon' AND
code_val = ktb.tb_time_horizon ) FFW$_46,
To_char( ktb.tb_trade_ent_dt, 'mm/dd/yyyy' ) FFW$_142,
( SELECT code_disp_tx
FROM KDD_CODE_SET_TRNLN
WHERE code_set = 'TradeExecutionEventType' AND
code_val = ktb.tb_last_event_type_cd ) FFW$_70,
ktb.tb_src_sys_cd FFW$_139
FROM KDD_TRADE_BLOTTER ktb
WHERE EXISTS
( SELECT 'ga0h'
FROM EMP_ORG
WHERE ktb.trade_orig_id = EMP_ORG.emp_intrl_id AND
supvsr_emp_intrl_id IN
( SELECT *
FROM TABLE(F_get_arg_table( Upper( 'ga0h' ) )) ) ) AND
ktb.tb_solcn_fl = 'Y' AND
ktb.trade_dt >= To_date( '12/26/2018', 'MM/DD/YYYY' ) AND
ktb.trade_dt <= To_date( '12/27/2018', 'MM/DD/YYYY' ) AND
ktb.trade_blttr_status_cd IN ( 'RVW' ) AND
EXISTS
( SELECT /*+ cardinality(w 8)*/ 'ANN,COM,CONV,EQT,ETP,FI,FX,FUT,MM,MF,OPT,OTH,PRE,SWAP'
FROM TABLE(F_get_arg_table( 'ANN,COM,CONV,EQT,ETP,FI,FX,FUT,MM,MF,OPT,OTH,PRE,SWAP' )) w
WHERE ktb.prdct_ctgry_cd = w.entity_id ) AND
EXISTS
( SELECT '529,MFALT,N/A,CDs,CEFLD,CEF,COMM,C,EM,ETF,ETN,FA,GA,GRP,LPE,MFLD,M,MFCP,MFNID,NID,OAN,OMF,PVS,RTS,T,UIT,Unit,VUL,WAR'
FROM TABLE(F_get_arg_table( '529,MFALT,N/A,CDs,CEFLD,CEF,COMM,C,EM,ETF,ETN,FA,GA,GRP,LPE,MFLD,M,MFCP,MFNID,NID,OAN,OMF,PVS,RTS,T,UIT,Unit,VUL,WAR' ))
WHERE ktb.prod_type_cd = entity_id ) AND
ktb.tb_account_class IN
( SELECT *
FROM TABLE(F_get_arg_table( 'Brokerage' )) ) AND
ktb.tb_entered_by_suprv IN
( SELECT *
FROM TABLE(F_get_users( 'MATT.CORREIA' )) ) AND
ktb.tb_acct_state IN
( SELECT *
FROM TABLE(F_chck_delg_reg_st( 'MATT.CORREIA', 'N' )) ) AND
Nvl( ktb.prod_type_cd, 'NA' ) NOT IN ( SELECT *
FROM TABLE(F_get_exc_prd( 'MATT.CORREIA' )) ) ) rnm
WHERE ROWNUM <= 1 )
WHERE rnum >= 1
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : MANTAS (2075:23081)
SQL ID : fbdcjcakqw2b4
SQL Execution ID : 16777216
Execution Started : 12/27/2018 11:39:00
First Refresh Time : 12/27/2018 11:40:18
Last Refresh Time : 12/27/2018 20:02:31
Duration : 30211s
Module/Action : JDBC Thin Client/-
Service : MANTPRD
Program : JDBC Thin Client
Global Stats
======================================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | PL/SQL | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes |
======================================================================================================
| 30213 | 29704 | 261 | 0.00 | 75 | 0.12 | 173 | 3G | 211K | 2GB |
======================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3661639513)
================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) |
================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | | | | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | KDD_CODE_SET_TRNLN | 1 | 1 | | | | | | | | | |
| 2 | INDEX UNIQUE SCAN | PK_K_CD_SET_TRNLN | 1 | 1 | | | | | | | | | |
| 3 | SORT GROUP BY | | 1 | | | | | | | | | | |
| 4 | TABLE ACCESS BY GLOBAL INDEX ROWID | KDD_TRADE_ATTRIBUTE | 1 | 2 | | | | | | | | | |
| 5 | INDEX RANGE SCAN | PK_KDD_TRADE_ATTR | 1 | 1 | | | | | | | | | |
| 6 | SORT GROUP BY | | 1 | | | | | | | | | | |
| 7 | INDEX RANGE SCAN | PK_KDD_TRD_REVIEW | 2 | 1 | | | | | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 1 | | | | | | | | | |
| 9 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | | | | | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID | CUST | 1 | 1 | | | | | | | | | |
| 11 | INDEX UNIQUE SCAN | PK_CUST | 1 | 1 | | | | | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | KDD_CODE_SET_TRNLN | 1 | 1 | | | | | | | | | |
| 13 | INDEX UNIQUE SCAN | PK_K_CD_SET_TRNLN | 1 | 1 | | | | | | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID | KDD_CODE_SET_TRNLN | 1 | 1 | | | | | | | | | |
| 15 | INDEX UNIQUE SCAN | PK_K_CD_SET_TRNLN | 1 | 1 | | | | | | | | | |
| 16 | TABLE ACCESS BY INDEX ROWID | KDD_CODE_SET_TRNLN | 1 | 1 | | | | | | | | | |
| 17 | INDEX UNIQUE SCAN | PK_K_CD_SET_TRNLN | 1 | 1 | | | | | | | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | ACCT | 1 | 1 | | | | | | | | | |
| 19 | INDEX UNIQUE SCAN | PK_ACCT | 1 | 1 | | | | | | | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | SCRTY | 1 | 1 | | | | | | | | | |
| 21 | INDEX UNIQUE SCAN | PK_SCRTY | 1 | 1 | | | | | | | | | |
| 22 | SORT AGGREGATE | | 1 | | | | | | | | | | |
| 23 | INDEX RANGE SCAN | PK_KDD_TRD_REVIEW | 2 | 1 | | | | | | | | | |
| 24 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 1 | | | | | | | | | |
| 25 | INDEX UNIQUE SCAN | PK_EMP | 1 | 1 | | | | | | | | | |
| 26 | TABLE ACCESS BY INDEX ROWID | KDD_CODE_SET_TRNLN | 1 | 1 | | | | | | | | | |
| 27 | INDEX UNIQUE SCAN | PK_K_CD_SET_TRNLN | 1 | 1 | | | | | | | | | |
| 28 | TABLE ACCESS BY INDEX ROWID | KDD_CODE_SET_TRNLN | 1 | 1 | | | | | | | | | |
| 29 | INDEX UNIQUE SCAN | PK_K_CD_SET_TRNLN | 1 | 1 | | | | | | | | | |
| 30 | VIEW | | 1 | 852K | | | 1 | | | | | | |
| 31 | COUNT STOPKEY | | | | | | 1 | | | | | | |
| 32 | FILTER | | | | 29464 | +554 | 1 | 0 | | | | | |
| 33 | HASH JOIN | | 2M | 76 | 30098 | +78 | 1 | 275 | | | 421K | | |
| 34 | VIEW | VW_NSO_1 | 8168 | 17 | 1 | +78 | 1 | 1 | | | | | |
| 35 | HASH UNIQUE | | 8168 | 17 | 1 | +78 | 1 | 1 | | | | | |
| 36 | COLLECTION ITERATOR PICKLER FETCH | F_GET_ARG_TABLE | 8168 | 16 | 1 | +78 | 1 | 2 | | | | | |
| 37 | HASH JOIN RIGHT SEMI | | 844 | 53 | 30098 | +78 | 1 | 1990 | | | 1M | | |
| 38 | COLLECTION ITERATOR PICKLER FETCH | F_GET_ARG_TABLE | 8168 | 16 | 1 | +78 | 1 | 58 | | | | | |
| 39 | HASH JOIN | | 844 | 36 | 30098 | +78 | 1 | 1991 | | | 1M | | |
| 40 | VIEW | VW_NSO_3 | 8168 | 17 | 1 | +78 | 1 | 62 | | | | | |
| 41 | HASH UNIQUE | | 8168 | 17 | 1 | +78 | 1 | 62 | | | | | |
| 42 | COLLECTION ITERATOR PICKLER FETCH | F_CHCK_DELG_REG_ST | 8168 | 16 | 1 | +78 | 1 | 126 | | | | | |
| 43 | HASH JOIN RIGHT SEMI | | 2 | 19 | 30098 | +78 | 1 | 1991 | | | 1M | | |
| 44 | COLLECTION ITERATOR PICKLER FETCH | F_GET_ARG_TABLE | 8 | 16 | 1 | +78 | 1 | 28 | | | | | |
| 45 | NESTED LOOPS | | 3 | 3 | 30129 | +78 | 1 | 1994 | | | | 16.36 | Cpu (4720) |
| 46 | NESTED LOOPS | | 14 | 3 | 30138 | +78 | 1 | 4G | | | | | |
| 47 | VIEW | VW_NSO_2 | 8168 | 1 | 30138 | +78 | 1 | 4569 | | | | | |
| 48 | HASH UNIQUE | | 8168 | 17 | 30138 | +78 | 1 | 4569 | | | 10M | | |
| 49 | COLLECTION ITERATOR PICKLER FETCH | F_GET_USERS | 8168 | 16 | 1 | +78 | 1 | 324K | | | | | |
| -> 50 | INDEX RANGE SCAN | IDX_TRADE_BLTTR_STATUS_CD | 14 | 2 | 30140 | +78 | 4570 | 4G | 201K | 2GB | | 37.03 | Cpu (10550) |
| | | | | | | | | | | | | | db file sequential read (132) |
| 51 | TABLE ACCESS BY GLOBAL INDEX ROWID | KDD_TRADE_BLOTTER | 2671 | 12 | 29659 | +554 | 4G | 1994 | 367 | 3MB | | 46.60 | gc cr block 2-way (1) |
| | | | | | | | | | | | | | Cpu (13440) |
| | | | | | | | | | | | | | db file sequential read (2) |
| 52 | NESTED LOOPS | | 2 | 16 | 29464 | +554 | 93 | 0 | | | | | |
| 53 | TABLE ACCESS BY INDEX ROWID | EMP_ORG | 1 | 1 | 29464 | +554 | 93 | 93 | | | | | |
| 54 | INDEX RANGE SCAN | PK_EMP_ORG | 1 | 1 | 29464 | +554 | 93 | 93 | | | | | |
| 55 | COLLECTION ITERATOR PICKLER FETCH | F_GET_ARG_TABLE | 2 | 15 | | | 93 | | | | | | |
| 56 | COLLECTION ITERATOR PICKLER FETCH | F_GET_EXC_PRD