Re: Database Monitoring. How much is enough ?

I have found the stats in V$SQLAREA to be a gold mine of performance data:

Sort by executions (desc) and ask the programmers why the TOP 40 statements execute so often
Sort by rows processed (desc) and ask the programmers if they are maxing the WHERE clause
Sort by rows per buffer get (desc) to spot indexes that do not support the WHERE clauses

I have a question for you: If you are doing ANALYZE TABLE VALIDATE STRUCTURE, why do you do the export to null? Is it possible to pass the analyze yet fail the export? I care, because we are about to make the switch from export to analyze.

Flora Seymour wrote:

> I am currently trying to do a sanity check on the amount of database
> monitoring that our Company does on our databases that range from 24x7 (20
> gigs) mission critical (a customer service database) to much smaller, much
> less critical ones. Most are Oracle 7.3 on Alpha VMS. Some are on Oracle
> 8 on NT.
>
> I am finding that I could continually develop routines to monitor various
> aspects of the databases for ever and a day and am wondering on how to
> draw the line between practical and paranoid !
>
> Thanks to some extra hired help, the things we are currently regularly
> monitoring for on our most critical databases are...
> - excessive index browning (dead space in index nodes)
> - database corruption ( monitoring via Export database to NULL device
> (weekly)
> - corrupt tables (analyze table validate structure)
> - corrupt indexes (analyze index validate structure)
> - excessive chained rows
> - ORA- errors in Alert log (search three time daily for any ORA- errors)
> - run BSTAT/ESTAT and OTK reports weekly (with the intent to review them
> less frequently - have weekly reports for comparison purposes)
> - run and review SMK (space management kit) Alert and Info reports weekly
> - report invalid database objects (stats <> 'VALID')
>
> One thing that we are consciously not doing is re-computing statistics
> regularly, in fear that it may cause the cost based optimizer to change
> strategy (for the worse). Performance is critical to us.
>
> I would like to know how this compares to what other Oracle sites do
> regularly.
> Have we covered off the most important things ?
> Should we be re-computing statistics regularly?
>
> Also, we are pushing to have more time allocated regularly to practice
> database recovery in effort to prove database backups are reliable.
> Currently recovery testing is done only by virtue of the fact that
> developers on occasion need a production database restored to a
> development area for testing. This is not a regular thing.
> I have read and understand that practicing recovery is a wise thing, but
> wonder in the "real world" whether DBAs actually make this a priority or
> just wish they could .
>
> In other Oracle shops is recovery practice a reality ?
>
>
> Any other comments on what practically should be done on a regular basis to
> ensure the availability of our databases and on what your Oracle shop does
> regularly would be of great interest.
>
> Thanks in advance for any input.
> Flora Seymour
> Flora Seymour
> Database Administrator
> Newfoundland Power
> Tel: (709)737-5728
> Fax: (709)737-5832
> Email: fseymour_at_newfoundlandpower.com
Received on Tue Mar 09 1999 - 13:35:17 CST