OraFAQ Forum: Performance Tuning » reduce the elapsed time

Home » RDBMS Server » Performance Tuning » reduce the elapsed time

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

reduce the elapsed time [message #461882]

Tue, 22 June 2010 01:29

akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai

Junior Member

Hi,

i have a sample code as below.
Kindly let me know what can be done to reduce the elapsed time of the below query

 select col1 from a where a.end_date is null and a.start_date <(sysdate-(xhours/24))
       order by  a.start_date

Report message to a moderator

Re: reduce the elapsed time [message #461893 is a reply to message #461882]

Tue, 22 June 2010 01:43

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

1/ Post the Execution plan of above Query.
2/ Might want to "(sysdate-(xhours/24))" calculate this value in some variable so for each line this will not be calcualted again.( will make difference for large table)

Report message to a moderator

Re: reduce the elapsed time [message #461894 is a reply to message #461893]

Tue, 22 June 2010 01:44

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

if the result set is very large and if you dont care of Sorting Remove it.

Report message to a moderator

Re: reduce the elapsed time [message #461901 is a reply to message #461894]

Tue, 22 June 2010 01:51

akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai

Junior Member

The explain plan is as below

SELECT STATEMENT  ALL_ROWSCost: 7 K  Bytes: 3 K  Cardinality: 132          
    2 SORT ORDER BY  Cost: 7 K  Bytes: 3 K  Cardinality: 132      
        1 TABLE ACCESS FULL TABLE a Cost: 7 K  Bytes: 3 K  Cardinality: 132

Report message to a moderator

Re: reduce the elapsed time [message #461953 is a reply to message #461901]

Tue, 22 June 2010 04:08

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

Senior Member

How many rows in the table?
Do you have an index on start_date?

Report message to a moderator

Re: reduce the elapsed time [message #461955 is a reply to message #461953]

Tue, 22 June 2010 04:19

akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai

Junior Member

There are 11459274 rows in it as of now but would potentially increase in future.
No we do not have any index on the dates.

Report message to a moderator

Re: reduce the elapsed time [message #461961 is a reply to message #461955]

Tue, 22 June 2010 04:56

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

Senior Member

Did you run the explain plan against a dev/test db that has a lot less rows in it?
The explain plan thinks that table is practically empty.

Regardless, if you don't have any indexes a full table scan is the only option, try adding an index on start_date.

Report message to a moderator

Re: reduce the elapsed time [message #461971 is a reply to message #461961]

Tue, 22 June 2010 05:12

akilabaskaran
Messages: 29
Registered: May 2007
Location: chennai

Junior Member

The explain plan in the live environment is also same.It also uses the Full Table Scan.
So i was also thinking of creating a function based index on the
end date. But since we sort and use start date it would be a better option to create a index on start date.

cookiemonster : Thanks For your suggestions and immediate help.

Report message to a moderator

Re: reduce the elapsed time [message #462034 is a reply to message #461882]

Tue, 22 June 2010 08:12

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

IMHO

1. If xhours is a table column then the expression
a.start_date < (sysdate-(xhours/24)) is not indexable and Oracle must use full table scan.

2. Let's assume that xhours is parameter. In that case you are selecting all rows older then (sysdate-(xhours/24)).
So (unless your table holds future dates) you are selecting almost all data - full table scan is used to get a better performance.

3. How many rows your query is supposed to select?

HTH.

[Updated on: Tue, 22 June 2010 08:14]

Report message to a moderator

Re: reduce the elapsed time [message #467179 is a reply to message #461901]

Thu, 22 July 2010 08:25

prashant_ora
Messages: 196
Registered: July 2010

Senior Member

have u checked the v$sql_plan for explain plan , becuase some time it differs from the explain plan which we took from any tool like pl-sql developper.

Report message to a moderator

Previous Topic:	index monitoring
Next Topic:	Delete statement is taking more time for execution

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 04 12:00:28 CDT 2024