[20200129]子游標不共享BIND_EQUIV_FAILURE.txt
[20200129]子游標不共享BIND_EQUIV_FAILURE.txt
--//生產系統再次遇到大量BIND_EQUIV_FAILURE原因導致子游標的情況。我看了我以前測試遇到的情況。
--//連結 http://blog.itpub.net/267265/viewspace-2156139/ =>[20180613]子游標不共享BIND_EQUIV_FAILURE。
--//別人曾經給我建議，問題可能出在alter session set statistics_level=all;的設定上，我也重複測試看看。
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
SYS@test> @ hide _cursor_obsolete_threshold
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
-------------------------- ----------------------------------------------- ------------- ------------- ------------ ----- ---------
_cursor_obsolete_threshold Number of cursors per parent before obsoletion. TRUE 8192 8192 TRUE FALSE
/*
grant execute on sys.dbms_lock to scott;
CREATE OR REPLACE FUNCTION sleep1 (seconds IN NUMBER)
RETURN NUMBER
is
d_date date;
BEGIN
select sysdate into d_date from dual;
sys.dbms_lock.sleep(seconds/10);
RETURN seconds;
END;
/
CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)
RETURN NUMBER
is
d_date date;
BEGIN
select sysdate into d_date from dual;
--//sys.dbms_lock.sleep(0.01);
RETURN seconds;
END;
/
*/
--//注：當時的測試因為別的原因執行如上程式碼，現在測試不需要。
create table t as select rownum id1,mod(rownum-1,1000)+1 id2 from dual connect by level<=2000;
SCOTT@test01p> select * from dba_extents where owner=user and segment_name='T'
2 @ prxx
==============================
OWNER : SCOTT
SEGMENT_NAME : T
PARTITION_NAME :
SEGMENT_TYPE : TABLE
TABLESPACE_NAME : USERS
EXTENT_ID : 0
FILE_ID : 11
BLOCK_ID : 176
BYTES : 65536
BLOCKS : 8
RELATIVE_FNO : 11
PL/SQL procedure successfully completed.
2.建立測試指令碼:
--//建立指令碼by.txt,註解alter session set statistics_level=all;:
set term off
--//alter session set statistics_level=all;
variable x number;
exec :x := &&1;
SElect t.* from t where id2<=:x;
set term on
@ dpc '' ''
quit
--//建立shell指令碼by.sh:
#!/bin/bash
# rm -f ez.txt
for i in $(seq 1000)
do
sqlplus -s -l scott/btbtms@test01p @by.txt $i >> ez.txt
done
--//在家裡的筆記本上測試有點慢，減少迴圈到1000次。
3.測試：
--//執行指令碼by.sh.
$ grep "SQL_ID" ez.txt | uniq -c
1000 SQL_ID ckynkwp4t00rz, child number 0
--//可以發現並沒有產生子游標。確實像別人講的那樣。
4.繼續測試:
--//修改指令碼by.txt.取消註解alter session set statistics_level=all;.
set term off
alter session set statistics_level=all;
variable x number;
exec :x := &&1;
SElect t.* from t where id2<=:x;
set term on
@ dpc '' ''
quit
--//重新整理共享池3次。
alter session set statistics_level=all;
alter session set statistics_level=all;
alter session set statistics_level=all;
$ mv ez.txt ezold.txt
--//執行指令碼by.sh
$ grep "SQL_ID" ez.txt | uniq -c
500 SQL_ID ckynkwp4t00rz, child number 0
51 SQL_ID ckynkwp4t00rz, child number 1
56 SQL_ID ckynkwp4t00rz, child number 2
61 SQL_ID ckynkwp4t00rz, child number 3
67 SQL_ID ckynkwp4t00rz, child number 4
74 SQL_ID ckynkwp4t00rz, child number 5
81 SQL_ID ckynkwp4t00rz, child number 6
90 SQL_ID ckynkwp4t00rz, child number 7
20 SQL_ID ckynkwp4t00rz, child number 8
--//在執行過程中,可以發現並出現大量子游標.
SCOTT@test01p> @ share ckynkwp4t00rz
old 15: and q.sql_id like ''&1''',
new 15: and q.sql_id like ''ckynkwp4t00rz''',
SQL_TEXT = SElect t.* from t where id2<=:x
SQL_ID = ckynkwp4t00rz
ADDRESS = 000007FF12B9AA10
CHILD_ADDRESS = 000007FF1265EDE8
CHILD_NUMBER = 0
LOAD_OPTIMIZER_STATS = Y
REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>39</ID><reason>Bind mismatch(25)</reason><size>0x0</size><details>extended_cursor_sharing</details></ChildNode>
--------------------------------------------------
SQL_TEXT = SElect t.* from t where id2<=:x
SQL_ID = ckynkwp4t00rz
ADDRESS = 000007FF12B9AA10
CHILD_ADDRESS = 000007FF16F0F400
CHILD_NUMBER = 1
REASON = <ChildNode><ChildNumber>1</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>2540213050</init_ranges_in_first_pass></ChildNode>
--------------------------------------------------
SQL_TEXT = SElect t.* from t where id2<=:x
SQL_ID = ckynkwp4t00rz
ADDRESS = 000007FF12B9AA10
CHILD_ADDRESS = 000007FF13C989F8
CHILD_NUMBER = 2
BIND_EQUIV_FAILURE = Y
REASON = <ChildNode><ChildNumber>2</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>2841161709</init_ranges_in_first_pass></ChildNode>
--------------------------------------------------
...
--------------------------------------------------
SQL_TEXT = SElect t.* from t where id2<=:x
SQL_ID = ckynkwp4t00rz
ADDRESS = 000007FF12B9AA10
CHILD_ADDRESS = 000007FF13F41A10
CHILD_NUMBER = 8
BIND_EQUIV_FAILURE = Y
REASON =
--------------------------------------------------
PL/SQL procedure successfully completed.
$ grep "SQL_ID" ez.txt | uniq -c | awk '{ sum=sum+$1};END {print sum}'
1000
--//正好1000次。
--//可以看出設定alter session set statistics_level=all;導致出現子游標不能共享，具體原因是什麼不清楚。
--//我反覆測試多次,結果都是一樣。
5.建立直方圖呢？
SCOTT@test01p> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1024 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
--//12c 可以支援bucket大於254.
SCOTT@test01p> @ tab_lh scott t ''
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER TABLE_NAME COLUMN
SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME]
IF NOT INPUT COLUMN_NAME ,USE "" .
COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM DATA_DEFAULT
----------- --------- ----------- - ------------ ---------- ----------- --------- ---------- ---------- ----------- ------------------- ---------- -------------------------
ID1 NUMBER 22 Y 2000 .0005 2000 1 2000 0 1024 2020-01-29 19:44:04 HYBRID
ID2 NUMBER 22 Y 1000 .00025 2000 1 1000 0 1000 2020-01-29 19:44:04 FREQUENCY
--//重新整理共享池3次。
alter session set statistics_level=all;
alter session set statistics_level=all;
alter session set statistics_level=all;
--//執行指令碼by.txt。
set term off
--//alter session set statistics_level=all;
variable x number;
exec :x := &&1;
SElect t.* from t where id2<=:x;
set term on
@ dpc '' ''
quit
$ mv ez.txt ez17.txt
$ grep "SQL_ID" ez.txt | uniq -c
1000 SQL_ID ckynkwp4t00rz, child number 0
--//沒有子游標產生。
SCOTT@test01p> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 254 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@test01p> @ tab_lh scott t ''
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER TABLE_NAME COLUMN
SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME]
IF NOT INPUT COLUMN_NAME ,USE "" .
COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM DATA_DEFAULT
----------- --------- ----------- - ------------ ---------- ----------- --------- ---------- --------- ----------- ------------------- --------- ------------
ID1 NUMBER 22 Y 2000 .0005 2000 1 2000 0 254 2020-01-29 20:01:43 HYBRID
ID2 NUMBER 22 Y 1000 .001 2000 1 1000 0 254 2020-01-29 20:01:43 HYBRID
--//重新整理共享池3次。
alter session set statistics_level=all;
alter session set statistics_level=all;
alter session set statistics_level=all;
$ mv ez.txt ez18.txt
$ grep "SQL_ID" ez.txt | uniq -c
1000 SQL_ID ckynkwp4t00rz, child number 0
--//沒有子游標產生。
--//也就是與直方圖無關。
6.繼續測試：
--//取消直方圖設定。
SCOTT@test01p> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
$ cat by.txt
set term off
alter session set statistics_level=all;
variable x number;
exec :x := &&1;
SElect t.* from t where id1<=:x;
set term on
@ dpc '' ''
quit
$ cat by.sh
#!/bin/bash
# rm -f ez.txt
for i in $(seq 2000 )
do
sqlplus -s -l scott/btbtms@test01p @by.txt $i >> ez.txt
done
alter system flush shared_pool;
--//注意查新條件是id1<= :x,驗證在x=1000後是否出現子游標。
SCOTT@test01p> @ share basmuva6swhg4
SQL_TEXT = SElect t.* from t where id1<=:x
SQL_ID = basmuva6swhg4
ADDRESS = 000007FF1314E908
CHILD_ADDRESS = 000007FF13133298
CHILD_NUMBER = 0
LOAD_OPTIMIZER_STATS = Y
REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>39</ID><reason>Bind mismatch(25)</reason><size>0x0</size><details>extended_cursor_sharing</details></ChildNode>
--------------------------------------------------
SQL_TEXT = SElect t.* from t where id1<=:x
SQL_ID = basmuva6swhg4
ADDRESS = 000007FF1314E908
CHILD_ADDRESS = 000007FF13270B40
CHILD_NUMBER = 1
REASON = <ChildNode><ChildNumber>1</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>3229815407</init_ranges_in_first_pass></ChildNode>
--------------------------------------------------
SQL_TEXT = SElect t.* from t where id1<=:x
SQL_ID = basmuva6swhg4
ADDRESS = 000007FF1314E908
CHILD_ADDRESS = 000007FF1343C4A0
CHILD_NUMBER = 2
BIND_EQUIV_FAILURE = Y
REASON = <ChildNode><ChildNumber>2</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>2954937500</init_ranges_in_first_pass></ChildNode>
..
PL/SQL procedure successfully completed.
$ grep "SQL_ID" ez.txt | uniq -c
1000 SQL_ID basmuva6swhg4, child number 0
101 SQL_ID basmuva6swhg4, child number 1
111 SQL_ID basmuva6swhg4, child number 2
122 SQL_ID basmuva6swhg4, child number 3
134 SQL_ID basmuva6swhg4, child number 4
147 SQL_ID basmuva6swhg4, child number 5
162 SQL_ID basmuva6swhg4, child number 6
178 SQL_ID basmuva6swhg4, child number 7
45 SQL_ID basmuva6swhg4, child number 8
$ grep "SQL_ID" ez.txt | uniq -c | awk 'BEGIN {a=909;} {sum=sum+$1;a=a*1.10;print sum, a }'
1000 999.9
1101 1099.89
1212 1209.88
1334 1330.87
1468 1463.95
1615 1610.35
1777 1771.38
1955 1948.52
2000 2143.37
--//可以看出一個規律返回1000條記錄是第1道坎，以後大約按照0.11的比例增加(最後一行測試不足不算)。當然這僅僅是我的猜測。
7.繼續測試：
--//翻轉執行看看，先執行2000：
$ cat by.sh
#!/bin/bash
# rm -f ez.txt
for i in $(seq 2000 -1 1 )
do
sqlplus -s -l scott/btbtms@test01p @by.txt $i >> ez.txt
done
$ grep "SQL_ID" ez.txt | uniq -c
2000 SQL_ID basmuva6swhg4, child number 0
--//並沒有產生子游標。
--//我現在僅僅估計出現BIND_EQUIV_FAILURE的原因可能是返回記錄出現很大變化時就會出現。
[20200129]子游標不共享BIND_EQUIV_FAILURE.txt
--//生產系統再次遇到大量BIND_EQUIV_FAILURE原因導致子游標的情況。我看了我以前測試遇到的情況。
--//連結 http://blog.itpub.net/267265/viewspace-2156139/ =>[20180613]子游標不共享BIND_EQUIV_FAILURE。
--//別人曾經給我建議，問題可能出在alter session set statistics_level=all;的設定上，我也重複測試看看。
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
SYS@test> @ hide _cursor_obsolete_threshold
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
-------------------------- ----------------------------------------------- ------------- ------------- ------------ ----- ---------
_cursor_obsolete_threshold Number of cursors per parent before obsoletion. TRUE 8192 8192 TRUE FALSE
/*
grant execute on sys.dbms_lock to scott;
CREATE OR REPLACE FUNCTION sleep1 (seconds IN NUMBER)
RETURN NUMBER
is
d_date date;
BEGIN
select sysdate into d_date from dual;
sys.dbms_lock.sleep(seconds/10);
RETURN seconds;
END;
/
CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)
RETURN NUMBER
is
d_date date;
BEGIN
select sysdate into d_date from dual;
--//sys.dbms_lock.sleep(0.01);
RETURN seconds;
END;
/
*/
--//注：當時的測試因為別的原因執行如上程式碼，現在測試不需要。
create table t as select rownum id1,mod(rownum-1,1000)+1 id2 from dual connect by level<=2000;
SCOTT@test01p> select * from dba_extents where owner=user and segment_name='T'
2 @ prxx
==============================
OWNER : SCOTT
SEGMENT_NAME : T
PARTITION_NAME :
SEGMENT_TYPE : TABLE
TABLESPACE_NAME : USERS
EXTENT_ID : 0
FILE_ID : 11
BLOCK_ID : 176
BYTES : 65536
BLOCKS : 8
RELATIVE_FNO : 11
PL/SQL procedure successfully completed.
2.建立測試指令碼:
--//建立指令碼by.txt,註解alter session set statistics_level=all;:
set term off
--//alter session set statistics_level=all;
variable x number;
exec :x := &&1;
SElect t.* from t where id2<=:x;
set term on
@ dpc '' ''
quit
--//建立shell指令碼by.sh:
#!/bin/bash
# rm -f ez.txt
for i in $(seq 1000)
do
sqlplus -s -l scott/btbtms@test01p @by.txt $i >> ez.txt
done
--//在家裡的筆記本上測試有點慢，減少迴圈到1000次。
3.測試：
--//執行指令碼by.sh.
$ grep "SQL_ID" ez.txt | uniq -c
1000 SQL_ID ckynkwp4t00rz, child number 0
--//可以發現並沒有產生子游標。確實像別人講的那樣。
4.繼續測試:
--//修改指令碼by.txt.取消註解alter session set statistics_level=all;.
set term off
alter session set statistics_level=all;
variable x number;
exec :x := &&1;
SElect t.* from t where id2<=:x;
set term on
@ dpc '' ''
quit
--//重新整理共享池3次。
alter session set statistics_level=all;
alter session set statistics_level=all;
alter session set statistics_level=all;
$ mv ez.txt ezold.txt
--//執行指令碼by.sh
$ grep "SQL_ID" ez.txt | uniq -c
500 SQL_ID ckynkwp4t00rz, child number 0
51 SQL_ID ckynkwp4t00rz, child number 1
56 SQL_ID ckynkwp4t00rz, child number 2
61 SQL_ID ckynkwp4t00rz, child number 3
67 SQL_ID ckynkwp4t00rz, child number 4
74 SQL_ID ckynkwp4t00rz, child number 5
81 SQL_ID ckynkwp4t00rz, child number 6
90 SQL_ID ckynkwp4t00rz, child number 7
20 SQL_ID ckynkwp4t00rz, child number 8
--//在執行過程中,可以發現並出現大量子游標.
SCOTT@test01p> @ share ckynkwp4t00rz
old 15: and q.sql_id like ''&1''',
new 15: and q.sql_id like ''ckynkwp4t00rz''',
SQL_TEXT = SElect t.* from t where id2<=:x
SQL_ID = ckynkwp4t00rz
ADDRESS = 000007FF12B9AA10
CHILD_ADDRESS = 000007FF1265EDE8
CHILD_NUMBER = 0
LOAD_OPTIMIZER_STATS = Y
REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>39</ID><reason>Bind mismatch(25)</reason><size>0x0</size><details>extended_cursor_sharing</details></ChildNode>
--------------------------------------------------
SQL_TEXT = SElect t.* from t where id2<=:x
SQL_ID = ckynkwp4t00rz
ADDRESS = 000007FF12B9AA10
CHILD_ADDRESS = 000007FF16F0F400
CHILD_NUMBER = 1
REASON = <ChildNode><ChildNumber>1</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>2540213050</init_ranges_in_first_pass></ChildNode>
--------------------------------------------------
SQL_TEXT = SElect t.* from t where id2<=:x
SQL_ID = ckynkwp4t00rz
ADDRESS = 000007FF12B9AA10
CHILD_ADDRESS = 000007FF13C989F8
CHILD_NUMBER = 2
BIND_EQUIV_FAILURE = Y
REASON = <ChildNode><ChildNumber>2</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>2841161709</init_ranges_in_first_pass></ChildNode>
--------------------------------------------------
...
--------------------------------------------------
SQL_TEXT = SElect t.* from t where id2<=:x
SQL_ID = ckynkwp4t00rz
ADDRESS = 000007FF12B9AA10
CHILD_ADDRESS = 000007FF13F41A10
CHILD_NUMBER = 8
BIND_EQUIV_FAILURE = Y
REASON =
--------------------------------------------------
PL/SQL procedure successfully completed.
$ grep "SQL_ID" ez.txt | uniq -c | awk '{ sum=sum+$1};END {print sum}'
1000
--//正好1000次。
--//可以看出設定alter session set statistics_level=all;導致出現子游標不能共享，具體原因是什麼不清楚。
--//我反覆測試多次,結果都是一樣。
5.建立直方圖呢？
SCOTT@test01p> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1024 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
--//12c 可以支援bucket大於254.
SCOTT@test01p> @ tab_lh scott t ''
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER TABLE_NAME COLUMN
SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME]
IF NOT INPUT COLUMN_NAME ,USE "" .
COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM DATA_DEFAULT
----------- --------- ----------- - ------------ ---------- ----------- --------- ---------- ---------- ----------- ------------------- ---------- -------------------------
ID1 NUMBER 22 Y 2000 .0005 2000 1 2000 0 1024 2020-01-29 19:44:04 HYBRID
ID2 NUMBER 22 Y 1000 .00025 2000 1 1000 0 1000 2020-01-29 19:44:04 FREQUENCY
--//重新整理共享池3次。
alter session set statistics_level=all;
alter session set statistics_level=all;
alter session set statistics_level=all;
--//執行指令碼by.txt。
set term off
--//alter session set statistics_level=all;
variable x number;
exec :x := &&1;
SElect t.* from t where id2<=:x;
set term on
@ dpc '' ''
quit
$ mv ez.txt ez17.txt
$ grep "SQL_ID" ez.txt | uniq -c
1000 SQL_ID ckynkwp4t00rz, child number 0
--//沒有子游標產生。
SCOTT@test01p> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 254 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@test01p> @ tab_lh scott t ''
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER TABLE_NAME COLUMN
SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME]
IF NOT INPUT COLUMN_NAME ,USE "" .
COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM DATA_DEFAULT
----------- --------- ----------- - ------------ ---------- ----------- --------- ---------- --------- ----------- ------------------- --------- ------------
ID1 NUMBER 22 Y 2000 .0005 2000 1 2000 0 254 2020-01-29 20:01:43 HYBRID
ID2 NUMBER 22 Y 1000 .001 2000 1 1000 0 254 2020-01-29 20:01:43 HYBRID
--//重新整理共享池3次。
alter session set statistics_level=all;
alter session set statistics_level=all;
alter session set statistics_level=all;
$ mv ez.txt ez18.txt
$ grep "SQL_ID" ez.txt | uniq -c
1000 SQL_ID ckynkwp4t00rz, child number 0
--//沒有子游標產生。
--//也就是與直方圖無關。
6.繼續測試：
--//取消直方圖設定。
SCOTT@test01p> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
$ cat by.txt
set term off
alter session set statistics_level=all;
variable x number;
exec :x := &&1;
SElect t.* from t where id1<=:x;
set term on
@ dpc '' ''
quit
$ cat by.sh
#!/bin/bash
# rm -f ez.txt
for i in $(seq 2000 )
do
sqlplus -s -l scott/btbtms@test01p @by.txt $i >> ez.txt
done
alter system flush shared_pool;
--//注意查新條件是id1<= :x,驗證在x=1000後是否出現子游標。
SCOTT@test01p> @ share basmuva6swhg4
SQL_TEXT = SElect t.* from t where id1<=:x
SQL_ID = basmuva6swhg4
ADDRESS = 000007FF1314E908
CHILD_ADDRESS = 000007FF13133298
CHILD_NUMBER = 0
LOAD_OPTIMIZER_STATS = Y
REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>39</ID><reason>Bind mismatch(25)</reason><size>0x0</size><details>extended_cursor_sharing</details></ChildNode>
--------------------------------------------------
SQL_TEXT = SElect t.* from t where id1<=:x
SQL_ID = basmuva6swhg4
ADDRESS = 000007FF1314E908
CHILD_ADDRESS = 000007FF13270B40
CHILD_NUMBER = 1
REASON = <ChildNode><ChildNumber>1</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>3229815407</init_ranges_in_first_pass></ChildNode>
--------------------------------------------------
SQL_TEXT = SElect t.* from t where id1<=:x
SQL_ID = basmuva6swhg4
ADDRESS = 000007FF1314E908
CHILD_ADDRESS = 000007FF1343C4A0
CHILD_NUMBER = 2
BIND_EQUIV_FAILURE = Y
REASON = <ChildNode><ChildNumber>2</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>2954937500</init_ranges_in_first_pass></ChildNode>
..
PL/SQL procedure successfully completed.
$ grep "SQL_ID" ez.txt | uniq -c
1000 SQL_ID basmuva6swhg4, child number 0
101 SQL_ID basmuva6swhg4, child number 1
111 SQL_ID basmuva6swhg4, child number 2
122 SQL_ID basmuva6swhg4, child number 3
134 SQL_ID basmuva6swhg4, child number 4
147 SQL_ID basmuva6swhg4, child number 5
162 SQL_ID basmuva6swhg4, child number 6
178 SQL_ID basmuva6swhg4, child number 7
45 SQL_ID basmuva6swhg4, child number 8
$ grep "SQL_ID" ez.txt | uniq -c | awk 'BEGIN {a=909;} {sum=sum+$1;a=a*1.10;print sum, a }'
1000 999.9
1101 1099.89
1212 1209.88
1334 1330.87
1468 1463.95
1615 1610.35
1777 1771.38
1955 1948.52
2000 2143.37
--//可以看出一個規律返回1000條記錄是第1道坎，以後大約按照0.11的比例增加(最後一行測試不足不算)。當然這僅僅是我的猜測。
7.繼續測試：
--//翻轉執行看看，先執行2000：
$ cat by.sh
#!/bin/bash
# rm -f ez.txt
for i in $(seq 2000 -1 1 )
do
sqlplus -s -l scott/btbtms@test01p @by.txt $i >> ez.txt
done
$ grep "SQL_ID" ez.txt | uniq -c
2000 SQL_ID basmuva6swhg4, child number 0
--//並沒有產生子游標。
--//我現在僅僅估計出現BIND_EQUIV_FAILURE的原因可能是返回記錄出現很大變化時就會出現。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2674776/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180613]子游標不共享BIND_EQUIV_FAILUREUIAI
- Oracle遊標共享，父遊標和子游標的概念Oracle
- Oracle - 共享遊標、父子游標、硬軟解析Oracle
- secureCRT游標不見啦Securecrt
- 【CURSOR】Oracle 子游標無法共享的原因之V$SQL_SHARED_CURSOROracleSQL
- [20220328]查詢游標為什麼不共享指令碼.txt指令碼
- [20170904]11Gr2 查詢游標為什麼不共享指令碼指令碼
- 深入理解父遊標，子游標的概念
- oracle實驗記錄 （子游標與解析）Oracle
- 父遊標 子游標和軟硬解析記載-02
- [20220420]完善查詢游標為什麼不共享指令碼.txt指令碼
- v$sql v$sqlarea和父遊標 子游標記載01SQL
- [20160407]游標共享TOP_LEVEL_RPI_CURSOR
- PLSQL Language Referenc-PL/SQL靜態SQL-游標變數-游標變數作為子程式引數SQL變數
- input 獲取游標位置與設定游標位置
- 子游標過多導致大量mutex爭用故障分析Mutex
- v$sql v$sqlarea v$sql_shared_cursor及父遊標，子游標SQL
- win10筆記本滑鼠游標不見了怎麼辦 win10滑鼠游標恢復的方法Win10筆記
- 游標操作快捷鍵
- winform之手繪矩形及游標字串與游標關聯顯示ORM字串
- (12)mysql 中的游標MySql
- 阻止游標預設事件事件
- win10游標怎麼縮放_win10游標縮放方法Win10
- 設定/獲得游標位置
- Mac滑鼠游標消失怎麼辦？蘋果電腦滑鼠指標不顯示的解決方法Mac蘋果指標
- cad游標大小怎麼調 cad游標中心正方形大小設定
- win10如何換滑鼠游標 win10更換滑鼠游標怎麼操作Win10
- PLSQL Language Referenc-PL/SQL靜態SQL-游標變數-建立游標變數SQL變數
- 1224關於共享池4SQL記憶體結構父子游標補充SQL記憶體
- win10 更改游標顏色方法 win10 滑鼠游標顏色怎麼改Win10
- PLSQL Language Referenc-PL/SQL靜態SQL-游標變數-游標變數賦值SQL變數賦值
- Web 中的“選區”和“游標”Web
- UITextField 游標位置,placeholder樣式UI
- (012)mysql中的游標MySql
- [20180822]session_cached_cursors與子游標堆0.txtSession
- 1229關於共享池4-SQL記憶體結構父子游標補充3SQL記憶體
- 20150213關於共享池4-SQL記憶體結構父子游標SQL記憶體
- PLSQL Language Referenc-PL/SQL靜態SQL-靜態SQL的描述-游標-顯式游標SQL