Re: High elapsed/cpu ratio

On Sep 10, 2:07 pm, ebwri..._at_gmail.com wrote:
> I'm working on tuning a particular query and have gotten the explain
> plan as pretty as possible, low cost, etc. However, when running the
> query (an insert into table select ... statement), I find the
> following information in the tkprof trace file:
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.02 0.02 0 0
> 0 0
> Execute 1 170.59 2289.34 870183 16953935
> 49 5
> Fetch 0 0.00 0.00 0 0
> 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2 170.61 2289.37 870183 16953935
> 49 5
>
> The cpu spent less than 3m executing the query, but it took nearly 40m
> for results to be retrieved and inserted. This was done in a
> development environment with little to no other users (so, no locking
> issues), fresh statistics on tables and indexes, and an empty target
> table that was truncated immediately before processing.
>
> The one issue I know that could cause problems is that the tables
> being accessed are in a very large (~500 GB) tablespace. This is not
> something that can be changed at this time.
>
> Thanks,
> Eric

Get a 10046 trace and put it through a resouce profiler such as the free orasrp that Egorst has written.

Follow a methodology such as developed by Cary Millsap in his book "Optimizing Oracle Performance" which really should be read and digested entirely at least 3 or 4 times. But a trace through a resource profiler will get you looking at "what's slowing you down the most". Received on Mon Sep 10 2007 - 16:24:04 CDT