DBA 3

3.其他方面 
①根据实例来查看进程id。

SQL code

select spid  from v$process where addr in (select paddr from v$session where sid = $sid)

②根据进程id来查看实例。

SQL code

select sid  from v$session where paddr in (select addr from v$process where spid = $pid)

③查看当前在session中的sql文。

SQL code

select SQL_TEXT  from V$SQLTEXT where HASH_VALUE = (select SQL_HASH_VALUE from v$session where sid = &sid) order by PIECE

④查看v$session_wait。

SQL code

select *  from v$session_wait where event not like 'rdbms%'   and event not like 'SQL*N%'   and event not like '%timer';

⑤Dictionary缓存的命中率。

SQL code

/*It should be about 15%, otherwise add share_pool_size*/SELECT sum(getmisses) / sum(gets) FROM v$rowcache;

⑥利用文件号和数据块来查看DB中的各个对象。

SQL code

select owner,segment_name,segment_type    from dba_extents where file_id = [$fno and &dno between block_id and block_id + blocks - 1 ]

⑦寻找hot block。

SQL code

select /*+ ordered */        e.owner || '.' || e.segment_name segment_name,       e.extent_id extent#,       x.dbablk - e.block_id + 1 block#,       x.tch,       l.child#  from sys.v$latch_children l, sys.x$bh x, sys.dba_extents e where l.name = 'cache buffers chains'   and l.sleeps > &sleep_count   and x.hladdr = l.addr   and e.file_id = x.file#   and x.dbablk between e.block_id and e.block_id + e.blocks - 1;

⑧找出每个文件上的等待事件。

SQL code

select df.name, kf.count  from v$datafile df, x$kcbfwait kf where (kf.indx + 1) = df.file#;

⑨找出引起等待事件的SQL语句。

SQL code

select sql_text, c.event  from v$sqlarea a, v$session b, v$session_wait c where a.address = b.sql_address   and b.sid = c.sid;

⑩判断你是从pfile启动还是spfile启动。

SQL code

SQL> select decode(count(*), 1, 'spfile', 'pfile' ) as DECODE  2  from v$spparameter  3  where rownum=1  4  and isspecified='TRUE';DECODE------spfileSQL>