關於cursor_sharing = similar (zt)
摘要:本文透過簡單實驗來嘗試說明cursor_sharing=similar的含義。
我們先看看在表沒有分析無統計資料情況下的表現
SQL> alter session set cursor_sharing = similar;
Session altered.
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4948
parse time elapsed 4468
parse count (total) 170148
parse count (hard) 1619 (硬分析次數)
parse count (failures) 80
SQL> select count(*) from t where object_id = 1000;
COUNT(*)
----------
0
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4948
parse time elapsed 4468
parse count (total) 170172
parse count (hard) 1620
parse count (failures) 80
SQL> /
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4948
parse time elapsed 4468
parse count (total) 170176
parse count (hard) 1620
parse count (failures) 80
SQL> select count(*) from t where object_id = 1000;
COUNT(*)
----------
0
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4948
parse time elapsed 4468
parse count (total) 170178
parse count (hard) 1620
parse count (failures) 80
SQL> select count(*) from t where object_id = 1001;
COUNT(*)
----------
0
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4948
parse time elapsed 4468
parse count (total) 170180
parse count (hard) 1620(即使object_id發生變化依然沒有硬解析)
parse count (failures) 80
我們再來看分析表和欄位資訊後的表現
SQL> analyze table t1 compute statistics for table for columns object_id;
Table analyzed.
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4973
parse time elapsed 4495
parse count (total) 170982
parse count (hard) 1640
parse count (failures) 80
SQL> select count(*) from t1 where object_id = 5000;
COUNT(*)
----------
0
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4973
parse time elapsed 4495
parse count (total) 170984
parse count (hard) 1641
parse count (failures) 80
SQL> select count(*) from t1 where object_id = 5000;
COUNT(*)
----------
0
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4973
parse time elapsed 4495
parse count (total) 171008
parse count (hard) 1641 (重複執行沒發生變化)
parse count (failures) 80
SQL> select count(*) from t1 where object_id = 5001;
COUNT(*)
----------
0
SQL> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 4973
parse time elapsed 4495
parse count (total) 171010
parse count (hard) 1642 (當object_id變化的時候產生硬分析)
parse count (failures) 80
SQL>
SQL> select sql_text,child_number from v$sql where sql_text like 'select count(*) from t1 where%';
SQL_TEXT
------------------------------------------------------------------------------
CHILD_NUMBER
------------
select count(*) from t1 where object_id = :"SYS_B_0"
0
select count(*) from t1 where object_id = :"SYS_B_0"
1
可以看出若存在object_id的 histograms ,則每次是不同的值的時候都產生硬解析 ,若不存在 histograms,則不產生硬解析。換句話說,當表的欄位被分析過存在histograms的時候,similar 的表現和exact一樣,當表的欄位沒被分析,不存在histograms的時候,similar的表現和force一樣。這樣避免了一味地如force一樣轉換成變數形式,因為有histograms的情況下轉換成變數之後就容易產生錯誤的執行計劃,沒有利用上統計資訊。而exact呢,在沒有histograms的情況下也要分別產生硬解析,這樣的話,由於執行計劃不會受到資料分佈的影響(因為沒有統計資訊)重新解析是沒有實質意義的。而similar則綜合了兩者的優點。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-84480/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 關於cursor_sharing = similar(ZT)MILA
- 關於 cursor_sharing = similarMILA
- 關於cursor_sharing=similarMILA
- CURSOR_SHARING=SIMILARMILA
- cursor_sharing=similar深度剖析MILA
- cursor_sharing : exact , force , similarMILA
- 有關引數cursor_sharing=similar的測試MILA
- ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’MILA
- cursor_sharing = similar , exact 區別MILA
- cursor_sharing=similar 與 直方圖MILA直方圖
- cursor_sharing設定為similar 的弊端MILA
- Cursor_sharing=SIMILAR取值與直方圖(上)MILA直方圖
- Cursor_sharing=SIMILAR取值與直方圖(下)MILA直方圖
- oracle實驗記錄 (cursor_sharing(2)SIMILAR)OracleMILA
- [20140802]cursor_sharing=similar.txtMILA
- 關於scn的理解 (zt)
- zt_繫結變數和cursor_sharing變數
- Oracle 11g 中 cursor_sharing 設定為SIMILAR 導致的問題OracleMILA
- 關於ThinkPad密碼(ZT)ThinkPad密碼
- 關於SQL Server 截斷日誌[zt]SQLServer
- 10203設定CURSOR_SHARING為SIMILAR導致物化檢視重新整理失敗MILA
- [zt ]關於RAID的掃盲知識AI
- 不錯的關於Oracle 全文索引的文章(zt)Oracle索引
- Cursor_sharing,Histogram,Analyze之間的關係Histogram
- 關於SQL Server中索引使用及維護簡介(zt)SQLServer索引
- 關於 RAC VIP (Oracle10G RAC) 的探討(zt)Oracle
- Cursor_sharing,Histogram,Analyze之間的關係(轉)Histogram
- 等待事件相關(zt)事件
- zt_關於wait events asynch descriptor resize_wait eventAI
- 我的關於軟體工程的一些觀點ZT (轉)軟體工程
- 關於車--標緻206相關問題解析及選車建議(zt)
- 關於控制檔案與資料檔案頭資訊的說明(zt)
- zt_checkpoint相關知識
- 男女關係33個比喻(zt)
- oracle cursor_sharing [轉]Oracle
- 關於B*tree索引(index)的中度理解及bitmap 索引的一點探究(zt)索引Index
- zt_Oracle9i,10g,11g 使用繫結變數的區別及與cursor_sharing的關係_自適應遊標共享Oracle變數
- 表碎片的相關知識(ZT)