Important SQLs for DBAs

SQL Detail.

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 ('<sql_id>');

 

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;

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');

Get the database name, Mode, role etc. from v$database.

select current_scn, name, open_mode, database_role, flashback_on from v$database;

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;

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;

 

 Get the Instance Detail.

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;

 Get Your Session Detail.

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;

If you have multiple Archive destination (Local or Standby), use the below command to check the status of each destination.

select dest_id, dest_name, error from v$archive_dest_status;

List of Sessions based on CPU Usage.

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);

 

To get SQL Performance Detail for a given SQL ID, which is currently running or is just completed.

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='<sql_id>';

Get all the SQLs from your database, which are currently running or is just completed.

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;

Get all the expensive SQLs from your database, which are currently running or is just completed. 

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;



List of Child Tables for a given Table.

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;

List of Parent Table for a given Child Table.

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;

Find out for any plan change and all of its detail .

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='sql_id'  
and sq.snap_id = sn.snap_id
order by sn.end_interval_time;


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

select * from v$sql_bind_capture where sql_id='<sql_id>' ;

Get the Detail of Plan History .

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

Get the list SQL Baselines for your Database.

select * from dba_sql_plan_baselines;

Find the SQL executed by a OS Process ID.

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>;