Just thought to share few queries from my repository. It may be useful for all new DBAs. Of course these are quite old ones and you can do most of them through Enterprise Manager, still we DBAs always like command line :-)

Show sessions that are blocking each other :

select	'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking
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
/

Show locked objects :

set lines 100 pages 999
col username 	format a20
col sess_id 	format a10
col object	format a25
col mode_held	format a10
select	oracle_username || ' (' || s.osuser || ')' username
,	s.sid || ',' || s.serial# sess_id
,	owner || '.' ||	object_name object
,	object_type
,	decode(	l.block
	,	0, 'Not Blocking'
	,	1, 'Blocking'
	,	2, 'Global') status
,	decode(v.locked_mode
	,	0, 'None'
	,	1, 'Null'
	,	2, 'Row-S (SS)'
	,	3, 'Row-X (SX)'
	,	4, 'Share'
	,	5, 'S/Row-X (SSX)'
	,	6, 'Exclusive', TO_CHAR(lmode)) mode_held
from	v$locked_object v
,	dba_objects d
,	v$lock l
,	v$session s
where 	v.object_id = d.object_id
and 	v.object_id = l.id1
and 	v.session_id = s.sid
order by oracle_username
,	session_id
/

Show which row is locked :

select	do.object_name
,	row_wait_obj#
,	row_wait_file#
,	row_wait_block#
,	row_wait_row#
,	dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
				ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from	v$session s
,	dba_objects do
where	sid=&sid
and 	s.ROW_WAIT_OBJ# = do.OBJECT_ID
/

Then select the row with that rowid...

select * from <table> where rowid=<rowid>;

List locks :

column lock_type format a12
column mode_held format a10
column mode_requested format a10
column blocking_others format a20
column username format a10
SELECT	session_id
,	lock_type
,	mode_held
,	mode_requested
,	blocking_others
,	lock_id1
FROM	dba_lock l
WHERE 	lock_type NOT IN ('Media Recovery', 'Redo Thread')
/

Show all ddl locks in the system :

select	ses.username
,	ddl.session_id
,	ses.serial#
,	owner || '.' || ddl.name object
,	ddl.type
,	ddl.mode_held
from	dba_ddl_locks ddl
,	v$session ses
where	owner like '%userid%'
and	ddl.session_id = ses.sid
/

Generate kill statement for ddl locking sessions :

select    'alter system kill session ''' || ddl.session_id || ',' || ses.serial# || ''' immediate;'
from    dba_ddl_locks ddl
,    v$session ses
where    owner like '%userid%'
and    ddl.session_id = ses.sid
/

Show currently exectuing sql :

select sql_text
from v$sqlarea
where users_executing > 0
/

Session statistics :

select    sn.name
,    st.value
from    v$sesstat st
,    v$statname sn
where    st.STATISTIC# = sn.STATISTIC#
and    st.VALUE > 0
and    st.SID = &SID
order     by value desc
/

Resource intensive sql :

change 8192 to match block size

select sql_text
,      executions
,      to_char((((disk_reads+buffer_gets)/executions) * 8192)/1048576, '9,999,999,990.00')
 as total_gets_per_exec_mb
,      to_char((( disk_reads             /executions) * 8192)/1048576, '9,999,999,990.00')
 as disk_reads_per_exec_mb
,      to_char((( buffer_gets            /executions) * 8192)/1048576, '9,999,999,990.00')
 as buffer_gets_per_exec_mb
,      parsing_user_id
from   v$sqlarea
where  executions > 10
order by 6 desc
/

File io stats :

Requires timed_statistics=true

set lines 80 pages 999
col fname heading "File Name" format a60
col sizemb heading "Size(Mb)" format 99,999
col phyrds heading "Reads" format 999,999,999
col readtim heading "Time" format 99.999
col phywrts heading "Writes" format 9,999,999
col writetim heading "Time" format 99.999
select     lower(name) fname
,          (bytes / 1048576) sizemb
,          phyrds
,    readtim
,          phywrts
,    writetim
from       v$datafile df
,          v$filestat fs
where      df.file# = fs.file#
order      by 1
/

In session tracing :

To switch it on:

exec dbms_system.set_sql_trace_in_session (<sid>, <serial#>, true);

To switch it off:

exec dbms_system.set_sql_trace_in_session (<sid>, <serial#>, false);

switch on event 10046 :

To switch it on:

alter session set events '10046 trace name context forever, level 8'; 

To switch it off:

alter session set events '10046 trace name context off';
Note. use tkprof to interpret the results.

Rows per block :

select    avg(row_count) avg
, max(row_count) max
, min(row_count) min
from      (
 select  count(*) row_count
 from    &table_name
 group   by substr(rowid, 1, 15)
 )
/

Show the buffer cache advisory :

Note. The current setting is halfway down and has a read factor of one.

set lines 100 pages 999
col est_mb format 99,999
col estd_physical_reads format 999,999,999,999,999
select    size_for_estimate est_mb
,    estd_physical_read_factor
,    estd_physical_reads
from    v$db_cache_advice
where    name = 'DEFAULT'
order by size_for_estimate
/

db_cache_advice needs to be on for the above to work

alter system set db_cache_advice=on;
PrintFriendly

Viewers of this post also viewed...