mystat :
========
select distinct * from v$sysstat order by class
select * from v$mystat
All Alerts :
=============
>dba_outstanding_alerts : DBA_OUTSTANDING_ALERTS describes alerts which the server considers to be
outstanding.
>dba_alert_history : DBA_ALERT_HISTORY describes a time-limited history of alerts which are no longer
outstanding.
>v$alert_types : V$ALERT_TYPES displays information about server alert types.
>user_advisor_findings
dba_advisor_objects
dba_advisor_actions
dba_advisor_rationale
----------------------------------------------------------------------
select username,sid,serial#,logon_time,status from v$session where sid=375
select * from dba_outstanding_alerts order by time_suggested desc
select * from dba_alert_history order by time_suggested desc
select * from user_advisor_findings
select * from dba_advisor_objects where owner='ABABIL'
select * from dba_advisor_actions where owner='ABABIL'
select * from dba_advisor_rationale where owner='ABABIL'
-------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
|session_wait shows what is each session waiting at at the instance you issue select * from v$session_wait
|
|session_event would be the sum of session_wait in each session and each event
|
|system_event would be the sum of all waits in session_event
|
|normally I use session_wait to check for enqueue or those transactions that is taking long time (and see what they are doing/waiting)
|
|I query session_event when I am tracing an user and see how many waits, wait time etc his session has been facing
|------------------------------------------------------------------------------------------------------------
select event,count(*) from v$session_wait group by event
select event,count(*) from v$session_event group by event
select sid,p1,p2,p3 from v$session_wait where event='latch: cache buffers chains'
select * from v$latchname where latch#=122
select 'kill -9 '||spid from v$process where addr in(
select paddr--sid,username,program,sql_address,logon_time,status,machine
from v$session where event='latch: cache buffers chains' and program like '%java%'
and username is not null and logon_time
select sql_address,count(*) from v$session where event='latch: cache buffers chains' group by sql_address
select sql_fulltext,address,sql_id from v$sqlarea where sql_id ='1grbab9syxsx9'
---------------------------------------------------------------------------------
SELECT /*+ FIRST_rows */ * FROM VW_DAILY_VOUCHER
WHERE GLOBALTXNNO IN (
SELECT DISTINCT GLOBALTXNNO FROM VW_DAILY_VOUCHER WHERE TXNDATE = : P_TXNDATE AND GLOBALTXNNO <> 0
AND NVL ( BATCHNO , '%' ) LIKE NVL ( : P_BATCHNO , '%' ) AND NVL ( GLCODE , '%' ) LIKE NVL ( : P_GLCODE , '%' )
AND NVL ( TO_CHAR ( GLOBALTXNNO ) , '%' ) LIKE NVL ( : P_GLOBALTXNNO , '%' )
AND NVL ( ENTRYUSER , '%' ) LIKE NVL ( : P_ENTRYUSER , '%' ) AND NVL ( POSTINGUSER , '%' ) LIKE NVL ( : P_VERIFYUSER , '%' )
AND NVL ( OWNERBRANCHID , '%' ) LIKE NVL ( : P_OWNERBRANCH , '%' )
AND NVL ( INITIATORBRANCHID , '%' ) LIKE NVL ( : P_INITBRANCH , '%' ) )
ORDER BY 3 ASC,19 ASC,20 ASC,16 ASC,17 ASC,1 ASC,2 ASC,18 ASC , to_number ( TRACERNO ) , 17 , 3 , 4
select sum(pga_alloc_mem) from v$process
SELECT vv.TXNDATE, vv.VALUEDATE, vv.GLOBALTXNNO, vv.TRACERN
O, vv.GLCODE, vv.GLTITTLE, vv.PARTICULARS, --vv.GLDT
CLIENTACCNO, vv.GLCURRENCY, --vv.BATCHNO, vv.LCYDEBIT,
vv.LCYCREDIT, vv.OWNERBRANCHID, vv.ENTRYUSER FROM VW_
select * from user_views where view_name like 'VV%'
select sid,username,program,sql_address,logon_time,event from v$session where status='ACTIVE' order by logon_time desc
select sql_text from v$sqlarea where address='070000058D49EE90'
select sql_address,prev_sql_addr from v$session where program like '%toad%'
select * from user_objects order by last_ddl_time desc
select event,count(*) from v$session_wait group by event
--CPU TIme :
===========
SELECT
SST.VALUE CPU, S.USERNAME, S.SID, S.SERIAL#, SQL_TEXT
FROM
V$SESSION S,
V$SQLTEXT_WITH_NEWLINES T,
V$SESSTAT SST
WHERE S.SQL_ID IS NOT NULL
AND S.SQL_ID = T.SQL_ID
AND SST.SID = S.SID
AND SST.STATISTIC# = (SELECT STATISTIC# FROM V$STATNAME SN WHERE SN.NAME = 'CPU used by this session')
ORDER BY 1 DESC, S.SID,T.PIECE;
==CPU TIME 3:
==============
a) ps -eaf -o pcpu,pid,user,tty,comm | grep ora | grep -v \/sh | grep -v ora_ | sort -r | head -20
b) SELECT a.username, b.sql_text
FROM v$session a, v$sqlarea b, v$process c
WHERE (c.spid = '&PID' OR a.process = '&PID')
AND a.paddr = c.addr
AND a.sql_address = b.address
==CPU TIME 3:
===============
select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
order by cpu_time desc
--Longes Time Query :
========================
select sql_text,elapsed_time,sql_id,hash_value,plan_hash_value,program_id,module,action
from v$sql where module like 'TOAD%' order by elapsed_time desc
select sql_text,elapsed_time,sql_id,hash_value,plan_hash_value,program_id,module,action
from v$sql where module like 'frmweb%' order by elapsed_time desc
select sesion.sid,
sesion.serial#,
sesion.username,
sesion.sql_id,
sesion.sql_child_number,
optimizer_mode,
hash_value,
address,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
--Session Query :
===============
SELECT p.spid,
s.process,
s.status,
s.machine,
s.username,
TO_CHAR (s.logon_time, 'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et / 60 TIME_IN_MIN,
s.action,
s.module,
s.program program,
s.sid sid,
s.serial# serial,
s.osuser,
'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
sql_string
FROM V$SESSION s,V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND UPPER(s.OSUSER) = 'ORACLE'
AND (UPPER(s.PROGRAM) LIKE 'FRMWEB%' OR UPPER(s.PROGRAM) LIKE 'JAVA%'
)
AND s.status = 'INACTIVE'
--and (Last_Call_ET/60)>(select TO_NUMBER(APCVALUE) from APPCONFIGURATIONwhere TRIM(UPPER(APCITEM))='APP_IDLE_TIME_IN_MINUTES')
ORDER BY p.spid
SELECT p.spid,
s.process,
s.status,
s.machine,
s.username,
TO_CHAR (s.logon_time, 'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et / 60 TIME_IN_MIN,
s.action,
s.module,
s.program program,
s.sid sid,
s.serial# serial,
s.osuser,
'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
sql_string
FROM V$SESSION s,V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND UPPER(s.OSUSER) = 'ORACLE'
AND (UPPER(s.PROGRAM) LIKE 'FRMWEB%' OR UPPER(s.PROGRAM) LIKE 'JAVA%'
)
AND s.status = 'INACTIVE'
--and (Last_Call_ET/60)>(select TO_NUMBER(APCVALUE) from APPCONFIGURATIONwhere TRIM(UPPER(APCITEM))='APP_IDLE_TIME_IN_MINUTES')
ORDER BY p.spid
Oracle with OS user :
=====================
select s.username "Oracle User",s.osuser "OS User",i.consistent_gets "Consistent Gets",
i.physical_reads "Physical Reads",s.status "Status",s.sid "SID",s.serial# "Serial#",
s.machine "Machine",s.program "Program",to_char(logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time",
w.seconds_in_wait "Idle Time", P.SPID "PROC",
name "Stat CPU", value
from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and 'SQL*Net message from client' = w.event(+)
and s.osuser is not null
and s.username is not null
and s.paddr=p.addr
and n.statistic# = t.statistic#
and n.name like '%cpu%'
and t.SID = s.sid
and status='ACTIVE'
order by 6 asc, 3 desc, 4 desc
Lock Object :
===============
--Find Database Lock:
====================
select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
--Details :
===============
SELECT
DISTINCT BLOCKER,
SID_SERIAL,
COUNT(*)
FROM
(
select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' || (select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' || (select serial# from v$session where sid=b.sid) sid_serial_V
from
v$lock a,
v$lock b
where
a.block = 1
and
b.request > 0
and
a.id1 = b.id1
and
a.id2 = b.id2
)
GROUP BY BLOCKER, SID_SERIAL
ORDER BY COUNT(*) DESC
select sql_hash_value from v$session where sid = &sid
select sql_Text from v$sql where hash_value = &sql_hash_value
SELECT * FROM V$OPEN_CURSOR WHERE SID = 767
--How to Get Detail of Locks with Object Locked:
==============================================
SELECT VLO.OS_USER_NAME "OS USERNAME", VLO.ORACLE_USERNAME "DB USER",
VP.SPID "SPID", AO.OWNER "OWNER", AO.OBJECT_NAME "OBJECT LOCKED",AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, 'NO LOCK',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCL',
6, 'EXCLUSIVE',
NULL
) "MODE OF LOCK",
VS.STATUS "CURRENT STATUS"
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> 'KILLED'
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;
--Find Last SQL query :
========================
select S.USERNAME||'('||s.sid||')-'||s.osuser UNAM
-- ,s.program||'-'||s.terminal||'('||s.machine||')' PROG
,s.sid||'/'||s.serial# sid
,s.status "Status",p.spid
,sql_text sqltext
from v$sqltext_with_newlines t,V$SESSION s , v$process p
where t.address =s.sql_address
and p.addr=s.paddr(+)
and t.hash_value = s.sql_hash_value
order by s.sid,t.piece
/
--cpu load :
==========
select * from v$osstat
select * from v$sysmetric_history
--io/cpu usage query :
========================
select
sid,
username, terminal,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username, b.terminal terminal,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by
b.sid, nvl(b.username,p.name) , b.terminal),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by
4 desc
select * from v$filestat
--Find SPID:
=============
select a.username,a.sid,b.spid from v$session a, v$process b where b.spid = '1638634'
select a.username,a.sid,b.spid from v$session a, v$process b where a.serial# = b.serial#
select s.username,p.spid from v$session s, v$process p
where s.paddr=p.addr AND p.background is null;
Tablespace Free Space :
=======================
SELECT a.tablespace_name "TableSpace",
ROUND(SUM(a.Ebytes),3) "Ext. Space(GB)",
ROUND(SUM(a.bytes),3) "Alo. Space(GB)",
(ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3)) "Used Space(GB)",
ROUND(SUM(a.Ebytes),3) - ((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "Free Space(GB)",
ROUND(SUM(a.bytes),3)-((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "ABs Free Space(GB)"
FROM (SELECT tablespace_name tablespace_name,SUM(DECODE(maxbytes,0,bytes,maxbytes)/1024/1024/1024) Ebytes,
SUM((bytes)/1024/1024/1024) bytes
FROM DBA_DATA_FILES
GROUP BY tablespace_name ) a,
(SELECT tablespace_name tablespace_name, SUM(bytes/1024/1024/1024) bytes
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) c
WHERE a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name
UNION
SELECT 'XXXTOTAL:',
ROUND(SUM(a.Ebytes),3) "Ext. Space(GB)",
ROUND(SUM(a.bytes),3) "Alo. Space(GB)",
(ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3)) "Used Space(GB)",
ROUND(SUM(a.Ebytes),3) - ((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "Free Space(GB)",
ROUND(SUM(a.bytes),3)-((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "ABs Free Space(GB)"
FROM (SELECT tablespace_name tablespace_name,SUM(DECODE(maxbytes,0,bytes,maxbytes)/1024/1024/1024) Ebytes,
SUM((bytes)/1024/1024/1024) bytes
FROM DBA_DATA_FILES
GROUP BY tablespace_name ) a,
(SELECT tablespace_name tablespace_name, SUM(bytes/1024/1024/1024) bytes
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) c
WHERE a.tablespace_name = c.tablespace_name
-----To check imp0rt performence
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_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
logical/physical read :
=======================
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('session logical reads','physical reads',
'physical reads direct','physical reads direct (lob)');
To check import performence :
==============================
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_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
To find Longops :
=================
select * from v$session_longops where time_remaining>0 order by time_remaining desc
select username,sid,serial#,logon_time,status,machine,sql_id,prev_sql_id from v$session
select * from v$sqlarea where sql_id ='8wk7fbjqgf1ap'
FRA space :
============
SELECT space_limit/1024/1024/1024 "TOTAL_SPACE_GB",space_used/1024/1024/1024 "SPACE_USED_GB" FROM V$RECOVERY_FILE_DEST;
select * from v$recovery_file_dest
select * from v$flash_recovery_area_usage
Archive History :
=================
select to_char(first_time,'DD/MON') day,
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'000')"01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'000')"02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'000')"03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'000')"04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'000')"05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'000')"06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'000')"07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'000')"08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'000')"09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'000')"10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'000')"11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'000')"12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'000')"13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'000')"14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'000')"15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'000')"16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'000')"17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'000')"18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'000')"19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'000')"20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'000')"21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'000')"22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'000')"23",
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'000') "00",
count(*) Total
from v$log_history
WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) - 7
group by to_char(first_time,'DD/MON') order by 1 desc
Logminer :
==========
select * from V$LOGMNR_CONTENTS
Analyze Table :
===============
select * from table(dbms_xplan.display_cursor('4u1s5ns3nuwbz',0))
From Banaful :
==============
select * from v$session_wait
select sid,p1,p2,p3,p2text from v$session_wait where event like 'SQL*Net%'
select segment_name from dba_extents where file_id=1 and (:p2 value) between block_id and block_id+1
select count(*) from obj
select count(*) from sys.obj$
PGA MEMORY USED :
=================
The following query can be used to find the total amount of memory allocated to the PGA areas of all sessions:
SQL> select sum(value)/1024/1024 Mb
from
v$sesstat s, v$statname n
where
n.STATISTIC# = s.STATISTIC# and
name = 'session pga memory';
Which process is requesting too much memory? :
===============================================
Some operations will require lots of process memory like huge PL/SQL tables or big sort operations. In these cases,
the processes will run for a certain period of time before getting the ora-4030 error, hopefully allowing us to
find out where and why the memory is allocated. You can use the following query to find out oracle's idea of
PGA and UGA size for the oracle processes.
select
sid,name,value
from
v$statname n,v$sesstat s
where
n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%'
order by 3 asc; This query will show the most hungry process last in the list.
select sql_text from v$sqlarea a, v$session s where a.address = s.sql_address and s.sid = 1024 ;
Resource Plan :
================
alter system set RESOURCE_MANAGER_PLAN='system_plan'
To check : select * from V$RSRC_PLAN
To set null : alter system set RESOURCE_MANAGER_PLAN=''
OS SPID with SQL_TEXT :
========================
SELECT a.username,
a.machine,
a.program,
a.sid,
a.serial#,
a.status,
c.piece,
c.sql_text
FROM v$session a,
v$process b,
v$sqltext c
WHERE b.spid in (1323346)
AND b.addr = a.paddr
AND a.sql_address = c.address (+)
ORDER BY a.sid, c.piece;
[oracle@apps1 ~]$ rwdiag.sh -findall
Broadcast mechanism used to locate servers
------------------------------------------
Channel address = 228.5.6.7
Channel port = 14021
(1) Name = rep_svr4 : Type = server : Host = apps1.aibl.com
(2) Name = rep_svr3 : Type = server : Host = apps1.aibl.com
(3) Name = rep_svr2 : Type = server : Host = apps1.aibl.com
(4) Name = rep_svr1 : Type = server : Host = apps1.aibl.com
(5) Name = rep_apps1_as_middle_home : Type = server : Host = apps1.aibl.com
[oracle@apps1 ~]$
opmnctl startproc ias-component=LogLoader
