PLSQL_以前正常程式因统计信息过久突然出现性能问题（案例）

2014-11-15 BaoXinjian

一、摘要

二、解决方式

select sid, sql_id from v$session where sid = 1001

select b.begin_interval_time, a.disk_reads_total, a.buffer_gets_total,a.buffer_gets_delta, a.rows_processed_total, a.plan_hash_value
  from dba_hist_sqlstat a, dba_hist_snapshot b  
 where a.snap_id = b.snap_id 
   and sql_id = ‘466bpq7055f4c‘ 
order by 1 desc;  

select* from v$sql_plan

select name, last_analized from dba_tables

exec dbms_stats.gather_table_stats(ownname=>‘SCOT‘,tabname=>‘GAVIN‘,estimate_percent=>3,degree=>32,granularity=>‘GLOBAL‘,cascade=>FALSE,no_invalidate=>FALSE);

203840548945首先创建一张测试表

 

SQL> create table t1 as select 1 id,object_name,object_type from sys.dba_objects;

 

Table created.

 

SQL> select count(*) total,count(distinct id) column_id_valnum from t1;

 

     TOTAL COLUMN_ID_VALNUM

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

     50345                1

 

SQL> create index idx_t1_id on t1(id);

 

Index created.

 

一共有5万多条记录，但是id列只有一种值‘1’。

下面，手工对这张表的统计信息进行收集。

SQL> exec dbms_stats.gather_table_stats(user,‘T1‘);

 

PL/SQL procedure successfully completed.

 

现在，我们执行查询，看看它的执行计划是怎样的。

 

SQL> set autotrace trace exp stat

SQL> select * from t1 where id=1;

 

50345 rows selected.

 

 

Execution Plan

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

Plan hash value: 3617692013

 

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

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

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

|   0 | SELECT STATEMENT  |      | 50345 |  1720K|    69   (3)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T1   | 50345 |  1720K|    69   (3)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("ID"=1)

 

 

Statistics

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

          1  recursive calls

          0  db block gets

       3630  consistent gets

          0  physical reads

          0  redo size

    1817080  bytes sent via SQL*Net to client

      37301  bytes received via SQL*Net from client

       3358  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      50345  rows processed

 

生成的执行计划非常准确。及时在表上创建索引，但是由于id的值都是‘1’，所以CBO还是采取全表扫面的执行计划。

下面我们要修改表，将50340条记录的id改为‘9999’，并且不收集信息情况下，重新执行查询，看看会是什么情况。

SQL> update t1 set id=9999 where rownum<50341;

 

50340 rows updated.

 

SQL> commit;

 

Commit complete.

SQL> select * from t1 where id=1;

 

 

Execution Plan

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

Plan hash value: 3617692013

 

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

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

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

|   0 | SELECT STATEMENT  |      | 50345 |  1720K|    69   (3)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T1   | 50345 |  1720K|    69   (3)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("ID"=1)

 

 

Statistics

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

          0  recursive calls

          0  db block gets

        291  consistent gets

          0  physical reads

          0  redo size

        676  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          5  rows processed

 

这个执行计划与我们的想法相左，修改完之后，为‘1’的记录应该是5条，使用索引应该比全表扫面要优，但是现在的执行计划确实全表扫描，就是因为统计信息过旧，造成执行计划不准确。

处理这个问题可以手工收集一次统计信息。

 

SQL> exec dbms_stats.gather_table_stats(user,‘T1‘);

 

PL/SQL procedure successfully completed.

 

SQL> select * from t1 where id=1;

 

 

Execution Plan

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

Plan hash value: 190799060

 

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

---------

 

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T

ime     |

 

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

---------

 

|   0 | SELECT STATEMENT            |           |    18 |   648 |     2   (0)| 0

0:00:01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |    18 |   648 |     2   (0)| 0

0:00:01 |

 

|*  2 |   INDEX RANGE SCAN          | IDX_T1_ID |    18 |       |     1   (0)| 0

0:00:01 |

 

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

---------

 

 

Predicate Information (identified by operation id):

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

 

   2 - access("ID"=1)

 

 

Statistics

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

          0  recursive calls

          0  db block gets

        291  consistent gets

          0  physical reads

          0  redo size

        676  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          5  rows processed

 Thanks and Regards