cms网站地图模板,深圳建设门户网站,东莞建设网站推广公司地址,wordpress相册标签分类在Oracle Database 12c Release 1 (12.1)版本中#xff0c;引入了对SQL计划管理#xff08;SPM#xff09;功能的增强#xff0c;特别是关于SQL计划基线的自动进化机制。这一改进允许数据库更加智能地管理和优化SQL查询的执行计划#xff0c;确保即使数据分布发生变化引入了对SQL计划管理SPM功能的增强特别是关于SQL计划基线的自动进化机制。这一改进允许数据库更加智能地管理和优化SQL查询的执行计划确保即使数据分布发生变化也能维持高效的查询性能。
ASPM的核心理念在于其能够根据实时运行时统计信息来评估不同执行计划的表现并据此决定是否需要采用新的执行计划。这意味着即使是在数据库重启之后优化器也能利用之前收集到的信息做出更加明智的选择。 一、SQL计划管理的基础
最初在Oracle 11g中引入的SQL计划管理旨在通过固定一组已验证为高效的执行计划来稳定查询性能。这种方法被称为“保守的计划选择策略”因为它倾向于使用经过验证的执行计划而不是每次都让优化器重新评估所有可能的执行路径。到了Oracle 12c虽然这个核心理念没有改变但引入了一些重要的更新以进一步提升自动化程度和灵活性。 二、SYS_AUTO_SPM_EVOLVE_TASK介绍
2.1 自动化SQL计划基线的进化
在Oracle数据库12C中现有基线的演变被自动化为称为SYS_AUTO_SPM_EVOVES_TASK的顾问任务该任务是由自动化数据库维护任务下现有的“ SQL Tuning Advisor”客户端触发的。
CONN syspdb1 AS SYSDBACOLUMN client_name FORMAT A35
COLUMN task_name FORMAT a30SELECT client_name, task_name
FROM dba_autotask_task;CLIENT_NAME TASK_NAME
----------------------------------- ------------------------------
sql tuning advisor AUTO_SQL_TUNING_PROG
auto optimizer stats collection gather_stats_prog
auto space advisor auto_space_advisor_progSQL 2.2 手动干预与任务管理
在Oracle 12c中一个名为SYS_AUTO_SPM_EVOLVE_TASK的新任务被引入用于自动进化现有的SQL计划基线。这个任务作为数据库维护任务的一部分由现有的sql tuning advisor客户端触发。这意味着一旦发现有更优的执行计划系统可以自动对其进行评估并在确认其优势后将其纳入基线之中。
CONN syspdb1 AS SYSDBACOLUMN parameter_name FORMAT A25
COLUMN parameter_value FORMAT a15SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name SYS_AUTO_SPM_EVOLVE_TASK
AND parameter_value ! UNUSED
ORDER BY parameter_name;PARAMETER_NAME PARAMETER_VALUE
------------------------- ---------------
ACCEPT_PLANS TRUE
DAYS_TO_EXPIRE UNLIMITED
DEFAULT_EXECUTION_TYPE SPM EVOLVE
EXECUTION_DAYS_TO_EXPIRE 30
JOURNALING INFORMATION
MODE COMPREHENSIVE
TARGET_OBJECTS 1
TIME_LIMIT 3600
_SPM_VERIFY TRUESQL
尽管自动化的进化过程极大简化了管理负担但在某些情况下手动干预仍然是必要的。例如如果想要阻止新的计划自动被接受可以调整ACCEPT_PLANS参数为FALSE
BEGINDBMS_SPM.set_evolve_task_parameter(task_name SYS_AUTO_SPM_EVOLVE_TASK,parameter ACCEPT_PLANS,value FALSE);
END;
/
DBMS_SPM 包有一个名为 REPORT_AUTO_EVOLVE_TASK 的函数用于显示自动进化任务所采取的行动的信息。如果没有指定参数则会为最新的任务运行生成文本报告。
SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100SELECT DBMS_SPM.report_auto_evolve_task
FROM dual;REPORT_AUTO_EVOLVE_TASK
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------Task Information:---------------------------------------------Task Name : SYS_AUTO_SPM_EVOLVE_TASKTask Owner : SYSDescription : Automatic SPM Evolve TaskExecution Name : EXEC_461Execution Type : SPM EVOLVEScope : COMPREHENSIVEStatus : COMPLETEDStarted : 02/19/2025 15:53:33Finished : 02/19/2025 15:53:35Last Updated : 02/19/2025 15:53:35Global Time Limit : 3600Per-Plan Time Limit : UNUSEDNumber of Errors : 0
---------------------------------------------------------------------------------------------SUMMARY SECTION
---------------------------------------------------------------------------------------------Number of plans processed : 0Number of findings : 0Number of recommendations : 0Number of errors : 0
---------------------------------------------------------------------------------------------
SQL 三、手工演进SPB(SQL Plan Baselines)
在之前的版本中使用EVOLVE_SQL_PLAN_BASELINE函数来实现SQL计划基线的演变。在12c中这已被基于任务的方法所取代通常涉及以下步骤。
CREATE_EVOLVE_TASK
EXECUTE_EVOLVE_TASK
REPORT_EVOLVE_TASK
IMPLEMENT_EVOLVE_TASK 此外以下函数可以与进化任务进行交互
CANCEL_EVOLVE_TASK
RESUME_EVOLVE_TASK
RESET_EVOLVE_TASK 为了展示这一点我们需要创建一个SQL计划基线
3.1 创建并填充测试表。
CONN test/testpdb1DROP TABLE spm_test_tab PURGE;CREATE TABLE spm_test_tab (id NUMBER,description VARCHAR2(50)
);INSERT /* APPEND */ INTO spm_test_tab
SELECT level,Description for || level
FROM dual
CONNECT BY level 10000;
COMMIT; 使用未索引的列查询表这会导致对整个表进行扫描。
SET AUTOTRACE TRACESELECT description
FROM spm_test_tab
WHERE id 99;Execution Plan
----------------------------------------------------------
Plan hash value: 1107868462----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 14 (0)| 00:00:01 |
----------------------------------------------------------------------------------
通过查询V$SQL视图来识别SQL语句的SQL_ID。
CONN syspdb1 AS SYSDBASELECT sql_id
FROM v$sql
WHERE plan_hash_value 1107868462
AND sql_text NOT LIKE EXPLAIN%;SQL_ID
-------------
gat6z1bc6nc2dSQL 3.2 使用此SQL_ID手动加载SQL计划基线。
SET SERVEROUTPUT ON
DECLAREl_plans_loaded PLS_INTEGER;
BEGINl_plans_loaded : DBMS_SPM.load_plans_from_cursor_cache(sql_id gat6z1bc6nc2d);DBMS_OUTPUT.put_line(Plans Loaded: || l_plans_loaded);
END;
/
Plans Loaded: 1PL/SQL procedure successfully completed.SQL
DBA_SQL_PLAN_BASELINES 视图提供了有关 SQL 计划基线的信息。我们可以看到有一个与我们的基准关联的计划该计划已启用并接受。
COLUMN sql_handle FORMAT A20
COLUMN plan_name FORMAT A30SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE %spm_test_tab%
AND sql_text NOT LIKE %dba_sql_plan_baselines%;SQL_HANDLE PLAN_NAME ENA ACC
-------------------- ------------------------------ --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES3.3刷新共享池以强制进行另一个硬解析
然后在ID列上创建索引然后重复查询以查看对执行计划的影响。
CONN syspdb1 AS SYSDBA
ALTER SYSTEM FLUSH SHARED_POOL;CONN test/testpdb1CREATE INDEX spm_test_tab_idx ON spm_test_tab(id);
EXEC DBMS_STATS.gather_table_stats(USER, SPM_TEST_TAB, cascadeTRUE);SET AUTOTRACE TRACESELECT description
FROM spm_test_tab
WHERE id 99;Execution Plan
----------------------------------------------------------
Plan hash value: 1107868462----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 14 (0)| 00:00:01 |
----------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter(ID99)Note
------ SQL plan baseline SQL_PLAN_7qxjk7bch8h5tb65c37c8 used for this statement
请注意查询没有使用新创建的索引即使我们强制执行硬解析。注释解释了SQL计划基线被使用的原因。查看DBA_SQL_PLAN_BASELINES视图我们可以看到原因。
CONN syspdb1 AS SYSDBA
COLUMN sql_handle FORMAT A20
COLUMN plan_name FORMAT A30SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_handle SQL_7b76323ad90440b9;SQL_HANDLE PLAN_NAME ENA ACC
-------------------- ------------------------------ --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5t3652c362 YES NO
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YESSQL
SQL 计划基准线现在包含第二个计划但尚未被接受。 如果您在DBA_SQL_PLAN_BASELINES视图中没有看到新行请返回并重新运行spm_test_tab中的查询直到您这样做。有时服务器需要几次尝试才能注意到对其他计划的需求。 3.4 创建新的进化任务
要使用新计划我们需要等待维护窗口或手动发展SQL计划基线。为此基线创建一个新的进化任务。
SET SERVEROUTPUT ON
DECLAREl_return VARCHAR2(32767);
BEGINl_return : DBMS_SPM.create_evolve_task(sql_handle SQL_7b76323ad90440b9);DBMS_OUTPUT.put_line(Task Name: || l_return);
END;
/
Task Name: TASK_21PL/SQL procedure successfully completed.SQL
执行进化任务。
SET SERVEROUTPUT ON
DECLAREl_return VARCHAR2(32767);
BEGINl_return : DBMS_SPM.execute_evolve_task(task_name TASK_21);DBMS_OUTPUT.put_line(Execution Name: || l_return);
END;
/
Execution Name: EXEC_471PL/SQL procedure successfully completed.SQL 进化任务结果报告。
SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100SELECT DBMS_SPM.report_evolve_task(task_name TASK_21, execution_name EXEC_471) AS output
FROM dual;OUTPUT
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------Task Information:---------------------------------------------Task Name : TASK_21Task Owner : SYSExecution Name : EXEC_471Execution Type : SPM EVOLVEScope : COMPREHENSIVEStatus : COMPLETEDStarted : 02/19/2025 16:38:15Finished : 02/19/2025 16:38:16Last Updated : 02/19/2025 16:38:16Global Time Limit : 2147483646Per-Plan Time Limit : UNUSEDNumber of Errors : 0
---------------------------------------------------------------------------------------------SUMMARY SECTION
---------------------------------------------------------------------------------------------Number of plans processed : 2Number of findings : 2Number of recommendations : 2Number of errors : 0
---------------------------------------------------------------------------------------------DETAILS SECTION
---------------------------------------------------------------------------------------------Object ID : 2Test Plan Name : SQL_PLAN_7qxjk7bch8h5t3652c362Base Plan Name : SQL_PLAN_7qxjk7bch8h5tb65c37c8SQL Handle : SQL_7b76323ad90440b9Parsing Schema : SYSTest Plan Creator : SYSSQL Text : SELECT description FROM spm_test_tab WHERE id 99Execution Statistics:
-----------------------------Base Plan Test Plan---------------------------- ----------------------------Elapsed Time (s): .00001 .000001CPU Time (s): .000016 .000001Buffer Gets: 4 0Optimizer Cost: 13 2Disk Reads: 0 0Direct Writes: 0 0Rows Processed: 0 0Executions: 10 10FINDINGS SECTION
---------------------------------------------------------------------------------------------Findings (1):
-----------------------------1. The plan was verified in 0.01700 seconds. It passed the benefit criterionbecause its verified performance was 14.66841 times better than that of thebaseline plan.Recommendation:
-----------------------------Consider accepting the plan. Executedbms_spm.accept_sql_plan_baseline(task_name TASK_21, object_id 2,task_owner SYS);EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------Baseline Plan
-----------------------------Plan Id : 101Plan Hash Value : 3059496904-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | SPM_TEST_TAB | 1 | 25 | 13 | 00:00:01 |
-----------------------------------------------------------------------------Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(ID99)Test Plan
-----------------------------Plan Id : 102Plan Hash Value : 911393634---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | SPM_TEST_TAB | 1 | 25 | 2 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 | 00:00:01 |
---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
------------------------------------------
* 2 - access(ID99)---------------------------------------------------------------------------------------------Object ID : 3Test Plan Name : SQL_PLAN_7qxjk7bch8h5t3652c362Base Plan Name : SQL_PLAN_7qxjk7bch8h5tb65c37c8SQL Handle : SQL_7b76323ad90440b9Parsing Schema : SYSTest Plan Creator : SYSSQL Text : SELECT description FROM spm_test_tab WHERE id 99Execution Statistics:
-----------------------------Base Plan Test Plan---------------------------- ----------------------------Elapsed Time (s): .000009 .000001CPU Time (s): 0 0Buffer Gets: 4 0Optimizer Cost: 13 2Disk Reads: 0 0Direct Writes: 0 0Rows Processed: 0 0Executions: 10 10FINDINGS SECTION
---------------------------------------------------------------------------------------------Findings (1):
-----------------------------1. The plan was verified in 0.00600 seconds. It passed the benefit criterionbecause its verified performance was 14.66667 times better than that of thebaseline plan.Recommendation:
-----------------------------Consider accepting the plan. Executedbms_spm.accept_sql_plan_baseline(task_name TASK_21, object_id 3,task_owner SYS);EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------Baseline Plan
-----------------------------Plan Id : 103Plan Hash Value : 3059496904-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | SPM_TEST_TAB | 1 | 25 | 13 | 00:00:01 |
-----------------------------------------------------------------------------Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(ID99)Test Plan
-----------------------------Plan Id : 104Plan Hash Value : 911393634---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | SPM_TEST_TAB | 1 | 25 | 2 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 | 00:00:01 |
---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
------------------------------------------
* 2 - access(ID99)--------------------------------------------------------------------------------------------- 3.5 接受新的进化任务
如果演进任务已完成并已报告建议,建议使用 ACCEPT_SQL_PLAN_BASELINE但您应该真正使用 IMPLEMENT_EVOLVE_TASK。
SET SERVEROUTPUT ON
DECLAREl_return NUMBER;
BEGINl_return : DBMS_SPM.implement_evolve_task(task_name TASK_21);DBMS_OUTPUT.put_line(Plans Accepted: || l_return);
END;
/
Plans Accepted: 2PL/SQL procedure successfully completed.SQL DBA_SQL_PLAN_BASELINES 视图显示第二个计划已被接受。
CONN sys/pdb1 AS SYSDBASELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_handle SQL_7b76323ad90440b9;SQL_HANDLE PLAN_NAME ENA ACC
-------------------- ------------------------------ --- ---
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5t3652c362 YES YES
SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YESSQL 重复之前的测试表明现在可以使用更高效的计划。
CONN test/testpdb1SET AUTOTRACE TRACE LINESIZE 130SELECT description
FROM spm_test_tab
WHERE id 99;Execution Plan
----------------------------------------------------------
Plan hash value: 2338891031--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SPM_TEST_TAB | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access(ID99)Note
------ SQL plan baseline SQL_PLAN_7qxjk7bch8h5t3652c362 used for this statement 3.6 删除计划
如果您想删除计划使用 DROP_SQL_PLAN_BASELINE 函数将其丢弃。
CONN syspdb1 AS SYSDBASET SERVEROUTPUT ON
DECLAREl_plans_dropped PLS_INTEGER;
BEGINl_plans_dropped : DBMS_SPM.drop_sql_plan_baseline (sql_handle SQL_7b76323ad90440b9);DBMS_OUTPUT.put_line(Plans Dropped: || l_plans_dropped);
END;
/
Plans Dropped: 2PL/SQL procedure successfully completed.SQL 查看执行计划
SET AUTOTRACE TRACE LINESIZE 130SELECT descriptionFROM spm_test_tabWHERE id 99;Execution Plan
----------------------------------------------------------
Plan hash value: 2338891031--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SPM_TEST_TAB | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access(ID99)
通过这种方式管理员能够细致地控制哪些计划被采纳以及何时采纳从而确保数据库性能始终处于最优状态。 三、总结
综上所述Oracle 12c中的自适应SQL计划管理不仅增强了数据库自我优化的能力还提供了足够的灵活性让管理员根据具体需求进行定制化配置。这对于保持复杂环境下的高效运行至关重要。
