Oracle性能优化——SQL基线（SQL baseline）的载入与进化（11g中引入的基线）
来源：互联网 发布：软件设计师考试准考证 编辑：程序博客网 时间：2024/04/28 18:41
参见 Oracle性能优化求生指南
SYS@ prod> conn sh/shConnected.执行想要创建基线的语句，语句的执行计划将会被缓存SH@ prod> set autotrace onSH@ 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(*)---------- 9Execution 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 sysdbaConnected.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 loadedPL/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_a8f88a44571be8ddSQL 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: 241207086Enabled: 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/shConnected.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 onSH@ 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(*)---------- 9Execution 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 statementStatistics---------------------------------------------------------- 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 sysdbaConnected.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 409SYS_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 PlanBaseline Report-------------------------------------------------------------------------------Inputs:-------SQL_HANDLE = SYS_SQL_a8f88a44571be8dd PLAN_NAME = TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES COMMIT= YESPlan: SQL_PLAN_ajy4a8jbjru6x5b4b1285------------------------------------ Plan was verified: Time used .08seconds. Plan passed performance criterion: 243.74 times better than baseline plan. Plan was changed to an acceptedplan. Baseline Plan Test Plan Stats Ratio------------- --------- ----------- Execution Status: COMPLETE COMPLETE RowsProcessed: 1 1 Elapsed Time(ms): 4.647 .043108.07 CPU Time(ms): 4.554 0 Buffer Gets: 1462 6243.67 Physical Read Requests: 0 0 Physical Write Requests: 00 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0Executions: 11-------------------------------------------------------------------------------Report Summary-------------------------------------------------------------------------------Number of plans verified:1Number of plans accepted: 1PL/SQL procedure successfully completed.查看进化后的基线是否生效，结果表明执行计划在稳定的基础上获得了提升SYS@ prod> conn sh/shConnected.SH@ prod> set autotrace onSH@ 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(*)---------- 9Execution 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 statementStatistics---------------------------------------------------------- 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 409SYS_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_a8f88a44571be8ddSQL 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: 241207086Enabled: 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: 1531646597PLAN_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.阅读全文
0 0
- Oracle性能优化——SQL基线（SQL baseline）的载入与进化（11g中引入的基线）
- oracle 11g BaseLine(基线)指定application中不可修改sql的执行计划
- Oracle SQL执行计划基线总结（SQL Plan Baseline）
- Oracle SQL执行计划基线总结（SQL Plan Baseline）
- Oracle SQL执行计划基线总结（SQL Plan Baseline）
- 软件配置管理中基线（baseline）
- 基线（Baseline）的含义以及在Android中的使用
- Canvas.drawText（）时，文字基线baseline的计算
- oracle 基线与优化（一）
- oracle 基线与优化（二）
- oracle 基线与优化（三）
- oracle 基线与优化（四）
- 创建AWR 基线（baseline）
- oracle sql计划基线
- Oracle优化——固化的基线（作用类型于存储提纲）
- 26.Oracle深度学习笔记——SQL基线
- sql plan baselines(计划基线）Oracle 原文
- ClearCase洋文翻译-baseline（基线）
- 简单的循环队列实现 不足之处请留言指出
- HDU-2203 kmp水一发
- 九度OJ 1086
- [转载]C++继承中重载、重写、重定义的区别：
- Oracle网络连通性是通过些才能保证流畅运行的
- Oracle性能优化——SQL基线（SQL baseline）的载入与进化（11g中引入的基线）
- 51Nod 1019 逆序数(树状数组/归并)
- python_day3
- js 对象的深拷贝
- JSP文件过大编译失败，异常如is exceeding the 65535 bytes
- LeetCode
- 随笔 | 大四了
- 没有后端基础，怎么入门学习Nginx？
- 简单的顺序栈实现 不足之处请留言指出
