AWR实战分析之-cursor: pin S wait on X

转自 http://blog.sina.com.cn/s/blog_61cd89f60102ecsv.html

早上开发的同事反馈ORACLE数据库中无法进行数据查询，随即把写的SQL也发了过来，很简单一个SQL，内容如下：select count(FO_SNO) from EPMFRAMEWORK.FO_PROJDATA_T 执行的十分钟没反回任何数据，以前从来没遇到过这种问题，打开TOAD取了一下AWR报告，明显发现异常

 

   

服务器CPU个数是32个，2637/60=43.95 > 32 说明此时服务器负载相对较高，我们再看下图的TOP 5

很显然，cursor :pin S wait on X 这个事件等待比较严重，跟据官方定位此问题的原因有三种

1.较高的硬解析

2.高版本问题

3.Bug

第二种情况可以跟据SQL ordered by Version Count排除，Bug也可以排除，因为其它的都可以查询，就一条SQL没有数据返回，说明此表涉及到的SQL存在较高的硬解析，存在较高硬解析一般可分为两种情况，一种是语句没有使用绑定变量，第二种情况是统计信息过时，因为系统是一个月15号集中发版，现在才2号，程序上没有改动，所以排除没有使用绑定变量，那么基本上可以确定统计信息过时导致SQL硬解析过程中hung住，于时针对此SQL做了一个STA，结果如下：

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : TEST_sql_tuning_task
Tuning Task Owner                 : SYSTEM
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 08/02/2013 11:39:49
Completed at                      : 08/02/2013 11:40:21
Number of Statistic Findings      : 2

-------------------------------------------------------------------------------
Schema Name: EPMFRAMEWORK
SQL ID     : ftwn8up44aj26
SQL Text   : select count(FO_SNO) from EPMFRAMEWORK.FO_PROJDATA_T

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  尚未分析索引 "EPMFRAMEWORK"."PK_FO_PROJDATA_T"。

  Recommendation
  --------------
  - 考虑收集此索引的优化程序统计信息。
    execute dbms_stats.gather_index_stats(ownname => 'EPMFRAMEWORK', indname
            => 'PK_FO_PROJDATA_T', estimate_percent =>
            DBMS_STATS.AUTO_SAMPLE_SIZE);

  Rationale
  ---------
    为了选择好的执行计划, 优化程序需要索引的最新统计信息。

2- Statistics Finding
---------------------
  尚未分析表 "EPMFRAMEWORK"."FO_PROJDATA_T"。

  Recommendation
  --------------
  - 考虑收集此表的优化程序统计信息。
    execute dbms_stats.gather_table_stats(ownname => 'EPMFRAMEWORK', tabname
            => 'FO_PROJDATA_T', estimate_percent =>
            DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
            AUTO');

  Rationale
  ---------
    为了选择好的执行计划, 优化程序需要此表的最新统计信息。

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 4260773032

--------------------------------------------
| Id  | Operation          | Name          |
--------------------------------------------
|   0 | SELECT STATEMENT   |               |
|   1 |  SORT AGGREGATE    |               |
|   2 |   TABLE ACCESS FULL| FO_PROJDATA_T |

从STA给出的建议来看，这个表和表的主键索引的统计信息失效，所以硬解析过程中Hung住

直接采用STA给出的建议，执行统计信息收集

begin

execute dbms_stats.gather_table_stats(ownname => 'EPMFRAMEWORK',

                                      tabname => 'FO_PROJDATA_T',

                             estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

                                   method_opt => 'FOR ALL COLUMNS SIZE AUTO');

end;

业务恢复正常：