oracle数据库SQL语句的一种优化手段 gather_plan_statistics hint

1、首先sqlplus登陆当前用户

2、执行  运行 sql语句 ，在语句中加入     /*+ gather_plan_statistics */

例子： 执行  sql 语句  select   /*+ gather_plan_statistics */  count(1) from (select count(t.poor_id) from TBL_FPXM_POOR t where t.state!='0' and t.poor_azc005 like '6201%' group by t.poor_id);

得到结果  ：

COUNT(1)
----------

38946

3、执行   查看计划    select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS'));

得到结果：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  c67u7d08hpjd9, child number 0
-------------------------------------
select /*+ gather_plan_statistics */count(1) from (select
count(t.poor_id) from TBL_FPXM_POOR t where t.state!='0' and
t.poor_azc005 like '6201%' group by t.poor_id)


Plan hash value: 2470818225


------------------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id  | Operation                                         | Name                                                        | Starts | E-Rows |A-Rows |   A-Time       | Buffers |  OMem |  1Mem  |  O/1/M   |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                                                                     |      1   |                |     1         |00:00:03.29 |     127K|               |                |               |
|   1 |  SORT AGGREGATE                     |                                                                     |      1   |      1        |     1        |00:00:03.29 |     127K|                |                |              |
|   2 |   VIEW                                               |                                                                     |      1   |    286K   | 38946   |00:00:03.29 |     127K|                |               |               |
|   3 |    HASH GROUP BY                      |                                                                      |      1   |    286K   | 38946   |00:00:03.29 |     127K|    33M     |  5413K|     1/0/0|
|   4 |     PARTITION LIST ALL                |                                                                      |      1   |    332K   |   370K   |00:00:00.21 |     127K|                 |              |              |
|*  5 |      INDEX FAST FULL SCAN      | INDEX_FPXM_POOR_AREACODE    |    11   |    332K   |   370K    |00:00:00.26 |     127K|                 |              |              |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

        5 - filter(("T"."POOR_AZC005" LIKE '6201%' AND "T"."STATE"<>'0'))

4、相关解释

这个语句总的逻辑IO是:127K    (buffers 是实际的逻辑IO数量,这里是累计值,包括子操作的值)
               starts 是对应的动作执行的次数
              E-ROWS 是优化器估算这一步返回的数据行数
               A-Rows  是这一步实际返回的数据行数

1、可以看出本条语句的第五项查询中 starts  对应的查询出现了11次这是由于有在这个表中创建了11个分区表  但是创建的索引是本地索引本地(local index)索引 而不是全局索引(global index)所以扫描索引的时候会扫描11次，优化的时候可以相应的修改为全局索引让扫描次数减少为1次。

2、创建索引的时候没有更好的筛选更小的数量，方法是修改相应的索引条件。