参见 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
执行计划中使用了固化的基线。