关于执行计划和SQL PROFILE的问题

zndavid

刘大，请教一个问题，在执行计划中，ROWS列是优化器预计返回的行数，在这个例子中，实际返回行数是31，但是优化器预计是2500，有一定的误差，但是我运行了SQL TUNNING ADVISOR并且APPLY PROFILE之后，执行计划能准备计算返回行数，这是为什么呢？SQL PROFILE到底做了什么呢？

代码：
create table t1 as select object_id,object_name from dba_objects where rownum<=50000;  

create table t2 as select * from dba_objects;  

create index t2_idx on t2(object_id);   

exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size auto',estimate_percent => 100);   
exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size auto',estimate_percent => 100);  

select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
--省略输出，此处返回31行，执行计划如下：
31 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2500 |    97K|   372   (1)| 00:00:05 |
|*  1 |  HASH JOIN         |      |  2500 |    97K|   372   (1)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 72500 |    72   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 72726 |   781K|   299   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')


Statistics
----------------------------------------------------------
        672  recursive calls
          0  db block gets
       1450  consistent gets
          0  physical reads
          0  redo size
       1873  bytes sent via SQL*Net to client
        546  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         24  sorts (memory)
          0  sorts (disk)
         31  rows processed

在运行了SQL TUNING ADVISOR并且APPLY PROFILE之后，执行计划如下：
31 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    31 |  1240 |   134   (1)| 00:00:02 |
|   1 |  NESTED LOOPS                |        |       |       |            |          |
|   2 |   NESTED LOOPS               |        |    31 |  1240 |   134   (1)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    31 |   899 |    72   (2)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SYS_SQLPROF_01501c593072c000" used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        302  consistent gets
          0  physical reads
          0  redo size
       1873  bytes sent via SQL*Net to client
        546  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         31  rows processed

PROFILE中的内容：
OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0124)

一些文章中提到SQL PROFILE是给优化器提供更详细的统计信息，但是有个疑问是为什么非要到PROFILE里面才有更详细的统计信息，而如果没有使用PROFILE，优化器则找不到详细的统计信息，造成预估的返回行数不对呢？

Maclean Liu(刘相兵

select /*MACLEAN2*/  t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

执行计划
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2500 |   100K|   584   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  2500 |   100K|   584   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 72500 |    65   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |   110K|  1290K|   518   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)



10046 trace it :

Tune the sql
~~~~~~~~~~~~

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : 任务_275
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 08/13/2013 16:43:49
Completed at       : 08/13/2013 16:43:50

-------------------------------------------------------------------------------
Schema Name: SYS

SQL ID     : 83u75psg0c1gq
SQL Text   : select /*MACLEAN2*/  t1.*,t2.owner from t1,t2 where
             t1.object_name like '%T1%' and t1.object_id=t2.object_id

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  为此语句找到了性能更好的执行计划。

  Recommendation (estimated benefit: 86.08%)

  ------------------------------------------
  - 考虑接受推荐的 SQL 概要文件。
    execute dbms_sqltune.accept_sql_profile(task_name => '任务_275', task_owner
            => 'SYS', replace => TRUE);

  Validation results
  ------------------
  已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行
统计信息。如果其中一个计划运行在很短的时间内就完成,
  则另一计划可能只执行了一部分。

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------

  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):             .022228           .010695      51.88 %
  CPU Time (s):                   .0234            .01092      53.33 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                     2147               298      86.12 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                    42                42
  Fetches:                           42                42
  Executions:                         1                 1


  Notes
  -----
  1. the original plan 的统计信息是 10 执行的平均值。
  2. the SQL profile plan 的统计信息是 10 执行的平均值。

2- Alternative Plan Finding
---------------------------
  通过搜索系统的实时和历史性能数据找到了此语句的某些替代执行计划。

  The following table lists these plans ranked by their average elapsed time.
  See section "ALTERNATIVE PLANS SECTION" for detailed information on each
  plan.


  id plan hash  last seen            elapsed (s)  origin          note

  -- ---------- -------------------- ------------ --------------- --------------
--
   1 1022743391  2013-08-13/16:41:38        0.012 Cursor Cache

   2 1838229974  2013-08-13/16:41:34        0.041 Cursor Cache    original plan


  Recommendation
  --------------
  - 请考虑使用最佳平均用时为计划创建 SQL 计划基线。
    execute dbms_sqltune.create_sql_plan_baseline(task_name => '任务_275',

            owner_name => 'SYS', plan_hash_value => 1022743391);

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    42 |  1722 |   584   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    42 |  1722 |   584   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |    42 |  1218 |    65   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |   110K|  1290K|   518   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)


2- Using SQL Profile
--------------------
Plan hash value: 1022743391

--------------------------------------------------------------------------------
-------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Tim
e     |
--------------------------------------------------------------------------------
-------
|   0 | SELECT STATEMENT             |        |    42 |  1722 |   149   (0)| 00:
00:01 |
|   1 |  NESTED LOOPS                |        |       |       |            |

      |
|   2 |   NESTED LOOPS               |        |    42 |  1722 |   149   (0)| 00:
00:01 |
|*  3 |    TABLE ACCESS FULL         | T1     |    42 |  1218 |    65   (0)| 00:
00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:
00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    12 |     2   (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

-------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
-------------------------------------------------------------------------------

Plan 1
------


  Plan Origin                 :Cursor Cache
  Plan Hash Value             :1022743391
  Executions                  :2
  Elapsed Time                :0.012 sec
  CPU Time                    :0.008 sec
  Buffer Gets                 :308
  Disk Reads                  :0
  Disk Writes                 :0

Notes:
  1. Statistics shown are averaged over multiple executions.

--------------------------------------------------------------------------------

-------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Tim
e     |
--------------------------------------------------------------------------------
-------
|   0 | SELECT STATEMENT             |        |  2500 |   100K|  5066   (1)| 00:
00:01 |
|   1 |  NESTED LOOPS                |        |       |       |            |
      |
|   2 |   NESTED LOOPS               |        |  2500 |   100K|  5066   (1)| 00:
00:01 |
|*  3 |    TABLE ACCESS FULL         | T1     |  2500 | 72500 |    65   (0)| 00:
00:01 |

|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:
00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    12 |     2   (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")


Plan 2
------

  Plan Origin                 :Cursor Cache
  Plan Hash Value             :1838229974
  Executions                  :1
  Elapsed Time                :0.041 sec
  CPU Time                    :0.031 sec
  Buffer Gets                 :2215
  Disk Reads                  :0
  Disk Writes                 :0


Notes:
  1. Statistics shown are averaged over multiple executions.
  2. The plan matches the original plan.

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2500 |   100K|   584   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  2500 |   100K|   584   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 72500 |    65   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |   110K|  1290K|   518   (1)| 00:00:01 |
---------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)

-------------------------------------------------------------------------------


SQL> oradebug setmypid
已处理的语句
SQL> oradebug tracefile_name
C:\APP\XIANGBLI\diag\rdbms\maclean\maclean\trace\maclean_ora_3604.trc

Maclean Liu(刘相兵

从sql tuning 过程中获得的10046 trace内容的 有用SQL如下：

/* Formatted on 2013/8/13 16:46:00 (QP5 v5.163.1008.3004) */
SELECT                                                            /* DS_SVC */
       /*+ cursor_sharing_exact dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) result_cache */
      SUM (
          C1)
  FROM (SELECT               /*+ qb_name("innerQuery") NO_INDEX_FFS( "T2")  */
              1 AS C1
          FROM "T2" SAMPLE BLOCK (42.0168, 8) SEED (1) "T2") innerQuery
         

SELECT                                                            /* DS_SVC */
       /*+ cursor_sharing_exact dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) result_cache */
      SUM (
          C1)
  FROM (SELECT               /*+ qb_name("innerQuery") NO_INDEX_FFS( "T1")  */
              1 AS C1
          FROM "T1" "T1"
         WHERE ("T1"."OBJECT_NAME" LIKE '%T1%')
               AND ("T1"."OBJECT_NAME" IS NOT NULL)) innerQuery
               
               
   

SELECT                                                            /* DS_SVC */
       /*+ cursor_sharing_exact dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) result_cache OPT_ESTIMATE(@"innerQuery", TABLE, "T1#1", SCALE_ROWS=0.0168) */
      SUM (
          C1)
  FROM (SELECT             /*+ qb_name("innerQuery") NO_INDEX_FFS( "T2#0")  */
              1 AS C1
          FROM "T2" SAMPLE BLOCK (42.0168, 8) SEED (1) "T2#0", "T1" "T1#1"
         WHERE     ("T1#1"."OBJECT_NAME" LIKE '%T1%')
               AND ("T1#1"."OBJECT_NAME" IS NOT NULL)
               AND ("T1#1"."OBJECT_ID" = "T2#0"."OBJECT_ID")) innerQuery

                          
                          
/* SQL Analyze(243,0) */ select /*MACLEAN2*/  t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
/* SQL Analyze(243,0) */ select /*MACLEAN2*/  t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id
/* SQL Analyze(243,0) */ select /*MACLEAN2*/  t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id

Maclean Liu(刘相兵

这里虽然 t1、t2表都有统计信息， 但sql tuning 为了获得更好的基数信息 选择了使用 统计信息采样

这里包括至少3次  采样：
1、 对t2 表采样
2、 对t1表的"T1"."OBJECT_NAME" LIKE '%T1%' 条件采样
3、 对t1 join t2的情况采样

Maclean Liu(刘相兵

简单总结sql tune 所做的：

针对查询分解为多个 data source和join ，通过动态采样获得其较为真实的基数card ， 并测试不同的执行计划， 通过SCALE_ROWS这个global hint 将sql tune的动态采样的信息 告诉下次解析的optimizer优化器， 例如某一个join最终获得的基数可能是 基于普通统计信息评估estimate的10倍。