ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > 用执行计划看truncate

用执行计划看truncate

原创 Linux操作系统 作者：279531451 时间：2009-10-16 22:26:29 0 删除 编辑

1.connect sys as sysdba
2.@?/rdbms/admin/utlxplan(@?==>ORACLE_HOME)
3.@?/sqlplus/admin/plustrce
4.grant plustrace to public;（可以为普通用户运行）
 

SQL> drop table t1;
Table dropped.
SQL> create table t1(x int) pctfree 90;
create table t1(x int) pctfree 90
*
ERROR at line 1:
ORA-04000: the sum of PCTUSED and PCTFREE cannot exceed 100
SQL> create table t1(x char(1000)) pctfree 90 pctused 2; --pctfree、pctused都是表的属性，后面会讲到
Table created.
SQL> exec show_space('T1');
Free Blocks.............................               0
Total Blocks............................               8
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               7
Unused Bytes............................          57,344
Last Used Ext FileId....................               1
Last Used Ext BlockId...................          62,089
Last Used Block.........................               1
PL/SQL procedure successfully completed.
SQL> insert into t1 values (1);
1 row created.
SQL> exec show_space('T1');
Free Blocks.............................               1
Total Blocks............................               8
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               6
Unused Bytes............................          49,152
Last Used Ext FileId....................               1
Last Used Ext BlockId...................          62,089
Last Used Block.........................               2
PL/SQL procedure successfully completed.
SQL> insert into t1 values (1);
1 row created.
SQL> exec show_space('T1');
Free Blocks.............................               1
Total Blocks............................               8
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               5
Unused Bytes............................          40,960
Last Used Ext FileId....................               1
Last Used Ext BlockId...................          62,089
Last Used Block.........................               3
PL/SQL procedure successfully completed.
SQL> insert into t1 values (1);
1 row created.
SQL> exec show_space('T1');
Free Blocks.............................               1
Total Blocks............................               8
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               4
Unused Bytes............................          32,768
Last Used Ext FileId....................               1
Last Used Ext BlockId...................          62,089
Last Used Block.........................               4
PL/SQL procedure successfully completed.
SQL> delete t1;  --<===用delete删除，不会降低高水位
3 rows deleted.
SQL> exec show_space('T1');
Free Blocks.............................               1
Total Blocks............................               8
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               4
Unused Bytes............................          32,768
Last Used Ext FileId....................               1
Last Used Ext BlockId...................          62,089
Last Used Block.........................               4
PL/SQL procedure successfully completed.
SQL> truncate table t1; --<===用truncate删除，高水位降低到1
Table truncated.
SQL> exec show_space('T1');
Free Blocks.............................               0
Total Blocks............................               8
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               7
Unused Bytes............................          57,344
Last Used Ext FileId....................               1
Last Used Ext BlockId...................          62,089
Last Used Block.........................               1
PL/SQL procedure successfully completed.
SQL> set autot traceonly      -- <==我们来看一下执行计划
SQL> select * from t1;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  1002 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |     1 |  1002 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets      --<===没有数据还有三个块，这是因为要进行三次读第一个块
          0  physical reads
          0  redo size
        270  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
SQL> commit;
Commit complete.
SQL> insert into t1 values (1);
1 row created.

Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT |      |     1 |   100 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          6  db block gets
          4  consistent gets
          0  physical reads
       1536  redo size
        932  bytes sent via SQL*Net to client
        940  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> commit;
Commit complete.
SQL> select * from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  1002 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |     1 |  1002 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets    --<==第一条记录在第一个块中
          0  physical reads
          0  redo size
       1413  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> select * from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |  2004 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |     2 |  2004 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets --<==第二条记录在第二个块中
          0  physical reads
          0  redo size
       2460  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)
          2  rows processed
SQL> insert into t1 values (1);
1 row created.

Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT |      |     1 |   100 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          7  db block gets
          5  consistent gets
          0  physical reads
       1732  redo size
        932  bytes sent via SQL*Net to client
        940  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> commit;
Commit complete.
SQL> select * from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |  3006 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |     3 |  3006 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets  --<==第三条记录在第一个块中
          0  physical reads
          0  redo size
       3473  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)
          3  rows processed

SQL> delete  t1;
3 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 775918519
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |     1 |     2   (0)| 00:00:01 |
|   1 |  DELETE            | T1   |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          4  recursive calls
          7  db block gets
         11  consistent gets
          0  physical reads
       4348  redo size
        931  bytes sent via SQL*Net to client
        925  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed
SQL> select * from t1;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  1002 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |     1 |  1002 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets    --<==表中没有数据，还有5个逻辑读，这是因为delete不能降低高水位，TABLE ACCESS FULL会扫描所有数据块
          0  physical reads
          0  redo size
        270  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
SQL> truncate table t1;
Table truncated.
SQL> select * from t1;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  1002 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |     1 |  1002 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        270  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
SQL> commit;
Commit complete.
SQL> select * from t1;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  1002 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |     1 |  1002 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        270  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
         
====我们再来看一下reuse storage
SQL> insert into t1 values (1);
1 row created.

Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT |      |     1 |   100 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          6  db block gets
          4  consistent gets
          0  physical reads
       1536  redo size
        932  bytes sent via SQL*Net to client
        940  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> insert into t1 values (1);
1 row created.

Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT |      |     1 |   100 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          7  db block gets
          6  consistent gets
          0  physical reads
       1616  redo size
        932  bytes sent via SQL*Net to client
        940  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> insert into t1 values (1);
1 row created.

Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT |      |     1 |   100 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          7  db block gets
          6  consistent gets
          0  physical reads
       1616  redo size
        932  bytes sent via SQL*Net to client
        940  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> insert into t1 values (1);
1 row created.

Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT |      |     1 |   100 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          7  db block gets
          6  consistent gets
          0  physical reads
       1616  redo size
        932  bytes sent via SQL*Net to client
        940  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> insert into t1 values (1);
1 row created.

Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT |      |     1 |   100 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          7  db block gets
          6  consistent gets
          0  physical reads
       1616  redo size
        932  bytes sent via SQL*Net to client
        940  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> commit;
Commit complete.
SQL> select * from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |  5010 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |     5 |  5010 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       5499  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)
          5  rows processed
SQL> truncate table t1 reuse storage;
Table truncated.
SQL> select * from t1;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  1002 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |     1 |  1002 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
          6  consistent gets
          1  physical reads
         96  redo size
        270  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
SQL> select * from t1;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  1002 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |     1 |  1002 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        270  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
SQL> select * from t1;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |  1002 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |     1 |  1002 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        270  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
 

In Oracle 9.2
segment header initially read 3 times
segment header read again every 10 extents

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7551038/viewspace-616770/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 找出当前用户下所有表的未索引的外码
下一篇： 完整性约束延迟性和即时性
请登录后发表评论 登录
全部评论
279531451

注册时间：2009-09-01

  • 博文量
    100
  • 访问量
    182887

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593