第9章性能诊断与SQL优化
在Oracle10g之前，缺省情况下AUTOTRACE功能并未打开，需要通过手工启动该功能。从Oracle 9i开始，Oracle提供了一个新的工具dbms_xplan用于格式化和查看SQL的执行计划，其原理是通过对plan_table的查询和格式化提供更友好的用户输出。
Explain plan for select count(*) from dual;
@?/rdbms/admin/utlxplp
该脚本中调用了dbms_xplan：
Select * from table(dbms_xplan.display());
在Oracle10g中plan_table不再需要创建，Oracle缺省增加了一个字电表plan_table$，然后基于plan_table$创建公用同义词供用户使用。
AUTOTRACE功能的内部操作
当使用AUTOTRACE功能时，在数据库内部，Oracle实际上是启动了2个session连接，一个session用于执行查询等操作，另外一个session用于记录执行计划和输出最终结果等操作。注意，这两个session都是由一个进程衍生创建的。这就是通常所说的，一个进程在数据库中可能对应多个session。
通过在全局启用10046事件，可以得到AUTOTRACE的内部操作：
Alter system set event=’10046 trace name context forever,level12’scope=spfile;
从V$SYSSTAT视图中，可以查询得到关于全表扫描的系统统计信息：
Select name,value from v$sysstat
Where name in (‘table scans(short tables)’,’table scans(long tables)’);
其中table scans(short tables)指对于小表的全表扫描的。Table scans(long tables)指对于大表的全表扫描繁的次数。
Oracle通过一个内部参数_small_table_threshold来定义大表和小表的界限。缺省情况下该参数等于2%的Buffer数量。
之所以要区分大小表是因为全表扫描可能引起Buffer Cache的抖动，缺省情况下，大表的全表扫描会被置于LRU的末端，以期尽快老化，减少Buffer的占用。
有效地降低SQL的逻辑读是SQL优化的基本原则之一。
使用SQL_TRACE /10046事件进行数据库诊断
当在数据库中启用SQL_TRACE或者设置10046事件之后，Oracle将会启动内核跟踪程序，持续记录会话的相关信息，并写入到相应trace文件中。跟踪记录的内容包括SQL的解析过程、SQL的执行计划、绑定变量的使用及会话中发生的等待事件等。
SQL_TRACE及10046事件的基础介绍
在使用SQL_TRACE之前，需要注意几点：
初始化参数TIMED_STATISTICS最好设为TRUE，否则一些重要信息不会被收集；
设置MAX_DUMP_FILE_SIZE
在session级可以设置：
Alter session set max_dump_file_size=unlimited;
在全局启用SQL_TRACE
在当前session级设置
跟踪其他用户进程：
在很多时候需要跟踪其他用户的进程，而不是当前用户，这可以通过Oracle提供的系统包DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION来完成。
通过查询v$session可以获得sid、serial#等信息：
Select sid,serial#,username from v$session where username is not null;
设置跟踪：
Exec dbms_system.set_sql_trace_in_session(9,437,true);
如果要对其他用户的参数进行设置，需要用到dbms_system包中的另一个过程：
Begin
Sys.dbms_system.set_bool_param_in_session(18,1605,’timed_statistics’,true);
Sys.dbms_system.set_bool_param_in_session(18,1605,’max_dump_file_size’,2147483647);
Sys.dbms_system.set_sql_trace_in_session(18,1605, true);
End;
/
10046事件说明
10046事件是Oracle提供的内部事件，是对sql_trace的增强。10046事件可以设置为以下4个级别：
Level 1：启用标准的SQL_TRACE功能，等价于SQL_TRACE
Level 4：等价于Level 1+绑定值（bind values）
Level 8：等价于Level 1+等待事件跟踪
Level 12：等价于Level 1+ Level 4+ Level 12
类似SQL_TRACE，10046事件可以在全局设置，也可以在session级设置。
在全局设置：
在参数文件中增加：event=’10046 trace name context forever, level12’
此设置对所有用户的所有进程生效、包括后台进程。
对当前session设置：
Alter session set events ‘10046 trace name context forever’;
Alter session set events ‘10046 trace name context forever, level8’;
Alter session set events ‘10046 trace name context off’;
对其他用户session设置：
通过DBMS_SYSTEM.SET_ENV系统包来实现：
Exec dbms_system.set_ev(9,437,10046,8,’eygle’); --执行跟踪
Exec dbms_system.set_ev(9,437,10046,0,’eygle’); --结束跟踪
诊断案例一：隐式转换与索引失效
在Oracle开发中，应该尽量避免使用隐式的数据类型转换，因为隐式数据类型转换可能会带来索引失效的问题，给系统埋下隐患。使用函数导致索引失效的问题与此类似。
诊断案例二：跟踪后台错误
很多时候，在进行数据库操作时，如drop user、drop table等，经常会遇到这样的错误：
ORA-00604：error occurred at recursive SQL level 1.
关于recursive SQL错误的说明：
当发出一条简单的SQL命令以后，Oracle数据库要在后台解析这条命令，并转换为Oracle数据库的一系列后台操作，这些后台操作统称为递归SQL。
比如create table这样一条简单的DDL命令，Oracle数据库在后台，实际上要把这个命令转换为对obj$、tab$、col$等低层表的插入操作；对于drop table操作，则是在这些系统表中进行反向删除操作。
10046与等待事件
10046事件时SQL_TRACE功能的增强，可以通过10046跟踪获得更多的信息，包括非常有用的等待事件等。对于不同进程的等待可以通过v$session_wait来查询，对于数据库全局等待可以通过v$system_event来获得。
等待事件Db file scattered read意味着使用全表扫描来访问数据
初始化参数db_file_multiblock_read_count代表Oracle在执行全表扫描时每次IO操作可以读取的数据块的数量。
如果db_file_multiblock_read_count参数设置为16，而extent大小为8个block，Oracle的一次IO操作不能跨越extent。所以全表扫描每次只能读取8个block。
可以通过下面的方法进行调整：
Create tablespace mytbs datafile ‘…..’ size100Mexent management local uniform. size 256K;
Alter table t move tablespace mytbs;
Select file_id,block_id,blocks from dba_extents where segment_name=’T’;
Show parameter db_file_multiblock_read_count
通常较大的db_file_multiblock_read_count设置可以加快全表扫描的执行，但是根据经验大于32的设置通常不会带来更大的性能提高。
10046与执行计划的选择
需要注意的是，增大db_file_multiblock_read_count参数的设置，会使全表扫描的成本降低，在CBO优化器下可能会使Oracle更倾向于使用全表扫描而不是索引访问。
使用物化视图进行翻页性能调整
物化视图通过预计算或汇总构建自己的独立存储，从而可以极大地提高相关处理的性能，通过查询重写功能，Oracle可以自动对SQL进行改写以最大程度地发挥物化视图的作用。
物化视图是典型的通过存储空间换取性能的方式，通过物化视图，Oracle可以：
有效地减少逻辑读取；
减少写操作——通过消除排序及聚集实现；
减少CPU的消耗——无需实时进行复杂运算；
显著提高相应速度。
结合业务逻辑，考虑创建物化视图，通过物化视图的中间存储消除不必要的全表扫描，同时必须充分考虑用户的业务需求是否允许足够的刷新间隔。
降序索引本质上是基于函数的索引，只有在CBO下才能被用到：
Create index inx_desc on HW_USER(col_nm1 desc,col_nm2 desc,col_nm3 desc);
实验验证：
SQL> show user
USER为"SCOTT"
SQL> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle9iEnterpriseEdition Release9.2.0.1.0 - Production
SQL> create table t as select * from user_objects;
表已创建。
SQL> create index idx_objname_desc on t(object_name desc);
索引已创建。
SQL> select index_name,table_name,index_type
2 from user_indexes where table_name='T';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
INDEX_TYPE
---------------------------
IDX_OBJNAME_DESC T
FUNCTION-BASEDNORMAL
SQL> select column_name,column_position,descend from user_ind_columns
2 where table_name='T';
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION DESC
--------------- ----
SYS_NC00013$
1 DESC
降序索引以及FBI的定义可以从DBA_IND_EXPRESSIONS或USER_IND_EXPRESSIONS视图中获得。
SQL> select * from user_ind_expressions where table_name='T';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_EXPRESSION
--------------------------------------------------------------------------------
COLUMN_POSITION
---------------
IDX_OBJNAME_DESC T
"OBJECT_NAME"
1
