hpuxrac wrote:
> 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.
May I suggest to use Oracle's traceanalyzer available for download from
metalink Note:224270.1.
Also another tool of carlos sierra is available named sqltxplan, quite
usefull for single statment analysis.
In my personal opinion tkprof is a legacy tool.
Jan
Received on Tue Sep 11 2007 - 14:10:13 CDT
