[20191213]toad 12下BIND_AWARE提示無效.txt
[20191213]toad 12下BIND_AWARE提示無效.txt
--//連結http://blog.itpub.net/267265/viewspace-2130781/的測試,發現當時測試的錯誤.有空再次驗證看看.
1.環境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table t as select rownum id ,lpad('x',100,'x') name ,lpad('1',1) flag from dual connect by level<=1e5;
update t set flag='0' where id=1e5;
commit ;
create index i_t_flag on t(flag);
SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => '',TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns flag size 4',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
2.測試:
--//先寫sql語句在toad sql編輯視窗:
select /*+ bind_aware gggg*/ * from t where flag=:x;
--//然後選擇執行,代入引數'0'.查詢字串gggg,獲取sql_id=ddgfa29wynq6d.
SCOTT@book> @ dpc ddgfa29wynq6d outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID ddgfa29wynq6d, child number 0
-------------------------------------
select /*+ bind_aware gggg*/ * from t where flag=:x
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 435 (100)| |
|* 1 | TABLE ACCESS FULL| T | 50000 | 5273K| 435 (1)| 00:00:06 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"=:X)
--//注意看下劃線,不知道為什麼toad要加入修改引數'_optim_peek_user_binds' 'false',不做繫結變數peek,
--//還可以一個細節就是沒有獲取繫結變數的值.導致提示bind_aware失效.
3.繼續測試:
--//取消load cached plan if possible.看到的測試結果也是一樣.
--//總之不知道為什麼toad 12的版本為什麼執行時將會話的_optim_peek_user_binds=false.而導致的提示失效.
--//這個應該在最佳化與除錯sql語句是應該引起注意.
--//另外一個簡單的驗證就是在sqlplus執行:
SCOTT@book> variable x varchar2(1)
SCOTT@book> exec :x := '0';
PL/SQL procedure successfully completed.
SCOTT@book> @ dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8srddvbs5ydfv, child number 0
-------------------------------------
select /*+ bind_aware OPT_PARAM('_optim_peek_user_binds' 'false') */ *
from t where flag=:x
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 435 (100)| |
|* 1 | TABLE ACCESS FULL| T | 50000 | 5273K| 435 (1)| 00:00:06 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"=:X)
--//bind_aware提示無效.
--//如果語句在sqlplus下先執行再在toad下觀察呢?
SCOTT@book> select /*+ bind_aware pppp */ * from t where flag=:x;
ID NAME F
---------- ---------------------------------------- -
100000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8b15sjx54pvfw, child number 0
-------------------------------------
select /*+ bind_aware pppp */ * from t where flag=:x
Plan hash value: 120143814
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 108 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_FLAG | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
--//然後在toad下執行,代入引數'0';
SQL_ID 8b15sjx54pvfw, child number 0
-------------------------------------
select /*+ bind_aware pppp */ * from t where flag=:x
Plan hash value: 120143814
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 108 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_FLAG | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."FLAG"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - (VARCHAR2(30), CSID=852): '0'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."ID"[NUMBER,22], "T"."NAME"[VARCHAR2,100], "FLAG"[VARCHAR2,1]
2 - "T".ROWID[ROWID,10], "FLAG"[VARCHAR2,1]
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
SQL_ID 8b15sjx54pvfw, child number 1
-------------------------------------
select /*+ bind_aware pppp */ * from t where flag=:x
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 435 (100)| |
|* 1 | TABLE ACCESS FULL| T | 50000 | 5273K| 435 (1)| 00:00:06 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."ID"[NUMBER,22], "T"."NAME"[VARCHAR2,100], "FLAG"[VARCHAR2,1]
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//你可以看到生成新的子游標.感覺toad這樣設計不好,不利於生產系統調優測試.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2668520/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20230329]利用bind_aware提示最佳化案例2.txt
- [20220517]toad使用gather_plan_statistics提示問題.txt
- [20191213]不完全恢復疑問.txt
- [20181206]toad 12小問題.txt
- [20191213]共享池繫結變數的值在哪裡.txt變數
- [20230130]toad看執行計劃注意.txt
- [20221010]使用toad管理索引改名問題.txt索引
- [20220324]toad與sql profile使用問題.txtSQL
- [20220414]toad與繫結變數peek.txt變數
- [20220414]toad呼叫執行指令碼問題.txt指令碼
- [20211206]toad下job建立檢視問題.txt
- [20220105]再論ORA-29275與toad 12.txt
- [20181120]toad看真實的執行計劃.txt
- [20181128]toad連線資料庫的問題.txt資料庫
- [20181006]12c使用toad連線問題.txt
- 教你如何解決SQL server中提示物件名無效SQLServer物件
- mac 配置IP地址，提示“無效的伺服器地址”Mac伺服器
- [20211229]toad下優化sql語句注意的問題.txt優化SQL
- [20210114]toad檢視真實執行計劃問題.txt
- [20181204]低版本toad 9.6直連與ora-12505.txt
- [20181128]toad連線資料庫的問題(補充).txt資料庫
- [20211014]toad安裝的一些定製化過程.txt
- [20190118]toad下如何除錯儲存過程和函式.txt除錯儲存過程函式
- 無效字元字元
- [20240313]toad gather_plan_statistics執行計劃相關問題.txt
- [20210205]toad檢視真實執行計劃問題3.txt
- [20210205]警惕toad下優化直方圖相關sql語句.txt優化直方圖SQL
- [20211213]提示precompute_subquery.txt
- [20210120]提示加入註解.txt
- [20180927]修改sql prompt提示.txtSQL
- nginx gzip on 無效Nginx
- [20181107]低版本toad連線18c資料庫問題.txt資料庫
- 解決virtualbox虛擬機器設定nat網路提示無效設定虛擬機
- win10系統開機無法登入提示控制程式碼無效怎麼解決Win10
- [20180503]檢視提示使用索引.txt索引
- [20210205]警惕toad下優化直方圖相關sql語句3.txt優化直方圖SQL
- win10複製檔案提示ms-dos功能無效怎麼解決Win10
- 有無後效性