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;
