oracle spm buffer get比较过程,Oracle SPM(SQL Plan Management)的使用

1、Oracle优化器辅助手段的发展

Oracle 8:HINT

Oracle 8&9: STORED OUTLINES

Oracle 10: SQL PROFILE

Oracle 11: SPM(SQL Plan Management)

Oracle 12:自适应计划

2、SPM简介

SPM特性有助于保持SQL语句的性能,只允许执行能提高语句性能的执行计划。SPM类似但又不同于STORED OUTLINES。SPM的目的是稳定SQL语句的执行计划,STORED OUTLINES是冻结SQL语句的执行计划,而SPM允许选择新的执行计划,只要能提高SQL语句的性能即可,我们需要SPM的原由可能包括:

①新版本的Oracle(新的优化器版本--使用捕捉和回放来测试影响);

②对优化器统计信息的更改或数据的变化;

③SCHEMA、应用程序或元数据的变化(使用SQL顾问的建议);

④系统设置发生更改(使用数据库回放);

⑤SQL配置文件(SQL Profile)的创建(数据倾斜和相关列的统计信息)。

3、SPM示例

SPM在捕捉计划基线时分为自动捕捉和手动导入,此示例为自动捕捉 (Automatic Initial Plan Capture)

da33ee8b192061e5b7dee920630e615e.png

(1)启动捕获并运行SQL语句,SPM判断语句是否可重复,然后关闭捕获:

在系统或会话级别设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES为true。SQL> create table test_spm as select * from dba_objects;

Table created.

SQL> show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_capture_sql_plan_baselines boolean FALSE

SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

SQL> select owner,object_name from test_spm where object_id= '1061 ';

SQL> select owner,object_name from test_spm where object_id= '1061 ';

SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false;

(2)查询dba_sql_plan_baselines,确定SQL语句在SPM中的状态:SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;

PLAN_NAME SQL_HANDLE ENA ACC FIX

------------------------------ ------------------------------ --- --- ---

MODULE

----------------------------------------------------------------

SQL_TEXT

--------------------------------------------------------------------------------

SQL_PLAN_3wv37cmg2hun7eb1890ae SQL_3e6c6764de286a87 YES YES NO

SQL*Plus

select owner,object_name from test_spm where object_id= '1061 '

注意:enabled为yes,accepted为yes,fixed为on。另外,SQL*Plus是添加这个计划的模块。

SPM通过几个标记来实现对执行计划的控制:Enabled(控制活动):

+ YES (活动的,但不一定会被使用)

+ NO (可以理解为被标记删除)Accepted(控制使用):

+ YES (只有 “Enabled” 并且“Accepted” 的计划才会被选择使用)

+ NO (如果是“Enabled” 那么只有被evolve成“Accepted”才有可能被执)Fixed(控制优先级):

+ YES (如果是“Enabled”并且“Accepted”,会优先选择这个计划,这个计划会被视为不需要改变的)

+ NO (普通的计划,无需优先)Reproduced(有效性):

+ YES (优化器可以使用这个计划)

+ NO (计划无效,比如索引被删除)

(3)增加索引以改变数据环境,并再次运行该语句。

SQL> create index idex_test on test_spm (object_id);

Index created.

SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

SQL> select owner,object_name from test_spm where object_id= '1061 ';

SQL> select owner,object_name from test_spm where object_id= '1061 ';

SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false;

注意:即使关闭了自动捕捉,针对存在baseline的SQL,仍旧会有新的PLAN生成,新的Plan仍会进入Plan History,标记为ENABLED但不是ACCEPTED。

(4)查询dba_sql_plan_baselines,再次确定SQL语句在SPM中的状态:SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;

PLAN_NAME SQL_HANDLE ENA ACC FIX MODULE

------------------------------ ------------------------------ --- --- --- ----------------------------------------------------------------

SQL_TEXT

--------------------------------------------------------------------------------

SQL_PLAN_3wv37cmg2hun7073625f3 SQL_3e6c6764de286a87 YES NO NO SQL*Plus

select owner,object_name from test_spm where object_id= '1061 '

SQL_PLAN_3wv37cmg2hun7eb1890ae SQL_3e6c6764de286a87 YES YES NO SQL*Plus

select owner,object_name from test_spm where object_id= '1061 '

此时:添加了另外一个计划名称(具有相同的SQL_HANDLE),并且accepted是no,表示优化器不会考虑这个计划。

(5)现在设置OPTIMIZER_USE_SQL_PLAN_BASELINES为false,并允许优化器选择执行计划(不一定在计划基线中的某一种)。然后,设置OPTIMIZER_USE_SQL_PLAN_BASELINES为true,并强制优化器在计划基线中仅仅选择一个已接收的执行计划。

关闭SPM查看执行计划:SQL> show parameter OPTIMIZER_USE_SQL_PLAN_BASELINES

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_use_sql_plan_baselines boolean TRUE

SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES=false;

SQL> explain plan for

select owner,object_name from test_spm where object_id= '1061 ';

SQL> select * from table(dbms_xplan.display(null,null, 'basic'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------

Plan hash value: 3093064873

-------------------------------------------------

| Id | Operation | Name |

-------------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST_SPM |

| 2 | INDEX RANGE SCAN | IDEX_TEST |

-------------------------------------------------

此时可以看到执行计划走的是索引的范围扫描。

开启SPM查看执行计划:

SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES=true;

SQL> explain plan for

select owner,object_name from test_spm where object_id= '1061 ';

SQL> select * from table(dbms_xplan.display(null,null, 'basic'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------

Plan hash value: 1145642998

--------------------------------------

| Id | Operation | Name |

--------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS FULL| TEST_SPM |

--------------------------------------

此时SQL执行计划使用了全表扫描,并没有使用accepted为no的索引扫描计划,因为它是未被接收的计划。

(6)使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE演进SQL执行计划,允许accepted状态为NO的计划变成YES,如果这个计划比原有accepted状态已经是YES的执行计划性能更优的话。SQL> set serveroutput on

set long 10000

declare

report clob;

begin

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

sql_handle => 'SQL_3e6c6764de286a87');

DBMS_OUTPUT.PUT_LINE(report);

end;

/

-------------------------------------------------------------------------------

Evolve SQL Plan Baseline

Report

-------------------------------------------------------------------------------

Inputs:

-------

SQL_HANDLE = SQL_3e6c6764de286a87

PLAN_NAME =

TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

VERIFY

= YES

COMMIT = YES

Plan: SQL_PLAN_3wv37cmg2hun7073625f3

------------------------------------

Plan was verified: Time used .12 seconds.

Plan passed performance criterion: 355.2 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): 5.426 .031

175.03

CPU Time(ms): 5.443 .111 49.04

Buffer Gets: 1069 3 356.33

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.

此时查询dba_sql_plan_baselines,SQL_PLAN_3wv37cmg2hun7073625f3这个执行计划此前是未接收状态,现在变成已接收状态:SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;

PLAN_NAME SQL_HANDLE ENA ACC FIX MODULE

------------------------------ ------------------------------ --- --- --- ----------------------------------------------------------------

SQL_TEXT

--------------------------------------------------------------------------------

SQL_PLAN_3wv37cmg2hun7073625f3 SQL_3e6c6764de286a87 YES YES NO SQL*Plus

select owner,object_name from test_spm where object_id= '1061 '

SQL_PLAN_3wv37cmg2hun7eb1890ae SQL_3e6c6764de286a87 YES YES NO SQL*Plus

select owner,object_name from test_spm where object_id= '1061 '

(7)再次设定OPTIMIZER_USE_SQL_PLAN_BASELINES为true时,运行SQL语句,就会使用这个新的已接收计划,因为索引的性能比全表扫描更好。SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES=true;

System altered.

SQL> explain plan for

select owner,object_name from test_spm where object_id= '1061 ';

Explained.

SQL> select * from table(dbms_xplan.display(null,null, 'basic'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------

Plan hash value: 3093064873

-------------------------------------------------

| Id | Operation | Name |

-------------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST_SPM |

| 2 | INDEX RANGE SCAN | IDEX_TEST |

-------------------------------------------------

4、使用固定的SQL计划基线

如果一条SQL语句存在不止一个固定计划,那么使用性能最好的固定计划。SQL> declare

l_plans_altered PLS_INTEGER;

begin

l_plans_altered := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(

sql_handle => 'SQL_3e6c6764de286a87',

plan_name => NULL,

attribute_name => 'fixed',

attribute_value => 'YES');

end;

/

PL/SQL procedure successfully completed.

此时查询dba_sql_plan_baselines,SQL_3e6c6764de286a8的fixed列值已经变为YES。SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;

PLAN_NAME SQL_HANDLE ENA ACC FIX MODULE

------------------------------ ------------------------------ --- --- --- ----------------------------------------------------------------

SQL_TEXT

--------------------------------------------------------------------------------

SQL_PLAN_3wv37cmg2hun7073625f3 SQL_3e6c6764de286a87 YES YES YES SQL*Plus

select owner,object_name from test_spm where object_id= '1061 '

--删除计划declare

l_plans_droped PLS_INTEGER;

begin

l_plans_droped := DBMS_SPM.DROP_SQL_PLAN_BASELINE(

sql_handle => 'SQL_3e6c6764de286a87');

end;

/

SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;

no rows selected

5、手动导入(Manual Plan Capture)

bcf3482ab469eb77b4f0f6326ad04b88.png

导入的baseline都会被自动标记为ACCEPTED,  Oralce提供六种方式把计划导入到sql plan baseline中:

(1)从 SQL Tuning Set STS 导入:

DBMS_SPM.LOAD_PLANS_FROM_SQLSET

(2)从Cursor Cache中装载:

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE

(3)从Stored Outlines中导入:

DBMS_SPM.MIGRATE_STORED_OUTLINE

(4)从内存中存在的计划中导入:

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE;

(5)从staging table表中导入:

dbms_spm.create_stgtab_baseline

(6)通过staging table从另外一个系统中移植:

DBMS_SPM.CREATE_STGTAB_BASELINE

DBMS_SPM.PACK_STGTAB_BASELINE

DBMS_SPM.UNPACK_STGTAB_BASELINE

手动导入示例:

常用的从Cursor Cache中load plan,使用DBMS_SPM.load_plans_from_cursor_cache函数来完成。SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;

no rows selected

SQL> select sql_id,sql_text from v$sql where sql_text like 'select owner,object_name%';

SQL_ID

-------------

SQL_TEXT

--------------------------------------------

62jytznuqhfm2

select owner,object_name from test_spm where object_id= '1061 '

SQL> DECLARE

l_plans_loaded PLS_INTEGER;

BEGIN

l_plans_loaded :=DBMS_SPM.load_plans_from_cursor_cache(sql_id => '62jytznuqhfm2');

END;

/

PL/SQL procedure successfully completed.

查看dba_sql_plan_baselines,视图中多了一条记录,并且显示该计划是accepted=YES状态。SQL> select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;

PLAN_NAME SQL_HANDLE ENA ACC FIX MODULE

------------------------------ ------------------------------ --- --- --- ----------------------------------------------------------------

SQL_TEXT

--------------------------------------------------------------------------------

SQL_PLAN_3wv37cmg2hun7eb1890ae SQL_3e6c6764de286a87 YES YES NO SQL*Plus

select owner,object_name from test_spm where object_id= '1061 '

6、SPM术语

SMB(SQL Management Base,SQL管理基线)相关术语

SPM主要功能:

计划捕捉(Plan Capture)创建SQL计划基线,保存接收的执行计划。

计划选择(Plan selection)新生产的执行计划一开始保存在SQL计划基线中,且处于“未接收”状态。

e35a83d7e1ce8ea5da757d76c88f7ab6.png计划演进(Plan evolution)演进那些在一定阀值下可以提高性能的“未接收”计划,变成接收状态并使用。SQL管理库(SMB):保存SQL计划历史和SQL计划基线,位于SYSAUX表空间,同时也保存SQL Profile。SQL计划历史:SMB的子集,包含SQL生成的已接收和未接收计划。SQL计划基线:SQL计划历史的子集,只包含SQL生成的已接收计划。旗标:用来标记SMB中执行计划的状态。启用 计划历史或计划基线中执行计划状态的默认值。

接收 计划在被认为可用之前需要被接收。

固定 该计划优于其他计划。

重现 CBO对于给定SQL能够重现的执行计划,自动设置为YES,如果不能重现,设置为NO。

自适应计划(Oracle12c 新特性)被认定是自适应计划,没有被接收;一旦计划被接收,这个自适应标志将会变成NO。

与计划的状态相关的术语。

接收的计划:计划必须同时启用和接收,才会被优化器使用。

启用的计划:SQL计划历史或SQL计划基线中执行计划的默认值已启用。计划必须同时启用和接收,才会被优化器使用。

固定的计划:固定的执行计划相比其他的计划优先级高。除非有其他固定的执行计划,这时会选择性能最优的固定执行计划。

其他与SPM相关术语。

AUTOPURGE:如果一个计划连续53周没有被使用,就会自动从计划历史中清除(基于视图DBA_SQL_PLAN_BASELINES中的LAST_EXECUTED日期),可以使用DBMS_SPM.CONFIGURE包修改这个日期。

OPTIMIZER_USE_SQL_PLAN_BASELINES:默认是true。如果SQL语句的计划基线存在,该数据库参数确定是否需要使用它。

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES:默认是false。如果设置成true,那么任何执行的SQL语句都会被添加到SQL计划基线(但不一定是接收的计划)。

DBA_SQL_PLAN_BASELINES:收集已经创建好的计划相关信息视图。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
本火锅店点餐系统采用Java语言和Vue技术,框架采用SSM,搭配Mysql数据库,运行在Idea里,采用小程序模式。本火锅店点餐系统提供管理员、用户两种角色的服务。总的功能包括菜品的查询、菜品的购买、餐桌预定和订单管理。本系统可以帮助管理员更新菜品信息和管理订单信息,帮助用户实现在线的点餐方式,并可以实现餐桌预定。本系统采用成熟技术开发可以完成点餐管理的相关工作。 本系统的功能围绕用户、管理员两种权限设计。根据不同权限的不同需求设计出更符合用户要求的功能。本系统中管理员主要负责审核管理用户,发布分享新的菜品,审核用户的订餐信息和餐桌预定信息等,用户可以对需要的菜品进行购买、预定餐桌等。用户可以管理个人资料、查询菜品、在线点餐和预定餐桌、管理订单等,用户的个人资料是由管理员添加用户资料时产生,用户的订单内容由用户在购买菜品时产生,用户预定信息由用户在预定餐桌操作时产生。 本系统的功能设计为管理员、用户两部分。管理员为菜品管理、菜品分类管理、用户管理、订单管理等,用户的功能为查询菜品,在线点餐、预定餐桌、管理个人信息等。 管理员负责用户信息的删除和管理,用户的姓名和手机号都可以由管理员在此功能里看到。管理员可以对菜品的信息进行管理、审核。本功能可以实现菜品的定时更新和审核管理。本功能包括查询餐桌,也可以发布新的餐桌信息。管理员可以查询已预定的餐桌,并进行审核。管理员可以管理公告和系统的轮播图,可以安排活动。管理员可以对个人的资料进行修改和管理,管理员还可以在本功能里修改密码。管理员可以查询用户的订单,并完成菜品的安排。 当用户登录进系统后可以修改自己的资料,可以使自己信息的保持正确性。还可以修改密码。用户可以浏览所有的菜品,可以查看详细的菜品内容,也可以进行菜品的点餐。在本功能里用户可以进行点餐。用户可以浏览没有预定出去的餐桌,选择合适的餐桌可以进行预定。用户可以管理购物车里的菜品。用户可以管理自己的订单,在订单管理界面里也可以进行查询操作。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值