Re: TKPROF output question

On Oct 24, 2:23 pm, samdb..._at_yahoo.com wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

So the physical server configuration (CPU, memory, kernel settings) hasn't changed, the O/S version hasn't changed, and Statspack reports for both databases show basically the same data ...

David Fitzjarrell Received on Wed Oct 24 2007 - 15:46:18 CDT