sql语句绑定方法：

1.使用sqlt中的sql profile绑定sql执行计划。

2.使用baseline绑定执行计划。

1.使用sqlprofile绑定执行计划。

[oracle@lxtrac4 coe]$ sqlplus erwa

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 7 08:12:53 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining and Real Application Testing options

Sql >@coe_xfr_sql_profile.sql 'sql_id' --此步可在内存中找相关的hash_value.选择cost最小的hash_value

drop sql_profile

BEGIN

DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_cxq114jft86bt_2901072614');

END;

/

2.使用baseline绑定执行计划 (相关视图dba_sql_plan_baselines）

参考资料：https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/managing-sql-plan-baselines.html#GUID-AE8CB28A-79CB-4B5F-BAE7-FF54E2EF6330

optimizer_capture_sql_plan_baselines=true 该参数控制系统是否自动捕获sql语句的执行计划。大约耗性能5%左右

如果不开启该参数，可以手动将执行计划加入到baseline

使用DBMS_SPM.LOAD_PLANS_FROM_% function将

1.从内存shared sql area加入到baseline中

VARIABLE v_plan_cnt NUMBER

EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '2cv6qqj01b9wu',plan_hash_value=>'3094410143');

SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED from DBA_SQL_PLAN_BASELINES;

SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC

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

SQL_708dd8dbda3c5c92 select * from emp where empno=7788 SQL_PLAN_713fsvgd3sr4k695cc014 MANUAL-LOAD-FROM-CURSOR-CACHE YES YES

SQL>

2.查看执行计划

SQL> set autot trace

SQL> select * from emp where empno=7788;

Execution Plan

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

Plan hash value: 2949544139

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("EMPNO"=7788)

Note

-----

- SQL plan baseline "SQL_PLAN_713fsvgd3sr4k695cc014" used for this statement ----使用sql_plan_baseline

Statistics

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

0 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

899 bytes sent via SQL*Net to client

596 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

3.如果出现多个执行计划。可以使用手动演进好的执行计划。12c中该演进是SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduled自动进行的

Variable report clob

Exec :report :=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (sql_handle=>'SQL_fe43f3cc55a1f6ea',plan_name=>'SQL_PLAN_gwhzmtjau3xrae27beb1d');

Print report

4.删除sql_plan_baseline

DECLARE

v_dropped_plans number;

BEGIN

v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE (

sql_handle => 'SQL_b6b0d1c71cd1807b',plan_name=>'SQL_PLAN_gwhzmtjau3xrae27beb1d'

);

DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');

END;

/

5.sql_plan_baseline存储在sysaux表空间中。

使用DBMS_SPM.CONFIGURE进行策略配置。

SPACE_BUDGET_PERCENT

PLAN_RETENTION_WEEKS

AUTO_CAPTURE_SQL_TEXT

AUTO_CAPTURE_PARSING_SCHEMA_NAME

AUTO_CAPTURE_MODULE

AUTO_CAPTURE_ACTION

相关配置见官方文档

SELECT PARAMETER_NAME, PARAMETER_VALUE

FROM DBA_SQL_MANAGEMENT_CONFIG

WHERE PARAMETER_NAME LIKE '%AUTO%';