淘先锋技术网

首页 1 2 3 4 5 6 7

oracle深入浅出 from,深入浅出Oracle学习笔记（9）

第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

2from user_indexes where table_name='T';

INDEX_NAMETABLE_NAME

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

INDEX_TYPE

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

IDX_OBJNAME_DESCT

FUNCTION-BASEDNORMAL

SQL> select column_name,column_position,descend from user_ind_columns

2where 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_NAMETABLE_NAME

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

COLUMN_EXPRESSION

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

COLUMN_POSITION

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

IDX_OBJNAME_DESCT

"OBJECT_NAME"

1