Re: TKPROF output question

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

Hi

Thanks for the input. The storage is attached to the same machine. So no changes in the configuration.

The reason it took so long was that one of the other DBA had apparently flipped an index to be a bitmap one in the new database. that created huge performance issue. Once that index was changed to a normal one the performance was normal at around 5 minutes.

Appreciate all your input,

Thanks

Sam Received on Thu Oct 25 2007 - 21:29:28 CDT