Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Determine database read/write statistics

317715
Member Posts: 40
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
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
Answers
-
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 -
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. -
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 -
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
This discussion has been closed.