oracle查看语言集,1-ORACLE性能查看语句集合

--查看数据库中有多少表被锁住

select b.owner table_owner, b.object_name, http://www.doczj.com/doc/9315197abfd5b9f3f90f76c66137ee06eff94edb.htmlername, c.sid, c.serial#

from v$locked_object a, dba_objects b, v$session c

where a.object_id = b.object_id

and c.sid = a.session_id;

--删除session(commond窗口)

alter system kill session 'sid,serial#';

--查看SID

select SID,LOGON_TIME from v$session where username='MAXIMO' ORDER by logon_time asc

SELECT * from v$session where sid='946'

--根据SId查找等待执行的SQL

select b.spid,a.sid,http://www.doczj.com/doc/9315197abfd5b9f3f90f76c66137ee06eff94edb.htmlername,c.hash_value,c.SQL_ID,c.sql_text

from v$session a,v$process b,v$open_cursor c

where a.sid=c.sid

and b.ADDR=a.PADDR

and a.SID='946'

and a.SERIAL#='42760'

select username,logon_time,seconds_in_wait from v$session where username='MAXIMO' and logon_time between to_date('2013-5-6','yyyy-MM-dd') and to_date('2013-5-7','yyyy-MM-dd') order by logon_time asc;

--当客户端建立一个连接，数据库创建进程，查看一个连接占用内存大小

select sum(pga_used_mem)/1024 total_used, sum(pga_used_mem)/count(1)/1024 used_avg, sum(pga_alloc_mem)/1024 total_alloc, sum(pga_alloc_mem)/count(1)/1024 alloc_avg from v$process;

--批量KILL session

command : SELECT CONCAT('ALTER SYSTEM KILL SESSION ''',CONCAT(CONCAT(CONCAT(SID,','),SERIAL#),''' immediate;')) FROM V$SESSION WHERE USERNAME='MAXIMO' AND LOGON_TIME < to_date('2013-9-10','yyyy-mm-dd')

--查看数据库占用的内存

select 'SGA' AS NAME,ROUND(sum(value)/1024/1024,2)||'M' AS "SIZE(M)" from v$sga

UNION

select 'PGA' AS NAME,ROUND(value/1024/1024,2)||'M' AS "SIZE(M)" from v$pgastat where name='total PGA allocated'

UNION

select 'TOTAL' AS NAME,((SELECT ROUND(sum(value)/1024/1024,2) from v$sga)+(select ROUND(value/1024/1024,2) from v$pgastat where name='total PGA allocated'))||'M' AS "SIZE(M)" FROM DUAL

UNION

SELECT NAME,TO_CHAR(VALUE) FROM V$PGASTAT WHERE NAME='process count';

--查看共享SQL区的使用率,这个使用率应该在90％以上，否则需要增加共享池的大小。

select (sum(gets-getmisses-usage-fixed))/sum(gets) "Data dictionary cache" from v$rowcache;

--查看数据字典缓冲区的使用率,动态性能表 --这个使用率也应该在90％以上，否则需要增加共享池的大小。

select(sum(pins-reloads))/sum(pins) "Library cache" from v$librarycache;

--查看数据库数据缓冲区的使用情况：

SELECT name,value FROM v$sysstat WHERE name in ('db block gets','consistent gets','physical reads');

--查看日志缓冲区的使用情况

SELECT name, value FROM v$sysstat WHERE name IN ('redo entries','redo log space requests');