Scripts for DBA


ASH Views

SELECT SQL_ID, SQL_FULLTEXT, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, ELAPSED_TIME FROM V$SQL WHERE SQL_ID = '&SQL_ID' ORDER BY ELAPSED_TIME DESC;


SELECT STAT.SQL_ID, SQL_TEXT, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, ELAPSED_TIME_DELTA, STAT.SNAP_ID, SS.END_INTERVAL_TIME FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT,
DBA_HIST_SNAPSHOT SS WHERE STAT.SQL_ID = TXT.SQL_ID AND STAT.DBID = TXT.DBID AND SS.DBID = STAT.DBID AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER
AND STAT.SNAP_ID = SS.SNAP_ID
--AND STAT.DBID = ? AND
--STAT.INSTANCE_NUMBER = 
AND STAT.SQL_ID ='&sql_id'
ORDER BY ELAPSED_TIME_DELTA DESC;


select * from dba_hist_sqlstat where sql_id='&sql_id'  order by snap_id desc;

set line 200
col BEGIN_INTERVAL_TIME for a40
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='&sql_id'  and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number   and executions_delta > 0  order by 1, 2, 3;


select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,
executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" from dba_hist_SQLSTAT a, dba_hist_snapshot b
where sql_id='&sql_id' and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
order by snap_id desc, a.instance_number;


col EVENT for a25
col SQL_PLAN_OPERATION for a25
col SQL_PLAN_OPTIONS for a29
col sql_plan_operation  for a15
select * from
(
   select
      sql_id,
      sql_plan_hash_value "PLAN_HASH",
      event,sql_exec_id,
      sql_exec_start,current_obj#,
      sql_plan_line_id "PLAN_LINE_ID",
      sql_plan_operation "PLAN_OPERATIONS",
      sql_plan_options,
      SUM (delta_read_io_requests) lio_read ,
      SUM (delta_read_io_bytes) pio_read
         from
      dba_hist_active_sess_history
   where
      sql_id='&SQL_ID'
   --and 
     -- snap_id >= :strt_snap
  -- and
    --  snap_id <= :end_snap
   group by
      sql_id,
      sql_plan_hash_value,
      event,sql_exec_id,
      sql_exec_start,
      current_obj#,
      sql_plan_line_id,
      sql_plan_operation,
      sql_plan_options
  );



SELECT a.sql_id,a.plan_hash_value,trunc(b.begin_interval_time) run_date,a.instance_number
,round((SUM(a.elapsed_time_delta)/sum(a.executions_total)/1000000)/60,2) avg_elapsed_time_mins
--sum(a.buffer_gets_delta),SUM(a.executions_delta),
--,sum(a.rows_processed_delta)/a.executions_total ROWS_PROCESSED_PER_EXECUTION
,SUM(a.executions_delta)
FROM dba_hist_sqlstat a,dba_hist_snapshot b where
a.snap_id=b.snap_id and a.dbid=b.dbid
and trunc(begin_interval_time) between to_date('&DDMMYYYY','dd/mm/yyyy') and to_date('&DDMMYYYY','dd/mm/yyyy')
and a.instance_number=b.instance_number
and a.sql_id = '&SQL_ID'
GROUP BY a.sql_id,a.plan_hash_value,trunc(b.begin_interval_time),a.instance_number order by 3 desc;


SELECT a.sql_id,a.plan_hash_value,trunc(b.begin_interval_time) run_date,a.instance_number
,round((SUM(a.elapsed_time_delta)/SUM(a.executions_delta)/1000000)/60,2) avg_elapsed_time_mins
--sum(a.buffer_gets_delta),SUM(a.executions_delta),
,sum(a.rows_processed_delta)/SUM(a.executions_delta) ROWS_PROCESSED_PER_EXECUTION
,SUM(a.executions_delta)
FROM dba_hist_sqlstat a,dba_hist_snapshot b where
a.snap_id=b.snap_id and a.dbid=b.dbid
and trunc(begin_interval_time) between to_date('01/04/2018','dd/mm/yyyy') and to_date('30/04/2018','dd/mm/yyyy')
and a.instance_number=b.instance_number
and a.sql_id = 'guzpb5zr43mza'
GROUP BY a.sql_id,a.plan_hash_value,trunc(b.begin_interval_time),a.instance_number order by 3 desc;         

select * from SYS.DBA_HIST_ACTIVE_SESS_HISTORY a where sql_id='1v1q4d3sc6s4n';

select * from dba_hist_active_sess_history where sql_id='5sjfpb2jwx52u';

select * from table(dbms_xplan.display_CURSOR('6rv72wn09mmq9'));

select * from table(dbms_xplan.display_awr('6rv72wn09mmq9','1331868757',FORMAT=>'ALL'));

select * from dba_hist_Sql_plan where sql_id='6rv72wn09mmq9' AND PLAN_HASH_vALUE=1331868757;

select * from dba_hist_Sqltext b where sql_id ='62nbbdgr71mjm';--b.sql_text  like 'SELECT GPTS_SEC_VAL_PRC_FLAT.VAL_D%';

select buffer_gets_total,rows_processed_total,(elapsed_time_total/1000000)/60,plan_hash_value,elapsed_time_delta,elapsed_time_total from SYS.DBA_HIST_sqlstat where sql_id='6dqga843f68p5';

select * from dba_sql_plan_baselines;

select * from dba_tab_PARTITIONS where table_name='FUND_PORT_CACHE';

select min(sample_time),max(sample_time) from dba_hist_active_sess_history where top_level_sql_id='2vpaz6cffv64t' and snap_id between 99815 and 99817 ;

select * from dba_hist_active_sess_history where sql_id='2jrp3jvct61zv' and snap_id between 99716 and 99719;

99815 and 99817 ;

select * from dba_hist_snapshot order by snap_id desc;

select * from dba_hist_Sqltext where  sql_text like 'SELECT GPTS_SEC_VAL_PRC_FLAT%07/28%' ;

select count(*),sql_plan_line_id,sql_plan_operation,sql_plan_options,SQL_PLAN_HASH_VALUE from dba_hist_active_sess_history where sql_id = '&sql_id' AND
snap_id between 99908 and 99915 group by sql_plan_line_id,sql_plan_operation,sql_plan_options,SQL_PLAN_HASH_VALUE ;

select count(*),sql_id from dba_hist_active_sess_history where top_level_sql_id='2vpaz6cffv64t'  and snap_id between 99911 and 99914  group by sql_id;

select count(*),sql_id from dba_hist_active_sess_history where top_level_sql_id='2vpaz6cffv64t'  and snap_id between 99815 and 99817  group by sql_id;

select count(*),sql_id from dba_hist_active_sess_history where top_level_sql_id='2vpaz6cffv64t'  and snap_id between 99716 and 99719 group by sql_id;

select * from dba_hist_active_sess_history where sample_time between to_date('27-JUL-2016 2145','DD-MON-YYYY HH24MI') and to_date('27-JUL-2016 2124','DD-MON-YYYY HH24MI');


Blocking sessions Analysis
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

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;



----BLOCKING WITH USERNAME---


col USERNAME for a20

SELECT
unique
l3.USERNAME ,
l1.sid, ' IS BLOCKING '
--l2.sid,
,sum(1) no_total_blocking
  FROM gv$lock l1, gv$lock l2, gv$session l3
WHERE l1.block =1 and l2.request > 0
   AND l1.id1=l2.id1
   AND l1.id2=l2.id2
   and l3.SID = L1.sid
   group by l3.USERNAME, l1.sid order by 4 desc ;



set lines 200 pages 200
col USERNAME for a12
col OSUSER for a12
col MACHINE for a40
select INST_ID,SID,SQL_ID,SERIAL#,USERNAME,OSUSER,MACHINE,LOGON_TIME,STATUS,STATE FROM GV$SESSION WHERE SID=&sid;


---- To find when application containers restarted ---

 set linesize 300
set pagesize 300
set colsep |
SET UNDERLINE =
COL USERNAME  format a20
COL SERVICE_NAME  format a20
COL logon_time FOR A28
col machine for a28
--break on inst_id skip 1
COL OSUSER FOR A20


COMPUTE SUM OF count(1) ON inst_id
PROMPT : USER WISE CONNECTION DETAILS:
select  inst_id,USERNAME, substr(machine,1,18) machine, OSUSER, SERVICE_NAME,
TO_CHAR(logon_Time,'DD-MON-YYYY HH24:MI:SS') logon_time,
count(1), status from gv$session
where Username like '&Schema_name' and (machine like ('&Host_name%') )
group by USERNAME, machine, inst_id, OSUSER, SERVICE_NAME,logon_Time, status order by logon_Time desc;



set lines 200 pages 200
col USERNAME for a12
col OSUSER for a12
col MACHINE for a40
select INST_ID,SID,SQL_ID,SERIAL#,USERNAME,OSUSER,MACHINE,LOGON_TIME,STATUS,STATE FROM GV$SESSION WHERE SID=&sid;


select INST_ID,USERNAME,count(1) FROM GV$SESSION WHERE USERNAME='ACTIVEORDERS' group by INST_ID,USERNAME;


set lines 200 pages 200
col USERNAME for a12
col OSUSER for a12
col MACHINE for a40

select INST_ID,SID,SQL_ID,SERIAL#,USERNAME,OSUSER,MACHINE,LOGON_TIME,STATUS,STATE FROM GV$SESSION WHERE OSUSER='&OSUSER';


==== blockers and waiters ===

col BLK_SESS for a15
col wtr_sess format a15
col blocker format a12
col waiter format a17
col duration format a9
col blocked_object format a50
select /*+ rule */
a.inst_id ||',' || a.sid || ',' || a.serial# blk_sess,
a.username blocker,
h.type,
b.inst_id||','||b.sid || ',' || b.serial# wtr_sess,
b.username waiter,
o.owner || '.' || o.object_name ||
nvl2 (subobject_name, '.' || subobject_name, null) blocked_object,
lpad (to_char (trunc (w.ctime / 3600)), 3, '0') || ':' ||
lpad (to_char (mod (trunc (w.ctime / 60), 60)), 2, '0') || ':' ||
lpad (to_char (mod (w.ctime, 60)), 2, '0') duration
from gv$lock h, gv$lock w, gv$session a, gv$session b, dba_objects o
where h.block != 0
and h.lmode != 0
and h.lmode != 1
and w.request != 0
and w.id1 = h.id1
and w.id2 = h.id2
and h.sid = a.sid
and w.sid = b.sid and h.inst_id = a.inst_id
and decode (w.type, 'TX', b.row_wait_obj#,'TM', w.id1)= o.object_id
order by w.ctime desc;


-- find blocking sessions for more than 15 minutes and object details ----

col MACHINE for a15
select s.SID,p.SPID,s.machine,s.username,CTIME/60 as minutes_locking, do.object_name as locked_object, q.sql_text,q.sql_id
from gv$lock l
join gv$session s on l.sid=s.sid
join gv$process p on p.addr = s.paddr
join gv$locked_object lo on l.SID = lo.SESSION_ID
join dba_objects do on lo.OBJECT_ID = do.OBJECT_ID 
join gv$sqlarea q on  s.sql_hash_value = q.hash_value and s.sql_address = q.address
where block=1 and ctime/60>15;

-- Check who is blocking who in RAC  ---

SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess, id1, id2, lmode, request, type
FROM gv$lock
WHERE (id1, id2, type) IN (
  SELECT id1, id2, type FROM gv$lock WHERE request>0)
ORDER BY id1, request;

-- Check who is blocking in RAC, including objects ---

SELECT DECODE(request,0,'Holder: ','Waiter: ') || gv$lock.sid sess, machine, do.object_name as locked_object,id1, id2, lmode, request, gv$lock.type
FROM gv$lock join gv$session on gv$lock.sid=gv$session.sid and gv$lock.inst_id=gv$session.inst_id
join gv$locked_object lo on gv$lock.SID = lo.SESSION_ID and gv$lock.inst_id=lo.inst_id
join dba_objects do on lo.OBJECT_ID = do.OBJECT_ID 
WHERE (id1, id2, gv$lock.type) IN (
  SELECT id1, id2, type FROM gv$lock WHERE request>0)
ORDER BY id1, request;

-- Who is blocking who ---

select sn.USERNAME,
m.SID,
sn.SERIAL#,
m.TYPE,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive') lock_type,
decode(REQUEST,
0, 'None', 
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)', 
4, 'Share', 
5, 'S/Row-X (SSX)',
6, 'Exclusive') lock_requested,
m.ID1,
m.ID2,
t.SQL_TEXT
from v$session sn, 
v$lock m , 
v$sqltext t
where t.ADDRESS = sn.SQL_ADDRESS 
and t.HASH_VALUE = sn.SQL_HASH_VALUE 
and ((sn.SID = m.SID and m.REQUEST != 0) 
or (sn.SID = m.SID and m.REQUEST = 0 and LMODE != 4 and (ID1, ID2) in
        (select s.ID1, s.ID2 
         from v$lock S 
         where REQUEST != 0 
         and s.ID1 = m.ID1 
         and s.ID2 = m.ID2)))
order by sn.USERNAME, sn.SID, t.PIECE;

-- Who is blocking who ---

select OS_USER_NAME os_user,
PROCESS os_pid,
ORACLE_USERNAME oracle_user,
l.SID oracle_id,
decode(TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table', type) lock_type,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', lmode) lock_held,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', request) lock_requested,
decode(BLOCK,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global', block) status,
OWNER,
OBJECT_NAME
from v$locked_object lo,
dba_objects do,
v$lock l
where lo.OBJECT_ID = do.OBJECT_ID
AND     l.SID = lo.SESSION_ID
and block=1


Select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK  from v$lock where block >0;
Find details of blocked resource:
select sid,row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#  from v$session where blocking_session=&blocking_session;


select distinct(session_id),instance_number "INST",sql_id,SQL_OPNAME,event,session_state,
blocking_session,blocking_inst_id "BLK_INST",current_obj# "CR_OBJ",current_block# "CR_BLK",current_row#,program,machine 
from dba_hist_active_sess_history where sql_id='8mhfqaavrf58w' and sample_time 
between to_date('04/05/2018 1:08', 'dd/mm/yyyy hh24:mi') and to_date('04/05/2018 1:50', 'dd/mm/yyyy hh24:mi')


Comments

Post a Comment

Popular posts from this blog

Tuples in Python

 Tuples Basically Tuples in Python are very similar to Lists, but, Tuples are immutable which means that they can not be modified/changed. We generally use Tuples to make sure the values are not changed like, dates, Year, and days of the week or dates on a Calendar.  Creating a Tuple: >>> my_t1=(1,2,3) >>> my_t2=('one','two','three') >>> my_t1 (1, 2, 3) >>> my_t2 ('one', 'two', 'three') -- check the length of tupple: >>> len(my_t1) 3 >>> len(my_t2) 3 -- Tuples can have mixed objects as like Lists. >>> my_t3=(1,'two',3,'four') >>> my_t3 (1, 'two', 3, 'four') -- indexing with tuples  >>> my_t3=(1,'two',3,'four') >>> my_t3[2] 3 Immutable Tuples We can not modify the value. Here is the example: >>> my_t3=(1,'two',3,'four') >>> my_t3[2]=4 Traceback (most recent call last):  ...
Read more

Auditing in Oracle database

Unified Auditing: Oracle Database 12c Release 1 introduces a new auditing feature called Unified Auditing (12.1). Oracle Unified Auditing modifies the database's fundamental auditing capabilities. In earlier versions of Oracle, each component has its own audit trail. With unified auditing, all auditing is compiled into a single repository and view. Due to the fact that all audit data is now in a single location and a single format, there is a double simplification as a result. This data is made available in a consistent format in the UNIFIED AUDIT TRAIL data dictionary view, which is made possible by the unified audit trail, which is stored in a read-only table in the AUDSYS schema in the SYSAUX tablespace.  DBAs can build audit rules and then assign them to various users using Oracle 12c Unified Auditing, which has the same advantages as giving users varying system capabilities based on their responsibilities. The unified_audit_trail database will contain all of the audit records....
Read more

Boolean in Python

What is a Boolean? Ture or False (Make sure to use initial letter as a capital) For Example, >>> True True >>> true Traceback (most recent call last):   File "<stdin>", line 1, in <module> NameError: name 'true' is not defined Examples: >>> x= True >>> type(x) <class 'bool'> >>> 9 > 8 True >>> 8> 9 False  How to define/assign a variable without value? >>> x Traceback (most recent call last):   File "<stdin>", line 1, in <module> NameError: name 'x' is not defined Here is the way, you could define the variable without defining/assigning the value >>> x=None >>> x
Read more