[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)