Category Archives: Resources contention

Latch contention

Library cache latch contention is typically caused by NOT using bind variables. It is due to excessive parsing of statements.

Cache buffers chain latches contention is typically caused by concurrent access to a very “hot” block and the most common type of such a hot block is an index root or branch block (since any index based query must access the root block).

Redo copy/redo allocation latches contention are rarely encountered today. They protect the redolog buffer

A useful and very complete article/scripts on latch contention by Tanel Poder (Oracle 9i to 12c):

http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting

The scripts:



Vincent Fenoll Oracle Database Administrator in Montreal

SQL Informations

The Oracle database administrator knows the pid of an Oracle resource-intensive process. Now we want to gather some informations on the SQL Statement (SQL_ID, username, program, terminal…) before running an explain plan.

SELECT    'USERNAME : ' || s.username  || CHR (10)   
		|| 'SCHEMA : '  || s.schemaname || CHR (10) 
		|| 'OSUSER : '  || s.osuser    || CHR (10)  
		|| 'PROGRAM : ' || s.program   || CHR (10)  
		|| 'MACHINE : ' || s.machine   || CHR (10)  
		|| 'TERMINAL : ' || s.terminal  || CHR (10)  
		|| 'SPID : '    || p.spid      || CHR (10)  
		|| 'SID : '     || s.sid       || CHR (10)  
		|| 'SERIAL# : ' || s.serial#   || CHR (10)  
		|| 'TYPE : '    || s.TYPE      || CHR (10)  
		|| 'SQL ID : '  || q.sql_id    || CHR (10)  
		|| 'CHILD_NUMBER : '  || q.child_number    || CHR (10)  
		|| 'SQL TEXT : ' || q.sql_text 
          RESULT
  FROM v$session s, v$process p, v$sql q
 WHERE s.paddr = p.addr AND s.sql_id = q.sql_id(+) AND p.spid = '&&MY_PID';
 

To find the cpu intensive queries:
http://www.oracle-scripts.net/cpu-issues/

To display the Oracle explain plan:
http://www.oracle-scripts.net/display-cursor/

Vincent Fenoll – Montreal

I/O Bottlenecks

Our Oracle databases in Montreal are sometimes experiencing performance problems and the Oracle DBA wants to know if there are I/O Bottlenecks.

 

If working with a zone (a virtual operating system abstraction), first you will have to find the disk:

$ zpool status
  pool: rpool
 state: ONLINE
  scan: none requested
config:

        NAME    STATE     READ WRITE CKSUM
        rpool   ONLINE       0     0     0
          c1d6  ONLINE       0     0     0		<--- keep"d6"

$  iostat -xd 5

r/s:  Reads/second
w/s:  Writes/second
kr/s: kilobytes read per second
kw/s: kilobytes written per second
wait: average number of transactions waiting for service (queue length)
actv: average number of transactions actively being serviced
svc_t:average response time  of  transactions,  in  milliseconds
%w:   percent of time there are transactions waiting for service
%b:  percent of time the disk is busy

                  extended device statistics
device      r/s    w/s   kr/s   kw/s wait actv  svc_t  %w  %b
1/md1      56.9    0.0 2104.5    0.0  0.0  0.4    7.9   0  32
1/md0      56.9    0.0 2104.5    0.0  0.0  0.4    7.9   0  32
1/md6      56.9    0.0 2104.5    0.0  0.0  0.4    7.9   0  32
2/md1       0.0   10.8    0.0   86.6  0.0  0.2   16.3   0  18
2/md0       0.0   10.8    0.0   86.6  0.0  0.3   28.1   0  18
2/md210     0.0    0.2    0.0    1.6  0.0  0.0    4.2   0   0

If working with another type of virtual zone, you will have difficulties to find which disks are experiencing high I/O activity.

But hopefully, you can have some help with SQL*Plus:

(1) Look for sessions that are currently waiting for I/O resources:
SELECT username,
       program,
       machine,
       sql_id
  FROM v$session
 WHERE event LIKE 'db file%read';

(2) which SQL statements are using a lots of disks:

 col schema format a20
 SELECT *
  FROM (  SELECT parsing_schema_name Schema, SQL_ID,
                 SUBSTR (sql_text, 1, 75) SQL,
                 disk_reads
            FROM v$sql
        ORDER BY disk_reads DESC)
 WHERE ROWNUM < 20;

 And with the result set of (1) or (2):
 set long 1000
 select SQL_FULLTEXT from v$sql where sql_id='xxxxxxxxxxx'

--> Tune your SQL Statement or execute it less times.

After that you can also check the global Oracle wait events:
http://www.oracle-scripts.net/resources-waits/

CPU Issues

The DBAs want to know if there are performance issues with cpu in an Oracle database for example:


First:
$ ps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head
%CPU    PID     USER TT      COMMAND
 55.3 934757   oracle ?       oracleGdefgP (LOCAL=NO)
 10.1 935480   oracle ?       oracleGdefgP (LOCAL=NO)
 0.1 935247   oracle ?       oracleGdefgP (LOCAL=NO)
 0.1 935041   oracle ?       oracleGdefgP (LOCAL=NO)

(you can make an alias with this command)

Linux or other UNIX:
$ top
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14848 oracle 25 0 188m 13m 15m R 100.1 0.2 33661:45 oracle
14853 oracle 25 0 188m 13m 15m R 99.8 0.2 313225:41 oracle
9725 oracle 18 0 1045m 287m 150m R 58.6 3.8 0:41.89 oracle

$ ps -ef | grep 14848
--> Tune application or SQL


Solaris:
$ prstat
   PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
180478 oracle   1239M 1230M sleep   59    0  10:24:37 100.0% oracle/1
180500 oracle   1245M 1235M sleep   59    0   4:34:17 20.0% oracle/11
180502 oracle   1234M 1224M sleep   59    0   2:30:03 0.0% oracle/1

$ ps -ef | grep 180478
--> Tune application or SQL

Vincent Fenoll – Oracle DBA in Montreal (QC) Canada

Wait time percentage

How the Oracle DBA can find the Wait time percentage of the total DB time during the last hour.

DBA Oracle
Compatibility: Oracle 11g
Creator: Cyrille MODIANO – Montreal

SELECT
  metric_name,
  ROUND(AVG(value),1) "Average Wait time %"
FROM
   v$sysmetric_history
WHERE metric_name = 'Database Wait Time Ratio'
GROUP BY metric_name;