Re: TKPROF output question

On Oct 24, 1:58 pm, samdb..._at_yahoo.com 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
>
> The corresponding value in the original database goes like this
>
> BEGIN Load_TESTFACT(to_date('10/22/2007','mm/dd/yyyy')); END;
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.01 0.14 0 0
> 0 0
> Execute 1 16.32 44.54 0 23183
> 71 1
> Fetch 0 0.00 0.00 0 0
> 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2 16.33 44.69 0 23183
> 71 1
>
> The no. of rows being loaded are same and the init parameters are
> same. In the new database the block size is 32 instead of 16 in the
> original database.
>
> This change is reflected in the OVERALL TOTALS FOR ALL NON-RECURSIVE
> STATEMENTS section of the trace file:
>
> OLD :
> OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 3 0.02 0.41 0 0
> 0 0
> Execute 4 18.19 504.79 45961 163614
> 558 2
> Fetch 0 0.00 0.00 0 0
> 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 7 18.21 505.20 45961 163614
> 558 2
>
> NEW:
> OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 3 0.00 0.03 0 0
> 0 0
> Execute 4 601.80 2048.31 8430 48003072
> 55681654 2
> Fetch 0 0.00 0.00 0 0
> 0 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 7 601.80 2048.34 8430 48003072
> 55681654 2
>
> Can you throw some light on what the section NON-RECURSIVE STATEMENTS
> means.
>
> Thanks
>
> Sam

Which other init.ora parameters did you 'adjust' besides the db_block_size?

David Fitzjarrell Received on Wed Oct 24 2007 - 14:16:33 CDT