sql语句绑定方法

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%';