如何查询Oracle性能监控

如果程序中使用了临时的LOB类型变量,Oracle会分配临时空间,如果并发很高,初始区很大,那么数据库可能产生严重的TEMP表空间的不足问题.
可以通过测试轻易再现这种状况,在多个Session中执行如下代码:
declare
  A CLOB;
BEGIN
  A:=‘ABC‘;
  DBMS_LOCK.SLEEP(120);
END;

查询v$sort_usage视图,可以获得临时表空间的使用情况（哪个用户、哪个Session因为什么原因使用了多少临时表空间）:
select s.username, s.sid, u.tablespace, u.contents, u.segtype,
round(u.blocks*8192/1024/1024,2) MB
from v$session s, v$sort_usage u
where s.saddr = u.session_addr
and u.contents = ‘TEMPORARY‘
order by MB DESC ;

SEGTYPE=SORT的是因为排序而用到临时表空间的。
SEGTYPE=LOB_DATA是因为使用了临时的LOB类型变量而用到临时表空间的。

参考：
http://www.eygle.com/archives/2006/03/lob_and_temporary_tablespace.html

实验：
SQL> select max(sid) from v$mystat;
 
  MAX(SID)
----------
        45
 
SQL> DECLARE
  2  a clob;
  3  BEGIN
  4  dbms_lob.createtemporary(a, TRUE,dbms_lob.call);
  5  dbms_lob.freetemporary(a);
  6  END;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL> select count(*) from dual;
 
  COUNT(*)
----------
         1
 
SQL> SELECT se.inst_id,
  2         se.username username,
  3         se.SID sid,
  4         se.status status,
  5         se.sql_hash_value,
  6         se.prev_hash_value,
  7         su.TABLESPACE tablespace,
  8         su.segtype,
  9         su.CONTENTS CONTENTS,
 10         round(su.blocks * 8192 / 1024 / 1024, 2) MB
 11  FROM gv$session se,
 12       gv$sort_usage su
 13  WHERE se.saddr=su.session_addr
 14  AND   se.inst_id=su.inst_id
 15  ORDER BY MB;
 
INST_ID USERNAME SID STATUS SQL_HASH_VALUE PREV_HASH_VALUE TABLESPACE SEGTYPE  CONTENTS  MB
------- -------- --- ------ -------------- --------------- ---------- -------- --------- --
      1 SYS       45 INACTI      317853294       317853294 TEMP       LOB_DATA TEMPORARY  1

此时CLOB占用的TEMP空间不会自动释放，需要等待会话断开，才能释放。但这个空间，在本会话中，还是可以重用的，只是不供其它会话使用。

在10.2.0.3以前，只能让会话退出，以释放这部份空间，在10.2.0.4中当作一个BUG（Bug:5723140）来修复，但默认不激活，需要通过设置60025事件才可以释放这些lob的TEMP空间。
参考：
http://www.dbaroad.me/archives/2009/09/lob_temp.html

LOB类型变量：
数据库中提供了两种字段类型 Blob 和 Clob 用于存储大型字符串或二进制数据（如图片）。 Blob 采用单字节存储，适合保存二进制数据，如图片文件。 Clob 采用多字节存储，适合保存大型文本数据。

临时表空间优化：
（一）、创建用户时要记得为用户创建临时表空间。
（二）、合理设置PGA，减少临时表空间使用的几率。
（三）、要为临时表空间保留足够的硬盘空间。
参考：
http://database.51cto.com/art/200907/132965.htm

查看临时表空间占用率：
select * from v$temp_space_header;

重建临时表空间的方法：
Temporary tablespace是不能直接drop默认的临时表空间的，不过我们可以通过以下方法达到。
查看目前的Temporary Tablespace
SQL> select name from v$tempfile;

NAME
———————————————————————
D:\ORACLE\ORADATA\ORCL\TEMP01.DBF

SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP

创建中转临时表空间
create temporary tablespace TEMP1 TEMPFILE ‘D:\ORACLE\ORADATA\ORCL\temp02.DBF‘ SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
改变缺省临时表空间 为刚刚创建的新临时表空间temp1
alter database default temporary tablespace temp1;
删除原来临时表空间
drop tablespace temp including contents and datafiles;
重新创建临时表空间
create temporary tablespace TEMP TEMPFILE ‘D:\ORACLE\ORADATA\ORCL\temp01.DBF‘ SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
重置缺省临时表空间为新建的temp表空间
alter database default temporary tablespace temp;
删除中转用临时表空间
drop tablespace temp1 including contents and datafiles;

以上的方法只是暂时释放了临时表空间的磁盘占用空间，是治标但不是治本的方法，真正的治本的方法是找出数据库中消耗资源比较大的sql语句，然后对其进行优化处理。
参考：
http://lanmh.javaeye.com/blog/643676

5、监控LibraryCache
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;

SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;

SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;

后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE

查找不能被充分共享利用的SQL语句（查询LibraryCache中执行次数偏低的SQL语句）：
SELECT sql_text FROM v$sqlarea WHERE executions < 5 ORDER BY UPPER(sql_text);

查找SQL执行次数和SQL解释次数（hard parse），对比两个值的差：
SELECT sql_text , parse_calls , executions FROM v$sqlarea ORDER BY parse_calls;

查询v$librarycache视图的Reloads值（reparsing）的值，值应该接近0，否则应该考虑调整shared pool size
invalidations的值也应该接近0
select namespace,gethitratio,pinhitratio,reloads,invalidations from v$librarycache;

重点关注SQL的命中率：
SELECT gethitratio FROM v$librarycache WHERE namespace = ‘SQL AREA‘;
查看指定某条SQL语句的执行情况（执行次数、加载次数等）：
SELECT sql_text , users_executing , executions , loads FROM v$sqlarea where sql_text like ‘select * from hr.tt‘;

*SQL语句运行过程
1).使用hash算法得到sql语句的hash_value值
2).如果hash_value值在内存中，叫做命中执行软解析
3).如果hash_value值不存在，执行硬解析
4).语法解析，查看是否有错误
5).语意解析，查看权限是否符合
6).若有视图，取出视图的定义
7).进行sql语句的自动改写，如将子查询改写为连接
8).选择最优的执行计划
9).变量绑定
10).运行执行计划
11).返回结果给用户
因为软解析是从此11步骤中第9步开始的，因此软解析比硬解析节约大量的系统开销，应该尽量降低硬解析的次数

诊断：
1） 检查v$librarycache中sql area的gethitratio是否超过90%，如果未超过90%，应该检查应用代码，提高应用代码的效率：
Select gethitratio from v$librarycache where namespace=‘SQL AREA‘;
2) v$librarycache中reloads/pins的比率应该小于1％，如果大于1％，应该增加参数shared_pool_size的值:
Select sum(pins) "executions", sum(reloads) "cache misses",sum(reloads)/sum(pins) from v$librarycache;
reloads/pins>1%有两种可能，一种是library cache空间不足，一种是sql中引用的对象不合法。
3)查看某个session的hard parse个数：
select a.sid,a.value from v$sesstat a,v$session b ,v$statname c where a.sid=b.sid and a.statistic#=c.statistic# and a.sid = 137 and c.name=‘parse count (hard)‘;

调优方法：
1）、调整shared_pool_size
SELECT shared_pool_size_for_estimate AS pool_size,estd_lc_size,estd_lc_time_saved FROM v$shared_pool_advice;

Alter System set shared_pool_size=120M;

2）、书写程序是尽量使用变量不要过多的使用常量
实验：
创建表格
SQL>CREATE TABLE  m(x int);
创建存储过程proc1，使用绑定变量
SQL>CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
  FOR i IN 1..10000
  LOOP
Execute immediate
  ‘INSERT INTO m VALUES(:x)‘ USING i;
  END LOOP;
END;
/
创建存储过程proc2，不使用绑定变量
SQL>CREATE OR REPLACE PROCEDURE proc2
AS
BEGIN
  FOR i IN 1..10000
  LOOP
Execute immediate
  ‘INSERT INTO m VALUES(‘||i||‘)‘ ;
  END LOOP;
END;
/
执行proc2和proc1，对比执行效率
SQL>SET TIMING ON
SQL> exec proc2;
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.93
SQL> select count(*) from m;
  COUNT(*)
----------
     10000
Elapsed: 00:00:00.01
SQL> TRUNCATE TABLE m;
Table truncated.
Elapsed: 00:00:01.76
SQL> exec proc1;
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.85
SQL> select count(*) from m;
  COUNT(*)
----------
     10000
Elapsed: 00:00:00.00

3）、修改cursor_sharing参数为similar，让类似的SQL语句不做hard parse：
有时候我们的应用程序没有使用绑定变量，而修改程序可能有点困难，我们可能需要设置CURSOR_SHARING=SIMILAR来强制ORACLE使用绑定变量。
Show parameter cursor
Alter system set cursor_sharing=SIMILAR
参考：
http://blog.csdn.net/biti_rainy/archive/2004/07/12/39466.aspx
http://space.itpub.net/519536/viewspace-562987
http://wiki.oracle.com/page/CURSOR_SHARING
实验：
SQL> show parameter cursor_sharing
 cursor_sharing string EXACT
SQL> select * from test where object_id=1;
no rows selected
SQL> select sql_text,parse_calls from v$sqlarea where sql_text like ‘select * from test%‘;
select * from test where object_id=:"SYS_B_0" 2
select * from test where object_id=1 1
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing=similar; ----second
Session altered.
SQL> select * from test where object_id=1;
no rows selected
SQL> select sql_text,parse_calls from v$sqlarea where sql_text like ‘select * from test%‘;
select * from test where object_id=:"SYS_B_0" 1
SQL> select * from test where object_id=2;
no rows selected
SQL> select sql_text,parse_calls from v$sqlarea where sql_text like ‘select * from test%‘;
select * from test where object_id=:"SYS_B_0" 2

4）、大对象保留
查找没有保存在library cache中的大对象：
Select * from v$db_object_cache where sharable_mem>10000 and type in (‘PACKAGE‘,‘PROCEDURE‘,‘FUNCTION‘,‘PACKAGE BODY‘) and kept=‘NO‘;
将这些对象保存在library cache中：
Execute dbms_shared_pool.keep(‘package_name‘);
对应脚本：dbmspool.sql

参考：
http://database.51cto.com/art/201004/194003.htm

6、找使用CPU多的用户session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
*12是cpu used by this session

再找出使用CPU多的SQL语句：
查找指定SPID正在执行的SQL语句：
SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,
S.osuser osname,P.serial# S_#,P.terminal,P.program  program,
P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80))  SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr
AND S.sql_address = a.address (+)  AND P.spid LIKE ‘%&1%‘;

*在linux环境可以通过ps查看进程信息包括pid,windows中任务管理器的PID与v$process中pid不能一一对应。 windows是多线程服务器,每个进程包含一系列线程。这点于unix等不同，Unix每个Oralce进程独立存在，在Nt上所有线程由Oralce 进程衍生。

指定SID查看正在执行的SQL语句：
SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,
S.osuser osname,P.serial# S_#,P.terminal,P.program  program,
P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80))  SQL
FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr
AND S.sql_address = a.address (+)  AND s.sid = ‘136‘;

7、回滚段的争用情况：
select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollname b where a.usn=b.usn;

对含有回滚段块的缓冲区的争用也会影响到对回滚段的争用。这可以通过查询动态性能表V$WAITSTAT来检测是否存在对回滚段的争用，例如：
SELECT class,count FROM V$WAITSTAT 
WHERE class IN(‘system undo header‘,‘system undo block‘,‘undo header‘,‘undo block‘);
其中参数含义如下：
◆ system undo header：对含有SYSTEM回滚段标题块的缓冲区的等待次数。
◆ system undo block：对含有SYSTEM回滚段非标题块的缓冲区的等待次数。
◆ undo header：对含有非SYSTEM回滚段标题块的缓冲区的等待次数。
◆ undo block：对含有非SYSTEM回滚段非标题块的缓冲区的等待次数。

如果任何等待次数大于总请求数的1%，则应创建更多的回滚段来减少竞争，可以周期性地检查这些统计数字，并将它与总的请求数据的次数作比较。总的请求数据次数可用如下语句求出:
SELECT SUM(value) FROM V$SYSSTAT 
WHERE name IN(‘db block gets‘,‘consistent gets‘);

8.查询 Buffer 命中率

 select 1 - ((physical.value - direct.value - lobs.value) / logical.value)
"Buffer Cache Hit Ratio"
from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
where physical.name = ‘physical reads‘
and direct.name=‘physical reads direct‘
and lobs.name=‘physical reads direct (lob)‘
and logical.name=‘session logical reads‘;

如何查询Oracle性能监控

标签：