Oracle 变量绑定与变量窥视合集
数据库环境
LEO1@LEO1> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
一 用示例说明绑定变量的应用领域是OLTP而不是OLAP
变量绑定：这是一个老生常谈的话题，我所理解的绑定就是执行计划的绑定，我所理解的变量就是谓词替换的变量。
变量绑定机制：要说机制不得不说一下SQL执行的过程，三部曲：解析 –> 执行 -> 取操作，而绑定变量就发生在解析这一步，而解析又分成硬解析和软解析。
硬解析：当一条SQL语句第一次执行时，首先生成执行计划，并把这个执行计划存放到shared_pool的library cache中，这个过程叫做硬解析。
软解析：如果SQL语句已经被硬解析过了，那么可以直接从library cache中抽取现成的执行计划来重用，这个过程叫做软解析，目的减少生成执行计划这方面的资源消耗。为什么这么说呢，硬解析会消耗一些系统资源，尤其是CPU的资源，从而影响系统的效率，如果能把这方面的影响消除，那么对系统当然是多多益善了，哈 多侃了几句。
SQL详细执行过程：当oracle接收到一条sql语句时，首先会把这条sql语句字符做成哈希值，然后到library cache中寻找是否有和这个哈希值相匹配的sql存在，如果有就直接使用这个sql的执行计划去执行当前的sql语句，最后将结果返回给用户。如果没有找到相同的哈希值，oracle会认为这是一条新的sql，将会重新生成执行计划来执行（在这个过程中先要检查语法分析和语义分析），最后将结果返回给用户。
实验
下面我们演示一下绑定变量和非绑定变量在资源消耗上的差异
LEO1@LEO1> drop table leo1 purge; 清理环境
Table dropped.
LEO1@LEO1> drop table leo2 purge;
Table dropped.
LEO1@LEO1> create table leo1 as select * from dba_objects; 创建leo1
Table created.
LEO1@LEO1> create table leo2 as select * from dba_objects; 创建leo2
Table created.
LEO1@LEO1> alter session set tracefile_identifier='bind_variable'; 设置trace文件标识
Session altered.
LEO1@LEO1> alter session set sql_trace=true; 启动trace功能，追踪sql资源消耗情况
Session altered.
LEO1@LEO1> begin
for i in 1..100 loop
execute immediate 'select * from leo1 where object_id=:i' using i;
end loop;
end;
/
PL/SQL procedure successfully completed.
我们对一条sql执行了100次并采用了绑定变量技术，oracle对这条sql只有一次硬解析，没有软解析，反复执行100次。
LEO1@LEO1> alter session set sql_trace=false; 关闭trace功能
Session altered.
LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select * from leo1 where %';
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
-------------------------------------------------- ----------- -------------------------- ----------- ---------- ------------------
select * from leo1 where object_id=:i 1 1 100
SQL_TEXT：我们跟踪的sql语句
PARSE_CALLS：硬解析+软解析次数 1次 只有硬解析没有软解析
LOADS：硬解析次数 1次
EXECUTIONS：执行次数 100次
虽说值隐藏在变量中，但在解析环节oracle认为是一样的
[oracle@leonarding1 trace]$ tkprof LEO1_ora_16433_bind_variable.trc bind_variable.txt sys=no
TKPROF: Release 11.2.0.1.0 - Development on Fri Feb 1 13:18:08 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
使用tkprof工具过滤和汇总trace文件的，sys=no 不输出sys用户递归语句，默认yes，实际上设置成no更具有可读性
--------- 下面是bind_variable.txt文件信息
********************************************************************************
SQL ID: 0b74y9utb0b6r #这就是SQL语句字符的哈希值
Plan Hash: 2716644435
select *
from
leo1 where object_id=:i
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.01 0.01 0 1 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.01 0.01 0 1 0 0
Misses in library cache during parse: 1 只有1次硬解析，反复执行100次
Optimizer mode: ALL_ROWS
Parsing user id: 85 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL LEO1 (cr=0 pr=0 pw=0 time=0 us cost=288 size=2484 card=12)
********************************************************************************
下面是一个非绑定变量的sql执行情况
LEO1@LEO1> alter session set sql_trace=true; 启动trace功能
Session altered.
LEO1@LEO1> begin
for i in 1..100 loop
execute immediate 'select * from leo2 where object_id='||i;
end loop;
end;
/
PL/SQL procedure successfully completed.
我们对一条sql执行了100次没有采用绑定变量技术，oracle对这条sql要硬解析100次，执行100次，资源严重被sql解析所消耗，系统显得缓慢不堪。
LEO1@LEO1> alter session set sql_trace=false; 关闭trace功能
Session altered.
LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select * from leo2 where %' order by 1;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
-------------------------------------------------- ----------- ---------- ----------
select * from leo2 where object_id=1 1 1 1
select * from leo2 where object_id=10 1 1 1
select * from leo2 where object_id=100 1 1 1
select * from leo2 where object_id=11 1 1 1
select * from leo2 where object_id=12 1 1 1
select * from leo2 where object_id=13 1 1 1
select * from leo2 where object_id=14 1 1 1
select * from leo2 where object_id=15 1 1 1
select * from leo2 where object_id=16 1 1 1
select * from leo2 where object_id=17 1 1 1
select * from leo2 where object_id=18 1 1 1
select * from leo2 where object_id=19 1 1 1
select * from leo2 where object_id=2 1 1 1
select * from leo2 where object_id=20 1 1 1
select * from leo2 where object_id=21 1 1 1
select * from leo2 where object_id=22 1 1 1
select * from leo2 where object_id=23 1 1 1
select * from leo2 where object_id=24 1 1 1
select * from leo2 where object_id=25 1 1 1
select * from leo2 where object_id=26 1 1 1
select * from leo2 where object_id=27 1 1 1
select * from leo2 where object_id=28 1 1 1
select * from leo2 where object_id=29 1 1 1
select * from leo2 where object_id=3 1 1 1
select * from leo2 where object_id=30 1 1 1
select * from leo2 where object_id=31 1 1 1
select * from leo2 where object_id=32 1 1 1
select * from leo2 where object_id=33 1 1 1
select * from leo2 where object_id=34 1 1 1
select * from leo2 where object_id=35 1 1 1
select * from leo2 where object_id=36 1 1 1
select * from leo2 where object_id=37 1 1 1
select * from leo2 where object_id=38 1 1 1
select * from leo2 where object_id=39 1 1 1
select * from leo2 where object_id=4 1 1 1
select * from leo2 where object_id=40 1 1 1
select * from leo2 where object_id=41 1 1 1
select * from leo2 where object_id=42 1 1 1
select * from leo2 where object_id=43 1 1 1
select * from leo2 where object_id=44 1 1 1
select * from leo2 where object_id=45 1 1 1
select * from leo2 where object_id=46 1 1 1
select * from leo2 where object_id=47 1 1 1
select * from leo2 where object_id=48 1 1 1
select * from leo2 where object_id=49 1 1 1
select * from leo2 where object_id=5 1 1 1
select * from leo2 where object_id=50 1 1 1
select * from leo2 where object_id=51 1 1 1
select * from leo2 where object_id=52 1 1 1
select * from leo2 where object_id=53 1 1 1
select * from leo2 where object_id=54 1 1 1
select * from leo2 where object_id=55 1 1 1
select * from leo2 where object_id=56 1 1 1
select * from leo2 where object_id=57 1 1 1
select * from leo2 where object_id=58 1 1 1
select * from leo2 where object_id=59 1 1 1
select * from leo2 where object_id=6 1 1 1
select * from leo2 where object_id=60 1 1 1
select * from leo2 where object_id=61 1 1 1
select * from leo2 where object_id=62 1 1 1
select * from leo2 where object_id=63 1 1 1
select * from leo2 where object_id=64 1 1 1
select * from leo2 where object_id=65 1 1 1
select * from leo2 where object_id=66 1 1 1
select * from leo2 where object_id=67 1 1 1
select * from leo2 where object_id=68 1 1 1
select * from leo2 where object_id=69 1 1 1
select * from leo2 where object_id=7 1 1 1
select * from leo2 where object_id=70 1 1 1
select * from leo2 where object_id=71 1 1 1
select * from leo2 where object_id=72 1 1 1
select * from leo2 where object_id=73 1 1 1
select * from leo2 where object_id=74 1 1 1
select * from leo2 where object_id=75 1 1 1
select * from leo2 where object_id=76 1 1 1
select * from leo2 where object_id=77 1 1 1
select * from leo2 where object_id=78 1 1 1
select * from leo2 where object_id=79 1 1 1
select * from leo2 where object_id=8 1 1 1
select * from leo2 where object_id=80 1 1 1
select * from leo2 where object_id=81 1 1 1
select * from leo2 where object_id=82 1 1 1
select * from leo2 where object_id=83 1 1 1
select * from leo2 where object_id=84 1 1 1
select * from leo2 where object_id=85 1 1 1
select * from leo2 where object_id=86 1 1 1
select * from leo2 where object_id=87 1 1 1
select * from leo2 where object_id=88 1 1 1
select * from leo2 where object_id=89 1 1 1
select * from leo2 where object_id=9 1 1 1
select * from leo2 where object_id=90 1 1 1
select * from leo2 where object_id=91 1 1 1
select * from leo2 where object_id=92 1 1 1
select * from leo2 where object_id=93 1 1 1
select * from leo2 where object_id=94 1 1 1
select * from leo2 where object_id=95 1 1 1
select * from leo2 where object_id=96 1 1 1
select * from leo2 where object_id=97 1 1 1
select * from leo2 where object_id=98 1 1 1
select * from leo2 where object_id=99 1 1 1
100 rows selected.
我们从动态性能视图上可以看出oracle每执行一次sql，都要先硬解析1次之后在执行。这种没有使用绑定变量技术在硬解析消耗上就比使用绑定变量技术多损耗100倍，如果执行的次数上万 上亿对系统性能的影响可想而知。
--------- 我们来看看trace文件的内容
[oracle@leonarding1 trace]$ tkprof LEO1_ora_16433_bind_variable.trc bind_variable.txt sys=no
TKPROF: Release 11.2.0.1.0 - Development on Fri Feb 1 13:49:52 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
********************************************************************************
SQL ID: 22r47f3t6w0td
Plan Hash: 2258638698
select *
from
leo2 where object_id=1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 85 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL LEO2 (cr=0 pr=0 pw=0 time=0 us cost=288 size=2484 card=12)
********************************************************************************
SQL ID: 9nb3n54fy0z8m
Plan Hash: 2258638698
select *
from
leo2 where object_id=2
********************************************************************************
SQL ID: 8mc705qymd7qs
Plan Hash: 2258638698
select *
from
leo2 where object_id=3
如上所示每个sql语句的SQL_ID都是不一样的，都是相对独立的，因此每执行1次就要解析1次，两种情况对比结果显示，绑定变量要比没有绑定变量消耗的资源少的少，sql执行的次数越多，这种效果越明显。所以我们说绑定变量本质就是用一个变量来代替谓词常量，让oracle只需要硬解析一次，后续sql都直接使用之前执行计划来执行，这样就省却了很消耗资源的硬解析过程
下面讨论绑定变量为什么适合于OLTP而不是OLAP
OLTP
1.适合OLTP系统架构
2.SQL简单非常相似，结果集非常小，例如 只有谓词部分不同，余下部分全部相同的SQL语句，这种情况下执行计划都是一样的，在执行计划几乎不变的情况下，oracle使用变量来代替谓词常量，使用同一个执行计划是非常合理的
3.SQL重复率很高，或者只有谓词条件不同而已
4.DML操作频繁
5.SQL语句执行条数多，条数越多减少硬解析越有意义
6.基于主键做查询，还有等值查询，唯一性查询，这类查询相对适合绑定变量
select * from leonarding where id=:leo;
OLAP
1.不适合OLAP系统架构
2.SQL的执行计划多变，会因为值的不同导致执行计划的不同，可能第一次执行是一种执行计划，第二次执行是另一种执行计划，所以不适合进行绑定变量操作，会让oracle盲目浪费大量资源消耗，SQL语句即使只有谓词条件不同，oracle应然可能采取不同的执行计划。
3.SQL重复率较低，大部分都是批量加载批量检索的操作
4.数据聚合操作频繁
5.SQL语句执行条数少，SQL硬解析对系统性能影响较小，绑定没有意义
6.分区表相对不太适合绑定变量技术
二 用示例演示一次硬分析（hard parse)和一次软分析（soft parse），以及一次更软的分析（softer soft parse)，并对给出演示结果
我们先看一个硬解析和软解析关系测试，什么时候硬解析，什么时候软解析
LEO1@LEO1> drop table leo6 purge; 清理环境
Table dropped.
LEO1@LEO1> create table leo6 as select * from dba_objects; 创建leo6表
Table created.
LEO1@LEO1> select count(*) from leo6; 第一次执行
COUNT(*)
----------
72011
LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select count(*) from leo6' order by 1;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
-------------------------------------------------- ----------- -------------------------- ----------- ---------- ------------------
select count(*) from leo6 1 1 1
总解析次数：1
硬解析次数：1
执行次数：1
硬解析发生在SQL语句第一次执行时，后续在执行相同语句就是软解析了，看看下面
LEO1@LEO1> select count(*) from leo6; 第二次执行
COUNT(*)
----------
72011
LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select count(*) from leo6' order by 1;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
-------------------------------------------------- ----------- -------------------------- ----------- ---------- ------------------
select count(*) from leo6 2 1 2
总解析次数：2
硬解析次数：1
执行次数：2
当SQL硬解析之后，后续相同的SQL都被软解析，除非SQL被剔除shared_pool
LEO1@LEO1> alter system flush shared_pool; 清空shared_pool
System altered.
LEO1@LEO1> select count(*) from leo6; 重新执行一次
COUNT(*)
----------
72011
LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select count(*) from leo6' order by 1;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
-------------------------------------------------- ----------- -------------------------- ----------- ---------- ------------------
select count(*) from leo6 1 1 1
当重新执行一次后，又重新有了一次硬解析，oracle认为这是一条新SQL，根据统计信息重新生成一次执行计划来执行。
Softer_soft_parse 会话对游标的缓存
什么是游标：游标可以理解为SQL语句的一个句柄，也叫SQL语句的指针，游标指向一条SQL语句，oracle会话要执行一条SQL时，首先要打开游标。
打开游标：新的SQL语句执行时候，在SGA中需要把这条SQL语句和shared_pool中SQL语句的哈希值建立一条通道（连接），即建立SQL语句句柄，这个建立通道的过程就叫打开游标。
softer_soft_parse超软解析：打开游标的过程是在软解析之后，它要在shared_pool中寻找哈希值（这个哈希值就是软解析之后得到的），如果没有找到就需要重新构造游标（这就是硬解析过程），如果游标是打开状态，那么会话可以直接使用打开的游标连接到shared_pool中SQL语句入口，执行SQL。如果游标是关闭状态，会话就需要重新建立到shared_pool连接（即打开游标操作），这样也会消耗一点点资源。而我们要做的就是尽量打开游标保持通道畅通，又由于这个操作是在软解析之后，又叫超软解析，比软解析还要软一点。
会话缓存游标实验
session_cached_cursor，这个参数可以控制，会话打开游标或关闭游标
如果值为0，说明不使用缓存游标功能，oracle每次都要重新打开游标
如果值为非0，说明使用缓存游标功能，会话一直保持打开状态，随时执行SQL语句
1.session_cached_cursors=0 测试
为了更好的对比效果，我们先要清空一下内存中的遗留数据，最简行以便的方法就是重启数据库了，当然我们这是测试环境，在生产环境中另当别论。
SYS@LEO1> shutdown immediate; 关闭数据库，清空SGA
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@LEO1> startup 启动数据库
ORACLE instance started.
Total System Global Area 680607744 bytes
Fixed Size 2216464 bytes
Variable Size 503320048 bytes
Database Buffers 167772160 bytes
Redo Buffers 7299072 bytes
Database mounted.
Database opened.
SYS@LEO1> conn leo1/leo1 切换leo1用户
Connected.
LEO1@LEO1> set linesize 300 设置版面
LEO1@LEO1> set pagesize 999
LEO1@LEO1> show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------------------
session_cached_cursors integer 50
检查参数默认为50
LEO1@LEO1> alter session set session_cached_cursors=0;
Session altered.
如果值为0，说明不使用缓存游标功能，oracle每次都要重新打开游标
LEO1@LEO1> show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------------------
session_cached_cursors integer 0
LEO1@LEO1> alter system flush shared_pool; 清空shared pool
System altered.
LEO1@LEO1> select 'STAT-'||name,value from v$sysstat where name like '%parse%' or name like'%cursor%'
union all
select 'LATCH-'||name,gets from v$latch where name = 'shared pool';
'STAT-'||NAME VALUE
---------------------------------------------------------------------- ----------
STAT-opened cursors cumulative 51660
STAT-opened cursors current 88 当前打开游标数
STAT-pinned cursors current 5
STAT-session cursor cache hits 37902 会话缓冲区游标命中率
STAT-session cursor cache count 3153
STAT-cursor authentications 668
STAT-parse time cpu 845 CPU解析耗时
STAT-parse time elapsed 1974 解析总耗时
STAT-parse count (total) 30593 解析总次数
STAT-parse count (hard) 2700 硬解析次数
STAT-parse count (failures) 6
STAT-parse count (describe) 0
LATCH-shared pool 263201 共享池latch数
查询系统当前资源消耗情况
LEO1@LEO1> drop table leo7 purge; 清空环境
Table dropped.
LEO1@LEO1> create table leo7 as select * from dba_objects; 创建leo7表
Table created.
LEO1@LEO1> select count(*) from leo7; 表size71972条
COUNT(*)
----------
71972
循环执行1w次
LEO1@LEO1> begin
for leo in 1..10000 loop
execute immediate 'select count(*) from leo7';
end loop;
end;
/
PL/SQL procedure successfully completed.
再次查询一下当前系统资源消耗情况
LEO1@LEO1> select 'STAT-'||name,value from v$sysstat where name like '%parse%' or name like'%cursor%'
union all
select 'LATCH-'||name,gets from v$latch where name = 'shared pool'; 2 3
'STAT-'||NAME VALUE
---------------------------------------------------------------------- ----------
STAT-opened cursors cumulative 63614
STAT-opened cursors current 89 当前打开游标数
STAT-pinned cursors current 6
STAT-session cursor cache hits 39494 会话缓冲区游标命中率
STAT-session cursor cache count 3513
STAT-cursor authentications 713
STAT-parse time cpu 851
STAT-parse time elapsed 1992
STAT-parse count (total) 41546 解析总次数
STAT-parse count (hard) 2739 硬解析次数
STAT-parse count (failures) 6
STAT-parse count (describe) 0
LATCH-shared pool 268172
这是session_cached_cursors=0的对比结果
当前打开游标数：88 -> 89 多了1个
会话缓冲区游标命中率：37902 -> 39494 多了1592次
硬解析次数：2700 -> 2739 多了39次
解析总次数：30593 -> 41546 多了10953次，这个和执行的次数很接近，差不多软解析了1w次
共享池latch数：263201 -> 268172 多了4971次
2.session_cached_cursors=100 测试
系统当前资源消耗
LEO1@LEO1> select 'STAT-'||name,value from v$sysstat where name like '%parse%' or name like'%cursor%'
union all
select 'LATCH-'||name,gets from v$latch where name = 'shared pool'; 2 3
'STAT-'||NAME VALUE
---------------------------------------------------------------------- ----------
STAT-opened cursors cumulative 80112
STAT-opened cursors current 88
STAT-pinned cursors current 5
STAT-session cursor cache hits 44463
STAT-session cursor cache count 4334
STAT-cursor authentications 791
STAT-parse time cpu 872
STAT-parse time elapsed 2016
STAT-parse count (total) 55199
STAT-parse count (hard) 2771
STAT-parse count (failures) 10
STAT-parse count (describe) 0
LATCH-shared pool 278343
LEO1@LEO1> alter session set session_cached_cursors=100;
如果值为非0，说明使用缓存游标功能，会话一直保持打开状态，随时执行SQL语句
LEO1@LEO1> show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 100
再重新循环执行1w次
LEO1@LEO1> begin
for leo in 1..10000 loop
execute immediate 'select count(*) from leo7';
end loop;
end;
/
PL/SQL procedure successfully completed.
LEO1@LEO1> select 'STAT-'||name,value from v$sysstat where name like '%parse%' or name like'%cursor%'
union all
select 'LATCH-'||name,gets from v$latch where name = 'shared pool';
'STAT-'||NAME VALUE
---------------------------------------------------------------------- ----------
STAT-opened cursors cumulative 92040
STAT-opened cursors current 89
STAT-pinned cursors current 5
STAT-session cursor cache hits 56058
STAT-session cursor cache count 4690
STAT-cursor authentications 803
STAT-parse time cpu 872
STAT-parse time elapsed 2017
STAT-parse count (total) 56081
STAT-parse count (hard) 2773
STAT-parse count (failures) 10
STAT-parse count (describe) 0
LATCH-shared pool 280878
这是session_cached_cursors=100的对比结果
当前打开游标数：88 -> 89 多了1个
会话缓冲区游标命中率：44463 -> 56058 多了11595多
硬解析次数：2771 -> 2773 多了2次
解析总次数：55199 -> 56081 多了882次
共享池latch数：278343 -> 280878 多了2535次
小结：我们从会话缓冲区游标命中率指标的对比结果可知，设置session_cached_cursors参数为非0时，oracle使用了缓存游标功能，命中率从1592提升到11595，共享池latch数从4971减少到2535，我们可以很明显的看出使用打开游标技术可以大大提高数据库的性能，softer_soft_parse超软解析可以提供比soft_parse软解析更好的性能。
三 用示例演示一次分析，多次执行的示例，并对给出演示结果
我们分别演示硬解析 软解析 执行不同组合
实验
没有绑定变量
LEO1@LEO1> drop table leo3 purge; 清理环境
Table dropped.
LEO1@LEO1> drop table leo4 purge;
Table dropped.
LEO1@LEO1> drop table leo5 purge;
Table dropped.
LEO1@LEO1> create table leo3 as select * from dba_objects; 创建三张表
Table created.
LEO1@LEO1> create table leo4 as select * from dba_objects;
Table created.
LEO1@LEO1> create table leo5 as select * from dba_objects;
Table created.
LEO1@LEO1> select object_type from leo3 where object_id=100; 执行4次
OBJECT_TYPE
-------------------
EDITION
LEO1@LEO1> select object_type from leo3 where object_id=200;
OBJECT_TYPE
-------------------
INDEX
LEO1@LEO1> select object_type from leo3 where object_id=300;
OBJECT_TYPE
-------------------
TABLE
LEO1@LEO1> select object_type from leo3 where object_id=400;
OBJECT_TYPE
-------------------
TABLE
LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select object_type from leo3 where %' order by 1;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
-------------------------------------------------- ----------- ---------- ----------
select object_type from leo3 where object_id=100 1 1 1
select object_type from leo3 where object_id=200 1 1 1
select object_type from leo3 where object_id=300 1 1 1
select object_type from leo3 where object_id=400 1 1 1
从视图上可以看出oracle认为这4条sql语句是完全不一样的，每执行一遍，都需要做一次硬解析。
绑定变量1
LEO1@LEO1> variable leo number; 定义变量
LEO1@LEO1> execute :leo:=100; 变量赋值 leo=100
PL/SQL procedure successfully completed.
LEO1@LEO1> select object_type from leo4 where object_id=:leo; 引用这个变量
OBJECT_TYPE
-------------------
EDITION
LEO1@LEO1> execute :leo:=200; 变量赋值 leo=200
PL/SQL procedure successfully completed.
LEO1@LEO1> select object_type from leo4 where object_id=:leo; 引用这个变量
OBJECT_TYPE
-------------------
INDEX
LEO1@LEO1> execute :leo:=300; 变量赋值 leo=300
PL/SQL procedure successfully completed.
LEO1@LEO1> select object_type from leo4 where object_id=:leo; 引用这个变量
OBJECT_TYPE
-------------------
TABLE
LEO1@LEO1> execute :leo:=400; 变量赋值 leo=400
PL/SQL procedure successfully completed.
LEO1@LEO1> select object_type from leo4 where object_id=:leo; 引用这个变量
OBJECT_TYPE
-------------------
TABLE
LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select object_type from leo4 where %' order by 1;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
-------------------------------------------------- ----------- ---------- --------------- ----------- ---------- --------------
select object_type from leo4 where object_id=:leo 4 1 4
SQL_TEXT：我们跟踪的sql语句
PARSE_CALLS：硬解析+软解析次数 其中硬解析1次+3次软解析
LOADS：硬解析次数 1次
EXECUTIONS：执行次数 4次
绑定变量：
Oracle认为这4条SQL是完全一样的（除了谓词部分）所以第一次执行的时候做一次硬解析后续3条SQL只做软解析，比上一个少了三次硬解析，性能提高
绑定变量2
LEO1@LEO1> begin
for leo in 1..4 loop
execute immediate 'select object_type from leo5 where object_id=:leo' using leo;
end loop;
end;
/
PL/SQL procedure successfully completed.
LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select object_type from leo5 where %' order by 1;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
-------------------------------------------------- ----------- ---------- --------------- ----------- ---------- --------------
select object_type from leo5 where object_id=:leo 1 1 4
绑定变量：
Oracle认为这4条SQL是完全一样的（除了谓词部分），和上面不同的是只做了1次硬解析没有软解析，反复执行了4次。我们做了一个循环，用leo变量代替谓词常量，每次都用相同的执行计划（执行计划不需要重新生成），只是改变一下常量值而已。
四 演示一个父游标产生3个子游标的示例，并分别说明每个子游标产生的原因
父游标：说白了就是SQL语句字符的哈希值，SQL文本一样它生成的哈希值就一样
子游标：区别相同SQL语句的一些其他不同
例如 1.不同用户下的相同SQL语句 2.不同权限下的相同SQL语句
父子游标：oracle要知道它们是同一个语句可共享相同的信息，还是不是同一个语句要区别对待
实验
LEO1@LEO1> conn leo1/leo1 切换到leo1用户
Connected.
LEO1@LEO1> create table t1 as select * from dba_objects; 创建t1表
Table created.
LEO1@LEO1> select count(*) from t1; 这是leo1用户下的t1表
COUNT(*)
----------
71973
LEO1@LEO1> conn leo2/leo2 切换到leo2用户
Connected.
LEO2@LEO1> create table t1 as select * from dba_objects; 也可以创建t1表
Table created.
LEO2@LEO1> select count(*) from t1; 这是leo2用户下的t1表
COUNT(*)
----------
71974
LEO2@LEO1> conn scott/tiger 切换到scott用户
Connected.
SCOTT@LEO1> create table t1 as select * from dba_objects; 又建立了一张t1表
Table created.
SCOTT@LEO1> select count(*) from t1; 这是scott用户下的t1表
COUNT(*)
----------
71975
LEO2@LEO1> select table_name,owner from dba_tables where table_name ='T1';
TABLE_NAME OWNER
---------- ------------------------------
T1 LEO2
T1 LEO1
T1 SCOTT
对象名相同，但是所属不同的用户，只要SQL语句一样，它的SQL_ID就一样，这时子游标就派上用场了，它可以区分不同属性的相同SQL。
SCOTT@LEO1> select sql_id,child_number,sql_text,parse_calls,plan_hash_value,loads from v$sql where sql_text='select count(*) from t1';
SQL_ID CHILD_NUMBER SQL_TEXT PARSE_CALLS PLAN_HASH_VALUE LOADS
------------- ------------ -------------------------------------------------- ----------- --------------- ------------------------- ----------
5bc0v4my7dvr5 0 select count(*) from t1 1 3724264953 1
5bc0v4my7dvr5 1 select count(*) from t1 1 3724264953 1
5bc0v4my7dvr5 2 select count(*) from t1 1 3724264953 1
SQL_ID：哈希值相同就认为是同一个父游标，用子游标来区分不同属性的相同SQL
CHILD_NUMBER：这个字段不同，说明oracle知道这是3个用户下的相同SQL语句
LOADS：都做了1次硬解析，说明oracle知道这是3个不完全相同的SQL语句
SCOTT@LEO1> select sql_id,child_number,child_address,sql_text from v$sql where sql_text='select count(*) from t1';
SQL_ID CHILD_NUMBER CHILD_ADDRESS SQL_TEXT
------------- ------------ ---------------- --------------------------------------------------
5bc0v4my7dvr5 0 000000007AEEDC28 select count(*) from t1
5bc0v4my7dvr5 1 000000007DB27088 select count(*) from t1
5bc0v4my7dvr5 2 000000007AEDCB88 select count(*) from t1
CHILD_ADDRESS：这列有3个值，说明oracle认为一个父游标5bc0v4my7dvr5对000000007AEEDC28
000000007DB27088 000000007AEDCB88 三个子游标，可知这3条看似相同的SQL在某些方面存在差异。如果在日常检查中发现有多个版本，要了解一下是什么原因导致出现这么多版本的！
PLAN_HASH_VALUE：这个列显示，这3条SQL语句都使用的是相同的执行计划，因为它们的执行计划哈希值都相同，我们输出执行计划来比较一下就明了了。
SCOTT@LEO1> select count(*) from t1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953 这是scott用户的哈希值
SCOTT@LEO1> conn leo1/leo1
Connected.
LEO1@LEO1> set autotrace trace explain
LEO1@LEO1> select count(*) from t1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953 这是leo1用户的哈希值
LEO1@LEO1> conn leo2/leo2
Connected.
LEO2@LEO1> set autotrace trace explain
LEO2@LEO1> select count(*) from t1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953 这是leo2用户的哈希值
执行计划中的哈希值和动态性能视图中的值一样，虽说不是完全一样的SQL，但走的执行计划全相同
LEO2@LEO1> select sql_id,auth_check_mismatch from v$sql_shared_cursor where sql_id='5bc0v4my7dvr5';
SQL_ID A
------------------------
5bc0v4my7dvr5 N
5bc0v4my7dvr5 Y
5bc0v4my7dvr5 Y
小结：对上述三条SQL_ID，进行不匹配检查，也显示不可共享游标信息。
五 演示ACS（adaptiver cursor sharing)的效果
变量窥视bind peeking：当一条SQL语句在硬解析时如果有绑定变量，会查看这个变量值，有了变量值便于得出正确的执行计划，在后续软解析过程中将不再查看这个变量值了。
变量窥视目的：就是为了得出正确的执行计划，第一条sql的执行计划的准确性是至关重要的，后面反复执行即可
变量窥视发生时刻：只发生在SQL硬解析的时候，不发生在SQL软解析的时候
变量窥视适合场景：
1.SQL执行计划不会因为变量值的不同而产生不同的执行计划，即执行计划几乎不改变
2.有大量用户并发
3.大量的除谓词外几乎相同的SQL
4.适合于OLTP场景
变量窥视不适合场景：
1. SQL执行计划会因为变量值的不同而产生不同的执行计划，就是oracle会因为不同谓词导致选择不同执行计划
2.SQL语句批量加载批量检索
3.不适合OLAP场景，因为OLAP执行计划与数据量的关系非常紧密，因此不适合绑定变量也就谈不上变量窥视问题
4.SQL解析对系统性能影响很小，所以绑定没有意义
实验
LEO1@LEO1> drop table leo8 purge; 清空环境
Table dropped.
LEO1@LEO1> create table leo8 as select 1 id,l.* from dba_objects l; 创建leo8表
Table created.
LEO1@LEO1> select id from leo8 where rownum<10;
ID
------------------
1
1
1
1
1
1
1
1
1
我们添加了1个ID列，值全为1，为了后面好对比测试效果
LEO1@LEO1> update leo8 set id=2 where rownum=1; 设置第一条记录的ID列值为2
1 row updated.
LEO1@LEO1> select id from leo8 where rownum<5; 除了第一条余下都为1
ID
-------------------
2
1
1
1
LEO1@LEO1> create index leo8_idx on leo8(id); 在ID列上创建一个B-tree索引
Index created.
LEO1@LEO1> execute
dbms_stats.gather_table_stats(ownname=>'LEO1',tabname=>'LEO8',method_opt=>'for all indexed columns size 254');
PL/SQL procedure successfully completed.
对leo8做一个表分析，分析一下占用了多少个块，表上有多少条记录等统计信息，还做了直方图分析，了解数据分布情况，数据的倾斜度
小结：上面创建了leo8表，ID列只有2个数值，id=2只有一条记录，剩下的全是id=1，目的就是构造一个数据严重倾斜的表，用来放大执行计划的效果。
绑定变量
LEO1@LEO1> variable i number; 首先定义变量i
LEO1@LEO1> execute :i:=1; 变量赋值i=1
PL/SQL procedure successfully completed.
LEO1@LEO1> alter session set sql_trace=true; 启动SQL追踪功能
Session altered.
LEO1@LEO1> select * from leo8 var_i_1 where id=:i; 变量值等于1时的SQL语句
……
LEO1@LEO1> execute :i:=2; 变量赋值i=2
PL/SQL procedure successfully completed.
LEO1@LEO1> select * from leo8 var_i_2 where id=:i; 变量值等于2时的SQL语句
……
说明：为了在trace文件中好分辨2个sql语句，我给起了2个别名，var_i_1表示变量值等于1时的SQL语句，var_i_2表示变量值等于2时的SQL语句。
LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like '%select count(*) from leo8%' order by 1;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
-------------------------------------------------- ----------- -------------------------- ----------- ---------- ------------------
select * from leo8 var_i_1 where id=:i 1 1 1
select * from leo8 var_i_2 where id=:i 1 1 1
由于表的别名不同，oracle认为这是2条不相同的SQL，因此都做了1次硬解析（hard parse），都执行了1次，oracle将会在硬解析时对绑定变量进行变量窥视bind peeking
LEO1@LEO1> select * from leo8 var_i_1 where id=:i; 变量值等于2时的结果集，应走索引
…..
我们第二次执行这条SQL，由于刚刚硬解析完，所以这次只做了1次软解析，而软解析时不会发生变量窥视。但我们要注意的是，此时变量i=2，不是之前的1了，从结果集上也可看出，按理说这条SQL应该走索引执行计划，i=2在列中只有1条，走索引是效率最高的，我们将会在后面trace文件中看看是不是这样
LEO1@LEO1> execute :i:=1; 重新赋予变量i=1
PL/SQL procedure successfully completed.
LEO1@LEO1> select * from leo8 var_i_2 where id=:i; 变量值等于1时的结果集，应走全表扫描
…..
我们重新赋予变量i=1，也是第二次执行这条SQL，它也刚硬解析完，这次也只是做软解析，不再进行变量窥视，由于i=1占了全表99.9%，应该走全表扫描，我们将会在后面trace文件中看看是不是这样
LEO1@LEO1> alter session set sql_trace=false; 关闭sql_trace功能
Session altered.
LEO1@LEO1> select value from v$diag_info where name='Default Trace File'; 写入的trace文件
VALUE
-------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_2494.trc
[oracle@leonarding1 trace]$ tkprof LEO1_ora_2494.trc bind_peeking.txt sys=no 过滤trace文件
TKPROF: Release 11.2.0.1.0 - Development on Sun Feb 3 14:28:54 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
看一下trace文件内容
[oracle@leonarding1 trace]$ vim bind_peeking.txt
SQL ID: 9n5fa1q0nvczd
Plan Hash: 948063081
select * from leo8 var_i_1 where id=:I 第一条sql语句，变量i=1
Misses in library cache during parse: 1 表示这是一次硬解析
Misses in library cache during execute: 1 表示执行了一次
Optimizer mode: ALL_ROWS 优化器模式：ALL_ROWS
Rows Row Source Operation
------- ---------------------------------------------------
71959 TABLE ACCESS FULL
在进行硬解析时发生了bind peeking，因此oracle知道谓词i=1，在和表分析的结果进行对比，知道i=1的记录占了全表的99.9%，所以采用全表扫描更优一些。
SQL ID: 9ux1nun51a030
Plan Hash: 948063081
select * from leo8 var_i_2 where id=:I 第二条sql语句，变量i=2
Misses in library cache during parse: 1 这也是一次硬解析
Misses in library cache during execute: 1 也执行了一次
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1 INDEX FAST FULL SCAN LEO8_IDX
在进行硬解析时发生了bind peeking，因此oracle知道谓词i=2，因为只有一条记录i=2，所以选择走索引更优一些，前面2条sql都是第一次执行，所以都发生硬解析，bind peeking只发生在硬解析阶段。
SQL ID: 1sguay77pxxhj
Plan Hash: 2836784050
select * from leo8 var_i_1 where id=:I 第三条sql语句，变量i=2
Misses in library cache during parse: 0 表示这是一次软解析，因为之前已经硬解析过了
Misses in library cache during execute: 1 表示执行了一次
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL
尽管i=2只有一条，oracle应然选择了之前的执行计划走的是全表扫描，这显然是错误的，应该走索引更优，之所以会选错，是因为SQL软解析时不会发生bind peeking，oracle不知道变量i的值是多少，没有窥视变量。
SQL ID: aam2chsgpj7mb
Plan Hash: 4156792434
select * from leo8 var_i_2 where id=:I 第四条sql语句，变量i=1
Misses in library cache during parse: 0 表示这是一次软解析，因为之前已经硬解析过了
Misses in library cache during execute: 1 表示执行了一次
Rows Row Source Operation
------- ---------------------------------------------------
71959 SORT AGGREGATE
71959 INDEX FAST FULL SCAN LEO8_IDX
执行这条sql时，oracle还是不知道变量i的值是多少，因为没有进行硬解析，只有软解析，不会发生bind peeking，oracle还是使用之前的执行计划来走，显然是错误的，这次绑定变量就是失败的。
小结：我们总结一下，之所以会导致oracle选择错误的执行计划，是因为bind peeking只能保证在第一次硬解析的时候选择正确的执行计划，后续软解析时不发生bind peeking，如果因为变量值的改变导致执行计划的改变，而我们还是一如既往绑定之前的执行计划，那么就会发生执行计划选择错误的严重后果。
Adaptive cursor sharing (ACS) 自适应游标共享
Adaptive cursor sharing：此技术就是用于改善上述不能及时bind peeking而导致错误执行计划的问题。
Oracle 11G新特性，它可以通过不间断观察绑定变量值，来决定新的SQL是否要继续使用之前的执行计划，解决bind peeking导致后续执行计划不变的问题。
缺点：
1.更多的硬解析，要不间断的bind peeking获取新变量值，增加了一些系统负荷
2.产生更多的子游标，需要更多的内存，凡是它认为值不能在共享前面的游标时就会产生一个子游标
3.消耗更多的CPU资源
4.消耗更多的LATCH资源
5.Oracle 11.1版本上ACS技术存在bug，会无限制的产生子游标，到11.2时这个bug就被修复了。
Oracle使用ACS的前提条件
1.绑定变量使用bind peeking
2.绑定变量的列上使用直方图，了解数据分布情况
实验
LEO1@LEO1> create table leo9 as select * from dba_objects; 创建表
Table created.
LEO1@LEO1> create index leo9_idx on leo9(object_id); 创建索引
Index created.
LEO1@LEO1> update leo9 set object_id=1 where rownum<=72006;
72006 rows updated.
LEO1@LEO1> update leo9 set object_id=2 where rownum<=100;
100 rows updated.
LEO1@LEO1> select count(*) from leo9 where object_id=1; object_id=1有71906
COUNT(*)
----------
71906
LEO1@LEO1> select count(*) from leo9 where object_id=2; object_id=2有100
COUNT(*)
----------
100
LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO9',method_opt=>'for all columns size 254');
PL/SQL procedure successfully completed.
做直方图分析，让oracle了解表数据分布情况
在没有绑定变量情况下看看如何选择执行计划的
LEO1@LEO1> set autotrace trace explain
LEO1@LEO1> select * from leo9 where object_id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 226982352
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71900 | 6670K| 288 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| LEO9 | 71900 | 6670K| 288 (1)| 00:00:04 |
--------------------------------------------------------------------------
object_id=1走全表扫描
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1)
LEO1@LEO1> select * from leo9 where object_id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3879848813
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 9500 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LEO9 | 100 | 9500 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LEO9_IDX | 100 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
object_id=2走索引
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
非常完美，各自都选择正确的执行计划了，该走全表扫描的走全表扫描，该走索引的走索引
绑定变量情况下看看如何选择执行计划的
LEO1@LEO1> alter system flush shared_pool; 清空shared_pool
System altered.
LEO1@LEO1> alter system flush shared_pool;
System altered.
LEO1@LEO1> alter system flush shared_pool; 多清空几遍比较保险
System altered.
LEO1@LEO1> select object_id,count(*) from leo9 group by object_id;
OBJECT_ID COUNT(*)
---------- ----------------- ------
1 71906
2 100
LEO1@LEO1> set autotrace traceonly
LEO1@LEO1> variable i number; 定义变量
LEO1@LEO1> execute :i:=1; 变量赋值object_id=1
PL/SQL procedure successfully completed.
LEO1@LEO1> select * from leo9 where object_id=:i; 引用变量where object_id=1
71906 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 226982352
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36003 | 3340K| 288 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| LEO9 | 36003 | 3340K| 288 (1)| 00:00:04 |
--------------------------------------------------------------------------
走全表扫描，正确的
LEO1@LEO1> execute :i:=2; 变量赋值object_id=2
PL/SQL procedure successfully completed.
LEO1@LEO1> select * from leo9 where object_id=:i; 引用变量where object_id=2
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3879848813
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 111 | 10545 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LEO9 | 111 | 10545 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LEO9_IDX | 111 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
走索引，正确的
LEO1@LEO1> select
sql_id,sql_text,plan_hash_value,child_number,executions,loads,buffer_gets,is_bind_sensitive,is_bind_aware,is_shareable from v$sql where sql_text like '%select * from leo9%' order by 1;
SQL_ID SQL_TEXT
PLAN_HASH_VALUE
CHILD_NUMBER
EXECUTIONS
LOADS
BUFFER_GETS
I I I
------------- -------------------------------------------------- --------------- ------------ ---------- ---------- ----------- - - -
252ghbzy0ynwd select * from leo9 where object_id=:i
3879848813
0
1
1
19
Y N Y
63937xfmcv3d2 EXPLAIN PLAN SET STATEMENT_ID='PLUS221275' FOR sel ect * from leo9 where object_id=:i
226982352
0
1
1
17
N N Y
1.从上面的执行计划可看出，变量值改变执行计划也跟着改变，oracle两次选择的都是正确的执行计划，说明ACS起作用了，每次都探测变量值，来决定执行计划是否重用。
2.从v$sql视图上看，
PLAN_HASH_VALUE：执行计划哈希值变化了，没用使用同一个执行计划
LOADS：每次都进行了硬解析，并在硬解析的时候bind peeking
IS_BIND_SENSITIVE：Y 表示ACS已经生效，会不间断探测变量值，已决定是否生成新的执行计划
IS_BIND_AWARE：Y 表示变量值的不同可能导致执行计划的改变
IS_SHAREABLE：是否允许游标重用，Y 表示游标可供下次重用，N 表示游标不可重用，说明这条SQL哈希值已经被剔除shared_pool
Leonarding
2013.2.3
天津&winter
分享技术~成就梦想
Blog：www.leonarding.com
