Useful SQL for DBA’s
We would be updating this page with Useful SQL for DBA’s. Feel free to contribute
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
/
Hope this helps
