As a DBA, you must check Oracle database performance and generate database health check report on periodic basis to get overall idea of the database health.
Sometimes Oracle database performance degrades due to heavy load on the server and then users starts complaining Oracle database is running slow.
Oracle database performance depends on many factors like resource allocated to the database and the the load on the server. To check database health you can generate AWR report which will show you the resource consumption, load on the database, time and resource consuming slow running queries and many recommendations to tune queries, SGA, PGA and other memory components.
oracle database health check and monitoring scripts
Check Top Wait Events in Oracle Database
set lines 180
set pages 1000
col event format a50
PROMPT --> Top 25 Wait Events
select * from (
select inst_id,event,count() E_COUNT from gv$session_wait where event <> 'SQLNet message from client'
group by inst_id,event order by 3 desc)
where rownum < 26
order by 3
/
Check wait events and waiting session details
col sql format a35
col username format a20
col child format 999
col secs format 9999
col machine format a12
col event format a25
col state format a10
select /*+ rule */ distinct
w.sid,s.username,substr(w.event,1,25) event,substr(s.machine,1,12) machine,substr(w.state,1,10) state,s.SQL_ID,--q.CHILD_NUMBER CHILD,
substr(q.sql_text,1,33) "SQL",round(s.LAST_CALL_ET/60) "MINS", round(s.LAST_CALL_ET) "Sec"
from gv$session_wait w,gv$session s,gv$sql q where w.event like '%&event%'
and w.sid=s.sid
and s.SQL_HASH_VALUE=q.HASH_VALUE(+)
and s.status='ACTIVE'
and s.username is not null
and substr(w.event,1,25) not like 'SQL*Net message from clie%'
order by "MINS"
/
To Check Foreground Session Count
COLUMN inst_id FORMAT 999 HEADING "INST_ID"
prompt
prompt +----------------------------------------------------+
prompt | Check the Foreground session count|
prompt +----------------------------------------------------+
select count (*) ,status , inst_id from gv$session
where username is not null
group by status , inst_id order by 3;
Check Blocking Locks in Oracle
select l1.inst_id,l1.sid, ' IS BLOCKING ', l2.sid,l1.type,l2.type,l1.lmode,l2.lmode,l2.inst_id
from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;
Query to check CPU used by Session
COLUMN value FORMAT 99999999999 HEADING "VALUE"
prompt
prompt +----------------------------------------------------+
prompt | CPU USED BY SESSION
prompt +----------------------------------------------------+
SELECT n.username, s.sid, s.value/100 cpu_usage_seconds FROM v$sesstat s,v$statname t, v$session n
WHERE s.statistic# = t.statistic# and ROWNUM <= 10
AND n.sid = s.sid
AND t.name='CPU used by this session'
and n.status='ACTIVE'
ORDER BY s.value desc;
SQL Ordered by CPU Usage
rem
rem SQL by CPU Usage (v$sqlarea)
rem
column sql_text format a40 word_wrapped heading 'SQL|Text'
column cpu_time heading 'CPU|Time'
column elapsed_time heading 'Elapsed|Time'
column disk_reads heading 'Disk|Reads'
column buffer_gets heading 'Buffer|Gets'
column rows_processed heading 'Rows|Processed'
set pages 55 lines 132
--ttitle 'SQL By CPU Usage'
prompt
prompt +----------------------------------------------------+
prompt | SQL BY CPU USAGE
prompt +----------------------------------------------------+
select * from
(select sql_text,sql_id,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 10
/
Redo Generated by Session
set pages 22 lines 80
---ttitle off
COLUMN value FORMAT 999999999999 HEADING "value"
prompt
prompt +----------------------------------------------------+
prompt | AMOUNT OF REDO GENERATED BY SESSION
prompt +----------------------------------------------------+
select sid, value
from v$sesstat s, v$statname n
where n.statistic# = s.statistic#
and n.name = 'redo size'
and rownum <=10
order by value desc;
Database Wait time Ratio
SET LINESIZE 1000
SET PAGESIZE 1000
COLUMN metric_name FORMAT a40 HEADING 'METRIC_NAME'
COLUMN metric_unit FORMAT a30 HEADING 'METRIC_UNIT'
COLUMN inst_id FORMAT 99 HEADING 'inst_id' JUSTIFY right
COLUMN intsize_csec FORMAT 999999999.99 HEADING 'INTSIZE_CSEC' JUSTIFY right
COLUMN VALUE FORMAT 999999999.99 HEADING 'VALUE' JUSTIFY right
COLUMN BEGIN_TIME FORMAT 9999999 HEADING 'begin_time' JUSTIFY right
COLUMN end_time FORMAT a20 HEADING 'end_time' TRUNC
prompt
prompt +----------------------------------------------------+
prompt | Validating the Database and Wait Time ratio |
prompt +----------------------------------------------------+
select METRIC_NAME,metric_unit,INST_ID,INTSIZE_CSEC,
VALUE,begin_time ,end_time
from GV$SYSMETRIC
where METRIC_NAME IN ('Database CPU Time Ratio',
'Database Wait Time Ratio')
AND
(INTSIZE_CSEC ,inst_id ) in
(select max(INTSIZE_CSEC),inst_id from GV$SYSMETRIC group by inst_id ) order by 3 ;
Wait class and their Waiting time
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN wait_class FORMAT a20 HEADING 'WAIT_CLASS'
COLUMN inst_id FORMAT 99 HEADING 'INST_ID' JUSTIFY right
COLUMN BEGIN_TIME FORMAT 9999999 HEADING 'BEGIN_TIME' JUSTIFY left
COLUMN end_time FORMAT 9999999 HEADING 'END_TIME' JUSTIFY left
COLUMN dbtime_in_wait FORMAT 9999.99 HEADING 'DBTIME_IN_WAIT' JUSTIFY right
COLUMN time_waited FORMAT 9999999.99 HEADING 'TIME_WAITED' JUSTIFY right
prompt
prompt +----------------------------------------------------+
prompt | The waits are from which wait classes|
prompt +----------------------------------------------------+
select b.wait_class ,a.inst_id ,a.begin_time ,
a.end_time , a.dbtime_in_wait , a.time_waited from GV$WAITCLASSMETRIC a , gV$SYSTEM_WAIT_CLASS b where
a.wait_class_id = b.wait_class_id
and a.inst_id =1
and a.inst_id = b.inst_id
--- and b.wait_class='Commit'
order by 5 desc ;
Check Temp Tablespace Useage
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
Check Tablespace Utilization
set line 1000;
col file_id for a6;
col file_name for a55;
col TABLESPACE_NAME for a30;
set pagesize 128;
set feedback off;
select a.TABLESPACE_NAME,
a.BYTES/1024/1024/1024 GB_used,
b.BYTES/1024/1024/1024 GB_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc
Using the above query you will get the overall idea about database performance. You can also generate AWR report and dig more into it. Database health check also depends on the expertise of the individual. So please collect all the inputs and figure out the root cause.