Forum Stats

  • 3,839,081 Users
  • 2,262,445 Discussions
  • 7,900,847 Comments

Discussions

Determine database read/write statistics

317715
317715 Member Posts: 40
edited Oct 15, 2009 6:49AM in General Database Discussions
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
Tagged:

Answers

  • 437796
    437796 Member Posts: 11
    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
    ajallen Member Posts: 1,796
    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.
    ajallen
  • 317715
    317715 Member Posts: 40
    edited Oct 15, 2009 5:27AM
    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
    437796 Member Posts: 11
    edited Oct 15, 2009 6:49AM
    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.