在对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
