Re: TKPROF output question

On Wed, 24 Oct 2007 11:58:17 -0700, samdba34 wrote:

> Hi
>
> We recently got new storage and are in the process of moving our
> database to the new faster storage. The data was exported and imported
> into the new database. But when I run a Pl/Sql procedure in the new
> database it takes around 34 minutes to cpmplte where as it takes just 5
> mts to complete in the original database. The main difference I found
> between the 2 trace files is in the OVERALL TOTALS FOR ALL NON-
> RECURSIVE STATEMENTS which for the new instance goes as follows :
>
> BEGIN Load_TABLE(to_date('10/22/2007','mm/dd/yyyy')); END;
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0 0
> Execute 1 597.67 2038.05 6730 47845804 55676347
> 1
> Fetch 0 0.00 0.00 0 0 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2 597.67 2038.06 6730 47845804 55676347
> 1

You should rather check the _next_ parts of the report, where you can see the real updates/inserts taking place during the procedure execution. By doing so, you will see the actual number of updated rows and, if there are several updates, you will see which ones are too long.

Moreover you will see the execution plans for those statements; it's not impossible that they have changed since your migration (are your statistics up-to-date?)

Jerome Received on Wed Oct 24 2007 - 15:12:43 CDT