Oracle 變數繫結與變數窺視合集

不一樣的天空w發表於2017-04-15

Oracle 變數繫結與變數窺視合集》
http://blog.itpub.net/26686207/viewspace-753872/

資料庫環境

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_poollibrary 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_poolSQL語句的雜湊值建立一條通道(連線),即建立SQL語句控制程式碼,這個建立通道的過程就叫開啟遊標。

softer_soft_parse超軟解析:開啟遊標的過程是在軟解析之後,它要在shared_pool中尋找雜湊值(這個雜湊值就是軟解析之後得到的),如果沒有找到就需要重新構造遊標(這就是硬解析過程),如果遊標是開啟狀態,那麼會話可以直接使用開啟的遊標連線到shared_poolSQL語句入口,執行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';

'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                              2 68172

這是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';

 

'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認為這4sql語句是完全不一樣的,每執行一遍,都需要做一次硬解析。


繫結變數
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認為這4SQL是完全一樣的(除了謂詞部分)所以第一次執行的時候做一次硬解析後續3SQL只做軟解析,比上一個少了三次硬解析,效能提高


繫結變數
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認為這4SQL是完全一樣的(除了謂詞部分),和上面不同的是隻做了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認為一個父遊標5bc0v4my7dvr5000000007AEEDC28  000000007DB27088  000000007AEDCB88  三個子游標,可知這3條看似相同的SQL在某些方面存在差異。如果在日常檢查中發現有多個版本,要了解一下是什麼原因導致出現這麼多版本的!

PLAN_HASH_VALUE這個列顯示3SQL語句都使用的是相同的執行計劃,因為它們的執行計劃雜湊值都相同,我們輸出執行計劃來比較一下就明瞭了。

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進行不匹配檢查也顯示不可共享遊標資訊。

演示ACSadaptiver 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

我們新增了1ID列,值全為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檔案中好分辨2sql語句,我給起了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時的結果集,應走全表掃描

…..

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   2            1       2

select * from leo8 var_i_2 where id=:i   2            1       2


我們重新賦予變數
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,所以選擇走索引更優一些,前面2sql都是第一次執行,所以都發生硬解析,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 peekingoracle不知道變數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 peekingoracle還是使用之前的執行計劃來走,顯然是錯誤的,這次繫結變數就是失敗的。

小結:我們總結一下,之所以會導致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=171906

  COUNT(*)

----------

     71906

LEO1@LEO1> select count(*) from leo9 where object_id=2;       object_id=2100

  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_SENSITIVEY 表示ACS已經生效,會不間斷探測變數值,已決定是否生成新的執行計劃

IS_BIND_AWAREY 表示變數值的不同可能導致執行計劃的改變

IS_SHAREABLE:是否允許遊標重用,Y 表示遊標可供下次重用,N 表示遊標不可重用,說明這條SQL雜湊值已經被剔除shared_pool

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31397003/viewspace-2137338/,如需轉載,請註明出處,否則將追究法律責任。

相關文章