Re: High elapsed/cpu ratio

On Sep 10, 1: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

Are the tables large, or is it just the tablespace that's big? A large tablespace is not likely the cause for this behaviour, especially after truncating the destination table as you've reset the highwater mark.

I'd be looking at Statspack reports generated before and during this insert to see what may be problem areas. A 10053 trace might be beneficial to see what the optimizer is actually doing. And ALWAYS post your Oracle version (all four numbers, as '8i', '9i', '10g' say nothing of the actual environment); there may be a known bug which has been resolved by a patchset you have yet to apply.

Since you've provided none of the above mentioned items, nor the explain plan for this query, there is really very little to use to 'solve' this dilemma. Post more information and possibly someone can see the problem.

David Fitzjarrell Received on Mon Sep 10 2007 - 13:22:07 CDT