ITPub博客

首页 > Linux操作系统 > Linux操作系统 > WORKITEM表上有索引WORKITEM$11289258,可查询的时候还是走全表扫描。

WORKITEM表上有索引WORKITEM$11289258,可查询的时候还是走全表扫描。

原创 Linux操作系统 作者:ses19828 时间:2013-07-04 15:51:04 0 删除 编辑
WORKITEM表上有索引WORKITEM$11289258,可查询的时候还是走全表扫描。
 
如图所示:目前最消耗系统资源的顶级的SQL语句,都涉及到这个workitem表
 
SELECT 'wt.workflow.work.WorkItem', A0.blob$context, A0.completedBy, A0.context, A0.description, A0.eventSet, A0.classnamekeyD4, A0.idA3D4, A0.classnamekeyA2ownership, A0.idA3A2ownership, A0.classnamekeyC4, A0.idA3C4, A0.classnamekeyB4, A0.priority, A0.reassigned, A0.required, A0.role, A0.classnamekeyA4, A0.idA3A4, A0.status, A0.taskURLPathInfo, TO_CHAR(A0.createStampA2, 'dd mm yyyy hh24:mi:ss'), A0.markForDeleteA2, TO_CHAR(A0.modifyStampA2, 'dd mm yyyy hh24:mi:ss'), A0.idA2A2, A0.updateCountA2, TO_CHAR(A0.updateStampA2, 'dd mm yyyy hh24:mi:ss'), A0.variablePermissionMap FROM WorkItem A0 WHERE ((A0.classnamekeyB4 = :1 ))
SELECT 'wt.workflow.work.WorkItem', A0.blob$context, A0.completedBy, A0.context, A0.description, A0.eventSet, A0.classnamekeyD4, A0.idA3D4, A0.classnamekeyA2ownership, A0.idA3A2ownership, A0.classnamekeyC4, A0.idA3C4, A0.classnamekeyB4, A0.priority, A0.reassigned, A0.required, A0.role, A0.classnamekeyA4, A0.idA3A4, A0.status, A0.taskURLPathInfo, TO_CHAR(A0.createStampA2, 'dd mm yyyy hh24:mi:ss'), A0.markForDeleteA2, TO_CHAR(A0.modifyStampA2, 'dd mm yyyy hh24:mi:ss'), A0.idA2A2, A0.updateCountA2, TO_CHAR(A0.updateStampA2, 'dd mm yyyy hh24:mi:ss'), A0.variablePermissionMap FROM WorkItem A0 WHERE ((A0.idA3A4 IN (SELECT A0.idA2A2 FROM WfAssignedActivity A0 WHERE ((A0.idA3parentProcessRef = :1 )) UNION ALL SELECT A0.idA2A2 FROM ProjectActivity A0 WHERE ((A0.idA3parentProcessRef = :2 )))))
全表扫描

全表扫描
可以想象一下如果这个表走索引,那么将大大的降低资源的消耗。
使WINDCHILLDB数据库,性能有一个本质的性能飞越。
因为workitem表中,字段存在BLOB大字段对象,如果走全表扫描需要消耗很多资源
以下实验对比全表扫描和索引扫描成本
SQL> select  * from windadmin.workitem where classnamekeyb4='VR:wt.change2.WTCha
ngeActivity2:29886272';
已选择6行。
已用时间:  00: 00: 00.87
执行计划
----------------------------------------------------------
Plan hash value: 3321654780
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   399K|   615M| 20381   (1)| 00:04:46 |
|*  1 |  TABLE ACCESS FULL| WORKITEM |   399K|   615M| 20381   (1)| 00:04:46 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("CLASSNAMEKEYB4"='VR:wt.change2.WTChangeActivity2:29886272
              ')

统计信息
----------------------------------------------------------
          0  recursive calls
          1  db block gets
      54547  consistent gets
          0  physical reads
          0  redo size
      11478  bytes sent via SQL*Net to client
        590  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
SQL> select /*+ index(workitem WORKITEM$11289258)*/ * from windadmin.workitem wh
ere classnamekeyb4='VR:wt.change2.WTChangeActivity2:29886272';
已选择6行。
已用时间:  00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 2088169885
--------------------------------------------------------------------------------
-----------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (
%CPU)| Time     |
--------------------------------------------------------------------------------
-----------------
|   0 | SELECT STATEMENT            |                   |   399K|   615M| 38221
  (1)| 00:08:56 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WORKITEM          |   399K|   615M| 38221
  (1)| 00:08:56 |
|*  2 |   INDEX RANGE SCAN          | WORKITEM$11289258 |   399K|       |   150
  (1)| 00:00:03 |
--------------------------------------------------------------------------------
-----------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CLASSNAMEKEYB4"='VR:wt.change2.WTChangeActivity2:29886272')

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
      11478  bytes sent via SQL*Net to client
        590  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
Limitations of Histogram on Character Columns Histograms only store the first 32 characters of a character string (5 characters pre 8.1.6 See Bug:598799 ). If histograms are gathered on character columns that have data that is longer than 31 characters and the first 31 characters are identical then column histograms may not reflect the cardinality of these columns correctly as these values will all be treated as if they are identical.
总结:对于超过32个字符的字符型列,超出的那一部分无法在直方图中体现。
简单的一句话,做了很多实验,找了很多资料,记录了相关的实验过程。
经典的索引问题:为什么查询的时候,有索引,而且走索引消耗的时间和资源都要远远的低于全表扫描,可是
ORACLE确傻傻的要走全表扫描?
原因:ORACLE会智能的收集表列上的统计信息和直方图。当它自己收集到的这些信息和实际的误差很大的时候。
由于这些错误的信息,误导了ORACLE选择正确的执行计划,从而出现了以前这个问题。
引深阅读http://www.itpub.net/thread-1781006-1-1.html
SQL>select length('VR:wt.change2.WTChangeActivity2:') from dual;
VR:WT.CHANGE2.WTCHAN
------------------------------
  32
查看表workitem的列classnamekeyb4的值得情况如下。
SQL> select classnamekeyb4 from windadmin.workitem;
 
CLASSNAMEKEYB4
--------------------------------------------------------------------------------
VR:wt.change2.WTChangeActivity2:29886272
VR:wt.change2.WTChangeActivity2:29886272
VR:wt.doc.WTDocument:29891714
VR:wt.doc.WTDocument:29893311
VR:wt.change2.WTChangeActivity2:30130678
VR:wt.change2.WTChangeActivity2:30130678
VR:wt.change2.WTChangeActivity2:29903877
VR:wt.change2.WTChangeOrder2:30130659
VR:wt.change2.WTChangeOrder2:30130659
VR:wt.change2.WTChangeActivity2:29898195
VR:wt.change2.WTChangeActivity2:29886272
VR:wt.doc.WTDocument:29891714
VR:wt.change2.WTChangeActivity2:29903649
VR:wt.change2.WTChangeActivity2:29898195
VR:wt.change2.WTChangeOrder2:30124944
VR:wt.change2.WTChangeActivity2:30124963
VR:wt.change2.WTChangeRequest2:30146744
VR:wt.change2.WTChangeActivity2:29903439
VR:wt.change2.WTChangeActivity2:29903439
VR:wt.change2.WTChangeRequest2:29886134
可以得出,当字段值为VR:wt.change2.WTChangeActivity2:的时候,已经达到了32个字符,那么ORACLE直方图会忽略后面的
不同的值,而错误的认为这些不同的值是同样的值。从而导致了错误的执行计划(有点绕)
所以,我们再做一个实验,采用该列的值为VR:wt.doc.WTDocument:29891714  这个值小于32个字符
以下是实验结果:
SQL> SELECT 'wt.workflow.work.WorkItem', A0.blob$context, A0.completedBy, A0.con
text, A0.description, A0.eventSet, A0.classnamekeyD4, A0.idA3D4, A0.classnamekey
A2ownership, A0.idA3A2ownership, A0.classnamekeyC4, A0.idA3C4, A0.classnamekeyB4
, A0.priority, A0.reassigned, A0.required, A0.role, A0.classnamekeyA4, A0.idA3A4
, A0.status, A0.taskURLPathInfo, TO_CHAR(A0.createStampA2, 'dd mm yyyy hh24:mi:s
s'), A0.markForDeleteA2, TO_CHAR(A0.modifyStampA2, 'dd mm yyyy hh24:mi:ss'), A0.
idA2A2, A0.updateCountA2, TO_CHAR(A0.updateStampA2, 'dd mm yyyy hh24:mi:ss'), A0
.variablePermissionMap FROM WorkItem A0 WHERE ((A0.classnamekeyB4 = 'VR:wt.doc.W
TDocument:29891714' ));
已用时间:00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 2088169885
--------------------------------------------------------------------------------
-----------------
| Id| Operation       | Name    | Rows| Bytes | Cost (
%CPU)| Time |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT    |       | 1 |1613 | 4
(0)| 00:00:01 |
| 1 |TABLE ACCESS BY INDEX ROWID| WORKITEM    | 1 |1613 | 4
(0)| 00:00:01 |
|*2 | INDEX RANGE SCAN    | WORKITEM$11289258 | 1 |   | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access("A0"."CLASSNAMEKEYB4"='VR:wt.doc.WTDocument:29891714')

统计信息
----------------------------------------------------------
    0recursive calls
    0db block gets
    7consistent gets
    0physical reads
    0redo size
   6550bytes sent via SQL*Net to client
  546bytes received via SQL*Net from client
    4SQL*Net roundtrips to/from client
    0sorts (memory)
    0sorts (disk)
    2rows processed
SQL>
这个时候,ORACLE选择了正确的执行计划了。
修改程序代码,在查询代码中加入优化器干扰器hint  
清除该列上面的直方图 
方法一:
SQL> dbms_stats.gather_table_stats(ownname => 'windadmin',tabname => 'WORKITEM',estimate_percent => null ,method_opt => 'for columns SIZE 1 CLASSNAMEKEYB4',cascade => true);
方法二:
执行
exec dbms_stats.delete_column_stats('windadmin','WORKITEM','CLASSNAMEKEYB4');
锁定
exec dbms_stats.lock_table_stats('windadmin','CLASSNAMEKEYB4');
解决办法就是删除了index列CLASSNAMEKEYB4上的优化器统计信息,单独对index搜集了一下优化器统计信息,(
不能通过搜集表或者让数据库自动搜集,他们搜集之后index和index列filename上的优化器统计信息
都不对),最后把这张表上的优化器统计信息暂时锁定了。这样就可以走index了。这个表上的数据变化不是太频繁,
只能定期手动这样处理了。
3. sql profile固定计划
以上三种方法,可选一种
优点与缺点对比:
第一种方法:需要软件应用组的人,找到该语句的查询页面代码,然后在查询代码中加入hint 注释。固定执行计划
            简单,但是需要他人来做
第二种方法:运维组自己就可以实现,无求于人。 只是删除该表列上的直方图,对其他无影响。简单方便。
第三种方法:学习中
个人推荐第二种方法,清除该列上面的直方图。)
测试环境,已经测试通过。

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

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

注册时间:2012-07-04

  • 博文量
    42
  • 访问量
    90799