RE: why did query behavior change ?
Date: Wed, 8 Dec 2010 16:48:05 -0600
Message-ID: <20101208224805.45632.qmail_at_optimaldba.com>
Did this impact *every* query accessing this table? What were the differences in the execution plans? What were the differences in the execution profiles (changes in physical i/o, consistent gets, cpu time, wait time)? When were statistics last gathered on the table? Were there any systemic changes?
- Original Message ------- On 12/8/2010 10:22 PM ed lewis wrote: Hi, You just recently experienced a performance issue with queries accessing a particular table. Response time went from 15-20 seconds, to 2-3 minutes.The table has around 500,000 rows, and is a "core" table in the app.
I did run 1 problem query interactively to get the explain plan. The query did a table lookup via an index, and it returned 165 rows. The "consistent gets" though were around 1 million. This is a 3rd party app, so what we can do with the query itself is limited.
The temporary workaround was to cache the table. This brought the response time back to normal. By caching the table, I assume the table will be handled differently in cache.The better results show that.
But why did the query behavior worsen ? I am trying to track down the root cause.
Any ideas are welcome.
thanks ed
solaris 10
oracle 10.2.0.4 - 2 node cluster-
asm 10.2.0.4
clusterware 10.2.0.4
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 08 2010 - 16:48:05 CST