[转帖]一个查询计划错误的原因分析_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 3049 | 回复: 0   主题: [转帖]一个查询计划错误的原因分析        下一篇 
derek
注册用户
等级:中校
经验:1550
发帖:209
精华:0
注册:2011-7-21
状态:离线
发送短消息息给derek 加好友    发送短消息息给derek 发消息
发表于: IP:您无权察看 2011-8-3 15:51:58 | [全部帖] [楼主帖] 楼主

【IT168 评论】周一收到生成支持人员的报告,系统上一个作业启动后很长时间没有完成,其执行时间远远大于上周的正常执行时间。接到报告后,首先检查了系统,不存在锁队列的问题。然后查询V$SESSION_LONGOPS,立即发现下面的语句正在进行长操作:

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。通过直接对其解析查询计划,发现它能正确命中索引:

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)


  但是,通过SQL_ID查询,实际的查询计划却是全表扫描:

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的变���值比较特殊,造成计算出的全表扫描代价低于索引扫描代价。为了确认问题,我们找到解析查询计划所“窥视”到的数据:

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
-------------
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个值,再重新解析查询计划,果然是全表扫描:

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数据: 

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调用的,所以修改的代码量非常少:在入口函数上加上以下语句。  

execute immediate 'alter session set "_OPTIM_PEEK_USER_BINDS" = false';


  后记

    此案例涉及两个问题值得注意:

    分区的平衡问题。如果分区之间的数据量存在很大差异,在绑定变量窥视被启用(默认)的情况下,硬解析出来的查询计划在不同分区上的性能差异可能非常大;

    绑定变量窥视其目的主要是帮助CBO下更加精确的计算出查询计划代价。但是,因为这依赖于被“窥视”的变量值,因而也为查询计划带来了不稳定性。数据的不平衡、分区的不平衡都可能会因为这种不稳定性而导致性能风险。




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论