Sunday, September 20, 2009
Check log sequece required for recovery in Oracle
select sequence# from V$log_history where 9151813979322 between first_change# and next_change#
Open cursor usage in Oracle
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;
check Undo users and uncommited transactions in Oracle
set lines 300
set pages 30000
SELECT a.sid, a.username, b.xidusn, b.used_urec Num_of_records, b.used_ublk USED_BLOCKS,(b.used_ublk * ( select value from v$parameter where name = 'db_block_size'))/1024/1024 MB_USED
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
select
(select sum(bytes/1024/1024)
from dba_data_files
where tablespace_name = (select upper(value)
from V$parameter where name = 'undo_tablespace')) TOTAL_UNDO_SIZE_MB,
nvl((SELECT sum(b.used_ublk * ( select value from v$parameter where name = 'db_block_size')/1024/1024)
FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr),0) TOTAL_UNDO_USED_MB,
(select sum(bytes/1024/1024)
from dba_data_files
where tablespace_name = (select upper(value)
from V$parameter where name = 'undo_tablespace')) - nvl((SELECT sum(b.used_ublk * ( select value from v$parameter where name = 'db_block_size')/1024/1024)
FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr),0) FREE_MB_UNDO
from dual;
--#uncommited transactions
col osuser for a10
col username for a10
set lines 300
set pages 300
col sql_text for a60
select V$SESSION.OSUSER,spid,V$SESSION.status,
V$SESSION.USERNAME,V$SESSION.SID,
V$SESSION.SERIAL#,
V$SESSION.PROGRAM,
V$SESSION.MACHINE,
v$transaction.start_time,
to_number(sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 run_time
from
V$SESSION , v$transaction,V$process
WHERE v$transaction.ses_addr = V$SESSION.saddr and V$process.addr = V$SESSION.paddr;
select OSUSER,V$SESSION.status,
USERNAME,SID,
SERIAL#,
SQL_TEXT,
v$transaction.start_time,
to_number(sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 run_time
from v$transaction ,
V$SESSION ,
V$SQLAREA
WHERE( (V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS and V$SESSION.sql_hash_value = V$SQLAREA.HASH_VALUE )
OR ( V$SESSION.PREV_SQL_ADDR = V$SQLAREA.ADDRESS and V$SESSION.PREV_hash_value = V$SQLAREA.HASH_VALUE ))
and v$transaction.ses_addr = V$SESSION.saddr;
set pages 30000
SELECT a.sid, a.username, b.xidusn, b.used_urec Num_of_records, b.used_ublk USED_BLOCKS,(b.used_ublk * ( select value from v$parameter where name = 'db_block_size'))/1024/1024 MB_USED
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
select
(select sum(bytes/1024/1024)
from dba_data_files
where tablespace_name = (select upper(value)
from V$parameter where name = 'undo_tablespace')) TOTAL_UNDO_SIZE_MB,
nvl((SELECT sum(b.used_ublk * ( select value from v$parameter where name = 'db_block_size')/1024/1024)
FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr),0) TOTAL_UNDO_USED_MB,
(select sum(bytes/1024/1024)
from dba_data_files
where tablespace_name = (select upper(value)
from V$parameter where name = 'undo_tablespace')) - nvl((SELECT sum(b.used_ublk * ( select value from v$parameter where name = 'db_block_size')/1024/1024)
FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr),0) FREE_MB_UNDO
from dual;
--#uncommited transactions
col osuser for a10
col username for a10
set lines 300
set pages 300
col sql_text for a60
select V$SESSION.OSUSER,spid,V$SESSION.status,
V$SESSION.USERNAME,V$SESSION.SID,
V$SESSION.SERIAL#,
V$SESSION.PROGRAM,
V$SESSION.MACHINE,
v$transaction.start_time,
to_number(sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 run_time
from
V$SESSION , v$transaction,V$process
WHERE v$transaction.ses_addr = V$SESSION.saddr and V$process.addr = V$SESSION.paddr;
select OSUSER,V$SESSION.status,
USERNAME,SID,
SERIAL#,
SQL_TEXT,
v$transaction.start_time,
to_number(sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 run_time
from v$transaction ,
V$SESSION ,
V$SQLAREA
WHERE( (V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS and V$SESSION.sql_hash_value = V$SQLAREA.HASH_VALUE )
OR ( V$SESSION.PREV_SQL_ADDR = V$SQLAREA.ADDRESS and V$SESSION.PREV_hash_value = V$SQLAREA.HASH_VALUE ))
and v$transaction.ses_addr = V$SESSION.saddr;
Temporary tablespace usage in Oracle
--
set lines 200
col sid_serial for a15
col osuser for a10
col module for a20
col program for a30
col tablespace for a10
-- Listing of temp segments.
--
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
--
-- Temp segment usage per session.
--
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
--
-- Temp segment usage per statement.
--
SELECT S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
set lines 200
col sid_serial for a15
col osuser for a10
col module for a20
col program for a30
col tablespace for a10
-- Listing of temp segments.
--
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
--
-- Temp segment usage per session.
--
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
--
-- Temp segment usage per statement.
--
SELECT S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
Calculate Optimal Undo in Oracle
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
checking long operations in Oracle RAC
#longopsrac.sql#
#--------------#
SELECT s.sid,
s.serial#,
s.machine,
TRUNC(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
TRUNC(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#
AND s.inst_id = sl.inst_id
AND sl.sofar < sl.totalwork
;
#--------------#
SELECT s.sid,
s.serial#,
s.machine,
TRUNC(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
TRUNC(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#
AND s.inst_id = sl.inst_id
AND sl.sofar < sl.totalwork
;
View RMAN jobs in Oracle
set lines 150 pages 30000
col ins format a10
col outs format a10
col TIME_TAKEN_DISPLAY format a10
select SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,input_type,
INPUT_BYTES_PER_SEC_DISPLAY ins,
OUTPUT_BYTES_PER_SEC_DISPLAY outs,
TIME_TAKEN_DISPLAY,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
,status
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
select output
from v$rman_output
where session_key = &session_key
order by recid;
@longopsrac
col ins format a10
col outs format a10
col TIME_TAKEN_DISPLAY format a10
select SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,input_type,
INPUT_BYTES_PER_SEC_DISPLAY ins,
OUTPUT_BYTES_PER_SEC_DISPLAY outs,
TIME_TAKEN_DISPLAY,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
,status
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
select output
from v$rman_output
where session_key = &session_key
order by recid;
@longopsrac
Views on Flashback Area Usage in Oracle
alter session set nls_date_format='DD-MON-YYYY HH24:MI:ss';
col OLDEST_FLASHBACK_SCN for 999999999999999
col name for a80
set lines 140 pages 3000
select name,SPACE_LIMIT/1024/1024 limit_mb,SPACE_USED/1024/1024 used_mb,round((SPACE_USED/SPACE_LIMIT)*100,2) percent_usage from V$RECOVERY_FILE_DEST;
select * from V$FLASH_RECOVERY_AREA_USAGE;
select estimated_flashback_size,flashback_size,oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
SELECT * FROM gv$flashback_database_stat;
set heading off
select '---------------' from dual;
select 'Flashback usage' from dual;
select '---------------' from dual;
set heading on
@/home/casenaem/flash_back_free.sql
set heading off
select '---------------' from dual;
select 'Restore Points' from dual;
select '---------------' from dual;
set heading on
select * from V$RESTORE_POINT;
col OLDEST_FLASHBACK_SCN for 999999999999999
col name for a80
set lines 140 pages 3000
select name,SPACE_LIMIT/1024/1024 limit_mb,SPACE_USED/1024/1024 used_mb,round((SPACE_USED/SPACE_LIMIT)*100,2) percent_usage from V$RECOVERY_FILE_DEST;
select * from V$FLASH_RECOVERY_AREA_USAGE;
select estimated_flashback_size,flashback_size,oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
SELECT * FROM gv$flashback_database_stat;
set heading off
select '---------------' from dual;
select 'Flashback usage' from dual;
select '---------------' from dual;
set heading on
@/home/casenaem/flash_back_free.sql
set heading off
select '---------------' from dual;
select 'Restore Points' from dual;
select '---------------' from dual;
set heading on
select * from V$RESTORE_POINT;
check Library Cache Lock in Oracle
select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = 'library cache pin'
order by lock_mode_held desc
/
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = 'library cache pin'
order by lock_mode_held desc
/
Backup passwords of users in a database
select 'alter user ' || username || ' identified by values ''' || password ||''';' from dba_users order by username;
Check Session Details in Oracle
set pages 49999
set lines 300
col PID for a7
col SID for 9999999999
col ser# for a7
col box for a29
col status for a9
col username for a20
col os_user for a12
col program for a25
col LOGON_TIME for a14
col module for a14
col machine for a10
select b.status as status,sql_hash_value,prev_hash_value,module,machine,
to_char(a.spid) as pid, to_char(b.sid) as sid, to_char(b.serial#) as "ser#", substr(b.machine,1,25) as box, b.username as username,
b.osuser as os_user, b.program program, to_char(b.LOGON_TIME,'dd-mon hh24:mi') LOGON_TIME from v$session b, v$process a where
b.paddr = a.addr
and type='USER'
and b.sid in (&session_id)
order by 3;
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
col SQL_TEXT for a100
SELECT b.sid,a.sql_text
FROM v$sqltext_with_newlines a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid in (&session_id)
ORDER BY b.sid,a.piece;
PROMPT
SET PAGESIZE 14
set lines 300
col PID for a7
col SID for 9999999999
col ser# for a7
col box for a29
col status for a9
col username for a20
col os_user for a12
col program for a25
col LOGON_TIME for a14
col module for a14
col machine for a10
select b.status as status,sql_hash_value,prev_hash_value,module,machine,
to_char(a.spid) as pid, to_char(b.sid) as sid, to_char(b.serial#) as "ser#", substr(b.machine,1,25) as box, b.username as username,
b.osuser as os_user, b.program program, to_char(b.LOGON_TIME,'dd-mon hh24:mi') LOGON_TIME from v$session b, v$process a where
b.paddr = a.addr
and type='USER'
and b.sid in (&session_id)
order by 3;
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
col SQL_TEXT for a100
SELECT b.sid,a.sql_text
FROM v$sqltext_with_newlines a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid in (&session_id)
ORDER BY b.sid,a.piece;
PROMPT
SET PAGESIZE 14
Waiting sessions in Oracle database
col wait_status for a30
col event for a40
set lines 170 pages 3000
SELECT sid, serial#, spid, username, event, wait_status, wait_time_milli
FROM(
SELECT s.inst_id AS inst_id,
s.indx AS sid,
se.ksuseser AS serial#,
-- spid from v$process
p.ksuprpid AS spid,
-- columns from v$session
se.ksuudlna AS username,
decode(bitand(se.ksuseidl,11),1,'ACTIVE',0,
decode(bitand(se.ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,
'SNIPED', 'KILLED') AS status,
decode(ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE') AS server,
se.ksuseunm AS osuser,
se.ksusepid AS process,
se.ksusemnm AS machine,
se.ksusetid AS terminal,
se.ksusepnm AS program,
decode(bitand(se.ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?') AS type,
se.ksusesqh AS sql_hash_value,
se.ksusepha AS prev_hash_value,
se.ksuseapp AS module,
se.ksuseact AS action,
se.ksuseclid AS client_identifier,
se.ksuseobj AS row_wait_obj#,
se.ksusefil AS row_wait_file#,
se.ksuseblk AS row_wait_block#,
se.ksuseslt AS row_wait_row#,
se.ksuseltm AS logon_time,
se.ksusegrp AS resource_consumer_group,
s.ksussseq AS seq#,
e.kslednam AS event,
e.ksledp1 AS p1text,
s.ksussp1 AS p1,
s.ksussp1r AS p1raw,
e.ksledp2 AS p2text,
s.ksussp2 AS p2,
s.ksussp2r AS p2raw,
e.ksledp3 AS p3text,
s.ksussp3 AS p3,
s.ksussp3r AS p3raw,
decode(s.ksusstim,
-2, 'WAITED UNKNOWN TIME',
-1,'LAST WAIT < 1 microsecond', -- originally WAITED SHORT TIME
0,'CURRENTLY WAITING SINCE '|| s.ksusewtm || 's',
'LAST WAIT ' || s.ksusstim/1000 || ' ms (' ||
s.ksusewtm || 's ago)') wait_status,
to_number(decode(s.ksusstim,0,NULL,-1,NULL,-2,NULL, s.ksusstim/1000))
AS wait_time_milli
from x$ksusecst s, x$ksled e , x$ksuse se, x$ksupr p
where bitand(s.ksspaflg,1)!=0
and bitand(s.ksuseflg,1)!=0
and s.ksussseq!=0
and s.ksussopc=e.indx
and s.indx=se.indx
and se.ksusepro=p.addr
and e.kslednam != 'SQL*Net message from client'
and e.kslednam != 'Streams AQ: waiting for messages in the queue'
and e.kslednam != 'rdbms ipc message'
and e.kslednam != 'Streams AQ: waiting for time management or cleanup tasks'
and e.kslednam != 'Streams AQ: qmn coordinator idle wait'
);
col event for a40
set lines 170 pages 3000
SELECT sid, serial#, spid, username, event, wait_status, wait_time_milli
FROM(
SELECT s.inst_id AS inst_id,
s.indx AS sid,
se.ksuseser AS serial#,
-- spid from v$process
p.ksuprpid AS spid,
-- columns from v$session
se.ksuudlna AS username,
decode(bitand(se.ksuseidl,11),1,'ACTIVE',0,
decode(bitand(se.ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,
'SNIPED', 'KILLED') AS status,
decode(ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE') AS server,
se.ksuseunm AS osuser,
se.ksusepid AS process,
se.ksusemnm AS machine,
se.ksusetid AS terminal,
se.ksusepnm AS program,
decode(bitand(se.ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?') AS type,
se.ksusesqh AS sql_hash_value,
se.ksusepha AS prev_hash_value,
se.ksuseapp AS module,
se.ksuseact AS action,
se.ksuseclid AS client_identifier,
se.ksuseobj AS row_wait_obj#,
se.ksusefil AS row_wait_file#,
se.ksuseblk AS row_wait_block#,
se.ksuseslt AS row_wait_row#,
se.ksuseltm AS logon_time,
se.ksusegrp AS resource_consumer_group,
s.ksussseq AS seq#,
e.kslednam AS event,
e.ksledp1 AS p1text,
s.ksussp1 AS p1,
s.ksussp1r AS p1raw,
e.ksledp2 AS p2text,
s.ksussp2 AS p2,
s.ksussp2r AS p2raw,
e.ksledp3 AS p3text,
s.ksussp3 AS p3,
s.ksussp3r AS p3raw,
decode(s.ksusstim,
-2, 'WAITED UNKNOWN TIME',
-1,'LAST WAIT < 1 microsecond', -- originally WAITED SHORT TIME
0,'CURRENTLY WAITING SINCE '|| s.ksusewtm || 's',
'LAST WAIT ' || s.ksusstim/1000 || ' ms (' ||
s.ksusewtm || 's ago)') wait_status,
to_number(decode(s.ksusstim,0,NULL,-1,NULL,-2,NULL, s.ksusstim/1000))
AS wait_time_milli
from x$ksusecst s, x$ksled e , x$ksuse se, x$ksupr p
where bitand(s.ksspaflg,1)!=0
and bitand(s.ksuseflg,1)!=0
and s.ksussseq!=0
and s.ksussopc=e.indx
and s.indx=se.indx
and se.ksusepro=p.addr
and e.kslednam != 'SQL*Net message from client'
and e.kslednam != 'Streams AQ: waiting for messages in the queue'
and e.kslednam != 'rdbms ipc message'
and e.kslednam != 'Streams AQ: waiting for time management or cleanup tasks'
and e.kslednam != 'Streams AQ: qmn coordinator idle wait'
);
Saturday, September 12, 2009
Response Time Analysis Made Easy in Oracle Database 10g
Historically, in trying to achieve maximum database performance, Oracle DBAs and performance analysts have fought an uphill battle to obtain solid response time metrics for system as well as user session activity. The problem facing DBAs has always had two facets: first, determining exactly "where" the database or user sessions have been spending their time; and second, determining the objective nature of the user experience.
Response Time Analysis Made Easy in Oracle Database 10g
Shared via AddThis
Response Time Analysis Made Easy in Oracle Database 10g
Shared via AddThis
Subscribe to:
Posts (Atom)