当前位置:数据库 > SQLServer >>

查询计划错误的原因分析

undefined undefined   SELECT *

   FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT

   WHERE BKGSHMTRESULT.BKG_CFM_ID = :B1

   AND BKGSHMTRESULT.COMP_ID = :B2
  从V$SESSION_LONGOPS看,它正在对表CR_BKG_INTMD_SHMT_PARTITION做FULL TABLE SCAN。而表CR_BKG_INTMD_SHMT_PARTITION是一张非常大的分区表,是我们之前做的优化建立的分区表(该案例我有在《11g新特性 ——更加灵活的分区策略》中提到,Partition Key是COMP_ID,分区策略是每个VIP用户一个分区,所有非VIP用户在DEFAULT分区)。

  这条语句的查询条件很简单,且在(BKG_CFM_ID,COMP_ID)上有建一个Global Index。通过直接对其解析查询计划,发现它能正确命中索引:

undefined undefined  SQL > EXPLAIN PLAN FOR

   2 SELECT *

   3 FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT

   4 WHERE BKGSHMTRESULT.BKG_CFM_ID = :B1

   5 AND BKGSHMTRESULT.COMP_ID = :B2;

  Explained.

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

  PLAN_TABLE_OUTPUT

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

   Plan hash value: 772272200

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

   | Id | Operation | Name | Rows | Bytes | Cost ( % CPU) | Time | Pstart | Pstop |

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

   | 0 | SELECT STATEMENT | | 1 | 880 | 5 ( 0 ) | 00 : 00 : 01 | | |

   | 1 | TABLE ACCESS BY GLOBAL INDEX ROWID | CR_BKG_INTMD_SHMT_PARTITION | 1 | 880 | 5 ( 0 ) | 00 : 00 : 01 | ROWID | ROWID |

   |* 2 | INDEX RANGE SCAN | CR_BKG_INTMD_PARTITION_IDX03 | 1 | | 4 ( 0 ) | 00 : 00 : 01 | | |

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

  Predicate Information (identified by operation id):

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

   2 - access("BKGSHMTRESULT"."BKG_CFM_ID" = TO_NUMBER(:B1) AND "BKGSHMTRESULT"."COMP_ID" = :B2)undefined undefined SQL > select lpad( ' ' , 2 * ( level - 1 )) || operation || ' ' ||

   2 decode(id, 0 , ' Cost = ' || position) "OPERATION",

   3 options,

   4 object_name

   5 from v$sql_plan

   6 start with (sql_id = ' f0mwuqfxxmtmf ' and hash_value = 3151619694 and id = 0 )

   7 connect by prior id = parent_id

   8 and prior sql_id = sql_id

   9 and prior hash_value = hash_value

   10 order by id, position;

  OPERATION OPTIONS OBJECT_NAME

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

   SELECT STATEMENT Cost = 265

  PARTITION LIST SINGLE

   TABLE ACCESS FULL CR_BKG_INTMD_SHMT_PARTITION
  这一现象通常是由于绑定变量窥视(Bind Variable Peeking)造成的:Peeking的变量值比较特殊,造成计算出的全表扫描代价低于索引扫描代价。为了确认问题,我们找到解析查询计划所“窥视”到的数据:

undefined undefined SQL > SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR( ' f0mwuqfxxmtmf ' , 0 , ' ADVANCED ' ));

  PLAN_TABLE_OUTPUT

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

  SQL_ID f0mwuqfxxmtmf, child number 0

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

   SELECT * FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT WHERE BKGSHMTRESULT.BKG_CFM_ID = :V_BKG_CFM_ID

   AND BKGSHMTRESULT.COMP_ID = :V_COMP_ID

   Plan hash value: 3035855418

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

   | Id | Operation | Name | Rows | Bytes | Cost ( % CPU) | Time | Pstart | Pstop |

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

   | 0 | SELECT STATEMENT | | | | 265 ( 100 ) | | | |

   | 1 | PARTITION LIST SINGLE | | 1 | 756 | 265 ( 1 ) | 00 : 00 : 04 | KEY | KEY |

   |* 2 | TABLE ACCESS FULL | CR_BKG_INTMD_SHMT_PARTITION | 1 | 756 | 265 ( 1 ) | 00 : 00 : 04 | KEY | KEY |

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

  Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$ 1

   2 - SEL$ 1 / BKGSHMTRESULT @SEL $ 1

  Outline Data

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

   /* +

  BEGIN_OUTLINE_DATA

  IGNORE_OPTIM_EMBEDDED_HINTS

  OPTIMIZER_FEATURES_ENABLE('10.2.0.3')

  OPT_PARAM('_complex_view_merging' 'false')

  ALL_ROWS

  OUTLINE_LEAF(@"SEL$1")

  FULL(@"SEL$1" "BKGSHMTRESULT"@"SEL$1")

  END_OUTLINE_DATA

   */

  Peeked Binds (identified by position):

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

   1 - :V_BKG_CFM_ID ( NUMBER ): 592533

   2 - :V_COMP_ID ( VARCHAR2 ( 30 ), CSID = 873 ): ' BANDHK270600815 '可以看到,两个变量的值分别为V_BKG_CFM_ID : 592533、V_COMP_ID : 'BANDHK270600815'。使用这2个值,再重新解析查询计划,果然是全表扫描:

undefined undefined SQL > explain plan for

   2 SELECT *

   3 FROM CR_BKG_INTMD_SHMT_PARTITION BKGSHMTRESULT

   4 WHERE BKGSHMTRESULT.BKG_CFM_ID = 592533

   5 AND BKGSHMTRESULT.COMP_ID = ' BANDHK270600815 ' ;

  Explained.

  SQL > SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY());

  PLAN_TABLE_OUTPUT

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

   Plan hash value: 554208192

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

   | Id | Operation | Name | Rows | Bytes | Cost ( % CPU) | Time | Pstart | Pstop |

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

   | 0 | SELECT STATEMENT | | 1 | 756 | 265 ( 1 ) | 00 : 00 : 04 | | |

   | 1 | PARTITION LIST SINGLE | | 1 | 756 | 265 ( 1 ) | 00 : 00 : 04 | KEY | KEY |

   |* 2 | TABLE ACCESS FULL | CR_BKG_INTMD_SHMT_PARTITION | 1 | 756 | 265 ( 1 ) | 00 : 00 : 04 | 14 | 14 |

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

  Predicate Information (identified by operation id):

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

   2 - filter("BKGSHMTRESULT"."BKG_CFM_ID" = 592536 )
  注意到在查询条件中存在Partition Key:COMP_ID,因此查询计划中存Partition List Single,仅对所在分区(14)查

询。,这里的Full Table Scan实际上是对一个分区的Full Scan,而'BANDHK270600815'正是一个VIP用户。我们再看该分区上的statistics数据: 

undefined undefined SQL > select num_rows, blocks from dba_tab_statistics where table_name = ' CR_BKG_INTMD_SHMT_PARTITION ' and owner = ' CS2_PARTY_OWNER ' and partition_name = ' P_COMP_BANDHK270600815 ' ;

  NUM_ROWS BLOCKS

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

   18 8
  分区上的数据非常少,因此这个Full Scan的Cost不高,解析出的查询计划为Full Table Scan,当数据落入DEFAULT分区(最大分区),其查询计划仍为Full Scan,从而导致了性能问题!继续查询,发现还存在少数几个分区的数据也非常少。这一结果和当初我们做POC时的有出入:在POC中,所有VIP用户的数据都在10K以上,但生产环境上却出现数据量极少的VIP用户。这一问题恐怕需要从开发那边BA/SA找答案了。作为DBA,我们的当前的任务就是如何避免再次发生由此引发的性能问题。

  由于我们的系统是工作日(周一到周五)运行,每周系统都会重启。因此,在周一很多语句都会被硬解析。这样的话,很难避免在硬解析时,窥视的数据再次落入这些小分区内。要避免再次造成性能问题,可以考虑以下方法:

  相关语句上加HINT,强制使用索引。但是这样的修改涉及面太大,且如果将来Schema发生变化,代码维护更新困难;

  用Stored Outline为语句固定查询计划。其缺点和第一点差不多;

  禁用Bind Variable Peeking。因为我们的系统会每周重启,如果在db level禁用,风险较大,所以我们考虑在session level禁用。因为该模块的代码都是通过Package调用的,所以修改的代码量非常少:在入口函数上加上以下语句。  

undefined undefined execute immediate ' alter session set "_OPTIM_PEEK_USER_BINDS" = false ' ;
  后记

 

补充:数据库,Mssql 
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,