IO time by tablespace
column name format a30
SELECT
ts.name,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,1,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI1,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,2,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI2,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,4,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI4,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,8,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI8,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,16,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI16,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,32,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI32,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,64,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI64,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,128,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI128,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,256,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI256,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,512,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI512,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,1024,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI1024,
round ( 100* MAX(DECODE(FH.SINGLEBLKRDTIM_MILLI,2048,FH.SINGLEBLKRDS,0))/(sum(sum(FH.SINGLEBLKRDS)) over(partition by ts.name)),3 ) MILLI2048,
round (100*sum(FH.SINGLEBLKRDS)/sum(sum(FH.SINGLEBLKRDS)) over(),2) AS total
FROM V$FILE_HISTOGRAM fh
join v$datafile df on df.file#=fh.file# and df.ENABLED='READ WRITE'
join v$tablespace ts on TS.TS#=df.ts#
GROUP BY ts.name ORDER BY total;
NAME MILLI1 MILLI2 MILLI4 MILLI8 MILLI16 MILLI32 MILLI64 MILLI128 MILLI256 MILLI512 MILLI1024 MILLI2048 TOTAL
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ANUJTEST 0 0 0 0 0 0 100 0 0 0 0 0 0
DROP1 0 0 0 0 0 0 0 100 0 0 0 0 0
EXAMPLE 0 0 20 20 40 20 0 0 0 0 0 0 0
PERFSTAT 0 0 0 0 0 0 0 100 0 0 0 0 0
TSAPEXF 0 0 0 0 0 0 0 100 0 0 0 0 0
RMAN 0 0 0 0 0 0 0 100 0 0 0 0 0
TEST 0 0 0 0 0 0 0 100 0 0 0 0 0
TSAPEXU 8.696 0 13.043 21.739 43.478 4.348 4.348 4.348 0 0 0 0 .02
UNDOTBS1 59.375 0 3.125 6.25 15.625 12.5 0 3.125 0 0 0 0 .03
SYSAUX 60.946 1.789 2.288 7.128 13.123 10.906 3.139 .573 .1 .004 0 0 23.62
SYSTEM 80.917 .588 .908 3.911 6.091 4.106 1.798 1.25 .386 .033 .011 0 26.68
USERS 89.227 .883 1.867 2.821 2.661 1.887 .565 .071 .018 0 0 0 49.64
Monday, 17 October 2011
Oracle Top IO objects user wise
Top IO objects
Top 20 IO OBJECTS by owner
[code]
set linesize 200 pagesize 100
select * from (
select t.* from (
select owner,object_name,
round(100*(sum(decode( statistic_name, 'logical reads', VALUE,null)) /sum(sum(VALUE)) over(partition by owner, object_name)) ,3) as l_reads,
round(100*(sum(decode( statistic_name, 'physical reads', VALUE,null))/sum(sum(VALUE)) over(partition by owner, object_name)) ,3) as p_reads,
round(100*(sum(decode( statistic_name, 'physical reads direct', VALUE,null))/sum(sum(VALUE)) over(partition by owner, object_name)) ,3) as p_reads_d,
round(100*(sum(decode( statistic_name, 'physical writes', VALUE,'physical writes direct', VALUE,null)) /sum(sum(VALUE)) over(partition by owner, object_name)) ,3) as p_writes,
round(100*sum(value)/sum(sum(value)) over(),2) AS total
from gv$segment_statistics
where object_type in ('TABLE', 'INDEX')
--and owner='OWNER'
and owner not in ('SYS', 'SYSTEM')
and statistic_name in ('logical reads','physical reads', 'physical reads direct', 'physical writes', 'physical writes direct')
group by object_name,owner
order by sum(value) desc )t
where rownum < 21)
/
set linesize 300 pagesize 300
col object_name for a25
col owner for a20
col statistic_name for a20
SELECT ROWNUM AS RANK, Seg_Lio.*
FROM ( SELECT
-- St.Statistic_Name,
St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'LIO' AS Unit
FROM gv$segment_Statistics St
WHERE St.Statistic_Name = 'logical reads'
ORDER BY St.VALUE DESC) Seg_Lio
WHERE owner not in ('SYS', 'SYSTEM')
and object_type='TABLE'
and ROWNUM <= 20
UNION ALL
SELECT ROWNUM AS RANK, Seq_Pio_r.*
FROM ( SELECT
--St.Statistic_Name,
St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'PIO Reads' AS Unit
FROM gv$segment_Statistics St
WHERE St.Statistic_Name = 'physical reads'
ORDER BY St.VALUE DESC) Seq_Pio_r
WHERE owner not in ('SYS', 'SYSTEM')
and object_type='TABLE'
and ROWNUM <= 20
UNION ALL
SELECT ROWNUM AS RANK, Seq_Pio_w.*
FROM ( SELECT
--St.Statistic_Name,
St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'PIO Writes' AS Unit
FROM gv$segment_Statistics St
WHERE St.Statistic_Name = 'physical writes'
ORDER BY St.VALUE DESC) Seq_Pio_w
WHERE owner not in ('SYS', 'SYSTEM')
and object_type='TABLE'
and ROWNUM <= 20
UNION ALL
SELECT ROWNUM AS RANK, Seq_Pio_w.*
FROM ( SELECT
--St.Statistic_Name,
St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'PIO READS Direct' AS Unit
FROM gv$segment_Statistics St
WHERE St.Statistic_Name = 'physical reads direct'
ORDER BY St.VALUE DESC) Seq_Pio_w
WHERE owner not in ('SYS', 'SYSTEM')
and object_type='TABLE'
and ROWNUM <= 20
UNION ALL
SELECT ROWNUM AS RANK, Seq_Pio_w.*
FROM ( SELECT
--St.Statistic_Name,
St.Owner,
St.Obj#,
St.Object_Type,
St.Object_Name,
St.VALUE,
'DB Block changes' AS Unit
FROM gv$segment_Statistics St
WHERE St.Statistic_Name = 'db block changes'
ORDER BY St.VALUE DESC) Seq_Pio_w
WHERE owner not in ('SYS', 'SYSTEM')
and object_type='TABLE'
and ROWNUM <= 20;
define statistic_name='physical writes'
set lines 140 pages 100
col owner format A12
col object_name format A30
col statistic_name format A30
col object_type format A10
col value format 99999999999
col perc format 99.99
undef statistic_name
break on statistic_name
with segstats as (
select * from (
select inst_id, owner, object_name, object_type , value , rank() over (partition by inst_id, statistic_name order by value desc ) rnk , statistic_name from gv$segment_statistics
where value >0
and statistic_name like '%'||'&&statistic_name' ||'%'
) where rnk <31
) ,
sumstats as ( select inst_id, statistic_name, sum(value) sum_value from gv$segment_statistics group by statistic_name, inst_id)
select a.inst_id, a.statistic_name, a.owner, a.object_name, a.object_type,a.value,(a.value/b.sum_value)*100 perc
from segstats a , sumstats b
where a.statistic_name = b.statistic_name
and a.inst_id=b.inst_id
order by a.statistic_name, a.value desc
/
with segstats as (
select * from (
select inst_id, owner, object_name, object_type , statistic_name , value, rank () over (partition by inst_id, statistic_name order by value desc ) rnk
from (
select inst_id, owner, object_name, object_type , statistic_name , sum(value) value from gv$segment_statistics
where value >0
and statistic_name like '%'||'&&statistic_name' ||'%'
group by inst_id, owner, object_name, object_type, statistic_name
)
) where rnk <31
),
sumstats as ( select inst_id, statistic_name, sum(value) sum_value from gv$segment_statistics group by statistic_name, inst_id)
select a.inst_id, a.statistic_name, a.owner, a.object_name, a.object_type,a.value,(a.value/b.sum_value)*100 perc
from segstats a , sumstats b
where a.statistic_name = b.statistic_name
and a.inst_id=b.inst_id
order by a.statistic_name, a.inst_id, a.value desc
/
Oracle desc table
desc.sql
!cat desc1.sql
spool /tmp/&&1.txt
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
SELECT 'SELECT ' FROM DUAL;
select chr(9)||lower(column_name)||',' from USER_tab_columns
where table_Name = UPPER('&&1');
SELECT ' FROM &1 WHERE ROWNUM <2' FROM DUAL;
SET HEADING ON
UNDEFINE 1
SET VERIFY ON
SET FEEDBACK ON
SPOOL OFF
desc.sql
SET PAUSE ON
SET PAUSE 'Any Key...;'
SET PAGES 24
col Tab_own form A10
col tab_name form a22 wrap
col col_name form a28 wrap
col col_def form A14
break on tab_own skip 1 on tab_name skip 1
spool tab_desc.lst
select
owner Tab_Own
,table_name Tab_Name
,column_name Col_Name
,decode(NULLABLE,'Y','N','Y') Mand
,data_type||decode(data_type
,'NUMBER','('
||decode(to_char(data_precision)
,null,'38'
, to_char(data_precision)||
decode(data_scale,null,''
, ','||data_scale)
)
||')'
,'DATE',null
,'LONG',null
,'LONG RAW',null
,'('||Substr(DATA_LENGTH,1,5)||')'
) col_def
from dba_tab_columns
where table_name like upper (nvl('EMP','ANUJ')||'%')
order by 1,2,column_id,3,4
/
spool off
clear col
SQL> @desc emp
Any Key...;
TAB_OWN TAB_NAME COL_NAME M COL_DEF
---------- ---------------------- ---------------------------- - --------------
ANUJ EMP EMPNO Y NUMBER(4,0)
ENAME N VARCHAR2(10)
JOB N VARCHAR2(9)
MGR N NUMBER(4,0)
HIREDATE N DATE
SAL N NUMBER(7,2)
COMM N NUMBER(7,2)
DEPTNO N NUMBER(2,0)
HR EMPLOYEES EMPLOYEE_ID Y NUMBER(6,0)
FIRST_NAME N VARCHAR2(20)
LAST_NAME Y VARCHAR2(25)
EMAIL Y VARCHAR2(25)
PHONE_NUMBER N VARCHAR2(20)
HIRE_DATE Y DATE
JOB_ID Y VARCHAR2(10)
SALARY N NUMBER(8,2)
COMMISSION_PCT N NUMBER(2,2)
MANAGER_ID N NUMBER(6,0)
DEPARTMENT_ID N NUMBER(4,0)
Any Key...;
TAB_OWN TAB_NAME COL_NAME M COL_DEF
---------- ---------------------- ---------------------------- - --------------
HR EMP_DETAILS_VIEW EMPLOYEE_ID Y NUMBER(6,0)
JOB_ID Y VARCHAR2(10)
MANAGER_ID N NUMBER(6,0)
DEPARTMENT_ID N NUMBER(4,0)
LOCATION_ID N NUMBER(4,0)
COUNTRY_ID N CHAR(2)
FIRST_NAME N VARCHAR2(20)
LAST_NAME Y VARCHAR2(25)
SALARY N NUMBER(8,2)
COMMISSION_PCT N NUMBER(2,2)
DEPARTMENT_NAME Y VARCHAR2(30)
JOB_TITLE Y VARCHAR2(35)
CITY Y VARCHAR2(30)
STATE_PROVINCE N VARCHAR2(25)
COUNTRY_NAME N VARCHAR2(40)
REGION_NAME N VARCHAR2(25)
SCOTT EMP EMPNO Y NUMBER(4,0)
ENAME N VARCHAR2(10)
Any Key...;
TAB_OWN TAB_NAME COL_NAME M COL_DEF
---------- ---------------------- ---------------------------- - --------------
SCOTT EMP JOB N VARCHAR2(9)
MGR N NUMBER(4,0)
HIREDATE N DATE
SAL N NUMBER(7,2)
COMM N NUMBER(7,2)
DEPTNO N NUMBER(2,0)
SYS EMP EMPNO N NUMBER(38)
44 rows selected.
!cat desc1.sql
spool /tmp/&&1.txt
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
SELECT 'SELECT ' FROM DUAL;
select chr(9)||lower(column_name)||',' from USER_tab_columns
where table_Name = UPPER('&&1');
SELECT ' FROM &1 WHERE ROWNUM <2' FROM DUAL;
SET HEADING ON
UNDEFINE 1
SET VERIFY ON
SET FEEDBACK ON
SPOOL OFF
desc.sql
SET PAUSE ON
SET PAUSE 'Any Key...;'
SET PAGES 24
col Tab_own form A10
col tab_name form a22 wrap
col col_name form a28 wrap
col col_def form A14
break on tab_own skip 1 on tab_name skip 1
spool tab_desc.lst
select
owner Tab_Own
,table_name Tab_Name
,column_name Col_Name
,decode(NULLABLE,'Y','N','Y') Mand
,data_type||decode(data_type
,'NUMBER','('
||decode(to_char(data_precision)
,null,'38'
, to_char(data_precision)||
decode(data_scale,null,''
, ','||data_scale)
)
||')'
,'DATE',null
,'LONG',null
,'LONG RAW',null
,'('||Substr(DATA_LENGTH,1,5)||')'
) col_def
from dba_tab_columns
where table_name like upper (nvl('EMP','ANUJ')||'%')
order by 1,2,column_id,3,4
/
spool off
clear col
SQL> @desc emp
Any Key...;
TAB_OWN TAB_NAME COL_NAME M COL_DEF
---------- ---------------------- ---------------------------- - --------------
ANUJ EMP EMPNO Y NUMBER(4,0)
ENAME N VARCHAR2(10)
JOB N VARCHAR2(9)
MGR N NUMBER(4,0)
HIREDATE N DATE
SAL N NUMBER(7,2)
COMM N NUMBER(7,2)
DEPTNO N NUMBER(2,0)
HR EMPLOYEES EMPLOYEE_ID Y NUMBER(6,0)
FIRST_NAME N VARCHAR2(20)
LAST_NAME Y VARCHAR2(25)
EMAIL Y VARCHAR2(25)
PHONE_NUMBER N VARCHAR2(20)
HIRE_DATE Y DATE
JOB_ID Y VARCHAR2(10)
SALARY N NUMBER(8,2)
COMMISSION_PCT N NUMBER(2,2)
MANAGER_ID N NUMBER(6,0)
DEPARTMENT_ID N NUMBER(4,0)
Any Key...;
TAB_OWN TAB_NAME COL_NAME M COL_DEF
---------- ---------------------- ---------------------------- - --------------
HR EMP_DETAILS_VIEW EMPLOYEE_ID Y NUMBER(6,0)
JOB_ID Y VARCHAR2(10)
MANAGER_ID N NUMBER(6,0)
DEPARTMENT_ID N NUMBER(4,0)
LOCATION_ID N NUMBER(4,0)
COUNTRY_ID N CHAR(2)
FIRST_NAME N VARCHAR2(20)
LAST_NAME Y VARCHAR2(25)
SALARY N NUMBER(8,2)
COMMISSION_PCT N NUMBER(2,2)
DEPARTMENT_NAME Y VARCHAR2(30)
JOB_TITLE Y VARCHAR2(35)
CITY Y VARCHAR2(30)
STATE_PROVINCE N VARCHAR2(25)
COUNTRY_NAME N VARCHAR2(40)
REGION_NAME N VARCHAR2(25)
SCOTT EMP EMPNO Y NUMBER(4,0)
ENAME N VARCHAR2(10)
Any Key...;
TAB_OWN TAB_NAME COL_NAME M COL_DEF
---------- ---------------------- ---------------------------- - --------------
SCOTT EMP JOB N VARCHAR2(9)
MGR N NUMBER(4,0)
HIREDATE N DATE
SAL N NUMBER(7,2)
COMM N NUMBER(7,2)
DEPTNO N NUMBER(2,0)
SYS EMP EMPNO N NUMBER(38)
44 rows selected.
Oracle Sqlplus variable declare
sqlplus var declare
sqlplus variable
sqlplus bind variable
SQL> var h varchar2(10)
SQL> begin :h := 'abcdf'; end;
/
PL/SQL procedure successfully completed.
SQL> print :h
H
--------------------------------
abcdf
SQL> var myvar varchar2(30)
SQL> exec :myvar := 'Anuj'
PL/SQL procedure successfully completed.
SQL> print myvar
MYVAR
----------------------------
Anuj
OR !!!
SQL> print :myvar
MYVAR
----------------------
Anuj
To unset variable
exec :myvar := '' or exec :myvar := NULL
You cannot undefine bind variables in sqlplus
================
VARIABLE btime VARCHAR2(30)
VARIABLE etime VARCHAR2(30)
exec :btime:='2022-01-26 12:00:00';
exec :etime:='2022-01-28 12:00:00';
select
count(*) count
--distinct a.machine,a.program,a.module
from dba_hist_active_sess_history a
where 1=1
and sample_time between to_date(:btime,'YYYY-MM-DD HH24:MI:SS') and to_date(:etime,'YYYY-MM-DD HH24:MI:SS')
;
COUNT
----------
41627
===for spool
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ; exec select max(snap_id) into :EndSnap from dba_hist_snapshot ; column awr new_val X select to_char(:BgnSnap||'_'||:EndSnap) awr from dual; spool awr_&X._file.txt --- select spool off
===
in Oracle 23c
Connected to: Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.3.0.23.09 SQL> var h varchar2(8) begin :h := '123456789'; end; / PL/SQL procedure successfully completed. SQL> print :h H -------------------------------- 12345678 SQL>
Sunday, 16 October 2011
Oracle Session Wait on single instance / RAC
Currently connected sessions waits, longops, sql, etc...
Oracle Wait info
select/*+ rule*/ distinct--because of multiple records in v$sql may exist
to_char(gv$session.LOGON_TIME,'YYYY.MM.DD HH24:MI:SS') as LOGON_TIME
, gv$session.STATUS
, gv$session.USERNAME
, gv$session.PROGRAM
, gv$session.INST_ID
, gv$session.SID
, gv$session.SERIAL#
, gv$transaction.START_TIME as TX_STIME
, gv$session.LAST_CALL_ET as LAST_CALL_ET
, gv$session_wait.EVENT
, v$latchname.NAME as LATCHNAME
, gv$session_wait.SECONDS_IN_WAIT
, dba_objects.owner||'.'||dba_objects.object_name req_object
, decode(nvl(gv$session.ROW_WAIT_OBJ#,-1),-1,'NONE',DBMS_ROWID.ROWID_CREATE( 1,
gv$session.ROW_WAIT_OBJ#, gv$session.ROW_WAIT_FILE#, gv$session.ROW_WAIT_BLOCK#,
gv$session.ROW_WAIT_ROW# )) req_rowid
, lockhold.inst_id as BLOCKING_INSTANCE
, lockhold.sid as BLOCKING_SESSION
, gv$session.COMMAND
, gv$session_longops.TIME_REMAINING as LONGOPS_CALL_RT
, gv$session_longops.MESSAGE as LONGOPS_MESSAGE
, gv$session_wait.P1
, gv$session_wait.P1TEXT
, gv$session_wait.P2 P2
, gv$session_wait.P2TEXT
, gv$session_wait.P3
, gv$session_wait.P3TEXT
, gv$sql.ADDRESS as SQL_ADDRESS
, gv$sql.HASH_VALUE as HASH_VALUE
, gv$sql.PLAN_HASH_VALUE as PLAN_HASH_VALUE
, gv$sql.OPTIMIZER_MODE as SQL_OPTIMIZER_MODE
, gv$sql.sql_text as SQL_TEXT
, sql1.ADDRESS as PREV_SQL_ADDRESS
, sql1.HASH_VALUE as PREV_HASH_VALUE
, sql1.PLAN_HASH_VALUE as PREV_PLAN_HASH_VALUE
, sql1.OPTIMIZER_MODE as PREV_SQL_OPTIMIZER_MODE
, sql1.sql_text as PREV_sql_text
--Network connection properties
, gv$session.SERVER
, gv$session.FAILOVER_TYPE
, gv$session.FAILOVER_METHOD
, gv$session.FAILED_OVER
, gv$session.FIXED_TABLE_SEQUENCE
--OS properties
, gv$session.MACHINE
, gv$session.MODULE
, gv$session.OSUSER
, gv$session.OWNERID
, gv$session.TERMINAL
, 'Alter system kill session '''||gv$session.SID||','||gv$session.SERIAL#||''';'
as KILL_SESSION
, gv$instance.HOST_NAME
, 'kill '||gv$process.SPID as KILL_SPID
from gv$session
, gv$instance
, gv$process
, gv$session_wait
, gv$sql
, gv$sql sql1
, gv$transaction
, v$latchname
, dba_objects
, gv$session_longops
, gv$lock lockwait
, gv$lock lockhold
where gv$session.INST_ID = gv$instance.INST_ID
and gv$session.PADDR=gv$process.ADDR(+)
and gv$session.INST_ID = gv$process.INST_ID(+)
and gv$session.sql_address=gv$sql.address(+)
and gv$session.INST_ID = gv$sql.INST_ID(+)
and gv$session.sql_hash_value=gv$sql.hash_value(+)
and gv$session.PREV_SQL_ADDR = sql1.address(+)
and gv$session.PREV_HASH_VALUE = sql1.hash_value(+)
and gv$session.SID=gv$session_wait.SID(+)
and gv$session.INST_ID = gv$session_wait.INST_ID(+)
and gv$session.SADDR=gv$transaction.SES_ADDR(+)
and gv$session.INST_ID = gv$transaction.INST_ID(+)
and (gv$session.SERIAL# <> 1 or upper(gv$session.PROGRAM) like '%LGWR%'
or upper(gv$session.PROGRAM) like '%DBW%' )--System session excluded,exc. DBWR
and upper(gv$session.PROGRAM) not like '%QMNC%'--Queue Monitor Coordinator excluded
and upper(gv$session.PROGRAM) not like '%Q0%'--Queue monitor processes excluded
and gv$session_wait.P2=v$latchname.LATCH#(+)
and gv$session.ROW_WAIT_OBJ# = dba_objects.object_id(+)
and gv$session.SID = gv$session_longops.sid(+)
and gv$session.INST_ID = gv$session_longops.INST_ID(+)
and gv$session.SERIAL# = gv$session_longops.SERIAL#(+)
and gv$session.sql_address = gv$session_longops.SQL_ADDRESS(+)
and gv$session.sql_hash_value = gv$session_longops.SQL_HASH_VALUE(+)
and nvl(gv$session_longops.SOFAR,0) <> nvl(gv$session_longops.TOTALWORK,1)
and gv$session.LOCKWAIT = lockwait.KADDR(+)
and lockwait.id1 = lockhold.id1(+)
and lockwait.id2 = lockhold.id2(+)
and nvl(lockwait.REQUEST,1) > 0
and nvl(lockwait.LMODE,0) = 0
and nvl(lockhold.REQUEST,0) = 0
and nvl(lockhold.LMODE,1) > 0
and nvl(lockwait.SID,0) <> nvl(lockhold.SID,1)
order by tx_stime, status, LOGON_TIME, username, machine asc;
------------
Prompt Active session waits ratio
select WAIT_EVENT
,lpad(TO_CHAR(PCTTOT,'990D99'),6)||'% waits with average duration ='
||TO_CHAR(AVERAGE_WAIT_MS,'9990D99')||'ms' as EVENT_VALUES
from (
select --RANK() OVER (order by sum(time_waited) desc) as RANK,
event as WAIT_EVENT--, sum(time_waited) time_waited_ms
,round(RATIO_TO_REPORT(sum(time_waited)) OVER ()*100,2) AS PCTTOT
,round(avg(average_wait)*10,2) as AVERAGE_WAIT_MS
from
(select se.SID, se.INST_ID, se.EVENT, se.TIME_WAITED,
se.AVERAGE_WAIT from gv$session_event se
where se.WAIT_CLASS not in ('Idle')
union
select ss.SID, ss.INST_ID, sn.NAME as EVENT
, ss.VALUE as TIME_WAITED
, 0 as AVERAGE_WAIT from gv$sesstat ss, v$statname sn
where ss."STATISTIC#" = sn."STATISTIC#"
and sn.NAME in ('CPU used when call started'))
where (sid, inst_id) in
(select sid, inst_id from gv$session
where gv$session.SERVICE_NAME not in ('SYS$BACKGROUND'))
group by event
order by PCTTOT desc) we;
Oracle Wait info
select/*+ rule*/ distinct--because of multiple records in v$sql may exist
to_char(gv$session.LOGON_TIME,'YYYY.MM.DD HH24:MI:SS') as LOGON_TIME
, gv$session.STATUS
, gv$session.USERNAME
, gv$session.PROGRAM
, gv$session.INST_ID
, gv$session.SID
, gv$session.SERIAL#
, gv$transaction.START_TIME as TX_STIME
, gv$session.LAST_CALL_ET as LAST_CALL_ET
, gv$session_wait.EVENT
, v$latchname.NAME as LATCHNAME
, gv$session_wait.SECONDS_IN_WAIT
, dba_objects.owner||'.'||dba_objects.object_name req_object
, decode(nvl(gv$session.ROW_WAIT_OBJ#,-1),-1,'NONE',DBMS_ROWID.ROWID_CREATE( 1,
gv$session.ROW_WAIT_OBJ#, gv$session.ROW_WAIT_FILE#, gv$session.ROW_WAIT_BLOCK#,
gv$session.ROW_WAIT_ROW# )) req_rowid
, lockhold.inst_id as BLOCKING_INSTANCE
, lockhold.sid as BLOCKING_SESSION
, gv$session.COMMAND
, gv$session_longops.TIME_REMAINING as LONGOPS_CALL_RT
, gv$session_longops.MESSAGE as LONGOPS_MESSAGE
, gv$session_wait.P1
, gv$session_wait.P1TEXT
, gv$session_wait.P2 P2
, gv$session_wait.P2TEXT
, gv$session_wait.P3
, gv$session_wait.P3TEXT
, gv$sql.ADDRESS as SQL_ADDRESS
, gv$sql.HASH_VALUE as HASH_VALUE
, gv$sql.PLAN_HASH_VALUE as PLAN_HASH_VALUE
, gv$sql.OPTIMIZER_MODE as SQL_OPTIMIZER_MODE
, gv$sql.sql_text as SQL_TEXT
, sql1.ADDRESS as PREV_SQL_ADDRESS
, sql1.HASH_VALUE as PREV_HASH_VALUE
, sql1.PLAN_HASH_VALUE as PREV_PLAN_HASH_VALUE
, sql1.OPTIMIZER_MODE as PREV_SQL_OPTIMIZER_MODE
, sql1.sql_text as PREV_sql_text
--Network connection properties
, gv$session.SERVER
, gv$session.FAILOVER_TYPE
, gv$session.FAILOVER_METHOD
, gv$session.FAILED_OVER
, gv$session.FIXED_TABLE_SEQUENCE
--OS properties
, gv$session.MACHINE
, gv$session.MODULE
, gv$session.OSUSER
, gv$session.OWNERID
, gv$session.TERMINAL
, 'Alter system kill session '''||gv$session.SID||','||gv$session.SERIAL#||''';'
as KILL_SESSION
, gv$instance.HOST_NAME
, 'kill '||gv$process.SPID as KILL_SPID
from gv$session
, gv$instance
, gv$process
, gv$session_wait
, gv$sql
, gv$sql sql1
, gv$transaction
, v$latchname
, dba_objects
, gv$session_longops
, gv$lock lockwait
, gv$lock lockhold
where gv$session.INST_ID = gv$instance.INST_ID
and gv$session.PADDR=gv$process.ADDR(+)
and gv$session.INST_ID = gv$process.INST_ID(+)
and gv$session.sql_address=gv$sql.address(+)
and gv$session.INST_ID = gv$sql.INST_ID(+)
and gv$session.sql_hash_value=gv$sql.hash_value(+)
and gv$session.PREV_SQL_ADDR = sql1.address(+)
and gv$session.PREV_HASH_VALUE = sql1.hash_value(+)
and gv$session.SID=gv$session_wait.SID(+)
and gv$session.INST_ID = gv$session_wait.INST_ID(+)
and gv$session.SADDR=gv$transaction.SES_ADDR(+)
and gv$session.INST_ID = gv$transaction.INST_ID(+)
and (gv$session.SERIAL# <> 1 or upper(gv$session.PROGRAM) like '%LGWR%'
or upper(gv$session.PROGRAM) like '%DBW%' )--System session excluded,exc. DBWR
and upper(gv$session.PROGRAM) not like '%QMNC%'--Queue Monitor Coordinator excluded
and upper(gv$session.PROGRAM) not like '%Q0%'--Queue monitor processes excluded
and gv$session_wait.P2=v$latchname.LATCH#(+)
and gv$session.ROW_WAIT_OBJ# = dba_objects.object_id(+)
and gv$session.SID = gv$session_longops.sid(+)
and gv$session.INST_ID = gv$session_longops.INST_ID(+)
and gv$session.SERIAL# = gv$session_longops.SERIAL#(+)
and gv$session.sql_address = gv$session_longops.SQL_ADDRESS(+)
and gv$session.sql_hash_value = gv$session_longops.SQL_HASH_VALUE(+)
and nvl(gv$session_longops.SOFAR,0) <> nvl(gv$session_longops.TOTALWORK,1)
and gv$session.LOCKWAIT = lockwait.KADDR(+)
and lockwait.id1 = lockhold.id1(+)
and lockwait.id2 = lockhold.id2(+)
and nvl(lockwait.REQUEST,1) > 0
and nvl(lockwait.LMODE,0) = 0
and nvl(lockhold.REQUEST,0) = 0
and nvl(lockhold.LMODE,1) > 0
and nvl(lockwait.SID,0) <> nvl(lockhold.SID,1)
order by tx_stime, status, LOGON_TIME, username, machine asc;
------------
Prompt Active session waits ratio
select WAIT_EVENT
,lpad(TO_CHAR(PCTTOT,'990D99'),6)||'% waits with average duration ='
||TO_CHAR(AVERAGE_WAIT_MS,'9990D99')||'ms' as EVENT_VALUES
from (
select --RANK() OVER (order by sum(time_waited) desc) as RANK,
event as WAIT_EVENT--, sum(time_waited) time_waited_ms
,round(RATIO_TO_REPORT(sum(time_waited)) OVER ()*100,2) AS PCTTOT
,round(avg(average_wait)*10,2) as AVERAGE_WAIT_MS
from
(select se.SID, se.INST_ID, se.EVENT, se.TIME_WAITED,
se.AVERAGE_WAIT from gv$session_event se
where se.WAIT_CLASS not in ('Idle')
union
select ss.SID, ss.INST_ID, sn.NAME as EVENT
, ss.VALUE as TIME_WAITED
, 0 as AVERAGE_WAIT from gv$sesstat ss, v$statname sn
where ss."STATISTIC#" = sn."STATISTIC#"
and sn.NAME in ('CPU used when call started'))
where (sid, inst_id) in
(select sid, inst_id from gv$session
where gv$session.SERVICE_NAME not in ('SYS$BACKGROUND'))
group by event
order by PCTTOT desc) we;
Oracle Active session on single Instance and RAC
Currently connected sessions waits, longops, sql, etc...
active session sessions
Instance
select/*+ RULE*/ distinct--because of multiple records in v$sql for PREV_SQL
to_char(gv$session.LOGON_TIME,'YYYY.MM.DD HH24:MI:SS') as LOGON_TIME
, gv$transaction.START_TIME as TX_STIME
, gv$session.LAST_CALL_ET as LAST_CALL_ET
, gv$session.STATUS
, gv$session.USERNAME
, gv$session.PROGRAM
, gv$session.SERVICE_NAME as service
, gv$session.INST_ID
, gv$session.SID
, gv$session.SERIAL#
, decode(gv$session_wait.state, 'WAITING', gv$session_wait.EVENT,
'On CPU / runqueue') as EVENT
, v$latchname.NAME as LATCHNAME
, gv$latchholder.sid
, gv$session_wait.SECONDS_IN_WAIT
, dba_objects.owner||'.'||dba_objects.object_name req_object
, decode(nvl(gv$session.ROW_WAIT_OBJ#,-1),-1,'NONE'
, DBMS_ROWID.ROWID_CREATE( 1, gv$session.ROW_WAIT_OBJ#
, gv$session.ROW_WAIT_FILE#, gv$session.ROW_WAIT_BLOCK#
, gv$session.ROW_WAIT_ROW# )) req_rowid
, lockhold.inst_id as BLOCKING_INSTANCE
, lockhold.sid as BLOCKING_SESSION
, gv$session.COMMAND
, gv$session_longops.TIME_REMAINING as LONGOPS_CALL_RT
, gv$session_longops.MESSAGE as LONGOPS_MESSAGE
, gv$session_wait.P1
, gv$session_wait.P1TEXT
, gv$session_wait.P1RAW /* X$BH.HLADDR for LATCH: CACHE BUFFERS CHAINS */
, gv$session_wait.P2 P2
, gv$session_wait.P2TEXT
, gv$session_wait.P3
, gv$session_wait.P3TEXT
, ses_optimizer_env38.VALUE as ses_optimizer_mode
, ses_optimizer_env48.VALUE as ses_cursor_sharing
, gv$sql.SQL_ID as SQL_ID
, gv$sql.PLAN_HASH_VALUE as PLAN_HASH_VALUE
, gv$sql.OPTIMIZER_MODE as SQL_OPTIMIZER_MODE
, gv$sql.sql_text as SQL_TEXT
, gv$sql.SQL_PROFILE
, gv$session.SQL_CHILD_NUMBER SQL_CHILD_NUMBER
, sql1.SQL_ID as PREV_SQL_ID
, sql1.PLAN_HASH_VALUE as PREV_PLAN_HASH_VALUE
, sql1.OPTIMIZER_MODE as PREV_SQL_OPTIMIZER_MODE
, sql1.sql_text as PREV_sql_text
, gv$session.PREV_CHILD_NUMBER
--Network connection properties
, gv$session.SERVER
, gv$session.FAILOVER_TYPE
, gv$session.FAILOVER_METHOD
, gv$session.FAILED_OVER
, gv$session.FIXED_TABLE_SEQUENCE
--OS properties
, gv$session.MACHINE
, gv$session.MODULE
, gv$session.OSUSER
, gv$session.OWNERID
, gv$session.TERMINAL
, 'Alter system kill session '''||gv$session.SID||','||gv$session.SERIAL#
||''';' as KILL_SESSION
--, gv$session.PROCESS
, gv$instance.HOST_NAME
, 'kill -9 '||gv$process.SPID as KILL_SPID
-- from 10g and above - SQL Trace info
, gv$session.SQL_TRACE, gv$session.SQL_TRACE_WAITS, gv$session.SQL_TRACE_BINDS
, 'begin sys.dbms_support.start_trace_in_session('||gv$session.SID||','||
gv$session.SERIAL#||', waits=>TRUE, binds=>TRUE );end;'
, 'begin sys.dbms_support.stop_trace_in_session('||gv$session.SID||','||
gv$session.SERIAL#||' );end;'
from gv$session
, gv$instance
, gv$process
, gv$session_wait
, gv$sql
, gv$sql sql1
, gv$transaction
, v$latchname
, dba_objects
, gv$session_longops
, gv$ses_optimizer_env ses_optimizer_env38
, gv$ses_optimizer_env ses_optimizer_env48
, gv$lock lockwait
, gv$lock lockhold
, gv$latchholder
where gv$session.INST_ID = gv$instance.INST_ID
and gv$session.PADDR=gv$process.ADDR(+)
and gv$session.INST_ID = gv$process.INST_ID(+)
and gv$session.sql_address=gv$sql.address(+)
and gv$session.sql_hash_value=gv$sql.hash_value(+)
and gv$session.SQL_CHILD_NUMBER = gv$sql.CHILD_NUMBER(+)
and gv$session.INST_ID = gv$sql.INST_ID(+)
and gv$session.PREV_SQL_ADDR = sql1.address(+)
and gv$session.PREV_HASH_VALUE = sql1.hash_value(+)
and gv$session.PREV_CHILD_NUMBER = sql1.CHILD_NUMBER(+)
and gv$session.SID=gv$session_wait.SID(+)
and gv$session.INST_ID = gv$session_wait.INST_ID(+)
and gv$session.SADDR=gv$transaction.SES_ADDR(+)
and gv$session.INST_ID = gv$transaction.INST_ID(+)
and gv$session.SERVICE_NAME not in ('SYS$BACKGROUND')
and gv$session.PROGRAM not like '%QMNC%' --Queue Monitor Coordinator excluded
and gv$session.PROGRAM not like '%q00%' --Queue monitor processes excluded
--and v$session.PROGRAM not like '%J00%' --DBMS_JOB processes excluded
and gv$session_wait.P2=v$latchname.LATCH#(+)
and gv$session_wait.p1raw = gv$latchholder.laddr(+)
and gv$session.ROW_WAIT_OBJ# = dba_objects.object_id(+)
and gv$session.SID = gv$session_longops.sid(+)
and gv$session.INST_ID = gv$session_longops.INST_ID(+)
and gv$session.SERIAL# = gv$session_longops.SERIAL#(+)
and gv$session.sql_address = gv$session_longops.SQL_ADDRESS(+)
and gv$session.sql_hash_value = gv$session_longops.SQL_HASH_VALUE(+)
and (nvl(gv$session_longops.TIME_REMAINING, 1) > 0
or
nvl(gv$session_longops.START_TIME, sysdate) = (select max(START_TIME) from gv$session_longops gsl where gv$session.SID = gsl.sid and gv$session.INST_ID = gsl.INST_ID and gv$session.SERIAL# = gsl.SERIAL# and gv$session.sql_address = gsl.SQL_ADDRESS and gv$session.sql_hash_value = gsl.SQL_HASH_VALUE))
and gv$session.sid = ses_optimizer_env38.sid(+)
and gv$session.INST_ID = ses_optimizer_env38.INST_ID(+)
and nvl(ses_optimizer_env38.id,38) = 38 --optimizer_mode
and gv$session.sid = ses_optimizer_env48.sid(+)
and gv$session.INST_ID = ses_optimizer_env48.INST_ID(+)
and nvl(ses_optimizer_env48.id, 48) = 48--cursor_sharing
and gv$session.LOCKWAIT = lockwait.KADDR(+)
and lockwait.id1 = lockhold.id1(+)
and lockwait.id2 = lockhold.id2(+)
and nvl(lockwait.REQUEST,1) > 0
and nvl(lockwait.LMODE,0) = 0
and nvl(lockhold.REQUEST,0) = 0
and nvl(lockhold.LMODE,1) > 0
and nvl(lockwait.SID,0) <> nvl(lockhold.SID,1)
order by tx_stime, status, LOGON_TIME, username, gv$session.sid;
=================
SET LINES 300 pagesize 300
col kill for a15
col UNAM2 format a20 word heading "Oracle User/OS User"
col PROG format a15 word heading "Program|Terminal"
col SID format a10 word heading "SID/|Serial#"
col STAT format A7 Heading "Status"
col LTIM format a20 word heading "Logon Time/|Last Activy time"
col TIMI format a20 word heading "Start time/|Last Update time|remaining->elapsed|[%done end time]"
col STMT format a64 heading "Statement" WORD
col OPNA format a10 WORD PRINT HEADING "Long Op"
col WORK format a12 WORD PRINT HEADING "Target|So far|Total Work"
col MESS format a50 WORD PRINT HEADING "Long op message" wrap
--NEWLINE
SELECT
''''||ses.sid ||','|| ses.serial#||',@'||ses.inst_id ||'''' kill
,ses.sql_id
,lo.opname opna
,NVL(ses.username,lo.username)||' / '||ses.osuser||DECODE(ses.audsid,userenv('SESSIONID'),'**','') unam2
,NVL(ses.status,'unknown') stat
-- ,TO_CHAR(ses.logon_time,'DD-MON-YYYY:HH24:MI:SS')||' '
-- ||to_char(sysdate - (ses.last_call_et)/86400,'DD-MON-YYYY:hh24:mi:ss') ltim
,NVL(LPAD(TO_CHAR(target),11,'_'),'___________')||','
||LPAD(TO_CHAR(sofar),11,'_')||','
||LPAD(TO_CHAR(totalwork),11,'_')||' ('||units||') ['
||DECODE(0,totalwork,'n/a',CEIL(100*sofar/(totalwork)))||'%]'
work
,start_time||' '
||last_update_time
||' '||TO_CHAR(time_remaining)||''
||'>;'||elapsed_seconds||' ['||
DECODE(0,time_remaining+elapsed_seconds,'n/a',CEIL(100*elapsed_seconds/(time_remaining+elapsed_seconds)))||'% eta '
||TO_CHAR(sysdate+(time_remaining/(60*60*24)),'HH24:MIpm')||']' timi
,DECODE(ses.program,'','',ses.program||' '||ses.terminal) prog
-- ||'('||RTRIM(ses.machine)||')'
-- ,lo.sid||'/'||lo.serial#||' ['||ses.audsid||']' sid
,message mess
,REPLACE(s.sql_text,CHR(10),' ') stmt
--,context
FROM gv$sql s ,gv$session_longops lo ,gv$session ses
WHERE lo.sql_address=s.address
and lo.inst_id=s.inst_id
AND lo.sql_hash_value=s.hash_value
AND lo.time_remaining!=0
AND lo.sid=ses.sid(+)
AND lo.serial#=ses.serial#(+)
/
set linesize 200 pages 200
col sid format 99999
col name format a36
col p1 format 999999999 Head 'P1'
col program format a25
col p2 format 999999999 Head 'P2'
col p3 format 999999999 Head 'P3'
col pgm format a15 head 'What'
col state format a15
col wt format 9999999 head 'Wait|Time'
col WaitEvent format a38 head 'Wait Event'
col lc format 99999999999.99 head 'last call'
col kill for a16
select
--A.sid,
''''||b.sid ||','|| b.serial#||',@'||b.inst_id ||'''' kill,
decode(A.event,'null event','CPU Exec',A.event) WaitEvent,A.p1,A.p2,A.p3,
decode(A.state,'WAITING','WTG',
'WAITED UNKNOWN TIME','UNK',
'WAITED SHORT TIME','WST',
'WAITED KNOWN TIME','WKT') wait_type,
decode(A.state,'WAITING',A.seconds_in_wait,
'WAITED UNKNOWN TIME',-999,
'WAITED SHORT TIME',A.wait_time,
'WAITED KNOWN TIME',A.WAIT_TIME) wt,
round((last_call_et/60),2) lc,
substr(nvl(b.module,b.program),1,15) pgm,b.sql_id,PREV_SQL_ID
from gv$session_wait A,gv$session B
where 1=1
and (A.event like 'gc%'
or A.event like 'GC%'
or A.event like 'ge%')
and A.event not like '%remote message'
and A.event not like '%sleep'
and A.sid=B.sid
and B.status='ACTIVE'
and a.inst_id=b.inst_id
order by 1
/
define interval_mins=30
set lines 200
col samplestart format a30 head 'Begin Time'
col sampleend format a30 head 'End Time'
col aas format 999.99 head 'AAS'
col aas_event format 999.99 head 'AAS per |Event'
col dbt format 999999 head 'DB Time'
col event format a30
col time_waited format 999999 head 'Time (ms)'
col wait_pct format 999.99 head '% of DB Time'
COLUMN bt NEW_VALUE _bt NOPRINT
COLUMN et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
sysdate et
from v$active_session_history;
with xtimes (xdate) as
(select to_date('&_bt') xdate
from dual
union all
select xdate+(&&interval_mins/1440)
from xtimes
where xdate+(&&interval_mins/1440) < sysdate)
select to_char(s1.xdate,'DD-MON-RR HH24:MI:SS') samplestart,
to_char(s1.xdate+(&&interval_mins/1440),'DD-MON-RR HH24:MI:SS') sampleend,
s2.event,.001*((sum(s2.time_waited))) time_waited,
(count(s2.sample_id)/(60*&&interval_mins)) aas_event,
(count(s3.sample_id)) dbt,
round(100*(sum(s2.time_waited)/1000000/count(s3.sample_id)),2) as wait_pct
from xtimes s1,
v$active_session_history s2,
v$active_session_history s3
where s2.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
and s3.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
and s2.sample_id=s3.sample_id
and (s2.event like 'gc%'
or s2.event like 'GC%'
or s2.event like 'ge%')
and s2.event not like '%remote message'
and s2.event not like '%sleep'
group by s1.xdate,s2.event
order by s1.xdate
/
undefine interval_mins
Measuring RAC Waits from DBA_HIST_ACTIVE_SESS_HISTORY
define interval_mins=30
set lines 200
col samplestart format a30 head 'Begin Time'
col sampleend format a30 head 'End Time'
col aas format 999.99 head 'AAS'
col aas_event format 999.99 head 'AAS per |Event'
col event format a30
col time_waited format 999999 head 'Time (ms)'
col wait_pct format 999.99 head 'Wait %'
COLUMN bt NEW_VALUE _bt NOPRINT
COLUMN et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
sysdate et
from v$active_session_history;
with xtimes (xdate) as
(select to_date('&_bt') xdate
from dual
union all
select xdate+(&&interval_mins/1440)
from xtimes
where xdate+(&&interval_mins/1440) < sysdate)
select to_char(s1.xdate,'DD-MON-RR HH24:MI:SS') samplestart,
to_char(s1.xdate+(&&interval_mins/1440),'DD-MON-RR HH24:MI:SS') sampleend,
s2.event,
.001*((sum(s2.time_waited))) time_waited,
10*(count(s2.sample_id)/(60*&&interval_mins)) aas_event,
10*(count(s3.sample_id)) dbt,
round(100*(sum(s2.time_waited)/1000000/(10*(count(s3.sample_id)))),2) as wait_pct
from xtimes s1,
dba_hist_active_sess_history s2,
dba_hist_active_sess_history s3
where s2.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
and s3.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
and s2.sample_id=s3.sample_id
and (s2.event like 'gc%'
or s2.event like 'GC%'
or s2.event like 'ge%')
and s2.event not like '%remote message'
and s2.event not like '%sleep'
group by s1.xdate,s2.event
order by s1.xdate
/
Oracle Trace File Info .....
Oracle Trace File path ....
Trace file info ..select value || '/' ||
(select lower(instance_name) from v$instance) ||
'_ora_' ||
(
select spid
from v$process
where addr = (
select paddr
from v$session
where sid = (
select sid
from v$mystat
where rownum = 1
)
)
) ||
'.trc'
from
v$parameter
where
name = 'user_dump_dest'
;
/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_17513.trc
1 row selected.
SET ECHO OFF FEEDBACK 6 HEADING ON LINESIZE 200 PAGESIZE 0 TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL ON VERIFY OFF
SELECT a.trace_path || ' > ' || b.trace_file "Trace File Path"
FROM
( SELECT value trace_path FROM v$parameter WHERE name='user_dump_dest' ) a
, ( SELECT c.instance || '_ora_' || spid ||'.trc' TRACE_FILE FROM v$process,
(select lower(instance_name) instance from v$instance) c
WHERE addr = ( SELECT paddr FROM v$session WHERE (audsid, sid) = ( SELECT sys_context('USERENV', 'SESSIONID') , sys_context('USERENV', 'SID') FROM dual))) b
/
==============================================================
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Database altered.
Oracle 11gr2 +
to check trace file name ...
SET ECHO OFF FEEDBACK 6 HEADING ON LINESIZE 200 PAGESIZE 0 TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL ON VERIFY OFF
SELECT a.trace_path || ' > ' || b.trace_file "Trace File Path",
'! cat '||a.trace_path || '/' || b.trace_file "Trace File Path"
FROM
( SELECT VALUE trace_path FROM v$diag_info WHERE name='Diag Trace' ) a
, ( SELECT c.instance || '_ora_' || spid ||'.trc' TRACE_FILE FROM v$process,
(select instance_name instance from v$instance) c
WHERE addr = ( SELECT paddr FROM v$session WHERE (audsid, sid) = ( SELECT sys_context('USERENV', 'SESSIONID') , sys_context('USERENV', 'SID') FROM dual))) b
/
set linesize 200 pagesize 200
col name for a20
col value for a70
select * from v$diag_info;
Subscribe to:
Posts (Atom)
