澳门新葡亰娱乐官网Oracle表碎片整理操作步骤详解

索引范围扫描,网上已经有很多讨论了,就是按照根、枝、叶的顺序读取。叶块的地址在枝块,枝块地址在根块。找到枝块就可以找到叶块,找到根块就可以找到枝块。那么,如何找到根块呢?

These two terms in the Predicate Information section indicate when the
data source is reduced. Simply, access means only retrieve those records
meeting the condition and ignore others. Filter means *after* you
already got the data, go through them all and keep those meeting the
condition and throw away the others.

高水位线下的许多数据块都是无数据的,但全表扫描的时候要扫描到高水位线的数据块,也就是说oracle要做许多的无用功!因此oracle提供了shrink
space碎片整理功能。对于索引,可以采取rebuild
online的方式进行碎片整理,一般来说,经常进行DML操作的对象DBA要定期进行维护,同时注意要及时更新统计信息!一:准备测试数据,使用HR用户,创建T1表,插入约30W的数据,并根据object_id创建普通索引,表占存储空间34M

澳门新葡亰娱乐官网 ,其实很简单,在 Oracle
中,根块永远在索引段头的下一个块处。因此,索引扫描是不必读取索引段头的。先在数据字典表中找到段头位置,块号加
1 就是根块位置了。

access: 直接获取那些满足条件的数据,抛弃其他不满足的数据
filter:
你已经有了一些数据,对这些已经有的数据应用filter,得到满足filter的数据。

复制代码 代码如下:SQL conn /as sysdba
已连接。 SQL select default_tablespace from dba_users where
username=’HR’; DEFAULT_TABLESPACE
———————————————————— USERS SQL
conn hr/hr 已连接。 SQL insert into t1 select * from t1; 已创建 74812
行。 SQL insert into t1 select * from t1; 已创建 149624 行。 SQL
commit; 提交完成。 SQL create index idx_t1_id on t1(object_id);
索引已创建。 SQL exec
dbms_stats.gather_table_stats(‘HR’,’T1′,CASCADE=TRUE); PL/SQL
过程已成功完成。 SQL select count(1) from t1; COUNT(1) ———- 299248
SQL select sum(bytes)/1024/1024 from dba_segments where
segment_name=’T1′; SUM(BYTES)/1024/1024 ——————– 34.0625
SQL select sum(bytes)/1024/1024 from dba_segments where
segment_name=’IDX_T1_ID’; SUM(BYTES)/1024/1024 ——————– 6

对索引范围扫描时的逻辑读,可以做如下测试:

二:估算表在高水位线下还有多少空间可用,这个值应当越低越好,表使用率越接近高水位线,全表扫描所做的无用功也就越少!DBMS_STATS包无法获取EMPTY_BLOCKS统计信息,所以需要用analyze命令再收集一次统计信息

SQL insert into table1 select rownum,abcde from dba_objects;

 

复制代码 代码如下:SQL SELECT blocks,
empty_blocks, num_rows FROM user_tables WHERE table_name =’T1′;
BLOCKS EMPTY_BLOCKS NUM_ROWS ———- ———— ———- 4302 0
299248 SQL analyze table t1 compute statistics; 表已分析。 SQL SELECT
blocks, empty_blocks, num_rows FROM user_tables WHERE table_name
=’T1′; BLOCKS EMPTY_BLOCKS NUM_ROWS ———- ———— ———-
4302 50 299248 SQL col table_name for a20 SQL SELECT TABLE_NAME, 2
(BLOCKS * 8192 / 1024 / 1024) – 3 (NUM_ROWS * AVG_ROW_LEN / 1024 /
1024) “Data lower than HWM in MB” 4 FROM USER_TABLES 5 WHERE
table_name = ‘T1’; TABLE_NAME Data lower than HWM in MB
——————– ————————- T1 5.07086182

12691 rows created.

一:简要说明

三: 查看执行计划,全表扫描大概需要消耗CPU 1175

SQL commit;

在查看执行计划的信息中,经常会看到两个谓词filter和access,它们的区别是什么,理解了这两个词对我们解读Oracle的执行计划信息会有所帮助。

复制代码 代码如下:SQL explain plan for
select * from t1; 已解释。 SQL select * from

Commit complete.

简单说,执行计划如果显示是access,就表示这个谓词条件的值将会影响数据的访问路径(表还是索引),而filter表示谓词条件的值并不会影响数据访问路径,只起到过滤的作用。

table(dbms_xplan.display); PLAN_TABLE_OUTPUT

SQL create index table1_id on table1(id) tablespace tbs_ts1;

二:举例说明
SQL> create table zhou_t (x int , y int );
表已创建。
SQL> set autotrace trace exp;
SQL> select /*+rule*/ * from zhou_t where x=5;

Plan hash value: 3617692013

Index created.

执行计划

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 299K| 28M| 1175 (1)| 00:00:15 | | 1 | TABLE

SQL exec dbms_stats.gather_table_stats(LHB,TABLE1);

Plan hash value: 1395150869

ACCESS FULL| T1 | 299K| 28M| 1175 (1)| 00:00:15 |

四:删除大部分数据,收集统计信息,全表扫描依然需要消耗CPU 1168

复制代码 代码如下:SQL delete from t1
where object_id100; 已删除298852行。 SQL commit; 提交完成。 SQL select
count(*) from t1; COUNT(*) ———- 396 SQL exec
dbms_stats.gather_table_stats(‘HR’,’T1′,CASCADE=TRUE); PL/SQL
过程已成功完成。 SQL analyze table t1 compute statistics; 表已分析。 SQL
SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE
table_name =’T1′; BLOCKS EMPTY_BLOCKS NUM_ROWS ———-
———— ———- 4302 50 396 SQL explain plan for select * from
t1; 已解释。 SQL select * from table(dbms_xplan.display);

PL/SQL procedure successfully completed.

| Id | Operation | Name |

| 0 | SELECT STATEMENT | |

PLAN_TABLE_OUTPUT

SQL select BLEVEL from dba_INDEXES where index_name=TABLE1_ID and
owner=LHB;

|* 1 | TABLE ACCESS FULL| ZHOU_T |

Plan hash value: 3617692013

BLEVEL

Predicate Information (identified by operation id):

1 – filter(“X”=5)

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 396 | 29700 | 1168 (1)| 00:00:15 | | 1 |


Note

  • rule based optimizer used (consider using cbo)

因为表zhou_t没有创建索引,执行计划没有选择数据访问路径的余地,谓词条件在这里只是起到数据过滤的作用,所以使用了filter

如果在表上创建了索引呢?

SQL> create index zhou_t_idx on zhou_t(x,y);
索引已创建。
SQL> select /*+rule*/ * from zhou_t where x=5;

TABLE ACCESS FULL| T1 | 396 | 29700 | 1168 (1)| 00:00:15 |

五:估算表在高水位线下还有多少空间是无数据的,但在全表扫描时又需要做无用功的数据

复制代码 代码如下:SQL SELECT TABLE_NAME,
2 (BLOCKS * 8192 / 1024 / 1024) – 3 (NUM_ROWS * AVG_ROW_LEN / 1024
/ 1024) “Data lower than HWM in MB” 4 FROM USER_TABLES 5 WHERE
table_name = ‘T1’; TABLE_NAME Data lower than HWM in MB
——————– ————————- T1 33.5791626

六:对表进行碎片整理,重新收集统计信息

复制代码 代码如下:SQL alter table t1
enable row movement; 表已更改。 SQL alter table t1 shrink space cascade;
表已更改。 SQL select sum(bytes)/1024/1024 from dba_segments where
segment_name=’T1′; SUM(BYTES)/1024/1024 ——————– .125 SQL
select sum(bytes)/1024/1024 from dba_segments where
segment_name=’IDX_T1_ID ‘; SUM(BYTES)/1024/1024 ——————–
.0625 SQL SELECT TABLE_NAME, 2 (BLOCKS * 8192 / 1024 / 1024) – 3
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) “Data lower than HWM in MB” 4
FROM USER_TABLES 5 WHERE table_name = ‘T1’; TABLE_NAME Data lower
than HWM in MB ——————– ————————- T1
33.5791626 SQL exec
dbms_stats.gather_table_stats(‘HR’,’T1′,CASCADE=TRUE); PL/SQL
过程已成功完成。
这个时候,只剩下0.1M的无用功了,执行计划中,全表扫描也只需要消耗CPU 3
SQL SELECT TABLE_NAME, 2 (BLOCKS * 8192 / 1024 / 1024) – 3 (NUM_ROWS
* AVG_ROW_LEN / 1024 / 1024) “Data lower than HWM in MB” 4 FROM
USER_TABLES 5 WHERE table_name = ‘T1’; TABLE_NAME Data lower than HWM
in MB ——————– ————————- T1 .010738373 SQL

1

执行计划

select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT

上面先向表中插入了 10000 多行,再创建了一个 1 层高的索引,索引只有 Root
块和叶块。

Plan hash value: 42197324

Plan hash value: 3617692013

| Id | Operation | Name |

| 0 | SELECT STATEMENT | |

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 396 | 29700 | 3 (0)| 00:00:01 | | 1 | TABLE

|* 1 | INDEX RANGE SCAN| ZHOU_T_IDX |

ACCESS FULL| T1 | 396 | 29700 | 3 (0)| 00:00:01 |

总共只有5个块,空块却有50个,明显empty_blocks信息过期 SQL select
blocks,empty_blocks,num_rows from user_tables where table_name=’T1′;
BLOCKS EMPTY_BLOCKS NUM_ROWS ———- ———— ———- 5 50
396 SQL analyze table t1 compute statistics; 表已分析。 SQL select
blocks,empty_blocks,num_rows from user_tables where table_name=’T1′;
BLOCKS EMPTY_BLOCKS NUM_ROWS ———- ———— ———- 5 3
396

Predicate Information (identified by operation id):

1 – access(“X”=5)

Note

  • rule based optimizer used (consider using cbo)

从上面可以看到,谓词条件影响到数据访问的路径——选择了索引,所以用access

 

SQL> create table t
  2  as select rownum r,object_name
  3  from dba_objects
  4  /
Table created.
SQL> create index t_idx on t(r);
Index created.
SQL> execute
dbms_stats.gather_table_stats(user,’t’,cascade=>true)
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly explain
SQL> select * from t
  2  where r = 10000;

Execution Plan

Plan hash value: 470836197

| Id  | Operation                   | Name  | Rows  | Bytes | Cost

(%CPU)| Time     |

|   0 | SELECT STATEMENT            |       |     1 |    30 |     2  
(0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    30 |     2  
(0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1  

(0)| 00:00:01 |

Predicate Information (identified by operation id):

 2 – access(“R”=10000)
使用的谓词是access ,访问的是索引,然后通过rowid 直接取出select结果。
SQL> select * from t
  2  where r > 10000 and r < 50000
  3  /

Execution Plan

Plan hash value: 1601196873

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    

|

|   0 | SELECT STATEMENT  |      | 40001 |  1171K|    88   (2)| 00:00:02
|
|*  1 |  TABLE ACCESS FULL| T    | 40001 |  1171K|    88   (2)|

00:00:02 |

Predicate Information (identified by operation id):

   1 – filter(“R”<50000 AND “R”>10000)
使用的谓词是filter 使用的是全表扫描,过滤掉不需要的行。
SQL> select r from t
  2  where r > 10000
  3  /

Execution Plan

Plan hash value: 3163761342

| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)|

Time     |

|   0 | SELECT STATEMENT     |       | 55631 |   271K|    42   (3)|
00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| T_IDX | 55631 |   271K|    42   (3)|

00:00:01 |

Predicate Information (identified by operation id):

   1 – filter(“R”>10000)
这里的执行计划,就有点意思了,使用的是索引扫描(index fast full
scan),
但是没有通过access 指出。可见oracle 决定使用索引扫描,并不一定要通过
access 来告诉我们。在这里r
可以完全通过读取索引来获得所需要的列值,并且
需要检索索引中的大部分key,所以oracle 决定使用index fast full
scan,这种
访问索引的方式会通过multiblocks read 方式读取索引的
bocks,返回的结果集
是未经排序的,并且因为读取了所以的index blocks ,所以需要对index
blocks
中的index keys 进行过滤。
SQL> create table emp
  2  as select employee_id,first_name,last_name
  3  from hr.employees;
Table created.
SQL> create index emp_idx on emp(employee_id,last_name);
Index created.
SQL> exec
dbms_stats.gather_table_stats(user,’emp’,cascade=>true)
PL/SQL procedure successfully completed.
SQL> select employee_id,last_name
  2  from emp
  3  where employee_id < 200 and last_name = ‘King’
  4  /

Execution Plan

Plan hash value: 3087982339

| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)|

Time     |

|   0 | SELECT STATEMENT |         |     2 |    24 |     1   (0)|
00:00:01 |
|*  1 |  INDEX RANGE SCAN| EMP_IDX |     2 |    24 |     1   (0)|

00:00:01 |

Predicate Information (identified by operation id):

   1 – access(“LAST_NAME”=’King’ AND “EMPLOYEE_ID”<200)
       filter(“LAST_NAME”=’King’)
我上面这个例子也比较有意思,我们在前面创建了一个复合索引,并且在where
子句中
使用了索引中的列。oracle 会根据where
条件通过访问复合索引中的列是否满足employee_id < 200
如果满足再根据条件filter 过滤出last_name = ‘King’ 的index Key。
小结:通过上面的列子,虽然例子不是很经典,但是我觉得已经可以说明。
1、如果oracle 决定使用 index 来获得结果集,不需要使用access
谓词告诉我们,我(oracle)使用了index.
2、通过index 访问数据,也有可能需要用到filter 的。

 

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图