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

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/

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