Oracle性能优化——SQL基线（SQLbaseline）的载入与进化（11g中引入的基线）
程序员文章站
2023-10-11 17:46:22
oracle性能优化——sql基线（sqlbaseline）的载入与进化（11g中引入的基线）
sys@ prod> conn sh/sh
connected....
oracle性能优化——sql基线（sqlbaseline）的载入与进化（11g中引入的基线）
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.