| performance problem [message #454558] |
Thu, 06 May 2010 04:47  |
klinki
Messages: 2
Registered: May 2010
|
Junior Member |
|
|
one and the same sql-query needs from time to time more than 20 times longer (15 minutes) than usual. this query is used twice a day. there is no system to see. sometimes it happens not only once in 20 days. sometimes within 2 days in a row.
does oracle do any work beyond the knowing tasks like gather_schema_stats etc. that are not well known?
|
|
|
|
|
|
|
|
| Re: performance problem [message #454746 is a reply to message #454558] |
Fri, 07 May 2010 03:37  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Based on the very sparse information you've provided, the most likely guess is a bind variable problem.
I will guess that your data has a column with a skewed distribution, such that for one value your query should use an index, and for another value a Full Table Scan would be better.
If your query uses a bind variable for this column, then if the first time that query is run, a value is passed in that causes the CBO to use a Full Table Scan, then all subsequent executions of that query will use a full table scan until the query ages out of the shared pool.
Further details from you may result in a more detailed, or different answer from us.
|
|
|
|
| Re: performance problem [message #455305 is a reply to message #454746] |
Tue, 11 May 2010 01:37 |
klinki
Messages: 2
Registered: May 2010
|
Junior Member |
|
|
thank you for your hint. it must have been a bind variable problem indeed. by changing the query, the way of retrieving data has changed. did not really understand the big difference but it works fine and faster than ever.
l.q. klinki
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
