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;
