Skip to content

All posts by Guang Cai Li - 6. page

在ORACLE 10G/11G版本时，如果要将数据库的数据文件，移动到不同的磁盘目录（即修改磁盘路径），如果数据库在OPEN状态需要将数据文件OFFLINE，或者在数据库MOUNT状态下进行修补。

在12C及以上版本时，可以在线移动一个正在被访问的数据文件；即是system表空间中的数据文件也可以。
可以在线移动数据文件，表示当用户正在访问系统的时候，很多维护操作可以在线执行；例如，将数据文件移动到其他存储设备或者移动到Oracle ASM。这确保了服务的连续性，并且满足正常运行时的服务水平协议（SLA）。

在 12C，当数据文件处于在线状态并且正在被访问的时候，可以执行下面的操作：

1. 重命名在线数据库文件
2. 迁移在线数据库文件
3. 拷贝在线数据文件
4. 迁移在线数据文件并且覆盖现有文件
5. 迁移在线数据文件到 oracle ASM

如下为对这些功能的测试案例：

1.在线移动数据文件-文件系统-ASM

 

 

–可用于数据文件重命令、从一个目录移动到另一目录(可能涉及不同的磁盘或分区、LV)

–直接使用MOVE DATAFILE参数相当于是移动或者重命令，不会保留原数据文件

–keep关键字，可以实现拷贝数据文件到新位置，原文件保留

移动PDB中数据文件需要在指定 的PDB中进行操作：

 

show con_name

 

CON_NAME

——————————

CDBPDB2

C##Luda@cdbpdb2>select name from v$dbfile;

 

NAME

————————————————————————————————————————————————————————————

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_undotbs1_dkx3hppt_.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/system01.dbf

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf’;

 

Database altered.

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf

ls: cannot access /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf: No such file or directory

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

C##Luda@cdbpdb2>select file_name, status, online_status from dba_data_files;

 

FILE_NAME                                                                        STATUS    ONLINE_

——————————————————————————– ——— ——-

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf                           AVAILABLE ONLINE

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FA AVAILABLE ONLINE

C/datafile/o1_mf_undotbs1_dkx3hppt_.dbf

 

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FA AVAILABLE ONLINE

C/datafile/o1_mf_pdb2_dkx9pq4r_.dbf

 

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/system01.dbf                           AVAILABLE SYSTEM

观察此时的ALERT日志：

2017-05-23T14:43:17.122383+08:00

CDBPDB2(4):alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf’

2017-05-23T14:43:17.146362+08:00

Moving datafile /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf (22) to /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

2017-05-23T14:43:55.724994+08:00

Move operation committed for file /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

2017-05-23T14:43:58.166391+08:00

CDBPDB2(4):Completed: alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf’

使用KEEP关键字的测试：–OMF管理的不会保留原文件，

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ keep;

 

Database altered.

 

C##Luda@cdbpdb2>select file_name, status, online_status from dba_data_files;

 

FILE_NAME                                                                        STATUS    ONLINE_

——————————————————————————– ——— ——-

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf                           AVAILABLE ONLINE

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf                             AVAILABLE ONLINE

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FA AVAILABLE ONLINE

C/datafile/o1_mf_undotbs1_dkx3hppt_.dbf

 

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/system01.dbf                           AVAILABLE SYSTEM

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf

ls: cannot access /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf: No such file or directory

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

 

 

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ keep;

 

Database altered.

 

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

 

 

使用REUSE关键字覆盖

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

 

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ keep;

alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ keep

*

ERROR at line 1:

ORA-01119: error in creating database file ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’

ORA-27038: created file already exists

Additional information: 1

 

 

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ reuse;

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

ls: cannot access /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf: No such file or directory

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

 

Oracle12C版本开始支持的在线移动数据文件功能测试

临时表空间使用率高问题的分析解释

当你监控temporary tablespace 的空闲空间，发现空闲空间并没有随着sort的结束而增加，甚至当前数据库没有任何客户端连接也是如此：仍然有大量的temporary类型的extent存在于temporary tablespace，有时候用户会在运行事务的时候遇到ora-1652（在temp表空间上），这个错误表明没有足够的空间，但是当用户的查询失败后，smon并没有去清理临时段。

Scope & Application
===================

如果TEMPORARY TABLESPACE的类型是TEMPORARY，TEMPORARY TABLESPACE里的使用过的空间是不会被释放的，除非shutdown。
如果是PERMANENT，SMON会在process不再使用临时段之后去做清理。

如果使用TEMPORARY类型的临时表空间，数据库刚刚startup后，第一个使用TEMPORARY tablespace进行排序的statement会创一个建sort segment，这个segment不会被释放，除非数据库restart，可以用V$SORT_SEGMENT察看当前的已分配了的sort segments地使用情况。

如果是用PERMANENT tablespace作排序，由smon负责在statement结束之后删除被创建的temporary segments，这样空间可以被其他对象使用。

使用如下查询：
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE ,
TABLESPACE_NAME
from DBA_SEGMENTS
where SEGMENT_TYPE = ‘TEMPORARY’;

to give results similar to:

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
—– ———— ———— —————
SYS 4.2 TEMPORARY TEMP

Note: segment_name and tablepace_name are likely to be different.

tablespace的类型可以用如下查询：
select TABLESPACE_NAME,
CONTENTS
from DBA_TABLESPACES
where TABLESPACE_NAME in ( );

to give results similar to:

TABLESPACE_NAME CONTENTS
—————————— ———
TEMP TEMPORARY
TEMP1 PERMANENT

解释
=====

1)
对于使用了TEMPORARY类型的TEMPORARY TABLESPACE，临时段被使用后不被回收是正常的行为（7.3以后），这时如果有ora-1652发生，说明临时表空间是真得不够.

系统中的临时段在oracle startup之后被创建，并只有在oracle shutdown的时候被释放，如果存在大量的extent，一个可能的原因是你的storage子句的设置有问题。

考虑到性能原因，当一个temporary extent被分配的时候，tablespace会做一个标记，操作结束之后这个extent不会被释放或回收，相应的，这个extent被简单的标志为free，对于后面的sort操作是available的，这样就省去了系统分配和回收temporary extent的负载。

2）
对于在PERMANENT的tablespace里面使用temporary segment，记得检查parameter file里面没有设置如下参数：
event=”10061 trace name context forever, level 10″
event=”10269 trace name context forever, level 10″

这两个参数禁止smon去做temporary segment的clean up和coalescing，在oraus.msg里面可以看到具体的定义：
10061, 00000, “disable SMON from cleaning temp segments
10269, 00000, “Don’t do coalesces of free space in SMON”

// *Cause: setting this event prevents SMON from doing free space coalesces

（正常情况下，smon会负责定期做temporary segment的clean up和coalescing。具体作的方式metalink可以找到）

通过查询V$SORT_SEGMENT来看temp segment是free还是being used

For example:
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

>>> DURING the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
—————- ———— ———– ———–
TEMP 590 590 0

>>> AFTER the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
————— ———— ———— ———–
TEMP 590 0 590

通过这个查询可以看到在做sort的时候，segment标识为used，sort结束后，标志为free。实际上这时候相应的extents都被释放回SEP（sort extent pool）里面。（SEP是SGA里面存放temp extent的部分，具体描述在metalink也可以找到）

参考MOS文档：Temporary Tablespace, the Sort Extent Pool, and OPS (文档 ID 65973.1)
Temporary Segments Are Not Being De-Allocated After a Sort（Note:1039341.6）

如下为典型的临时表空间问题的排查步骤和命令脚本：

1.查看当前临时表空间配置情况–大小、数据文件个数、扩展情况等
2.哪些会话使用临时表空间较多，具体占用大小、临时段类型
3.找出使用临时表空间较多的SQL信息
4.找出使用临时表空间较多的SESSION信息
###############临时表空间使用过多的排查思路
1.查看当前临时表空间配置情况
–查看当前临时文件大小、是否支持自动扩展等
set linesize 180
col file_name for a50
col tablespace_name for a20
select file_name,file_id,tablespace_name,bytes/1024/1024/1024 gb,status,AUTOEXTENSIBLE,MAXBYTES from dba_temp_files;

set linesize 180
col name for a50
select * from V$TEMPFILE;
—查看临时表空间使用率
col tablespace_name for a30
select (s.tot_used_blocks/f.total_blocks)*100 as “percent used”
from
(select sum(used_blocks) tot_used_blocks from gv$sort_segment where tablespace_name=’TEMP’) s,
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name=’TEMP’) f;
====
select f.TABLESPACE_NAME,f.total_MB,f.USED_MB,f.USED_MB/f.total_MB*100 as percent_used
from (select bb.TABLESPACE_NAME,bb.temp_MB total_MB,aa.used_blocks*P.VALUE/1024/1024 USED_MB
from (select a.TABLESPACE_NAME,sum(a.used_blocks) USED_blocks from gv$sort_segment a group by a.TABLESPACE_NAME) aa,
(select b.TABLESPACE_NAME,sum(b.BYTES/1024/1024) temp_MB from dba_temp_files b group by b.TABLESPACE_NAME) bb, SYS.V_$SYSTEM_PARAMETER P
where aa.TABLESPACE_NAME= bb.TABLESPACE_NAME and UPPER(P.NAME)=’DB_BLOCK_SIZE’) f;

###########################################################

2.查看当前哪个会话使用的临时段较大
—–找出使用临时空间最多的20个会话session_addr及其SQL_ID
set linesize 180
select * from
(select username,session_addr,sql_id,contents,segtype,blocks*8/1024 mb
from v$sort_usage order by blocks desc)
where rownum<11;

select * from v$temporary_lobs;

#####查出TEMP段大小以及进程相关信息
set linesize 180 pagesize 10000
col username for a10
col program for a20
col machine for a15
COL EVENT FOR A28
col sql_id for a15
col spid for a8
col TABLESPACE for a6

select * from (select s.username,b.spid,s.sid,s.program,s.machine,s.sql_id,s.event,to_char(s.LOGON_TIME,’yyyymmdd hh24:mi:ss’),u.tablespace, u.segtype, round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P,v$process b
where s.saddr = u.session_addr
and s.paddr=b.addr
AND UPPER(P.NAME)=’DB_BLOCK_SIZE’
order by MB DESC) where rownum<31;

===prev_sql_id
========
select * from (select s.username,b.spid,s.sid,s.program,s.machine,s.prev_sql_id,to_char(s.LOGON_TIME,’yyyymmdd hh24:mi:ss’), u.segtype, round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P,v$process b
where s.saddr = u.session_addr
and s.paddr=b.addr
AND UPPER(P.NAME)=’DB_BLOCK_SIZE’
order by MB DESC) where rownum<31;
===

—通过SQL_ID查SQL文本–如果SESSION已执行完排序SQL后并未有执行其它SQL，则可如下查：
select SQL_ID,sql_text from v$sqltext where sql_id in(’08yg5rb6upbra’,”) order by SQL_ID,piece;
###########################################################
3.找出使用临时表空间较多的SQL信息
v$sort_usage中的SQL_ID是会话前一条执行的SQL,–即对应V$SESSION中的字段PREV_SQL_ID.
–从11.2.0.2这个版本开始，v$sort_usage的基表x$ktsso中增加了一个字段ktssosqlid，表示该临时段真正关联的SQL。
以上述的测试结果为例，查询这个基表的结果如下：
select ktssosqlid,v$session.SADDR from x$ktsso, v$session where ktssoses = v$session.saddr
and ktssosno = v$session.serial#
and v$session.SADDR in(‘3231FE14′,’322DEA14′);

对于11.2.0.2之前版本的查询：
—通过第三步查出的session_addr关联SADDR查prev_sql_id–要求prev_sql_id执行后未执行新SQL
select sid,prev_sql_id, sql_id from v$session where saddr=’070000294AC0D050’;

#######################################
4.找出使用临时表空间较多的SESSION信息
–使用第三步查出的session_addr查出的使用临时表空间较多的SQL_ID及SESSION信息：
select a.sql_id sort_sql_id,b.sql_id,b.prev_sql_id, a.contents,a.segtype,a.blocks*8/1024 Mb,b.sid
from v$sort_usage a,v$session b
where a.session_addr=b.saddr and (b.sid=31 or b.saddr in(‘3231FE14′,’322DEA14′));
–根据session_addr查询会话的SID/终端信息等
col spid for a10
col machine for a30
select b.spid,a.sid,a.username,a.OSUSER,a.program,a.machine from v$session a,v$process b
where a.paddr=b.addr and a.type=’USER’ and a.SADDR in(‘3231FE14′,’322DEA14’);

################
适用11.2.0.2及以上，直接查出排序空间使用较多的SQL_ID
col username for a10
col osuser for a10
col tablespace for a15
select * from
(select k.inst_id “INST_ID”,
ktssoses “SADDR”,
sid,
ktssosno “SERIAL#”,
username “USERNAME”,
osuser “OSUSER”,
s.machine,
s.event,
ktssosqlid “SQL_ID”,
ktssotsn “TABLESPACE”,
decode(ktssocnt, 0, ‘PERMANENT’, 1, ‘TEMPORARY’) “CONTENTS”,
decode(ktssosegt, 1, ‘SORT’, 2, ‘HASH’, 3, ‘DATA’, 4, ‘INDEX’,
5, ‘LOB_DATA’, 6, ‘LOB_INDEX’ , ‘UNDEFINED’) “SEGTYPE”,
ktssofno “SEGFILE#”,
ktssobno “SEGBLK#”,
ktssoexts “EXTENTS”,
ktssoblks “BLOCKS”,
round(ktssoblks*p.value/1024/1024, 2) “SIZE_MB”,
ktssorfno “SEGRFNO#”
from x$ktsso k, v$session s,
(select value from v$parameter where name=’db_block_size’) p
where ktssoses = s.saddr
and ktssosno = s.serial#
order by size_mb)
where rownum<11;

====
select * from
(select
ktssoses “SADDR”,
s.sid,
username “USERNAME”,
s.machine,
s.event,
ktssosqlid “SQL_ID”,
ktssotsn “TABLESPACE”,
decode(ktssocnt, 0, ‘PERMANENT’, 1, ‘TEMPORARY’) “CONTENTS”,
decode(ktssosegt, 1, ‘SORT’, 2, ‘HASH’, 3, ‘DATA’, 4, ‘INDEX’,
5, ‘LOB_DATA’, 6, ‘LOB_INDEX’ , ‘UNDEFINED’) “SEGTYPE”,
round(ktssoblks*p.value/1024/1024, 2) “SIZE_MB”
from x$ktsso k, v$session s,
(select value from v$parameter where name=’db_block_size’) p
where ktssoses = s.saddr
and ktssosno = s.serial#
order by size_mb)
where rownum<11;

Oracle临时表空间使用率高问题的分析解释与排查脚本

在对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绑定变量窥视功能的使用与测试