Database - 4. page

在对SQL进行性能分析时，我们通常需要获取SQL执行时使用的查询条件值，从而准确的获取SQL执行时读取数据的情况。那么如何获取ORACLE数据库中已经执行的SQL的绑定变量值呢？

1.在ORACLE 9i无绑定变量相关视图，所以只有事先用10046做TRACE才可以得到已执行目标SQL中绑定变量的值。
2.在ORACLE10G/11G中得到已执行目标SQL中的绑定变量值：
查询视图 v$sql_bind_capture
3.如果此视图中查不到，可能是对应shared cursor已经不在SHARED POOL。此时可以尝试去awr相关的数据字典表dba_hist_sqlstat/dba_hist_sqlbind中查询

3.如果SQL正在执行，还可以对进行进行10046trace跟踪的方法获得。

4.SQL中绑定变量值被ORACLE捕获v$sql_bind_capture的条件：
–注意ORACLE只捕获目标SQL的WHERE条件中的绑定变量值，对于INSERT语句不会捕获values子句对应的绑定变量输入值。
1.硬解析时   2.以软解析/软软解析方式执行时，默认情况下至少间隔15分钟会捕获一次。
5.常用查询绑定变量值命令：
col value_string for a20
col name for a20
col bind1 for a20

set linesize 200
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select * from emp where empno%’;
SQL> select sql_id,name,position,datatype_string,MAX_LENGTH,last_captured,value_string from v$sql_bind_capture where sql_ID=’0m472wx7184s6′;
以上为从内存中读取，以下为从AWR相关视图读取：
SQL> select sql_id,dbms_sqltune.extract_bind(bind_data,1).value_string bind1 from dba_hist_sqlstat where sql_id=’0m472wx7184s6′ order by snap_id;
SQL> select snap_id,name,position,value_string,MAX_LENGTH,last_captured,was_captured from dba_hist_sqlbind where sql_id=’0m472wx7184s6′ order by snap_id;
例如：
col value_string for a30
col name for a20
col bind1 for a20
set linesize 200
set pagesize 1000
select INSTANCE_NUMBER,snap_id,sql_id,name,position,MAX_LENGTH,value_string,to_char(last_captured,’yyyy/mm/dd hh24:mi:ss’),was_captured
from dba_hist_sqlbind where sql_id=’grwrscmzrya34′ and snap_id between 23901 and 23905 order by INSTANCE_NUMBER,snap_id desc;
–从AWR中查询此SQL的历史执行计划对应的绑定变量值变动情况
col value_string for a30
col name for a10
col bind1 for a20
set linesize 200
set pagesize 1000
select a.snap_id,a.sql_id,b.plan_hash_value,a.name,a.position,a.MAX_LENGTH,a.value_string,
to_char(a.last_captured,’yyyy/mm/dd hh24:mi:ss’),was_captured
from dba_hist_sqlbind a,dba_hist_sqlstat b
where a.sql_id=’43vsbga68xm6n’ and a.sql_id=b.sql_id and a.snap_id = b.snap_id and a.snap_id>44500
order by snap_id desc;

实验A：15分钟内两次硬解析，验证是否每次硬解析时ORACLE都会捕获绑定变量值。

create table t1(id number,name varchar2(10));
insert into t1 values(1,’aaa’);
insert into t1 values(2,’bbb’);
commit;
select * from t1;
——————-
var a varchar2(10);
exec :a :=’aaa’;
select * from t1 where name=:a;

SQL> set linesize 200
SQL> col sql_text for a50
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select * from t1 where%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
————————————————– ————- ————- ———-
select * from t1 where name=:a cs3ssn1qvtyd8 1 1

SQL> col value_string for a20
SQL> col name for a20
SQL> set linesize 200
SQL> select sql_id,name,position,datatype_string,to_char(last_captured,’yyyy/mm/dd hh24:mi:ss’),value_string from v$sql_bind_capture where sql_ID=’cs3ssn1qvtyd8′;

SQL_ID NAME POSITION DATATYPE_STRING TO_CHAR(LAST_CAPTUR VALUE_STRING
————- ——————– ———- ————— ——————- ——————–
cs3ssn1qvtyd8 :A 1 VARCHAR2(32) 2015/12/30 10:01:46 aaa

select to_char(sysdate,’yyyy/mm/dd hh24:mi:ss’) from dual;
################刷新共享池，再次执行硬解析：alter system flush shared_pool;
var a varchar2(10);
exec :a :=’bbb’;
select * from t1 where name=:a;

SQL> select sql_id,name,position,datatype_string,to_char(last_captured,’yyyy/mm/dd hh24:mi:ss’),value_string from v$sql_bind_capture where sql_ID=’cs3ssn1qvtyd8′;

SQL_ID NAME POSITION DATATYPE_STRING TO_CHAR(LAST_CAPTUR VALUE_STRING
————- ——————– ———- ————— ——————- ——————–
cs3ssn1qvtyd8 :A 1 VARCHAR2(32) 2015/12/30 10:03:36 bbb

#####################
验证SELECT可捕获INSERT不行：
SQL> select * from t1;

ID NAME
———- ———-
1 1
1 1
1 bys1

SQL> var a varchar2(10);
SQL> exec :a :=’bys1′;

PL/SQL procedure successfully completed.

SQL> select * from t1 where name=:a;

ID NAME
———- ———-
1 bys1

SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select * from t1 where name=%’;

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
———————————– ————- ————- ———-
select * from t1 where name=:a cs3ssn1qvtyd8 1 1
SQL> col name for a10
SQL> col value_string for a10
SQL> col DATATYPE_STRING for a10
SQL> set linesize 180 pagesize 180
SQL> col DATATYPE_STRING for a14
SQL> select sql_id,name,position,datatype_string,to_char(last_captured,’yyyy/mm/dd hh24:mi:ss’),value_string from v$sql_bind_capture where sql_ID=’cs3ssn1qvtyd8′;

SQL_ID NAME POSITION DATATYPE_STRIN TO_CHAR(LAST_CAPTUR VALUE_STRI
————- ———- ———- ————– ——————- ———-
cs3ssn1qvtyd8 :A 1 VARCHAR2(32) 2018/05/07 22:48:30 bys1
—-#############3
#####################################
###############INSERT测试不能捕获值
SQL> var a varchar2(10);
exec :a :=’bbb’;
SQL>
PL/SQL procedure successfully completed.

SQL> insert into t1 values(222,:a);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘insert into t1 values(222%’;

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
———————————– ————- ————- ———-
insert into t1 values(222,:a) 551x884g3s28y 1 1

SQL> select sql_id,name,position,datatype_string,to_char(last_captured,’yyyy/mm/dd hh24:mi:ss’),value_string from v$sql_bind_capture where sql_ID=’551x884g3s28y’;

SQL_ID NAME POSITION DATATYPE_STRIN TO_CHAR(LAST_CAPTUR VALUE_STRI
————- ———- ———- ————– ——————- ———-
551x884g3s28y :A 1 VARCHAR2(32)

SQL> SQL> select sql_id,name,position,datatype_string,to_char(last_captured,’yyyy/mm/dd hh24:mi:ss’),value_string from v$sql_bind_capture where sql_ID=’frajw750xmssv’;

SQL_ID NAME POSITION DATATYPE_STRIN TO_CHAR(LAST_CAPTUR VALUE_STRI
————- ———- ———- ————– ——————- ———-
frajw750xmssv :ID 1 NUMBER
frajw750xmssv :NAME 2 VARCHAR2(4000)
frajw750xmssv :ID 1 NUMBER
frajw750xmssv :NAME 2 VARCHAR2(2000)

 

实验B，验证绑定变量分级时同样可以捕获

–alter system flush shared_pool;
var a varchar2(10);
exec :a :=’bbb’;
select * from t1 where name=:a;
select plan_hash_value,child_number,SQL_ID,EXECUTIONS,loads,address,CHILD_ADDRESS from v$sql where sql_TEXT like ‘select * from t1 where%’;
—-
SQL> select * from t1 where name=:a;
ID NAME
———- ——————–
2 bbb
SQL> select plan_hash_value,child_number,SQL_ID,EXECUTIONS,loads,address,CHILD_ADDRESS from v$sql where sql_TEXT like ‘select * from t1 where%’;
PLAN_HASH_VALUE CHILD_NUMBER SQL_ID EXECUTIONS LOADS ADDRESS CHILD_AD
————— ———— ————- ———- ———- ——– ——–
3617692013 0 cs3ssn1qvtyd8 1 1 2D869EBC 2D869DE0
SQL> select sql_id,name,position,datatype_string,to_char(last_captured,’yyyy/mm/dd hh24:mi:ss’),value_string from v$sql_bind_capture where sql_ID=’cs3ssn1qvtyd8′;
SQL_ID NAME POSITION DATATYPE_STRING TO_CHAR(LAST_CAPTUR VALUE_STRING
————- ——————– ———- ————— ——————- ——————–
cs3ssn1qvtyd8 :A 1 VARCHAR2(32) 2015/12/30 10:17:12 bbb
—————–
var a varchar2(40);
exec :a :=’aaa’;
select * from t1 where name=:a;
—-
SQL> var a varchar2(40);
SQL> exec :a :=’aaa’;
PL/SQL procedure successfully completed.
SQL> select * from t1 where name=:a;
ID NAME
———- ——————–
1 aaa
SQL> select plan_hash_value,child_number,SQL_ID,EXECUTIONS,loads,address,CHILD_ADDRESS from v$sql where sql_TEXT like ‘select * from t1 where%’;
PLAN_HASH_VALUE CHILD_NUMBER SQL_ID EXECUTIONS LOADS ADDRESS CHILD_AD
————— ———— ————- ———- ———- ——– ——–
3617692013 0 cs3ssn1qvtyd8 1 1 2D869EBC 2D869DE0
3617692013 1 cs3ssn1qvtyd8 1 1 2D869EBC 32497674
SQL> select sql_id,name,position,datatype_string,to_char(last_captured,’yyyy/mm/dd hh24:mi:ss’),value_string from v$sql_bind_capture where sql_ID=’cs3ssn1qvtyd8′;
SQL_ID NAME POSITION DATATYPE_STRING TO_CHAR(LAST_CAPTUR VALUE_STRING
————- ——————– ———- ————— ——————- ——————–
cs3ssn1qvtyd8 :A 1 VARCHAR2(128) 2015/12/30 10:18:27 aaa
cs3ssn1qvtyd8 :A 1 VARCHAR2(32) 2015/12/30 10:17:12 bbb

实验3：验证硬解析时ORACLE捕获绑定变量值及15分钟后软解析也捕获。

SQL> var a number;
SQL> exec :a :=7369;
PL/SQL procedure successfully completed.
SQL> select * from emp where empno=:a;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——————- ———- ———- ———-
7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20
col value_string for a20
col name for a20
set linesize 200
SQL> select sql_id,name,position,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_ID=’0m472wx7184s6′;
SQL_ID NAME POSITION DATATYPE_STRING LAST_CAPTURED VALUE_STRING
————- ——————– ———- —————————— ——————- ——————–
0m472wx7184s6 :A 1 NUMBER 2015/01/01 20:15:26 7369
################
第一次执行SQL，硬解析，绑定变量值被捕获。
下面在未到15分钟时再次执行SQL语句，可以看到绑定变量值未被捕获
################################################################
SQL> exec :a :=7499;
PL/SQL procedure successfully completed.
SQL> select * from emp where empno=:a;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——————- ———- ———- ———-
7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select * from emp where empno%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
—————————————- ————- ————- ———-
select * from emp where empno=:a 0m472wx7184s6 1 3
SQL> select sql_id,name,position,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_ID=’0m472wx7184s6′;
SQL_ID NAME POSITION DATATYPE_STRING LAST_CAPTURED VALUE_STRING
————- ——————– ———- —————————— ——————- ——————–
0m472wx7184s6 :A 1 NUMBER 2015/01/01 20:15:26 7369
SQL> select sysdate from dual;
SYSDATE
——————-
2015/01/01 20:28:26
##############################
SQL> exec :a :=7566;
PL/SQL procedure successfully completed.
SQL> select * from emp where empno=:a;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——————- ———- ———- ———-
7566 JONES MANAGER 7839 1981/04/02 00:00:00 2975 20
SQL> select sql_text,sql_id,version_count,executions from $sqlarea where sql_text like ‘select * from emp where empno%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
—————————————- ————- ————- ———-
select * from emp where empno=:a 0m472wx7184s6 1 4
SQL> select sql_id,name,position,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_ID=’0m472wx7184s6′;
SQL_ID NAME POSITION DATATYPE_STRING LAST_CAPTURED VALUE_STRING
————- ——————– ———- —————————— ——————- ——————–
0m472wx7184s6 :A 1 NUMBER 2015/01/01 20:33:08 7566
#################################################################
从AWR的相关数据库字典视图中查询
###################3333
SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
SQL> col bind1 for a20
SQL> select sql_id,dbms_sqltune.extract_bind(bind_data,1).value_string bind1 from dba_hist_sqlstat where sql_id=’0m472wx7184s6′ order by snap_id;
SQL_ID BIND1
————- ——————–
0m472wx7184s6 7566
SQL> select snap_id,name,position,value_string,last_captured,was_captured from dba_hist_sqlbind where sql_id=’0m472wx7184s6′ order by snap_id;
SNAP_ID NAME POSITION VALUE_STRING LAST_CAPTURED WAS
———- ——————– ———- ——————– ——————- —
122 :A 1 7566 2015/01/01 20:33:08 YES
##############################3

关于INSERT语句绑定变量值的查询：
直接在INSERT语句的values中使用绑定变量，ORACLE不会捕获此绑定变量值。
SQL> insert into t5 values(:a,:b);
1 row created.
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘insert into t5 values%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
—————————————- ————- ————- ———-
insert into t5 values(:a,:b) b24jrkhpgudt6 1 1
SQL> select sql_id,name,position,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_ID=’b24jrkhpgudt6′;
SQL_ID NAME POSITION DATATYPE_STRING LAST_CAPTURED VALUE_STRING
————- ——————– ———- —————————— ——————- ——————–
b24jrkhpgudt6 :A 1 NUMBER
b24jrkhpgudt6 :B 2 VARCHAR2(32)
##################################
INSERT语句中使用子查询，子查询的WHERE条件中使用绑定变量，可以被ORACLE捕获到。
SQL> insert into t5 select * from t5 where id=:a and name=:b;
1 row created.
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘insert into t5 select%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
—————————————- ————- ————- ———-
insert into t5 select * from t5 where id 6vzfdf6fn990d 1 1
=:a and name=:b

SQL> select sql_id,name,position,datatype_string,last_captured,value_string from v$sql_bind_capture where sql_ID=’6vzfdf6fn990d’;
SQL_ID NAME POSITION DATATYPE_STRING LAST_CAPTURED VALUE_STRING
————- ——————– ———- —————————— ——————- ——————–
6vzfdf6fn990d :A 1 NUMBER 2015/01/01 21:13:59 777
6vzfdf6fn990d :B 2 VARCHAR2(32) 2015/01/01 21:13:59 bys

ORACLE获取SQL语句执行时的绑定变量值的方法汇总

游标共享cursor sharing是指 shared cursor间的共享，就是重用存储在child cursor中的解析树和执行计划，无需硬解析。

常规游标共享：

一些OLTP系统在开发阶段未考虑使用绑定变量，系统上线后出现问题，此时改代码代价较大，这种情况下，从8I开始引入了常规游标共享：仅SQL文本中WHERE条件或INSERT时VALUES子句中的具体输入值不同的目标SQL之间共享解析树和执行计划。 可以有效降低硬解析数量同时对应用透明。
当开启常规游标共享时，ORACLE在实际解析目标SQL之间时会使用系统产生的绑定变量来替换目标SQL的SQL文本中WHERE条件或INSERT时VALUES子句中的具体输入值，替换后实际执行的SQL就已经使用绑定变量的入写后的等价SQL，这个替换过程ORACLE自动完成。
目前，ORACLE数据库系统产生的绑定变量命名规则是SYS_B_n.
常规游标共享受参数cursor_sharing控制，此参数的值为EXACT,SIMILAR,FORCE,含义为：
EXACT
是默认值，此时不使用常规游标共享。
SIMILAR
在9I引入，在12C后不被支持，MOS文档116901.1
此时，ORACLE在实际解析目标SQL之间时会使用系统产生的绑定变量来替换目标SQL的SQL文本中WHERE条件或INSERT时VALUES子句中的具体输入值，但是替换后并不一定使用，ORACLE会对认为安全的谓词条件重用执行计划，不安装的则硬解析。所以会出现同一个父游标下多个子游标且子游标中存储的解析树和执行计划可能一样(11G自适应游标共享引入此种情况不适用)。
安全的谓词条件：目标SQL的执行计划不随谓词条件输入变化而变化就是安全的，如主键的等值查询
而对于谓词条件中范围查询，则是不安全的如> >= < <= % between及目标列有直方图的等值查询等
在10G及以后，ORACLE会自动收集直方图统计信息，意味着出现不安全谓词概率大为增加，所以不建议设置为SIMILAR(此参数还可能导致BUG)。

FORCE
此时，ORACLE在实际解析目标SQL之间时会使用系统产生的绑定变量来替换目标SQL的SQL文本中WHERE条件或INSERT时VALUES子句中的具体输入值，并且无条件重用之前硬解析时的解析树和执行计划。(11G自适应游标共享引入后此种情况不适用)。

实验如下：–ORACLE 10.2.0.1版本

cursor_sharing参数值为EXACT，不会使用常规游标共享

SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create index idx_t1 on t1(object_id);
Index created.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS ( ownname=>’BYS’,tabname =>’t1′,estimate_percent=>100,method_opt=>’for all columns size 1′,cascade=>TRUE,no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> show parameter cursor_sh
NAME TYPE VALUE
———————————— ———– ——————————
cursor_sharing string EXACT
SQL> select object_name from t1 where object_id=1;
no rows selected
SQL> select object_name from t1 where object_id=0;
no rows selected

col sql_text for a80
set linesize 150
select sql_text,sql_id,version_count from v$sqlarea where sql_text like ‘select object_name from t1 where object_id%’;
SQL_TEXT SQL_ID VERSION_COUNT
——————————————————————————– ————- ————-
select object_name from t1 where object_id=1 gkfzppmmk1aca 1
select object_name from t1 where object_id=0 ggk51gg6hmnq8 1

 

cursor_sharing参数值为EXACT，会使用常规游标共享

SQL> alter session set cursor_sharing=’SIMILAR’;
Session altered.
SQL> select object_id,object_name from t1 where object_id=0;
no rows selected
SQL> select object_id,object_name from t1 where object_id=1;
no rows selected
SQL> select sql_text,sql_id,version_count,EXECUTIONS from v$sqlarea where sql_text like ‘select object_id,object_name from t1 where object_id%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select object_id,object_name from t1 where object_id=:”SYS_B_0″ gyyd6hs3hjjry 1 2

使用一个范围查询，因为使用不安全的谓词–BETWEEN

此时就均使用硬解析，就出现出现同一个父游标下多个子游标且子游标中存储的解析树和执行计划可能一样
SQL> select count(*) from t1 where object_id between 0 and 3;
COUNT(*)
———-
2
SQL> select count(*) from t1 where object_id between 2 and 4;
COUNT(*)
———-
3
SQL> select sql_text,sql_id,version_count,EXECUTIONS from v$sqlarea where sql_text like ‘select count(*) from t1 where object_id between%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select count(*) from t1 where object_id between :”SYS_B_0″ and :”SYS_B_1″ cprsstt8gyg9m 2 2
SQL> select plan_hash_value,child_number from v$sql where sql_id=’cprsstt8gyg9m’;
PLAN_HASH_VALUE CHILD_NUMBER
————— ————
2351893609 0
2351893609 1
col plan_table_output for a100
set long 900
set pagesize 100
select * from table(dbms_xplan.display_cursor(‘cprsstt8gyg9m’,0,’advanced’));
PLAN_TABLE_OUTPUT
—————————————————————————————————-
SQL_ID cprsstt8gyg9m, child number 0
————————————-
select count(*) from t1 where object_id between :”SYS_B_0″ and
:”SYS_B_1″
Plan hash value: 2351893609
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T1 | 2 | 8 | 2 (0)| 00:00:01 |
—————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
3 – SEL$1 / T1@SEL$1
Outline Data

Peeked Binds (identified by position):
————————————–
1 – :SYS_B_0 (NUMBER): 0
2 – :SYS_B_1 (NUMBER): 3
Predicate Information (identified by operation id):
—————————————————
2 – filter(:SYS_B_0<=:SYS_B_1)
3 – access(“OBJECT_ID”>=:SYS_B_0 AND “OBJECT_ID”<=:SYS_B_1)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]
SQL> select * from table(dbms_xplan.display_cursor(‘cprsstt8gyg9m’,1,’advanced’));
PLAN_TABLE_OUTPUT
—————————————————————————————————-
SQL_ID cprsstt8gyg9m, child number 1
————————————-
select count(*) from t1 where object_id between :”SYS_B_0″ and
:”SYS_B_1”
Plan hash value: 2351893609
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T1 | 3 | 12 | 2 (0)| 00:00:01 |
—————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
3 – SEL$1 / T1@SEL$1
Outline Data

Peeked Binds (identified by position):
————————————–
1 – :SYS_B_0 (NUMBER): 2
2 – :SYS_B_1 (NUMBER): 4
Predicate Information (identified by operation id):
—————————————————
2 – filter(:SYS_B_0<=:SYS_B_1)
3 – access(“OBJECT_ID”>=:SYS_B_0 AND “OBJECT_ID”<=:SYS_B_1)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]
52 rows selected.

cursor_sharing参数值为FORCE，即使使用不安全的谓词条件，也会使用常规游标共享

SQL> alter session set cursor_sharing=’FORCE’;
Session altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from t1 where object_id between 2 and 4;
COUNT(*)
———-
3
SQL> select count(*) from t1 where object_id between 0 and 2;
COUNT(*)
———-
1
SQL> select sql_text,sql_id,version_count,EXECUTIONS from v$sqlarea where sql_text like ‘select count(*) from t1 where object_id between%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select count(*) from t1 where object_id between :”SYS_B_0″ and :”SYS_B_1″ cprsstt8gyg9m 1 2

Oracle数据库游标共享参数cursor sharing研究与测试

绑定变量窥视功能：

从ORACLE9I引入，是否启用由隐含参数_optim_peek_user_binds控制，默认是TRUE,开启。
当绑定变量窥视启用以后，每当ORACLE以硬解析的方式解析使用绑定变量的目标SQL时，ORACLE会实际窥视PEEKING一下对应绑定变量的具体输入值，并以此为标准，确定可选择率selectivity和结果集的行数cardinality进而确定执行计划。
–这个窥视动作只有在硬解析时才有。(只需要对表做DDL操作–推荐添加注释的COMMENT语句，再次执行时会硬解析)
–另一种方式是删除shared pool 库缓存中的shared cursor。在10.2.0.4版本及以上可以使用，但是在10.2.0.4中使用则需要先设置5614566 EVENT才能生效。

绑定变量窥视功能推出为解决什么样的业务场景：

随着具体输入值不同，目标SQL的WHERE条件的可选择率selectivity和结果集的行数cardinality可能随之发生变化，而可选择率selectivity和结果集的行数cardinality的值会影响CBO对于相关执行步骤成本值的估算，从而影响CBO对目标SQL执行计划的选择，目标SQL的执行计划可能变化。
对使用绑定变量的SQL，ORACLE使用以下两种方法来决定执行计划：
1.使用绑定变量窥视
2.如果不使用绑定变量窥视，对于可选择率可能会随着具体输入值的不同而不同的谓词条件使用默认的可选择率5%。

绑定变量窥视相关的查询语句：

SQL> select sql_text,sql_id,address,hash_value from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
SQL_TEXT SQL_ID ADDRESS HASH_VALUE
——————————————————————————– ————- ——– ———-
select count(*) from t1 where object_id between 😡 and :y 9dhu3xk2zu531 3967E8D0 2247955553
SQL> exec sys.dbms_shared_pool.purge(‘3967E8D0,2247955553′,’c’);
PL/SQL procedure successfully completed.
SQL> select sql_text,sql_id,address,hash_value from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
no rows selected
–关闭绑定变量窥视的方法是 alter session set “_optim_peek_user_binds”=false;–可以动态调整
NAME VALUE DESCRIPTION
—————————————- —————————— —————————————-
_optim_peek_user_binds FALSE enable peeking of user binds
SQL> alter system set “_optim_peek_user_binds”=false;
System altered.
NAME VALUE DESCRIPTION
—————————————- —————————— —————————————-
_optim_peek_user_binds FALSE enable peeking of user binds
SQL> alter system set “_optim_peek_user_binds”=true;
System altered.
NAME VALUE DESCRIPTION
—————————————- —————————— —————————————-
_optim_peek_user_binds TRUE enable peeking of user binds
————————————————
优点：
避免使用默认的可选择率，有更大可能得到正确的执行计划。
缺点：
执行计划被固定，此执行计划取决于硬解析时的输入值，可能不是最做优。(11G自适应游标共享后有所缓解)。同时如果再次发生硬解析，执行计划可以变化，如变差则可能影响系统性能。

如下绑定变量窥视测试例子：

1.首先创建测试环境
在WHERE条件上使用两个绑定变量：where object_id between 999 and 1000;
测试出不使用绑定变量时–999 and 1000、1 and 80000的不同查询条件的执行计划是不同的。

2.测试绑定变量窥视开启时，不同绑定变量值在硬解析时会生成新执行计划

3.测试绑定变量窥视关闭时，不同绑定变量值在硬解析时不会生成新执行计划
#######################################
create table t1 as select * from dba_objects;
create index idx_t1 on t1(object_id);
select count(*) from t1;
COUNT(*)
———-
86756
SQL> select count(distinct(object_id)) from t1;
COUNT(DISTINCT(OBJECT_ID))
————————–
86756
exec DBMS_STATS.GATHER_TABLE_STATS ( ownname=>’BYS’,tabname =>’t1′,estimate_percent=>100,method_opt=>’for all columns size 1′,cascade=>TRUE,no_invalidate=>false);
SQL> select count(*) from t1 where object_id between 999 and 1000;
COUNT(*)
———-
2
SQL> select count(*) from t1 where object_id between 999 and 60000;
COUNT(*)
———-
58664
SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
SQL_TEXT SQL_ID VERSION_COUNT
—————————————————————————————————- ————- ————-
select count(*) from t1 where object_id between 999 and 1000 5gu397922cuqd 1
select count(*) from t1 where object_id between 999 and 60000 b8xxw70vja3tn 1
查看执行计划：
col plan_table_output for a100
set long 900
set pagesize 100
select * from table(dbms_xplan.display_cursor(‘5gu397922cuqd’,0,’advanced’));
PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID 5gu397922cuqd, child number 0
————————————-
select count(*) from t1 where object_id between 999 and 1000
Plan hash value: 1970818898
—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| IDX_T1 | 3 | 15 | 2 (0)| 00:00:01 |
—————————————————————————-
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
2 – SEL$1 / T1@SEL$1
Outline Data
————-
Predicate Information (identified by operation id):
—————————————————
2 – access(“OBJECT_ID”>=999 AND “OBJECT_ID”<=1000)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]

QL> select count(*) from t1 where object_id between 999 and 60000;

COUNT(*)
———-
58664

SQL> select * from table(dbms_xplan.display_cursor(null,null,’advanced’));

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
SQL_ID b8xxw70vja3tn, child number 0
————————————-
select count(*) from t1 where object_id between 999 and 60000
Plan hash value: 2101382132
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | | | 54 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_T1 | 57682 | 281K| 54 (2)| 00:00:01 |
——————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
2 – SEL$1 / T1@SEL$1
Outline Data
————-
Predicate Information (identified by operation id):
—————————————————
2 – filter((“OBJECT_ID”<=60000 AND “OBJECT_ID”>=999))
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]
############################################################################
接下来使用绑定变量查看：
SQL> var x number;
SQL> var y number;
SQL> exec 😡 :=999
PL/SQL procedure successfully completed.
SQL> exec :y :=1000
SQL> conn bys/bys
Connected.
SQL> select count(*) from t1 where object_id between 😡 and :y;
COUNT(*)
———-
2
col sql_text for a80
set linesize 150
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select count(*) from t1 where object_id between 😡 and :y 9dhu3xk2zu531 1 1

SQL> select * from table(dbms_xplan.display_cursor(‘9dhu3xk2zu531′,0,’advanced’));
PLAN_TABLE_OUTPUT
———————————————————————————————————
SQL_ID 9dhu3xk2zu531, child number 0
————————————-
select count(*) from t1 where object_id between 😡 and :y
Plan hash value: 2351893609
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T1 | 3 | 15 | 2 (0)| 00:00:01 |
—————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
3 – SEL$1 / T1@SEL$1
Outline Data
————-

Peeked Binds (identified by position):
————————————–
1 – :X (NUMBER): 999
2 – :Y (NUMBER): 1000
Predicate Information (identified by operation id):
—————————————————
2 – filter(:Y>=:X)
3 – access(“OBJECT_ID”>=:X AND “OBJECT_ID”<=:Y)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]
52 rows selected.

SQL> exec :y :=60000
PL/SQL procedure successfully completed.
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select count(*) from t1 where object_id between 😡 and :y 9dhu3xk2zu531 1 1
SQL> select count(*) from t1 where object_id between 😡 and :y;
COUNT(*)
———-
58664
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select count(*) from t1 where object_id between 😡 and :y 9dhu3xk2zu531 1 2

SQL> select * from table(dbms_xplan.display_cursor(‘9dhu3xk2zu531′,0,’advanced’));
PLAN_TABLE_OUTPUT
————————————————————————————
SQL_ID 9dhu3xk2zu531, child number 0
————————————-
select count(*) from t1 where object_id between 😡 and :y
Plan hash value: 2351893609
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T1 | 3 | 15 | 2 (0)| 00:00:01 |
—————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
3 – SEL$1 / T1@SEL$1
Outline Data
————-
Peeked Binds (identified by position):
————————————–
1 – :X (NUMBER): 999
2 – :Y (NUMBER): 1000
Predicate Information (identified by operation id):
—————————————————
2 – filter(:Y>=:X)
3 – access(“OBJECT_ID”>=:X AND “OBJECT_ID”<=:Y)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]
52 rows selected.

#############################3
对表做DDL操作，重新执行语句，会做绑定变量窥视。
SQL> comment on table t1 is ‘test1’;
Comment created.
SQL> select count(*) from t1 where object_id between 😡 and :y;
COUNT(*)
———-
58664
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like ‘select count(*) from t1 where%’;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
——————————————————————————– ————- ————- ———-
select count(*) from t1 where object_id between 😡 and :y 9dhu3xk2zu531 1 1
SQL> select * from table(dbms_xplan.display_cursor(‘9dhu3xk2zu531′,0,’advanced’));
PLAN_TABLE_OUTPUT
———————————————————————————————————–
SQL_ID 9dhu3xk2zu531, child number 0
————————————-
select count(*) from t1 where object_id between 😡 and :y
Plan hash value: 1410530761
———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————
| 0 | SELECT STATEMENT | | | | 54 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX FAST FULL SCAN| IDX_T1 | 57682 | 281K| 54 (2)| 00:00:01 |
———————————————————————————

############################################
关闭绑定变量窥视后验证：
不会生成INDEX FAST FULL SCAN较好的执行计划。
SQL> show parameter peek
NAME TYPE VALUE
———————————— ———– ——————————
_optim_peek_user_binds boolean FALSE

SQL> comment on table t1 is ‘test1’;
Comment created.

SQL> var x number;
SQL> var y number;
SQL> exec 😡 :=1
PL/SQL procedure successfully completed.

SQL> exec :y :=80000
PL/SQL procedure successfully completed.

SQL> col plan_table_output for a160
SQL> set long 9000
SQL> set linesize 200
SQL> set pagesize 1000
SQL> select * from table(dbms_xplan.display_cursor(‘9dhu3xk2zu531′,0,’advanced’));
PLAN_TABLE_OUTPUT
—————————————————————————————————————————————————————-
SQL_ID 9dhu3xk2zu531, child number 0
————————————-
select count(*) from t1 where object_id between 😡 and :y
Plan hash value: 3858015043
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T1 | 25 | 125 | 2 (0)| 00:00:01 |
—————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1
3 – SEL$1 / T1@SEL$1
Outline Data
————-
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘10.2.0.5’)
OPT_PARAM(‘_optim_peek_user_binds’ ‘false’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
INDEX(@”SEL$1” “T1″@”SEL$1” (“T1″.”OBJECT_ID”))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
—————————————————
2 – filter(:X<=:Y)
3 – access(“OBJECT_ID”>=:X AND “OBJECT_ID”<=:Y)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) COUNT(*)[22]
46 rows selected.

Oracle绑定变量窥视功能的使用与测试

在ORACLE数据库中SQL效率的突然衰减通常是因为目标SQL的执行计划发生改变。(应用升级导致SQL执行次数变化非常大也有可能导致，但是不是单个SQL的问题了)
此时，通常通过重新收集统计信息或修改SQL(加HINT)来解决。但是有时收集统计信息并不能解决，而且无法修改SQL，此时就需要用到其它方法来固定执行计划。

常用的固定执行计划的方法有以下：

9I:stored outline
10G:SQL PROFILE(automatic/manual)
11G:SPM /SQL PROFILE(automatic/manual)

1.使用SQL PROFILE来稳定执行计划
是9I:stored outline的进化，9I:stored outline能实现的功能SQL PROFILE也完全能实现。
SQL PROFILE的优点：
更容易生成，更改和控制
对SQL语句的支持上做得更好，适应范围更广

很容易实现锁定或稳定执行计划、在不能修改目标SQL的SQL文本情况下使目标SQL语句按指定执行计划运行。

#############
SQLT工具中的COE系列脚本-sqlt\utl目录：
1.coe_xfr_sql_profile.sql脚本
适用于当前数据库中同一SQL有多个执行计划(内存或AWR数据中)时进行绑定；
通过替换hint数据也可以实现将另一个SQL(一般是加HINT)的较好执行计划，绑定到目标SQL上。

2.coe_load_sql_profile.sql
同一数据库中两个SQL(一般有一个是加了HINT)的执行计划，用加hint的更优执行计划，替换目标SQL当前的执行计划。
跨数据库也可以进行这种替换执行计划。

3.coe_load_sql_baseline.sql
同一数据库中两个SQL(一般有一个是加了HINT)的执行计划，用加hint的更优执行计划，替换目标SQL当前的执行计划。
跨数据库也可以进行这种替换执行计划。
脚本执行步骤：
1.获取原始SQL文本、修改SQL的PHV
2.为modified_sql手动创建基线
3.通过DBMS_SPM.ALTER_SQL_PLAN_BASELINE attribute_value修改基线对应成原始SQL
4.CREATE_STGTAB_BASELINE创建表并导出BASELINE数据

参考MOS文档：
Automatic SQL Tuning and SQL Profiles (文档 ID 271196.1)

Whitepaper: SQL Plan Management in Oracle Database 11g
Master Note: Plan Stability Features (Including SQL Plan Management (SPM)) (文档 ID 1359841.1)
FAQ: SQL Plan Management (SPM) Frequently Asked Questions (文档 ID 1524658.1)
How to Use SQL Plan Management (SPM) – Plan Stability Worked Example (文档 ID 456518.1)
How to Accept & Unaccept Plans in SQL Plan Baselines (文档 ID 1309799.1)
How to Drop Plans from the SQL Plan Management (SPM) Repository (文档 ID 790039.1)
Loading Hinted Execution Plans into SQL Plan Baseline. (文档 ID 787692.1)
–>Document 215187.1 SQLTXPLAIN offers a script ( ./utl/coe_load_sql_baseline.sql ) to automate the steps
Transporting SQL PLAN Baselines from One Database to Another. (文档 ID 880485.1)

迁移SQL PLAN Baselines
How to Load SQL Plans into SQL Plan Management (SPM) from the Automatic Workload Repository (AWR) (文档 ID 789888.1)
How to Move a SQL Tuning Set from One Database to Another (文档 ID 751068.1)
Automatic SQL Tuning and SQL Profiles (文档 ID 271196.1)
How to Move SQL Profiles from One Database to Another (Including to Higher Versions) (文档 ID 457531.1)

 

1.通过COE系列脚本：coe_xfr_sql_profile.sql脚本固定执行计划的测试：

—注意脚本中force_match=>FALSE要修改为force_match=>TRUE
–删除SQLPROFILE：
SQL>exec dbms_sqltune.drop_sql_profile(name => ‘coe_2r2qpwfmc2fwp_190799060′);
PL/SQL procedure successfully completed.

情况1：当前内存或AWR中已经有同一SQL的不同执行计划，且不同的执行计划中有正确 的执行计划，此时使用方法如下：
1.通过SQL_TEXT查出SQL_ID，执行如下命令生成脚本，再执行脚本来固定执行计划：
SQL> @coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: d3124k0m9ppwf

PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
4127820982 .035

Parameter 2: —输入通过SQL_ID得到的不同HASH_VALUE中的正确值：4127820982
Execute coe_xfr_sql_profile_d3124k0m9ppwf_4127820982.sql
on TARGET system in order to create a custom SQL Profile
with plan 4127820982 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.
在SQLPLUS窗口中执行此coe_xfr_sql_profile_d3124k0m9ppwf_4127820982.sql文件然后验证即可。

验证SQL执行情况：
select EXECUTIONS,PLAN_HASH_VALUE,ELAPSED_TIME/1000000,ELAPSED_TIME/1000000/EXECUTIONS,LAST_ACTIVE_TIME,ROWS_PROCESSED
from v$sql where EXECUTIONS>0 and sql_id=’g87an0j5djjpm’ order by last_active_time desc;

 

2.通过SPM固定执行计划的方法

ORACLE在11G推出了SPM sql plan management,是一种主动的稳定执行计划的手段，能够保证只有被验证过的执行计划才会被启用。
在目标SQL产生新执行计划后，新执行计划不会被启用，需要由用户确认是否启用。
SPM首先可以主动稳定执行计划，同时对于可能出现的更高效的执行计划，也可能得到使用。
启用SPM后，每个SQL对应一个SQL PLAN baseline–Oracle SQL执行计划基线，存储对应的执行计划.当SQL有多个执行计划，该SQL可能有多个SQL PLAN baseline–dba_sql_plan_baselines.
dba_sql_plan_baselines中的列ENABLED/ACCEPTED用来描述一个SQL PLAN baseline所对应的执行计划是否被启用。
只有列ENABLED/ACCEPTED值均为YES才会被启用。当一条SQL有超过1列ENABLED/ACCEPTED均为YES的执行计划，ORACLE会选择COST小的执行计划。
在ORACLE 11G及以上版本，有如下 两种方法可以生成目标SQL的SQL PLAN baseline.
1.自动捕获,手工切换使用
2.手工生成/批量导入(适合大版本升级，确保升级后执行计划不变更)
########################################
命令汇总:
show parameter use_sql_plan
alter session set optimizer_capture_sql_plan_baselines=TRUE;
alter session set optimizer_use_sql_plan_baselines=FALSE;
set linesize 300
set pagesize 1000
select SQL_HANDLE,SQL_TEXT,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED from dba_sql_plan_baselines
where sql_text like ‘select object_id,object_name from t8 where object_id between 300 and 400%’;

###将dba_sql_plan_baselines的列ACCEPTED设置为YES。(只能从NO改为YES,不能从YES改为NO)
var temp varchar2(1000);
exec :temp := dbms_spm.evolve_sql_plan_baseline(sql_handle=>’SQL_4fa41df4e6247591′,plan_name=>’SQL_PLAN_4z90xymm28xcjec50a0c0′,verify=>’NO’,commit=>’YES’);

###将dba_sql_plan_baselines的列ENABLED设置为YES。–需要指定sql_handle，plan_name
var temp varchar2(1000);
exec :temp := dbms_spm.alter_sql_plan_baseline(sql_handle=>’SQL_4fa41df4e6247591′,plan_name=>’SQL_PLAN_4z90xymm28xcj9576b900′,attribute_name=>’ENABLED’,attribute_value=>’YES’);
###将dba_sql_plan_baselines的列ENABLED设置为NO，禁用了此执行计划。–需要指定sql_handle，plan_name
var temp varchar2(1000);
exec :temp := dbms_spm.alter_sql_plan_baseline(sql_handle=>’SQL_4fa41df4e6247591′,plan_name=>’SQL_PLAN_4z90xymm28xcjec50a0c0′,attribute_name=>’ENABLED’,attribute_value=>’NO’);

exec DBMS_STATS.GATHER_TABLE_STATS ( ownname=>’BYS’,tabname =>’T8′,estimate_percent=>100,cascade=>TRUE);
exec DBMS_STATS.SET_INDEX_STATS( ownname=>’BYS’,indname =>’IDX_T8′,clstfct=>10000000,no_invalidate=>false);

验证SQL执行情况：
select EXECUTIONS,PLAN_HASH_VALUE,ELAPSED_TIME/1000000,ELAPSED_TIME/1000000/EXECUTIONS,LAST_ACTIVE_TIME,ROWS_PROCESSED
from v$sql where EXECUTIONS>0 and sql_id=’g87an0j5djjpm’ order by last_active_time desc;

Oracle固定执行计划的方法介绍与测试

1. HANGANALYZE介绍及命令

此部分信息主要参考MOS文档：Interpreting HANGANALYZE trace files to diagnose hanging and performance problems for 9i and 10g. (文档 ID 215858.1)

1.1 使用hanganalyze的场景：

当数据库出现严重的性能问题时，问题的原因可能是HANG。 使用HANGANALYZE生成的跟踪文件，将能够快速确定两个或多个进程是否死锁以及多少其他进程可能会受到影响。 通过使用HANGANALYZE，还可以确定在发生HANG时是否需要执行性能分析来诊断真实问题。

 

很多时候数据库并没有hang住，而只是由于数据库的性能问题，处理的时间比较长。Hanganalyze工具使用内核调用检测会话在等待什么资源，报告出占有者和等待者的相互关系。另外，它还会将一些比较”interesting”的进程状态dump出来，这个取决于我们使用hanganalyze的分析级别。

 

“true” hang，可理解为数据库内部发生死锁。普通的DML产生死锁时oracle服务器会自动监测他们的依赖关系并回滚其中一个操作， 终止这种相互等待的局面。而当这种死锁发生在争夺内核级别的资源（比如说是pins或latches）时，Oracle并不能自动的监测并处理这种死锁。

 

hanganalyze工具从8.1.6可用，在9i增强了诊断RAC环境下的信息，在任一节点执行即可报告出整个集群下的所有会话的信息。

 

1.2 SYSTEMSTATE与HANGANALYZE在处理hang问题时的对比：

SYSTEMSTATE处理hang问题时有如下不足：

SYSTEMSTATE dump reads the SGA in a “dirty” manner, so it may be inconsistent when the time to dump all the process is long.

转储时间过长时可能会读到非一致的数据

SYSTEMSTATE dump usually dumps a lot of information (most of which is not needed to determine the source of the hang), which makes difficult to determine the dependencies between processes quickly.

通过会转储大量无关的信息，不利于快速分析

SYSTEMSTATE dumps do not identify “interesting” processes on which to perform additional dumps (ERRORSTACK or PROCESS STATE).

1.3 HANGANALYZE处理hang问题的方式:

HANGANALYZE使用内部内核调用来确定会话是否正在等待资源，并报告阻塞者和被阻塞者之间的关系。 此外，它将“interesting”进程转储，并且根据在执行HANGANALYZE时使用的level在这些进程上执行自动PROCESSSTATE转储和ERRORSTACKS。

注意：HANGANALYZE不是用来替换SYSTEMSTATE转储，而是可以在诊断复杂问题的同时作为解释SYSTEMSTATE的路线图。

 

 

1.4 HANGANALYZE命令示例及level

会话级别的：
SQL>ALTER SESSION SET EVENTS ‘immediate trace name HANGANALYZE level <level>’;实例级别: ===》》需要以SYSDBA; sqlplus -prelim / as sysdba 或者sqlplus  / as sysdba
SQL>ORADEBUG hanganalyze <level>

 

单实例：

oradebug setmypid

oradebug unlimit

oradebug tracefile_name

oradebug hanganalyze 3

 

 

集群范围的：
SQL>ORADEBUG setmypid
SQL>ORADEBUG setinst all
SQL>ORADEBUG -g def hanganalyze <level>

或者：

For 11g:

sqlplus ‘/ as sysdba’

oradebug setorapname reco

oradebug  unlimit

oradebug -g all hanganalyze 3

exit

 

For 10g, run oradebug setmypid instead of oradebug setorapname reco:

sqlplus ‘/ as sysdba’

oradebug setmypid

oradebug unlimit

oradebug -g all hanganalyze 3

oradebug tracefile_name

exit

各个level的含义如下：
1-2:只有hanganalyze输出，不dump任何进程
3：Level2+Dump出在IN_HANG状态的进程
4：Level3+Dump出在等待链里面的blockers(状态为LEAF/LEAF_NW/IGN_DMP)
5：Level4+Dump出所有在等待链中的进程（状态为NLEAF）

10 :Dump all processes (IGN state)

Oracle官方建议不要超过level 3，一般level 3也能够解决问题，超过level 3会给系统带来较大负载。

 

1.5 HANGANALYZE trace文件分析- State of ALL nodes部分

LINUX64+11.2.0.4单实例使用删除不提交模拟TX等待时的TRACE：

 

State of ALL nodes  =====》》》全部节点的状态, 单实例时和state of local nodes内容一样

([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):

[0]/1/1/7/0xc38b1360/1966/LEAF/  =====》》》 LEAF/持锁会话，即它不等待任何会话或资源

[31]/1/32/31/0xc3852840/2211/NLEAF/[0][41] =====》》》NLEAF表明等待会话nodenum=0/41

[41]/1/42/11/0xc3833f80/2038/NLEAF/[0] =====》》》NLEAF表明是等待会话nodenum=0

 

关于此部分信息各个列的含义解释：

nodenum  =====》》》HANGANALYZE工具用于标识每个会话的连续序号

cnode =====》》》为实例编号，从1开始，在RAC中会显示为1、2、3这样，oracle9i起可用

sid         = Session ID

sess_srno   = Serial#

session  这个可能是进程的PADDR

ospid        = OS Process Id

state       = State of the node，详细的状态见下面。

[adjlist]：当state为NLEAF时adjlist列会有值，对应阻塞会话的[nodenum]值(可能有多个阻塞会话，RAC时显示的[nodenum]值可能在另一节点上此时在State of LOCAL nodes部分会找不到对应[nodenum])

predecessor: 表示waiter node，即等待此会话的节点  =====》》》11.2.0.3、11.2.0.4未发现有此列

 

state列的不同值：

IN_HANG:这表示该node处于死锁状态,通常还有其他node(blocker)也处于该状态

持锁会话：

LEAF 持锁会话，即它不等待任何会话或资源

LEAF_NW它也是持锁会话

LEAF/LEAF_NW:该node通常是blocker.通过”predecessor”列可以判断这个node是否是blocker。LEAF说明该NODE没有等待其他资源，而LEAF_NW则可能是没有等待其他资源或者是在使用CPU。

被阻塞会话：

NLEAF 表明是处于等待的会话(被阻塞)，此时adjlist列会阻塞会话的[nodenum]值；

当有许多行会话信息中的state列都是NLEAF状态，可能是数据库发生性能问题而不是数据库hang。

其它状态：

SINGLE_NODE/SINGLE_NODE_NW: This can be described the same as LEAF and LEAF_NW, except that they don’t have processes depending on them

IGN/IGN_DMP:这类会话通常被认为是空闲会话，除非其adjlist列里存在node。如果是非空闲会话则说明其adjlist里的node正在等待其他node释放资源。

 

 

 

 

2. HANGANALYZE分析示例1

示例1：linux32位+10.2.0.1.0单实例 update产生TX锁的TRACE文件：

State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[145]/0/146/5/0x786fc944/3858/LEAF/9/10//153

–这里的LEAF表示该SID阻塞了predecessor=153，nodenum153对应的SID=154,即为被阻塞者
[148]/0/149/1/0x78700160/3806/SINGLE_NODE/11/12//none
[150]/0/151/1/0x787026c8/3804/SINGLE_NODE/13/14//none
[153]/0/154/5/0x78705ee4/3903/NLEAF/15/16/[145]/none

–这里的NLEAF表示该会话被阻塞了,adjlist对应的nodenum=145对应的SID=146,即为阻塞者.

 

示例2：HP-UX +9.2.0.6.0单实例 split分区时HANG：

步骤1：split分区时HANG住后执行hanganalyze进行分析如下：

HANG ANALYSIS:
==============
Cycle 1 :<cnode/sid/sess_srno/proc_ptr/ospid/wait_event>:
<0/329/43816/0x4d6b5638/23487/rowcachelock>
–<0/254/19761/0x4d687438/23307/librarycachelock>
Cycle 2 :<cnode/sid/sess_srno/proc_ptr/ospid/wait_event>:
<0/295/57125/0x4d6b8978/19237/rowcachelock>
Cycle 3 :<cnode/sid/sess_srno/proc_ptr/ospid/wait_event>:
<0/295/57125/0x4d6b8978/19237/rowcachelock>
Open chains found:
Other chains found:
Chain 1 :<cnode/sid/sess_srno/proc_ptr/ospid/wait_event>:
<0/312/10459/0x4d69f9b8/25247/NoWait>
Extra information that will be dumped at higher levels:
[level  3] :   4 node dumps — [IN_HANG]
[level  5] :   1 node dumps — [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level 10] : 223 node dumps — [IGN]

State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[0]/0/1/1/0x4d7146c0/5132/IGN/1/2//none
……………………………………………………
[238]/0/239/57618/0x4d7b18a0/13476/IN_HANG/395/402/[294][238][328][253]/none
……………………………………………………
[253]/0/254/19761/0x4d7bb710/23307/IN_HANG/397/400/[328][238][294]/294
………………………………………………………………
[294]/0/295/57125/0x4d7d6820/19237/IN_HANG/396/401/[294][238][253]/238
[328]/0/329/43816/0x4d7ecf40/23487/IN_HANG/398/399/[253]/253
………………………………………………………………

cycle表示oracle内部确定的死锁。其中我们的当前手工执行split的295进程也在里面。我们观察其他的进程在做什么，如329：
SQL>selectmachine,status,program,sql_textfromv$sessiona,v$sqlareab
2  wherea.sql_address=b.addressanda.sid=329;
MACHINE   STATUS    PROGRAM                              SQL_TEXT
——— ——-  ———         ——————————————————————-
hl_rdb01  ACTIVE   sqlplus@hl_rdb01(TNSV1-V3)   ALTER TABLEA_PDA_SP_STATS PLITPARTITION P_MAXAT(20090609) INTO(PARTITION P_20090608  TABLESPACETS_DATA_A  ,PARTITION P_MAX TABLESPACETS_DATA_A)
SQL>select event from v$session_wait where sid=329;
EVENT
——————————————–
row cache lock
发现也是在执行split语句，但是问了同事，他已经把之前运行失败的脚本完全kill掉了。估计在数据库中进程挂死了，没有完全的释放。
kill掉329号进程后，发现还是挂住，所以我们继续做hanganlyze：

 

==============
HANG ANALYSIS:
==============
Cycle 1 :<cnode/sid/sess_srno/proc_ptr/ospid/wait_event>:
<0/295/57125/0x4d6b8978/19237/rowcachelock>
Cycle 2 :<cnode/sid/sess_srno/proc_ptr/ospid/wait_event>:
<0/254/19761/0x4d687438/23307/librarycachelock>
–<0/239/57618/0x4d6b74f8/13476/rowcachelock>

([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):

[238]/0/239/57618/0x4d7b18a0/13476/IN_HANG/395/402/[294][238][328][253]/none
[253]/0/254/19761/0x4d7bb710/23307/IN_HANG/397/400/[328][238][294]/294
[294]/0/295/57125/0x4d7d6820/19237/IN_HANG/396/401/[294][238][253]/238
[328]/0/329/43816/0x4d7ecf40/23487/IN_HANG/398/399/[253]/253

 

对STATE OF NODES部分分析，可以发现：

nodenum=238在等待[294][238][328][253]，没有阻塞别的会话

nodenum=294即执行分区操作的进程，此进程阻塞了nodenum=238的，同时在等待nodenum= [294][238][253]释放资源

nodenum=253阻塞了nodenum=294，即执行分区的会话，同时在等待nodenum=[328][238][294]

nodenum=328的阻塞了nodenum=253，同时也被253阻塞

简单来说，就是 nodenum=328阻塞了nodenum=253，nodenum=253阻塞了nodenum=294.

可以尝试kill nodenum=328,不能解决了再kill nodenum=253,再不行了kill [238]

 

我们继续把其他的进程杀掉。终于295的split执行成功。
SQL>ALTER TABLEA_PDA_SP_STAT SPLIT  PARTITIONP_MAXAT(20090609)
INTO(PARTITIONP_20090608 TABLESPACETS_DATA_A  ,PARTITION P_MAX TABLESPACETS_DATA_A)
Table altered.
Elapsed:00:31:03.21
继续执行split下一个分区，也很快完成。
SQL>ALTER TABLEA_PDA_SP_STATS PLITPARTITION P_MAXAT(20090610)
2   INTO(PARTITIONP_20090609 TABLESPACETS_DATA_A
3   ,PARTITIONP_MAX TABLESPACETS_DATA_A);
Table altered.
Elapsed:00:00:00.02
至此，问题解决.

 

示例3：11.2.0.3两节点RAC模拟TX锁时两个节点均有会话hang

 

============================================================================

Extra information that will be dumped at higher levels:

[level  4] :   1 node dumps — [LEAF] [LEAF_NW]

[level  5] :   3 node dumps — [NO_WAIT] [INVOL_WT] [SINGLE_NODE] [NLEAF] [SINGLE_NODE_NW]

 

State of ALL nodes

([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):

[9]/1/10/193/0x95231978/24694/NLEAF/[136]

[136]/1/137/2227/0x95376400/18342/LEAF/ [481]/2/10/3041/0x95231978/24814/NLEAF/[136]

[726]/2/255/3581/0x94d0a760/1112/NLEAF/[136]

 

分析如下：

—->nodenum=9的 session（即:1号实例上sid为10的session）的blocker（阻塞者）为nodenum=136的 session

——>nodenum=136的 session是blocker（阻塞者），位于1号节点SID=137

->nodenum=481的 session（即:2号实例上sid为10的session）的blocker（阻塞者）为nodenum=136的 session

->nodenum=726的 session（即:2号实例上sid为255的session）的blocker（阻塞者）为nodenum=136的 session

 

•  *** 2015-11-13 19:20:12.061

=============================================================================

•  END OF HANG ANALYSIS

============================================================================

•  *** 2015-11-13 19:20:12.064
•  =============================================================================
•  HANG ANALYSIS DUMPS:
•    oradebug_node_dump_level: 3
•  =============================================================================
•  State of LOCAL nodes
•  ([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
•  [9]/1/10/193/0x95231978/24694/NLEAF/[136]
•  [136]/1/137/2227/0x95376400/18342/LEAF/
•     ·  No processes qualify for dumping.
•     ·  =============================================================================
•  HANG ANALYSIS DUMPS: END
•  =============================================================================
•  session (kjzddbx) switches to a new action
•  PORADATA SERIAL:1 RINST:2 RDIAG:31036
•  PORADATA SERIAL:1 RINST:2 REMOTE EXEC OSPID:31036 =====》》远程节点的执行信息
•  PORADATA SERIAL:1 RINST:2 TRACEFILE:/u02/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_diag_31036.trc  =====》》远程节点的TRACE文件信息
•  PORADATA SERIAL:1 RINST:2  DONE

 

节点2的TRACE文件可以发现：

•  State of LOCAL nodes
•  ([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
•  [481]/2/10/3041/0x95231978/24814/NLEAF/[136]
•  [726]/2/255/3581/0x94d0a760/1112/NLEAF/[136]

—->可以看到，sid为10的session的blocker（阻塞者）是 nodenum=136，而这个136不在State of LOCAL nodes中，这说明这个nodenum=136在对方实例上。

—->可以看到，sid为255的session的blocker（阻塞者）是 nodenum=136，而这个136不在State of LOCAL nodes中，这说明这个nodenum=136在对方实例上。

 

Oracle的hanganalyze分析工具介绍及测试示例分析