Oracle11g 多列收集统计信息--直方图

  最近发现有一条SQL要执行几百秒，是执行计划走错了，为什么走错呢？来看一下：

SELECT *

  FROM (SELECT *
          FROM (SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM
                  FROM (SELECT DISTINCT PRO.PROJECT_ID,
                                        PRO.PROJECT_NAME,
                                        PRO.PROJECT_CODE,
                                        PRO.PROJECT_CHARGER,
                                        PRO.EXE_DEPT,
                                        PRO.EXE_DEPT_CODE,
                                        PRO.CONS_DEPT,
                                        PRO.CONS_DEPT_CODE,
                                        PRO.PROJECT_ACCESS_TYPE,
                                        PROJECT_AMOUNT,
                                        PRO.PROJECT_TYPE_CODE,
                                        VER.SANCTIFIED_AMOUNT,
                                        VER.BUDGET_AMOUNT,
                                        PRO.APPLY_YEAR,
                                        PRO.PROJECT_PROPERTY,
                                        PRO.BUILD_TYPE,
                                        PRO.PROJECT_TYPE,
                                        PRO.PLAN_START_DATE,
                                        PRO.TECH_PROJECT_CODE,
                                        PRO.PLAN_COMPLETE_DATE,
                                        PRO.LOCAL_CODE,
                                        PRO.DATA_AREA           DATA_AREA,
                                        PRO.PROJECT_KIND,
                                        PRO.PROJECT_ATTRIBUTE,
                                        PRO.BELONGS_CATEGORY BELONGS_CATEGORY,
                                        PRO.FUND_SOURCE         FUND_SOURCE,
                                        PRO.PROJECT_CHARGER_ID  PROJECT_CHARGER_ID,
                                        V_BGG.TODO_TASK_ID     AS TODO_TASK_ID,
                                        VER.FBS_VERSION_ID,
                                        NULL                    AS DONE_TASK_ID,
                                        VER.LEGAL_MARK,
                                        VER.AUDIT_STATUS,
                                        VER.PROCESS_INSTANCE_ID,
                                        V_BGG.READ_FLAG,
                                        VER.FBS_CLASS,
                                        V_BGG.BACK_FLAG AS BACK_FLAG
                          FROM GG_PROJECT         PRO,
                               GG_FBS_VERSION     VER,
                               V_GG_BGG_TODOTASK V_BGG
                         WHERE PRO.PROJECT_ID = VER.PROJECT_ID
                           AND (VER.VERSION_ID = '1' OR VER.FBS_CLASS = '2')
                           AND V_BGG.MAIN_PROCESS_INS_ID =
                               VER.PROCESS_INSTANCE_ID
                           AND V_BGG.VERSION = 8
                           AND VER.AUDIT_STATUS != '3'
                           AND V_BGG.TRANS_ACTOR_ID = 'DFA179F838A14CAFB96D065948F46D86'
                           AND V_BGG.CUR_NODE_ID = 'Task_5'
                           AND VER.PROJECT_TYPE_CODE = PRO.PROJECT_TYPE_CODE
                           AND PRO.PROJECT_STATUS < 40
                           AND PRO.PROJECT_TYPE_CODE = '4') INNER_TABLE)
         WHERE OUTER_TABLE_ROWNUM <= 10) OUTER_TABLE
 WHERE OUTER_TABLE_ROWNUM > 0 ;
 ---------------------------------------------------------------------------------------------------------------
| Id   | Operation                               | Name                    | Rows | Bytes   | Cost | Time     |
---------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                        |                         |    1 |    1071 | 5101 | 00:01:02 |
|  * 1 |   VIEW                                  |                         |    1 |    1071 | 5101 | 00:01:02 |
|    2 |    COUNT                                |                         |      |         |      |          |
|    3 |     NESTED LOOPS                        |                         |    1 |     580 | 5101 | 00:01:02 |
|    4 |      NESTED LOOPS                       |                         |  256 |     580 | 5101 | 00:01:02 |
|    5 |       MERGE JOIN CARTESIAN             |                          |   32 |   16704 | 5012 | 00:01:01 |
|  * 6 |        TABLE ACCESS BY INDEX ROWID      | GG_RU_TODO_TASK_GG      |   1  |     121 |    5 | 00:00:01 |
|  * 7 |         INDEX RANGE SCAN                | TODO_TASK_GG_I_2        |    3 |         |    3 | 00:00:01 |
|    8 |        BUFFER SORT                      |                         | 9223 | 3698423 | 5007 | 00:01:01 |
|    9 |         PARTITION LIST SINGLE           |                         | 9223 | 3698423 | 5007 | 00:01:01 |
| * 10 |          TABLE ACCESS FULL              | GG_PROJECT              | 9223 | 3698423 | 5007 | 00:01:01 |
| * 11 |       INDEX RANGE SCAN                  | FBS_PROJECT_ID_TEMP1231 |    8 |         |    2 | 00:00:01 |
| * 12 |      TABLE ACCESS BY GLOBAL INDEX ROWID | GG_FBS_VERSION          |    1 |      58 |    4 | 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("from$_subquery$_002"."OUTER_TABLE_ROWNUM">0 AND "OUTER_TABLE_ROWNUM"<=10)
* 6 - filter("BRTT"."VERSION"=8 AND "BRTT"."CUR_NODE_ID"='Task_5')
* 7 - access("BRTT"."TRANS_ACTOR_ID"='F2D29C42D4904336B0CDEBEC6BC0B68A')
* 10 - filter(TO_NUMBER("PRO"."PROJECT_STATUS")<40)
* 11 - access("PRO"."PROJECT_ID"="PROJECT_ID")
* 12 - filter("VER"."PROCESS_INSTANCE_ID" IS NOT NULL AND "VER"."AUDIT_STATUS"<>3 AND ("VER"."VERSION_ID"='1' OR "VER"."FBS_CLASS"=2) AND "VER"."PROJECT_TYPE_CODE"='4' AND

  "BRTT"."MAIN_PROCESS_INS_ID"="VER"."PROCESS_INSTANCE_ID")

    走了笛卡尔积，执行计划第6步评估rows评估的不准，按照给出的条件GG_RU_TODO_TASK_GG查出来9条记录，按照笛卡尔积的算法，GG_PROJECT扫描了9次，所以很慢。因为是三个字段的过滤，这种情况确实有问题，因为直方图都是单个字段的。想着把这三个字段联合建一个直方图，测试发现执行计划走对了，执行时间在1s内。
  
exec dbms_stats.gather_table_stats(user,'GG_RU_TODO_TASK_GG',cascade => true,degree => 4,method_opt  => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (TRANS_ACTOR_ID,CUR_NODE_ID,version)',no_invalidate=>FALSE);

--------------------------------------------------------------------------------------------------------------
| Id   | Operation                               | Name                  | Rows  | Bytes   | Cost | Time     |
--------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                        |                       |     1 |    1071 | 6096 | 00:01:14 |
|  * 1 |   VIEW                                  |                       |     1 |    1071 | 6096 | 00:01:14 |
|    2 |    COUNT                                |                       |       |         |      |          |
|    3 |     NESTED LOOPS                        |                       |     1 |     580 | 6096 | 00:01:14 |
|    4 |      NESTED LOOPS                       |                       |     1 |     580 | 6096 | 00:01:14 |
|  * 5 |       HASH JOIN                         |                       |     1 |     179 | 6094 | 00:01:14 |
|  * 6 |        TABLE ACCESS BY INDEX ROWID      | BGG_RU_TODO_TASK_GG   |     3 |     363 |    5 | 00:00:01 |
|  * 7 |         INDEX RANGE SCAN                | TODO_TASK_GG_I_2      |     3 |         |    3 | 00:00:01 |
|    8 |        PARTITION LIST SINGLE            |                       | 27586 | 1599988 | 6089 | 00:01:14 |
|    9 |         PARTITION LIST ALL              |                       | 27586 | 1599988 | 6089 | 00:01:14 |
| * 10 |          TABLE ACCESS FULL              | GG_FBS_VERSION        | 27586 | 1599988 | 6089 | 00:01:14 |
| * 11 |       INDEX UNIQUE SCAN                 | PK_PROJECT_ID_T       |     1 |         |    1 | 00:00:01 |
| * 12 |      TABLE ACCESS BY GLOBAL INDEX ROWID | GG_PROJECT            |     1 |     401 |    2 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("from$_subquery$_002"."OUTER_TABLE_ROWNUM">0 AND "OUTER_TABLE_ROWNUM"<=10)
* 5 - access("BRTT"."MAIN_PROCESS_INS_ID"="VER"."PROCESS_INSTANCE_ID")
* 6 - filter("BRTT"."VERSION"=8 AND "BRTT"."CUR_NODE_ID"='Task_1')
* 7 - access("BRTT"."TRANS_ACTOR_ID"='1F624F50BCAD4231B165BA246E582243')
* 10 - filter("VER"."PROCESS_INSTANCE_ID" IS NOT NULL AND "VER"."AUDIT_STATUS"<>3 AND ("VER"."VERSION_ID"='1' OR "VER"."FBS_CLASS"=2))
* 11 - access("PRO"."PROJECT_ID"="VER"."PROJECT_ID")
* 12 - filter(TO_NUMBER("PRO"."PROJECT_STATUS")<40 AND "PRO"."PROJECT_TYPE_CODE"='4')