1、oracle資料庫訪問的方法
在執行計劃中，我經常看到有很多訪問資料的方法如：

| Id | Operation             | Name          | Rows | Bytes |TempSpc| Cost (%C
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |    18M| 1589M|       |   189K
|* 1 | HASH JOIN            |               |    18M| 1589M|    11M|   189K
|   2 |   INDEX FAST FULL SCAN| UNI_MY_OBJ_ID |   661K| 3874K|       |   568
|   3 |   TABLE ACCESS FULL   | MYTEST        |    18M| 1483M|       | 64386

--------------------------------------------------------------------------------
| Id | Operation                    | Name          | Rows | Bytes | Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   110 | 9900 |   121   (
|   1 | NESTED LOOPS                |               |   110 | 9900 |   121   (
|* 2 |   INDEX UNIQUE SCAN          | UNI_MY_OBJ_ID |     1 |     6 |     2   (
|   3 |   TABLE ACCESS BY INDEX ROWID| MYTEST        |   110 | 9240 |   119   (
|* 4 |    INDEX RANGE SCAN          | MY_OBJ_ID     |   116 |       |     2   (
--------------------------------------------------------------------------------

TABLE ACCESS FULL，全表掃描，對錶進行全表訪問
INDEX FAST FULL SCAN，全索引掃描，對某個索引進行全部掃描
INDEX UNIQUE SCAN，唯一索引掃描，對某個主鍵或者唯一索引指定的值進行檢索
INDEX RANGE SCAN，按一定範圍對索引進行檢索
下面我就來說說訪問資料的方式：
--全表掃描（Full Table Scans, FTS）
是對錶的所有塊進行全部掃描，透過ROWID的表存取（Table Access by ROWID或rowid lookup）獲取表的全部資料

--索引掃描（Index Scan或index lookup），我們列出幾種情況
a、索引範圍掃描(index range scan)：範圍掃描，如where後面的between，連線中的非唯一等值條件時用得較多
b、索引全掃描(index full scan)：需要檢索的值包含在索引中
c、索引快速掃描(index fast full scan)：在連線條件中，只需要某表的索引做判斷時，使用的是快速索引掃描
d、索引唯一掃描(index unique scan)：主鍵或者唯一索引等值
e、索引跳躍掃描(INDEX SKIP SCAN)：通常使用在組合索引中，對索引欄位中，排在第二個及以後的值進行判斷

2.oracle資料庫連線的方式
--合併連線(Sort Merge Join (SMJ) )
a) 對於非等值連線，這種連線方式的效率是比較高的。
b) 如果在關聯的列上都有索引，效果更好。
c) 對於將2個較大的row source做連線，該連線方法比NL連線要好一些。
d) 但是如果sort merge返回的row source過大，則又會導致使用過多的rowid在表中查詢資料時，資料庫效能下降，因為過多的I/O。
--巢狀迴圈(Nested Loops, NL)：
a) 如果driving row source(外部表)比較小，並且在inner row source(內部表)上有唯一索引，或有高選擇性非唯一索引時，使用這種方法可以得到較好的效率。
b) NESTED LOOPS有其它連線方法沒有的的一個優點是：可以先返回已經連線的行，而不必等待所有的連線操作處理完才返回資料，這可以實現快速的響應時間。
--雜湊連線(Hash Join, HJ)：
a) 這種方法是在oracle7後來引入的，使用了比較先進的連線理論，一般來說，其效率應該好於其它2種連線，但是這種連線只能用在CBO最佳化器中，而且需要設定合適的hash_area_size引數，才能取得較好的效能。
b) 在2個較大的row source之間連線時會取得相對較好的效率，在一個row source較小時則能取得更好的效率。
c) 只能用於等值連線中

3.oracle成本計算
依據cpu消耗使用的成本計算
--According to the CPU costing model:
Cost = (#SRds * sreadtim + #MRds * mreadtim + #CPUCycles / cpuspeed) / sreadtim
where
#SRDs：number of single block reads
#MRDs：number of multi block reads
#CPUCycles：number of CPU Cycles
sreadtim：single block read time
mreadtim：multi block read time
cpuspeed：CPU cycles per second

--建立測試表
CREATE TABLE "HDNC57"."MYTEST"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"STATUS" VARCHAR2(7),
"TIMESTAMP" VARCHAR2(19)
)
插入了大概1000多萬的資料.

--進行統計分析
SQL> EXEC dbms_stats.gather_table_stats('TEST_USER','MYTEST',cascade =>true,estimate_percent => 50,method_opt => 'for all columns size 1' );

PL/SQL procedure successfully completed.

--檢視實際的IO和CPU成本
SQL> alter system flush shared_pool;

System altered.

SQL> alter session set events '10053 trace name context forever, level 2';

Session altered.

SQL> select count(*) from test_user.mytest where object_id >= 400000;

COUNT(*)
----------
    154560
提取跟蹤檔案相應數值：
跟蹤檔案：
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for MYTEST[MYTEST]
Column (#3): OBJECT_ID(
    AvgLen: 5 NDV: 165297 Nulls: 0 Density: 0.000006 Min: 2 Max: 417809
Table: MYTEST Alias: MYTEST
    Card: Original: 18511062.000000 Rounded: 789145 Computed: 789144.97 Non Adjusted: 789144.97
Access Path: TableScan
    Cost: 64361.19 Resp: 64361.19 Degree: 0
      Cost_io: 64195.00 Cost_cpu: 6130585710
      Resp_io: 64195.00 Resp_cpu: 6130585710
Access Path: index (index (FFS))
    Index: MY_OBJ_ID
    resc_io: 11741.00 resc_cpu: 3501331838
    ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
    Cost: 11835.92 Resp: 11835.92 Degree: 1
      Cost_io: 11741.00 Cost_cpu: 3501331838
      Resp_io: 11741.00 Resp_cpu: 3501331838
Access Path: index (IndexOnly)
    Index: MY_OBJ_ID
    resc_io: 1850.00 resc_cpu: 173299064
    ix_sel: 0.042631 ix_sel_with_filters: 0.042631
    Cost: 1854.70 Resp: 1854.70 Degree: 1
Best:: AccessPath: IndexRange
Index: MY_OBJ_ID
         Cost: 1854.70 Degree: 1 Resp: 1854.70 Card: 789144.97 Bytes: 0
oracle基於cbo分析比較後，認為採用index (IndexOnly)最優，所以得出了索引範圍掃描的執行計劃成本，根據索引範圍掃描的成本為1855，具體的執行計劃如下
SQL> explain plan for SELECT COUNT(*) "COUNT(*)" FROM "TEST_USER"."MYTEST" "MYTEST" WHERE "MYTEST"."OBJECT_ID">=400000;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2285605767

-------------------------------------------------------------------------------
| Id | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |     5 | 1855   (1)| 00:00:23 |
|   1 | SORT AGGREGATE   |           |     1 |     5 |            |          |
|* 2 |   INDEX RANGE SCAN| MY_OBJ_ID |   789K| 3853K| 1855   (1)| 00:00:23 |
-------------------------------------------------------------------------------
所以根據cbo，採用了範圍索引掃描。

4、手動計算成本
SQL> select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';
PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                     3074.07407
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 30

該數值匹配到計算公式中mbrc

--根據執行計劃來看，採用的是索引範圍掃描，手動根據公司計算如下：
COST = IO COST + CPUCOST
IO COST = blevel+(leaf_blocks*ix_sel)+(clustering_factor*ix_sel_with_filters)
CPU COST = #cpucycles/(cpuspeed或者cpuspeednw) / 1000 / sreadtim
sreadtim = ioseektim + db_block_size / iotfrspeed = 10 + 8192 / 4096 = 12
IO COST計算需要統計資訊
統計資訊如下：
TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
-------------- ---------------- ---------- ----------- ------------- ----------------- ----------
MYTEST MY_OBJ_ID 2 43346 165297 18780224 18780270

IO COST = 2 + 43346 * 0.042631 = 1850

cpu成本如下：
CPU COST = 173299064 / 3074.07407 / 1000 / 12 = 4.69

所以總體成本為：
1849.883 + 4.69 = 1850 + 5 = 1855
說明的幾點：
--IO COST = blevel+(leaf_blocks*ix_sel)+(clustering_factor*ix_sel_with_filters) 的計算過程中，(clustering_factor*ix_sel_with_filters)值為0，因為這一段表示的主要是對索引進行範圍掃描，無再過濾可能
--resc_cpu: 173299064 值為毫秒，10053裡面評估出來的值
--ix_sel：由於where條件中只有大於> ，所以他是開放，根據索引的統計：
AvgLen: 5 NDV: 165297 Nulls: 0 Density: 0.000006 Min: 2 Max: 417809，繫結的值是400000
(max - 當前值) / (max - min) = (417809 - 400000) / 417807 = 17809 / 417807 = 0.042629，與10053略有偏差。
5、其它掃描方式的成本計算
--全表掃描：
COST = IO COST + CPUCOST
IO COST = ceil(blocks/mbrc)*mreadtim/sreadtim
CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim
IO COST 和CPU COST前半部分主要計算所花費的時間，最後除以單塊訪問的時間用於換算成單塊讀需要讀取的塊數。
--索引唯一掃描：
COST = IO COST + CPUCOST
IO COST = blevel +1 + 1
CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim
IO COST 第一步+1是訪問的葉子塊，最後一步+1是統計回表的塊，因為唯一索引回表塊數也是1。
--索引全掃描：
COST = IO COST + CPUCOST
IO COST = blevel+leaf_blocks+(clustering_factor*ix_sel_wiht_filters)
CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim
索引全掃描是單塊順序讀，掃描的結果有序；
(clustering_factor*ix_sel_wiht_filter)表示回表的IO COST，如果不回表則不需要計算這一步，索引全掃描可以回表，但是索引快速全掃描不會回表；
ix_sel：因為索引全掃描是掃描所有的葉子塊，不需要涉及到索引塊的選擇率，所以ix_sel是1；
ix_sel_with_filters:既然走索引全掃描，則該索引就不會存在過濾的可能，因為如果索引有過濾的可能，則直接走該單索引的範圍掃描，無再過濾值，所以ix_sel_with_filters也是1。
--索引快速全掃描：
COST = IO COST + CPUCOST
IO COST = ceil(leaf_blocks/ mbrc)*mreadtim/sreadtim
CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim
索引快速全掃描是多塊讀，掃描結果無序，不需要回表；
ix_sel、ix_sel_with_filters 這2個引數在索引快速全掃描中無效，因為索引快速全掃描是多塊讀且不需要回表。
--索引跳躍掃描：
COST = IO COST + CPUCOST
若葉子塊數少於前導列的不同值數，IO COST以葉子塊數來計算。
IO COST = blevel+ leaf_blocks+(CLUSTERING_FACTOR*ix_sel_filter)
若葉子塊數多於前導列的不同值數，IO COST以前導列不同值數來計算。
IO COST = blevel+前導列不同值數*ceil(leaf_blocks/distinct_keys) +( CLUSTERING_FACTOR*ix_sel_with_filters)
CPU COST =#cpucycles/(cpuspeed或者cpuspeednw)/ 1000/sreadtim
ix_sel=ix_sel_with_filters：是where條件中走該索引謂詞（一個/多個相乘）的選擇率
跳躍掃描不僅僅出現在無第一個前導列時，如果複合索引包括三個欄位，where條件中沒有第二個欄位的查詢列，但有第一和第三的查詢列時，
其實是可以走索引跳躍掃描，相當於跳過第二個欄位，ix_sel和ix_sel_with_filters就是第一列和第三列的選擇率相乘。而且也等於10053事件trace的ss sel這個引數；
前導列不同值數非常大時走索引跳躍掃描的成本會很高。

oracle基於cbo成本計算方式說明

相關文章