Frequently Used Oracle DBA Commands

******************************

Command to find sql from sql id:

select sql_text from v$sqltext where sql_id='9rfvrjph5vun2' order by PIECE ;

*******************************

Command to find space usage in a tablespace:

undef ts_name
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from  dba_data_files where tablespace_name = '&&TS_NAME' group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from  dba_free_space where tablespace_name = '&TS_NAME' group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4;

***************************************

Command to find datafiles in a specific tablespace:

set lines 300
col file_name for a50
select file_id,file_name,bytes/1024/1024,autoextensible from dba_data_files where tablespace_name='&TS_NAME';

****************************************

Command to find currently running long operations:

set lines 300
set pages 2000
col message for a50
select sid,serial#,message,sofar,totalwork,(sofar/totalwork)*100 "pct",ELAPSED_SECONDS/60/60,TIME_REMAINING/60/60 from v$session_longops where TIME_REMAINING<>0 and sofar<>totalwork ;

***************************************

Command to gather statistics: (schema level)

exec DBMS_STATS.gather_schema_stats (ownname => 'SCOTT', cascade =>true,estimate_percent => dbms_stats.auto_sample_size);

******************************************

Command to check backup availability:

set feedback off
col Name format a14
col BACKUP_TYPE format a11
col OPERATION format a15
col START_TIME format a22
set line 150

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY  HH24:MI:SS';

select a.Name ,COMMAND_ID  ,OPERATION,STATUS,MBYTES_PROCESSED,START_TIME,END_TIME from  V$RMAN_STATUS , v$database a where OBJECT_TYPE='DB FULL' and OPERATION='BACKUP'  and trunc(START_TIME)>trunc(sysdate)-1

********************************************

Command to get os process related to sid:

col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr and p.spid=15107
/

***************************************
Script to compress exp dmp file on the fly:

Use the following technique if you use an Oracle version prior to 8i:

Create a compressed export on the fly. Depending on the type of data, you probably can export up to 10 gigabytes to a single file. This example uses gzip. It offers the best compression I know of, but you can also substitute it with zip, compress or whatever.

# create a named pipe
mknod exp.pipe p
# read the pipe - output to zip file in the background
gzip < exp.pipe > scott.exp.gz &
# feed the pipe
exp userid=scott/tiger file=exp.pipe ...

Import directly from a compressed export:

# create a  name pipe
mknod imp_pipe p
# read the zip file and output to pipe
gunzip < exp_file.dmp.gz > imp_pipe &
# feed the pipe
imp system/pwd@sid file=imp_pipe log=imp_pipe.log ...

In case of low-performance system, it is better to add RECORDLENGTH parameter with tiny value to ensure that gzip has enough time to extract data before imp reads it:

imp system/pwd@sid RECORDLENGTH=4096 file=imp_pipe log=imp_pipe.log ...

***********************************
Command to get redo log count hourly:

col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999

select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1
/

*************************************

Command to find segments causing redo log  generaion:

SELECT to_char(begin_interval_time,'YY-MM-DD HH24') snap_time,
 dhso.object_name,
 sum(db_block_changes_delta) BLOCK_CHANGED
 FROM dba_hist_seg_stat dhss,
 dba_hist_seg_stat_obj dhso,
 dba_hist_snapshot dhs
 WHERE dhs.snap_id = dhss.snap_id
 AND dhs.instance_number = dhss.instance_number
 AND dhss.obj# = dhso.obj#
 AND dhss.dataobj# = dhso.dataobj#
 AND begin_interval_time BETWEEN to_date('12-02-12 08:00','YY-MM-DD HH24:MI')
 AND to_date('12-02-13 08:00','YY-MM-DD HH24:MI')
 GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'),
 dhso.object_name
 HAVING sum(db_block_changes_delta) > 0
ORDER BY sum(db_block_changes_delta) desc ;

***********************************
To find process utilization hourly:

select to_char(BEGIN_TIME,'dd-mon-yyyy hh24:mi:ss') begin_time, to_char(END_TIME,'dd-mon-yyyy hh24:mi:ss') end_time, MAXVAL*5000/100 max_process_count, ROUND(MAXVAL,2) "limit_reached (%age)"
from dba_hist_sysmetric_summary
where trunc(end_time) = '15-MAR-2013' and metric_id = 2118
order by 1;

*************************************

Command to find used and free memory:

select round(used.bytes /1024/1024 ,2) used_mb
, round(free.bytes /1024/1024 ,2) free_mb
, round(tot.bytes /1024/1024 ,2) total_mb
from (select sum(bytes) bytes
from v$sgastat
where name != 'free memory') used
, (select sum(bytes) bytes
from v$sgastat
where name = 'free memory') free
, (select sum(bytes) bytes
from v$sgastat) tot
/

**************************************
Note - The actual PGA usage might hit and go above those limits. The current allocation can be queried.

Command to find current allocation of sga and pga:

select round(sum(bytes)/1024/1024/1024,3) SGA_G
  from v$sgastat;

select round(value/1024/1024/1024,3) PGA_G
  from v$pgastat
 where name = 'total PGA allocated';

 *************************************
Command to find current Open cursor count:
 select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
 and b.name = 'opened cursors current'
and p.name= 'open_cursors'
 group by p.value;

*****************************************
Command to find current utilization of processes and sessions:
set lines 300
select * from v$resource_limit;

*************************************
Command to find CPU usage for active sessions:
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN username FORMAT A30
COLUMN sid FORMAT 999,999,999
COLUMN serial# FORMAT 999,999,999
COLUMN "cpu usage (seconds)"  FORMAT 999,999,999.0000

SELECT
   s.username,
   t.sid,
   s.serial#,
   SUM(VALUE/100) as "cpu usage (seconds)"
FROM
   v$session s,
   v$sesstat t,
   v$statname n
WHERE
   t.STATISTIC# = n.STATISTIC#
AND
   NAME like '%CPU used by this session%'
AND
   t.SID = s.SID
AND
   s.status='ACTIVE'
AND
   s.username is not null
GROUP BY username,t.sid,s.serial#
/

************************************
Command to check services in RAC in tabular format:
cd <CRS_HOME>/bin

./crs_stat | awk -F= '/NAME=/{n=$2}/TYPE=/{t=$2}/TARGET=/{g=$2}/STATE=/{s=$2; printf("%-75s%-18s%-15s%-30s\n", n,t,g,s)}'

************************************
To check histogram on a table:
select distinct histogram from dba_tab_col_statistics where table_name = '<table_name>';
************************************
To check resources in a database:
set lines 1000
set pages 1000
select * from v$resource_limit;

In RAC environment, below statement gives PROCESSES and SESSIONS information all all instances.
set lines 1000
set pages 1000
select * from gv$resource_limit where RESOURCE_NAME in ('processes','sessions');
************************************
Find blocking sessions with v$session:
set lines 300
set pages 300
SELECT inst_id,
   s.blocking_session,
   s.sid,
   s.serial#, username,status,
   s.seconds_in_wait
FROM
   gv$session s
WHERE
   blocking_session IS NOT NULL;

Find blocking sessions using v$lock:
SELECT
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM
   gv$lock l1, gv$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;

Command to identify locked tables:
set lines 180
col object_name for a40
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
    a.object_id = c.object_id;


select a.sid, a.serial#,sql_id ,(case LOCKED_MODE   when 0 then 'none'
  when 1 then  'null'
   when  2 then 'row-S'
  when 3 then 'row-x'
  when 4 then 'share'
  when 5 then 'S/Row-X'
  when  6 then 'exclusive'
   end ) LOCKED_MODE
    from v$session a, v$locked_object b, dba_objects c
  where b.object_id = c.object_id
  and a.sid = b.session_id
   and OBJECT_NAME='XYS';


Command to identify blocked objects:
SELECT sid, id1 FROM v$lock WHERE TYPE='TM';

****************************
To check wait events on a particular SID:
select SID,EVENT,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session_wait where sid=<sid>;

To check wait events on all sessions:
set lines 300
select a.SID,b.serial#,b.username,b.status,b.sql_id, a.EVENT,a.WAIT_TIME,a.SECONDS_IN_WAIT,a.STATE from v$session_wait a,v$session b where a.sid=b.sid ;

****************************
To find complete query using sql id:
select sql_text from v$sqltext where sql_id='2vgdsq0w31qgy' order by PIECE ;
****************************
To check CPU usage by each ACTIVE session:
set pages 1000
set lines 300
select
   ss.username,
   se.SID,ss.serial#,
   VALUE/100 cpu_usage_seconds
from
   v$session ss,
   v$sesstat se,
   v$statname sn
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like '%CPU used by this session%'
and
   se.SID = ss.SID
and
   ss.status='ACTIVE'
and
   ss.username is not null
order by VALUE desc ;


Select username,v.sid||','||v.serial# sid_session,round((s.value/y.value)*100,2) cpu_used_percentage
from v$session v, v$sesstat s,v$sysstat y
where v.username is not null
and v.sid = s.sid
and s.statistic#=y.statistic#
and y. name = 'CPU used by this session'
Order by username,3 desc ;

*********************************
To get the information of min and max time of ACTIVE SESSION HISTORY:
select min(sample_time), max(sample_time) from sys.WRH$_ACTIVE_SESSION_HISTORY ;
select min(sample_time), max(sample_time) from v$ACTIVE_SESSION_HISTORY ;

*********************************
To get the DDL of a procedure:
SELECT dbms_metadata.get_ddl('PROCOBJ','YOURJOBNAME', 'YOURJOBOWNER') from dual;

*********************************
Commands to reset a sequence value:
ALTER SEQUENCE serial INCREMENT BY -400;
SELECT serial.NEXTVAL FROM foo;
ALTER SEQUENCE serial INCREMENT BY 1;


To get the IO of each session:
set pagesize 9999
set linesize 140
column username  format a10
column sid       format 9999
column serial#   format 99999
column actv      format a2
column sess_mi   format 9,999.9
column last      format 9,999.99
column TotGets   format 999,999.999
column phyRds    format 999,999
column "KBSnt"   format 9,999,999
column "Trips/s" format 999
column "KbSnt/s" format 99,999
column Trips     format 99,999,999
column "B/trip"  format 9,999,999
column hit_rat   format 9,999

SELECT a.username, a.sid, a.serial#,
       decode(a.status, 'ACTIVE', 'Y','INACTIVE', 'N') actv,
       (sysdate-logon_time)*24*60 sess_mi,
       a.last_call_et/60 "last",
       c.value/1024 "KBSnt",
       e.value "Trips",
       e.value / ((sysdate - logon_time)*24*60*60) "Trips/s",
       ((8*c.value/1024)/((sysdate - logon_time)*24*60*60)) "KbSnt/s",
       c.value/e.value "B/trip",
       b.block_gets + b.consistent_gets TotGets,
       b.physical_reads phyRds,
       1-(physical_reads/(b.block_gets + b.consistent_gets)) hit_rat
FROM v$session a, v$sess_io b, v$sesstat c, v$statname d, v$sesstat e, v$statname f
WHERE a.sid = b.sid
AND a.sid = c.sid
AND c.statistic# = d.statistic#
AND d.name = 'bytes sent via SQL*Net to client'
AND a.sid = e.sid
AND e.statistic# = f.statistic#
AND f.name = 'SQL*Net roundtrips to/from client'
AND a.username is not null
AND (a.last_call_et < 3600 or a.status = 'ACTIVE')
AND sysdate - logon_time > 0
AND a.username != 'SYS'
AND (b.block_gets + b.consistent_gets) > 0
ORDER BY username
/

Using sysdba for oracle exp/imp utility

Example:

$ exp userid=\'/ as sysdba\' file=DBEXP.dmp log=DBEXP.log full=y

Use \'/ as sysdba\' to take exp as sysdba (SYS user).

Object dropped along with table in oracle

Objects dropped along with table in oracle

Which objects are dropped along with table and which objects become INVALID.

Below are automatically dropped with table.

1. Table structure
2. Table data
3. Indexes on the table
4. Triggers associated with table

Below objects status becomes invalid.

1. Views
2. Procedures
3. Functions
4. Packages
5. Materialized views

Oracle wait events

free buffer waits

This wait event indicates that a server process was unable to find a free buffer and has posted the database writer to make

free buffers by writing out dirty buffers. Once DBWR finishes writing the dirty buffers to disk, they are free to be reused.

AWR shows significant waits for free buffer waits

Causes and solution

1) It could be due to poor file write performance in some filesystems. Investigate possible I/O performance problems on the filesystem.

2) Due to small buffer cache. Increase db_cache_size parameter manually or implement Automatic memory management (ASMM).

3) Use multiple DBwriters or enable asynchronous I/O if the platform supports.


log buffer space

This event occurs when server processes are writing redo records to the log buffer faster than LGWR can write them out. So, the log buffer fills up and the processes wait for free space. After LGWR writes some buffers out, then those buffers may be reused by other processes.

Causes and solution

1) LOG_BUFFER is too small. Increase the size of redo log buffer.

2) Could be due to slow redo log files (lgwr is writing very slow due to slow disks). Move the redo logs to faster disks.


read by other session

A session wants to pin a block that is currently being read from disk into the buffer cache by another session.

Causes and solution

1) Tune the SQL statement to use less I/O. from 10g, SQL tuning advisor can be used for tuning the query.

2) Due to small buffer cache. Increase db_cache_size parameter manually or implement Automatic memory management (ASMM).

Causes of HARD parsing and solutions

Causes of HARD parsing and solution

Usually hard parses are due to large statements involving partitioned objects. They also causes high CPU usage.

Case 1:

Optimizer is using dymanic sampling which impacts the parse time

Dynamic sampling is performed by the CBO at parse time when it is either requested via hint or parameter, or by default because statistics are missing.

Depending on the level of the dynamic sampling, it may take some time to complete.

This time is reflected in the parse time for the statement

From 10g, use SQL Tuning Advisor to generate a profile for the query and accept it.

Find the hints needed to implement the plan normally generated with dynamic sampling and modify the query with the hints.

Case 2:

Many queries are being HARD parsed

Hard parsing can happen due to unshared SQL statments (by using Literals in the statement)

if the "Misses in the library cache" is close to total number of parses, hard parsing can be sensed.

Identifying and modifying (rewriting) the SQL statements being hard parsed statements to use bind values could solve the

issue.

To make the statement use bind variables, we can se CURSOR_SHARING parameter in init.ora.

The following are the options set based on the desired output.

    EXACT - Leave the statement as it was written with literals (default value).
    FORCE - Substitute all literals with binds (as much as possible).
    SIMILAR - Substitute literals with binds only if the query's execution plan won't change.


Case 3:

Hard parsing due to the shared SQL aged out

If the shared pool is too small, it causes many statements that could be shared to age out of the library cache and later

reloaded.

Each reload requires a hard parse and impacts the CPU and latches.

This can be identified by AWR report in Library cache statistics section. This section shows more "reloads" count.

Increasing the sharedpool size could reduce the number of statements being aged out.

Implement Automatic Shared Memory Management(ASMM) by setting SGA_TARGET value greater than 0. This will manage the shared

pool size automatically.

Keeping the frequently used SQL and PL/SQL statements in the sharedpool (pinning) is one of the method to avoid SQL

statement age out issues.

This can be done using DBMS_SHARED_POOL.KEEP() procedure.

This will reduce reloads and fragmentation since the object doesn't need to keep reentering the shared pool over and over.

VMSTAT for DBA

What to look in vmstat (For DBA's)

A vmstat output can be used to identify CPU bottlenecks.

Below output shows vmstat output of a typical unix database server.

 procs            memory                        swap        io       system    cpu
 r  b   swpd   free   buff  cache         si   so    bi    bo     in    cs       us sy id wa
 2  5 375912  19548  17556 477472    0    1     0     0      1     1        1  0  0  1
 0  4 375912  18700  17556 478264    0    0  1044   0     774  1329   8  1   0  91
 0  5 375912  17664  17556 479168    0    0  1160   0     764  1110   8  1   0  91

In the above output, we should look for first 2 columns. ie. "r" and "b".

The server experiences bottleneck issues when "r" is greater than the number of CPU’s on the server.

In the above output first row shows "r" is 2. ie. if the CPU count is 2 and "r" is 2, there is no much CPU bottleneck. But "b" shows 5. ie. there are some sessions are waiting. 

It needs more investigation at database level if the waiting sessions are database sessions.

Things to remember in vmstat

r: How many processes are waiting for CPU time.
b: Wait Queue - Process which are waiting for I/O (disk, network, user input,etc..)

Watch command in unix

One more very simple command useful and handy to find the load and CPU starvation.

See the below output

root> w

  7:54am  up 12 days, 12:45,  49 users,  load average: 0.09, 0.11, 0.32
User     tty           login@  idle   JCPU   PCPU  what
root     ttyp1         7:01pm  5:47                tee -a /u01/home/pocj
jjuir    ttyp2         2:48pm    20      3      3  runmenu50 pamenu
uunhik   ttyp3         5:29pm    24                runmenu50 pamenu

The load average is an arbitrary number that shows overall resource consumption of the server. 

Most load average displays have three values for the load average. 

The load average display shows the load averages for the past minute, the past 5 minutes, and the past 10 minutes. 

A low load average is ideal, and the load average should stay below zero. 

Whenever the value exceeds “1” there may be a CPU overload problem.