Re: The query seems efficient - why poor performance?

On Monday, June 30, 2014 9:37:25 AM UTC-7, Mark D Powell wrote:
> On Monday, June 30, 2014 7:59:32 AM UTC-4, vsevolod afanassiev wrote:
>

> > I've found Statspack report for the query#1 from year 2012: it was using the same plan and execution time was only 1.7 millisecond. In 2 years the tables have grown (may be by 1/3), but this doesn't explain performance deterioration from 1.7 millisecond to 390 milliseconds.
>
> > The server is running with CPU utilization 20% or less. This is physical server - not an LDOM or a zone. Top 5 events show 'db file sequential reads', CPU time, 'db file scattered read', 'log file sync' - nothing unusual.
>
>
>
> Is the plan today the same as from a year ago? The difference in performance could be due to degradation in the performance of your SAN as it has become fuller over time. Can you compare your IO latency numbers from today to some time(s) in the past?
>
>
>
> HTH -- Mark D Powell --

Orders of magnitude seem a bit much for SAN degradation? That much difference would normally be plan or concurrency issues, especially with so few disk accesses. I would question whether the plan is truly the same, and see if OEM might be showing some locking spikes (my memory is hazy on that versions display, though).

I really don't know where to go with the information given, but perhaps CPU saturation is slowing down I/O access as well as everything else. I see it a lot on my completely different system with a few particular plans that use nested loops and just thrash CPU scanning the SGA for a particular segment, plus concurrency issues for other users of the segment. Yes, memory is orders of magnitude faster than I/O, but only if there is apples to apples comparison between the two. We have a tasty fruit salad here, but maybe need to go easy on the papaya.

OS tools for memory and disk might be useful here, as well as simple tests of copying files during the bad times.

jg

-- 
_at_home.com is bogus.
A billion here, a billion there, pretty soon you're talking real money. http://www.bloomberg.com/news/2014-06-30/oracle-plans-bond-offering-to-help-acquisition-of-micros.html