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

7j6v5781v6

　　一．性能视图
　　性能视图是Oracle中一些记录数据库性能方面的视图，通过查看这些视图，获得数据库当前或历史上某个时间的性能数据。它比SQL_TRACE，AWR报告获取数据更及时，便捷。
　　
　　Oracle动态性能视图
　　http://blog.csdn.net/xujinyang/article/details/6831389
　　
　　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*fromv$session;
　　从这里确定根据machine列和program列确定SID。
　　
　　根据SID确定SQL:
　　/* Formatted on 2010/9/6 11:08:21 (QP5 v5.115.810.9015) */
　　SELECTa.sql_text,
　　b.status,
　　b.last_call_et,
　　b.event
　　FROMv$sqla,v$session b
　　WHEREa.sql_id=b.sql_idANDb.sid=23
　　
　　也可以根据进程号来查看。具体参考Blog：
　　oracle实时查询最耗CPU资源的SQL语句
　　http://blog.csdn.net/xujinyang/article/details/6829975
　　
　　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) */
　　SELECTsql_text,
　　cpu_time/(1000*1000)t_cpu,
　　TRUNC(elapsed_time/(1000*1000))t_elap,
　　(cpu_time/elapsed_time/(1000*1000))*100pct
　　FROMv$sql
　　WHEREsql_textLIKE'insert into sf select%'
　　
　　SQL_TEXTT_CPUT_ELAPPCT
　　------------------------------------------------------------
　　insert into sf select * from u.3120020.000056249
　　insert into sf select * from u.2964020.000062524
　　
　　返回如上结果，如果说T_ELAP时间比较多，而CPU时间比较少，说明这条语句在执行过程中基本处于等待状态。关于各个等待事件，参考Blog：
　　Oracle常见的33个等待事件
　　http://blog.csdn.net/xujinyang/article/details/6882035
　　
　　1.1.3共享池中的SQL
　　并不是所有的SQL语句都可以从V$SQL中找到，因为ORACLE会动态地更新共享池的信息，将一些过旧的SQL从共享池中删除，以便于新的SQL语句提供共享池的空间。
　　
　　我们可以手动的清空共享池中的信息，SQL语句如下：
　　SQL>alter system flush shared_pool;
　　
　　我们知道，SQL的解析的过程中，会把硬解析之后的SQL放在放在共享池中，如果我们清空了共享池，那么就需要重新做硬分析。
　　Oracle SQL的硬解析和软解析
　　http://blog.csdn.net/xujinyang/article/details/6829604
　　
　　关于这点的验证，可以参考如下方法：
　　（1）开启SQL_TRACE
　　（2）做一条事务
　　（3）清空缓冲区
　　（4）在做同样的事务
　　（5）关闭SQL_TRACE
　　（6）用tkprof查看trace文件，生成文件时加上aggregate=no参数，这样如果是一条SQL执行多次，在tkprof的trace文件中会分别列出来。这个参数默认是YES。
　　
　　Oracle SQL Trace和10046事件
　　http://blog.csdn.net/xujinyang/article/details/6831398
　　
　　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;
　　NAMETYPEVALUE
　　------------------------------------ ----------- ------
　　cursor_sharingstringEXACT
　　查看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%';
　　PUIDPSID SQL_TEXTSQL_IDCHILD_AD
　　---------- ---------- ------------------------------ ------------- --------
　　00 insert into tabpart$ (obj#, da 9hp6m1g7j275b A21042D8
　　00 insert into tab$(obj#,ts#,file asnhcg241fr2y A877959C
　　
　　---如果这里有多条SQL_TEXT，SQL_ID相同的，就说明SQL没有重用。我们可以用如下SQL来确定是哪里不一致造成：
　　
　　查看不能重用原因：
　　SQL> select * from v$sql_shared_cursor where sql_id='asnhcg241fr2y';
　　
　　SQL_IDADDRESSCHILD_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 A877959C0 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是客户端执行程序的名称，module是Oracle的存储过程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：查询active的session：
　　SQL> select status,last_call_et,event from v$session where sid=23;
　　STATUSLAST_CALL_ET EVENT
　　-------- ------------ --------------------------------------------
　　INACTIVE9976 SQL*Net message from client
　　
　　这里的9976表示的从session变成inactive到现在的秒数。
　　示例2：查询inactive的session：
　　
　　/* Formatted on 2010/9/6 16:52:32 (QP5 v5.115.810.9015) */
　　SELECTa.sql_text,
　　b.status,
　　b.last_call_et,
　　b.event
　　FROMv$sqla,v$session b
　　WHEREa.sql_id=b.sql_idANDb.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) */
　　SELECTa.sid,b.name,a.VALUE
　　FROMv$sesstata,v$statname b
　　WHEREa.sid=23ANDa.statistic#=b.statistic#
　　ANDb.nameIN
　　('consistent gets',
　　'physical reads',
　　'parse count (total)',
　　'parse count (hard)');
　　
　　SID NAMEVALUE
　　---------- -------------------- ----------
　　23 consistent gets29750
　　23 physical reads386
　　23 parse count (total)387
　　23 parse count (hard)82
　　
　　这里显示了SID=23的session的信息。
　　
　　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) */
　　SELECTevent,
　　p1,
　　p1text,
　　p2,
　　p2text,
　　p3,
　　p3text,
　　wait_time,
　　seconds_in_wait,
　　state
　　FROMv$session_wait
　　WHEREsid=23;
　　
　　关于等待事件参考Blog：
　　Oracle常见的33个等待事件
　　http://blog.csdn.net/xujinyang/article/details/6882035
　　
　　二．性能参数
　　性能参数指它的设置会影响数据库性能问题的初始化参数。这些参数比较多，具体参考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_1，SYS_B_2.这两条语句看起来一样，但是，Oracle依然会把它们作为2条SQL语句来处理。
　　
　　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值才是最优的。而Similar和Force是在系统没有使用绑定变量时，为了降低系统大量的SQL解析而使用的补救方法，但是它有很多问题，如不加区别或者略加区别的对谓词强制绑定变量，导致SQL的执行计划错误。
　　
　　SIMILAR和Force的区别：
　　Similar：如果CBO发现被绑定变量的谓词还有其他执行计划可以选择，如果谓词条件的值有变化，就将会产生一个新的子游标，而不是重用之前的SQL语句；如果谓词没有其他的执行计划可选择，则忽略谓词的值，重用之前的SQL语句。
　　
　　Force：CBO和SQL语句的所有谓词用变量替换，只做一次硬解析，之后所有的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对这张表做扫描需要花费40次I/O。但是实际上，Oracle花费的I/O次数可能大于这个值，可可能小于这个值。因为Oracle在读多个数据库时，当内存中已经有了某个数据块时，Oracle就不再从磁盘中读取它。
　　
　　对于OLTP数据库来说，每次用户读取的记录数非常少，这个值可以考虑设置的小一点；对于OLAP系统，因为查询的量非常大，所以可以考虑设置大一些。
　　
　　注意：多数据块读取操作只发生在一下两种情况：
　　（1）FTS(FULL TABLE SCAN)
　　（2）INDEX_FFS(INDEX FAST FULL SCAN)
　　
　　关于这两种连接方式，参考Blog：
　　Oracle索引扫描的五种类型
　　http://blog.csdn.net/xujinyang/article/details/6832643
　　
　　这个参数才10g R2版本后，Oracle不建议修改它的默认值。当设置这个值为默认值时，Oracle会通过收集SQL的I/O情况，来动态设置这个参数的值，如果手工修改了它的默认值，Oracle将确定使用这个新值。
　　这个参数影响到CBO对成本的评估，通常来说，这个值设置的越大，FFS或者INDEX_FFS得成本就会越低，执行计划就越向这面倾斜。
　　
　　整理自《让Oracle跑的更快》
　　------------------------------------------------------------------------------