Oracle database performance health check



Database Troubleshooting
Assessing database availability issues quickly
• Identifying system performance issues with OS utilities
• Querying data dictionary views to display resource-intensive SQL statements
• Using Oracle performance tools to identify resource-consuming SQL statements
• Identifying and resolving locking issues
• Troubleshooting open-cursor issues
• Investigating issues with the undo and temporary tablespaces

 1.Checking Database Availability
2.Investigating Disk Fullness
3.Identifying System Bottlenecks

 When inspecting the end of the alert.log, look for errors that indicate these types of issues:
Archiver pr • ocess hung, owing to inadequate disk space
• File system out of space
• Tablespace out of space
• Running out of memory in the buffer cache or shared pool
   Media error indicating that a data file is missing or damaged
• Error indicating an issue with writing an archive redo log;
Using vmstat
The vmstat utility displays real-time performance information about processes, memory, paging, disk I/O, and CPU

 check the oracle performce

 Here, it’s used to identify the top Oracle
memory–using processes:

 $ ps -e -o pmem,pid,user,tty,args | grep -i oracle | sort -n -k 1 -r | head
You can also run top using the b (batch mode) option and send the output to a file for later analysis:
$ top –b > tophat.out
While running in batch mode, the top command will run until you kill it (with Ctrl+C) or until it reaches a
specified number of iterations. You could run the previous top command in batch mode
it’s used to identify the top Oracle
memory–using processes:
$ ps -e -o pmem,pid,user,tty,args | grep -i oracle | sort -n -k 1 -r | head

use the following SQL statement to determine what type of program is associated with
the OS process 

 select
'USERNAME : ' || s.username|| chr(10) ||
'OSUSER : ' || s.osuser || chr(10) ||
'PROGRAM : ' || s.program || chr(10) ||
'SPID : ' || p.spid || chr(10) ||
'SID : ' || s.sid || chr(10) ||
'SERIAL# : ' || s.serial# || chr(10) ||
'MACHINE : ' || s.machine || chr(10) ||
'TERMINAL : ' || s.terminal
from v$session s,
v$process p
where s.paddr = p.addr
and p.spid = &PID_FROM_OS;

select
'USERNAME : ' || s.username || chr(10) ||
'OSUSER : ' || s.osuser || chr(10) ||
'PROGRAM : ' || s.program || chr(10) ||
'SPID : ' || p.spid || chr(10) ||
'SID : ' || s.sid || chr(10) ||
'SERIAL# : ' || s.serial# || chr(10) ||
'MACHINE : ' || s.machine || chr(10) ||
'TERMINAL : ' || s.terminal || chr(10) ||
'SQL TEXT : ' || sa.sql_text
from v$process p,v$session s,
v$sqlarea sa
where p.addr = s.paddr
and s.username is not null
and s.sql_address = sa.address(+)
and s.sql_hash_value = sa.hash_value(+)
and p.spid= &PID_FROM_OS;

 Finding Resource-Intensive SQL Statements

 Monitoring Real-Time SQL Execution Statistics

 select * from (
select a.sid session_id, a.sql_id
,a.status
,a.cpu_time/1000000 cpu_sec
,a.buffer_gets, a.disk_reads
,b.sql_text sql_text
from v$sql_monitor a
,v$sql b
where a.sql_id = b.sql_id
order by a.cpu_time desc)
where rownum <=20;

 The next SQL statement monitors currently executing queries, ordered by the number of disk
reads:

 select * from (
select a.sid session_id, a.sql_id, a.status
,a.cpu_time/1000000 cpu_sec
,a.buffer_gets, a.disk_reads
,substr(b.sql_text,1,35) sql_text
from v$sql_monitor a
,v$sql b
where a.sql_id = b.sql_id
and a.status='EXECUTING'
order by a.disk_reads desc)
where rownum <=20;
use the following query to identify the ten most resource-intensive queries, based
on CPU time:

 select * from(
select s.sid, s.username, s.sql_id
,sa.elapsed_time/1000000, sa.cpu_time/1000000
,sa.buffer_gets, sa.sql_text
from v$sqlarea sa
,v$session s
where s.sql_hash_value = sa.hash_value
and s.sql_address = sa.address
and s.username is not null
order by sa.cpu_time desc)
where rownum <= 10;
Running Oracle Diagnostic Utilities
Oracle provides several utilities for diagnosing database performance issues:
• Automatic workload repository (AWR)
• Automatic database diagnostic monitor (ADDM)
• Active session history (ASH)
• Statspack

 Detecting and Resolving Locking Issues

 Space-related issue (e.g., the archive redo destination is full and has suspended all
transactions).
• A process has a lock on a table row and is not committing or rolling back, thus preventing
another session from modifying the same row.

 This query shows information such as the locking session SQL statement and the waiting
SQL statement:
set lines 80
col blkg_user form a10
col blkg_machine form a10
col blkg_sid form 99999999
col wait_user form a10
col wait_machine form a10
col wait_sid form 9999999
col obj_own form a10
col obj_name form a10
col blkg_sql form a50
col wait_sql form a50
select
s1.username blkg_user, s1.machine blkg_machine
,s1.sid blkg_sid, s1.serial# blkg_serialnum
,s1.process blkg_OS_PID
,substr(b1.sql_text,1,50) blkg_sql
,chr(10)
,s2.username wait_user, s2.machine wait_machine
,s2.sid wait_sid, s2.serial# wait_serialnum
,s2.process wait_OS_PID
,substr(w1.sql_text,1,50) wait_sql
,lo.object_id blkd_obj_id
,do.owner obj_own, do.object_name obj_name
from v$lock l1
,v$session s1
,v$lock l2
,v$session s2
,v$locked_object lo
,v$sqlarea b1
,v$sqlarea w1
,dba_objects do
where s1.sid = l1.sid
and s2.sid = l2.sid
and l1.id1 = l2.id1
and s1.sid = lo.session_id
and lo.object_id = do.object_id
and l1.block = 1
and s1.prev_sql_addr = b1.address
and s2.sql_address = w1.address
and l2.request > 0;

 Viewing SQL That Is Consuming Undo Space

 identify which users are consuming space in the undo tablespace. Run this query
to report on basic information regarding space allocated on a per-user basis:
select s.sid, s.serial#, s.osuser, s.logon_time
,s.status, s.machine
,t.used_ublk, t.used_ublk*16384/1024/1024 undo_usage_mb
from v$session s
,v$transaction t
where t.addr = s.taddr

 SQL statement associated with a user consuming undo space, then join to V$SQL,
as shown:
select s.sid, s.serial#, s.osuser, s.logon_time, s.status
,s.machine, t.used_ublk
,t.used_ublk*16384/1024/1024 undo_usage_mb
,q.sql_text
from v$session s
,v$transaction t
,v$sql q
where t.addr = s.taddr
and s.sql_id = q.sql_id;

 Determining if Temporary Tablespace Is Sized Correctly

 space within the temporary tablespace:

 select tablespace_name
,tablespace_size/1024/1024 mb_size
,allocated_space/1024/1024 mb_alloc
,free_space/1024/1024 mb_free
from dba_temp_free_space;

 Viewing SQL That Is Consuming Temporary Space

 SELECT s.sid, s.serial#, s.username
,p.spid, s.module, p.program
,SUM(su.blocks) * tbsp.block_size/1024/1024 mb_used
,su.tablespace
FROM v$sort_usage su
,v$session s
,dba_tablespaces tbsp
,v$process p
WHERE su.session_addr = s.saddr
AND su.tablespace = tbsp.tablespace_name
AND s.paddr = p.addr
GROUP BY
s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,
p.program, tbsp.block_size, su.tablespace
ORDER BY s.sid;

Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Blocking session on RAC database

Blocking session on RAC database Blocking session of the databases Generate the report for Blocking session The SQL will provide Blocking sessions with SID, node information SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID AND L1.BLOCK > 0 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2; Blockers information select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s...
Read more

Finding object size in Oracle database

Ref: http://www.runningoracle.com/product_info.php?cPath=2_44&products_id=153 Finding object size in Oracle database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily with TOAD SQL Editor. For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte)  SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name  FROM DBA_SEGMENTS  WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',  'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY')  --AND TABLESPACE_NAME LIKE 'COSTE%'  --AND SEGMENT_NAME LIKE 'P2010201%'  --AND partition_name LIKE 'P20100201%' --AND segment_type = 'TABLE' --AND OWNER = 'TARG...
Read more