[20191112]SQL Tuning by adding column alias (2).txt

[20191112]SQL Tuning by adding column alias (2).txt

http://raajeshwaran.blogspot.com/2019/05/sql-tuning-by-adding-column-alias.html

SQL Tuning is not about adding an index to the table or adding hints or rewriting the query, it is all about
understanding how oracle works and most importantly how the CBO makes use of the table and column level statistics,
histograms, constraints, datatypes, system statistics and other optimizer transformation applied to build an efficient
plan.

SQL最佳化不是關於將索引新增到表或新增提示或重寫查詢，它都是關於瞭解Oracle如何工作，最重要的是CBO如何利用表和列級統計資訊，
應用於構建高效的直方圖、約束、資料型別、系統統計和其他最佳化器轉換計劃。

The below example started from a real project where we are moving an application database running on 11.2.0.4 in HPUX
platform to a database 11.2.0.4 running on Exadata platform.

下面的示例從一個真正的專案開始，我們正在移動一個在HPUX上執行在11.2.0.4上的應用程式資料庫在Exadata平臺上執行的資料庫
11.2.0.4的平臺。

After migration, we found that a delete statement was running for more than 9hours in Exadata that got completed in few
secs in non-exadata platform.

遷移後，我們發現一個DELETE語句在Exdata中執行了超過9個小時，在非ExadataPlatform中幾秒內完成了這些工作。

--//自己重複測試,btw我的測試在非exadata環境也很慢.

1.環境:
--//非exadata環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t as select object_id c1, owner c2, object_type c3 ,a.* from all_objects a where rownum <= 10000;
insert into t select * from t where rownum <=10;
commit ;
--//分析表略.

2.測試:
SCOTT@book> alter session set statistics_level=all ;
Session altered.

SELECT /*+ qb_name(q1) */
       ROWID
  FROM (SELECT /*+ qb_name(q2) */
              c1
              ,c3
              ,ROWID
              ,RANK () OVER (PARTITION BY c1 ORDER BY c3 DESC) rnk
          FROM t
         WHERE c1 IN (  SELECT /*+ qb_name(q3) */
                               c1
                          FROM t
                      GROUP BY c1, c2
                        HAVING COUNT (*) > 1))
 WHERE rnk > 1;

Plan hash value: 2076813628
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |       |   271K(100)|          |      0 |00:00:48.49 |    1730K|       |       |          |
|*  1 |  VIEW                  |      |      1 |      1 |    25 |   271K  (3)| 00:54:19 |      0 |00:00:48.49 |    1730K|       |       |          |
|   2 |   WINDOW SORT          |      |      1 |      1 |    24 |   271K  (3)| 00:54:19 |     20 |00:00:48.49 |    1730K|  2048 |  2048 | 2048  (0)|
|*  3 |    FILTER              |      |      1 |        |       |            |          |     20 |00:00:48.49 |    1730K|       |       |          |
|   4 |     TABLE ACCESS FULL  | T    |      1 |  10010 |   234K|    53   (0)| 00:00:01 |  10010 |00:00:00.01 |     173 |       |       |          |
|*  5 |     FILTER             |      |  10000 |        |       |            |          |     10 |00:00:48.47 |    1730K|       |       |          |
|   6 |      HASH GROUP BY     |      |  10000 |      2 |    22 |    54   (2)| 00:00:01 |     99M|00:00:42.97 |    1730K|  1661K|  1661K| 1485K (0)|
|   7 |       TABLE ACCESS FULL| T    |  10000 |  10010 |   107K|    53   (0)| 00:00:01 |    100M|00:00:06.56 |    1730K|       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - Q2 / from$_subquery$_001@Q1
   2 - Q2
   4 - Q2 / T@Q2
   5 - Q3
   7 - Q3 / T@Q3
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RNK">1)
   3 - filter( IS NOT NULL)
   5 - filter(("C1"=:B1 AND COUNT(*)>1))

--//存在2次filter.這樣代入實際行數是 10000*10000 = 100000000.
--//如果rowid使用別名,這樣阻止一次filter.看看看看執行計劃:

SELECT /*+ qb_name(q1) */
       Rid
  FROM (SELECT /*+ qb_name(q2) */
              c1
              ,c3
              ,ROWID rid
              ,RANK () OVER (PARTITION BY c1 ORDER BY c3 DESC) rnk
          FROM t
         WHERE c1 IN (  SELECT /*+ qb_name(q3) */
                               c1
                          FROM t
                      GROUP BY c1, c2
                        HAVING COUNT (*) > 1))
 WHERE rnk > 1;

Plan hash value: 296566557

---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |       |   108 (100)|          |      0 |00:00:00.02 |     346 |       |       |          |
|*  1 |  VIEW                   |          |      1 |    502 | 12550 |   108   (2)| 00:00:02 |      0 |00:00:00.02 |     346 |       |       |          |
|   2 |   WINDOW SORT           |          |      1 |    502 | 18574 |   108   (2)| 00:00:02 |     20 |00:00:00.02 |     346 |  2048 |  2048 | 2048  (0)|
|*  3 |    HASH JOIN RIGHT SEMI |          |      1 |    502 | 18574 |   107   (1)| 00:00:02 |     20 |00:00:00.02 |     346 |  2440K|  2440K| 1385K (0)|
|   4 |     VIEW                | VW_NSO_1 |      1 |    501 |  6513 |    54   (2)| 00:00:01 |     10 |00:00:00.01 |     173 |       |       |          |
|*  5 |      FILTER             |          |      1 |        |       |            |          |     10 |00:00:00.01 |     173 |       |       |          |
|   6 |       HASH GROUP BY     |          |      1 |     26 |  5511 |    54   (2)| 00:00:01 |  10000 |00:00:00.01 |     173 |  1661K|  1661K| 2580K (0)|
|   7 |        TABLE ACCESS FULL| T        |      1 |  10010 |   107K|    53   (0)| 00:00:01 |  10010 |00:00:00.01 |     173 |       |       |          |
|   8 |     TABLE ACCESS FULL   | T        |      1 |  10010 |   234K|    53   (0)| 00:00:01 |  10010 |00:00:00.01 |     173 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5174989D / from$_subquery$_001@Q1
   2 - SEL$5174989D
   4 - SEL$186AFB95 / VW_NSO_1@SEL$5174989D
   5 - SEL$186AFB95
   7 - SEL$186AFB95 / T@Q3
   8 - SEL$5174989D / T@Q2
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RNK">1)
   3 - access("C1"="C1")
   5 - filter(COUNT(*)>1)