OraFAQ Forum: Performance Tuning » using sysdate from dual takes longer time

Home » RDBMS Server » Performance Tuning » using sysdate from dual takes longer time

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

using sysdate from dual takes longer time [message #666150]

Tue, 17 October 2017 13:12

manikandan23
Messages: 34
Registered: February 2017

Member

Hi,

I have 2 queries. It is yielding the same results. But one query is having cost of 85k and the other one is just 2. Can anyone explain how the difference is and how can I improve the first one?

1. Slow running:

select nvl(pos.qty,0) nqty,pos.*,
sysdate as current_timestamp
from custdata.pos_data pos
where 1=1 and create_date >= (select sysdate-1 from dual)
and src = 'WAL852'
and feed_type ='INV'
and (qty >= 0 or qty is not null);

2. Fast running:

select nvl(pos.qty,0) nqty,pos.*,
sysdate as current_timestamp
from custdata.pos_data pos
where 1=1 and create_date >= (sysdate-1)
and src = 'WAL852'
and feed_type ='INV'
and (qty >= 0 or qty is not null);

Report message to a moderator

Re: using sysdate from dual takes longer time [message #666153 is a reply to message #666150]

Tue, 17 October 2017 13:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post both EXPLAIN PLANS

Report message to a moderator

Re: using sysdate from dual takes longer time [message #666154 is a reply to message #666153]

Tue, 17 October 2017 13:21

manikandan23
Messages: 34
Registered: February 2017

Member

Explain Plan:

1. First Query:

Plan
SELECT STATEMENT  CHOOSECost: 81,128  Bytes: 158,842,332  Cardinality: 2,406,702  		
	3 TABLE ACCESS BY INDEX ROWID TABLE CUSTDATA.POS_DATA Cost: 81,126  Bytes: 158,842,332  Cardinality: 2,406,702  	
		1 INDEX RANGE SCAN INDEX CUSTDATA.IDX_POS_DATA_SRC Cost: 15,249  Cardinality: 83,389,963  
		2 FAST DUAL  Cost: 2  Cardinality: 1

2nd query:

Plan
SELECT STATEMENT  CHOOSECost: 1  Bytes: 462  Cardinality: 7  		
	2 TABLE ACCESS BY INDEX ROWID TABLE CUSTDATA.POS_DATA Cost: 1  Bytes: 462  Cardinality: 7  	
		1 INDEX RANGE SCAN INDEX CUSTDATA.IDX_POS_DATA_CRT_DT Cost: 1  Cardinality: 96

[mod-edit: code tags added by bb]

[Updated on: Tue, 17 October 2017 21:44] by Moderator

Report message to a moderator

Re: using sysdate from dual takes longer time [message #666155 is a reply to message #666154]

Tue, 17 October 2017 13:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888

EXPLAIN PLAN FOR <SQL statement>
SELECT * FROM table(dbms_xplan.display);

Report message to a moderator

Re: using sysdate from dual takes longer time [message #666156 is a reply to message #666154]

Tue, 17 October 2017 13:34

manikandan23
Messages: 34
Registered: February 2017

Member

The table custdata has separate indexes on the columns SRC, create_date, feed_type.

The processor uses the index associated with the column SRC in the first query.

In the second query it is using the index associated with the column create_date.

Basically, I did not want to use index hint to speed up the process.

Report message to a moderator

Re: using sysdate from dual takes longer time [message #666157 is a reply to message #666155]

Tue, 17 October 2017 13:42

manikandan23
Messages: 34
Registered: February 2017

Member

Hi,

I am sorry I am not sure what you are asking. I already copy pasted the explain plan for both the queries from my toad window.

Thanks,
Mani A

Report message to a moderator

Re: using sysdate from dual takes longer time [message #666158 is a reply to message #666157]

Tue, 17 October 2017 13:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

manikandan23 wrote on Tue, 17 October 2017 11:42

Hi,

I am sorry I am not sure what you are asking. I already copy pasted the explain plan for both the queries from my toad window.

Thanks,
Mani A

toad's EXPLAIN PLAN lacks sufficient detail.

Report message to a moderator

Re: using sysdate from dual takes longer time [message #666162 is a reply to message #666158]

Tue, 17 October 2017 15:18

Alien
Messages: 292
Registered: June 1999

Senior Member

Hi Mani,

besides the output from the queries that BlackSwan posted, I'd also like to see your Oracle version and the definition of the tables/indexes.

Regards,

Arian

Report message to a moderator

Re: using sysdate from dual takes longer time [message #666163 is a reply to message #666157]

Tue, 17 October 2017 16:12

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

manikandan23 wrote on Tue, 17 October 2017 19:42

Hi,

I am sorry I am not sure what you are asking. I already copy pasted the explain plan for both the queries from my toad window.

Thanks,
Mani A

What is being asked is clear. To repeat:
Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
Using TOAD really doesn't help, you know. Much better to use EXPLAIN PLAN and DBMS_XPLAN.DISPLAY

Report message to a moderator

Re: using sysdate from dual takes longer time [message #666169 is a reply to message #666163]

Wed, 18 October 2017 04:06

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Nothing to do with dates but this:

and (qty >= 0 or qty is not null)

is logically equivalent to:

and qty is not null

I suspect you meant:

and (qty >= 0 AND qty is not null)

which is logically equivalent to:

and qty >= 0

Report message to a moderator

Re: using sysdate from dual takes longer time [message #666179 is a reply to message #666169]

Wed, 18 October 2017 09:42

manikandan23
Messages: 34
Registered: February 2017

Member

Thank you All. We decided to use the Index Hint to force the optimizer to use the index associated with the create_date column which resolved the performance issue.

It looks like the long running version of the query used the other index that was created on SRC column which was costlier compared to the create_date index.

Thank you Again!

Report message to a moderator

Previous Topic:	Statspack snap shot at level 7 is slow
Next Topic:	Hints

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 20:55:47 CST 2025