oracleperf

Training on 13/06

tuning, two things to look at: wait events and statistics

80% of AWR report is for Oracle

Automatic perfomance tuning features:
-AWR Automatic Workload Repository
-Automatic Db diagnostic monitor (ADDM)

Top 10 foreground events by Total Wait Time

@v_sys_time_model

list of waits:
select name from $v_sys_time_model

enq:TX - row lock contention (used while insert, update, etc)
log file sync: wit for commit (no more than 4ms)

desc v$system_event

!ls v*

top20 wait reason
+-----------------------------------+
DB time = DB CPU time + DB wait time
+-----------------------------------+

v$sysstat
select name, value from v$sysstat order from 1;

check for sorts in disk, memory

Displaying SGA stattistics:
SELECT * FROM v$sgainfo;

BUFFER BUSY WAITS
=================
---> Increase PCTFREE (per table) - to fix buffer busy waits
!ls *seg*
v$segment_statistics

desc dba_tables -> PCT_FREE

SHRINK SPACE COMPACT
SHRINK SPACE

FREE BUFFER WAIT
================

solution increase buffer cache
increase DBWR

LATCHES
======

increase memory

if a table has many users updating, then we do partioning