本文介绍如何在Oracle数据库中创建、管理和优化SQL执行计划基线以提高查询性能。通过手动加载和自动捕获的方式创建基线，并演示了如何通过基线使执行计划稳定及进一步优化。
参见 Oracle性能优化求生指南
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: 3580706376
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 409 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 45 | | |
|* 2 | HASH JOIN | | 123 | 5535 | 409 (1)| 00:00:05 |
|* 3 | TABLE ACCESS FULL| COUNTRIES | 1 | 15 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| CUSTOMERS | 2341 | 70230 | 405 (1)| 00:00:05 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUSTOMERS"."COUNTRY_ID"="COUNTRIES"."COUNTRY_ID")
3 - filter("COUNTRIES"."COUNTRY_NAME"='New Zealand')
4 - filter("CUSTOMERS"."CUST_INCOME_LEVEL"='G: 130,000 - 149,999' AND
"CUSTOMERS"."CUST_YEAR_OF_BIRTH"<1952)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1462 consistent gets
19 physical reads
0 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)
1 rows processed
从游标缓存区中手动载入关于这个SQL的执行计划做为基线
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
8 v_plan_count := dbms_spm.load_plans_from_cursor_cache( sql_id => v_sql_id ) ;
9 dbms_output.put_line( v_plan_count || ' plans loaded' ) ;
10 end ;
11 /
1 plans loaded
PL/SQL procedure successfully completed.
查看基线的信息，这个基线的状态是ACCEPTED，是将会被优化器使用的
SYS@ prod> select sql_handle , plan_name , origin , accepted , optimizer_cost as cost
2 from dba_sql_plan_baselines
3 where sql_text like 'select /*ghbaselines1*/%' ;
SQL_HANDLE PLAN_NAME ORIGIN ACC COST
------------------------------ ------------------------------ -------------- --- ----------
SYS_SQL_a8f88a44571be8dd SQL_PLAN_ajy4a8jbjru6x0e60872e MANUAL-LOAD YES 409
查看基线里面的执行计划
SYS@ prod> var v_sql_handle varchar2(50) ;
SYS@ prod> exec :v_sql_handle := 'SYS_SQL_a8f88a44571be8dd' ;
PL/SQL procedure successfully completed.
SYS@ prod> select * from table(dbms_xplan.display_sql_plan_baseline(:v_sql_handle , null , 'basic' ) ) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_a8f88a44571be8dd
SQL text: select /*ghbaselines1*/ count(*) from customers join countries using (
country_id ) where country_name = 'New Zealand' and cust_income_level =
'G: 130,000 - 149,999' and cust_year_of_birth < '1952'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ajy4a8jbjru6x0e60872e Plan id: 241207086
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 3580706376
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL| COUNTRIES |
| 4 | TABLE ACCESS FULL| CUSTOMERS |
-----------------------------------------
24 rows selected.
创建索引
SYS@ prod> conn sh/sh
Connected.
SH@ prod> create index cust_country_index_dob_ix on
2 customers( country_id , cust_income_level , cust_year_of_birth ) ;
Index created.
查看执行计划是否有变化，因为基线的存在执行计划并没有变化，执行计划获得了稳定
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: 3580706376
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 409 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 45 | | |
|* 2 | HASH JOIN | | 123 | 5535 | 409 (1)| 00:00:05 |
|* 3 | TABLE ACCESS FULL| COUNTRIES | 1 | 15 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| CUSTOMERS | 2341 | 70230 | 405 (1)| 00:00:05 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUSTOMERS"."COUNTRY_ID"="COUNTRIES"."COUNTRY_ID")
3 - filter("COUNTRIES"."COUNTRY_NAME"='New Zealand')
4 - filter("CUSTOMERS"."CUST_INCOME_LEVEL"='G: 130,000 - 149,999' AND
"CUSTOMERS"."CUST_YEAR_OF_BIRTH"<1952)
Note
-----
- SQL plan baseline "SQL_PLAN_ajy4a8jbjru6x0e60872e" used for this statement
Statistics
----------------------------------------------------------
537 recursive calls
37 db block gets
1555 consistent gets
1 physical reads
14388 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)
1 rows processed
查看新的基线（新的执行计划由优化器生成，但是由于基线的存在并没有被使用，即使更好，但是会生成一个新的基线）
两个基线的SQL_HANDLE是一样的。
SH@ prod> conn / as sysdba
Connected.
SYS@ prod> select sql_handle , plan_name , origin , accepted , optimizer_cost as cost
2 from dba_sql_plan_baselines
3 where sql_text like 'select /*ghbaselines1*/%' ;
SQL_HANDLE PLAN_NAME ORIGIN ACC COST
------------------------------ ------------------------------ -------------- --- ----------
SYS_SQL_a8f88a44571be8dd SQL_PLAN_ajy4a8jbjru6x0e60872e MANUAL-LOAD YES 409
SYS_SQL_a8f88a44571be8dd SQL_PLAN_ajy4a8jbjru6x5b4b1285 AUTO-CAPTURE NO 5
进化基线，verify为YES表示对性能有提升才会被接受。Commit表示直接接受，而不是给出建议。
SYS@ prod> var v_sql_handle varchar2(50)
SYS@ prod> exec :v_sql_handle := 'SYS_SQL_a8f88a44571be8dd'
SYS@ prod> var v_report varchar2(2000)
SYS@ prod> begin
2 :v_report := dbms_spm.evolve_sql_plan_baseline ( sql_handle => :v_sql_handle ,
3 verify => 'YES' ,
4 commit => 'YES' ) ;
5 end ;
6 /
PL/SQL procedure successfully completed.
查看报告
SYS@ prod> set serveroutput on
SYS@ prod> exec dbms_output.put_line(:v_report ) ;
-------------------------------------------------------------------------------
Evolve SQL Plan
Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_a8f88a44571be8dd
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT
= YES
Plan: SQL_PLAN_ajy4a8jbjru6x5b4b1285
------------------------------------
Plan was verified: Time used .08
seconds.
Plan passed performance criterion: 243.74 times better than baseline plan.
Plan was changed to an accepted
plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
Rows
Processed: 1 1
Elapsed Time(ms): 4.647 .043
108.07
CPU Time(ms): 4.554 0
Buffer Gets: 1462 6
243.67
Physical Read Requests: 0 0
Physical Write Requests: 0
0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1
1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified:
1
Number of plans accepted: 1
PL/SQL procedure successfully completed.
查看进化后的基线是否生效，结果表明执行计划在稳定的基础上获得了提升
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
----------------------------------------------------------
14 recursive calls
14 db block gets
14 consistent gets
0 physical reads
3040 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当然会选Cost小的
SYS@ prod> select sql_handle , plan_name , origin , accepted , optimizer_cost as cost
2 from dba_sql_plan_baselines
3 where sql_text like 'select /*ghbaselines1*/%' ;
SQL_HANDLE PLAN_NAME ORIGIN ACC COST
------------------------------ ------------------------------ -------------- --- ----------
SYS_SQL_a8f88a44571be8dd SQL_PLAN_ajy4a8jbjru6x0e60872e MANUAL-LOAD YES 409
SYS_SQL_a8f88a44571be8dd SQL_PLAN_ajy4a8jbjru6x5b4b1285 AUTO-CAPTURE YES 5
查看基线的执行计划，两个都会被展示
SYS@ prod> select * from table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_a8f88a44571be8dd' , null , 'basic' ) ) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_a8f88a44571be8dd
SQL text: select /*ghbaselines1*/ count(*) from customers join countries using (
country_id ) where country_name = 'New Zealand' and cust_income_level =
'G: 130,000 - 149,999' and cust_year_of_birth < '1952'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ajy4a8jbjru6x0e60872e Plan id: 241207086
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 3580706376
-----------------------------------------
| Id | Operation | Name |
-----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL| COUNTRIES |
| 4 | TABLE ACCESS FULL| CUSTOMERS |
-----------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ajy4a8jbjru6x5b4b1285 Plan id: 1531646597
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 1428720438
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL| COUNTRIES |
| 4 | INDEX RANGE SCAN | CUST_COUNTRY_INDEX_DOB_IX |
---------------------------------------------------------
41 rows selected.
扫一扫
1127
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
