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

I missed the first time thru that apparently you are only retrieving 5 rows but spending lots and lots of logical io's ( and some physical ones ) to get those 5 rows.

At second glance it's probably not the inserting at all that's impacting you but the query you are using in the select part.

What does the complete query look like exactly?

You should be able to get a similar bad response by just running the select part. Received on Tue Sep 11 2007 - 09:56:00 CDT