ITPub博客

首页 > 数据库 > Oracle > [20140118]12c-AdaptiveQueryOptimization

[20140118]12c-AdaptiveQueryOptimization

原创 Oracle 作者:lfree 时间:2014-01-19 09:32:16 0 删除 编辑

[20140118]12c新特性-Adaptive Query Optimization.txt

twp-optimizer-with-oracledb-12c-1963236.pdf

Adaptive Query Optimization

By far the biggest change to the optimizer in Oracle Database 12c is Adaptive Query Optimization.
Adaptive Query Optimization is a set of capabilities that enable the optimizer to make run-time
adjustments to execution plans and discover additional information that can lead to better statistics.
This new approach is extremely helpful when existing statistics are not sufficient to generate an optimal
plan. There are two distinct aspects in Adaptive Query Optimization, adaptive plans, which focuses on
improving the initial execution of a query and adaptive statistics, which provide additional information
to improve subsequent executions. 

--想自己做一个例子,不好做,参照文档学习一下:

select /*+ gather_plan_statistics */p.product_name from order_items o ,product_information p
where o.unit_price=15
and o.quantity>1 and p.product_id=o.product_id;

OE@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7fcy4z056msgm, child number 0
-------------------------------------
select /*+ gather_plan_statistics */p.product_name from order_items o
,product_information p where o.unit_price=15 and o.quantity>1 and
p.product_id=o.product_id

Plan hash value: 1553478007

-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |      1 |        |     7 (100)|     13 |00:00:00.03 |      24 |     20 |       |       |          |
|*  1 |  HASH JOIN         |                     |      1 |      4 |     7   (0)|     13 |00:00:00.03 |      24 |     20 |  2061K|  2061K|  422K (0)|
|*  2 |   TABLE ACCESS FULL| ORDER_ITEMS         |      1 |      4 |     3   (0)|     13 |00:00:00.02 |       7 |      6 |       |       |          |
|   3 |   TABLE ACCESS FULL| PRODUCT_INFORMATION |      1 |      1 |     1   (0)|    288 |00:00:00.01 |      17 |     14 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   2 - filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))

Note
-----
   - this is an adaptive plan

--看note提示执行计划adaptive plan。

To see all of the operations in an adaptive plan, including the positions of the statistics collectors, the
additional format parameter '+adaptive' must be specified in the DBMS_XPLAN functions. In this mode
an additional notation (-) appears in the id column of the plan, indicating the operations in the plan
that were not used (inactive). The SQL Monitor tool in Oracle Enterprise Manager always shows the
full adaptive plan but does not indicate which operations in the plan are inactive.

--第2次执行:
select /*+ gather_plan_statistics */p.product_name from order_items o ,product_information p
where o.unit_price=15
and o.quantity>1 and p.product_id=o.product_id;


OE@test01p> @dpc '' adaptive
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7fcy4z056msgm, child number 1
-------------------------------------
select /*+ gather_plan_statistics */p.product_name from order_items o
,product_information p where o.unit_price=15 and o.quantity>1 and
p.product_id=o.product_id

Plan hash value: 1553478007

------------------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                     | Name                   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT              |                        |      1 |        |     8 (100)|     13 |00:00:00.01 |      24 |       |       |          |
|  *  1 |  HASH JOIN                    |                        |      1 |     13 |     8   (0)|     13 |00:00:00.01 |      24 |  2061K|  2061K|  391K (0)|
|-    2 |   NESTED LOOPS                |                        |      1 |        |            |     13 |00:00:00.01 |       7 |       |       |          |
|-    3 |    NESTED LOOPS               |                        |      1 |     13 |     8   (0)|     13 |00:00:00.01 |       7 |       |       |          |
|-    4 |     STATISTICS COLLECTOR      |                        |      1 |        |            |     13 |00:00:00.01 |       7 |       |       |          |
|  *  5 |      TABLE ACCESS FULL        | ORDER_ITEMS            |      1 |     13 |     3   (0)|     13 |00:00:00.01 |       7 |       |       |          |
|- *  6 |     INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |      0 |        |            |      0 |00:00:00.01 |       0 |       |       |          |
|-    7 |    TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |      0 |      1 |     5   (0)|      0 |00:00:00.01 |       0 |       |       |          |
|     8 |   TABLE ACCESS FULL           | PRODUCT_INFORMATION    |      1 |    288 |     5   (0)|    288 |00:00:00.01 |      17 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   5 - filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
   6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")

Note
-----
   - statistics feedback used for this statement
   - this is an adaptive plan (rows marked '-' are inactive)

--可以看到statistics feedback used for this statement,对比就可以发现全表扫描时E-Rows,A-Rows差距很大。

A new column has also been added to V$SQL(IS_RESOLVED_ADAPTIVE_PLAN) to indicate if a SQL
statement has an adaptive plan and if that plan has been full resolved or not. If
IS_RESOLVED_ADAPTIVE_PLAN is set to 'Y', it means that the plan was not only adaptive, but the
final plan has been selected. However, if IS_RESOLVED_ADAPTIVE_PLAN is set to 'N', it indicates the
plan selected is adaptive but the final plan has not yet been decided on. The 'N' value is only possible
during the initial execution of a query, after that the value for an adaptive plan will always be 'Y'.  This
column is set to NULL for non-adaptive plans.

It is also possible to put adaptive join methods into reporting mode by setting the initialization
parameter OPTIMIZER_ADAPTIVE_REPORTING_ONLY to TRUE (default FALSE). In this mode,
information needed to enable adaptive join methods is gathered, but no action is taken to change the
plan. This means the default plan will always be used but information is collected on how the plan
would have adapted in non-reporting mode. This information can be viewed in the adaptive plan
report, which is visible when you display the plan using the additional format parameter '+report'.

column is_bind_aware format a10
column is_bind_sensitive format a10
column is_obsolete format a10
column is_reoptimizable format a10
column is_resolved_adaptive_plan format a10
column is_shareable format a10

SELECT sql_id,
       child_number,
       sql_text,
       is_bind_aware,
       is_bind_sensitive,
       is_obsolete,
       is_reoptimizable,
       is_resolved_adaptive_plan,
       is_shareable
  FROM v$sql
WHERE sql_id = '7fcy4z056msgm';

SQL_ID        CHILD_NUMBER SQL_TEXT                                                     IS_BIND_AW IS_BIND_SE IS_OBSOLET IS_REOPTIM IS_RESOLVE IS_SHAREAB
------------- ------------ ------------------------------------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
7fcy4z056msgm            0 select /*+ gather_plan_statistics */p.product_name from orde N          N          N          Y          Y          N
                           r_items o ,product_information p where o.unit_price=15 and o
                           .quantity>1 and p.product_id=o.product_id

7fcy4z056msgm            1 select /*+ gather_plan_statistics */p.product_name from orde N          N          N          N          Y          Y
                           r_items o ,product_information p where o.unit_price=15 and o
                           .quantity>1 and p.product_id=o.product_id
--child_numnber=0 的is_shareable='N',这样以后会剔除共享池.

SYS@test01p> show parameter OPTIMIZER_ADAPTIVE_REPORTING_ONLY
NAME                                 TYPE     VALUE
------------------------------------ -------- --------
optimizer_adaptive_reporting_only    boolean  FALSE

OE@test01p> select * from table(dbms_xplan.display_cursor('7fcy4z056msgm',format=>'+report'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7fcy4z056msgm, child number 0
-------------------------------------
select /*+ gather_plan_statistics */p.product_name from order_items o
,product_information p where o.unit_price=15 and o.quantity>1 and
p.product_id=o.product_id

Plan hash value: 1553478007

------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |       |       |     7 (100)|          |
|*  1 |  HASH JOIN         |                     |     4 |   128 |     7   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| ORDER_ITEMS         |     4 |    48 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| PRODUCT_INFORMATION |     1 |    20 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   2 - filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))

Note
-----
   - this is an adaptive plan


Reoptimized plan:
-----------------
This cursor is marked for automatic reoptimization.  The plan that is
expected to be chosen on the next execution is displayed below.

Plan hash value: 1553478007

------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |    13 |   416 |     8   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |                     |    13 |   416 |     8   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| ORDER_ITEMS         |    13 |   156 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| PRODUCT_INFORMATION |   288 |  5760 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   2 - filter("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1)

Note
-----
   - this is an adaptive plan

53 rows selected.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1072675/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6291516