oracle實驗記錄 （predicate對cpu cost的影響）
理解謂詞對cpu cost的影響
這個實驗指令碼來自cost-based oracle,動手實驗一次加深印象
SQL> create table t1(
2 v1,
3 n1,
4 n2
5 )
6 as
7 select
8 to_char(mod(rownum,20)),
9 rownum,
10 mod(rownum,20)
11 from
12 all_objects
13 where
14 rownum <= 3000
15 ;
表已建立。
SQL> select count(distinct v1) from t1;
COUNT(DISTINCTV1)
-----------------
20~~~~~~~~~~~~~~~~~~~~V1 有20個不同值 char類 ,每個數對應150行
SQL> select count(distinct n1) from t1;
COUNT(DISTINCTN1)
-----------------
3000~~~~~~~~~~~~~~~~~~~~~N1 有3000個不同值 NUMBER類 表示 是唯一的
SQL> select count(distinct n2) from t1;
COUNT(DISTINCTN2)
-----------------
20~~~~~~~~~~~~~~~~~~~~N2 有20個不同值 NUMBER類 ,每個數對應150行
總共3000 rows
下面4個查詢中 前3個使用+ ordered_predicates HINTS 強制按所寫的predication順序 從左到右
最後一個 讓CBO優化器自己考慮 注意
_pred_move_around = true (predication 移動 oracle自動排序predication)
_optimizer_cost_model = choose(基本使用CPU_COSTING 而不使用8I 的傳統計算(只算IO))
SQL> exec dbms_stats.gather_table_stats('SYS','T1');
PL/SQL 過程已成功完成。
SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';
會話已更改。
SQL> ed
已寫入 file afiedt.buf
1 select
2 /*+ ordered_predicates */
3 v1, n2, n1
4 from
5 t1
6 where
7 v1 = 1
8 and n2 = 18
9* and n1 = 998
10 /
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998)
SQL> ed
已寫入 file afiedt.buf
1 select
2 /*+ordered_predicates */
3 v1, n2, n1
4 from
5 t1
6 where
7 n1 = 998
8 and n2 = 18
9* and v1 = 1
SQL> /
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)
SQL> ed
已寫入 file afiedt.buf
1 select
2 /*+ ordered_predicates */
3 v1, n2, n1
4 from
5 t1
6 where
7 v1 = '1'
8 and n2 = 18
9* and n1 = 998
SQL> /
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V1"='1' AND "N2"=18 AND "N1"=998)
SQL> ed
已寫入 file afiedt.buf
1 select
2 v1, n2, n1
3 from
4 t1
5 where
6 v1 = 1
7 and n2 = 18
8* and n1 = 998
SQL> /
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)
看TRACE
使用
predication 順序- filter(TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998)
select
/*+ ordered_predicates */
v1, n2, n1
from
t1
where
v1 = 1
and n2 = 18
and n1 = 998
Access Path: TableScan
Cost: 3.25 Resp: 3.25 Degree: 0
Cost_io: 3.00 Cost_cpu: 1070604****************
Resp_io: 3.00 Resp_cpu: 1070604
Best:: AccessPath: TableScan
Cost: 3.25 Degree: 1 Resp: 3.25 Card: 0.00 Bytes: 0
分析:總共3000行中oracle首先對v1 = 1 進行了3000次轉換(to_number) 又在表裡3000行中 讓 V1與一個數值進行比較產生了 150行(3000/20) CPU進行了3000次比較操作
又在這150行中 n2與一個數值比較 產生8行(150/20) CPU進行150次比較操作, 最後在這8行中 N1與一個數值比較(N1 唯一) CPU進行8次比較操作,CPU一共進行了 3158次數值比較
+3000次轉換操作
****************
使用
predication 順序- filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)
select
/*+ordered_predicates */
v1, n2, n1
from
t1
where
n1 = 998
and n2 = 18
and v1 = 1
Access Path: TableScan
Cost: 3.18 Resp: 3.18 Degree: 0
Cost_io: 3.00 Cost_cpu: 762786************
Resp_io: 3.00 Resp_cpu: 762786
Best:: AccessPath: TableScan
Cost: 3.18 Degree: 1 Resp: 3.18 Card: 0.00 Bytes: 0
分析:總共3000行中oracle 讓N1與一個數值 進行了3000次比較 產生 1行(3000/3000) CPU進行3000次比較操作 ,對這一行 N2與一個數比較一次 產生1行 CPU進行了1次比較操作,
對這一行 V1與一個數比較一次CPU進行1次比較操作, 且V1轉換一次
所以為3002次比較操作+1次轉換
這個是最節約CPU成本的
******************************
使用
predication 順序- filter("V1"='1' AND "N2"=18 AND "N1"=998)
select
/*+ ordered_predicates */
v1, n2, n1
from
t1
where
v1 = '1'
and n2 = 18
and n1 = 998
Access Path: TableScan
Cost: 3.18 Resp: 3.18 Degree: 0
Cost_io: 3.00 Cost_cpu: 770604**********************
Resp_io: 3.00 Resp_cpu: 770604
Best:: AccessPath: TableScan
Cost: 3.18 Degree: 1 Resp: 3.18 Card: 0.00 Bytes: 0
分析:總共3000行中 oracle 讓V1與一個值比較 產生150行 CPU進行了3000次比較操作,150行中 讓N2與一個值比較cpu進行比較操作150次產生8行,又在這8行中 讓N1與一個數值比
較 CPU比較8次產生1行
注意沒有進行轉換3000次對V1
所以ORACLE CPU進行了3158次比較操作
SQL> select 1070604-770604 from dual;
1070604-770604
--------------
300000
3000次轉換 CPU COST 多了 300000 這樣可以得出 一次to_number隱式轉換 需要100次 CPU操作(300000/3000=100)
**************************
oracle optimizer自動選 順序- filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)
select
v1, n2, n1
from
t1
where
v1 = 1
and n2 = 18
and n1 = 998
Access Path: TableScan
Cost: 3.18 Resp: 3.18 Degree: 0
Cost_io: 3.00 Cost_cpu: 762786*********************
Resp_io: 3.00 Resp_cpu: 762786
Best:: AccessPath: TableScan
Cost: 3.18 Degree: 1 Resp: 3.18 Card: 0.00 Bytes: 0
分析:最後這個ORACLE 採用了第2個SELECT 所使用的PREDICATION 順序 沒有按照 指定的順序 oracle進行COST 評估後採用了這個順序
_pred_move_around = true 由於這個引數 讓ORACLE自動對PREDICATION 選擇合適的順序
SQL> alter system set "_pred_move_around"=false;************實驗改下
系統已更改。
SQL> set autotrace traceonly
SQL> alter session set events '10053 trace name context forever';
會話已更改。
SQL> select
2 v1, n2, n1
3 from
4 t1
5 where
6 v1 = 1
7 and n2 = 18
8 and n1 = 998
9 /
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 9 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)~~~~~~~~~~~~~~~~雪特還是自己變化了
_pred_move_around FALSE FALSE FALSE FALSE
enables predicate move-around
10GR2中好象沒起作用
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-613681/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 修改系統時間對oracle的影響Oracle
- git checkout 對工作目錄的影響 —— Git 學習筆記 21Git筆記
- 修改主機時區對Oracle的影響分析Oracle
- oracle點陣圖索引對DML操作的影響Oracle索引
- 杜比實驗室：Covid -19對全球娛樂體驗和支出的影響
- 淺析CPU結構對程式的影響以及熔斷原理
- 新特性：/dev/shm對Oracle 11g的影響devOracle
- 磁碟排序對Oracle資料庫效能的影響PT排序Oracle資料庫
- 【Dataguard】Oracle多租戶環境對Dataguard的影響Oracle
- 虛擬記憶體對 OI 的影響記憶體
- Oracle 11g 測試停庫對job的影響Oracle
- 技術人員評估英特爾CPU新漏洞對效能的影響
- 『做題記錄』[AGC028C] Min Cost CycleGC
- 【原創】ARM平臺記憶體和cache對xenomai實時性的影響記憶體AI
- Oracle優化案例-select中to_clob對效能的影響（二十一）Oracle優化
- 【CURSOR】Oracle繫結變數、執行計劃對遊標的影響Oracle變數
- 測試修改作業系統時間&時區對oracle的影響作業系統Oracle
- Oracle 查詢轉換-03 Predicate PushingOracle
- unusable index對DML/QUERY的影響Index
- Nologging對恢復的影響(二)
- 語言對思維的影響
- Nologging對恢復的影響(一)
- 網線的分類與對網速的影響 網線對網速影響大嗎？
- 大型網站的HTTPS實踐（三）——HTTPS對效能的影響網站HTTP
- 浮動的盒子對img的影響
- Oracle優化案例-統計資訊對執行計劃的影響（十三）Oracle優化
- 【效能】Oracle表並行對邏輯遷移後查詢效能的影響Oracle並行
- MySQL:簡單記錄character_set_server影響引數MySqlServer
- Oracle 監聽投毒COST解決Oracle
- 大資料實驗記錄大資料
- 觀影記錄
- 【恩墨學院】深入解讀Oracle 18c對於DBA的影響及應對措施Oracle
- Linux讀寫執行許可權對目錄和檔案的影響Linux
- 來電對播放音樂的影響
- python：super()對多繼承的影響Python繼承
- DB2 HADR對效能的影響DB2
- INDEX建立方式對SQL的影響IndexSQL
- 關於OPcache對Swoole影響的理解opcache
- NEJM：全球首個咖啡對心臟急性影響的隨機對照試驗出爐！隨機