Oracle Database Common Scripts

Database Top SQL

Select DISTINCT st.snap_id, To_char (Sn.begin_interval_time, ' yyyy-mm-dd hh24:mi:ss ') | | '~' || To_char (sn.end_interval_time, ' hh24:mi:ss ') time range, st.sql_id, (select S.username from v$session s where s.sql_id = St.sql_ ID and rownum = 1) as "execution user", round (st.cpu_time_delta/900/1000000, 2) as "average Cpu consumption", round (st.elapsed_time_delta/de Code (ST.EXECUTIONS_DELTA, 0, 1, st.executions_delta)/1000000, 2) as "SQL Average execution time (s)", St.executions_delta as "number of executions", st.pl An_hash_value, St.buffer_gets_delta, St.rows_processed_delta, (select distinct sql_text from Gv$sql a where a.sql_id = St. sql_id) Sql_text from Dba_hist_sqlstat St, dba_hist_snapshot Sn Where st.snap_id = sn.snap_id and St.dbid in (select dbid From V$database) and Sn.begin_interval_time > (Sysdate-(30/1440)) and round (st.cpu_time_delta/900/1000000, 2) & Gt 1.5 Order by "Average CPU consumption" desc

GG Process Delay

Select hostname, SID, program, status, Group_name, Lag_time, Time_since_chkpt, To_char (snap_date, ' Yyyy-mm-dd hh24:mi: SS ') Snap_date from Ggmpdata.ggmon_vw_status A Where ((Substr (Time_since_chkpt, 1, InStr (Time_since_chkpt, ': ')-1) * 60 * + substr (time_since_chkpt, InStr (Time_since_chkpt, ': ') + 1, InStr (Time_since_chkpt, ': ', 1, 2)-InStr (Time_since_c Hkpt, ': ')-1) * + substr (time_since_chkpt, InStr (Time_since_chkpt, ': ', 1, 2) + 1) >)) and sid = ' CFS1 ' and GR Oup_name = ' d_nbiz '

Occupy Temporal table Space SQL

Select Se.username, Se.sid, se.serial#, Se.status, Se.last_call_et, Se.machine, Se.osuser, su.extents, Su.blocks, Su.blocks * (SELECT VALUE from v$parameter WHERE NAME = ' db_block_size ')/1024/1024 | | ' M ' as Temp_size, Su.segtype, Su.sqlhash, SE. sql_id, sa.sql_text from V$session se, v$sort_usage su, v$sqlarea sa where su.session_addr = se.saddr and su.sql_id = Sa.s ql_id and su.sqladdr = sa.address and (Su.extents > Trunc ((select total_extents from v$sort_segment) * 0.05) or Su.bloc KS > Trunc ((select Total_blocks from v$sort_segment) * 0.05))

SQL execution time exceeds 1 hours

Select DISTINCT S.inst_id,s.username,s.sid,ceil (sysdate-s. Sql_exec_start) *24*60*60) time, s.sql_id,sq. Sql_text,s.machine,s.osuser, ' exec dbmgr.prc_kill_session (' | | s.username| | "," | | s.sid| | "," | | s.serial#| | '); Kill_sql from Gv$session S, gv$sql sq where 1=1 and s.inst_id=sq.inst_id and s.sql_id=sq.sql_id and s.status = ' ACTIVE ' an D s.wait_class<> ' Idle ' and s.username is not null and s.username not in (' Gbdsqp ', ' dbmgr ') and s.sql_id are NOT null and s.sql_exec_start<sysdate-1/24 ORDER BY time Desc

Blocked for more than 600 seconds

Select/*+ Rule */decode (request, 0, ' Holder ', ' Waiter ') Holder, lo.inst_id, Lo.sid Sid, Sq. sql_id, SE. serial#, se.username username, sq.sql_text sql, CTime wait_sec, Lo. REQUEST from Gv$lock Lo, gv$session se, Gv$sqlarea sq where (id1, ID2, Lo.type) in (select Id1, Id2, type from Gv$lock whe Re request > 0 and CTime > (ID1, Id2, Lo.type) not in (select Id1, id2,lo.type from Gv$session s,gv$lock Lo where S.sid=lo. SID and s.sql_id= ' 0bvyj0pxgb4uk ')) and Lo.sid = Se.sid and lo.inst_id=se.inst_id and Se.sql_hash_value = Sq.hash_value (+) and Se.username not in (' SYS ', ' Dbqua ', ' dbmgr ') Order by Holder

DB Long Transaction

SELECT * FROM (select Lg.sid, lg. serial# se#, S.username, S.machine, Lg. sql_id Lgsql, s.sql_id sSQL, round (sofar/totalwork*100,2) | | ' % ' as progress, time_remaining is expected to remain long, elapsed_seconds run time, sq. Sql_text, s.event from Gv$session_longops LG, Gv$session S, gv$sqlarea sq where Sofar < totalwork and Lg.sid=s.sid and s.sql_id=sq.sql_id ORDER BY time_remaining Desc) where rownum<21

No variables used

Select substr (Sql_text, 1,) "SQL", COUNT (*) CNT, sum (SHARABLE_MEM) "Totexecs" from V$sqlarea WHERE executions =1 and to _char (sysdate, ' hh24 ') in (' Ten ', ' + ') GROUP by substr (Sql_text, 1, +) having count (*) > The ORDER by 2

Occupy rollback segments

SELECT s.sid,s.username user name, s.status status, Round (S.LAST_CALL_ET/60) | | ' Minutes ' occupation time, round (r.rssize/1024/1024) | | M ' rollback segment from v$session s, V$transaction T, v$rollstat r WHERE s.saddr = t.ses_addr and T.xidusn = R.usn and ((R.curext = t. start_uext-1) OR ((R.curext = r.extents-1) and T.start_uext = 0) and s.last_call_et/60 > (' SYS ', ' APPS ') and R.rssize > 50*1024*1024 ORDER BY last_call_et Desc

User executes SQL for more than 20 minutes

Select Last_call_et executed time _ seconds, username user name, A.machine machine name, a.sql_id, A.sid, a.serial#, B.sql_text SQL from V$session A, v$sq L b where a.status = ' ACTIVE ' and A.last_call_et > and a.username like '%dbmono% ' and a.sql_id = b.sql_id

Viewing lock Timeouts

Select s.inst_id,s.username,s.sid,s.serial#,s.status,k.ctime,s.sql_id, ' exec prc_kill_session (v_username = ' | | s.username| | ", V_sid =" | | s.sid| | ", v_serial# =" | | s.serial#| | "); ' Kill_sql from Gv$lock K, gv$session s where k.inst_id = s.inst_id and s.username = ' edsop ' and k.sid = S.sid and K.lmode In (3, 6) and K.type in (' TM ', ' TX ') and K.ctime > 600

Oracle Database Common Scripts