oracle dba诊断案例性能优化 1

  读盖国强他们牛人编写的<oracle dba 性能书>系列的读后:

1 查询v$session视图，获取当前进程：
  select sid,serial#,username from v$session where username is not null;

    比如出来sid  serial#  username
             7    286
      然后对相应的应用会话用sql_trace跟踪
     exec dbms_system.set_sql_trace_in_session(7,286,true)
      然后再关闭
        exec dbms_system.set_sql_trace_in_session(7,286,false)


2 比如有ora-01438错误，则通过errstack进行后台跟踪，获得详细信息
      alter system set events='1438 trace name errorstack forevel,level 3';
   然后执行出错的过程，再关闭：
     alter system set events='1438 trace name errorstack off';

3 书上说,oracle 11g中,shared pool中每个subpool至少为512MB呀,所以内存还是要越大

越好

4 logminer的使用，具体见
   http://blog.csdn.net/xujinyang/article/details/7031968一文，写的不错

5 oracle的in值里，不允许超过1000个值，即select * from id in (xxxxxxxx)

6 oracle的fetchsize默认为10，当需要从服务端拿大量数据时，可以加大这个参数，增

加性能，不要大于100，大于40好点

7 数据库访问的漏斗法则
    先到后顺序：先减少数据访问（减少磁盘访问） 2 返回更少的数据（减少
网络传输）  3 减少交互次数  4 减少CPU开销 5 增加更多资源
 
8 查询当前数据库连接的程序，机器，SID，EVENT
  select sid,serial#,program,machine,sql_id,event from v$session where
type='USER' and status='ACTIVE';


9 oracle flash 闪回空间：
   增加闪回时间，增加到3小时
     alter system set undo_retention=10800 scope=both;
 
10 同义词的好处
     1）隐藏一个数据库对象的名字和拥有者 2）和视图类似实现更精细控制
3）简化SQL语句
    create public synonym  sales for xxx.xxxx;


11 查看一个SQL的真实执行计划
首先找出：
  select hash_value,child_number,sql_text from v$sql where sql_text like

'%...%'
  
select * from table(
dbms_xplan.display_cursor('hash_value','child_number',advanced'));

   如果要在oracle 9i下获得其执行计划，使用
http://www.laoxiong.net/oracle9i_display_CURSOR.html,用法
   set serverouput on size 100000

比如查出oracle中os端top显示的spid为121345
  exec printsql(123145,'SPID')
    
 
  执行计划的执行顺序为：
   先从计划开头一直往右看，直到最右边并列的代码部分，如果见到并列的，就从上往

下看，对于并列的步骤，靠上的先执行，对于不并列的步骤，靠右的先执行

  10g/11g里执行计划的增强：
  扩展的xplan包：
     select * from table(xplan.display_cursor)
,代码在http://tomszrp.itpub.net/get/11835/xplan.rar
  可以下载，下载后，安装
  @xplan.sql
新增加了order列；以及E-ROWS估计行数，A-ROWS实际行数
   select /*+ gather_plan_statistics */ ........
   select from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
 


12 oracle 中的bind peeking问题：
   使用绑定变量可以减少SQL PARSE，但是使用绑定变量有一个不好的地方，就是对于访

问具有倾斜的列，可能使用错误的执行计划。在Oracle 9i之前，如果WHERE 条件里面全

部使用绑定变量，那么只能使用固定的选择性参数来确定执行计划。

=操作和>=操作的选择性为5%，范围扫描的选择性为25%。缺省值的方式可能生成不好的执

行计划。所以Oracle 9i就出现了一个新的技术，bind peeking。什么是bind peeking呢

？当SQL第一次执行的时候，优化器会根据绑定变量来确定执行计划（如果存在柱状图）

。BIND PEEKING只有当该SQL第一次执行的时候，进行HARD PARSE的时候才进行，第二次

调用该SQL，就不会再次进行BIND PEEKING。这种情况下，就存在另外一个风险，如果某

个列的倾斜性很厉害，那么使用BIND PEEKING就是不安全的，因为不同的参数代入，只能

走第一次执行时的执行计划，那么执行计划就像掷色子一样，要靠运气了。碰到这种情况

，应用就不应该使用绑定变量，而应该改为直接值了。
  这时可以使用刷新一下共享池alter system flush shared_pool;
  或者alter session set "_optim_peek_user_binds"=false;

   我们可以通过隐含的参数来调整数据库默认的bind peeking行为：

_OPTIM_PEEK_USER_BINDS。 如果我们想关闭Bind Variable Peeking，我们可以设置该参

数为 False 即可。

SQL>alter session set "_optim_peek_user_binds"=false


使用了Bind Var能提高性能主要是因为这样做可以尽量避免不必要的硬分析(Hard Parse)

而节约了时间，同时节约了大量的CPU资源。

    当一个Client提交一条Sql给Oracle后，Oracle 首先会对其进行解析(Parse)，然后

将解析结果提交给优化器(Optimiser)来进行优化而取得Oracle认为的最优的Query Plan

，然后再按照这个最优的Plan来执行这个Sql语句(当然在这之中如果只需要软解析的话会

少部分步骤)。

当Oracle接到 Client提交的Sql后会首先在共享池(Shared Pool)里面去查找是否有之前

已经解析好的与刚接到的这一个Sql完全相同的Sql(注意这里说的是完全相同，既要求语

句上的字符级别的完全相同，又要求涉及的对象也必须完全相同)。当发现有相同的以后

解析器就不再对新的Sql在此解析而直接用之前解析好的结果了。这里就节约了解析时间

以及解析时候消耗的CPU资源。尤其是在OLTP中运行着的大量的短小Sql，效果就会比较明

显了。因为一条两条Sql的时间可能不会有多少感觉，但是当量大了以后就会有比较明显

的感觉了。


但是，使用绑定变量的一个缺点是，给出的执行计划并不一定就是SQL在真正应用程序里

所使用的执行计划。这时我们就可以通过 event 10053 事件来查看。

13 SQL PROFILE:可以锁定或者稳定执行计划，在不能修改应用中的SQL
的情况下使用SQL语句按指定的执行计划执行
   可以使用sql tuning advisor来尝试这个SQL；
dbms_sqltune.create_tuning_task
,具体参考oracle dba手记3 ，P141页

14 使用 SQL PROFILE稳定SQL语句的执行计划：
  select * from table(dbms_xplan.display_cursor(null,null,'outline'));
然后使用工具sqlt.zip，从sharedpool,awr中获得sql执行时产生的多个
执行计划的统计信息，然后输入正确需要稳定的执行计划的HASH值，再产生另外
一个脚本，运行这个脚本，会产生对应的sql profile.

   但sql profile适合于某些个别范围小的SQL，影响范围小，如果SQL发生改变，需要
重新生成sql profile.

15 关于NULL：
     除了is null,is not null外，对null的任何操作结果还都是null;
要注意null的相关and和or操作
   AND操作：null and true=null,null and false=false,null and null=null
    or:  null or true=true null or false=false,null or null=null
   not null的值依然是null，因为null表示不确定，无论null为true还是FALSE，始终最

后不确定，为null.
  注意的是:oracle 把null当作varchar2来处理
  ' '空字符串其实是null的字符类型的表现格式。
   btree索引是不存储null值的，而位图索引是存储NULL值的，而如果复合索引中，如果

包含null值，也是可以的，只要有一个索引列不为空；如果是单列索引，IS NULL是肯定

不能使用索引的了，但IS NOT NULL可以。
  大多数函数，如果输入为NULL，则输出也为null.NVL,NVL2,DECODE例外，它们在输入参

数为NULL的时候，结果可能不是NULL，如果这些输入参数均为NULL，则肯定为NULL，如果

不全为NULL，结果可能不是NULL。
  聚集函数不会处理null值，不管为max,min,avg还是sum.


16 可以使用strace来跟踪listner,
  先使用ps -ef|grep tnslsnr
   得到的是父进程ppid=1的监听进程号
    然后使用strace -fr -o /tmp/lsnr.out -p <listner pid监听进程号>
  然后重新连接，再运行后CTRL+C停止STRACE运行。

评论

1 楼 jyjava 2012-01-17  

lz，能否整理一份关于sql查询优化的文档，期待着，呵呵