Home » RDBMS Server » Performance Tuning » Query Slow (Oracle-9i and 10g )
Query Slow [message #398768] Fri, 17 April 2009 08:46 Go to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Hello Experts

A Query yesterday took 5 min to give output, but today it takes 60 min to give output.

a) In this one day gap no database parameters were changed.
b) No index/constraints were dropped.

1) What could be the reasons ?
2) What all are the things I have to check ?
Note
----
This is the only interview question(repeatedly asked).
Please help....
I collected some notes from Google, but panel members are not satisfied with my answer.That is why I am asking u, please..



Thanks in advance
sbmk_design
Re: Query Slow [message #398770 is a reply to message #398768] Fri, 17 April 2009 08:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>I collected some notes from Google, but panel members are not satisfied with my answer
In many cases, these "panel members" might have a particular answer in mind and it is very hard to derive exactly what is in their mind.
For what it is worth,
please post what you have found with your research.

>> 1) What could be the reasons ?
Obviously data.
Just by adding a few records( in very particular manner) and with badly designed indexes, CBO will start hallucinating without the updated stats.
Re: Query Slow [message #398772 is a reply to message #398768] Fri, 17 April 2009 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2) All those explained in:
How to Identify Performance Problem and Bottleneck
OraFAQ Performance Tuning

Regards
Michel

Re: Query Slow [message #401139 is a reply to message #398768] Fri, 01 May 2009 15:05 Go to previous messageGo to next message
satishnawle
Messages: 3
Registered: May 2009
Junior Member
please check you query if your using index , please use hint also in you query . You need to analyze table and indexes


Satish
Re: Query Slow [message #403452 is a reply to message #398768] Fri, 15 May 2009 13:20 Go to previous messageGo to next message
g.manivannan
Messages: 2
Registered: May 2009
Location: Mumbai
Junior Member

Check last_analyzed column from dba_tables and analyze the tables and If needed rebuild the indexes.
Re: Query Slow [message #403455 is a reply to message #398768] Fri, 15 May 2009 13:25 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Check last_analyzed column from dba_tables and analyze the tables and If needed rebuild the indexes.
Which metrics & what values are used to conclude the index "needs" to be rebuilt?
Re: Query Slow [message #403457 is a reply to message #403452] Fri, 15 May 2009 13:30 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>If needed rebuild the indexes.
You just bought the wrath of Oracle Gods by using the most argued phrase in Oracle Terminology.
Hope there is not going to be another war on this.
Re: Query Slow [message #403459 is a reply to message #403457] Fri, 15 May 2009 13:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If needed rebuild the indexes.

Yes 2 posts, 2 bad advices:
http://www.orafaq.com/forum/m/403453/102589/#msg_403453

Regards
Michel
Re: Query Slow [message #403615 is a reply to message #398768] Sun, 17 May 2009 10:39 Go to previous messageGo to next message
bangalibor
Messages: 15
Registered: September 2008
Location: dhaka
Junior Member

there may be a case like,
till yesterday the query was using an index , to get the data, after one day of data insertion , the optimizer may have surpresses index as now the cost of using index is more than the cost of a full table scan. may be....

as i have seen situations where, a date query with an interval of 5 days gets executed in 22 mins and a 6 days interval takes 1 n half hour.. as it surpresses index in this case..

in my case, one day data in almost 1.5 crores.......

have a nice day
Re: Query Slow [message #404101 is a reply to message #403615] Wed, 20 May 2009 06:53 Go to previous messageGo to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Dear Mr.bangalibor

1)How u identified the problem (i.e what all are the data dictionary views/tables u used)

2)What solution helped in your case. Can u please share your experience..........

Thanks in advance
sbmk_design

Re: Query Slow [message #404184 is a reply to message #404101] Wed, 20 May 2009 12:41 Go to previous message
bangalibor
Messages: 15
Registered: September 2008
Location: dhaka
Junior Member

First of all,

Check the execution plan of the query.

check does the index exists on the
where clause,foreign key,

does the query going for
where like,where <>,where not null,where null so on
does your where clause has function defined onthe column
like where upper(ename)='ALEX'

does your table have a join ,
what kind of join it is going for..
where 2 table or multiple join
wht is the driving table in this case



if there is no index,create an index and check

remember one thing,
creating index may make your query slower too. so, create verify and then move away....
Previous Topic: PERFORMANCE ISSUE - QUERY TUNING
Next Topic: Mysterious Insufficient TEMP Tablespace...
Goto Forum:
  


Current Time: Sun Dec 04 08:56:33 CST 2016

Total time taken to generate the page: 0.08276 seconds