oracle的存储结构（一）

个人的总结：

段和数据文件是多对多关系。

一个段不能跨多个表空间，

一个区不能在多个数据文件上的。

粒度由大到小一次：表空间、数据文件、段、区、块。

问题：
一个表的一行占用多少空间，一个表由占用多少空间？
为什么有的表不大，但是查询起来也很慢的呢？

当然，这是可以从视图中看出来，但是呢，了解oracle的底层结构，对一些调优的东西应该是大有裨益的。

一个测试：

SQL> set timing on
SQL> set autotrace on
SQL> select sysdate from dual
2 /

SYSDATE
--------------
07-12月-10

已用时间: 00: 00: 00.00

执行计划
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation        | Name | Rows | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 | FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          0 consistent gets
          0 physical reads
          0 redo size
        412 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

①写入500 0000条数据

SQL>
2
3 BEGIN
4 FOR I IN 1..5000000 LOOP
5 INSERT INTO td_test VALUES(I);
6 END LOOP;
7 commit;
8 END ;
9 /

PL/SQL 过程已成功完成。

已用时间: 00: 03: 32.17

②SQL> SELECT COUNT(*)
2 FROM TD_TEST
3 /
FROM TD_TEST
*
第 2 行出现错误:
ORA-00376: 此时无法读取文件 4
ORA-01110: 数据文件 4: 'F:\ORACLE\RURU\RURU\USERS01.DBF'

note：

为什么会出错哪？因为此刻修改表空间为offline状态。

SQL> alter tablespace users offline
2 /
Tablespace altered

SQL> alter tablespace users oNline
2 /
Tablespace altered

SQL> SELECT COUNT(*)
2 FROM TD_TEST
3 /

COUNT(*)
----------
   5000000

已用时间: 00: 00: 27.78

执行计划
----------------------------------------------------------
Plan hash value: 1193277808

----------------------------------------------------------------------
| Id | Operation          | Name    | Rows | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 | 16324   (2)| 00:03:16 |
|   1 | SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TD_TEST | 5242K| 16324   (2)| 00:03:16 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
          1 recursive calls
          1 db block gets
     147460 consistent gets
      73973 physical reads
    5298124 redo size
        408 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>
2
3 BEGIN
4 FOR I IN 5000000..9000000 LOOP
5 INSERT INTO td_test VALUES(I);
6 END LOOP;
7 COMMIT;
8 END ;
9 /

PL/SQL 过程已成功完成。

已用时间: 00: 03: 32.62
SQL> select count(*)
2 from td_test
3 /

COUNT(*)
----------
   9000001

已用时间: 00: 00: 23.78

执行计划
----------------------------------------------------------
Plan hash value: 1193277808

----------------------------------------------------------------------
| Id | Operation          | Name    | Rows | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 | 29564   (2)| 00:05:55 |
|   1 | SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TD_TEST | 9637K| 29564   (2)| 00:05:55 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
          6 recursive calls
          1 db block gets
     191556 consistent gets
     111996 physical reads
    4234540 redo size
        411 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

③SQL> delete td_test;

已删除9000001行。

已用时间: 00: 07: 45.82

执行计划
----------------------------------------------------------
Plan hash value: 3749660188

----------------------------------------------------------------------
| Id | Operation          | Name    | Rows | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | DELETE STATEMENT   |         |     1 | 29316   (1)| 00:05:52 |
|   1 | DELETE            | TD_TEST |       |            |          |
|   2 |   TABLE ACCESS FULL| TD_TEST |     1 | 29316   (1)| 00:05:52 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
      24892 recursive calls
   10031653 db block gets
     183347 consistent gets
     132412 physical reads
3030303220 redo size
        680 bytes sent via SQL*Net to client
        550 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
    9000001 rows processed
SQL> commit;

提交完成。

已用时间: 00: 00: 00.00

④delete后继续执行同样的语句

SQL> select count(*)
2 from td_test
3 /

COUNT(*)
----------
         0

已用时间: 00: 01: 00.84

执行计划
----------------------------------------------------------
Plan hash value: 1193277808

----------------------------------------------------------------------
| Id | Operation          | Name    | Rows | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 | 29316   (1)| 00:05:52 |
|   1 | SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TD_TEST |     1 | 29316   (1)| 00:05:52 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
     264874 consistent gets
     106352 physical reads
    9547808 redo size
        407 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

⑤truncate 后执行同样的语句

SQL> truncate table td_test
2 /

表被截断。

已用时间: 00: 00: 03.96
SQL> select count(*)
2 from td_test
3 /

COUNT(*)
----------
         0

已用时间: 00: 00: 00.00

执行计划
----------------------------------------------------------
Plan hash value: 1193277808

----------------------------------------------------------------------
| Id | Operation          | Name    | Rows | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     2   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TD_TEST |     1 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
          1 recursive calls
          1 db block gets
          6 consistent gets
          0 physical reads
         96 redo size
        407 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

总结：

1、不能简单的看执行计划，而应该看表对应的段里到底有多少个块，块是oracle读写输入输出的基本单位哦，因此会出现一个表中没有数据，但是也会查半天的情况。

2、delete和truncate之后读的块为甚麽相差那么多？也就是delete之后表对应的段中所包含的块并没有减少，而truncate却少了。

3、后面的统计信息还是很有用的，

6 consistentgets 逻辑读

0 physical reads 物理读

4、优化的原则是尽可能的少读少写。少读就是少扫描block。