根据执行计划优化sql语句

优化前：表连接使用merge

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

SQL> alter session set statistics_level=all;   Session altered.   SQL> select e.sal,d.dname from (select deptno,sum(sal) sal from emp group by deptno) e,dept d where d.deptno=e.deptno;          SAL DNAME ---------- --------------       8750 ACCOUNTING      10875 RESEARCH       9400 SALES   SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats +alias +outline'));   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID  g2kzsydh3kmmq, child number 0 ------------------------------------- select e.sal,d.dname from (select deptno,sum(sal) sal from emp group by deptno) e,dept d where d.deptno=e.deptno   Plan hash value: 2992795152   ----------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ----------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |      2 |        |      6 |00:00:00.01 |      20 |       |       |          | |   1 |  MERGE JOIN                  |         |      2 |      3 |      6 |00:00:00.01 |      20 |       |       |          | |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      2 |      4 |      8 |00:00:00.01 |       8 |       |       |          | |   3 |    INDEX FULL SCAN           | PK_DEPT |      2 |      4 |      8 |00:00:00.01 |       4 |       |       |          | |*  4 |   SORT JOIN                  |         |      8 |      3 |      6 |00:00:00.01 |      12 |  2048 |  2048 |     2/0/0| |   5 |    VIEW                      |         |      2 |      3 |      6 |00:00:00.01 |      12 |       |       |          | |   6 |     HASH GROUP BY            |         |      2 |      3 |      6 |00:00:00.01 |      12 |  1214K|  1214K|     2/0/0| |   7 |      TABLE ACCESS FULL       | EMP     |      2 |     14 |     28 |00:00:00.01 |      12 |       |       |          | -----------------------------------------------------------------------------------------------------------------------------   Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------      1 - SEL$1    2 - SEL$1 / D@SEL$1    3 - SEL$1 / D@SEL$1    5 - SEL$2 / E@SEL$1    6 - SEL$2    7 - SEL$2 / EMP@SEL$2   Outline Data -------------     /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$2")       OUTLINE_LEAF(@"SEL$1")       INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))       NO_ACCESS(@"SEL$1" "E"@"SEL$1")       LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")       USE_MERGE(@"SEL$1" "E"@"SEL$1")       FULL(@"SEL$2" "EMP"@"SEL$2")       USE_HASH_AGGREGATION(@"SEL$2")       END_OUTLINE_DATA   */   Predicate Information (identified by operation id): ---------------------------------------------------      4 - access("D"."DEPTNO"="E"."DEPTNO")        filter("D"."DEPTNO"="E"."DEPTNO")     56 rows selected.   SQL>

 

将上面的执行计划中的merge改为hash连接，使用hint再次执行：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93

SQL> select   /*+   2        BEGIN_OUTLINE_DATA   3        IGNORE_OPTIM_EMBEDDED_HINTS   4        OPTIMIZER_FEATURES_ENABLE('11.2.0.4')   5        DB_VERSION('11.2.0.4')   6        ALL_ROWS   7        OUTLINE_LEAF(@"SEL$2")   8        OUTLINE_LEAF(@"SEL$1")   9        INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))  10        NO_ACCESS(@"SEL$1" "E"@"SEL$1")  11        LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")  12        <strong>USE_HASH</strong>(@"SEL$1" "E"@"SEL$1")  13        FULL(@"SEL$2" "EMP"@"SEL$2")  14        USE_HASH_AGGREGATION(@"SEL$2")  15        END_OUTLINE_DATA  16    */ e.sal,d.dname from (select deptno,sum(sal) sal from emp group by deptno) e,dept d where d.deptno=e.deptno;          SAL DNAME ---------- --------------       9400 SALES      10875 RESEARCH       8750 ACCOUNTING   SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats +alias +outline'));   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID  5sns096pbcwj8, child number 0 ------------------------------------- select   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$2")  OUTLINE_LEAF(@"SEL$1")       INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))       NO_ACCESS(@"SEL$1" "E"@"SEL$1") LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")       USE_HASH(@"SEL$1" "E"@"SEL$1")       FULL(@"SEL$2" "EMP"@"SEL$2") USE_HASH_AGGREGATION(@"SEL$2")       END_OUTLINE_DATA   */ e.sal,d.dname from (select deptno,sum(sal) sal from emp group by deptno) e,dept d where d.deptno=e.deptno   Plan hash value: 208030399   ----------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ----------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |      1 |        |      3 |00:00:00.01 |       8 |       |       |          | |*  1 |  HASH JOIN                   |         |      1 |      3 |      3 |00:00:00.01 |       8 |  1599K|  1599K|     1/0/0| |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      4 |      4 |00:00:00.01 |       2 |       |       |          | |   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       1 |       |       |          | |   4 |   VIEW                       |         |      1 |      3 |      3 |00:00:00.01 |       6 |       |       |          | |   5 |    HASH GROUP BY             |         |      1 |      3 |      3 |00:00:00.01 |       6 |  1214K|  1214K|     1/0/0| |   6 |     TABLE ACCESS FULL        | EMP     |      1 |     14 |     14 |00:00:00.01 |       6 |       |       |          | -----------------------------------------------------------------------------------------------------------------------------   Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------      1 - SEL$1    2 - SEL$1 / D@SEL$1    3 - SEL$1 / D@SEL$1    4 - SEL$2 / E@SEL$1    5 - SEL$2    6 - SEL$2 / EMP@SEL$2   Outline Data -------------     /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$2")       OUTLINE_LEAF(@"SEL$1")       INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))       NO_ACCESS(@"SEL$1" "E"@"SEL$1")       LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")       USE_HASH(@"SEL$1" "E"@"SEL$1")       FULL(@"SEL$2" "EMP"@"SEL$2")       USE_HASH_AGGREGATION(@"SEL$2")       END_OUTLINE_DATA   */   Predicate Information (identified by operation id): ---------------------------------------------------      1 - access("D"."DEPTNO"="E"."DEPTNO")     62 rows selected.   SQL>

 针对该sql，在创建新的profile。