昨天出于无聊, 测试了一下关于Oracle9idual表的相关处理.
发现一个问题, 对于很小的表, 使用full table 桑蚕并不是最好的选择..:-)
SQL> create table dual_x(dummy varchar2(1) primary key);
Table created.
SQL> insert into dual_x values('X');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dual_x;
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL_X'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
375 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
使用普通的全表扫面, 一次全表扫面需要3个consistent read
SQL> /
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL_X'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
SQL> select /*+index_ffs(a)*/ * from dual_x a;
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=41 Bytes=82)
1 0 INDEX (FAST FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=2 C
ard=41 Bytes=82)
Statistics
----------------------------------------------------------
22 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
SQL> /
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=41 Bytes=82)
1 0 INDEX (FAST FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=2 C
ard=41 Bytes=82)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
我修改这条语句的执行计划, 强制使用index fast full scan, 得到的结果是相同的, 还是3个consistent read.
我们来给这个表坐一下分析, 统计一下表/索引相关的信息.
SQL> analyze table dual_x compute statistics for table for all indexes;
Table analyzed.
SQL> select * from dual_x;
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
1 0 INDEX (FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=1 Card=1
Bytes=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
SQL> spool off
我们可以看到现在这个语句的执行计划为INDEX FULL SCAN, 对应的io为1个consistent read. 这是我们的这个小表的执行计划才是最优的.
由上面的例子, 我们可以得出两个结论.
1. 小表也需要优化, 特别是dual表, 每天使用的频率相当高, 这样调整之后, 可以给系统节约大量io读写.
2. 对于小表而言, 全表扫面不一定是最好的.:-)
之后, 我又对v$bh表做了一下查询, 得到下面的这个结果,
SQL> select index_name from ind where table_name = 'DUAL_X';
INDEX_NAME
------------------------------
SYS_C002610
SQL> select * from v$bh where objd = 31819;
FILE# BLOCK# CLASS# STATU XNC FORCED_READS FORCED_WRITES
---------- ---------- ---------- ----- ---------- ------------ -------------
LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N OBJD TS#
-------- ----------------- ------------------ - - - - - - ---------- ----------
1 89425 4 xcur 0 0 0
00 Y N N N N N 31819 0
1 89426 1 xcur 0 0 0
00 Y N N N N N 31819 0
SQL> select * from obj where object_name = 'SYS_C002610';
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SYS_C002610
31820 31820 INDEX
11-APR-05 11-APR-05 2005-04-11:22:25:18 VALID N Y N
SQL> select * from v$bh where objd = 31820;
FILE# BLOCK# CLASS# STATU XNC FORCED_READS FORCED_WRITES
---------- ---------- ---------- ----- ---------- ------------ -------------
LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N OBJD TS#
-------- ----------------- ------------------ - - - - - - ---------- ----------
1 89441 4 xcur 0 0 0
00 Y N N N N N 31820 0
1 89442 1 xcur 0 0 0
00 Y N N N N N 31820 0
问题: 这个地方只有两条记录, 一个是segment header, 一个是segment data, 但是为什么会产生3个consistent read, 哪位大师能给我解释一下吗:-)
重启数据库之后, 再查询dual_x表, 得到以下的信息, 也就是select * from dual_x 只在v$bh中产生一条记录.
SQL> set autotrace on
SQL> select * from dual_x;
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
1 0 INDEX (FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=1 Card=1
Bytes=2)
Statistics
----------------------------------------------------------
171 recursive calls
0 db block gets
26 consistent gets
2 physical reads
60 redo size
375 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
1 0 INDEX (FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=1 Card=1
Bytes=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
375 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from v$bh where objd = 31820;
FILE# BLOCK# CLASS# STATU XNC FORCED_READS FORCED_WRITES
---------- ---------- ---------- ----- ---------- ------------ -------------
LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N OBJD TS#
-------- ----------------- ------------------ - - - - - - ---------- ----------
1 89442 1 xcur 0 0 0
00 Y N N N N N 31820 0
[@more@]
发现一个问题, 对于很小的表, 使用full table 桑蚕并不是最好的选择..:-)
SQL> create table dual_x(dummy varchar2(1) primary key);
Table created.
SQL> insert into dual_x values('X');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dual_x;
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL_X'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
375 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
使用普通的全表扫面, 一次全表扫面需要3个consistent read
SQL> /
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL_X'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
SQL> select /*+index_ffs(a)*/ * from dual_x a;
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=41 Bytes=82)
1 0 INDEX (FAST FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=2 C
ard=41 Bytes=82)
Statistics
----------------------------------------------------------
22 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
SQL> /
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=41 Bytes=82)
1 0 INDEX (FAST FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=2 C
ard=41 Bytes=82)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
我修改这条语句的执行计划, 强制使用index fast full scan, 得到的结果是相同的, 还是3个consistent read.
我们来给这个表坐一下分析, 统计一下表/索引相关的信息.
SQL> analyze table dual_x compute statistics for table for all indexes;
Table analyzed.
SQL> select * from dual_x;
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
1 0 INDEX (FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=1 Card=1
Bytes=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
SQL> spool off
我们可以看到现在这个语句的执行计划为INDEX FULL SCAN, 对应的io为1个consistent read. 这是我们的这个小表的执行计划才是最优的.
由上面的例子, 我们可以得出两个结论.
1. 小表也需要优化, 特别是dual表, 每天使用的频率相当高, 这样调整之后, 可以给系统节约大量io读写.
2. 对于小表而言, 全表扫面不一定是最好的.:-)
之后, 我又对v$bh表做了一下查询, 得到下面的这个结果,
SQL> select index_name from ind where table_name = 'DUAL_X';
INDEX_NAME
------------------------------
SYS_C002610
SQL> select * from v$bh where objd = 31819;
FILE# BLOCK# CLASS# STATU XNC FORCED_READS FORCED_WRITES
---------- ---------- ---------- ----- ---------- ------------ -------------
LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N OBJD TS#
-------- ----------------- ------------------ - - - - - - ---------- ----------
1 89425 4 xcur 0 0 0
00 Y N N N N N 31819 0
1 89426 1 xcur 0 0 0
00 Y N N N N N 31819 0
SQL> select * from obj where object_name = 'SYS_C002610';
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SYS_C002610
31820 31820 INDEX
11-APR-05 11-APR-05 2005-04-11:22:25:18 VALID N Y N
SQL> select * from v$bh where objd = 31820;
FILE# BLOCK# CLASS# STATU XNC FORCED_READS FORCED_WRITES
---------- ---------- ---------- ----- ---------- ------------ -------------
LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N OBJD TS#
-------- ----------------- ------------------ - - - - - - ---------- ----------
1 89441 4 xcur 0 0 0
00 Y N N N N N 31820 0
1 89442 1 xcur 0 0 0
00 Y N N N N N 31820 0
问题: 这个地方只有两条记录, 一个是segment header, 一个是segment data, 但是为什么会产生3个consistent read, 哪位大师能给我解释一下吗:-)
重启数据库之后, 再查询dual_x表, 得到以下的信息, 也就是select * from dual_x 只在v$bh中产生一条记录.
SQL> set autotrace on
SQL> select * from dual_x;
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
1 0 INDEX (FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=1 Card=1
Bytes=2)
Statistics
----------------------------------------------------------
171 recursive calls
0 db block gets
26 consistent gets
2 physical reads
60 redo size
375 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
1 0 INDEX (FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=1 Card=1
Bytes=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
375 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from v$bh where objd = 31820;
FILE# BLOCK# CLASS# STATU XNC FORCED_READS FORCED_WRITES
---------- ---------- ---------- ----- ---------- ------------ -------------
LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N OBJD TS#
-------- ----------------- ------------------ - - - - - - ---------- ----------
1 89442 1 xcur 0 0 0
00 Y N N N N N 31820 0
[@more@]
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/94317/viewspace-795736/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- Problem to connect as SYSDBA(zt from asktom)
- relation between consistent gets and arraysize(zt from asktom)
- about tune db cache
- DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS(zt from asktom)
- Calling Export from Pl/sql using C external proc
- sql to get data from flat file(zt from asktom)
- how tom write pagenation sql
- DIAGNOSING AND RESOLVING ORA-04031 ERROR
- about date manipulation(zt asktom)
- 不要和陌生人喝酒(安全警告)
