Home » RDBMS Server » Performance Tuning » using sysdate from dual takes longer time
using sysdate from dual takes longer time [message #666150] Tue, 17 October 2017 13:12 Go to next message
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);
Re: using sysdate from dual takes longer time [message #666153 is a reply to message #666150] Tue, 17 October 2017 13:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post both EXPLAIN PLANS
Re: using sysdate from dual takes longer time [message #666154 is a reply to message #666153] Tue, 17 October 2017 13:21 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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);

Re: using sysdate from dual takes longer time [message #666156 is a reply to message #666154] Tue, 17 October 2017 13:34 Go to previous messageGo to next message
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.
Re: using sysdate from dual takes longer time [message #666157 is a reply to message #666155] Tue, 17 October 2017 13:42 Go to previous messageGo to next message
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
Re: using sysdate from dual takes longer time [message #666158 is a reply to message #666157] Tue, 17 October 2017 13:51 Go to previous messageGo to next message
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.
Re: using sysdate from dual takes longer time [message #666162 is a reply to message #666158] Tue, 17 October 2017 15:18 Go to previous messageGo to next message
Alien
Messages: 289
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
Re: using sysdate from dual takes longer time [message #666163 is a reply to message #666157] Tue, 17 October 2017 16:12 Go to previous messageGo to next message
John Watson
Messages: 8922
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
Re: using sysdate from dual takes longer time [message #666169 is a reply to message #666163] Wed, 18 October 2017 04:06 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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
Re: using sysdate from dual takes longer time [message #666179 is a reply to message #666169] Wed, 18 October 2017 09:42 Go to previous message
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!
Previous Topic: Statspack snap shot at level 7 is slow
Next Topic: Hints
Goto Forum:
  


Current Time: Thu Mar 28 10:56:19 CDT 2024