Re: Lose statistics on instance bounce?

In article <255uvs07c4jafp7ppvfd1j8qnbgnces5sl_at_4ax.com>,   Speed Razer <speedrazer_at_NoT_SpamM_usa.net> wrote:
> Yes, we're looking at the hit ratio's since the startup. We run some
> performance scripts that read the V$SYSSTAT tables so they're clean
> after a bounce. For example, here's the snip from the report 24 hours
> after a cold backup when the 'ANALYZE STATISTICS' is run right after
> startup:
>
> =========================
> BUFFER HIT RATIO
> =========================
> (should be > 70, else increase db_block_buffers in init.ora)
>
> logical_reads phys_reads phy_writes BUFFER HIT RATIO
> --------------- ------------ ------------ ----------------
> 42,706,201 1,115,173 10,184 97
>
> Now here's one 24 hours after without the analyze:
>
> =========================
> BUFFER HIT RATIO
> =========================
> (should be > 70, else increase db_block_buffers in init.ora)
>
> logical_reads phys_reads phy_writes BUFFER HIT RATIO
> --------------- ------------ ------------ ----------------
> 39,006,668 15,648,152 9,474 60
>
> Go figure, huh? Any other ideas? Thanks again, SR
>
> On Tue, 31 Oct 2000 12:40:51 -0600, "Kevin Brand"
> <kevin.brandx_at_tel.gte.com> wrote:
>
> >
> >Are you looking at hit ratios since startup during that initial 4-5
 days?
> >If so, try taking a snapshot over a smaller amout of time using
 either
> >utlEBstat or your own CACHE tool. That is, after the first several
 hours
> > or 1 day ) after a bounce of the instance, take an hour long sample
 during
> >a busy time and calculate your hit ratios off of the sample.
> >
> >Bouncing the instance certainly clears the buffer cache AND all of
 the
> >v$sysstat "statistics", but it does NOT clear table/index statistics
> >generated by calls to ANALYZE.
> >
> >-Kevin
> >
> >
> >"Speed Razer" <speedrazer_at_NoT_SpamM_usa.net> wrote in message
> >news:4j1uvs8o380b9h8pr2s7jioc9vsrvkp7ue_at_4ax.com...
> >> Some time back I remember reading a post on Metalink by some guy
> >> claiming that he preferred hot backups to cold backups because when
> >> you bounce your instance for cold backups, you lost all your
> >> statistics. I ran that concept by some other DBAs who said that
 was
> >> hogwash. However, I've seen what I believe to be exactly this
> >> behavior. Namely, if I bounce the instance and don't analyze
> >> statistics, it takes 4-5 days for the db buffer cache hit ratios to
> >> climb above 90%. But if I analyze right after I restart the
 instance,
> >> the hit ratio is usually up to 99% within a day. Any thoughts??
> >>
> >> Many thanks,
> >> SR
> >
>
>

ANALYZE TABLE .... places the entire table contents for every analyzed table into the shared pool whereas queries may only put part of the table data for the queried table or tables there. As the percentage of "found" data increases in the shared pool the better the V$SYSSTAT percentages become.

You are comparing physical reads to logical reads; since table data is not in the shared pool in such great numbers without the ANALYZE TABLE ... actions after database startup it is expected that the cache hits will be low. Queries are retrieving their results from the disk, not the cache, since the data is not yet found in the cache. All you are accomplishing, outside of refreshing your estimated or calculated statistics, it putting table data into the shared pool faster than the normal usage would. If the data is in the shared pool, your cache hits will be high and your physical reads will be low. Once the shared pool is cleared your physical reads will be high and your cache hits will be low. You needn't 'bounce' your instance to prove this -- issue an 'ALTER SYSTEM FLUSH SHARED_POOL' command and watch your 'stats' return to the 'bounce with no ANALYZE' numbers.

You are NOT losing table/index stats -- you have simply cleared the shared pool/data dictionary and all ANALYZE is doing to that is populating it again.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.