[20150610]sql的謂詞中使用函式.txt

[20150610]sql的謂詞中使用函式.txt

--程式開發經常可以看到如下的程式碼：
-- trunc(dd_date)=trunc(sysdate),結果導致必須建立函式索引。實際上類似的錯誤還有很多。
-- to_char(dd_date,'yyyymmdd') = '20150610' 等等。

-- 以前我給新來的講課都講這些例子，可惜可惜不知道對方不長記憶還是需要多次提到，總之在實際的開發中一直出現相似的問題。

-- 還是透過一個例子來說明：

1.建立測試環境：

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t as
with a as (select rownum from dual connect by level<=3e3)
select rownum id ,sysdate-rownum/1440 dd_date from a,a ;

exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);

SCOTT@test> select owner, num_rows, blocks, last_analyzed from dba_tables where owner=user and table_name ='T';
OWNER    NUM_ROWS     BLOCKS LAST_ANALYZED
------ ---------- ---------- -------------------
SCOTT     9000000      23693 2015-06-10 09:43:12

-- 23693*8/1024=185.1015625,不到190M。

2.開始測試：
SCOTT@test> show sga
Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             973082064 bytes
Database Buffers          620756992 bytes
Redo Buffers                7344128 bytes

SCOTT@test> select count(*) from t where trunc(dd_date)=trunc(sysdate+10);
  COUNT(*)
----------
         0
Elapsed: 00:00:05.68

--注意要測試多次，這樣可以快取資料到記憶體中，最後幾次執行以上語句測試需要5，6秒完成。

--而執行如下呢？
SCOTT@test> select count(*) from t where dd_date between trunc(sysdate+10) and trunc(sysdate+10)-1/86400;
  COUNT(*)
----------
         0
Elapsed: 00:00:00.00

--執行計劃肯定都是全表掃描，因為沒有任何索引，但是查詢條件是trunc(dd_date)=trunc(sysdate+10)的語句，相當於trunc(dd_date)
--呼叫函式9e6次，可以看出5.XX秒基本都耗在這個上面。而第2次查詢沒有這個呼叫trunc函式，差距是如此的懸殊。
--另外注意後面的常量如(trunc(sysdate+10))僅僅需要計算1次。

--當然實際應用呼叫函式次數可能沒有多，至少說明在謂詞中使用函式要注意，希望這些引起開發注意。

3.使用explain plan看看cpu cost。

explain plan set statement_id='x1' for  select count(*) from t where trunc(dd_date)=trunc(sysdate+10);
explain plan set statement_id='x2' for  select count(*) from t where dd_date between trunc(sysdate+10) and trunc(sysdate+10)-1/86400;

SCOTT@test> set numw 12
SCOTT@test> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where options='FULL';
STATEMENT_ OPERATION                      OPTIONS            COST     CPU_COST      IO_COST         TIME
---------- ------------------------------ ---------- ------------ ------------ ------------ ------------
x1         TABLE ACCESS                   FULL               7192  15198728278         6419            1
x2         TABLE ACCESS                   FULL               6849   8448729226         6419            1

--很明顯第一個cpu cost消耗高。
--另外補充cpu_cost轉化cost的計算：
--計算公式如下：
CPU Cost = ROUND(#CPUCycles / cpuspeednw / 1000 / sreadtim)

SCOTT@test> Select * from sys.aux_stats$;
SNAME                          PNAME                       PVAL1 PVAL2
------------------------------ -------------------- ------------ --------------------
SYSSTATS_INFO                  STATUS                            COMPLETED
SYSSTATS_INFO                  DSTART                            05-27-2015 09:43
SYSSTATS_INFO                  DSTOP                             05-27-2015 09:43
SYSSTATS_INFO                  FLAGS                           0
SYSSTATS_MAIN                  CPUSPEEDNW                   1639
SYSSTATS_MAIN                  IOSEEKTIM                      10
SYSSTATS_MAIN                  IOTFRSPEED                   4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR
13 rows selected.

SCOTT@test> show parameter db_file_multiblock_read_count
NAME                                 TYPE     VALUE
------------------------------------ -------- --------
db_file_multiblock_read_count        integer  8

--根據以上資料，計算如下：
--sreadtim
10+8192/4096=12
--mreadtim
10+8*8192/4096=26

15198728278/1639/1000/12=772.7643013016066773418
773+6419=7192

8448729226/1639/1000/12=429.56727811673784828147
430+6419=6849

--正好與上面的測試結果一致。至於這裡的cpu_cost如何計算出來的，估計比較複雜放棄。