1.用示例說明綁定變量的應用領域是OLTP而不是OLAP。<br>
?2.用示例演示一次硬分析（hard parse)和一次軟分析（soft? parse），以及一次更軟的分析（softer soft parse)，并對給出演示結果。<br>
?3.用示例演示一次分析，多次執行的示例，并對給出演示結果。<br>
?4.演示一個父游標產生3個子游標的示例，并分別說明每個子游標產生的原因。<br>
?5.演示ACS（adaptiver cursor sharing)的效果。<br>

?==================================================================================
1.用示例說明綁定變量的應用領域是OLTP而不是OLAP。<br>

答：
?? ?1.1 示例說明
?? ?SQL> drop table t1 purge;

?? ?Table dropped.

?? ?SQL> create table t1 as select * from dba_objects;

?? ?Table created.

?? ?SQL> alter session set tracefile_identifier='bind_var';

?? ?Session altered.

?? ?SQL> alter session set sql_trace=true;

?? ?Session altered.

?? ?SQL> begin
?? ?? 2? for i in 1..100 loop
?? ?? 3? execute immediate 'select * from t1 where object_id=:i' using i;
?? ?? 4? end loop;
?? ?? 5? end;
?? ?? 6? /

?? ?PL/SQL procedure successfully completed.

?? ?SQL> alter session set sql_trace=false;

?? ?Session altered.

?? ?SQL> select sql_text,pase_calls,loads,executions from v$sql where sql_text like 'select * from t1 where %';
?? ?select sql_text,pase_calls,loads,executions from v$sql where sql_text like 'select * from t1 where %'
?? ??? ??? ??? ??? ?*
?? ?ERROR at line 1:
?? ?ORA-00904: "PASE_CALLS": invalid identifier


?? ?SQL> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select * from t1%';

?? ?SQL_TEXT?? ??? ??? ??? ??? ??? ??? ??? ??? ?PARSE_CALLS?? ??? ??? ?LOADS?? ?EXECUTIONS
?? ?-----------?? ??? ??? ??? ??? ??? ??? ??? ??? ?----------?? ??? ??? ?----?? ?----------
?? ?select * from t1 where object_id=:i?? ??? ??? ?1?? ??? ??? ??? ??? ?1??????? 100


?? ?從上面的查詢中，可以看到，在重復執行了100次的查詢中，有1次的硬解析，


?? ?1.2 下面再進行一個非綁定變量的SQL 執行情況：

?? ?SQL> alter session set sql_trace=true;????????????? 啟動trace功能

?? ?Session altered.

?? ?SQL> begin

?? ?for i in 1..100 loop

?? ?execute immediate 'select * from T where object_id='||i;

?? ?end loop;

?? ?end;

?? ?/

?? ?PL/SQL procedure successfully completed.

?? ?我們對一條sql執行了100次沒有采用綁定變量技術，oracle對這條sql要硬解析100次，執行100次，CPU的消耗就有了100次。

?? ?SQL> 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 T1 where %' order by 1;

?? ?SQL_TEXT?? ??? ??? ??? ??? ??? ??? ??? ?PARSE_CALLS????? LOADS EXECUTIONS
?? ?----------------------?? ??? ??? ??? ??? ?---------- ---------- ----------
?? ?select * from t1 where object_id=1??????? 1????????? 1????????? 1
?? ?....
?? ?select * from t1 where object_id=100????? 1????????? 1????????? 1


?? ?100 rows selected.

?? ?SQL>


?? ?再來查看一下跟蹤文件：

?? ?1.3 使用綁定變量跟蹤部分：

?? ?D:\app\oracle\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_5528_bind_var.trc out_b
?? ?ind.log

?? ?TKPROF: Release 11.2.0.3.0 - Development on 星期四 12月 5 17:58:35 2013

?? ?Copyright (c) 1982, 2011, Oracle and/or its affiliates.? All rights reserved.





?? ?SQL ID: 28gj7tsy13xq8 Plan Hash: 3617692013

?? ?select *
?? ?from
?? ? t1 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.00????????? 0????????? 1????????? 0?????????? 0
?? ?Fetch??????? 0????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
?? ?------- ------? -------- ---------- ---------- ---------- ----------? ----------
?? ?total????? 101????? 0.01?????? 0.00????????? 0????????? 1????????? 0?????????? 0

?? ?Misses in library cache during parse: 1
?? ?Optimizer mode: ALL_ROWS
?? ?Parsing user id: 84???? (recursive depth: 1)
?? ?Number of plan statistics captured: 1

?? ?Rows (1st) Rows (avg) Rows (max)? Row Source Operation
?? ?---------- ---------- ----------? ---------------------------------------------------
?? ??? ??? ? 0????????? 0????????? 0? TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=3 us cost=305 size=2484 card=12)

?? ?********************************************************************************


?? ?1.4 沒使用綁定變量跟蹤部分：
?? ?begin
?? ?for i in 1..100 loop
?? ?execute immediate 'select * from t1 where object_id='||i;
?? ?end loop;
?? ?end;

?? ?call???? count?????? cpu??? elapsed?????? disk????? query??? current??????? rows
?? ?------- ------? -------- ---------- ---------- ---------- ----------? ----------
?? ?Parse??????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
?? ?Execute????? 1????? 0.00?????? 0.01????????? 0????????? 0????????? 0?????????? 1
?? ?Fetch??????? 0????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
?? ?------- ------? -------- ---------- ---------- ---------- ----------? ----------
?? ?total??????? 2????? 0.00?????? 0.02????????? 0????????? 0????????? 0?????????? 1

?? ?Misses in library cache during parse: 1
?? ?Optimizer mode: ALL_ROWS
?? ?Parsing user id: 84 ?
?? ?********************************************************************************

?? ?SQL ID: 9vx4vjbr7qrnn Plan Hash: 2586623307

?? ?SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
?? ?? NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
?? ?? NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
?? ?? NVL(SUM(C2),:"SYS_B_1")
?? ?FROM
?? ? (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T1") FULL("T1")
?? ?? NO_PARALLEL_INDEX("T1") */ :"SYS_B_2" AS C1, CASE WHEN "T1"."OBJECT_ID"=
?? ?? :"SYS_B_3" THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "TANG"."T1"
?? ?? SAMPLE BLOCK (:"SYS_B_6" , :"SYS_B_7") SEED (:"SYS_B_8") "T1") SAMPLESUB


?? ?call???? count?????? cpu??? elapsed?????? disk????? query??? current??????? rows
?? ?------- ------? -------- ---------- ---------- ---------- ----------? ----------
?? ?Parse????? 100????? 0.00?????? 0.01????????? 0????????? 0????????? 0?????????? 0
?? ?Execute??? 100????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
?? ?Fetch????? 100????? 0.18?????? 0.16????????? 0?????? 6900????????? 0???????? 100
?? ?------- ------? -------- ---------- ---------- ---------- ----------? ----------
?? ?total????? 300????? 0.18?????? 0.17????????? 0?????? 6900????????? 0???????? 100

?? ?Misses in library cache during parse: 1
?? ?Misses in library cache during execute: 1
?? ?Optimizer mode: ALL_ROWS
?? ?Parsing user id: 84???? (recursive depth: 2)
?? ?Number of plan statistics captured: 3

?? ?Rows (1st) Rows (avg) Rows (max)? Row Source Operation
?? ?---------- ---------- ----------? ---------------------------------------------------
?? ??? ??? ? 1????????? 1????????? 1? SORT AGGREGATE (cr=69 pr=0 pw=0 time=1701 us)
?? ??? ?? 4452?????? 4452?????? 4452?? TABLE ACCESS SAMPLE T1 (cr=69 pr=0 pw=0 time=4608 us cost=19 size=128650 card=5146)

?? ?********************************************************************************



?? ?SQL ID: d44dqxf5hgz0j Plan Hash: 3617692013

?? ?select *
?? ?from
?? ? t1 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: 84???? (recursive depth: 1)
?? ?Number of plan statistics captured: 1

?? ?Rows (1st) Rows (avg) Rows (max)? Row Source Operation
?? ?---------- ---------- ----------? ---------------------------------------------------
?? ??? ??? ? 0????????? 0????????? 0? TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=2 us cost=305 size=2484 card=12)

?? ?********************************************************************************



?? ?從上面使用了綁定變量的方法，可以看到，執行100次，只有一次硬解析。
?? ?而非綁定變量方法，每一次的SQL,都是獨立解析及運行的。CPU的消耗也可以看到兩者的差異。

?? ?

?? ?1.4.綁定變量的SQL代碼優化，主要是減少了SQL 的硬解析。

?? ?1.5 在OLTP 架構中，
?? ??? ?1.5.1 SQL 簡單而非常相似，并且結果集非常小，不同的只是謂語部分。所以這種情況下，執行計劃都是一樣的。
?? ??? ?在執行計劃都幾乎不變的情況下。ORACLE使用變量來代替謂詞，使用同一個執行計劃，是非常合理的。

?? ??? ?1.5.2 SQL 重復率很高，或者只是謂詞條件不同而已；
?? ??? ?1.5.3 SQL 語句執行條件多，條數越多，CPU的消耗就大，這種情況下，減少硬解析就越有意義了。

?? ?1.6 在OLAP 架構中

?? ??? ?1.6.1 SQL 執行的重復率低，大部分都只是批量，定時加載，
?? ??? ?1.6.2 數據聚合操作頻繁；
?? ??? ?1.6.3 SQL 語句執行條數少，SQL 硬解析對系統性能影響較小，更多的瓶頸是IO，
?? ??? ?1.6.3 分區表的查詢，相對也不太適合綁定變量技術.
?? ??? ?綜上所述，所以說OLAP不適合使用綁定變量。

---------------------------------------------------------------------------------------------
?2.用示例演示一次硬分析（hard parse)和一次軟分析（soft? parse），以及一次更軟的分析（softer soft parse)，并對給出演示結果。<br>

?? ?ORACLE SQL 執行過程：
?? ? 數據庫端收到一個sql 請求后，會建立一個進程，與用戶進程組成一個會話，在pga區處理sql 請求。
?? ? 然后開始訪問sga的sharl pool,并解析SQL，生成執行計劃；
?? ?
?? ? 如果一條SQL 在share pool中已經存在，那么ORACLE 只需獎已存在的執行計劃應用到當前SQL上，去訪問數據即可。這種情況稱之為軟解析（SOFT PARSE);
?? ?如果在共享匯中沒有找到SQL的執行計劃，ORACLE就會對SQL 進行： 語法解析，語義解析，生成執行計劃等動作，這些解析步驟就總稱為硬解析（HARD PARSE);
?? ?而這些解析步驟是比較消耗資源的。

?? ?還有另外一種解析是：緩存游標信息，后續的SQL不用再去打開一個新的cursor,而是直接去share pool 中找到已查詢過的數據，這樣就更省資源，
?? ?成為更軟的解析（SOFTER SOFT PARSE).

?? ?2.1 演示數據準備及第一次執行效果
?? ?SQL> drop table t2 purge;

?? ?Table dropped.

?? ?SQL> create table t2 as select * from dba_objects;

?? ?Table created.

?? ?SQL> select count(0) from t2;

?? ?? COUNT(0)
?? ?----------
?? ??? ? 76429

?? ?查詢SQL 運行情況
?? ?SQL> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select count(0) from t2' order by

?? ?SQL_TEXT?? ??? ??? ??? ??? ??? ??? PARSE_CALLS?? ??? ?LOADS?? ??? ?EXECUTIONS
?? ?--------------------------------- -----------?? ??? ?----------?? ?----------
?? ?select count(0) from t2???????????????????? 1??????? 1????????? 1

?? ?
?? ?可以看到總解析 1次；硬解析 1次，執行次數 1 次；
?? ?下面我們再執行一次：

?? ?2.2 第二次執行效果

?? ?SQL> select count(0) from t2;

?? ?? COUNT(0)
?? ?----------
?? ??? ? 76429

?? ?SQL> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select count(0) from t2' order by

?? ?SQL_TEXT?? ??? ??? ??? ??? ??? ??? PARSE_CALLS?? ??? ?LOADS?? ??? ?EXECUTIONS
?? ?--------------------------------- -----------?? ??? ?----------?? ?----------
?? ?select count(0) from t2???????????????????? 2??????? 1????????? 2

?? ?這時可以看到總解析 2次；硬解析 1次，執行次數 2 次；?? ?第2次沒有進行硬解析，就是因為SQL 放在
?? ?SHARED_POOL中，進行了一次軟解析；
?? ?
?? ?2.3 我們再把SHARED_POOL清空，看看效果：


?? ?SQL> alter system flush shared_pool;

?? ?System altered.

?? ?SQL> select count(0) from t2;

?? ?? COUNT(0)
?? ?----------
?? ??? ? 76429

?? ?SQL> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select count(0) from t2' order by

?? ?SQL_TEXT?? ??? ??? ??? ??? ??? ??? ?PARSE_CALLS????? LOADS?? ??? ?EXECUTIONS
?? ?--------------------------------- -----------?? ??? ?----------?? ?----------
?? ?select count(0) from t2?? ??? ??? ??? ?1?? ??? ??? ??? ?1?? ??? ??? ?1


?? ?這時，ORACLE 認為是一條新的SQL了。所以又有一次硬解析。

?? ?2.4 softer_soft_parse 會話對游標的緩存

?? ??? ?SQL> alter session set tracefile_identifier='tang';
?? ??? ?Session altered.

?? ??? ?SQL> alter system flush shared_pool;

?? ??? ?System altered.

?? ??? ?SQL> alter session set events '10046 trace name context forever,level 12';

?? ??? ?Session altered.

?? ??? ?SQL> alter session set sql_trace=true;

?? ??? ?Session altered.

?? ??? ?SQL>


?? ??? ?SQL> show parameter session_cached_cursors;

?? ??? ?NAME???????????????????????????????? TYPE
?? ??? ?------------------------------------ ---------------------------------
?? ??? ?VALUE
?? ??? ?------------------------------
?? ??? ?session_cached_cursors?????????????? integer
?? ??? ?50
?? ??? ?SQL> set linesize 100;


?? ??? ?SQL> select 'STAT-'||name,value from v$sysstat where name like '%parse%' or name like'%cursor%'
?? ??? ?? 2? union all select 'LATCH-'||name,gets from v$latch where name = 'shared pool';

?? ??? ?'STAT-'||NAME?? ??? ??? ????? VALUE
?? ??? ?----------?? ?------------------------------
?? ??? ?STAT-opened cursors cumulative?? ??? ?10897
?? ??? ?STAT-opened cursors current?? ??? ?28
?? ??? ?STAT-pinned cursors current?? ??? ?12
?? ??? ?STAT-session cursor cache hits?? ??? ?12908
?? ??? ?STAT-session cursor cache count?? ??? ?882
?? ??? ?STAT-cursor authentications?? ??? ?237
?? ??? ?STAT-parse time cpu?? ??? ??? ?91
?? ??? ?STAT-parse time elapsed?? ??? ??? ?224
?? ??? ?STAT-parse count (total)?? ??? ?1752
?? ??? ?STAT-parse count (hard)?? ??? ??? ?1271
?? ??? ?STAT-parse count (failures)?? ??? ?0
?? ??? ?STAT-parse count (describe)?? ??? ?0
?? ??? ?LATCH-shared pool?? ??? ??? ?98158

?? ??? ?13 rows selected.

?? ??? ?SQL>


?? ??? ?SQL> select count(0) from t10;

?? ??? ?? COUNT(0)
?? ??? ?----------
?? ??? ??? ? 76432

?? ??? ?SQL> begin
?? ??? ?? 2? for i in 1..10000 loop
?? ??? ?? 3? execute immediate 'select count(0) from t10';
?? ??? ?? 4? end loop;
?? ??? ?? 5? end;
?? ??? ?? 6? /

?? ??? ?PL/SQL procedure successfully completed.

?? ??? ?SQL> select substr('STAT-'||name,1,50) as name,value from v$sysstat where name like '%parse%' or name like'%cursor%' un
?? ??? ?ets from v$latch where name = 'shared pool';

?? ??? ?NAME?? ??? ??? ??? ?VALUE
?? ??? ?----------------------------------------
?? ??? ?STAT-opened cursors cumulative?? ??? ?21492
?? ??? ?STAT-opened cursors current?? ??? ?29
?? ??? ?STAT-pinned cursors current?? ??? ?11
?? ??? ?STAT-session cursor cache hits?? ??? ?23418
?? ??? ?STAT-session cursor cache count?? ??? ?1033
?? ??? ?STAT-cursor authentications?? ??? ?262
?? ??? ?STAT-parse time cpu?? ??? ??? ?96
?? ??? ?STAT-parse time elapsed?? ??? ??? ?230
?? ??? ?STAT-parse count (total)?? ??? ?1887
?? ??? ?STAT-parse count (hard)?? ??? ??? ?1290
?? ??? ?STAT-parse count (failures)?? ??? ?0
?? ??? ?STAT-parse count (describe)?? ??? ?0
?? ??? ?LATCH-shared pool?? ??? ??? ?118288


?? ??? ?13 rows selected.

?? ??? ?SQL>


?? ??? ?SQL> alter system flush shared_pool;

?? ??? ?System altered.

?? ??? ?SQL> alter session set session_cached_cursors=0;

?? ??? ?Session altered.


?? ??? ?SQL> select substr('STAT-'||name,1,50) as name,value from v$sysstat where name like '%parse%' or name like'%cursor%' union all
?? ??? ?ets from v$latch where name = 'shared pool';

?? ??? ?NAME?? ??? ??? ??? ??? ?VALUE
?? ??? ?-------------------------------------------
?? ??? ?STAT-opened cursors cumulative?? ??? ?26250
?? ??? ?STAT-opened cursors current?? ??? ?28
?? ??? ?STAT-pinned cursors current?? ??? ?11
?? ??? ?STAT-session cursor cache hits?? ??? ?28300
?? ??? ?STAT-session cursor cache count?? ??? ?1671
?? ??? ?STAT-cursor authentications?? ??? ?363
?? ??? ?STAT-parse time cpu?? ??? ??? ?148
?? ??? ?STAT-parse time elapsed?? ??? ??? ?300
?? ??? ?STAT-parse count (total)?? ??? ?2855
?? ??? ?STAT-parse count (hard)?? ??? ??? ?1620
?? ??? ?STAT-parse count (failures)?? ??? ?0
?? ??? ?STAT-parse count (describe)?? ??? ?0
?? ??? ?LATCH-shared pool?? ??? ??? ?153871

?? ??? ?13 rows selected.


?? ??? ?SQL> select count(0) from t10;

?? ??? ?? COUNT(0)
?? ??? ?----------
?? ??? ??? ? 76432

?? ??? ?
?? ??? ?SQL> begin
?? ??? ?? 2? for i in 1..10000 loop
?? ??? ?? 3? execute immediate 'select count(0) from t10';
?? ??? ?? 4? end loop;
?? ??? ?? 5? end;
?? ??? ?? 6? /

?? ??? ?PL/SQL procedure successfully completed.

?? ??? ?SQL>

?? ??? ?SQL> select substr('STAT-'||name,1,50) as name,value from v$sysstat where name like '%parse%' or name like'%cursor%' union all
?? ??? ?ets from v$latch where name = 'shared pool';

?? ??? ?NAME?? ??? ??? ????? VALUE
?? ??? ?----------------------------------------
?? ??? ?STAT-opened cursors cumulative?? ??? ?39364
?? ??? ?STAT-opened cursors current?? ??? ?30
?? ??? ?STAT-pinned cursors current?? ??? ?10
?? ??? ?STAT-session cursor cache hits?? ??? ?34321
?? ??? ?STAT-session cursor cache count?? ??? ?1912
?? ??? ?STAT-cursor authentications?? ??? ?442
?? ??? ?STAT-parse time cpu?? ??? ??? ?184
?? ??? ?STAT-parse time elapsed?? ??? ??? ?337
?? ??? ?STAT-parse count (total)?? ??? ?13302
?? ??? ?STAT-parse count (hard)?? ??? ??? ?1814
?? ??? ?STAT-parse count (failures)?? ??? ?0
?? ??? ?STAT-parse count (describe)?? ??? ?0
?? ??? ?LATCH-shared pool?? ??? ??? ?177749


?? ??? ?13 rows selected.

?? ??? ?SQL>




?---------------------------------------------------------------------------------------------
?3.用示例演示一次分析，多次執行的示例，并對給出演示結果。<br>
?
?
?? ?3.1 使用不綁定變量，執行3次不同的SQL,是各自進行硬分析的：
?? ?SQL>? alter system flush shared_pool;

?? ?System altered.

?? ?SQL> select object_id from t10 where object_id=100;

?? ? OBJECT_ID
?? ?----------
?? ??? ??? 100

?? ?SQL> select object_id from t10 where object_id=200;

?? ? OBJECT_ID
?? ?----------
?? ??? ??? 200

?? ?SQL> select object_id from t10 where object_id=300;

?? ? OBJECT_ID
?? ?----------
?? ??? ??? 300


?? ?SQL> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select object_id from t10%'

?? ?SQL_TEXT?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?PARSE_CALLS????? LOADS EXECUTIONS
?? ?-----------?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?---------- ---------- ----------
?? ?select object_id from t10 where object_id=300????????? 1????????? 1????????? 1

?? ?select object_id from t10 where object_id=200????????? 1????????? 1????????? 1

?? ?select object_id from t10 where object_id=100????????? 1????????? 1????????? 1


?? ?SQL>

?? ?3.2 使用綁定變量

?? ?SQL> var vid number;
?? ?SQL> execute :vid:=100;

?? ?PL/SQL procedure successfully completed.

?? ?SQL> select object_id from t10 where object_id:=vid;
?? ?select object_id from t10 where object_id:=vid
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? *
?? ?ERROR at line 1:
?? ?ORA-00920: invalid relational operator


?? ?SQL> select object_id from t10 where object_id=:vid;

?? ? OBJECT_ID
?? ?----------
?? ??? ??? 100

?? ?SQL> execute :vid:=150;

?? ?PL/SQL procedure successfully completed.

?? ?SQL> select object_id from t10 where object_id=:vid;

?? ? OBJECT_ID
?? ?----------
?? ??? ??? 150

?? ?SQL> execute :vid:=160;

?? ?PL/SQL procedure successfully completed.

?? ?SQL> select object_id from t10 where object_id=:vid;

?? ? OBJECT_ID
?? ?----------
?? ??? ??? 160

?? ?SQL> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select object_id from t10%';

?? ?SQL_TEXT?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?PARSE_CALLS????? LOADS EXECUTIONS
?? ?----------------------?? ??? ??? ??? ??? ??? ??? ?----------- ---------- ----------
?? ?select object_id from t10 where object_id=300?????????? 1????????? 1????????? 1

?? ?select object_id from t10 where object_id=200????????? 1????????? 1????????? 1

?? ?select object_id from t10 where object_id=:vid????????? 3????????? 1????????? 3

?? ?select object_id from t10 where object_id=100????????? 1????????? 1????????? 1


?? ?SQL>

?? ?從上面最后的查詢看到，最后一次使用變量的方式執行的3次SQL,只進行了一次硬分析
?? ?Oracle認為這3條SQL是完全一樣的（除了謂詞部分）所以第一次執行的時候做一次硬解析后續2條SQL只做軟解析，比上一個少了2次硬解析，性能提高


?? ?綁定變量2


?? ?SQL> begin
?? ?? 2? for i in 1..3 loop
?? ?? 3? execute immediate 'select object_id from t10 where object_id=:i' using i;
?? ?? 4? end loop;
?? ?? 5? end;
?? ?? 6? /

?? ?PL/SQL procedure successfully completed.

?? ?SQL> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select object_id from t10%';

?? ?SQL_TEXT?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?PARSE_CALLS????? LOADS EXECUTIONS
?? ?--------------------------------------------?? ?----------- ---------- ----------
?? ?select object_id from t10 where object_id=300?? ??? ??? ?? 1????????? 1????????? 1

?? ?select object_id from t10 where object_id=200?? ??? ??? ?? 1????????? 1????????? 1

?? ?select object_id from t10 where object_id=:i?? ??? ??? ?? 1????????? 1????????? 3

?? ?select object_id from t10 where object_id=:vid?? ??? ??? ?? 3????????? 1????????? 3

?? ?select object_id from t10 where object_id=100?? ??? ??? ?? 1????????? 1????????? 1


?? ?SQL>

?? ?Oracle認為這3條SQL是完全一樣的（除了謂詞部分），和上面不同的是只做了1次硬解析沒有軟解析，反復執行了3次。
?? ?我們做了一個循環，用leo變量代替謂詞常量，每次都用相同的執行計劃（執行計劃不需要重新生成），只是改變一下常量值而已。
?? ?
?? ?上面3種執行方法，從上面的查詢中，很好的看出各自的不同，優點最明顯的就是最后一次的SQL.



?---------------------------------------------------------------------------------------------
?4.演示一個父游標產生3個子游標的示例，并分別說明每個子游標產生的原因。<br>

?? ?4.1 把用戶TANG.10 的查詢權限賦給 test,scott;

?? ?SQL> grant select on t10 to test;
?? ?Grant succeeded

?? ?SQL> grant select on t10 to scott;
?? ?Grant succeeded

?? ?SQL>
?? ?
?? ?4.2 在test,scott 用戶下各建立T10表；

?? ?C:\Users\Administrator>sqlplus test/test@orcl

?? ?SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 6 17:27:31 2013

?? ?Copyright (c) 1982, 2010, Oracle.? All rights reserved.


?? ?Connected to:
?? ?Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
?? ?With the Partitioning, OLAP, Data Mining and Real Application Testing options


?? ?SQL> create table t10 as select * from tang.t10;

?? ?Table created.

?? ?SQL> select count(1) from t10;

?? ?? COUNT(1)
?? ?----------
?? ??? ? 76430

?? ?SQL>

?? ?C:\Users\Administrator>sqlplus scott/scott@orcl

?? ?SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 6 17:30:24 2013

?? ?Copyright (c) 1982, 2010, Oracle.? All rights reserved.


?? ?Connected to:
?? ?Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
?? ?With the Partitioning, OLAP, Data Mining and Real Application Testing options

?? ?SQL> create table t10 as select * from tang.t10;

?? ?Table created.

?? ?SQL> select count(1) from t10;

?? ?? COUNT(1)
?? ?----------
?? ??? ? 76430

?? ?SQL>


?? ?4.3 在用用戶tang 查詢表


SQL> select count(1) from t10;

? COUNT(1)
----------
???? 76430

?? ?4.4 查詢子游標記錄
?SQL>select sql_id,child_number,sql_text,parse_calls,plan_hash_value,loads from v$sql
?where sql_text='select count(1) from t10';

?? ??? ?SQL_ID?? ?CHILD_NUMBER?? ?SQL_TEXT?? ?PARSE_CALLS?? ?PLAN_HASH_VALUE?? ?LOADS
?? ??? ?-----------------------------------------------------------------------
?? ?1?? ?9z6r9gcz6gnmz?? ?0?? ?select count(1) from t10?? ?3?? ?1331550546?? ?1
?? ?2?? ?9z6r9gcz6gnmz?? ?1?? ?select count(1) from t10?? ?2?? ?1331550546?? ?1
?? ?3?? ?9z6r9gcz6gnmz?? ?2?? ?select count(1) from t10?? ?2?? ?1331550546?? ?1


?? ?SQL_ID 相同，即說明是使用了同一個父游標，用子游標來區分不同屬性的相同SQL
?? ?CHILD_NUMBER：這個字段不同，說明oracle知道這是3個用戶下的相同SQL語句
?? ?LOADS：都做了1次硬解析，說明oracle知道這是3個不完全相同的SQL語句



?? ? select sql_id,child_number,child_address,sql_text
?? ? from v$sql where sql_text='select count(1) from t10';

?? ??? ??? ?SQL_ID?? ?CHILD_NUMBER?? ?CHILD_ADDRESS?? ?SQL_TEXT
?? ?1?? ?9z6r9gcz6gnmz?? ?0?? ??? ??? ?00000002AF6653C0?? ?select count(1) from t10
?? ?2?? ?9z6r9gcz6gnmz?? ?1?? ??? ??? ?00000002AF778630?? ?select count(1) from t10
?? ?3?? ?9z6r9gcz6gnmz?? ?2?? ??? ??? ?00000002AFD6C328?? ?select count(1) from t10

?? ?CHILD_ADDRESS 值不相同，即3個子游標的不同入口地址。
? ---------------------------------------------------------------------------------------------
?5.演示ACS（adaptiver cursor sharing)的效果。<br>

?? ?5.1 建立測試環境數據?? ?

?? ?SQL> drop table t purge;

?? ?Table dropped.

?? ?SQL> create table t as select case when rownum<=100 then 1 else 2 end as id from dual connect by rownum<1000000;

?? ?Table created.

?? ?SQL> create index idx_t_id on t(id);

?? ?Index created.

?? ?5.2 對表進行統計分析（ACS 要有直方圖信息）
?? ?SQL> execute dbms_stats.gather_table_stats(user,'T',method_opt=>'for all columns size 2');

?? ?PL/SQL procedure successfully completed.


?? ?SQL> set autotrace off;
?? ?SQL> select id,count(0) from t group by id;

?? ??? ??? ?ID?? COUNT(0)
?? ?---------- ----------
?? ??? ??? ? 1??????? 100
?? ??? ??? ? 2???? 999899


?? ?SQL> set autot trace exp;
?? ?SQL> select count(0) from t where id=1;

?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 1700799834

?? ?------------------------------------------------------------------------------
?? ?| Id? | Operation???????? | Name???? | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ?------------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT? |????????? |???? 1 |???? 3 |???? 3?? (0)| 00:00:01 |
?? ?|?? 1 |? SORT AGGREGATE?? |????????? |???? 1 |???? 3 |??????????? |????????? |
?? ?|*? 2 |?? INDEX RANGE SCAN| IDX_T_ID |?? 181 |?? 543 |???? 3?? (0)| 00:00:01 |
?? ?------------------------------------------------------------------------------

?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------

?? ??? 2 - access("ID"=1)

?? ?SQL> select count(0) from t where id=2;

?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 2966233522

?? ?---------------------------------------------------------------------------
?? ?| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ?---------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT?? |????? |???? 1 |???? 3 |?? 433?? (3)| 00:00:06 |
?? ?|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |???? 3 |??????????? |????????? |
?? ?|*? 2 |?? TABLE ACCESS FULL| T??? |?? 999K|? 2928K|?? 433?? (3)| 00:00:06 |
?? ?---------------------------------------------------------------------------

?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------

?? ??? 2 - filter("ID"=2)

?? ?SQL>?? ?
?? ?從上面兩個執行計劃可以看出，兩個執行計劃使用了ACS，走了不同的執行計劃；

?? ?5.3 使用綁定變量執行SQL,并查看不同變量時的變化
?? ?SQL> alter session set sql_trace=true;

?? ?Session altered.

?? ?SQL>? alter session set tracefile_identifier='tang'
?? ?? 2? ;

?? ?Session altered.

?? ?SQL> var v_num number;
?? ?SQL> execute :v_num:=2;

?? ?PL/SQL procedure successfully completed.

?? ?SQL> select count(0) from t where id=:v_num;

?? ?? COUNT(0)
?? ?----------
?? ??? ?999899

?? ?SQL> execute :v_num:=1;

?? ?PL/SQL procedure successfully completed.

?? ?SQL> select count(0) from t where id=:v_num;

?? ?? COUNT(0)
?? ?----------
?? ??? ??? 100

?? ?SQL> select count(0) from t where id=:v_num;

?? ?? COUNT(0)
?? ?----------
?? ??? ??? 100

?? ?SQL> alter session set sql_trace=false;

?? ?Session altered.

?? ?SQL>? select value from v$diag_info where name='Default Trace File';

?? ?VALUE
?? ?--------------------------------------------------------------------------------
?? ?D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_ora_5952_tang.trc

?? ?SQL>

?? ?跟蹤文件如下：
?? ?
?? ?5.3.1 定義變量為2 時的執行計劃：?? ?
?? ?SQL ID: 9qvxqz87xn8vt Plan Hash: 0

?? ?BEGIN :v_num:=2; END;


?? ?call???? count?????? cpu??? elapsed?????? disk????? query??? current??????? rows
?? ?------- ------? -------- ---------- ---------- ---------- ----------? ----------
?? ?Parse??????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
?? ?Execute????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 1
?? ?Fetch??????? 0????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
?? ?------- ------? -------- ---------- ---------- ---------- ----------? ----------
?? ?total??????? 2????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 1

?? ?Misses in library cache during parse: 0
?? ?Optimizer mode: ALL_ROWS
?? ?Parsing user id: 84 ?
?? ?********************************************************************************

?? ?--執行綁定變量SQL，我們可以看執行計劃為全表掃描，這是沒有問題的，因為數據比較傾斜，
?? ?id為2的記錄很多，之前的測試也是全表掃描
?--但是我們要注意，這個SQL語句執行了2次，也就是全表掃描的方式執行了2次，
?也就是當值為1的第一次執行走的是全表掃描。

?5.3.2 定義變量為1 時， 已重新進行了一個硬分析。走的也是全表掃描，這點和想象的不一樣。
?感覺很奇怪，
?? ?SQL ID: fdp2j9cdw8pc5 Plan Hash: 0

?? ?BEGIN :v_num:=1; END;


?? ?call???? count?????? cpu??? elapsed?????? disk????? query??? current??????? rows
?? ?------- ------? -------- ---------- ---------- ---------- ----------? ----------
?? ?Parse??????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
?? ?Execute????? 1????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 1
?? ?Fetch??????? 0????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 0
?? ?------- ------? -------- ---------- ---------- ---------- ----------? ----------
?? ?total??????? 2????? 0.00?????? 0.00????????? 0????????? 0????????? 0?????????? 1

?? ?Misses in library cache during parse: 0
?? ?Optimizer mode: ALL_ROWS
?? ?Parsing user id: 84 ?
?? ?********************************************************************************

?? ?再看一下是否有錯：
?? ?從下面的測試中看到，不管我使用變量 1或者2 ，走的都是全表掃描。并沒有走索引。
?? ?
?? ?查看 is_bind_sensitive
?????? 表示游標是否對綁定變量敏感。數值如果為Y，表示當綁定變量的數值發生變化后，
?? ??? 優化器有可能會產生一個不同的執行計劃，簡單說就是ACS生效了。
?? ??? 我這里就是因為這個，一直是N,如下：
?? ??? ?
?? ?

SQL> select sql_id,child_number,executions,loads,buffer_gets,is_bind_sensitive,is_bind_aware from v$sql where sql_text like 'select count(0) from t where id=:v_n';

SQL_ID????????????????????????????????? CHILD_NUMBER EXECUTIONS????? LOADS
--------------------------------------- ------------ ---------- ----------
BUFFER_GETS bin bin bin
----------- --- --- ---
3fvuhx84ba43x????????????????????????????????????? 0????????? 0????????? 1
????????? 0 N?? N?? Y


SQL> exec :n :=2;

PL/SQL procedure successfully completed.

SQL> select count(*) from t where id=:n;

? COUNT(*)
----------
??? 999899

SQL> select sql_id,child_number,executions,loads,buffer_gets,is_bind_sensitive as "bind_sensi",is_bind_aware as "
re" from v$sql where sql_text like 'select count(*) from t where id=:n';

SQL_ID????????????????????????????????? CHILD_NUMBER EXECUTIONS????? LOADS
--------------------------------------- ------------ ---------- ----------
BUFFER_GETS bin bin bin
----------- --- --- ---
3fvuhx84ba43x????????????????????????????????????? 0????????? 1????????? 1
?????? 1527 N?? N?? Y


SQL> exec :n :=2;

PL/SQL procedure successfully completed.

SQL> select count(*) from t where id=:n;

? COUNT(*)
----------
??? 999899

SQL> select sql_id,child_number,executions,loads,buffer_gets,is_bind_sensitive as "bind_sensi",is_bind_aware as "
re" from v$sql where sql_text like 'select count(*) from t where id=:n';

SQL_ID????????????????????????????????? CHILD_NUMBER EXECUTIONS????? LOADS
--------------------------------------- ------------ ---------- ----------
BUFFER_GETS bin bin bin
----------- --- --- ---
3fvuhx84ba43x????????????????????????????????????? 0????????? 2????????? 1
?????? 3054 N?? N?? Y


SQL>
SQL> exec :n :=1;

PL/SQL procedure successfully completed.

SQL> select count(*) from t where id=:n;

? COUNT(*)
----------
?????? 100

SQL> select sql_id,child_number,executions,loads,buffer_gets,is_bind_sensitive as "bind_sensi",is_bind_aware as "
re" from v$sql where sql_text like 'select count(*) from t where id=:n';

SQL_ID????????????????????????????????? CHILD_NUMBER EXECUTIONS????? LOADS
--------------------------------------- ------------ ---------- ----------
BUFFER_GETS bin bin bin
----------- --- --- ---
3fvuhx84ba43x????????????????????????????????????? 0????????? 3????????? 1
?????? 4581 N?? N?? Y


SQL>

總結

以上是生活随笔為你收集整理的【性能优化】 之 变量邦定的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。