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

r囧r小猫 2022-07-13 10:52 231阅读 0赞

最近发现有一条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')