Re: TKPROF output question

On Oct 24, 12:16 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

The only change is to the block size of the database.

Thanks

Sam Received on Wed Oct 24 2007 - 14:23:58 CDT