Oracle优化——固化的基线（作用类型于存储提纲）

当有固化的基线存在时，即使有导致执行计划发生改变的变化发生时，也不会产生新的基线。所以固化的基线不会进化。而且，当有固化基线时，优化器首先选择固化基线，不管它是不是最优化的。 Oracle建议使用固化基线而不是存储提纲。

SH@ prod> select /*ghbaselines1*/ count(*) from customers join countries using ( country_id ) 
  2  where country_name = 'New Zealand' and cust_income_level = 'G: 130,000 - 149,999' 
  3  and cust_year_of_birth < '1952' ;

  COUNT(*)
----------
         9

导入基线，并且指定其为固化
SH@ prod> conn / as sysdba
Connected.
SYS@ prod> declare
  2  v_sql_id v$sql.sql_id%type ;
  3  v_plan_count number ;
  4  begin
  5  select sql_id into v_sql_id from v$sql
  6  where sql_text like 'select /*ghbaselines1*/%' ;
  7  v_plan_count := dbms_spm.load_plans_from_cursor_cache( sql_id => v_sql_id , fixed => 'YES' ) ;
  8  dbms_output.put_line(v_plan_count || ' plans loaded') ;
  9  end ;
 10  /
1 plans loaded

PL/SQL procedure successfully completed.

查看基线
SYS@ prod> select sql_handle , plan_name , origin , accepted , optimizer_cost as cost , fixed 
  2  from dba_sql_plan_baselines
  3  where sql_text like 'select /*ghbaselines1*/%' ;

SQL_HANDLE                     PLAN_NAME                      ORIGIN         ACC       COST FIX
------------------------------ ------------------------------ -------------- --- ---------- ---
SYS_SQL_a8f88a44571be8dd       SQL_PLAN_ajy4a8jbjru6x0e60872e MANUAL-LOAD    YES        409 NO
SYS_SQL_a8f88a44571be8dd       SQL_PLAN_ajy4a8jbjru6x5b4b1285 MANUAL-LOAD    YES          5 YES

验证固化基线的使用
SYS@ prod> conn sh/sh
Connected.
SH@ prod> set autotrace on
SH@ prod> select /*ghbaselines1*/ count(*) from customers join countries using ( country_id ) 
  2  where country_name = 'New Zealand' and cust_income_level = 'G: 130,000 - 149,999' 
  3  and cust_year_of_birth < '1952' ;

  COUNT(*)
----------
         9


Execution Plan
----------------------------------------------------------
Plan hash value: 1428720438

-------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                           |     1 |    45 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |                           |     1 |    45 |            |          |
|   2 |   NESTED LOOPS      |                           |   123 |  5535 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| COUNTRIES                 |     1 |    15 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | CUST_COUNTRY_INDEX_DOB_IX |   123 |  3690 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   3 - filter("COUNTRIES"."COUNTRY_NAME"='New Zealand')
   4 - access("CUSTOMERS"."COUNTRY_ID"="COUNTRIES"."COUNTRY_ID" AND
              "CUSTOMERS"."CUST_INCOME_LEVEL"='G: 130,000 - 149,999' AND
              "CUSTOMERS"."CUST_YEAR_OF_BIRTH"<1952)

Note
-----
   - SQL plan baseline "SQL_PLAN_ajy4a8jbjru6x5b4b1285" used for this statement


Statistics
----------------------------------------------------------
         17  recursive calls
         13  db block gets
         16  consistent gets
          0  physical reads
       2996  redo size
        526  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
1rows processed
执行计划中使用了固化的基线。

Oracle优化——固化的基线（作用类型于存储提纲）

相关推荐