Home » RDBMS Server » Server Administration » TUNE QUERY
| TUNE QUERY [message #60487] |
Tue, 10 February 2004 12:32  |
Dave
Messages: 92
Registered: August 1999
|
Member |
|
|
HI Guys,
Can anyone please help me in tunning this query. Any help will be great. Thanks in advance.
Thanks
Dave
CREATE OR REPLACE PACKAGE prc_lc_s_term_polPkg AS
TYPE RCT1 IS REF CURSOR;
END;
/
CREATE OR REPLACE procedure prc_lc_s_term_pol
(
i_acc_code CHAR,
i_div_code CHAR,
i_close_date CHAR,
i_term_date CHAR,
i_cycle_date CHAR,
i_contract_mask CHAR,
i_batch_id INTEGER,
RC1 IN OUT prc_lc_s_term_polPkg.RCT1
)
AS
t_close_date DATE := TO_DATE(i_close_date,'YYYYMMDD');
t_term_date DATE := TO_DATE(i_term_date,'YYYYMMDD');
t_cycle_date DATE := TO_DATE(i_cycle_date,'YYYYMMDD');
t_proc_date DATE;
------------------------------------------
-- Select all policies that may need
-- fund processing
------------------------------------------
BEGIN
BEGIN
OPEN RC1 FOR
SELECT AL_POL_NUM "LO_POL_NUM"
FROM T_LIAL_ALLOCATION_DETAIL,
T_LIAH_ALLOCATION_HEADER,
T_LIPC_POLICY_COMMON
WHERE AL_ACC_CODE = i_acc_code
AND AL_DIV_CODE = i_div_code
AND AL_POL_NUM = PC_POL_NUM
AND PC_CONT LIKE i_contract_mask
AND ((PC_BATCH_ID = i_batch_id) or (i_batch_id = -999))
AND AH_POL_NUM = AL_POL_NUM
AND AH_END_DATE > t_close_date
AND AL_ALLOC_AMT > 0 -- allocations target fund for closed fund.
UNION
SELECT AL_POL_NUM "LO_POL_NUM"
FROM T_LIAL_ALLOCATION_DETAIL,
T_LIAH_ALLOCATION_HEADER,
T_LIPC_POLICY_COMMON
WHERE AL_ACC_CODE = i_acc_code
AND AL_DIV_CODE = i_div_code
AND AL_POL_NUM = PC_POL_NUM
AND PC_CONT LIKE i_contract_mask
AND ((PC_BATCH_ID = i_batch_id) or (i_batch_id = -999))
AND AH_POL_NUM = AL_POL_NUM
AND AH_END_DATE > t_term_date -- allocations terminated funds
UNION
SELECT TC_POL_NUM "LO_POL_NUM"
FROM T_LITC_TRANSACTION_COMMON,
T_ANTQ_TRANSACTION_REQUEST,
T_LIPC_POLICY_COMMON
WHERE TC_TXN_NUM = TQ_TXN_NUM
AND TC_POL_NUM = PC_POL_NUM
AND PC_CONT LIKE i_contract_mask
AND ((PC_BATCH_ID = i_batch_id) or (i_batch_id = -999))
AND TC_TXN_STATUS <> 'D'
AND TC_TXN_STATUS <> 'U'
AND TQ_ACC_CODE = i_acc_code
AND TQ_DIV_CODE = i_div_code
AND TC_TXN_DATE > t_close_date
AND TQ_TXN_AMT > 0 -- pending txn, closed target fund
UNION
SELECT TC_POL_NUM "LO_POL_NUM"
FROM T_LITC_TRANSACTION_COMMON,
T_ANTQ_TRANSACTION_REQUEST,
T_LIPC_POLICY_COMMON
WHERE TC_TXN_NUM = TQ_TXN_NUM
AND TC_POL_NUM = PC_POL_NUM
AND TC_TXN_STATUS <> 'D'
AND TC_TXN_STATUS <> 'U'
AND PC_CONT LIKE i_contract_mask
AND ((PC_BATCH_ID = i_batch_id) or (i_batch_id = -999))
AND TQ_ACC_CODE = i_acc_code
AND TQ_DIV_CODE = i_div_code
AND TC_TXN_DATE > t_term_date -- pending txn, terminated fund
UNION
SELECT PC_POL_NUM "LO_POL_NUM"
FROM -- Currently in a terminated fund
(
SELECT
PC_POL_NUM, MAX(TT_TXN_TYPE_CODE)
FROM
T_LITD_TRANSACTION_DETAIL,
T_LITC_TRANSACTION_COMMON,
T_LITT_TRANSACTION_TYPE_CODE,
T_LIPC_POLICY_COMMON
WHERE
TC_POL_NUM = PC_POL_NUM and
PC_CONT LIKE i_contract_mask and
((PC_BATCH_ID = i_batch_id) or (i_batch_id = -999)) and
TC_CVG_ID = PC_CVG_ID and
TC_TXN_STATUS = 'D' and
TC_ERR_CODE = 0 and
TC_TXN_DATE =
(
SELECT
MAX( TC_TXN_DATE )
FROM
T_LITC_TRANSACTION_COMMON
WHERE
TC_POL_NUM = PC_POL_NUM and
PC_CONT LIKE i_contract_mask and
((PC_BATCH_ID = i_batch_id) or (i_batch_id = -999)) and
TC_CVG_ID = PC_CVG_ID and
TC_TXN_STATUS ='D' and
TC_ERR_CODE = 0 and
TC_TXN_DATE <= t_cycle_date
)AND
TC_TXN_TYPE = TT_TXN_TYPE AND
TC_TXN_NUM = TD_TXN_NUM AND
TD_ACC_CODE = i_acc_code AND
TD_DIV_CODE = i_div_code
GROUP BY PC_POL_NUM
);
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
END;
|
|
|
|
| Re: TUNE QUERY [message #60508 is a reply to message #60487] |
Wed, 11 February 2004 09:24  |
ilver
Messages: 50
Registered: January 2004
|
Member |
|
|
Hi Dave,
It's hard to advice you, given only the query.
1. You need to give the optimizer the best input to decide on. I.e updated statistics and some ressources.
2. You should then identify what the optimizer decides and evaluate if some of "it's" decisions is against your expantations. -Mayby you need to change your indexing, give it some hint(s). The optimizer is "intelligent" but happen to be caught by general assumptions that can reflect leaks in the architecture etc.
3.
Using explain plan and extentended SQL trace will hopefully give you some detailed information on whats going on and how the query can be tuned.
In fact if you post an explain plan or some extended trace it would be easyer to advice you on this specific issue.
/ilver
|
|
|
|
| Re: TUNE QUERY [message #60513 is a reply to message #60508] |
Wed, 11 February 2004 11:10 |
Dave
Messages: 92
Registered: August 1999
|
Member |
|
|
Hi ilver,
Thanks for reply back I have posted the trace file please look as you request. I have added alias to the tables.
TKPROF: Release 8.1.7.0.0 - Production on Wed Feb 11 15:50:03 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Trace file: V:oracleadminCOSA0731udumpORA01896.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
********************************************************************************
BEGIN prc_lc_s_term_pol ('F1', '01', '20031212', '29991231', '20031215', '%',
-999,:x); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 7
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.00 0.00 0 0 0 7
Misses in library cache during parse: 7
Optimizer goal: CHOOSE
Parsing user id: 19 (DBO)
********************************************************************************
SELECT A.AL_POL_NUM "LO_POL_NUM"
FROM
T_LIAL_ALLOCATION_DETAIL A,T_LIAH_ALLOCATION_HEADER B,T_LIPC_POLICY_COMMON C
WHERE A.AL_ACC_CODE = :b1 AND A.AL_DIV_CODE = :b2 AND A.AL_POL_NUM =
PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID = :b4 ) OR (:b4 =
-999 )) AND B.AH_POL_NUM = A.AL_POL_NUM AND B.AH_END_DATE > :b6 AND
A.AL_ALLOC_AMT > 0 UNION SELECT A.AL_POL_NUM "LO_POL_NUM" FROM
T_LIAL_ALLOCATION_DETAIL A,T_LIAH_ALLOCATION_HEADER B,T_LIPC_POLICY_COMMON
C WHERE A.AL_ACC_CODE = :b1 AND A.AL_DIV_CODE = :b2 AND A.AL_POL_NUM =
PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID = :b4 ) OR (:b4 =
-999 )) AND B.AH_POL_NUM = A.AL_POL_NUM AND B.AH_END_DATE > :b12 UNION
SELECT D.TC_POL_NUM "LO_POL_NUM" FROM T_LITC_TRANSACTION_COMMON D,
T_ANTQ_TRANSACTION_REQUEST E,T_LIPC_POLICY_COMMON C WHERE D.TC_TXN_NUM =
E.TQ_TXN_NUM AND D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT LIKE :b3 AND
((C.PC_BATCH_ID = :b4 ) OR (:b4 = -999 )) AND D.TC_TXN_STATUS != 'D' AND
D.TC_TXN_STATUS != 'U' AND E.TQ_ACC_CODE = :b1 AND E.TQ_DIV_CODE = :b2
AND D.TC_TXN_DATE > :b6 AND E.TQ_TXN_AMT > 0 UNION SELECT D.TC_POL_NUM
"LO_POL_NUM" FROM T_LITC_TRANSACTION_COMMON D,T_ANTQ_TRANSACTION_REQUEST
E,T_LIPC_POLICY_COMMON C WHERE D.TC_TXN_NUM = E.TQ_TXN_NUM AND
D.TC_POL_NUM = C.PC_POL_NUM AND D.TC_TXN_STATUS != 'D' AND
D.TC_TXN_STATUS != 'U' AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID = :b4 )
OR (:b4 = -999 )) AND E.TQ_ACC_CODE = :b1 AND E.TQ_DIV_CODE = :b2 AND
D.TC_TXN_DATE > :b12 UNION SELECT PC_POL_NUM "LO_POL_NUM" FROM (SELECT
C.PC_POL_NUM,MAX(G.TT_TXN_TYPE_CODE) FROM T_LITD_TRANSACTION_DETAIL F,
T_LITC_TRANSACTION_COMMON D,T_LITT_TRANSACTION_TYPE_CODE G,
T_LIPC_POLICY_COMMON C WHERE D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT
LIKE :b3 AND ((C.PC_BATCH_ID = :b4 ) OR (:b4 = -999 )) AND D.TC_CVG_ID =
C.PC_CVG_ID AND D.TC_TXN_STATUS = 'D' AND D.TC_ERR_CODE = 0 AND
D.TC_TXN_DATE = (SELECT MAX(D.TC_TXN_DATE) FROM
T_LITC_TRANSACTION_COMMON D,T_LIPC_POLICY_COMMON C,
T_LITT_TRANSACTION_TYPE_CODE G,T_LITD_TRANSACTION_DETAIL F WHERE
D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID =
:b4 ) OR (:b4 = -999 )) AND D.TC_CVG_ID = C.PC_CVG_ID AND D.TC_TXN_STATUS =
'D' AND D.TC_ERR_CODE = 0 AND D.TC_TXN_DATE <= :b31 ) AND D.TC_TXN_TYPE
= G.TT_TXN_TYPE AND D.TC_TXN_NUM = F.TD_TXN_NUM AND F.TD_ACC_CODE = :b1
AND F.TD_DIV_CODE = :b2 GROUP BY PC_POL_NUM )
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 126 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 126 0 0
Misses in library cache during parse: 2
Optimizer goal: CHOOSE
Parsing user id: 19 (DBO) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT UNIQUE
0 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LIAL_ALLOCATION_DETAIL
0 INDEX RANGE SCAN (object id 4715)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4870)
0 TABLE ACCESS BY INDEX ROWID T_LIAH_ALLOCATION_HEADER
0 INDEX RANGE SCAN (object id 3947)
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LIAL_ALLOCATION_DETAIL
0 INDEX RANGE SCAN (object id 4715)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4870)
0 TABLE ACCESS BY INDEX ROWID T_LIAH_ALLOCATION_HEADER
0 INDEX RANGE SCAN (object id 3947)
0 HASH JOIN
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_ANTQ_TRANSACTION_REQUEST
0 INDEX RANGE SCAN (object id 4421)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX UNIQUE SCAN (object id 4892)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4872)
0 HASH JOIN
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4872)
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_ANTQ_TRANSACTION_REQUEST
0 INDEX RANGE SCAN (object id 4421)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX UNIQUE SCAN (object id 4892)
0 VIEW
0 SORT GROUP BY
0 FILTER
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LITD_TRANSACTION_DETAIL
0 INDEX RANGE SCAN (object id 3750)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX UNIQUE SCAN (object id 4892)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX UNIQUE SCAN (object id 4870)
0 TABLE ACCESS BY INDEX ROWID T_LITT_TRANSACTION_TYPE_CODE
0 INDEX UNIQUE SCAN (object id 4023)
0 SORT AGGREGATE
0 MERGE JOIN CARTESIAN
0 MERGE JOIN CARTESIAN
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4872)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX RANGE SCAN (object id 4893)
0 SORT JOIN
0 FIRST ROW
0 INDEX FULL SCAN (MIN/MAX) (object id 4023)
0 SORT JOIN
0 INDEX FAST FULL SCAN (object id 3750)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (UNIQUE)
0 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAL_ALLOCATION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIAL_ALLOCATION_DETAIL_RI2' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIPC_POLICY_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAH_ALLOCATION_HEADER'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIAH_ALLOCATION_HEADER' (UNIQUE)
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAL_ALLOCATION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIAL_ALLOCATION_DETAIL_RI2' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIPC_POLICY_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAH_ALLOCATION_HEADER'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIAH_ALLOCATION_HEADER' (UNIQUE)
0 HASH JOIN
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_ANTQ_TRANSACTION_REQUEST'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_ANTQ_TRANS_REQUEST_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_ANTQ_TRANSACTION_REQUEST'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_ANTQ_TRANS_REQUEST_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 VIEW
0 SORT (GROUP BY)
0 FILTER
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITD_TRANSACTION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LITD_TRANSACTION_DETAIL_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LIPC_POLICY_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITT_TRANSACTION_TYPE_CODE'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITT_TRANSACTION_TYPE_COD' (UNIQUE)
0 SORT (AGGREGATE)
0 MERGE JOIN (CARTESIAN)
0 MERGE JOIN (CARTESIAN)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LITC_TRANSACTION_COMMON_IDX4' (NON-UNIQUE)
0 SORT (JOIN)
0 FIRST ROW
0 INDEX GOAL: ANALYZED (FULL SCAN (MIN/MAX))
OF 'PK_T_LITT_TRANSACTION_TYPE_COD' (UNIQUE)
0 SORT (JOIN)
0 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'T_LITD_TRANSACTION_DETAIL_RI1' (NON-UNIQUE)
********************************************************************************
SELECT A.AL_POL_NUM "LO_POL_NUM"
FROM
T_LIAL_ALLOCATION_DETAIL A,T_LIAH_ALLOCATION_HEADER B,T_LIPC_POLICY_COMMON C
WHERE A.AL_ACC_CODE = :b1 AND A.AL_DIV_CODE = :b2 AND A.AL_POL_NUM =
PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID = :b4 ) OR (:b4 =
-999 )) AND B.AH_POL_NUM = A.AL_POL_NUM AND (B.AH_END_DATE > :b6 AND
B.AH_END_DATE > :b7 ) AND A.AL_ALLOC_AMT > 0 UNION SELECT D.TC_POL_NUM
"LO_POL_NUM" FROM T_LITC_TRANSACTION_COMMON D,T_ANTQ_TRANSACTION_REQUEST
E,T_LIPC_POLICY_COMMON C WHERE D.TC_TXN_NUM = E.TQ_TXN_NUM AND
D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID =
:b4 ) OR (:b4 = -999 )) AND (D.TC_TXN_STATUS != 'D' AND D.TC_TXN_STATUS !=
'U' ) AND E.TQ_ACC_CODE = :b1 AND E.TQ_DIV_CODE = :b2 AND (D.TC_TXN_DATE
> :b6 AND D.TC_TXN_DATE > :b7 ) AND E.TQ_TXN_AMT > 0 UNION SELECT
PC_POL_NUM "LO_POL_NUM" FROM (SELECT C.PC_POL_NUM,MAX(G.TT_TXN_TYPE_CODE),
MAX(D.TC_TXN_DATE) FROM T_LITD_TRANSACTION_DETAIL F,
T_LITC_TRANSACTION_COMMON D,T_LITT_TRANSACTION_TYPE_CODE G,
T_LIPC_POLICY_COMMON C WHERE D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT
LIKE :b3 AND ((C.PC_BATCH_ID = :b4 ) OR (:b4 = -999 )) AND D.TC_CVG_ID =
C.PC_CVG_ID AND D.TC_TXN_STATUS = 'D' AND D.TC_ERR_CODE = 0 AND
D.TC_TXN_DATE <= :b18 AND D.TC_TXN_TYPE = G.TT_TXN_TYPE AND D.TC_TXN_NUM =
F.TD_TXN_NUM AND F.TD_ACC_CODE = :b1 AND F.TD_DIV_CODE = :b2 GROUP BY
PC_POL_NUM )
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 goal: CHOOSE
Parsing user id: 19 (DBO) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT UNIQUE
0 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LIAL_ALLOCATION_DETAIL
0 INDEX RANGE SCAN (object id 4715)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4870)
0 TABLE ACCESS BY INDEX ROWID T_LIAH_ALLOCATION_HEADER
0 INDEX RANGE SCAN (object id 3947)
0 HASH JOIN
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_ANTQ_TRANSACTION_REQUEST
0 INDEX RANGE SCAN (object id 4421)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX UNIQUE SCAN (object id 4892)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4872)
0 VIEW
0 SORT GROUP BY
0 NESTED LOOPS
0 HASH JOIN
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4872)
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LITD_TRANSACTION_DETAIL
0 INDEX RANGE SCAN (object id 3750)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX UNIQUE SCAN (object id 4892)
0 TABLE ACCESS BY INDEX ROWID T_LITT_TRANSACTION_TYPE_CODE
0 INDEX UNIQUE SCAN (object id 4023)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (UNIQUE)
0 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAL_ALLOCATION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIAL_ALLOCATION_DETAIL_RI2' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIPC_POLICY_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAH_ALLOCATION_HEADER'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIAH_ALLOCATION_HEADER' (UNIQUE)
0 HASH JOIN
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_ANTQ_TRANSACTION_REQUEST'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_ANTQ_TRANS_REQUEST_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 VIEW
0 SORT (GROUP BY)
0 NESTED LOOPS
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITD_TRANSACTION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LITD_TRANSACTION_DETAIL_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITT_TRANSACTION_TYPE_CODE'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITT_TRANSACTION_TYPE_COD' (UNIQUE)
********************************************************************************
SELECT A.AL_POL_NUM "LO_POL_NUM"
FROM
T_LIAL_ALLOCATION_DETAIL A,T_LIAH_ALLOCATION_HEADER B,T_LIPC_POLICY_COMMON C
WHERE A.AL_ACC_CODE = :b1 AND A.AL_DIV_CODE = :b2 AND A.AL_POL_NUM =
PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID = :b4 ) OR (:b4 =
-999 )) AND B.AH_POL_NUM = A.AL_POL_NUM AND (B.AH_END_DATE > :b6 AND
B.AH_END_DATE > :b7 ) AND A.AL_ALLOC_AMT > 0 UNION SELECT D.TC_POL_NUM
"LO_POL_NUM" FROM T_LITC_TRANSACTION_COMMON D,T_ANTQ_TRANSACTION_REQUEST
E,T_LIPC_POLICY_COMMON C WHERE D.TC_TXN_NUM = E.TQ_TXN_NUM AND
D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID =
:b4 ) OR (:b4 = -999 )) AND (D.TC_TXN_STATUS != 'D' AND D.TC_TXN_STATUS !=
'U' ) AND E.TQ_ACC_CODE = :b1 AND E.TQ_DIV_CODE = :b2 AND (D.TC_TXN_DATE
> :b6 AND D.TC_TXN_DATE > :b7 ) AND E.TQ_TXN_AMT > 0 UNION SELECT
PC_POL_NUM "LO_POL_NUM" FROM (SELECT C.PC_POL_NUM,MAX(G.TT_TXN_TYPE_CODE),
MAX(D.TC_TXN_DATE) FROM T_LITD_TRANSACTION_DETAIL F,
T_LITC_TRANSACTION_COMMON D,T_LITT_TRANSACTION_TYPE_CODE G,
T_LIPC_POLICY_COMMON C WHERE D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT
LIKE :b3 AND ((C.PC_BATCH_ID = :b4 ) OR (:b4 = -999 )) AND D.TC_CVG_ID =
C.PC_CVG_ID AND D.TC_TXN_STATUS = 'D' AND D.TC_ERR_CODE = 0 AND
D.TC_TXN_DATE = (SELECT MAX(D.TC_TXN_DATE) FROM
T_LITC_TRANSACTION_COMMON D,T_LIPC_POLICY_COMMON C,
T_LITT_TRANSACTION_TYPE_CODE G,T_LITD_TRANSACTION_DETAIL F WHERE
D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID =
:b4 ) OR (:b4 = -999 )) AND D.TC_CVG_ID = C.PC_CVG_ID AND D.TC_TXN_STATUS =
'D' AND D.TC_ERR_CODE = 0 AND D.TC_TXN_DATE <= :b21 ) AND D.TC_TXN_TYPE
= G.TT_TXN_TYPE AND D.TC_TXN_NUM = F.TD_TXN_NUM AND F.TD_ACC_CODE = :b1
AND F.TD_DIV_CODE = :b2 GROUP BY PC_POL_NUM )
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 goal: CHOOSE
Parsing user id: 19 (DBO) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT UNIQUE
0 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LIAL_ALLOCATION_DETAIL
0 INDEX RANGE SCAN (object id 4715)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4870)
0 TABLE ACCESS BY INDEX ROWID T_LIAH_ALLOCATION_HEADER
0 INDEX RANGE SCAN (object id 3947)
0 HASH JOIN
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_ANTQ_TRANSACTION_REQUEST
0 INDEX RANGE SCAN (object id 4421)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX UNIQUE SCAN (object id 4892)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4872)
0 VIEW
0 SORT GROUP BY
0 FILTER
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LITD_TRANSACTION_DETAIL
0 INDEX RANGE SCAN (object id 3750)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX UNIQUE SCAN (object id 4892)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX UNIQUE SCAN (object id 4870)
0 TABLE ACCESS BY INDEX ROWID T_LITT_TRANSACTION_TYPE_CODE
0 INDEX UNIQUE SCAN (object id 4023)
0 SORT AGGREGATE
0 MERGE JOIN CARTESIAN
0 MERGE JOIN CARTESIAN
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4872)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX RANGE SCAN (object id 4893)
0 SORT JOIN
0 FIRST ROW
0 INDEX FULL SCAN (MIN/MAX) (object id 4023)
0 SORT JOIN
0 INDEX FAST FULL SCAN (object id 3750)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (UNIQUE)
0 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAL_ALLOCATION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIAL_ALLOCATION_DETAIL_RI2' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIPC_POLICY_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAH_ALLOCATION_HEADER'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIAH_ALLOCATION_HEADER' (UNIQUE)
0 HASH JOIN
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_ANTQ_TRANSACTION_REQUEST'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_ANTQ_TRANS_REQUEST_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 VIEW
0 SORT (GROUP BY)
0 FILTER
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITD_TRANSACTION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LITD_TRANSACTION_DETAIL_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LIPC_POLICY_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITT_TRANSACTION_TYPE_CODE'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITT_TRANSACTION_TYPE_COD' (UNIQUE)
0 SORT (AGGREGATE)
0 MERGE JOIN (CARTESIAN)
0 MERGE JOIN (CARTESIAN)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LITC_TRANSACTION_COMMON_IDX4' (NON-UNIQUE)
0 SORT (JOIN)
0 FIRST ROW
0 INDEX GOAL: ANALYZED (FULL SCAN (MIN/MAX))
OF 'PK_T_LITT_TRANSACTION_TYPE_COD' (UNIQUE)
0 SORT (JOIN)
0 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'T_LITD_TRANSACTION_DETAIL_RI1' (NON-UNIQUE)
********************************************************************************
SELECT A.AL_POL_NUM "LO_POL_NUM"
FROM
T_LIAL_ALLOCATION_DETAIL A,T_LIAH_ALLOCATION_HEADER B,T_LIPC_POLICY_COMMON C
WHERE A.AL_ACC_CODE = :b1 AND A.AL_DIV_CODE = :b2 AND A.AL_POL_NUM =
PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID = :b4 ) OR (:b4 =
-999 )) AND B.AH_POL_NUM = A.AL_POL_NUM AND (B.AH_END_DATE > :b6 AND
B.AH_END_DATE > :b7 ) AND A.AL_ALLOC_AMT > 0 UNION SELECT D.TC_POL_NUM
"LO_POL_NUM" FROM T_LITC_TRANSACTION_COMMON D,T_ANTQ_TRANSACTION_REQUEST
E,T_LIPC_POLICY_COMMON C WHERE D.TC_TXN_NUM = E.TQ_TXN_NUM AND
D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID =
:b4 ) OR (:b4 = -999 )) AND D.TC_TXN_STATUS != 'D' AND D.TC_TXN_STATUS !=
'U' AND E.TQ_ACC_CODE = :b1 AND E.TQ_DIV_CODE = :b2 AND (D.TC_TXN_DATE >
:b6 AND D.TC_TXN_DATE > :b7 ) AND E.TQ_TXN_AMT > 0 UNION SELECT
PC_POL_NUM "LO_POL_NUM" FROM (SELECT C.PC_POL_NUM,MAX(G.TT_TXN_TYPE_CODE),
MAX(D.TC_TXN_DATE) FROM T_LITD_TRANSACTION_DETAIL F,
T_LITC_TRANSACTION_COMMON D,T_LITT_TRANSACTION_TYPE_CODE G,
T_LIPC_POLICY_COMMON C WHERE D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT
LIKE :b3 AND ((C.PC_BATCH_ID = :b4 ) OR (:b4 = -999 )) AND D.TC_CVG_ID =
C.PC_CVG_ID AND D.TC_TXN_STATUS = 'D' AND D.TC_ERR_CODE = 0 AND
D.TC_TXN_DATE = (SELECT MAX(D.TC_TXN_DATE) FROM
T_LITC_TRANSACTION_COMMON D,T_LIPC_POLICY_COMMON C,
T_LITT_TRANSACTION_TYPE_CODE G,T_LITD_TRANSACTION_DETAIL F WHERE
D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID =
:b4 ) OR (:b4 = -999 )) AND D.TC_CVG_ID = C.PC_CVG_ID AND D.TC_TXN_STATUS =
'D' AND D.TC_ERR_CODE = 0 AND D.TC_TXN_DATE <= :b21 ) AND D.TC_TXN_TYPE
= G.TT_TXN_TYPE AND D.TC_TXN_NUM = F.TD_TXN_NUM AND F.TD_ACC_CODE = :b1
AND F.TD_DIV_CODE = :b2 GROUP BY PC_POL_NUM )
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 goal: CHOOSE
Parsing user id: 19 (DBO) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT UNIQUE
0 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LIAL_ALLOCATION_DETAIL
0 INDEX RANGE SCAN (object id 4715)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4870)
0 TABLE ACCESS BY INDEX ROWID T_LIAH_ALLOCATION_HEADER
0 INDEX RANGE SCAN (object id 3947)
0 HASH JOIN
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_ANTQ_TRANSACTION_REQUEST
0 INDEX RANGE SCAN (object id 4421)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX UNIQUE SCAN (object id 4892)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4872)
0 VIEW
0 SORT GROUP BY
0 FILTER
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LITD_TRANSACTION_DETAIL
0 INDEX RANGE SCAN (object id 3750)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX UNIQUE SCAN (object id 4892)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX UNIQUE SCAN (object id 4870)
0 TABLE ACCESS BY INDEX ROWID T_LITT_TRANSACTION_TYPE_CODE
0 INDEX UNIQUE SCAN (object id 4023)
0 SORT AGGREGATE
0 MERGE JOIN CARTESIAN
0 MERGE JOIN CARTESIAN
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4872)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX RANGE SCAN (object id 4893)
0 SORT JOIN
0 FIRST ROW
0 INDEX FULL SCAN (MIN/MAX) (object id 4023)
0 SORT JOIN
0 INDEX FAST FULL SCAN (object id 3750)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (UNIQUE)
0 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAL_ALLOCATION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIAL_ALLOCATION_DETAIL_RI2' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIPC_POLICY_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAH_ALLOCATION_HEADER'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIAH_ALLOCATION_HEADER' (UNIQUE)
0 HASH JOIN
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_ANTQ_TRANSACTION_REQUEST'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_ANTQ_TRANS_REQUEST_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 VIEW
0 SORT (GROUP BY)
0 FILTER
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITD_TRANSACTION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LITD_TRANSACTION_DETAIL_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LIPC_POLICY_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITT_TRANSACTION_TYPE_CODE'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITT_TRANSACTION_TYPE_COD' (UNIQUE)
0 SORT (AGGREGATE)
0 MERGE JOIN (CARTESIAN)
0 MERGE JOIN (CARTESIAN)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LITC_TRANSACTION_COMMON_IDX4' (NON-UNIQUE)
0 SORT (JOIN)
0 FIRST ROW
0 INDEX GOAL: ANALYZED (FULL SCAN (MIN/MAX))
OF 'PK_T_LITT_TRANSACTION_TYPE_COD' (UNIQUE)
0 SORT (JOIN)
0 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'T_LITD_TRANSACTION_DETAIL_RI1' (NON-UNIQUE)
********************************************************************************
SELECT AL_POL_NUM "LO_POL_NUM"
FROM
T_LIAL_ALLOCATION_DETAIL A,T_LIAH_ALLOCATION_HEADER B,T_LIPC_POLICY_COMMON C
WHERE AL_ACC_CODE = :b1 AND AL_DIV_CODE = :b2 AND AL_POL_NUM =
PC_POL_NUM AND PC_CONT LIKE :b3 AND ((PC_BATCH_ID = :b4 ) OR (:b4 = -999
)) AND AH_POL_NUM = AL_POL_NUM AND AH_END_DATE > :b6 AND AL_ALLOC_AMT > 0
UNION SELECT AL_POL_NUM "LO_POL_NUM" FROM T_LIAL_ALLOCATION_DETAIL,
T_LIAH_ALLOCATION_HEADER,T_LIPC_POLICY_COMMON WHERE AL_ACC_CODE = :b1 AND
AL_DIV_CODE = :b2 AND AL_POL_NUM = PC_POL_NUM AND PC_CONT LIKE :b3 AND
((PC_BATCH_ID = :b4 ) OR (:b4 = -999 )) AND AH_POL_NUM = AL_POL_NUM AND
AH_END_DATE > :b12 UNION SELECT TC_POL_NUM "LO_POL_NUM" FROM
T_LITC_TRANSACTION_COMMON,T_ANTQ_TRANSACTION_REQUEST,T_LIPC_POLICY_COMMON
WHERE TC_TXN_NUM = TQ_TXN_NUM AND TC_POL_NUM = PC_POL_NUM AND PC_CONT
LIKE :b3 AND ((PC_BATCH_ID = :b4 ) OR (:b4 = -999 )) AND TC_TXN_STATUS !=
'D' AND TC_TXN_STATUS != 'U' AND TQ_ACC_CODE = :b1 AND TQ_DIV_CODE = :b2
AND TC_TXN_DATE > :b6 AND TQ_TXN_AMT > 0 UNION SELECT TC_POL_NUM
"LO_POL_NUM" FROM T_LITC_TRANSACTION_COMMON,T_ANTQ_TRANSACTION_REQUEST,
T_LIPC_POLICY_COMMON WHERE TC_TXN_NUM = TQ_TXN_NUM AND TC_POL_NUM =
PC_POL_NUM AND TC_TXN_STATUS != 'D' AND TC_TXN_STATUS != 'U' AND PC_CONT
LIKE :b3 AND ((PC_BATCH_ID = :b4 ) OR (:b4 = -999 )) AND TQ_ACC_CODE =
:b1 AND TQ_DIV_CODE = :b2 AND TC_TXN_DATE > :b12 UNION SELECT PC_POL_NUM
"LO_POL_NUM" FROM (SELECT PC_POL_NUM,MAX(TT_TXN_TYPE_CODE) FROM
T_LITD_TRANSACTION_DETAIL,T_LITC_TRANSACTION_COMMON,
T_LITT_TRANSACTION_TYPE_CODE,T_LIPC_POLICY_COMMON WHERE TC_POL_NUM =
PC_POL_NUM AND PC_CONT LIKE :b3 AND ((PC_BATCH_ID = :b4 ) OR (:b4 = -999
)) AND TC_CVG_ID = PC_CVG_ID AND TC_TXN_STATUS = 'D' AND TC_ERR_CODE = 0
AND TC_TXN_DATE = (SELECT MAX(TC_TXN_DATE) FROM
T_LITC_TRANSACTION_COMMON WHERE TC_POL_NUM = PC_POL_NUM AND PC_CONT LIKE
:b3 AND ((PC_BATCH_ID = :b4 ) OR (:b4 = -999 )) AND TC_CVG_ID = PC_CVG_ID
AND TC_TXN_STATUS = 'D' AND TC_ERR_CODE = 0 AND TC_TXN_DATE <= :b31 )
AND TC_TXN_TYPE = TT_TXN_TYPE AND TC_TXN_NUM = TD_TXN_NUM AND TD_ACC_CODE
= :b1 AND TD_DIV_CODE = :b2 GROUP BY PC_POL_NUM )
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 120 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 120 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 19 (DBO) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT UNIQUE
0 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LIAL_ALLOCATION_DETAIL
0 INDEX RANGE SCAN (object id 4715)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4870)
0 TABLE ACCESS BY INDEX ROWID T_LIAH_ALLOCATION_HEADER
0 INDEX RANGE SCAN (object id 3947)
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LIAL_ALLOCATION_DETAIL
0 INDEX RANGE SCAN (object id 4715)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4870)
0 TABLE ACCESS BY INDEX ROWID T_LIAH_ALLOCATION_HEADER
0 INDEX RANGE SCAN (object id 3947)
0 HASH JOIN
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_ANTQ_TRANSACTION_REQUEST
0 INDEX RANGE SCAN (object id 4421)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX UNIQUE SCAN (object id 4892)
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4872)
0 HASH JOIN
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4872)
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_ANTQ_TRANSACTION_REQUEST
0 INDEX RANGE SCAN (object id 4421)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX UNIQUE SCAN (object id 4892)
0 VIEW
0 SORT GROUP BY
0 FILTER
0 NESTED LOOPS
0 HASH JOIN
0 TABLE ACCESS BY INDEX ROWID T_LIPC_POLICY_COMMON
0 INDEX RANGE SCAN (object id 4872)
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID T_LITD_TRANSACTION_DETAIL
0 INDEX RANGE SCAN (object id 3750)
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX UNIQUE SCAN (object id 4892)
0 TABLE ACCESS BY INDEX ROWID T_LITT_TRANSACTION_TYPE_CODE
0 INDEX UNIQUE SCAN (object id 4023)
0 SORT AGGREGATE
0 FILTER
0 TABLE ACCESS BY INDEX ROWID T_LITC_TRANSACTION_COMMON
0 INDEX RANGE SCAN (object id 4893)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (UNIQUE)
0 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAL_ALLOCATION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIAL_ALLOCATION_DETAIL_RI2' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIPC_POLICY_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAH_ALLOCATION_HEADER'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIAH_ALLOCATION_HEADER' (UNIQUE)
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAL_ALLOCATION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIAL_ALLOCATION_DETAIL_RI2' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIPC_POLICY_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAH_ALLOCATION_HEADER'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIAH_ALLOCATION_HEADER' (UNIQUE)
0 HASH JOIN
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_ANTQ_TRANSACTION_REQUEST'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_ANTQ_TRANS_REQUEST_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_ANTQ_TRANSACTION_REQUEST'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_ANTQ_TRANS_REQUEST_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 VIEW
0 SORT (GROUP BY)
0 FILTER
0 NESTED LOOPS
0 HASH JOIN
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITD_TRANSACTION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LITD_TRANSACTION_DETAIL_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITT_TRANSACTION_TYPE_CODE'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITT_TRANSACTION_TYPE_COD' (UNIQUE)
0 SORT (AGGREGATE)
0 FILTER
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LITC_TRANSACTION_COMMON_IDX4' (NON-UNIQUE)
********************************************************************************
SELECT A.AL_POL_NUM "LO_POL_NUM"
FROM
T_LIAL_ALLOCATION_DETAIL A,T_LIAH_ALLOCATION_HEADER B,T_LIPC_POLICY_COMMON C
WHERE A.AL_ACC_CODE = :b1 AND A.AL_DIV_CODE = :b2 AND A.AL_POL_NUM =
PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID = :b4 ) OR (:b4 =
-999 )) AND B.AH_POL_NUM = A.AL_POL_NUM AND (B.AH_END_DATE > :b6 OR
B.AH_END_DATE > :b7 ) AND A.AL_ALLOC_AMT > 0 UNION SELECT D.TC_POL_NUM
"LO_POL_NUM" FROM T_LITC_TRANSACTION_COMMON D,T_ANTQ_TRANSACTION_REQUEST
E,T_LIPC_POLICY_COMMON C WHERE D.TC_TXN_NUM = E.TQ_TXN_NUM AND
D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID =
:b4 ) OR (:b4 = -999 )) AND D.TC_TXN_STATUS != 'D' AND D.TC_TXN_STATUS !=
'U' AND E.TQ_ACC_CODE = :b1 AND E.TQ_DIV_CODE = :b2 AND (D.TC_TXN_DATE >
:b6 OR D.TC_TXN_DATE > :b7 ) AND E.TQ_TXN_AMT > 0 UNION SELECT PC_POL_NUM
"LO_POL_NUM" FROM (SELECT C.PC_POL_NUM,MAX(G.TT_TXN_TYPE_CODE),
MAX(D.TC_TXN_DATE) FROM T_LITD_TRANSACTION_DETAIL F,
T_LITC_TRANSACTION_COMMON D,T_LITT_TRANSACTION_TYPE_CODE G,
T_LIPC_POLICY_COMMON C WHERE D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT
LIKE :b3 AND ((C.PC_BATCH_ID = :b4 ) OR (:b4 = -999 )) AND D.TC_CVG_ID =
C.PC_CVG_ID AND D.TC_TXN_STATUS = 'D' AND D.TC_ERR_CODE = 0 AND
D.TC_TXN_DATE = (SELECT MAX(D.TC_TXN_DATE) FROM
T_LITC_TRANSACTION_COMMON D,T_LIPC_POLICY_COMMON C,
T_LITT_TRANSACTION_TYPE_CODE G,T_LITD_TRANSACTION_DETAIL F WHERE
D.TC_POL_NUM = C.PC_POL_NUM AND C.PC_CONT LIKE :b3 AND ((C.PC_BATCH_ID =
:b4 ) OR (:b4 = -999 )) AND D.TC_CVG_ID = C.PC_CVG_ID AND D.TC_TXN_STATUS =
'D' AND D.TC_ERR_CODE = 0 AND D.TC_TXN_DATE <= :b21 ) AND D.TC_TXN_TYPE
= G.TT_TXN_TYPE AND D.TC_TXN_NUM = F.TD_TXN_NUM AND F.TD_ACC_CODE = :b1
AND F.TD_DIV_CODE = :b2 GROUP BY PC_POL_NUM )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 6 78 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 6 78 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 19 (DBO) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (UNIQUE)
0 UNION-ALL
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAL_ALLOCATION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIAL_ALLOCATION_DETAIL_RI2' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIPC_POLICY_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIAH_ALLOCATION_HEADER'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PK_T_LIAH_ALLOCATION_HEADER' (UNIQUE)
0 HASH JOIN
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_ANTQ_TRANSACTION_REQUEST'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_ANTQ_TRANS_REQUEST_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 VIEW
0 SORT (GROUP BY)
0 FILTER
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITD_TRANSACTION_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LITD_TRANSACTION_DETAIL_RI1' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITC_TRANSACTION_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LIPC_POLICY_COMMON' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'T_LITT_TRANSACTION_TYPE_CODE'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_T_LITT_TRANSACTION_TYPE_COD' (UNIQUE)
0 SORT (AGGREGATE)
0 MERGE JOIN (CARTESIAN)
0 MERGE JOIN (CARTESIAN)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LIPC_POLICY_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LIPC_POLICY_COMMON_IDX3' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'T_LITC_TRANSACTION_COMMON'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'T_LITC_TRANSACTION_COMMON_IDX4' (NON-UNIQUE)
0 SORT (JOIN)
0 FIRST ROW
0 INDEX GOAL: ANALYZED (FULL SCAN (MIN/MAX))
OF 'PK_T_LITT_TRANSACTION_TYPE_COD' (UNIQUE)
0 SORT (JOIN)
0 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'T_LITD_TRANSACTION_DETAIL_RI1' (NON-UNIQUE)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 7
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.00 0.00 0 0 0 7
Misses in library cache during parse: 7
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 485 0.00 0.00 4 108 3 0
Execute 1567 0.00 0.00 6 324 0 0
Fetch 5887 0.00 0.00 36 8136 0 5041
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7939 0.00 0.00 46 8568 3 5041
Misses in library cache during parse: 121
14 user SQL statements in session.
478 internal SQL statements in session.
492 SQL statements in session.
6 statements EXPLAINed in this session.
********************************************************************************
Trace file: V:oracleadminCOSA0731udumpORA01896.TRC
Trace file compatibility: 8.00.04
Sort options: default
0 session in tracefile.
14 user SQL statements in trace file.
478 internal SQL statements in trace file.
492 SQL statements in trace file.
27 unique SQL statements in trace file.
6 SQL statements EXPLAINed using schema:
DBO.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
11262 lines in trace file.
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 12:22:21 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
