latch cache buffer chains 引发的sql优化问题

kylefree

系统和数据库版本aix6.1+oracle11.2.0.2，在一套准备新上的系统上做压力测试，当压力发起后，存在大量latch cache buffer chains等待事件，根据当时的sql跟踪和awr报告，发现一条很可疑的sql，每次执行产生大量逻辑读操作，引起的latch cache buffer chains等待。类似select * from (select ...  from tab where a=? and b<>'null' order by c desc) where rownum<:1这样的语句结构（具体请见附件txt），一开始还被<>'null'给忽悠到了，后来注意到是带引号的字符串值是null。。。经过一下午的捣鼓。晚上终于想出个办法把sql结构给改了，同时增加一条索引create index idx_T_VPOS_INFO_test2 on T_VPOS_INFO (REG_ID,STRA_ID,VPOS_ID desc);看了下执行代价。逻辑读比原先少很多。但是不知道改的对不对，会不会发生逻辑变化。。还请大神看看。附件awr和语句执行情况记录。  由于需要desc排序返回结果，似乎exists不适用，有些问题。。
表的总记录数5w多，符合where条件的2w2多，当rownum<500时，优化后sql执行效率明显，但当rownum<25000也就是符合where的值全返回时，优化后的sql反而不及原始sql。。
又测试了下优化后的sql在rownum<50的情况，相同的执行计划，产生了不同的执行代价，逻辑读的差距很大，2次sort的逻辑读低于1次sort的逻辑读。见附件。。。

[ 本帖最后由 kylefree 于 2012-8-1 00:32 编辑 ]

dla001

没钱看附件了。

表的总记录数5w多，符合where条件的2w2多，当rownum<500时，优化后sql执行效率明显，但当rownum<25000也就是符合where的值全返回时，优化后的sql反而不及原始sql

这个很正常，但要知道，一般人能看的数据也只有前面的几百条。后面的数据很少有机会翻到的。如果后面的数据经常被查询，那可能是你的应用提供的功能与用户想要的功能不一致。

kylefree

附件不要钱的。。没设置钱呀。
现在困惑下面这个了。
又测试了下优化后的sql在rownum<50的情况，相同的执行计划，产生了不同的执行代价，逻辑读的差距很大，2次sort的逻辑读低于1次sort的逻辑读。见附件。。。

dla001

如果你的SQL中写的是<50 而不是传的变量进去，请看一下10046的结果。autotrace 结果可能不是实际的情况。

1112061

这个下载还要money？

kylefree

不用money的啊。费用都是0的。没设置过。
后来想到了，用这条索引解决了
create index idx_T_VPOS_INFO_test on T_VPOS_INFO (VPOS_ID desc,REG_ID,STRA_ID);
SQL> explain plan for
  2  select *
  3    from (select vpos0_.VPOS_ID            as VPOS1_23_,
  4                 vpos0_.CUSTOM_ID          as CUSTOM2_23_,
  5                 vpos0_.REG_ID             as REG3_23_,
  6                 vpos0_.VPOS_STATUS        as VPOS4_23_,
  7                 vpos0_.AUTH_FLAG          as AUTH5_23_,
  8                 vpos0_.COMPLET_FLAG       as COMPLET6_23_,
  9                 vpos0_.TELE_NO            as TELE7_23_,
10                 vpos0_.VPOS_ADDR          as VPOS8_23_,
11                 vpos0_.MID                as MID23_,
12                 vpos0_.DEV_ID             as DEV10_23_,
13                 vpos0_.RELATE_FLAG        as RELATE11_23_,
14                 vpos0_.INPUT_TIME         as INPUT12_23_,
15                 vpos0_.FIRST_TIME         as FIRST13_23_,
16                 vpos0_.LAST_SIGN_TIME     as LAST14_23_,
17                 vpos0_.NEXT_SIGN_TIME     as NEXT15_23_,
18                 vpos0_.APP_MODEL_ID       as APP16_23_,
19                 vpos0_.DATA_ORIG          as DATA17_23_,
20                 vpos0_.INIT_KEY_FLAG      as INIT18_23_,
21                 vpos0_.STRA_ID            as STRA19_23_,
22                 vpos0_.LAST_SIGN_END_TIME as LAST20_23_,
23                 vpos0_.INTERVAL_DAYS      as INTERVAL21_23_,
24                 vpos0_.BRANCH_ID          as BRANCH22_23_,
25                 vpos0_.TERMINALID         as TERMINALID23_,
26                 vpos0_.AREACODE           as AREACODE23_,
27                 vpos0_.TASK_ID            as TASK25_23_,
28                 vpos0_.TASK_TYPE          as TASK26_23_,
29                 vpos0_.FRTIMES            as FRTIMES23_,
30                 vpos0_.OLD_DEV_ID         as OLD28_23_,
31                 vpos0_.STOCK_FLAG         as STOCK29_23_,
32                 vpos0_.BMS_MID            as BMS30_23_,
33                 vpos0_.TERM_TYPE          as TERM31_23_
34            from T_VPOS_INFO vpos0_
35           where (vpos0_.REG_ID in ('10240004',
36                                    '10240003',
37                                    '10240006',
38                                    '10240005',
39                                    '10240002',
40                                    '10240001',
41                                    '102410',
42                                    '102411',
43                                    '102412',
44                                    '102409',
45                                    '102408',
46                                    '102407',
47                                    '102406',
48                                    '102405',
49                                    '102404',
50                                    '102403',
51                                    '102402',
52                                    '102401',
53                                    '102400'))
54             and vpos0_.STRA_ID <> 'null'
55           order by vpos0_.VPOS_ID desc)
56   where rownum <= 500;

Explained.

SQL> select * from table(DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3040648111

------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |   500 |   423K|   377   (1)| 00:00:05 |
|*  1 |  COUNT STOPKEY                |                      |       |       |            |          |
|   2 |   VIEW                        |                      |   510 |   431K|   377   (1)| 00:00:05 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_VPOS_INFO          | 26462 |  6279K|   377   (1)| 00:00:05 |
|*  4 |     INDEX FULL SCAN           | IDX_T_VPOS_INFO_TEST |   510 |       |    92   (2)| 00:00:02 |
------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=500)
   4 - filter(("VPOS0_"."REG_ID"='102400' OR "VPOS0_"."REG_ID"='10240001' OR
              "VPOS0_"."REG_ID"='10240002' OR "VPOS0_"."REG_ID"='10240003' OR "VPOS0_"."REG_ID"='10240004'
              OR "VPOS0_"."REG_ID"='10240005' OR "VPOS0_"."REG_ID"='10240006' OR "VPOS0_"."REG_ID"='102401'
              OR "VPOS0_"."REG_ID"='102402' OR "VPOS0_"."REG_ID"='102403' OR "VPOS0_"."REG_ID"='102404' OR
              "VPOS0_"."REG_ID"='102405' OR "VPOS0_"."REG_ID"='102406' OR "VPOS0_"."REG_ID"='102407' OR
              "VPOS0_"."REG_ID"='102408' OR "VPOS0_"."REG_ID"='102409' OR "VPOS0_"."REG_ID"='102410' OR

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              "VPOS0_"."REG_ID"='102411' OR "VPOS0_"."REG_ID"='102412') AND "VPOS0_"."STRA_ID"<>'null')

23 rows selected.