OraFAQ Forum: Performance Tuning » Please help me in tuning below query

Home » RDBMS Server » Performance Tuning » Please help me in tuning below query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Please help me in tuning below query [message #450119]

Mon, 05 April 2010 04:05

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' );

Report message to a moderator

Re: Please help me in tuning below query. [message #450121 is a reply to message #450119]

Mon, 05 April 2010 04:07

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Re: Please help me in tuning below query. [message #450126 is a reply to message #450119]

Mon, 05 April 2010 04:19

mdsohail10
Messages: 13
Registered: October 2009
Location: Bangladesh

Junior Member

i think it will be better to make a view instead writing nested query.

Report message to a moderator

Re: Please help me in tuning below query [message #450128 is a reply to message #450119]

Mon, 05 April 2010 04:24

Yasir Hashmi
Messages: 304
Registered: April 2006

Senior Member

How many records is it inserting?
Whats the explain plan for the query?

How many columns in the where clause are indexed?

Report message to a moderator

Re: Please help me in tuning below query [message #450134 is a reply to message #450128]

Mon, 05 April 2010 04:34

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)

Report message to a moderator

Re: Please help me in tuning below query [message #450140 is a reply to message #450119]

Mon, 05 April 2010 04:58

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'

Report message to a moderator

Re: Please help me in tuning below query [message #450142 is a reply to message #450140]

Mon, 05 April 2010 05:06

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)

Report message to a moderator

Re: Please help me in tuning below query [message #450148 is a reply to message #450142]

Mon, 05 April 2010 05:26

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Mon, 05 April 2010 11:07

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.
...

Which part of this do you not understand?

Regards
Michel

Report message to a moderator

Re: Please help me in tuning below query [message #450439 is a reply to message #450119]

Wed, 07 April 2010 04:44

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?

Report message to a moderator

Previous Topic:	Query benchmarking
Next Topic:	Resolving Latch Free Waits

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 12 08:57:02 CDT 2024