Category Archives: Unix

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

Disk Space Full

When a disk is out os space, the database hungs or is unreachable and the Oracle DBA wants to find where are the large files to move or delete them. For example:


$ df –h
--> show 100% used

-- Find first 15 large files:
$ cd "My file system full"
$ find . -ls | sort -nrk7 | head -15
montreal.zip
quebec.zip
laval.zip
saint-eustache.zip
oracle-dba-montreal.zip
results.zip

-- Find first 15 large folders on Solaris (useful for small but big amount of files )
$ du -o . | sort -nr | head -15
(other O/S replace -o by -S)

Author: Vincent Fenoll – Montreal

Find Replace

I want to do a simple find replace of a string in one line for all the files in a Unix directory

Useful when the Oracle DBA has to update a lots of SQL or shell scripts.

Example of a find replace: ~/sql directory has thousands of SQL files and I’d like to find out my_source_string and replace it with my_target_string:

find my_folder  -type f | xargs perl -pi -e 's/my_source_string/my_target_string/g'

Oracle Montreal find replace Unix string

MassExecuteSQL

I have some standard SQL that I want to run against multiple databases on a single server to help me report informations or diagnose problems.

This script intended for Oracle DBA, executes an sql statement on all Oracle 18c databases running on a Unix server.

First example:       On all databases.
Second example: On a list of ORACLE_SID

 

Let’s do that!

-- ################################################
-- # Creator: Cyrille Modiano (DBA Oracle
-- # Created: 2011/09/08 Last Validation: sept. 2018
-- # Name: MassExecuteSQL
-- ################################################
-- #
-- # Compatible: Oracle 18c, 12c, 11g, 10g, 8i, 9i
-- #
-- ################################################

for base in `ps -ef | grep pmon | grep -v grep | awk -F_ '{print $3}'`
do
export ORACLE_SID=$base
export ORAENV_ASK=NO
. oraenv
echo Database $base : >> resultat.log
echo "          " >> resultat.log
echo "select status from v\$instance;" | sqlplus -s "/ as sysdba" >> resultat.log
done

# With a list of databases

for base in BASE1 BASE2 BASE3 BASE4 BASE5
do
export ORACLE_SID=$base
export ORAENV_ASK=NO
. oraenv
echo Database $base : >> resultat.log
echo "          " >> resultat.log
echo "select status from v\$instance;" | sqlplus -s "/ as sysdba" >> resultat.log
done