[20240313]toad gather_plan_statistics執行計劃相關問題.txt

[20240313]toad gather_plan_statistics執行計劃相關問題.txt

--//自己現在已經很少使用toad,使用也是作為輔助功能,畢竟圖形介面能更快的操作顯示資訊.
--//昨天遇到一個問題,自己當時沒有反映過來,浪費點時間,做一個記錄避免以後再次犯渾.
--//我一般在toad的sql編輯介面下儘可能看真實的執行計劃
--//參考:https://blog.itpub.net/267265/viewspace-2130781/=>[20161216]toad下顯示真實的執行計劃.txt
--//沒有想到看到的執行計劃統計存在一些問題，看下面的例子：

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

2.建立例子:

create table t as select rownum id ,lpad('x',100,'x') name ,lpad('1',1) flag from dual connect by level<=1e4;
update t set flag='0' where id=1e4;
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.

--//在flag欄位上建立直方圖.

3.測試:
--//在toad介面上輸入如下語句並執行如下：
SELECT /*+ gather_plan_statistics */ * FROM T WHERE FLAG=:X;
--//注:加入gather_plan_statistics提示執行計劃可以顯示更多資訊.

--//帶入'1'測試看看,執行計劃如下：
--//注意是字串型別。
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 48 (100)| |
|* 1 | TABLE ACCESS FULL| T | 5000 | 522K| 48 (0)| 00:00:01 |
---------------------------------------------------------------------------
--//你可以發現並沒有顯示完整的執行計劃統計資訊，提示gather_plan_statistics沒有起作用。
--//實際上顯示是explain plan的執行計劃.

--//帶入'0'測試看看，執行計劃如下:
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 | | | 48 (100)| | 1 |00:00:00.01 | 160 |
|* 1 | TABLE ACCESS FULL| T | 1 | 5000 | 522K| 48 (0)| 00:00:01 | 1 |00:00:00.01 | 160 |
--------------------------------------------------------------------------------------------------------------------
-//看到的執行計劃不同，為什麼呢? 實際上問題在於toad執行時每次fetch 1001行。
--//:X = 0 是僅僅返回1條，執行計劃已經完成，可以顯示完整的統計資訊。
--//而帶入:X = 1時，僅僅顯示前500條(實際上fetch 1001行)，沒有執行完成，無法顯示完整的執行統計資訊。可以拖動捲軸或者按
--//page down鍵到結尾，這樣也可以得到完整的執行計劃。

--//如果滾動到中間(實際上只要不到結尾), 看執行計劃：
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 | | | 48 (100)| | 1001 |00:00:00.01 | 18 |
|* 1 | TABLE ACCESS FULL| T | 1 | 5000 | 522K| 48 (0)| 00:00:01 | 1001 |00:00:00.01 | 18 |
--------------------------------------------------------------------------------------------------------------------
--//看到A-ROWS=1001.也就是第1次fetch的數量,也就是以後在toad下看這類執行計劃注意,這樣看到的執行計劃的統計資訊可能不真實!!
--//如果按page down鍵到結尾再看執行計劃：
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 | | | 48 (100)| | 9999 |00:00:00.01 | 168 |
|* 1 | TABLE ACCESS FULL| T | 1 | 5000 | 522K| 48 (0)| 00:00:01 | 9999 |00:00:00.01 | 168 |
--------------------------------------------------------------------------------------------------------------------

--//如果在sql編輯介面上開啟auto trace，全部結果fetch完成就不會出現上面看到的情況了。
--//再次帶入'1'測試看看，執行計劃如下:
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 | | | 48 (100)| | 9999 |00:00:00.01 | 168 |
|* 1 | TABLE ACCESS FULL| T | 1 | 5000 | 522K| 48 (0)| 00:00:01 | 9999 |00:00:00.01 | 168 |
--------------------------------------------------------------------------------------------------------------------

4.繼續測試：
--//關閉auto trace。
--//再次帶入'1'測試看看，執行計劃如下:
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 | | | 48 (100)| | 9999 |00:00:00.01 | 168 |
|* 1 | TABLE ACCESS FULL| T | 1 | 5000 | 522K| 48 (0)| 00:00:01 | 9999 |00:00:00.01 | 168 |
--------------------------------------------------------------------------------------------------------------------

--//再次帶入'1'測試看看，執行計劃如下:
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 | | | 48 (100)| | 1001 |00:00:00.01 | 18 |
|* 1 | TABLE ACCESS FULL| T | 1 | 5000 | 522K| 48 (0)| 00:00:01 | 1001 |00:00:00.01 | 18 |
--------------------------------------------------------------------------------------------------------------------
--//注:當時馬上看執行計劃,理論講應該沒有A-Rows之類的統計,本來以為會顯示explain plan的執行計劃,但是看到的卻是
--//A-Rows=1001的情況,我猜測可能是使用dbms_xplan.display_cursor,format加入last的緣故.

--//開始我很困惑,實際上這樣第1次執行看到的是前1次的執行計劃統計資訊。

--//使用SQL Tracker跟蹤發現,toad執行如下：
select * from table(dbms_xplan.display_cursor(sql_id => '7pnr0krspk166', cursor_child_no => null, format => 'ALL,
ALLSTATS, LAST, ROWS, BYTES, COST, OUTLINE, PARTITION, PARALLEL, PEEKED_BINDS, PREDICATE, PROJECTION, ALIAS, REMOTE,
NOTE'))

--//加入有引數last，顯示引數我基本全部選上。
--//而第2次執行，因為前面有記錄這個統計資訊，這樣就有顯示，雖然不準確。當然這些是我的猜測!!

--//總之，在toad下注意這個細節，建議最佳化除錯sql語句時開啟auto trace，這樣看執行計劃的統計資訊比較準確,不容易出現誤判。

5.另外的問題注意：
--//你可以注意一個細節，toad下無論帶入'0'還是'1'選擇的執行計劃都是全表掃描，E-Rows=5000並沒有採用直方圖的結果,也就是總記
--//錄的50%.
--//即使小量修改sql語句，sql_id不同,導致重新分析sql語句,第1次帶入引數'0'，執行計劃也是選擇全表掃描。
--//如果仔細看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
*/

--//不知道為什麼toad關閉了繫結變數peek。而且我不知道怎麼開啟，使用toad的版本號12.6.0.53,只有toad 9.X版本正常的.
--//這樣使得在toad除錯sql語句涉及到這方面相關問題時要小心，而且導致BIND_AWARE提示無效。

SELECT /*+ gather_plan_statistics BIND_AWARE OPT_PARAM('_optim_peek_user_binds' 'true') */ * 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 | | | 48 (100)| | 1 |00:00:00.01 | 160 |
|* 1 | TABLE ACCESS FULL| T | 1 | 5000 | 522K| 48 (0)| 00:00:01 | 1 |00:00:00.01 | 160 |
--------------------------------------------------------------------------------------------------------------------
--//還是全表掃描。

6.sqlplus下測試：

SCOTT@book> variable x varchar2(32) ;
SCOTT@book> exec :x :='0';
PL/SQL procedure successfully completed.

SCOTT@book> SELECT /*+ gather_plan_statistics */ * FROM T WHERE FLAG=:X;
ID NAME F
---------- ---------------------------------------- -
10000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx


SCOTT@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8s7vwp7ykv52x, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM T WHERE FLAG=:X
Plan hash value: 120143814
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 107 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | I_T_FLAG | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (VARCHAR2(30), CSID=852): '0'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG"=:X)
--//建立新的子游標,child number=1,可以使用I_T_FLAG索引。

7.總結：
--//注意toad下除錯最佳化sql語句時注意這個細節，避免以後在這個方面浪費時間。