Useful SQL for DBA’s

We would be updating this page with Useful SQL for DBA’s. Feel free to contribute

[—ATOC—]
[—TAG:h2—]

Dataguard

This section will cover helpful sql for dataguard

1)Check the status of archiving destinations- Primary

set linesize 500
col DESTINATION for a30
SELECT inst_id,DEST_ID , STATUS ,DESTINATION , ERROR FROM gV$ARCHIVE_DEST WHERE DEST_ID <4 order by 1,2;

2) Check the Apply/Transport lag- BCP

select name,value from v$dataguard_stats where name in ('apply lag','transport lag');

3) Find the standby lags in terms of number of archives to be applied – BCP

select to_char(sysdate,'DD.MM.RR HH24:MI: SS') time, a.thread#, (select max (sequence#) from v$archived_log where archived='YES' and thread#=a.thread#) archived, max (a.sequence#) applied, (select max (sequence#) from v$archived_log where archived='YES' and thread#=a.thread#)-max (a.sequence#) gap from  v$archived_log a where a.applied='YES' group by a.thread#;

4)Check progress of MRP – BCP

select inst_id,process,pid,status,thread#,sequence#, block# from gv$managed_standby where process like 'MRP%'

5) Stop managed recovery – BCP

alter database recover managed standby database cancel

6)Start Managed Recovery – BCP

To use real time apply (Need standby logs)

alter database recover managed standby database using current logfile parallel 8 disconnect;

Without standby logs

alter database recover managed standby database parallel 8 disconnect

Scheduler Jobs

1)Find failed jobs in last n days/hours

select instance_id,owner, job_name, log_date, status, ADDITIONAL_INFO,RUN_DURATION,ERROR# from
dba_scheduler_job_run_details
where status = 'FAILED'
and log_date > sysdate - &time order by log_date;

To find for last 1 hour enter 1/24

2)To find job history

col owner for a10
col job_name for a20
col log_date for a30
select owner, job_name, log_date, status,instance_id,run_duration,additional_info
from dba_scheduler_job_run_details
where job_name like '&job_name' and
owner like '&owner' and
log_date > sysdate - &time
order by log_date;

3)Get job details

select owner, job_name,JOB_ACTION,START_DATE, END_DATE, ENABLED , STATE , job_class, RUN_COUNT, FAILURE_COUNT, LAST_START_DATE, LAST_RUN_DURATION, NEXT_RUN_DATE, repeat_interval from dba_scheduler_jobs where job_name like '%&job%' and owner='&owner';

4)Running job details

select OWNER,JOB_NAME,RUNNING_INSTANCE,ELAPSED_TIME,session_id from dba_scheduler_running_jobs;

5)Find wait events/sid details of running jobs

col job_name for a30
col owner for a20
col event for a40
select inst_id,sid,serial#,sql_id,sql_child_number,owner,job_name,event,p1,p2,elapsed_time from GV$SESSION ,dba_scheduler_running_jobs where session_id=sid and inst_id=RUNNING_INSTANCE and job_name='&job';

6)Runaway jobs or jobs without session id

select OWNER,JOB_NAME,RUNNING_INSTANCE,ELAPSED_TIME,session_id from dba_scheduler_running_jobs where session_id is null;

7)Scheduler job window details

col window_name format a17
col RESOURCE_PLAN format a25
col LAST_START_DATE format a50
col duration format a15
col enabled format a5
select window_name, RESOURCE_PLAN, LAST_START_DATE, DURATION, enabled from DBA_SCHEDULER_WINDOWS;

8)Determining active window

SELECT WINDOW_NAME, RESOURCE_PLAN FROM DBA_SCHEDULER_WINDOWS WHERE ACTIVE='TRUE';

Generic Administration

1)Find Sid and corresponding OS Process id

set pages 1000 lines 120
col program for a20
col osuser for a15
col suser for a15
col puser for a15
select s.inst_id,s.sid,p.spid,s.osuser,p.program,s.status,s.event,s.sql_id from gv$session s ,gv$process p where s.paddr=p.addr and s.sid=&sid
/

2)Current Sql for particular sid

select sq.sql_text from V$SESSIOn s,V$SQLTEXT sq where sq.sql_id=s.sql_id and s.status='ACTIVE' and s.sid=&sid order by piece;

3)Find Database locks

col machine for a40
col event for a30
col bl_inst for a8
col bl_sess for a8
col SERVICE_NAME for a20
col username for a15

select a.inst_id,a.sid,a.serial#,a.username,a.machine,a.event,NVL(to_char(a.blocking_instance),'****') BL_INST,NVL(to_char(a.blocking_session),'*******') BL_SESS,b.status,a.seconds_in_wait/60 "Mins",a.wait_time,a.SQL_ID,a.service_name
from gv$session a,
(SELECT inst_id,DECODE(request,0,'Holder ','Waiter ') Status,sid FROM gV$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0)
ORDER BY id1, request) b
where a.inst_id=b.inst_id and a.sid=b.sid and a.seconds_in_wait >= 30 order by a.seconds_in_wait;

4)Kill database sessions

This will generate kill script for current session. You can modify where condition

select 'alter system kill session '''||sid||','||serial#||''';' from V$SESSION where username='&user';

In 11g RAC, you can remotely kill session

select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate ;' from GV$SESSION where username='&user';

Performance

 

1)Sessions with highest CPU

col username for a20
col "OS PID" for a20
col sql_id for a20
col module for a50
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module
,s.sql_id, s.sql_child_number,st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;

2)Flush sql from shared pool

select 'exec dbms_shared_pool.purge('''||ADDRESS||','||HASH_VALUE||''',''C'')' from V$SQLAREA where SQL_ID='&sqlid';

3)Find values of bind’s for currently running sql

col VALUE_STRING for a40
select SQL_ID,CHILD_NUMBER,VALUE_STRING,POSITION from V$SQL_BIND_CAPTURE where sql_id='&sqlid';

To check bind from AWR

col VALUE_STRING for a40
select snap_id, sql_id, position, value_string, last_captured
from DBA_HIST_SQLBIND
where sql_id = '&sqlid'
order by snap_id, last_captured, position;

4)Find if plan has changed by checking history
This sql has been taken from Kerry Osborne site

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and ss.BEGIN_INTERVAL_TIME >sysdate -30
and executions_delta > 0
order by 1, 2, 3
/

5) Find query plans which have been degraded by certain ratio. You can specify degradation in terms of Buffer gets or Time

select * from(
 select a.inst_id ID,a.module,a.PARSING_SCHEMA_NAME,a.sql_id,a.PLAN_HASH_VALUE Current_Plan_Hash_Value,
 b.PLAN_HASH_VALUE Old_Plan_Hash_Value,
 round(avg(a.BUFFER_GETS/(a.executions +1)),1) CURR,
 round(avg(a.ELAPSED_TIME/(a.executions +1))) CURR_ELAP,
 round(avg(b.BUFFER_GETS_DELTA/(b.EXECUTIONS_DELTA )),1) OLD ,
 round(avg(b.ELAPSED_TIME_DELTA/(b.EXECUTIONS_DELTA ))) OLD_ELAP ,
 trunc((avg(a.BUFFER_GETS/(a.executions +1))) *100 / (avg(b.BUFFER_GETS_DELTA/(b.EXECUTIONS_DELTA +1)))/100) Degradation_Ratio,
 substr(a.sql_text,1,75) SQL
 from gv$sql a , DBA_HIST_SQLSTAT b
 where a.sql_id = b.sql_id
 and a.PLAN_HASH_VALUE != b.PLAN_HASH_VALUE
 and b.SNAP_ID > (select min(snap_id) from dba_hist_snapshot where BEGIN_INTERVAL_TIME > sysdate - 7)
 and b.EXECUTIONS_DELTA > 0
 and b.BUFFER_GETS_DELTA > 0
 and a.executions > &executions
 and a.PARSING_SCHEMA_NAME !='SYS'
 and a.PARSING_SCHEMA_NAME not like 'DOSQL%'
 and ((to_date(LAST_LOAD_TIME,'YYYY-MM-DD/HH24:MI:SS') > sysdate -(&&time )) or (to_date(FIRST_LOAD_TIME,'YYYY-MM-DD/HH24:MI:SS') > sysdate -(&&time )))
 group by a.inst_id,a.module,a.PARSING_SCHEMA_NAME,a.sql_id,a.PLAN_HASH_VALUE ,
 b.PLAN_HASH_VALUE,substr(a.sql_text,1,75)
 )
 where (
 (CURR > (OLD *&degradation_ratio))
 or
 (CURR_ELAP > (OLD_ELAP * &elaps_degradation_ratio))
 )
 order by 11 desc;

6) Get hourly IOPS report

with t1 as
(select to_char(BEGIN_TIME,'MM/DD/YYYY HH24') time,METRIC_NAME,avg(AVERAGE) ReadIOPS
from dba_hist_sysmetric_summary
where METRIC_NAME in ('Physical Read Total IO Requests Per Sec')
and trunc(BEGIN_TIME) between sysdate-3 and sysdate
group by METRIC_NAME, to_char(BEGIN_TIME,'MM/DD/YYYY HH24')),
t2 as
(select to_char(BEGIN_TIME,'MM/DD/YYYY HH24') time,METRIC_NAME,avg(AVERAGE) WriteIOPS
from dba_hist_sysmetric_summary
where METRIC_NAME in ('Physical Write Total IO Requests Per Sec')
and trunc(BEGIN_TIME) between sysdate-3 and sysdate
group by METRIC_NAME, to_char(BEGIN_TIME,'MM/DD/YYYY HH24'))
select t1.time,t1.ReadIOPS,t2.WriteIOPS, (t1.ReadIOPS+t2.WriteIOPS) total
from t1 , t2
where t1.time=t2.time
order by 1 asc;

Hope this helps

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Disclaimer!!

This blog reflect our own views and do not necessarily represent the views of our current or previous employers.

The contents of this blog are from our experience, you may use at your own risk, however you are strongly advised to cross reference with Product documentation and test before deploying to production environments.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 188 other subscribers