SELECT 'ALTER SYSTEM KILL SESSION '||'''||SID||','||SERIAL#||''
from V$SESSION
WHERE TO_CHAR(LOGON_TIME,'YYYYDDMM HH24:MI') < TO_CHAR(SYSDATE-1,'YYYYDDMM HH24:MI')
AND USERNAME IS NOT NULL
============finding top CPU user SQL =============
SELECT /*+ RULE */ * FROM (
SELECT cpu_time, disk_reads, rows_processed, sql_text FROM v$sqlarea
ORDER BY cpu_time DESC )
WHERE rownum <6
-------by rows_processed, disk_reads (max io)
SELECT * FROM (
SELECT cpu_time, disk_reads, rows_processed, sql_text FROM v$sqlarea
ORDER BY disk_reads DESC )
WHERE rownum <6
--------by max time elapsed user---------------
SELECT * FROM (
SELECT cpu_time, disk_reads, rows_processed,elapsed_time sql_text FROM v$sqlarea
ORDER BY elapsed_time DESC )
WHERE rownum <6
------- By run time memory
SELECT * FROM (
SELECT cpu_time, runtime_mem, rows_processed,elapsed_time sql_text FROM v$sqlarea
ORDER BY elapsed_time DESC )
WHERE rownum <6
---------check morethan1sess by user/osuser
set lines 100
col username form a25
col osuser form a25
col machine form a25
SELECT username, osuser,machine, count(*) from v$session where username is not
null group by username, osuser,machine having count(*) >1
-------------------------------
----- check buffer cache hit ratio:
SELECT SID ,OPNAME,TARGET,SOFAR,TOTALWORK FROM V$SESSION_LONGOPS WHERE
TIME_REMAINING!='0' AND sofar
COL id FORMAT 999
COL parent_id FORMAT 999 HEADING "PARENT"
COL operation FORMAT a35 TRUNCATE
COL object_name FORMAT a30
ed
Wrote file afiedt.buf
SELECT id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' ||
options operation, object_name
FROM (
SELECT id, parent_id, operation, options, object_name
FROM v$sql_plan
WHERE address = '&address'
AND hash_value = &hash_value
AND child_number = &child_number)
START WITH id = 0
CONNECT BY PRIOR id = parent_id
/
select distinct vs.sid, vs.serial#, vp.spid, vsql.child_number CN, vs.process pai, vs.username, vs.osuser, vs.machine, to_char(floor(vs.last_call_et/3600),'fm0000')||':'||
to_char(floor(mod(vs.last_call_et,3600)/60),'fm00')||':'||
to_char(mod(mod(vs.last_call_et,3600),60),'fm00')||' Hs' "ACTIVE SINCE", to_char(vs.logon_time,'DD-MON-YY HH24:MI:SS') Logon
from v$session vs, v$process vp, dba_kgllock dk, v$sql vsql
where vs.paddr=vp.addr
AND SQL_HASH_VALUE='&1'
AND DK.KGLLKUSE = VS.SADDR
AND DK.KGLLKHDL = VSQL.CHILD_ADDRESS
AND VS.SQL_ADDRESS = VSQL.ADDRESS
order by machine;
====================================================================================================---- TO FIND USERS LOCKING RESOURCES IN SAME ORDER (NOT DEADLOCKS),
--- SOLN: KILL THE LOCKING SESSION (SID)
select 'alter system kill session'||''''||sid||','||serial# ||''''||';'from v$session where sid=&sid1 AND USERNAME IS NOT NULL; --status='INACTIVE'
===================
---check the session wait on DB
select event, count(*) from v$session_wait group by event having count(*)>1;
=======================================================================================================================
============finding top CPU user SQL =============
SELECT * FROM (
SELECT cpu_time, disk_reads, rows_processed, sql_text FROM v$sqlarea
ORDER BY cpu_time DESC ) WHERE rownum <6;
=======================================================================================================================
-------by rows_processed, disk_reads (max io)
SELECT * FROM (SELECT cpu_time, disk_reads, rows_processed, sql_text FROM v$sqlarea ORDER BY disk_reads DESC )
WHERE rownum <6;
=======================================================================================================================
SELECT GROUP#,STATUS,TO_CHAR(FIRST_TIME,'DD-MON-YYYY hh:mi:ss') Time FROM V$LOG ORDER BY FIRST_TIME;
=======================================================================================================================
select count(*) from v$log_history where trunc(first_time)=trunc(sysdate);
=======================================================================================================================
===lONG RUNNING QUERY
SET LINESIZE 2000
col username form a10
col target form a24
col opname form a11
col program form a16
col terminal form a15
col sid form 999
select s.username,s.sid,s.program,s.terminal,OPNAME,TARGET,SOFAR ,TOTALWORK from v$session s,v$session_longops l where s.sid=l.sid and time_remaining!='0';
======================================================================================
==CURRENT SESSION ACTIVE
SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS NOT NULL AND STATUS='ACTIVE';
=======================================================================================================================
---Temp seg usages
col username form a15
col tablespace form a10
select s.username,s.sid,blocks,tablespace,segtype from v$tempseg_usage t ,v$session s where t.session_num=s.serial# order by extents;
=======================================================================================================================
------kill session
select 'alter system kill session'||''''||sid||','||serial# ||''''||';'from v$session where status='INACTIVE' AND USERNAME IS NOT NULL
=======================================================================================================================
=======================================================================================================================
--to check free space in SGA
SELECT pool,name,bytes/1024/1024 "mb" FROM V$SGASTAT WHERE NAME LIKE '%free memory%';
=======================================================================================================================
$$$$$$$$$$Log OPS $$$$$$$$$$$$$
SET LINESIZE 2000
col username form a10
col target form a24
col opname form a11
col program form a16
col terminal form a15
col sid form 999
col sql_text form a200
select s.username,s.sid,s.program,s.terminal,OPNAME,TARGET,SOFAR ,TOTALWORK,s.osuser,q.sql_text from v$session s,v$session_longops l,v$sqlarea q where s.sid=l.sid and s.SQL_ADDRESS=q.ADDRESS and time_remaining!='0'
=======================================================================================================================
-- to check blocker/waiter using v$lock in oracle 9i : works FAST:
SELECT /*+ RULE */ l1.sid, ' IS BLOCKING ', l2.sid
FROM v$lock l1, v$lock l2
WHERE l1.block =1
AND l2.request > 0
AND l1.id1=l2.id1
AND l1.id2=l2.id2;
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
create view bsess as SELECT l1.sid SID, '||IS BLOCKING||' blk ,l2.sid SID2 FROM v$lock l1, v$lock l2 WHERE l1.block =1 AND l2.request > 0 AND l1.id1=l2.id1 AND l1.id2=l2.id2
create or replace view bsessv2 as SELECT s1.status,s1.serial# ,l1.sid, ' || IS BLOCKING || ' blk , l2.sid sid2 ,s2.serial# serial2,s2.status status2
FROM v$lock l1, v$lock l2 ,v$session s1,v$session s2 WHERE l1.block =1 AND l2.request > 0
AND l1.id1=l2.id1 AND l1.id2=l2.id2;
^^^^^^^^^^^^^^^^^ sql statement ^^^^^^^^^^^^^^^^^
Full sql text
select x.sql_text from v$session s,v$sqltext x where s.sql_address=x.address and s.sid = &which_sid
order by sid, piece asc;
select ses.sid SID,sqa.SQL_TEXT SQL from v$session ses, v$sqlarea sqa, v$process proc where ses.paddr=proc.addr and ses.sql_hash_value=sqa.hash_value and proc.spid='&ospid';
select sid,USERNAME,sql_text,to_char(LOGON_TIME,'dd-mon-yyyy HH24:MI'),status,machine,program from v$session,v$sqlarea
where sid =&sid1 and SQL_ADDRESS=ADDRESS;--hash_value=sql_hash_value
select sid,USERNAME,sql_text,LOGON_TIME from v$session,v$sqlarea where sid =&sid1 and SQL_ADDRESS=ADDRESS;--hash_value=sql_hash_value
select sid,serial# from v$session where process='';
select sql_text from v$sqlarea a, v$session b where a.hash_value = b.sql_hash_value
and b.sid = 2178
select ses.sid SID,ses.serial#,sqa.SQL_TEXT SQL from v$session ses, v$sqlarea sqa, v$process proc where ses.paddr=proc.addr and ses.sql_hash_value=sqa.hash_value and proc.spid=17480;
=======================================================================================================================
----FIND SID USING PROCESS ID(OS)
SELECT SID,SPID,S.USERNAME,S.STATUS FROM V$PROCESS P, V$SESSION S WHERE ADDR=PADDR AND SPID=&osid;
=======================================================================================================================
--------
COL id FORMAT 999
COL parent_id FORMAT 999 HEADING "PARENT"
COL operation FORMAT a35 TRUNCATE
COL object_name FORMAT a30
SELECT id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' ||
options operation, object_name
FROM (
SELECT id, parent_id, operation, options, object_name
FROM v$sql_plan
WHERE address = '&address'
AND hash_value = &hash_value
AND child_number = &child_number)
START WITH id = 0
CONNECT BY PRIOR id = parent_id
/
=======================================================================================================================
select distinct vs.sid, vs.serial#, vp.spid, vsql.child_number CN, vs.process pai, vs.username, vs.osuser, vs.machine, to_char(floor(vs.last_call_et/3600),'fm0000')||':'||
to_char(floor(mod(vs.last_call_et,3600)/60),'fm00')||':'||
to_char(mod(mod(vs.last_call_et,3600),60),'fm00')||' Hs' "ACTIVE SINCE", to_char(vs.logon_time,'DD-MON-YY HH24:MI:SS') Logon
from v$session vs, v$process vp, dba_kgllock dk, v$sql vsql
where vs.paddr=vp.addr
AND SQL_HASH_VALUE='&1'
AND DK.KGLLKUSE = VS.SADDR
AND DK.KGLLKHDL = VSQL.CHILD_ADDRESS
AND VS.SQL_ADDRESS = VSQL.ADDRESS
order by machine;
=======================================================================================================================
select /*+ RULE */ l1.sid,'IS BLOCKING',l2.sid from v$lock l1,v$lock l2
where l1.block=1 and l2.request>0 and l1.id1=l2.id1 and l1.id2=l2.id2
=======================================================================================================================
select /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
/
*****************killing os level in unix where session marked as kill (in v$session (status)************
select username, sid, serial#, status from v$session where status='KILLED'
select p.spid,s.sid from v$process p, v$session s where p.addr=s.paddr and s.status='KILLED';
SELECT SID,SPID,S.USERNAME,S.STATUS,s.machine FROM V$PROCESS P, V$SESSION S WHERE ADDR=PADDR AND SPID=&osid;
SELECT p.spid,S.USERNAME,S.STATUS,s.machine FROM V$PROCESS P, V$SESSION S WHERE ADDR=PADDR AND s.sid=
select vs.sid, vs.username, vs.osuser, vs.process fg_pid,
vp.spid bg_pid from v$session vs, v$process vp
where vs.paddr = vp.addr
/
If you get something like:
SID USERNAME OSUSER FG_PID BG_PID
---- --------------- ---------- --------- ---------
10 JULIAN JULIAN 1965:6969 1234
run in unix as the oracle user
kill -9 1234
Find put sid of unix process id which taking long time ang high cpu usage
select vs.sid, vs.username, vs.osuser, vs.process fg_pid,
vp.spid bg_pid from v$session vs, v$process vp
where vs.paddr = vp.addr and vp.spid=
******************************************************************
JOB WAIT QUEUE's
select * from v$system_event where event like ‘%wait%’;
select s.sid, s.username, s.osuser, s.type, s.machine, s.program, p.program,
s.event from v$session s, v$process p where s.paddr = p.addr and s.event = 'jobq slave wait'
********************************************************************************
Waiting time by user
select sesion.sid, sesion.username,sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time from v$active_session_history active_session_history, v$session sesion where active_session_history.sample_time between sysdate - 60/2880 and sysdate and active_session_history.session_id = sesion.sid group by sesion.sid, sesion.username order by 3;
****************************************************************************************************
Sql using more resourcec
select active_session_history.user_id, dba_users.username,sqlarea.sql_text, sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time from v$active_session_history active_session_history,
v$sqlarea sqlarea, dba_users where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.sql_id = sqlarea.sql_id and active_session_history.user_id = dba_users.user_id
group by active_session_history.user_id,sqlarea.sql_text, dba_users.username order by 4;
***************************************************************************************************
Objects highest resources
select dba_objects.object_name, dba_objects.object_type, active_session_history.event, sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time from v$active_session_history active_session_history, dba_objects
where active_session_history.sample_time between sysdate - 60/2880 and sysdate and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event order by 4;
****************************************************************************************************
select * from v$waitstat;
************************************
All Wait events
SELECT event, total_waits, time_waited, percent FROM (SELECT se.event, se.total_waits, se.time_waited,
(se.time_waited / total.total_waittime) * 100 percent FROM (SELECT sum (time_waited) total_waittime
FROM v$system_event WHERE event NOT IN ('pmon timer', 'ges pmon to exit', 'ges lmd and pmon to attach',
'ges cached resource cleanup', 'parallel recovery coordinator waits for cleanup of slaves', 'smon timer', 'index (re)build online cleanup',
'dispatcher timer', 'dispatcher listen timer', 'timer in sksawat', 'SQL*Net message to client', 'SQL*Net message to dblink', 'SQL*Net more data to client',
'SQL*Net more data to dblink', 'SQL*Net message from client','SQL*Net more data from client','SQL*Net message from dblink',
'SQL*Net more data from dblink','SQL*Net break/reset to client','SQL*Net break/reset to dblink','PL/SQL lock timer','rdbms ipc message',
'ges remote message','gcs remote message','jobq slave wait','Null event' )
AND time_waited > 10) total, v$system_event se WHERE se.event NOT IN ('pmon timer', 'ges pmon to exit', 'ges lmd and pmon to attach',
'ges cached resource cleanup', 'parallel recovery coordinator waits for cleanup of slaves', 'smon timer','index (re)build online cleanup',
'dispatcher timer','dispatcher listen timer', 'timer in sksawat', 'SQL*Net message to client', 'SQL*Net message to dblink',
'SQL*Net more data to client','SQL*Net more data to dblink','SQL*Net message from client','SQL*Net more data from client',
'SQL*Net message from dblink', 'SQL*Net more data from dblink','SQL*Net break/reset to client','SQL*Net break/reset to dblink','PL/SQL lock timer',
'rdbms ipc message', 'ges remote message','gcs remote message', 'jobq slave wait', 'Null event' )
AND se.time_waited > 10 ORDER BY percent DESC) WHERE ROWNUM < 10;
*****************************************************************************
Dynamic RMAN Views for Past and Current Jobs
col STATUS format a9
col hrs format 999.99
select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key
col ins format a10
col outs format a10
select SESSION_KEY,OPTIMIZED, COMPRESSION_RATIO, INPUT_BYTES_PER_SEC_DISPLAY ins,
OUTPUT_BYTES_PER_SEC_DISPLAY outs,TIME_TAKEN_DISPLAY from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
select * from V$RMAN_BACKUP_TYPE;
select output from v$rman_output where session_key = 4 order by recid;
select output from v$rman_output where session_key = 4 order by recid;
*************************************************************************************************
Database Growth
select to_char(creation_time, 'RRRR Month') "Month", sum(bytes)/1024/1024
"Growth in Meg" from sys.v_$datafile where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month');
***************************************************************************************************
The following query shows the library cache latch information:
select name,gets,misses,sleeps from v$latch where name like 'library%';
To get the OS process id:
select a.name,pid from v$latch a , V$latchholder b
where a.addr=b.laddr and a.name = 'library cache%';
select count(*) number_of_waiters from v$session_wait w, v$latch l
where w.wait_time = 0 and w.event = 'latch free'
and w.p2 = l.latch# and l.name like 'library%';
It is also very useful to just select from v$session_wait to determine what
else is causing a slowdown:
select * from v$session_wait where event != 'client message'
and event not like '%NET%' and wait_time = 0 and sid > 5;
To determine the percentage of sql statement parse calls that find a cursor to
share you can execute the following:
select gethitratio from v$librarycache where namespace = 'SQL AREA';
To identify the SQL statements that are receiving a lot of parse calls execute
the following query:
select sql_text, parse_calls, executions from v$sqlarea
where parse_calls > 100 and executions < 2*parse_calls;
select name, value from v$sysstat where name like 'parse count%';
**********************************************************************************************************
setting Opatch path in UNIX
export PATH=$PATH:$ORACLE_HOME/OPatch
************************************************************************************************************
Jobq slave wait
select s.sid,p.spid,s.username, s.osuser, s.type, s.machine, s.program, p.program, s.event
from v$session s, v$process p where s.paddr = p.addr and s.event = 'jobq slave wait'
************************************************************************************************************
select s.username,b.sid, a.STATISTIC#, name, b.value from v$sesstat b, v$statname a, v$session s
where a.STATISTIC# = b.STATISTIC# and ( name like '%physical%' or name like '%consist%' )
and b.sid = s.sid and s.program not like 'oracle@%'
order by 2,4
************************************************************************************************************
enq: TX - row lock contention
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request
/
for which SQL currently is waiting to,
select sid, sql_text from v$session s, v$sql q where sid in (select sid from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));
The blocking session is,
select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
************************************************************************************************************
Latch Info
Select n.name, l.sleeps from v$latch l, v$latchname n where n.latch#=l.latch# and l.sleeps > 0 order by l.sleeps
************************************************************************************************************
JOB's
select job,SCHEMA_USER,LOG_USER,PRIV_USER,what from dba_jobs where SCHEMA_USER='FINSTAGE_PROD';
************************************************************************************************************
licence max watermark
select RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION,
INITIAL_ALLOCATION from v$resource_limit
/
****************************************************************************************************
script to recompile PL/SQL packages:
Set heading off;
set feedback off;
set echo off;
Set lines 999;
Spool run_invalid.sql
select 'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from dba_objects where status = 'INVALID'and
object_type in ('PACKAGE','FUNCTION','PROCEDURE');
spool off;
set heading on;
set feedback on;
set echo on;
****************************************************************************************************
index usage scan
col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Operation’ format a15
col c3 heading ‘Option’ format a15
col c4 heading ‘Index|Usage|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2
select p.object_name c1, p.operation c2, p.options c3,
count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s
where p.object_owner <> 'SYS' and p.operation like '%INDEX%'
and p.sql_id = s.sql_id group by
p.object_name, p.operation, p.options order by 1,2,3;
select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;
*******************************************************************
showing gather stats job in oracle 10g
select state from dba_scheduler_jobs where job_name ='GATHER_STATS_JOB';
select job_name, job_type, program_name, schedule_name, job_class
from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
*******************************************************************
col sid format 999
col process format 9999
col sql_text format a810
set linesize 1500
select sid,process, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));
*******************************************************************
IMP monitoring
SELECT
SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM
sys.v_$sqlarea
WHERE
sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;
*******************************************************************
No comments:
Post a Comment