Re: High elapsed/cpu ratio

> 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.

Both, the driving table is about 4.4 million rows, two others are in the 10s of millions. However, most of the tables are accessed on a unique index, or a non-unique index with a high degree of selectivity.

> 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.

Sorry about that, it's 9.2.0.6 on Solaris 5.9

> 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.

I was afraid this might be the case. I'm writing a query against the Oracle eBusiness Suite's
AR tables. I wasn't entirely sure if I would be violating any sort of licensing agreements by
posting detailed information (and I'm still not sure). If it would be helpful, I could replace table
and index names with placeholders.

I guess I'm really looking for information on what would cause a large elapsed/cpu ratio in general
rather than help optimizing the specific query.

> David Fitzjarrell

Eric Received on Mon Sep 10 2007 - 13:32:07 CDT