一個sql最多可擁有多少子游標
http://www.oaktable.net/content/does-parse-time-increase-linearly-number-child-cursors
由大名鼎鼎的Antognini測試所得,即sql編譯時間會隨著子游標數量的增多而線性增長,這個很好理解,子游標採用連結串列結構每次解析時必須挨個遍歷才能得出最終值
到了11203,情況發生了些許變化,引入隱含引數_cursor_obsolete_threshold(預設值100),即每個父遊標最多隻能有100個子遊標,如果超出則建立一個新的父遊標
http://www.antognini.ch/2012/10/how-many-children-can-a-parent-cursor-have-1000000/
作者採用以下指令碼驗證Pre-11203的版本中一個父遊標最多可以擁有多少子游標
SQL> CREATE TABLE t (n NUMBER);
Table created.
SQL> INSERT INTO t VALUES (1);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> execute dbms_stats.gather_table_stats(user,'t')
PL/SQL procedure successfully completed.
SQL> DECLARE
2 l_count PLS_INTEGER;
3 BEGIN
4 FOR oic IN 1..100
5 LOOP
6 EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_caching = '||oic;
7 FOR oica IN 1..10000
8 LOOP
9 EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = '||oica;
10 EXECUTE IMMEDIATE 'SELECT count(*) FROM t' into l_count;
11 END LOOP;
12 END LOOP;
13 END;
14 /
DECLARE
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 6809
Session ID: 7 Serial number: 9
Alert log出現如下資訊
Errors in file /u00/app/oracle/diag/rdbms/dbm11202/DBM11202/trace/DBM11202_ora_6809.trc (incident=25425):
ORA-00600: internal error code, arguments: [15206], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u00/app/oracle/diag/rdbms/dbm11202/DBM11202/incident/incdir_25425/DBM11202_ora_6809_i25425.trc
檢視跟蹤檔案
LibraryHandle: Address=0x2c5e4d030 Hash=3a56fe71 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=SELECT count(*) FROM t
FullHashValue=6d0bd54734bada875cc6ce3e3a56fe71 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=978779761 wnerIdn=36
Statistics: InvalidationCount=0 ExecutionCount=65535 LoadCount=65536 ActiveLocks=51 TotalLockCount=65536 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=65535 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
Concurrency: DependencyMutex=0x2c5e4d0e0(0, 4097, 0, 0) Mutex=0x2c5e4d160(0, 5120057, 12919, 0)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x2c5e4d0c0[0x2c5e4d0c0,0x2c5e4d0c0]
Pin=0x2c5e4d0a0[0x2c5e4d0a0,0x2c5e4d0a0]
Timestamp: Current=10-19-2012 07:02:48
HandleReference: Address=0x2c5e4d1e0 Handle=(nil) Flags=[00]
LibraryObject: Address=0x2c5e4bfc0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
ChildTable: size='65536'
Child: id='0' Table=0x2c5e4ce70 Reference=0x2c5e4c898 Handle=0x2c5e4bbd0
Child: id='1' Table=0x2c5e4ce70 Reference=0x2c5e4cd58 Handle=0x2c5dd4338
Child: id='2' Table=0x2c5e4ce70 Reference=0x2c5dce0d0 Handle=0x2c5dcdae8
Child: id='3' Table=0x2c5e4ce70 Reference=0x2c5dce568 Handle=0x2c5dc8298
Child: id='4' Table=0x2c5e4ce70 Reference=0x2c5dcea00 Handle=0x2c5dc2a48
…
Child: id='65530' Table=0x2846e56d8 Reference=0x2846b07e8 Handle=0x2846b03d8
Child: id='65531' Table=0x2846e56d8 Reference=0x2846b0c80 Handle=0x2846ad188
Child: id='65532' Table=0x2846e56d8 Reference=0x2846b1118 Handle=0x2846a9f38
Child: id='65533' Table=0x2846e56d8 Reference=0x2846a60f8 Handle=0x2846a5ce8
Child: id='65534' Table=0x2846e56d8 Reference=0x2846a6590 Handle=0x2846a2a98
可看出11202中單個父遊標對應的子游標最多可有65534個
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15480802/viewspace-747306/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20130104]oracle能有多少子游標.txtOracle
- PLSQL Language Referenc-PL/SQL靜態SQL-靜態SQL的描述-游標-顯式游標SQL
- SPOJ 694 求一個字串有多少子串 字尾陣列字串陣列
- PLSQL Language Referenc-PL/SQL靜態SQL-游標變數-建立游標變數SQL變數
- 比特幣擁有最多者之一去世,享年 41 歲比特幣
- PLSQL Language Referenc-PL/SQL靜態SQL-游標變數-游標變數賦值SQL變數賦值
- PL/SQL第三章--游標SQL
- PLSQL Language Referenc-PL/SQL靜態SQL-游標變數-游標變數作為宿主變數SQL變數
- PLSQL Language Referenc-PL/SQL靜態SQL-游標變數-開啟和關閉游標變數SQL變數
- PLSQL Language Referenc-PL/SQL靜態SQL-靜態SQL的描述-游標-開啟和關閉顯式游標SQL
- SQL Server 伺服器主體擁有一個或多個端點無法刪除;錯誤15141SQLServer伺服器
- PLSQL Language Referenc-PL/SQL靜態SQL-游標變數-游標變數作為子程式引數SQL變數
- 公司快速地擁有一個網站網站
- 游標美化
- v$sql v$sqlarea v$sql_shared_cursor及父遊標,子游標SQL
- v$sql v$sqlarea和父遊標 子游標記載01SQL
- PLSQL Language Referenc-PL/SQL靜態SQL-游標變數SQL變數
- SHOW PROCESSLIST 最多能顯示多長的 SQL?SQL
- 黑袋子——一個擁有黑科技的APPAPP
- input 獲取游標位置與設定游標位置
- 擁有五個特點是CRM系統評判標準
- 預設讓表單第一個input文字框獲取游標
- 求一個sql。多謝各位SQL
- 游標操作快捷鍵
- 子游標過多導致大量mutex爭用故障分析Mutex
- win10觸控板沒有游標怎麼辦 win10觸控板沒有游標的方法Win10
- 一個可拖拽的React標籤元件React元件
- winform之手繪矩形及游標字串與游標關聯顯示ORM字串
- 四角游標跟隨滑鼠, 滑鼠落在哪個圖片上, 游標就跟隨到哪張圖片 (一種實現思路)
- 從零開始,開發一個 Web Office 套件(6):游標 & Click 事件Web套件事件
- SQL Server中Truncate許可權必須擁有db_owner 或db_ddladminSQLServer
- Nielsen:YouTube在同型別網站中擁有最多的年輕使用者型別網站
- 繼承應該是擁有且可訪問繼承
- 擁有GRANT ANY OBJECT PRIVILEGE許可權時的許可權回收Object
- (12)mysql 中的游標MySql
- 阻止游標預設事件事件
- secureCRT游標不見啦Securecrt
- css 滑鼠游標設定CSS