WISE User Tips

If you see in the "Instance Activity Statistics" report a non-zero value for the "redo log space requests" statistic, you can increase the value of LOG_BUFFER init.ora parameter to increase LGWR (Redo Log Writer background process) log buffer in order to speed up redo writing to online redo log files.

If you see in the "Buffer Waits Summary" report excessive waits, you may experience a contention problem for some data buffer class ( as it specified by Class report column ). This type of contention usually occurs for a segment header block of a high-level index node block. To solve the problem, you can add more freelists for the object.

If the statistic "Buffer Waits" for a tablespace in the "Tablespace I/O" report is greater than 1000, you may want to consider tablespace reorganization issue in order to spread tables within it across another tablespaces.

If the "In Memory Sort Ratio" statistic drops below 90% and the disk sort rate statistic is greater than 100/hour, you may want to increase the value of SORT_AREA_SIZE init.ora parameter in order to reduce sort operations which use temporary segements.

The data buffer hit ratio (DBHR) statistic represents the percent at which a requested data block is found in the buffer pool. The more DBHR approaches 100%, the more the likelihood that the requested data block resides in memory. This reduces the expensive disk I/O resulting in better application response time. It is recommended to keep DBHR above the 95%. If it falls below 95%, you may experience performance problem with excessive disk I/O.
   You can use two reports provided by STATSPACK Viewer Pro utility "Instance Efficiency Percentages" and "Buffer Pools" in order to monitor buffer cache activity. 
   Oracle8i supports the ability to separate database tables into the different pools based on their characteristics. The new data buffer pools are defined in the init.ora file by setting the parameters like:

DB_BLOCK_BUFFERS = 10000

BUFFER_POOL_KEEP = (3000 , 3)

BUFFER_POOL_RECYCLE = ( 1000 , 3 )

   The good candidates for caching in the KEEP buffer cache are small, frequently accessed tables with full scans. You can find out these tables from "Top SQLs with High Buffer Gets" as small tables with high buffer get statistic value and full table scan in the SQL execution plan.
   The RECYCLE buffer is designed to keep data buffers for frequently accessed large tables with full scan. You can find out these tables from "Top SQLs with High Physical Reads" as large tables with high high Reads/Execution ratio and full table scan in the SQL execution plan.

For databases which are experiencing extensive data modification, the tuning of the DBWR background processes is very important. STATSPACK Viewer supports report called "Instance Activity Statistics" where an Oracle DBA can find DBWR performance metrics like "DBWR buffers scanned", "DBWR checkpoint buffers written", "DBWR checkpoints", "summed dirty queue length" , etc. In order to determine whether your current DBWR configuration is sufficient for database modification activity you can use two statistics called "summed dirty queue length" and "write requests". The ratio of summed dirty queue length/write requests should be less than 100.

Hello.

Need some assistance.
At the moment I am trying to make some of my custom reports behave a bit better.
Basically it is a report that reports on IO activity on a per disk basis.
Every 5 min iostat is run and the data is then inserted into a table called stats$iostat, structure as follows

CREATE TABLE perfstat.stats$iostat
(
snap_time date,
elapsed_seconds number(4),
hdisk varchar2(8),
kb_read number(9,0),
kb_write number(9,0)
);

In this table it get x amount of records every 5 min, a record for every drive that is installed on a the machine.

Currently I am running the following query in statspack.

select hdisk,
to_char(snap_time,'yyyy-mm-dd HH24') "Date Hour",
sum(kb_read) sum_kb_read,
sum(kb_write) sum_kb_write
from
stats$iostat 
group by
hdisk,
to_char(snap_time,'yyyy-mm-dd HH24')

This returns for every hour of the day a value for the every drive.No the problem is this does not work nicely with statspack viewer.
Looking at your reports if I was to draw summalarities between them you would return averages per drive, then when I double click on the value you return the per hour or snap shot value for that specific paramater.
I need to change the above to something likewise. Say initial report displays the averages for every drive and then when I double click the drive it draws the drive on a per hour basis.
Any help would be appreciated, once I got the one report sorted out I can apply the same logic to the other data sets, vmstat, sar -w, sar -u, sar -r
I am attaching a txt document compressed to show how the data currently looks inside the database for the iostat table.

thx 

Answer:

I modified a little bit your table by adding a new column snap_id in order to link stats data to STATSPACK's snapshots.
This facilitates the report writing and allows you to use standard technique to write STATSPACK based reports for your own tables.

The create table statement looks like:

CREATE TABLE perfstat.stats$iostat
(
snap_id number,
snap_time date,
elapsed_seconds number(4),
hdisk varchar2(8),
kb_read number(9,0),
kb_write number(9,0)
);

I have added the following sample data to this table to build example reports:

insert into stats$iostat values ( 1 , sysdate-5/24 , 0 , 'md10', 84 , 123 );
insert into stats$iostat values ( 1 , sysdate-5/24 , 0 , 'md101', 74 , 120 );
insert into stats$iostat values ( 1 , sysdate-5/24 , 0 , 'md70', 64 , 100 );
insert into stats$iostat values ( 2 , sysdate-4/24 , 0 , 'md10', 23 , 45 );
insert into stats$iostat values ( 2 , sysdate-4/24 , 0 , 'md101', 64 , 66 );
insert into stats$iostat values ( 2 , sysdate-4/24 , 0 , 'md70', 12 , 132 );
insert into stats$iostat values ( 3 , sysdate-3/24 , 0 , 'md10', 23 , 45 );
insert into stats$iostat values ( 3 , sysdate-3/24 , 0 , 'md101', 64 , 66 );
insert into stats$iostat values ( 3 , sysdate-3/24 , 0 , 'md70', 12 , 132 );
insert into stats$iostat values ( 4 , sysdate-2/24 , 0 , 'md10', 29 , 25 );
insert into stats$iostat values ( 4 , sysdate-2/24 , 0 , 'md101', 24 , 86 );
insert into stats$iostat values ( 4 , sysdate-2/24 , 0 , 'md70', 121 , 32 );
insert into stats$iostat values ( 5 , sysdate-1/24 , 0 , 'md10', 73 , 55 );
insert into stats$iostat values ( 5 , sysdate-1/24 , 0 , 'md101', 14 , 66 );
insert into stats$iostat values ( 5 , sysdate-1/24 , 0 , 'md70', 12 , 92 );
commit;

Note that you should rewrite your procedure that gathers the data to this table in order to correctly populate snap_id column.

Now, we create and save a new report called say "Disk I/O Statistics" with the following sql text:

select
hdisk,
Avg(kb_read) "Reads",
Avg(kb_write) "Writes" 
from
stats$iostat
Where
snap_id > :pBgnSnap and
snap_id <= :pEndSnap and
:pDbId = :pDbId and
:pInstNum = :pInstNum
Group by hdisk 

As you can see, this report allows you to get averages for i/o stats for the whole chosen snapshot interval.
If you click on a particular statistic likewise you did in the standard reports, you will get the detailed report for this statistic.

Note that I use dummy parameter equals like :pDbId = :pDbId and :pInstNum = :pInstNum. This is done in order Statspack Viewer Pro recognizes this report as STATSPACK based report.