mysql执行计划固定_固定执行计划-SQL PROFILE手工绑定

固定(稳定)执行计划

你的应用的功能时快时慢，变化比较大，功能的性能能够保持一种稳定的状态，ORACLE 固定执行计划，采用以下这几种方式

oracle 9i使用 Outline

oracle 10g采用 sql profile

oracle 11g增加了sql plan manage

oracle 10g采用 sql profile ：两种模式

从SQL语句历史的执行计划，找到一个合理的，进行绑定

还有一种无法从历史的执行计划找到合理的，只能手工构造进行绑定

提供脚本

create_sql_profile

提供绑定shared pool中已有的执行计划中，找一个绑定或自己构造一个绑定

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

--

-- File name: create_sql_profile.sql

--

-- Purpose: Create SQL Profile based on Outline hints in V$SQL.OTHER_XML.

--

-- Author: Kerry Osborne

--

-- Usage: This scripts prompts for four values.

--

-- sql_id: the sql_id of the statement to attach the profile to (must be in the shared pool),if sql_id is not shared pool,must be bulid sql plan

--

-- child_no: the child_no of the statement from v$sql

--

-- new_sql_id:需要绑定的SQL语句

--

-- profile_name: the name of the profile to be generated

--

-- category: the name of the category for the profile

--

-- force_macthing: a toggle to turn on or off the force_matching feature

--

-- Description:

--

-- Based on a script by Randolf Giest.

--

-- Mods: This is the 2nd version of this script which removes dependency on rg_sqlprof1.sql.

--

-- See kerryosborne.oracle-guy.com for additional information.

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

--

-- @rg_sqlprof1 '&&sql_id' &&child_no '&&new_sql_id' '&&category' '&force_matching'

set feedback off

set sqlblanklines on

accept sql_id -

prompt 'Enter value for sql_id: ' -

default 'X0X0X0X0'

accept child_no -

prompt 'Enter value for child_no (0): ' -

default '0'

accept new_sql_id -

prompt 'Enter value for new_sql_id: ' -

default '0'

accept profile_name -

prompt 'Enter value for profile_name (PROF_sqlid_planhash): ' -

default 'X0X0X0X0'

accept category -

prompt 'Enter value for category (DEFAULT): ' -

default 'DEFAULT'

accept force_matching -

<