Re: Increased physical reads on a single datafile

My first inclination would be to find out what is affected by the firmware change the SA made.

Next I would monitor v$sess_io to catch the account(s) causing all the physical reads, and then collect their SQL from the v$sqltext and run it all through explain plan.

Someone or something is likely doing some queries that are either new, or the execution plan has changed to a change in statistics.

Jared

On Fri, 28 Apr 2000 rsands_at_lendleaserei.com wrote:

>
>
> Gurus ...
>
> A single tablespace/datafile is showing a huge increase in phys
> reads since the end of March. The numbers are:
>
> DATE PHYRDS READ_PCT PHYWRTS WRITE_PCT
> Mar 22 3,863 0.21 651 0.23
> Apr 28 5,985,597 76.84 757 0.12
>
> All other datafiles show just minor variation. Most are at less than
> 1% for read and write pct, and the few that do have higher physical
> read or writes are the major players in the application, but still remain
> below 3 % for datafiles, and between 3 to 5% for the index files. I have
> checked and all the indexes are still in place and the schema has been
> analyzed nightly (compute) This tablespace holds 6 frequently used
> but smaller tables. No changes on the server except that the SA updated
> firmware. DB hit ratio has nosed dived from approx. 95% to 75%, but
> the problem appears to be confined to this tablespace. SGA settings
> have not been changed.
>
> Any suggestions on what to investigate next would be appreciated!!
>
> TIA
>
> Robyn
>
>
> --
> Author:
> INET: rsands_at_lendleaserei.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address Received on Fri Apr 28 2000 - 19:01:42 CDT