Oracle 性能相关的几个 视图 和 参数

 

一.性能视图

         性能视图是Oracle中一些记录数据库性能方面的视图,通过查看这些视图,获得数据库当前或历史上某个时间的性能数据。 它比SQL_TRACEAWR报告获取数据更及时,便捷。

 

Oracle 动态性能视图

http://www.cndba.cn/Dave/article/1569

 

1.1 V$SQL

         V$SQL 视图是一个DBA 使用频率非常高的动态视图,它通常和V$SESSION 一起使用来获得当前会话的一些SQL执行情况。可以通过该视图查看正在执行的SQL语句及这条SQL运行了多长时间或者它正在等待什么样的事件。

 

1.1.1 V$SQL 查看SQL 内容

         为了获取用户连接到数据库中的信息,需要先从V$SESSION视图确定用户的SID号,然后用v$session v$sql查看相关信息。

 

         SQL>select * from v$session;

         从这里确定根据machine列和program列确定SID

 

         根据SID 确定SQL:

    /* Formatted on 2010/9/6 11:08:21 (QP5 v5.115.810.9015) */

SELECT   a.sql_text,

         b.status,

         b.last_call_et,

         b.event

  FROM   v$sql a, v$session b

 WHERE   a.sql_id = b.sql_id AND b.sid = 23

 

         也可以根据进程号来查看。具体参考Blog

                   oracle 实时查询最耗CPU资源的SQL语句

                   http://www.cndba.cn/Dave/article/1227

 

1.1.2 V$SQL 查看SQL执行和等待时间

对于已经执行完毕的会话,可以在V$SQL视图中找到它的执行时间和消耗的CPU时间,这些信息对我们分析一些性能上存在问题的SQL有用处。比如对比SQL 消耗的CPU 和执行时间,就可以大致知道SQL语句执行中是否有长时间的等待事件:

         /* Formatted on 2010/9/6 13:05:05 (QP5 v5.115.810.9015) */

SELECT   sql_text,

         cpu_time / (1000 * 1000) t_cpu,

         TRUNC (elapsed_time / (1000 * 1000)) t_elap,

         (cpu_time / elapsed_time / (1000 * 1000)) * 100 pct

  FROM   v$sql

 WHERE   sql_text LIKE 'insert into sf select%'

 

SQL_TEXT                   T_CPU     T_ELAP        PCT

------------------------------         ----------     ----------   ----------

insert into sf select * from u    .312002        0    .000056249

insert into sf select * from u    .296402        0    .000062524

 

返回如上结果,如果说T_ELAP 时间比较多,而CPU时间比较少,说明这条语句在执行过程中基本处于等待状态。 关于各个等待事件,参考Blog

                  Oracle 常见的33个等待事件

                   http://blog.csdn.net/tianlesoftware/archive/2010/08/12/5807800.aspx

 

1.1.3 共享池中的SQL

         并不是所有的SQL语句都可以从V$SQL中找到,因为ORACLE会动态地更新共享池的信息,将一些过旧的SQL从共享池中删除,以便于新的SQL语句提供共享池的空间。

 

         我们可以手动的清空共享池中的信息,SQL语句如下:

                   SQL>alter system flush shared_pool;

 

我们知道,SQL的解析的过程中,会把硬解析之后的SQL放在放在共享池中,如果我们清空了共享池,那么就需要重新做硬分析。

         Oracle SQL的硬解析和软解析

      http://www.cndba.cn/Dave/article/1185

 

 

关于这点的验证,可以参考如下方法:

(1)       开启SQL_TRACE

(2)       做一条事务

(3)       清空缓冲区

(4)       在做同样的事务

(5)       关闭SQL_TRACE

(6)       tkprof 查看trace文件,生成文件时加上aggregate=no参数,这样如果是一条SQL执行多次,在tkproftrace文件中会分别列出来。 这个参数默认是YES

 

Oracle SQL Trace 10046 事件

         http://www.cndba.cn/Dave/article/1571

 

 

1.2 V$SQL_SHARED_CURSOR

         官网链接:http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/dynviews_3058.htm#REFRN30254

 

         这个视图存放了SQL在执行过程中游标共享的信息,它能帮助我们分析看起来一样的SQL,为什么没有共享的原因。

 

SQL> show parameter cursor_sharing;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------

cursor_sharing                       string      EXACT

查看SQL

SQL> select parsing_user_id puid,parsing_schema_id psid,sql_text,sql_id,child_address from v$sql where sql_text like 'insert into t%';

      PUID       PSID SQL_TEXT                       SQL_ID        CHILD_AD

---------- ---------- ------------------------------ ------------- --------

         0          0 insert into tabpart$ (obj#, da 9hp6m1g7j275b A21042D8

0                                           0 insert into tab$(obj#,ts#,file asnhcg241fr2y A877959C

 

--- 如果这里有多条SQL_TEXTSQL_ID相同的,就说明SQL没有重用。 我们可以用如下SQL来确定是哪里不一致造成:

 

查看不能重用原因:

SQL> select * from v$sql_shared_cursor where sql_id='asnhcg241fr2y';

 

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A I T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B

------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

asnhcg241fr2y A8779678 A877959C            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

 

如果这里有Y,就是导致不能重用的原因, 这些字母和V$SQL_SHARED_CURSOR 每个字段对应。

 

 

1.3 V$SESSION

         官网链接:http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/dynviews_3016.htm#REFRN30223

 

         我们可以从该视图查看用户会话的信息。可以使用machine或者module找到我们的用户。Macine 是客户端机器的名称,userName是会话连接时提供的用户名,Program是客户端执行程序的名称,moduleOracle 的存储过程DBMS_ALLPLCATION_INFO.SET_MODULE给出的执行程序的名称。

 

         这种直接查询v$session视图的方法只适合哪种两层结构的C-S架构,这种是客户端直接连接到数据库。 但是现在基本都是三层架构。 通过中间件如weblogic来连接数据库。 这种情况下就需要在中间件服务上进行跟踪,比如获得用户道和中间件的连接信息,然后根据中间件的信息或者日志来确定用户的最终信息。

 

         V$SESSION 常用来查看用户当前的状态,当前执行的SQL语句,SQL语句执行时间,以及等待事件等。

 

         V$SESSION 里面有个字段last_call_et(单位:秒),表示执行时间,这里有两种状态:

1.       Session 处于active 状态,该字段表示session变成active到现在的时间;

2.       Session处于inactive状态, 此时表示session 变成inactive到现在的时间。

 

示例1:查询activesession

SQL> select status,last_call_et,event from v$session where sid=23;

STATUS   LAST_CALL_ET EVENT

-------- ------------ --------------------------------------------

INACTIVE         9976 SQL*Net message from client

 

这里的9976 表示的从session变成inactive到现在的秒数。

示例2:查询inactivesession

 

/* Formatted on 2010/9/6 16:52:32 (QP5 v5.115.810.9015) */

SELECT   a.sql_text,

         b.status,

         b.last_call_et,

         b.event

  FROM   v$sql a, v$session b

 WHERE   a.sql_id = b.sql_id AND b.sid = '279';

 

注意:

RAC 状态下,会话需要来自不同的实例,所以在RAC 环境下需要使用GV$SESSION视图, 因为这个视图含有INST_ID 字段,通过这个字段可以区别实例。

 

 

1.4 V$SESSTAT

         官网链接:http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/dynviews_3027.htm#REFRN30232

 

         这个视图记录了某个session从运行以来各种资源统计数据,通过关联表v$statname 可以查询出某个session的资源消耗情况,如:

         /* Formatted on 2010/9/6 17:06:56 (QP5 v5.115.810.9015) */

SELECT   a.sid, b.name, a.VALUE

  FROM   v$sesstat a, v$statname b

 WHERE   a.sid = 23 AND a.statistic# = b.statistic#

         AND b.name IN

                  ('consistent gets',

                   'physical reads',

                   'parse count (total)',

                   'parse count (hard)');

 

       SID NAME                      VALUE

---------- -------------------- ----------

        23 consistent gets           29750

        23 physical reads              386

        23 parse count (total)         387

        23 parse count (hard)           82

 

这里显示了SID=23session的信息。

 

 

1.5 V$SESSION_WAIT

官网链接地址:http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/dynviews_3023.htm#REFRN30229

        

         V$SESSION_WAIT 记录了会话的一些等待信息,这些等待信息在v$session视图里可以可以查到。

 

示例:

         /* Formatted on 2010/9/6 17:19:40 (QP5 v5.115.810.9015) */

SELECT   event,

         p1,

         p1text,

         p2,

         p2text,

         p3,

         p3text,

         wait_time,

         seconds_in_wait,

         state

  FROM   v$session_wait

 WHERE   sid = 23;

 

关于等待事件参考Blog    

                  Oracle 常见的33个等待事件

                   http://blog.csdn.net/tianlesoftware/archive/2010/08/12/5807800.aspx

 

 

 

二. 性能参数

         性能参数指它的设置会影响数据库性能问题的初始化参数。 这些参数比较多,具体参考ORACLE 官网文档。

 

2.1 CURSOR_SHARING

 

         该参数决定在什么情况下可以使用共享游标,即SQL重用。它有三个值: EXACT, SIMILAR FORCE.

 

默认情况下,oracle 将该参数值是EXACT. 意思是SQL必须绝对一样才能共享游标,否则将作为新的SQL语句处理。

         这种设置的意义在于,从Oracle层面来看,通过精确地匹配每个SQL语句,就可以保证只有语句完全相同的SQL,才可以在共享池中被重用,否则将作为新的SQL语句对待。 而把构造完全一样的SQL语句的任务留给用应用来完成,即由应用来通过变量绑定的方式达到SQL重用,而不是依赖ORACLE来实现,这样的好处是可以大大减少ORACLE花费在SQL分析上的资源消耗(cursor_sharing=similar,及避免Oracle不加判断地绑定变量导致执行计划选择的错误(cursor_sharing=force.

 

2.1.1 cursor_sharing=exact(默认值)

         这种情况下,只有SQL完全一样的,才会在共享池中重用SQL,我们可以使用绑定变量来实现SQL一样。但是在OLTP系统中,如果绑定变量的效果不太好,将CURSOR_SHARING设置为exact 就会增加Oracle SQL 的硬分析量,消耗更多的系统资源。 如果出现这种情况,cursor_sharing 就需要设置为其他的两个值。

 

2.1.2 cursor_sharing=similar

SQL> alter session set cursor_sharing=similar;

会话已更改。

SQL> select * from all_objects set_similar where object_id=10;

SQL> select * from all_objects set_similar where object_id=20;

SQL> select sql_text from v$sql where sql_text like '%set_similar%';

 

SQL_TEXT

------------------------------------------------------------------------------

select * from all_objects set_similar where object_id=:"SYS_B_0"

select * from all_objects set_similar where object_id=:"SYS_B_0"

 

如果你测试的结果不一样,把共享池清空一下就可以了:

         SQL> alter system flush shared_pool;

 

从这个结果看,当设置cursor_sharing=similar 时,Oracle会将SQL语句中的谓词条件用同一个名称的一个变量替代:SYS_B_0, 如果谓词中还有其他变量,将一次使用SYS_B_1SYS_B_2. 这两条语句看起来一样,但是,Oracle 依然会把它们作为2SQL语句来处理。

 

 

2.1.3 cursor_sharing=force

 

SQL> alter session set cursor_sharing=force;

SQL> select * from all_objects set_similar where object_id =2;

SQL> select * from all_objects set_similar where object_id =1;

SQL> select sql_text from v$sql where sql_text like '%set_similar%';

SQL_TEXT

--------------------------------------------------------------------------

select * from all_objects set_similar where object_id =:"SYS_B_0"

 

如果你测试的结果不一样,把共享池清空一下就可以了:

         SQL> alter system flush shared_pool;

 

         从上面的结果看,当设置cursor_sharing=force时,Oracle 会把这两条SQL语句的谓词用变量SYS_B_0代替,并且将它们看做同一条SQL语句来处理。

 

OLTP系统才能使用绑定变量带来性能上的提升,因为在这样的系统中,SQL执行计划基本上是相同的,不会因为谓词的条件而改变。

而在OLAP系统中,因为OLAP系统中数据的变化非常大,列上的数据分布也可能很不均匀,这时候使用绑定变量,可能会出现问题。

按照Oracle 官方的说法,将参数值设置为EXACT是最优的。但是它的前提是需要通过应用程序绑定变量来达到最优的SQL重用。 只有高效的变量绑定,EXACT值才是最优的。而SimilarForce 是在系统没有使用绑定变量时,为了降低系统大量的SQL解析而使用的补救方法,但是它有很多问题,如不加区别或者略加区别的对谓词强制绑定变量,导致SQL的执行计划错误。

 

SIMILAR Force 的区别:

         Similar如果CBO 发现被绑定变量的谓词还有其他执行计划可以选择,如果谓词条件的值有变化,就将会产生一个新的子游标,而不是重用之前的SQL语句;如果谓词没有其他的执行计划可选择,则忽略谓词的值,重用之前的SQL语句。

 

         Force CBOSQL 语句的所有谓词用变量替换,只做一次硬解析,之后所有的SQL都重用第一个SQL语句。

 

 

2.2 DB_FILE_MULTIBLOCK_READ_COUNT

         Oracle 在做一次连续的数据库扫描时,一次I/O 允许读取的最大数据块数,但有一个限制,就是每次I/O的大小不能超过Oracle 运行的操作系统的最大I/O值(通常是1M)。

         假设一张表有10240KB大小,数据块的大小为8kb,设置DB_FILE_MULTIBLOCK_READ_COUNT=32,那么我们对这张表做全表扫描的次数为:     10240/(32*8)=40次,即Oracle 对这张表做扫描需要花费40I/O 但是实际上,Oracle 花费的I/O次数可能大于这个值,可可能小于这个值。 因为Oracle在读多个数据库时,当内存中已经有了某个数据块时,Oracle 就不再从磁盘中读取它。

 

         对于OLTP数据库来说,每次用户读取的记录数非常少,这个值可以考虑设置的小一点;对于OLAP系统,因为查询的量非常大,所以可以考虑设置大一些。

 

注意: 多数据块读取操作只发生在一下两种情况:

(1)       FTS(FULL TABLE SCAN)

(2)       INDEX_FFS(INDEX FAST FULL SCAN)

 

 关于这两种连接方式,参考Blog

Oracle 索引扫描的四种类型

http://www.cndba.cn/Dave/article/1455

 

这个参数才10g R2版本后,Oracle不建议修改它的默认值。 当设置这个值为默认值时,Oracle 会通过收集SQLI/O 情况,来动态设置这个参数的值,如果手工修改了它的默认值,Oracle 将确定使用这个新值。

         这个参数影响到CBO对成本的评估,通常来说,这个值设置的越大,FFS或者INDEX_FFS 得成本就会越低,执行计划就越向这面倾斜。

 

 

 

 

 

 

整理自《让Oracle 跑的更快》

------------------------------------------------------------------------------

QQ: 492913789
Email: ahdba@qq.com
Blog: http://www.cndba.cn/dave

网上资源: http://tianlesoftware.download.csdn.net

相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

DBA1 群:62697716(); DBA2 群:62697977()

DBA3 群:63306533;     聊天 群:40132017

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值