ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 深入浅出Oracle学习笔记(9)

深入浅出Oracle学习笔记(9)

原创 Linux操作系统 作者:gvora 时间:2009-05-31 22:53:18 0 删除 编辑

9 性能诊断与SQL优化

Oracle 10g之前,缺省情况下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());

Oracle 10gplan_table不再需要创建,Oracle缺省增加了一个字电表plan_table$,然后基于plan_table$创建公用同义词供用户使用。

 

AUTOTRACE功能的内部操作

当使用AUTOTRACE功能时,在数据库内部,Oracle实际上是启动了2session连接,一个session用于执行查询等操作,另外一个 session用于记录执行计划和输出最终结果等操作。注意,这两个session都是由一个进程衍生创建的。这就是通常所说的,一个进程在数据库中可能对应多个session

通过在全局启用10046事件,可以得到AUTOTRACE的内部操作:

Alter system set event=’10046 trace name context forever,level 12’ 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_TRACE10046事件的基础介绍

在使用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可以获得sidserial#等信息:

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_TRACE10046事件可以在全局设置,也可以在session级设置。

在全局设置:

在参数文件中增加:event=’10046 trace name context forever, level 12’

此设置对所有用户的所有进程生效、包括后台进程。

对当前session设置:

Alter session set events ‘10046 trace name context forever’;

Alter session set events ‘10046 trace name context forever, level 8’;

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 userdrop table等,经常会遇到这样的错误:

ORA-00604error 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大小为8blockOracle的一次IO操作不能跨越extent。所以全表扫描每次只能读取8block

可以通过下面的方法进行调整:

Create tablespace mytbs datafile ‘…..’ size 100M exent 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

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

Oracle9i Enterprise Edition Release 9.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-BASED NORMAL

 

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_EXPRESSIONSUSER_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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15203236/viewspace-604582/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-12-30

  • 博文量
    62
  • 访问量
    288204