Oracle（22）—— Oracle性能分析

--1查询数据库允许的最大进程数：
select name,value from v$parameter where name = 'processes';

--2查询数据库允许的最大会话数：
select name,value from v$parameter where name = 'sessions';

--3查询数据库当前进程数：
select count(*) from v$process;

--4查看数据库的并发连接数：
select Count(*) from v$session where status='ACTIVE' And username='UPSPRDMS';

--5查询被锁定的表
SELECT object_name, machine, s.sid, s.serial#
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id　= o.object_id
AND l.session_id = s.sid
And s.username = 'UPSPRDMS';

--6查询sql阻塞信息：
SELECT  *  FROM v$session WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1) And username='UPSPRDMS';
 
--7查看数据库的并发连接数信息：
select * from v$session where status='ACTIVE' And username='UPSPRDMS' And ROWNUM<50;

其中 username 代表对应的数据库

--8监控事例的等待
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4 ;

--9性能最差的SQL
SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
                FROM v$sqlarea
                ORDER BY disk_reads DESC)
WHERE ROWNUM<20;