Determine database read/write statistics

317715

From the following (in Oracle documentation)
DB_WRITER_PROCESSES parameter is useful for systems that modify data heavily. It specifies the initial number of database writer processes for an instance.


And from the "Deployment Guide for Oracle on Windows using Dell PowerEdge Servers.pdf" in http://www.oracle.com/technology/tech/windows/index.html
RAID LEVELS I have heard that it is for disks where datafiles reside the following is true
If I/O is <= 90% reads, then it is advisable to go for RAID 10. If I/O is > 90% reads, then RAID 5 could be considered.


I would like to know
1. How do we find out whether our database is "read heavy" or "write heavy"? Are there are scripts available please?
2. In commercial environments, what sort of RAID Levels are normally used for "read heavy and write heavy databases?

Edited by: sandeshd on Oct 14, 2009 3:11 PM

437796

We were in a similar situation some weeks ago, we decided to make a trigger (logout) for saving the butes for a specific schema, you can work with this data importing it with Excel or something similar.


DROP TABLESPACE BYTES_USUARIOS INCLUDING CONTENTS AND DATAFILES;

CREATE TABLESPACE BYTES_USUARIOS DATAFILE
'/oradata/oradata/ewok/bytes_usuarios.dbf' SIZE 1024M AUTOEXTEND ON NEXT 25M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

+++++++++++

CREATE USER B1
IDENTIFIED BY VALUES %password%
DEFAULT TABLESPACE BYTES_USUARIOS
TEMPORARY TABLESPACE TEMP
PROFILE MONITORING_PROFILE
ACCOUNT UNLOCK;
-- 1 Role for B1
GRANT CONNECT TO B1;
ALTER USER B1 DEFAULT ROLE NONE;
-- 2 System Privileges for B1
GRANT CREATE TABLE TO B1;
GRANT CREATE SESSION TO B1;
-- 1 Tablespace Quota for B1
ALTER USER B1 QUOTA UNLIMITED ON BYTES_USUARIOS;

++++++++++

CREATE TABLE b1.BYTES_USUARIOS
(
USERNAME VARCHAR2(30 BYTE),
SID NUMBER,
SERIAL# NUMBER,
MACHINE VARCHAR2(64 BYTE),
LOGON_TIME DATE,
CLS VARCHAR2(53 BYTE),
NAME VARCHAR2(64 BYTE),
VALUE NUMBER
)
TABLESPACE BYTES_USUARIOS
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

grant all on b1.bytes_usuarios to system;

++++++++++++++++

grant select on v_$mystat to system;

grant select on v_$session to system;

grant select on v_$statname to system;


DROP TRIGGER SYSTEM.TRG_LOGOFF;

CREATE OR REPLACE TRIGGER SYSTEM.TRG_LOGOFF
BEFORE LOGOFF
ON DATABASE
DECLARE
--VAR_CADENA VARCHAR(20);
begin

--VAR_CADENA := "%bytes%";

--execute immediate '
insert into b1.bytes_usuarios (
select
ss.username,
ss.sid, ss.serial#, ss.machine, ss.logon_time,
decode (bitand( 1,class), 1,'User ', '') ||
decode (bitand( 2,class), 2,'Redo ', '') ||
decode (bitand( 4,class), 4,'Enqueue ', '') ||
decode (bitand( 8,class), 8,'Cache ', '') ||
decode (bitand( 16,class), 16,'Parallel Server ', '') ||
decode (bitand( 32,class), 32,'OS ', '') ||
decode (bitand( 64,class), 64,'SQL ', '') ||
decode (bitand(128,class),128,'Debug ', '') cls,
name,(value/1024/1024) from sys.v_$statname m, sys.v_$mystat s, sys.v_$session ss
where
m.statistic# = s.statistic#
and (name like '%bytes sent%' or name like '%bytes received%')
and ss.sid = (select distinct sid from sys.v_$mystat)
);-- '
end;
/


++++++++++++

TODO
--------
select username, name, sum(value)
from b1.bytes_usuarios
group by username, name
order by username, name


SOLO bytes enviados
-----------------------------
select username, name, sum(value)
from b1.bytes_usuarios
where
and name like '%sent%'
group by username, name
order by username, name

SOLO bytes recibidos
-----------------------------
select username, name, sum(value)
from b1.bytes_usuarios
where
and name like '%received%'
group by username, name
order by username, name

ajallen

An AWR report will tell you read and write stats for the time frame of the report.

What RAID is used in commercial environments? I will have to answer that with the standard IT response "It depends".

It depends on the type of disk services in use - local Vs. SAN/NAS. It depends on cost Vs. performance. If the storage manager is the niggardly type, he might choose RAID 5. If he is more interested in performance he might choose to give you RAID 10. If he does not understand his job, he might give you RAID 01. If the data is on a SAN, RAID 5 Vs. RAID 10 is still a question of performance, but the write cache tends to mitigate the additional overhead of computing parity for RAID 5, but RAID 10 can still be expected to give better read performance.

Probably as clear as mud, but the message here is that there are a number of factors that you must consider for your shop and determine what is best for you. That may or may not be the same as what others are doing.

317715

Alonsocala - Thanks for your reply....but cannot see what your reply has to do with my question! Did you post here by mistake?

Edited by: sandeshd on Oct 15, 2009 10:27 AM

437796

Hi

With this trigger, after a couple of hours, your table will have some data, so if you use this sql´s (down) you can know exactly how many megas your database moved (in & out), so you can know easily how much heavy (sent & received) is your db.

I think, you can use it for answer your point number 1



TODO <-- everything (megas)

select username, name, sum(value)
from b1.bytes_usuarios
group by username, name
order by username, name

SOLO bytes enviados <--- just megas sent

select username, name, sum(value)
from b1.bytes_usuarios
where
and name like '%sent%'
group by username, name
order by username, name

SOLO bytes recibidos <--- just megas received
--------------------------
select username, name, sum(value)
from b1.bytes_usuarios
where
and name like '%received%'
group by username, name
order by username, name

Edited by: alonsocala on 15-oct-2009 11:47