Category: DATABASES

Unix: Performance commands at O.S.

Posted on by

Performance related commands iostat , vmstat

Introduction to iostat , vmstat and netstat

This document is primarily written with reference to solaris performance monitoring and tuning but these tools are available in other unix variants also with slight syntax difference.

iostat , vmstat and netstat are three most commonly used tools for performance monitoring . These comes built in with the operating system and are easy to use .iostat stands for input output statistics and reports statistics for i/o devices such as disk drives . vmstat gives the statistics for virtual Memory and netstat gives the network statstics .

Following paragraphs describes these tools and their usage for performance monitoring.

Table of content :
1. Iostat
* Syntax
* example
* Result and Solutions

2. vmstat
* syntax
* example
* Result and Solutions

3. netstat
* syntax
* example
* Result and Solutions

Input Output statistics ( iostat )

iostat reports terminal and disk I/O activity and CPU utilization. The first line of output is for the time period since boot & each subsequent line is for the prior interval . Kernel maintains a number of counters to keep track of the values.

iostat’s activity class options default to tdc (terminal, disk, and CPU). If any other option/s are specified, this default is completely overridden i.e. iostat -d will report only statistics about the disks.

syntax:
Basic synctax is iostat interval count
option – let you specify the device for which information is needed like disk , cpu or terminal. (-d , -c , -t or -tdc ) . x options gives the extended statistics .

interval – is time period in seconds between two samples . iostat 4 will give data at each 4 seconds interval.

count – is the number of times the data is needed . iostat 4 5 will give data at 4 seconds interval 5 times

Example
$ iostat -xtc 10 5

extended disk statistics tty cpu
disk r/s w/s Kr/s Kw/s wait actv svc_t %w %b tin tout us sy wt id
sd0 2.6 3.0 20.7 22.7 0.1 0.2 59.2 6 19 0 84 3 85 11 0
sd1 4.2 1.0 33.5 8.0 0.0 0.2 47.2 2 23
sd2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
sd3 10.2 1.6 51.4 12.8 0.1 0.3 31.2 3 31

The fields have the following meanings:
disk name of the disk
r/s reads per second
w/s writes per second
Kr/s kilobytes read per second
Kw/s kilobytes written per second
wait average number of transactions waiting for service (Q length)
actv average number of transactions actively being serviced
(removed from the queue but not yet completed)
%w percent of time there are transactions waiting
for service (queue non-empty)
%b percent of time the disk is busy (transactions
in progress)
Results and Solutions
The values to look from the iostat output are:
* Reads/writes per second (r/s , w/s)
* Percentage busy (%b)
* Service time (svc_t)

If a disk shows consistently high reads/writes along with , the percentage busy (%b) of the disks is greater than 5 percent, and the average service time (svc_t) is greater than 30 milliseconds, then one of the following action needs to be taken

1.) Tune the application to use disk i/o more efficiently by modifying the disk queries and using available cache facilities of application servers .

2.) Spread the file system of the disk on to two or more disk using disk striping feature of volume manager /disksuite etc.

3.) Increase the system parameter values for inode cache , ufs_ninode , which is Number of inodes to be held in memory. Inodes are cached globally (for UFS), not on a per-file system basis

4.) Move the file system to another faster disk /controller or replace existing disk/controller to a faster one.
Virtual Memory Statistics ( vmstat )

vmstat

vmstat reports virtual memory statistics of process, virtual memory, disk, trap, and CPU activity.

On multicpu systems , vmstat averages the number of CPUs into the output. For per-process statistics .Without options, vmstat displays a one-line summary of the virtual memory activity since the system was booted.

syntax
Basic synctax is vmstat interval count

option – let you specify the type of information needed such as paging -p , cache -c ,.interrupt -i etc.

if no option is specified information about process , memory , paging , disk ,interrupts & cpu is displayed .

interval – is time period in seconds between two samples . vmstat 4 will give data at each 4 seconds interval.

count – is the number of times the data is needed . vmstat 4 5 will give data at 4 seconds interval 5 times.

Example
The following command displays a summary of what the system
is doing every five seconds.

# vmstat 5

procs memory page disk faults cpu
r b w swap free re mf pi p fr de sr s0 s1 s2 s3 in sy cs us sy id
0 0 0 11456 4120 1 41 19 1 3 0 2 0 4 0 0 48 112 130 4 14 82
0 0 1 10132 4280 0 4 44 0 0 0 0 0 23 0 0 211 230 144 3 35 62
0 0 1 10132 4616 0 0 20 0 0 0 0 0 19 0 0 150 172 146 3 33 64
0 0 1 10132 5292 0 0 9 0 0 0 0 0 21 0 0 165 105 130 1 21 78

The fields of vmstat’s display are
procs
r in run queue
b blocked for resources I/O, paging etc.
w swapped
memory (in Kbytes)
swap – amount of swap space currently available
free – size of the free list

page ( in units per second).
re page reclaims – see -S option for how this
field is modified.
mf minor faults – see -S option for how this
field is modified.
pi kilobytes paged in
po kilobytes paged out
fr kilobytes freed
de anticipated short-term memory shortfall (Kbytes)
sr pages scanned by clock algorithm
disk ( operations per second )
There are slots for up to four disks,
labeled with a single letter and number.
The letter indicates the type of disk
(s = SCSI, i = IPI, etc).
The number is the logical unit number.

faults
in (non clock) device interrupts
sy system calls
cs CPU context switches

cpu – breakdown of percentage usage of CPU time.
On multiprocessors this is an a
average across all processors.
us user time
sy system time
id idle time
Results and Solution from iostat

A. CPU issues
Following columns has to be watched to determine if there is any cpu issue

1. Processes in the run queue (procs r)
2. User time (cpu us)
3. System time (cpu sy)
4. Idle time (cpu id)

procs cpu
r b w us sy id
0 0 0 4 14 82
0 0 1 3 35 62
0 0 1 3 33 64
0 0 1 1 21 78
Problem symptoms

A.) Number of processes in run queue
1.) If the number of processes in run queue (procs r) are consistently greater than the number of CPUs on the system it will slow down system as there are more processes then available CPUs .
2.) if this number is more than four times the number of available CPUs in the system then system is facing shortage of cpu power and will greatly slow down the processess on the system.
3.) If the idle time (cpu id) is consistently 0 and if the system time (cpu sy) is double the user time (cpu us) system is facing shortage of CPU resources.

Resolution
Resolution to these kind of issues involves tuning of application procedures to make efficient use of cpu and as a last resort increasing the cpu power or adding more cpu to the system.

B. Memory Issues
Memory bottlenecks are determined by the scan rate (sr) . The scan rate is the pages scanned by the clock algorithm per second. If the scan rate (sr) is continuously over 200 pages per second then there is a memory shortage.

Resolution
1. Tune the applications & servers to make efficient use of memory and cache.
2. Increase system memory .
3. Implement priority paging in s in pre solaris 8 versions by adding line “set priority paging=1? in
/etc/system. Remove this line if upgrading from Solaris 7 to 8 & retaining old /etc/system file.

RAC: List all locked objects for all instances in RAC

Posted on by 
 
SET LINESIZE 230
SET PAGESIZE 300
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.inst_id,
b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a, gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4; Sample output:
Script which will generate the alter session kill command:
select blocking_session,blocked_session,script from 
 ( select distinct 
 s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ET=' || s1.last_call_et || 'sn. STATUS=' || s1.status || ' EVENT=' || s1.event || ' ACTION= ' || s1.action || ' PROGRAM=' || s1.program || ' MODULE=' || s1.module || ')' blocking_session,
 s2.username || '@' || s2.machine || ' ( INST=' || s2.inst_id || ' SID=' || s2.sid || ' ET=' || s2.last_call_et || 'sn. STATUS=' || s2.status || ' EVENT=' || s2.event || ' ACTION= ' || s2.action || ' PROGRAM=' || s2.program || ' MODULE=' || s2.module || ')' blocked_session,
 decode(s1.type,'USER','alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' || s1.inst_id || ''' immediate;' ,null)
 script ,
 count(*) over (partition by s1.inst_id,s1.sid) blocked_cnt
 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 )
 order by blocked_cnt desc;

Finding the SQL_id with other details about the blocking sessions.

 set lines 1234 pages 9999
  col inst_id for a10
  col serial# for a10
  col machine for a30
  col username for a10
  col event for a20
  col blocking_session for 999999
  col blocking_instance for 999999
  col status for a10
  col INST_ID for 9999
  col SERIAL# for 999999
  select inst_id,sid,serial#, machine, username, event, blocking_session, blocking_instance, status, sql_id 
  from gv$session where status ='ACTIVE'and username is not null and username!='SYS';

DB Admin: Used, auto and max space of datafile/s of given tablespace in Gb

Posted on by

Given tablespace used, auto and max in GB

set lines 200
set pagesize 150
break on report
compute sum of mbytes on report
col file_name format a70
select
file_id,
file_name,
bytes/1024/1024/1024 CURRENT_USED_GB,
autoextensible,
maxbytes/1024/1024/1024 CAN_GROW_TO_MAX_GB
from
dba_data_files
where
tablespace_name=upper('&Tbs_Name');

Sample output:

Tablespace usage history

Posted on by

Tablespace usage history of 7 days. 

select thedate,
gbsize,
prev_gbsize,
gbsize-prev_gbsize diff
from (
select thedate,
gbsize,
lag(gbsize,1) over (order by r) prev_gbsize
from (
select rownum r,
thedate,
gbsize
from (
select trunc(thedate) thedate,
max(gbsize) gbsize
from (
select to_date(to_char(snapshot.begin_interval_time,'YYYY-MON-DD HH24:MI:SS'),'YYYY-MON-DD HH24:MI:SS') thedate,
round((usage.tablespace_usedsize*block_size.value)/1024/1024/1024,2) gbsize
from dba_hist_tbspc_space_usage usage,
v$tablespace tablespace,
dba_hist_snapshot snapshot,
v$parameter block_size
where usage.snap_id = snapshot.snap_id
and usage.tablespace_id = tablespace.ts#
and tablespace.name = '&tablespace'
and block_size.name = 'db_block_size'
)
group by
trunc(thedate)
order by
trunc(thedate)
)
)
);

Enter value for Tablespace: TEMP

Sample Output:

THEDATE       GBSIZE PREV_GBSIZE       DIFF
——— ———- ———– ———-
15-OCT-15      13.04
16-OCT-15      11.21       13.04      -1.83
17-OCT-15        .91       11.21      -10.3
18-OCT-15          0         .91       -.91
19-OCT-15       6.44           0       6.44
20-OCT-15       4.45        6.44      -1.99
21-OCT-15      15.89        4.45      11.44
22-OCT-15       3.88       15.89     -12.01

8 rows selected.

DB Admin: Tablesapces size(MB), free(MB) % used and %free

Posted on by 
set pagesize 150
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

Sample output: 

Note: Least free % tablespace will 1st in output. Also it include temp tablespace/s.

DB Admin: Database size in GBs

Posted on by 
 
 SET SERVEROUTPUT ON
declare
total_size_b number;
tfree_size_b number;
tused_size_b number;
begin
dbms_output.enable(100000);
select Sum(bytes) into total_size_b
from dba_data_files;
select Sum(bytes) into tfree_size_b
from dba_free_space;
select Sum(bytes) into tused_size_b
from dba_segments;
dbms_output.put_line('Total:' || TO_CHAR(Round(total_size_b/1024/1024/1024, 2), '999,999.00') || ' GB');
dbms_output.put_line('Free: ' || TO_CHAR(Round(tfree_size_b/1024/1024/1024, 2), '999,999.00') || ' GB');
dbms_output.put_line('Used: ' || TO_CHAR(Round(tused_size_b/1024/1024/1024, 2), '999,999.00') || ' GB');
end;
/
SET SERVEROUTPUT ON

Sample output:

DB Admin: Database size in TBs

Posted on by 
SET SERVEROUTPUT ON
declare
total_size_b number;
tfree_size_b number;
tused_size_b number;
begin
dbms_output.enable(100000);
select
Sum(bytes) into total_size_b
from
dba_data_files;
select
Sum(bytes) into tfree_size_b
from
dba_free_space;
select
Sum(bytes) into tused_size_b
from
dba_segments;
dbms_output.put_line('Total:' || TO_CHAR(Round(total_size_b/1024/1024/1024/1024, 2), '999,999.00') || ' TB');
dbms_output.put_line('Free: ' || TO_CHAR(Round(tfree_size_b/1024/1024/1024/1024, 2), '999,999.00') || ' TB');
dbms_output.put_line('Used: ' || TO_CHAR(Round(tused_size_b/1024/1024/1024/1024, 2), '999,999.00') || ' TB');
end;
/
SET SERVEROUTPUT OFF

Database: Oracle: Tablesapces size(MB), free(MB) % used and %free

Posted on by 
 set pagesize 150
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

Note: Least free % tablespace will 1st in output. Also it include temp tablespace/s.


Fusion Middleware environment start and stop steps

Posted on by

t

1. Steps, in order, to start Fusion Middleware environment.

1. Start the database-based repository i.e. start the database.
set the correct ORACLE_HOME and ORACLE_SID
Start the Net Listener:
$ORACLE_HOME/bin/lsnrctl start
Start the database instance:

$ORACLE_HOME/bin/sqlplus
SQL> connect SYS as SYSDBA
SQL> startup

2. Start the Oracle WebLogic Server Administration Server.
You can start/stop WebLogic Server Administration Servers using the WLST command line or a script.
$DOMAIN_HOME/bin/startWebLogic.sh <

Note: While you start/stop also start/stop the processes running in the Administration Server including the WebLogic Server Administration Console and Fusion Middleware Control.

3. Ensure Node Manager is running. Below is the script to stat it.
$WLS_HOME/server/bin/startNodeManager.sh
OR
$DOMAIN_HOME/bin/startNodeManager.sh
Note: stopNodeManager.sh can be used to stop it.

4. Start Oracle Identity Management system components.
Set $ORACLE_HOME and $ORACLE_INSTANCE environment for Identity Management components.
Start/stop OPMN and all system components:
$ORACLE_INSTANCE/bin/opmnctl startall

Steps, in order, to stop Fusion Middleware environment

2. Steps, in order, to stop Fusion Middleware environment

1. Stop system components as Oracle HTTP Server etc.
Note: You can stop them in any order.
Set $ORACLE_HOME and $ORACLE_INSTANCE
$ORACLE_INSTANCE/bin/opmnctl stopall

To stop Oracle Management Agent, use the following command:

opmnctl stopproc ias-component=EMAGENT
2. Stop WebLogic Server Managed Servers. 
Note: Any applications deployed to the server are also stopped.
$DOMAIN_HOME/bin/startManagedWebLogic.sh
managed_server_name admin_url

3. Stop Oracle Identity Management components.
set $ORACLE_HOME environment variable to the Oracle home for the Identity Management components.
$ORACLE_INSTANCE/bin/opmnctl stopall

4.Stop the Administration Server.
You can start Server Administration Servers using the WLST command line or a script.

$DOMAIN_HOME/bin/bin/stopWebLogic.sh

Note: While you start/stop also start/stop the processes running in the Administration Server including the WebLogic Server Administration Console and Fusion Middleware Control.

5. Stop the database.
Set $ORACLE_HOME and $ORACLE_INSTANCE
connect SYS as SYSDBA
SQL> shutdown immediate ;