Hi,

I'm trying to figure out some security parameter but I'm not sure how meaningful they are.

I've put together some script to get information like the Buffer Cache Hit Ratio (v$sysstat), Index Hit Ratio (v$sql), Sort Ratio (v$sysstat), Library Cache Hit Ratio (v$librarycache), Dictionary Cache Hit Ratio (v$rowcache), latch contention (v$latch, v$latchholder, v$latchname), shared pool reloads (v$librarycache) and so on. The mentioned Views are the base to get the information.

Are the information from these views meaningful or do I have to take snapshots and generate reports with Statspack to get useful information. I'm afraid that the information are just information for that point in time rather than for a long time period (since the SID is installed). So, they are not really useful to tune the database due to this information.

What's the best way to get useful information(parameter)? And how get Statspack the information for the reports?

Thanks a million for your help!

First you need to install statspack:

http://www.dba-oracle.com/t_statspack_installation_steps.htm

Also, try looking at this to get a few basics on using statspack:

http://www.dba-oracle.com/art_statspack.htm

To start with, you have to install statspack. AWR is installed automatically, but has additional licensing cost to use that statspack does not have.

>> Are the information from these views meaningful

It depends.

The buffer cache hit ratio is only useful for signalling a severely undersized cache and changes in processing.

Here is what I measure for Oracle databases:

http://www.remote-dba.net/alerts.htm

****************************************************************************
>> The mentioned Views are the base to get the information.

Those views are acculumators (since startup), you want the delta values from STATSPACK instead.

*****************************************************************************
>> I'm afraid that the information are just information for that point in time rather than for a long time period

True, but you don;t ant a "long" time period, an hour is a good interval fopr monitoring

*****************************************************************************
>> do I have to take snapshots and generate reports with Statspack to get useful information.

Yes.

>> What's the best way to get useful information(parameter)? And how get Statspack the information for the reports?

I have complete step-by-step instruction in the book "Oracle Tuning: The Definitive Reference":

http://www.rampant-books.com/book_2005_1_a...tive_tuning.htm

*****************************************************************************