Oracle RDBMS tips

Monitoring

Tables/Indexes

• desc user_tables
• desc user_indexes

DB CPU

• V$SYSSTAT: Oracle CPU usage for all sessions. The parse time cpu statistic shows the total CPU time used for parsing.
• V$SESSTAT: Oracle CPU usage for each session. 
• select * from v$resource_limit;
• select count(*) from V$process;

Wait Events

• db file sequential read: usually single block INDEXED reads [1]. Faster disks or more spindles will help. Larger buffer cache may help.
• db file scattered read
• db file single write
• db file parallel write
• log file parallel write

Latches

• HW enq
• Latch: Cache Buffer Chain - Ref [2]
  -- Find memory address:
  

  SELECT 
        addr,
        sleeps
  FROM 
        v$latch_children c,
        v$latchname n
  WHERE
        n.name='cache buffers chains' and
        c.latch#=n.latch# and
        sleeps > 100
  ORDER BY sleeps;
  
  -- Find file and block numbers:
  SELECT file#, dbablk, class, state, TCH
    FROM X$BH
    WHERE HLADDR='address of latch';
  -- Find segment name from dba_segments
  

Data/Log/Sys Files

• select name from v$datafile;
• select name from v$controlfile;
• select member from v$logfile;

Tablespace Sizes

select TABLESPACE_NAME, sum(decode(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES))/1024/1024/1024 MAX_SIZE_IN_GB, count(*) NUM_TABLES

from DBA_DATA_FILES

group by TABLESPACE_NAME;

Performance Tuning Recommendations

• #Transactions = based on the application, the number of concurrent transactions that can occur
• #Sessions = (1.1 * PROCESSES) + 5
• DML_LOCKS = #Transactions * (avg #SQLs per transaction)
• REPLICATION_DEPENDENCY_TRACKING: disable where replication is not required as skipping tracking generation will make read/write operations faster  

References

[1] http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::%3AP11_QUESTION_ID:6265095774206
[2] https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_wait_bottlenecks_latch_cache_buffer_chains_pct.html