Important SQLs for DBAs

1. To get SQL Performance Detail for a given SQL ID, which is currently running or is just completed. (The SQL ID should be a available in the memory (Shared Pool), else the SQL will not return any result):


SELECT
    ROUND(a.elapsed_time/(1000*1000),2) elapsed_time,
    ROUND((a.elapsed_time/(1000*1000))/a.executions,2) elapsed_time_per_exec,
    a.sql_id,
    SUBSTR(a.sql_text, 1,40) sql_text,
    a.executions,a.users_executing,
    a.first_load_time,
    a.rows_processed,
    a.parsing_schema_name,
    a.plan_hash_value,
    a.module,
    a.last_load_time
FROM v$sqlarea a
WHERE a.executions > 0
  and sql_id='g306ta1yzcfuq';

1a. To get all the SQLs from your database, which are currently running or is just completed. (The SQL ID should be a available in the memory (Shared Pool), else the SQL will not return any result. If it the SQL is not available in the v$sqlarea, check in DBA_HIST_SQLSTAT view)

SELECT to_char(last_active_time, 'dd-mon-yyyy hh24:mi:ss') last_active_time,
    ROUND(a.elapsed_time/(1000*1000),2) elapsed_time,
    ROUND((a.elapsed_time/(1000*1000))/a.executions,2) elapsed_time_per_exec,
    a.sql_id,
    SUBSTR(a.sql_text, 1,40) sql_text,
    a.executions,a.users_executing,
    a.first_load_time,
    a.rows_processed,
    a.parsing_schema_name,
    a.plan_hash_value,
    a.module,
    a.last_load_time
FROM v$sqlarea a
WHERE a.executions > 0
 and parsing_schema_name NOT IN ('SYS','SYSTEM','DBSNMP')
 order by last_active_time;

2. To get all the expensive SQLs from your database, which are currently running or is just completed. (The SQL ID should be a available in the memory (Shared Pool), else the SQL will not return any result)


select
ROUND(a.elapsed_time/(1000*1000),2) elapsed_time,
ROUND((a.elapsed_time/(1000*1000))/a.executions,2) elapsed_time_per_exec,
a.sql_id,
SUBSTR(a.sql_text, 1,40) sql_text,
a.executions,a.users_executing,
a.first_load_time,
a.rows_processed,
a.parsing_schema_name,
a.plan_hash_value,
a.module,
a.last_load_time , a.*
FROM v$sqlarea a
WHERE a.executions > 100
-- and ( LOWER(SUBSTR(a.sql_text, 1,4)) <> 'call' AND LOWER(SUBSTR(a.sql_text, 1,5)) <> 'begin')
and ROUND((a.elapsed_time/(1000*1000))/a.executions,2) > 0
order by ROUND((a.elapsed_time/(1000*1000))/a.executions,2) desc , a.executions desc;


3. Generate kill session command for a single user.
set lines 300
select 'alter system kill session '||''''||s.sid||','||s.serial#||''' immediate;' kill_command, s.sid, s.serial#, s.status, p.spid
 from v$session s, v$process p
 where s.username = '&username'
and p.addr (+) = s.paddr;

4. If you have multiple Archive destination (Local or Standby), you can use the below command to check the status of each destination and the error message, if any of the destination is not receiving the archive log files.

set lines 300
col dest_name format A20
select dest_id, dest_name, error from v$archive_dest_status;

5. Get the database name, Mode, role etc. from v$database.
select current_scn, name, open_mode, database_role, flashback_on from v$database;

6. Get the Redo Generation Rate Per Hour.

select to_char(completion_time, 'DD-MON-YYYY HH24') DAY, Thread#,
count(*) "Archive File Count", round(sum(blocks*block_size)/1024/1024/1024) GB
from v$archived_log 
where completion_time between sysdate-1 and sysdate

group by to_char(completion_time, 'DD-MON-YYYY HH24'), thread# order by 1,2;

7. Get the Redo Generation Rate Per Day.
select to_char(completion_time, 'DD-MON-YYYY') DAY, Thread#,
 count(*) "Archive File Count", round(sum(blocks*block_size)/1024/1024/1024) GB
 from v$archived_log 
 where completion_time > (sysdate - 7)
 group by to_char(completion_time, 'DD-MON-YYYY'), thread# order by 1,2;

8. Get the list of Child Tables for a given Table

SET LINES 300
col parent_owner format A15
col child_owner format A15
col parent_table format A30
col child_table format A30
col parent_cons_name format A30
col child_cons_name format A30

SELECT 
      p.owner parent_owner, 
      c.owner child_owner, 
      p.table_name Parent_table, 
      p.constraint_name parent_cons_name, 
      c.table_name Child_table, 
      c.constraint_name child_cons_name
  FROM 
      dba_constraints p, dba_constraints c
where p.owner='&parent_Owner'
  and p.table_name='&Parent_Table_Name'
  and p.constraint_type IN ('P','U')
  and c.constraint_type='R' 
  and p.owner=c.r_owner
  and p.constraint_name=c.r_constraint_name
order by 4;

9. Get the List of Parent Table for a given Child Table.

SET LINES 300
col parent_owner format A15
col child_owner format A15
col parent_table format A30
col child_table format A30
col parent_cons_name format A30
col child_cons_name format A30

SELECT 
      p.owner parent_owner, 
      c.owner child_owner, 
      p.table_name Parent_table, 
      p.constraint_name parent_cons_name, 
      c.table_name Child_table, 
      c.constraint_name child_cons_name
  FROM 
      dba_constraints p, dba_constraints c
where c.owner='&child_Owner'
  and c.table_name='&Child_Table_Name'
  and p.constraint_type IN ('P','U')
  and c.constraint_type='R' 
  and p.owner=c.r_owner
  and p.constraint_name=c.r_constraint_name
order by 4;

10. Get the Instance Detail.
set lines 300
col host_name format A25
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

select instance_number, instance_name, host_name, startup_time, status from v$instance;

11. Get Your Session Detail.

col Database Format A10
col Username Format A10
col current_schema format A10
SELECT 
 sysdate, 
 sys_context('USERENV','SERVER_HOST') server_host,
 sys_context('USERENV','SERVICE_NAME') service_name,
 sys_context('USERENV','INSTANCE_NAME') instance_name,
 sys_context('USERENV','DB_UNIQUE_NAME') DB_UNIQUE_NAME, 
 sys_context('USERENV','DB_NAME') DB_NAME, 
 sys_context('USERENV','DB_DOMAIN') DB_DOMAIN, 
 sys_context('USERENV','SESSION_USER') Username,
 sys_context('USERENV','CURRENT_SCHEMA') current_schema
 FROM Dual;

12. Find out if there is any plan change and all of its detail for a SQL ID.
(** The change in plan_hash_value column will indicate a change in plan)

select 
  sn.begin_interval_time, 
  sn.end_interval_time, 
  sq.sql_id, 
  sq.plan_hash_value,
  sq.executions_total,
  sq.executions_delta,
  round(sq.elapsed_time_total/(1000*1000),3) elapsed_time_total_in_sec,
  round(sq.elapsed_time_delta/(1000*1000),3) elapsed_time_delta_in_sec,
  sq.module, 
  sq.action,
  round(sq.cpu_time_total/(1000*1000),3) cpu_time_total_in_sec,
  round(sq.cpu_time_delta/(1000*1000),3) cpu_time_delta_in_sec
from dba_hist_sqlstat sq, dba_hist_snapshot sn
where sq.sql_id='gsd2mfhfcynwg'  
and sq.snap_id = sn.snap_id
order by sn.end_interval_time;

13. Get the actual values for Bind Variables for a SQL ID

select * from v$sql_bind_capture where sql_id='gsd2mfhfcynwg' ;

14. Get the Detail of Plan History for a SQL ID

select * from dba_hist_sql_plan sq where sq.sql_id='gsd2mfhfcynwg';

15. Get the list SQL Baslines for your Database.

select * from dba_sql_plan_baselines;

16. Find the SQL executed by a OS Process ID

prompt "Please Enter The UNIX Process ID"
set pagesize 50000
set linesize 30000
set long 500000
set head off
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;


16. List of Sesions based on CPU Usage

sessions based on cpu usage :
-----------------------------
set pages 1000
set lines 1000
col OSPID for a06
col SID for 99999
col SERIAL# for 999999
col SQL_ID for a14
col USERNAME for a15
col PROGRAM for a23
col MODULE for a18
col OSUSER for a10
col MACHINE for a25
select * from (
select p.spid "ospid",
se.SID,ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.MACHINE,
se.VALUE/100 cpu_usage_sec
from
v$session ss,
v$sesstat se,
v$statname sn,
v$process p
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
and ss.paddr=p.addr 
--and value > 0
order by se.VALUE desc);

17. Session Detail

select 
    a.event, 
    a.sid, 
    a.username,
    a.blocking_session, 
    a.final_blocking_session, 
    a.sql_id, 
    a.status, 
    a.osuser, 
    a.machine, 
    a.program, 
    a.last_call_et, 
    a.blocking_session_status, 
    a.final_blocking_session_status, 
    a.wait_class, 
    a.seconds_in_wait, 
    a.state
FROM 
  v$session a where username is not null
and event not in ('SQL*Net message from client','SQL*Net message to client');

17. SQL Detail from V$SQL

select 
sql_id, 
plan_hash_value,
first_load_time, 
executions, 
round(elapsed_time/(1000*1000),3) elapsed_time_total_in_sec
 from V$sql 
where sql_id IN ('fsy3ubymdz500','g306ta1yzcfuq');

18. Tablespace Usage

select 
tablespace_name, 
file_name, 
ROUND(bytes/1024/1024/1024,1) SIZE_GB, 
ROUND(maxbytes/1024/1024/1024,1) max_GB, 
autoextensible,
ROUND(bytes/maxbytes*100) percent_used
from 
dba_data_files 
order by bytes/maxbytes*100 DESC;

select 
tablespace_name, 
ROUND(SUM(bytes)/1024/1024/1024,1) SIZE_GB, 
ROUND(SUM(maxbytes)/1024/1024/1024,1) max_GB, 
ROUND(SUM(bytes)/SUM(maxbytes)*100) percent_used
from 
dba_data_files 
GROUP by tablespace_name
ORDER BY 4 DESC;

----------------------------------------------------

SELECT A.TABLESPACE_NAME, A.GB SEGMENT_USED_GB, B.SIZE_GB ALLOCATED_GB, B.MAX_GB MAX_GB, B.PERCENT_USED PERCENT_ALLOCATED, ROUND(A.GB/B.SIZE_GB*100) PERCENT_USED
FROM
(select tablespace_name, ROUND(sum(bytes)/1024/1024/1024,1) GB from dba_segments group by tablespace_name having ROUND(sum(bytes)/1024/1024/1024,1) > 0 ) A,
(select 
 tablespace_name, 
 ROUND(SUM(bytes)/1024/1024/1024,1) SIZE_GB, 
 ROUND(SUM(maxbytes)/1024/1024/1024,1) max_GB, 
 ROUND(SUM(bytes)/SUM(maxbytes)*100) percent_used
from 
 dba_data_files 
GROUP by tablespace_name
HAVING ROUND(SUM(bytes)/1024/1024/1024,1) > 0) B WHERE A.tablespace_name = B.tablespace_name
ORDER BY ROUND(A.GB/B.SIZE_GB*100) DESC;

-- Blocking Session Detail

set lines 300

select

   blocking_session,

   sid,

   serial#,

   wait_class,

   seconds_in_wait

from

   v$session

where

   blocking_session is not NULL

order by

   blocking_session;

-- Show Sessions holding a TX lock

select * from v$lock where type='TX' and lmode>0;

Show Sessions Waiting for a TX lock

select * from v$lock where type='TX' and request>0;

-- To Find All Locked Objects in Oracle

column sid_ser format a12 heading 'session,|serial#'; 

column username format a12 heading 'os user/|db user'; 

column process format a9 heading 'os|process'; 

column spid format a7 heading 'trace|number'; 

column owner_object format a35 heading 'owner.object'; 

column locked_mode format a13 heading 'locked|mode'; 

column status format a8 heading 'status'; 

select 

    substr(to_char(l.session_id)||','||to_char(s.serial#),1,12) sid_ser, 

    substr(l.os_user_name||'/'||l.oracle_username,1,12) username, 

    l.process, 

    p.spid, 

    substr(o.owner||'.'||o.object_name,1,35) owner_object, 

    decode(l.locked_mode, 

             1,'No Lock', 

             2,'Row Share', 

             3,'Row Exclusive', 

             4,'Share', 

             5,'Share Row Excl', 

             6,'Exclusive',null) locked_mode, 

    substr(s.status,1,8) status 

from 

    v$locked_object l, 

    all_objects     o, 

    v$session       s, 

    v$process       p 

where 

    l.object_id = o.object_id 

and l.session_id = s.sid 

and s.paddr      = p.addr 

and s.status != 'KILLED'

/

-- Check the lock on a Specific Tables

SELECT c.owner

      ,c.object_name

      ,c.object_type

      ,vs.module

      ,vs.machine

      ,vs.osuser

      ,vlocked.oracle_username

      ,vs.sid

      ,vp.pid

      ,vp.spid AS os_process

      ,vs.serial#

      ,vs.status

      ,vs.saddr

      ,vs.audsid

      ,vs.process

FROM 

    v$locked_object vlocked

    ,v$process       vp

    ,v$session       vs

    ,dba_objects     c

WHERE vs.sid = vlocked.session_id

AND vlocked.object_id = c.object_id

AND vs.paddr = vp.addr

AND c.object_name LIKE '%' || upper('&tab_name') || '%'

AND nvl(vs.status

      ,'XX') != 'KILLED';

-- One more Query to Check Locked Objects in the Database

col session_id head 'Sid' form 9999

col object_name head "Table|Locked" form a30

col oracle_username head "Oracle|Username" form a10 truncate

col os_user_name head "OS|Username" form a10 truncate

col process head "Client|Process|ID" form 99999999

col mode_held form a15

select lo.session_id,lo.oracle_username,lo.os_user_name,

lo.process,do.object_name,

decode(lo.locked_mode,0, 'None',1, 'Null',2, 'Row Share (SS)',

3, 'Row Excl (SX)',4, 'Share',5, 'Share Row Excl (SSX)',6, 'Exclusive',

to_char(lo.locked_mode)) mode_held

from v$locked_object lo, dba_objects do

where lo.object_id = do.object_id

order by 1,5

/

-- SQL Detail of a SQL ID

SELECT

 sql_id,

 TRUNC(sql_text,1,100) sql_text,

 plan_hash_value,

 executions,

 round(elapsed_time/(1000*1000),3) elapsed_time_total_in_sec,

 users_executing

FROM v$sql

WHERE sql_id = '&sql_id';