[20210205]警惕toad下優化直方圖相關sql語句.txt
[20210205]警惕toad下優化直方圖相關sql語句.txt
--//今天優化sql語句在toad12 ,我發現一個奇怪現象,語句的執行計劃不使用我建立的索引.折騰N久,才想起以前遇到
--//的情況,連結如下:http://blog.itpub.net/267265/viewspace-2668520/=>[20191213]toad 12下BIND_AWARE提示無效.txt
--//問題我優化的資料庫11.2.0.3與我的測試環境11.2.0.4有一點點不同.
--//先演示測試環境遇到的情況:
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 /*+ gather_plan_statistics */ * from t where flag=:x;
--//然後選擇執行,代入引數'0'.獲取sql_id=apjr1ppx7hgjm.
SQL_ID ctu9k9j5v97wn, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where flag=:x
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 435 (100)| | 1 |00:00:00.01 | 1567 |
|* 1 | TABLE ACCESS FULL| T | 1 | 50000 | 5273K| 435 (1)| 00:00:06 | 1 |00:00:00.01 | 1567 |
--------------------------------------------------------------------------------------------------------------------
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]
-//注意看下劃線,不知道toad下加入OPT_PARAM('_optim_peek_user_binds' 'false'),導致繫結變數peek無效.
--//E-Rows=50000,也就是一半記錄,導致執行計劃走全表掃描.
--//即使加入提示改寫如下:
select /*+ OPT_PARAM('_optim_peek_user_binds' 'true') gather_plan_statistics */ * from t where flag=:x;
--//一樣無效.
3.使用dbms_sqldiag包看執行計劃分析:
$ cat 10053x.sql
execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1');
SCOTT@book> @ 10053x ctu9k9j5v97wn 0
PL/SQL procedure successfully completed.
SCOTT@book> @ pp
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_57333_actu9k9j5v97wn.trc
--//我才發現這樣看到的執行計劃可能不是真實的執行計劃.
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Column (#3):
NewDensity:0.000005, OldDensity:0.000005 BktCnt:100000, PopBktCnt:99999, PopValCnt:1, NDV:2
Column (#3): FLAG(
AvgLen: 2 NDV: 2 Nulls: 0 Density: 0.000005
Histogram: Freq #Bkts: 2 UncompBkts: 100000 EndPtVals: 2
Table: T Alias: T
Card: Original: 100000.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
Cost: 434.96 Resp: 434.96 Degree: 0
Cost_io: 434.00 Cost_cpu: 35372940
Resp_io: 434.00 Resp_cpu: 35372940
Access Path: index (AllEqRange)
Index: I_T_FLAG
resc_io: 2.00 resc_cpu: 15483
ix_sel: 0.000010 ix_sel_with_filters: 0.000010
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange
Index: I_T_FLAG
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 0
...
----- Current SQL Statement for this session (sql_id=2scp7tw6bfvzu) -----
/* SQL Analyze(15,0) */ select /*+ gather_plan_statistics */ * from t where flag=:x
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x7bcb1030 145 package body SYS.DBMS_SQLTUNE_INTERNAL
0x7bcb1030 12098 package body SYS.DBMS_SQLTUNE_INTERNAL
0x7d7830c0 1229 package body SYS.DBMS_SQLDIAG
0x7c1b2e80 1 anonymous block
sql_text_length=84
sql=/* SQL Analyze(15,0) */ select /*+ gather_plan_statistics */ * from t where flag=:x
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-----------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 108 | 2 | 00:00:01 |
| 2 | INDEX RANGE SCAN | I_T_FLAG| 1 | | 1 | 00:00:01 |
-----------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("FLAG"=:X)
Content of other_xml column
===========================
db_version : 11.2.0.4
parse_schema : SCOTT
plan_hash : 120143814
plan_hash_2 : 2969257144
Peeked Binds
============
Bind variable information
position=1
datatype(code)=1
datatype(string)=VARCHAR2(32)
char set id=852
char format=1
max length=32
value=0
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
*/
--//執行計劃對不上.outlined裡面沒有OPT_PARAM('_optim_peek_user_binds' 'false')
--//另外我嘗試在toad下執行@ 10053x ctu9k9j5v97wn 0,看到的情況也是一樣的.
--//也就是使用dbms_sqldiag包看執行計劃分析也要注意.
4.繼續分析:
SCOTT@book> alter session set "_optim_peek_user_binds"=false;
Session altered.
SCOTT@book> @ 10053x ctu9k9j5v97wn 0
PL/SQL procedure successfully completed.
--//檢視轉儲,
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Column (#3):
NewDensity:0.000005, OldDensity:0.000005 BktCnt:100000, PopBktCnt:99999, PopValCnt:1, NDV:2
Column (#3): FLAG(
AvgLen: 2 NDV: 2 Nulls: 0 Density: 0.000005
Histogram: Freq #Bkts: 2 UncompBkts: 100000 EndPtVals: 2 --//直方圖存在
Table: T Alias: T
Card: Original: 100000.000000 Rounded: 50000 Computed: 50000.00 Non Adjusted: 50000.00
Access Path: TableScan
Cost: 434.96 Resp: 434.96 Degree: 0
Cost_io: 434.00 Cost_cpu: 35372940
Resp_io: 434.00 Resp_cpu: 35372940
Access Path: index (AllEqRange)
Index: I_T_FLAG
resc_io: 873.00 resc_cpu: 25717867
ix_sel: 0.500000 ix_sel_with_filters: 0.500000 --//ix_sel: 0.500000,走索引肯定很差
Cost: 873.70 Resp: 873.70 Degree: 1
Best:: AccessPath: TableScan
Cost: 434.96 Degree: 1 Resp: 434.96 Card: 50000.00 Bytes: 0
check parallelism for statement[<unnamed>]
kkfdtParallel: parallel is possible (no statement type restrictions)
kkfdPaForcePrm: dop:1 ()
kkfdPaPrm: use dictionary DOP(1) on table
kkfdPaPrm:- The table : 96021
kkfdPaPrm:DOP = 1 (computed from hint/dictionary/autodop)
kkfdiPaPrm: dop:1 serial(?)
***************************************
...
sql_text_length=84
sql=/* SQL Analyze(44,0) */ select /*+ gather_plan_statistics */ * from t where flag=:x
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 435 | |
| 1 | TABLE ACCESS FULL | T | 49K | 5273K | 435 | 00:00:06 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("FLAG"=:X)
Content of other_xml column
===========================
db_version : 11.2.0.4
parse_schema : SCOTT
plan_hash : 1601196873
plan_hash_2 : 2498539100
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
*/
--//設定環境一致後,看到的執行計劃一致,實際上dbms_sqldiag包是分析語句
sql=/* SQL Analyze(15,0) */ select /*+ gather_plan_statistics */ * from t where flag=:x
--//不知道為什麼toad在執行sql語句時遇到直方圖情況是自動加上了OPT_PARAM('_optim_peek_user_binds' 'false').
5.總結:
--//toad 12下要注意這個細節,不然可能遇到無法解析的情況.
--//結果導致加入BIND_AWARE提示無效.
--//另外在11.2.0.3下我看到的情況更加奇怪另外寫一篇blog分析.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2756382/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210205]警惕toad下優化直方圖相關sql語句3.txt優化直方圖SQL
- [20211229]toad下優化sql語句注意的問題.txt優化SQL
- 【效能優化】直方圖優化直方圖
- 關於sql語句的優化SQL優化
- [20140301]直方圖與優化.txt直方圖優化
- SQL語句優化SQL優化
- toad執行sql語句SQL
- SQL Server優化之SQL語句優化SQLServer優化
- 【效能優化】Oracle直方圖解析優化Oracle直方圖圖解
- sql優化相關SQL優化
- [20131204]sql語句優化.txtSQL優化
- [20151221]sql語句優化.txtSQL優化
- MYSQL SQL語句優化MySql優化
- sql語句效能優化SQL優化
- SQL語句的優化SQL優化
- 求助：SQL語句優化SQL優化
- 優化由直方圖資訊導致的sql效能問題優化直方圖SQL
- [20201210]sql語句優化.txtSQL優化
- MySQL之SQL語句優化MySql優化
- SQL語句優化（轉載）SQL優化
- 常用SQL語句優化技巧SQL優化
- Oracle之sql語句優化OracleSQL優化
- SQL 語句的優化方法SQL優化
- [20190328]簡單探究sql語句相關mutexes.txtSQLMutex
- [20160815]查詢相關表的sql語句.txtSQL
- [20170615]直方圖-高度直方圖(11g).txt直方圖
- 直方圖均衡化直方圖
- [20190329]探究sql語句相關mutexes補充2.txtSQLMutex
- 優化 SQL 語句的步驟優化SQL
- 一個SQL語句的優化SQL優化
- Oracle SQL語句優化之UNIONOracleSQL優化
- SQL語句操作符優化SQL優化
- SQL語句優化技術分析SQL優化
- SQL語句優化方法30例SQL優化
- 一條sql語句的優化SQL優化
- sql語句的優化案例分析SQL優化
- [20181114]一條sql語句的優化.txtSQL優化
- [20131025]一條sql語句的優化.txtSQL優化