Home » RDBMS Server » Performance Tuning » Please help me in tuning below query
Please help me in tuning below query [message #450119] |
Mon, 05 April 2010 04:05 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Gangaprasad
Messages: 7 Registered: April 2010 Location: India
|
Junior Member |
|
|
Hi all,
Below query taking almost 1Hour to execute.
Please help me on same.
INSERT INTO B_TRB112
(COUNTRY_CODE,
LEG_VEH,
BOFF_CODE,
REF_NUM,
SEQ_NUM,
SERIAL_NUM,
CHARGE_TYPE,
CHARGE_CCY,
RECV_STATUS,
POSTING_DATE,
LINK_NUM,
SUPPRESS_TRACER,
LAST_TRACER_DATE,
NEXT_TRACER_DATE,
AGE)
SELECT
B_ACCT_RECV.COUNTRY_CODE,
B_ACCT_RECV.LEG_VEH,
B_ACCT_RECV.BOFF_CODE,
B_ACCT_RECV.REF_NUM,
B_ACCT_RECV.SEQ_NUM,
B_ACCT_RECV.SERIAL_NUM,
B_ACCT_RECV.CHARGE_TYPE,
B_ACCT_RECV.TXN_CCY,
B_ACCT_RECV.RECV_STATUS,
B_ACCT_RECV.POSTING_DATE,
B_ACCT_RECV.LINK_NUM,
B_ACCT_RECV.SUPPRESS_TRACER,
B_ACCT_RECV.LAST_TRACER_DATE,
B_ACCT_RECV.NEXT_TRACER_DATE,
TRUNC(TO_DATE('20120810000000','YYYYMMDDHH24MISS'))
- TRUNC(B_ACCT_RECV.POSTING_DATE)
FROM B_ACCT_RECV
WHERE
B_ACCT_RECV.COUNTRY_CODE = 'US' and
B_ACCT_RECV.LEG_VEH = 'CNA' and
TRUNC(B_ACCT_RECV.POSTING_DATE)
<= TRUNC(TO_DATE('20120810000000', 'YYYYMMDDHH24MISS')) and
(B_ACCT_RECV.RECV_STATUS = 'OST' or
B_ACCT_RECV.RECV_STATUS = 'ACT' or
B_ACCT_RECV.RECV_STATUS = 'PPD') and
not EXISTS (SELECT A.REF_NUM FROM B_ACCRUAL A WHERE B_ACCT_RECV.REF_NUM = A.REF_NUM
AND B_ACCT_RECV.CHARGE_TYPE = A.CHARGE_TYPE) and
not EXISTS (SELECT A.REF_NUM FROM B_AMORTIZATION A WHERE B_ACCT_RECV.REF_NUM = A.REF_NUM
AND B_ACCT_RECV.CHARGE_TYPE = A.CHARGE_TYPE) and
B_ACCT_RECV.COUNTRY_STATE = 'A' and
B_ACCT_RECV.LEG_VEH_STATE = 'A' and
NOT EXISTS (select x.link_num from B_ACCT_RECV_SUB x
where x.link_num = B_ACCT_RECV.link_num and
x.recv_type = 'COF' );
|
|
|
|
|
|
Re: Please help me in tuning below query [message #450134 is a reply to message #450128] |
Mon, 05 April 2010 04:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Gangaprasad
Messages: 7 Registered: April 2010 Location: India
|
Junior Member |
|
|
Hi yasir,
Please find Explain Plan
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=4383 Card=1 Bytes=
124)
1 0 NESTED LOOPS (ANTI) (Cost=4383 Card=1 Bytes=124)
2 1 NESTED LOOPS (ANTI) (Cost=4381 Card=1 Bytes=105)
3 2 HASH JOIN (RIGHT ANTI) (Cost=4379 Card=1 Bytes=86)
4 3 TABLE ACCESS (FULL) OF 'B_ACCT_RECV_SUB' (TABLE) (Co
st=1511 Card=3610 Bytes=39710)
5 3 TABLE ACCESS (FULL) OF 'B_ACCT_RECV' (TABLE) (Cost=2
867 Card=1746 Bytes=130950)
6 2 TABLE ACCESS (BY INDEX ROWID) OF 'B_ACCRUAL' (TABLE) (
Cost=2 Card=1 Bytes=19)
7 6 INDEX (RANGE SCAN) OF 'B_IDX_ACCRUAL_01' (INDEX) (Co
st=1 Card=1)
8 1 TABLE ACCESS (BY INDEX ROWID) OF 'B_AMORTIZATION' (TABLE
) (Cost=2 Card=1 Bytes=19)
9 8 INDEX (RANGE SCAN) OF 'B_IDX_AMORTIZATION_01' (INDEX)
(Cost=1 Card=1)
|
|
|
Re: Please help me in tuning below query [message #450140 is a reply to message #450119] |
Mon, 05 April 2010 04:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Yasir Hashmi
Messages: 304 Registered: April 2006
|
Senior Member |
|
|
Are these columns indexed??
B_ACCT_RECV.COUNTRY_CODE = 'US' and
B_ACCT_RECV.LEG_VEH = 'CNA' and
TRUNC(B_ACCT_RECV.POSTING_DATE)
<= TRUNC(TO_DATE('20120810000000', 'YYYYMMDDHH24MISS')) and
(B_ACCT_RECV.RECV_STATUS = 'OST' or
B_ACCT_RECV.RECV_STATUS = 'ACT' or
B_ACCT_RECV.RECV_STATUS = 'PPD') and
x.link_num = B_ACCT_RECV.link_num and
x.recv_type = 'COF'
|
|
|
Re: Please help me in tuning below query [message #450142 is a reply to message #450140] |
Mon, 05 April 2010 05:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Gangaprasad
Messages: 7 Registered: April 2010 Location: India
|
Junior Member |
|
|
Hi Yasir,
Mentioned columns are not indexed.
I am planing to break single SQL query into four equivalent queries as below.
Please suggest me whether this apporach will work.
============================================================================================================================
INSERT INTO B_TRB112
(COUNTRY_CODE,
LEG_VEH,
BOFF_CODE,
REF_NUM,
SEQ_NUM,
SERIAL_NUM,
CHARGE_TYPE,
CHARGE_CCY,
RECV_STATUS,
POSTING_DATE,
LINK_NUM,
SUPPRESS_TRACER,
LAST_TRACER_DATE,
NEXT_TRACER_DATE,
AGE)
SELECT
B_ACCT_RECV.COUNTRY_CODE,
B_ACCT_RECV.LEG_VEH,
B_ACCT_RECV.BOFF_CODE,
B_ACCT_RECV.REF_NUM,
B_ACCT_RECV.SEQ_NUM,
B_ACCT_RECV.SERIAL_NUM,
B_ACCT_RECV.CHARGE_TYPE,
B_ACCT_RECV.TXN_CCY,
B_ACCT_RECV.RECV_STATUS,
B_ACCT_RECV.POSTING_DATE,
B_ACCT_RECV.LINK_NUM,
B_ACCT_RECV.SUPPRESS_TRACER,
B_ACCT_RECV.LAST_TRACER_DATE,
B_ACCT_RECV.NEXT_TRACER_DATE,
TRUNC(TO_DATE('20120810000000','YYYYMMDDHH24MISS'))
- TRUNC(B_ACCT_RECV.POSTING_DATE)
FROM B_ACCT_RECV
WHERE
B_ACCT_RECV.COUNTRY_CODE = 'US' and
B_ACCT_RECV.LEG_VEH = 'CNA' and
TRUNC(B_ACCT_RECV.POSTING_DATE)
<= TRUNC(TO_DATE('20120810000000', 'YYYYMMDDHH24MISS')) and
(B_ACCT_RECV.RECV_STATUS = 'OST' or
B_ACCT_RECV.RECV_STATUS = 'ACT' or
B_ACCT_RECV.RECV_STATUS = 'PPD') and
B_ACCT_RECV.COUNTRY_STATE = 'A' and
B_ACCT_RECV.LEG_VEH_STATE = 'A' ;
==============================================================================================
31112 rows created.
Commit complete.
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=2867 Card=1746 Byt
es=130950)
1 0 TABLE ACCESS (FULL) OF 'B_ACCT_RECV' (TABLE) (Cost=2867 Ca
rd=1746 Bytes=130950)
==================================================================================================
DELETE from B_TRB112 B where exists (
SELECT A.REF_NUM FROM B_ACCRUAL A WHERE B.REF_NUM = A.REF_NUM
AND B.CHARGE_TYPE = A.CHARGE_TYPE);
10435 rows deleted.
Commit complete.
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=ALL_ROWS (Cost=61 Card=1 Bytes=40
)
1 0 DELETE OF 'B_TRB112'
2 1 NESTED LOOPS (SEMI) (Cost=61 Card=1 Bytes=40)
3 2 TABLE ACCESS (FULL) OF 'B_TRB112' (TABLE) (Cost=60 Car
d=1 Bytes=21)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'B_ACCRUAL' (TABLE) (
Cost=1 Card=1 Bytes=19)
5 4 INDEX (RANGE SCAN) OF 'B_IDX_ACCRUAL_01' (INDEX) (Co
st=1 Card=1)
==================================================================================================
SQL> DELETE from B_TRB112 B where exists (
2 SELECT A.REF_NUM FROM B_AMORTIZATION A WHERE B.REF_NUM = A.REF_NUM
3 AND B.CHARGE_TYPE = A.CHARGE_TYPE);
6101 rows deleted.
Commit complete.
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=ALL_ROWS (Cost=61 Card=1 Bytes=40
)
1 0 DELETE OF 'B_TRB112'
2 1 NESTED LOOPS (SEMI) (Cost=61 Card=1 Bytes=40)
3 2 TABLE ACCESS (FULL) OF 'B_TRB112' (TABLE) (Cost=60 Car
d=1 Bytes=21)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'B_AMORTIZATION' (TAB
LE) (Cost=1 Card=1 Bytes=19)
5 4 INDEX (RANGE SCAN) OF 'B_IDX_AMORTIZATION_01' (INDEX
) (Cost=1 Card=1)
==================================================================================================
SQL> DELETE from B_TRB112 B where exists (
2 select x.link_num from B_ACCT_RECV_SUB x
3 where x.link_num = B.link_num and
4 x.recv_type = 'COF' );
0 rows deleted.
Commit complete.
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=ALL_ROWS (Cost=62 Card=1 Bytes=19
)
1 0 DELETE OF 'B_TRB112'
2 1 NESTED LOOPS (SEMI) (Cost=62 Card=1 Bytes=19)
3 2 TABLE ACCESS (FULL) OF 'B_TRB112' (TABLE) (Cost=60 Car
d=1 Bytes=8)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'B_ACCT_RECV_SUB' (TA
BLE) (Cost=2 Card=1 Bytes=11)
5 4 INDEX (RANGE SCAN) OF 'B_IDX_ACCT_RECV_SUB_1' (INDEX
) (Cost=2 Card=1)
|
|
|
|
Re: Please help me in tuning below query [message #450439 is a reply to message #450119] |
Wed, 07 April 2010 04:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Going by the explain plan you posted, this NOT EXISTS is doing a Full Table Scan:NOT EXISTS (select x.link_num from B_ACCT_RECV_SUB x
where x.link_num = B_ACCT_RECV.link_num and
x.recv_type = 'COF' );
Make sure that there's an index on Link_num,Revc_Type on B_Acct_Recv_Sub - that should help there.
Rewrite all of your NOT EXISTS queries as 'SELECT null FROM...' rather than 'SELECT <column name> FROM....' - it is exactly the same functionality, but makes it a it clearer that you're nt interested in any values fro the row, and (I think) runs slightly quicker.
What percentage of the rows of the table B_ACCT_RECV should be retrieved by the query?
|
|
|
Goto Forum:
Current Time: Thu Sep 05 23:35:56 CDT 2024
|