OraFAQ Forum: Server Administration » Slow query - but only when query on certain values

Home » RDBMS Server » Server Administration » Slow query - but only when query on certain values

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

Slow query - but only when query on certain values [message #375203]

Fri, 03 August 2001 13:57

Mike Oakes
Messages: 25
Registered: December 2000

Junior Member

Hello,
This doesn't make any sense. I am querying a few tables by way of a database view. The problem I have is that the time it takes to return the result set is different depending on the query value.

If I do a select * from view where name = 'mike';
the query returns very fast.

When I do a select * from view where name = 'april'; the query runs very slow. I cannot understand why this would occur.

Has anyone ever seen this and is there something I should look for.

All indexes seem to be fine.

Thanks in advance
Mike O.

Report message to a moderator

Re: Slow query - but only when query on certain values [message #375215 is a reply to message #375203]

Mon, 06 August 2001 08:59

kavithask
Messages: 34
Registered: March 2001
Location: London

Member

Hi,

As you say, it does not make much sense. But did you run the SQL behind the view with the parameter and check if there was a difference in execution time? The explain plan will return the same execution pattern, but did u run it thru them and check if there was a difference?

Cheers,
Kavitha

Report message to a moderator

Re: Slow query - but only when query on certain values [message #375252 is a reply to message #375203]

Sun, 12 August 2001 15:13

Sudhakar Atmakuru
Messages: 58
Registered: May 2001

Member

I guess, there may be existing some other data related to particular values in your tables, like 'mike' does not have any data that to be considered while it is running the query and 'april' does. So it might be taking a little while to analyze such data. I am not sure what could be the culprit causing problem, but try this way, run ANALYZE command to analyze all table and indexes specified your query. So that it creates the statistics which are important and critical for your ORACLE optimizer to query the data quicker and better with the analyzed statistics. And another way, if you find, 'mike' does not have related and linked data in the database but 'april' does, and so it is taking more time for 'april', then find out what path are you using for the optimizer. If it is RULE based, then try it out with COST based and give a hint in your sql query like

SELECT /*+ CHOOSE */ column1,column2,... FROM table;

So the optimizer chooses whichever better to retrieve the data in a quicker way.

There is another way, find out whether the indexes created on querying tables are right and the indexed columns are not NULL allowed. If your query (the condition appears right after WHERE) includes any columns those are not indexed or have NULL allowed then it could be the reason it is taking more time for particular values. It is a good idea to have an index on every column that is included in the querying condition and those columns are NOT NULL defined. Make sure all these are existing in right way and rebuild the indexes with ALTER command and run ANALYZE to gather the statistics that is so useful for your OPTIMIZER. Hope this helps.
Good luck.

Report message to a moderator