oracle構建索引index後table的10046資料塊讀取測試
測試目的
1,未建立索引前的表的掃描情況
1,掃描哪些資料塊
2,資料塊之間的關係
3,物理讀
4,邏輯讀
5,以上測試區分：全表掃描與部分表記錄掃描
6,掃描資料塊是採用單塊讀取還是多塊讀取還是先單塊讀後多塊讀取？
2,小結：
1,表掃描速度與資料塊大小的關係
2,表掃描與並行度設定的關係
3,表掃描與db cache的關係
前文測試了全表掃描的資料塊讀取情況；如果對錶建立了索引，先讀取索引，然後根據ROWID再讀取對應表記錄的資料塊
SQL> create table t_detail(a int);
Table created.
--插入10000條記錄
SQL> insert into t_detail select level from dual connect by level<=10000;
10000 rows created.
SQL> commit;
Commit complete.
SQL> create index idx_t_detail on t_detail(a);
Index created.
--跟蹤已建索引的查詢
SQL> alter system set events '10046 trace name context level 8';
System altered.
--因表資料量10000條，10046 trace對查詢速度有一定影響
SQL> select count(a) from t_detail where a=2000;
COUNT(A)
----------
1
--關閉10046 trace
SQL> alter system set events '10046 trace name context off';
System altered.
--僅摘錄10046 trace重要內容
WAIT #2: nam='Disk file operations I/O' ela= 886 FileOperation=2 fileno=10 filetype=2 obj#=69559 tim=31824399508 --先是一個等待事件
WAIT #2: nam='db file sequential read' ela= 20687 file#=10 block#=276483 blocks=1 obj#=69559 tim=31824420353 －單塊讀 file#=10 block#=276483 blocks=1 obj#=69559
WAIT #2: nam='db file sequential read' ela= 823 file#=10 block#=276488 blocks=1 obj#=69559 tim=31824421542 --繼續單塊讀 file#=10 block#=276488 blocks=1 obj#=69559
FETCH #2:c=0,e=23170,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=1976055679,tim=31824421699 --然後提取資料了
STAT #2 id=1 cnt=1 pid=0 pos=1 bj=0 p='SORT AGGREGATE (cr=2 pr=2 pw=0 time=0 us)'
STAT #2 id=2 cnt=1 pid=1 pos=1 bj=69559 p='INDEX RANGE SCAN IDX_T_DETAIL (cr=2 pr=2 pw=0 time=0 us cost=1 size=13 card=1)'
--上述2個單塊讀的資料塊是什麼呢？表還是表所屬索引的資料塊
--可知上述TRACE中的物件不是表
SQL> select owner,object_name,object_id from dba_objects where object_name='T_DETAIL' and wner='SCOTT';
OWNER OBJECT_NAME OBJECT_ID
------------------------------ -------------------------------------------------------------------------------- ----------
SCOTT T_DETAIL 69558
--是不是索引呢,就是索引，所以單塊讀先是讀取索引的資料塊
SQL> select owner,object_name,object_id from dba_objects where object_name='IDX_T_DETAIL' and wner='SCOTT';
OWNER OBJECT_NAME OBJECT_ID
------------------------------ -------------------------------------------------------------------------------- ----------
SCOTT IDX_T_DETAIL 69559
--既然讀取索引的資料塊，哪這是索引的哪個位置的資料塊呢
--index的段頭塊為276482
SQL> select segment_name,HEADER_FILE,header_block from dba_segments ds where ds.segment_name='IDX_T_DETAIL';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------------------------------------------------------------------- ----------- ------------
IDX_T_DETAIL 10 276482
--表佔用的塊
SQL> select segment_name,extent_id,block_id,blocks from dba_extents where wner='SCOTT' and segment_name='T_DETAIL';
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ---------- ----------
T_DETAIL 0 220392 8
T_DETAIL 1 276336 8
T_DETAIL 2 276344 8
--index佔用的塊
SQL> select segment_name,extent_id,block_id,blocks from dba_extents where wner='SCOTT' and segment_name='IDX_T_DETAIL';
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
-------------------------------------------------------------------------------- ---------- ---------- ----------
IDX_T_DETAIL 0 276480 8
IDX_T_DETAIL 1 276488 8
IDX_T_DETAIL 2 276496 8
IDX_T_DETAIL 3 276504 8
而上述的10046的trace中單塊讀 file#=10 block#=276483 blocks=1 obj#=69559，正好位於第一個extent之內
另一個單塊讀file#=10 block#=276488 blocks=1 obj#=69559，剛好是第二個extent的頭一個塊
--我們看看這2個資料塊的內容
---1,block 276483
Start dump data blocks tsn: 8 file#:10 minblk 276483 maxblk 276483
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8, rdba=42219523
BH (0x000007FF023F7FE8) file#: 10 rdba: 0x02843803 (10/276483) class: 1 ba: 0x000007FF02360000
--中間部分略
Block header dump: 0x02843803
Object id on Block? Y
seg/obj: 0x10fb7 csc: 0x00.5826bb itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2843800 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.005826bb
Branch block dump --分支塊轉儲
---中間部分略
row#0[8051] dba: 42219525=0x2843805
col 0; len 3; (3): c2 05 57
col 1; TERM
row#1[8042] dba: 42219526=0x2843806
col 0; len 3; (3): c2 0a 42
col 1; TERM
row#2[8033] dba: 42219527=0x2843807
col 0; len 3; (3): c2 0f 2d
col 1; TERM
row#3[8024] dba: 42219528=0x2843808
col 0; len 3; (3): c2 14 18
col 1; TERM
row#4[8015] dba: 42219529=0x2843809
col 0; len 3; (3): c2 19 03
col 1; TERM
row#5[8006] dba: 42219530=0x284380a
col 0; len 3; (3): c2 1d 52
col 1; TERM
row#6[7997] dba: 42219531=0x284380b
col 0; len 3; (3): c2 22 3d
col 1; TERM
row#7[7988] dba: 42219532=0x284380c
col 0; len 3; (3): c2 27 28
col 1; TERM
row#8[7979] dba: 42219533=0x284380d
col 0; len 3; (3): c2 2c 13
col 1; TERM
row#9[7970] dba: 42219534=0x284380e
col 0; len 3; (3): c2 30 62
col 1; TERM
row#10[7961] dba: 42219535=0x284380f
col 0; len 3; (3): c2 35 4d
col 1; TERM
row#11[7952] dba: 42219537=0x2843811
col 0; len 3; (3): c2 3a 38
col 1; TERM
row#12[7943] dba: 42219538=0x2843812
col 0; len 3; (3): c2 3f 23
col 1; TERM
row#13[7934] dba: 42219539=0x2843813
col 0; len 3; (3): c2 44 0e
col 1; TERM
row#14[7925] dba: 42219540=0x2843814
col 0; len 3; (3): c2 48 5d
col 1; TERM
row#15[7916] dba: 42219541=0x2843815
col 0; len 3; (3): c2 4d 48
col 1; TERM
row#16[7907] dba: 42219542=0x2843816
col 0; len 3; (3): c2 52 33
col 1; TERM
row#17[7898] dba: 42219543=0x2843817
col 0; len 3; (3): c2 57 1e
col 1; TERM
row#18[7889] dba: 42219544=0x2843818
col 0; len 3; (3): c2 5c 09
col 1; TERM
row#19[7880] dba: 42219545=0x2843819
col 0; len 3; (3): c2 60 58
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 8 file#: 10 minblk 276483 maxblk 276483
--轉儲第二個索引塊
SQL> alter system dump datafile 10 block 276488;
System altered
--略去無關內容
Block header dump: 0x02843808
Object id on Block? Y
seg/obj: 0x10fb7 csc: 0x00.5826bb itc: 2 flg: E typ: 2 - INDEX
brn: 1 bdba: 0x2843800 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 --說明索引塊也會記錄itl事務槽內容
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.005826bb
Leaf block dump --說明是葉子塊
===============
header address 224405092=0xd602664
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
－－略去中間內容
kdxlebksz 8036
row#0[8023] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 14 18
col 1; len 6; (6): 02 83 5c ee 02 5a
row#1[8010] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 14 19
col 1; len 6; (6): 02 83 5c ee 02 5b
row#2[7997] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 14 1a
col 1; len 6; (6): 02 83 5c ee 02 5c
row#3[7984] flag: ------, lock: 0, len=13
－－略去中間內容
row#477[1827] flag: ------, lock: 0, len=12
col 0; len 2; (2): c2 19
col 1; len 6; (6): 02 83 5c ef 01 a3
row#478[1814] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 19 02
col 1; len 6; (6): 02 83 5c ef 01 a4
----- end of leaf block dump -----
End dump data blocks tsn: 8 file#: 10 minblk 276488 maxblk 276488
--轉儲下其它索引塊
－－276496
Start dump data blocks tsn: 8 file#:10 minblk 276496 maxblk 276496
frmt: 0x02 chkval: 0xc06a type: 0x20=FIRST LEVEL BITMAP BLOCK
Dump of First Level Bitmap Block
--------------------------------
nbits : 2 nranges: 2 parent dba: 0x02843801 poffset: 1
--276504索引塊
Start dump data blocks tsn: 8 file#:10 minblk 276504 maxblk 276504
*** 2013-03-15 20:04:18.821
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8, rdba=42219544
Block dump from disk:
buffer tsn: 8 rdba: 0x02843818 (10/276504)
scn: 0x0000.005826c4 seq: 0x02 flg: 0x04 tail: 0x26c40602
frmt: 0x02 chkval: 0x14da type: 0x06=trans data
－－－中間內容略
Leaf block dump
header address 224405092=0xd602664
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
－－中間內容略
----- end of leaf block dump -----
End dump data blocks tsn: 8 file#: 10 minblk 276504 maxblk 276504
小結：1，在有索引的情況下，查詢表先是掃描索引塊的分支塊，然後是葉子塊，透過葉子塊找到最終的記錄
邏輯讀為2，我分析因為分子塊和葉子塊各為兩次；故為2；如果索引的高度增高，這樣邏輯讀次數就會變大了；所以重構索引很重要
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 1976055679
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
|* 2 | INDEX RANGE SCAN| IDX_T_DETAIL | 1 | 13 | 1 (0)| 00:00:01
|
--------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"=2000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
544 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-756282/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle index索引結構（一）OracleIndex索引
- Oracle index索引塊分裂split資訊彙總OracleIndex索引
- 【Oracle】-【索引】先查資料再建索引，還是先建索引再插資料？Oracle索引
- oracle 構建索引index_logging_nologgingOracle索引Index
- 索引組織表（Index Organizied Table）索引Index
- 【Mysql】資料庫索引，百萬資料測試索引效果MySql資料庫索引
- 資料庫索引背後的資料結構資料庫索引資料結構
- oracle 測試 清除分割槽資料，索引釋放空間Oracle索引
- 資料庫表--index organized table資料庫IndexZed
- 資料庫表--index clustered table資料庫Index
- 使用10046跟蹤Oracle前映象資料讀Oracle
- 僅對部分資料構建索引索引
- 索引組織表（index organized table ，IOT）索引IndexZed
- Oracle資料庫中的不可見索引 invisible indexOracle資料庫索引Index
- oracle index索引原理OracleIndex索引
- Oracle表table與索引index的分析及索引重建及統計資訊匯入匯出Oracle索引Index
- 如何加快建 index 索引 的時間Index索引
- 使用SQLT來構建Oracle測試用例SQLOracle
- sqlserver讀取oracle資料庫資料SQLServerOracle資料庫
- netty讀取大塊的有分界資料Netty
- Oracle中truncate table後的資料恢復(Oracle資料恢復工具-ODU)Oracle資料恢復
- mysql資料庫索引的建立以及效能測試MySql資料庫索引
- ORACLE資料庫Table (index) 分析統計及其生成方式Oracle資料庫Index
- 構建之法讀後感
- oracle10g r2_sql tuning_bitmap index點陣圖索引_index效能小測試OracleSQLIndex索引
- 往返讀取後臺資料的代價
- 有關oracle external table的一點測試。Oracle
- 轉載-treedump索引讀取索引儲存的資料值--非唯一性索引索引
- JB的測試之旅-測試資料的準備/構造
- 讀資料工程之道：設計和構建健壯的資料系統21資料獲取
- 關於Oracle 9i 跳躍式索引掃描(Index Skip Scan)的小測試 (轉)Oracle索引Index
- Oracle Index-organized table (IOT)概述OracleIndexZed
- MySQL構造測試資料MySql
- 讀資料工程之道：設計和構建健壯的資料系統24獲取資料的方式
- Oracle索引分裂（Index Block Split）Oracle索引IndexBloC
- Oracle筆記 之 索引(index)Oracle筆記索引Index
- oracle dml與索引index(一)Oracle索引Index
- Oracle資料庫Table,Index,Database分析統計資料方式總結及注意點（zt）Oracle資料庫IndexDatabase