The most crutial performance indicator of database is the response time.
response time=processing time + wait time.
If the wait time is more within the system, we need to investigate further and we need to identify the wait events that are causing slowness of the application.
Every oracle wait event falls in any one of the following classes :
1.application(due to locks)
2.user I/O(database files sequential read, database files scatter read etc)
3.commit(lock file sink)
4.idle
5.network
6.system I/O
7.administrative(rebuilding of indexes)
8.concurrency
9.cluster
Note : The only event that falls under commit class is "lock file sink".
Sometimes we may notice a message "checkpoint not complete" in the alert log.
Reasons for this message could be
1.Archiver might not archived the content of active online redo log file.
2.DBWR might not have written the dirty buffers to respective datafiles.
Solution : Add one more redo log group. Increase the number of DBWR processes (dbwr_io_slave_processes).
If sombody complains about the performance, first check the behaviour of the Operating System(cpu
utilisation, memory utilisation, swap usage etc) by executing "top" command or "sar" command(system activity report).
oralce@venu1>top -d .5
sys>select metric_name,value from sys.v_$sysmetric where metric_name in('Database Cpu Time Ratio','Database Wait Time Ratio') and intsize_csec=(select max(intsize_csec) from sys.v_$sysmetric);
sys>save check.sql
scott>select * from tab;
scott>show user
scott>update emp set sal=500 where empno=7902; -- emp contains 10 million records.
another terminal :
scott>update emp set sal=100 where empno=7902;
sys>@check.sql
Note : In the above query, if database wait time ratio is greater than database cpu time ratio, we need to investigate further to understand that wait events within the system.
sys>desc v$session_wait_class
sys>select sid,username from v$session where username in('SYS','SCOTT');
sys>select sid,username from v$session where username='SCOTT';
sys>select wait_class,sum(time_waited),sum(time_waited)/sum(total_waits) sum_Waits from v$system_wait_class group by wait_class;
sys>save wait.sql
To understand the sessions that are experiencing waits.
sys>select sid,event,seconds_in_wait from v$session_wait where sid in(select sid from v$session where username is not NULL);
sys>save wait1.sql
enq : TX - row lock contension
Generally, we come across this when there are locks on the objects.
enq : TM - row lock contension
Generally, we come across this whenever there are missing foreign key constraints in the child table.
sys>select blocking_session,sid,wait_class,seconds_in_wait from v$session where blocking_session is not NULL order by blocking_session;
sys>save blocking.sql
sys>desc v$lock
sys>select sid,lmode,request,block from v$lock;
sys>select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.owner,b.machine from v
$locked_object a,v$session b,dba_objects c where b.sid=a.session_id and a.object_id=c.object_id;
sys>save blocking1.sql
another terminal :
scott>select * from emp;
sys>@wait1.sql
another terminal :
scott>select e.ename,d.dname,d.deptno from emp e,dept d where e.empno=d.deptno;
sys>@wait1.sql
another terminal :
scott>update emp set sal=100;
sys>@wait1.sql
sys>desc v$session_wait
