全表扫描的工作是扫描高水位一下所有的数据块。
这里就有一个问题,什么是高水位线。高水位的标志存在表头。
该数据块以后都是崭新未格式化的数据块,高水位的目的有二。它是全表扫描的
终点,并行插入的起点!
优化全表扫描的办法有四,核心就是降低高水位!
一、降低高水位;二、紧密码放数据;三、并行查询;四、修改初始化参数
降低高水位的办法有三:
    一、在线回收空间;二、挪动表空间;三、导出和导入。
紧密码放数据办法有二:
    一、调整pctfree;二、使用压缩特性。

实验如下:
建立大表,50万左右,分析表,列select * from t1;的计划,看代价!
SQL> conn scott/tiger
Connected.
SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 as select * from emp where 0=9;

Table created.

SQL> insert into t1 select * from emp;

已创建14行。

SQL> insert into t1 select * from t1;

已创建14行。

SQL> /

--一直斜杠,直到

已创建229376行。

SQL> commit;
现在我们就有了45万行左右的大表!
分析表,获得统计信息!
analyze table T1 compute statistics; 

Table analyzed.

SQL> set autot trace expl
SQL> select * from t1;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   458K|    14M|   544  (10)| 00:00:07 |
|   1 |  TABLE ACCESS FULL| T1   |   458K|    14M|   544  (10)| 00:00:07 |
--------------------------------------------------------------------------
我们看到代价为544,我们围绕544进行优化,降低代价!

set autot off
delete t1 where deptno=30;
commit;
analyze table T1 compute statistics; 
select * from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   262K|  8192K|   526   (7)| 00:00:07 |
|   1 |  TABLE ACCESS FULL| T1   |   262K|  8192K|   526   (7)| 00:00:07 |
--------------------------------------------------------------------------
我们看到代价为526,比原来小一点,因为cost是根据块,内存,cpu,网络综合计算的。
行少了一半,但代价没有少多少!因为这里高水位没有变化!

一、在线回收空间;
alter table t1 enable row  movement;
alter table t1 shrink space;
analyze table T1 compute statistics; 
SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE from tabs where table_name='T1';
NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
-------- ---------- ------------ ----------
  262144       1376           32         21
占用了1376个数据块。

select * from t1;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   262K|  8192K|   275  (11)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| T1   |   262K|  8192K|   275  (11)| 00:00:04 |
--------------------------------------------------------------------------
我们看到代价为275,比原来小了接近一半。

二、挪动表空间;
SQL> alter table t1 move tablespace users;
这句话也可以重新码放数据。
SQL> analyze table T1 compute statistics; 

表已分析。

SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE from tabs where table_name='T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
---------- ---------- ------------ ----------
    262144       1568           96        826
占用了1568个数据块,比原来多了192个数据块,这是因为高水位不是一个一个块的挪动,
而是一组一组的挪动。

select * from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   262K|  8192K|   310  (10)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| T1   |   262K|  8192K|   310  (10)| 00:00:04 |
--------------------------------------------------------------------------
代价为310,比原来的275大,因为浪费了一些块,这些块存在于高水位下,但没有数据。
但数据库全表扫描的时候还是查看了空块,浪费了!

三、调整pctfree
SQL> alter table t1 pctfree 0;
Table altered.
这句话的目的是使每个数据块更加紧密的码放数据,没有update,或者update行长不变的表,
pctfree应该设置为0.

SQL> alter table t1 move tablespace users;


analyze table T1 compute statistics; 
select * from t1;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   262K|  8192K|   281  (10)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| T1   |   262K|  8192K|   281  (10)| 00:00:04 |
--------------------------------------------------------------------------
代价为281,比310笑了10%,因为pctfree默认为10。

四、使用压缩存储的新特性
alter table t1 compress;
alter table t1 move tablespace users;
analyze table T1 compute statistics; 
select * from t1;
执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   262K|  8192K|    97  (27)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T1   |   262K|  8192K|    97  (27)| 00:00:02 |
--------------------------------------------------------------------------
代价为97,因为压缩了,数据在同一个数据块内复用了,减少了存储空间。
但带来的负面影响是当我们update的时候,表会暴涨,比不压缩还大,而且普通的
插入不能压缩,只有在直接加载的时候,才会有压缩的特性,参考网站内的压缩表文章。

五、使用并行查询来提高全表扫描的性能。
SQL> select /*+ full(t1) parallel(t1 16) */ * from t1;

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

-----------------------------------------------------------------------------------


| Id  | Operation      | Name     | Rows  | Bytes | Cost (%CPU)| Time  |  TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |   |   262K| 8192K  |  7  (29)| 00:00:01 |        |      |         |
|   1 |  PX COORDINATOR      |       |    |       |       |     |      |      |         |
|   2 |   PX SEND QC (RANDOM)|:TQ10000 |262K|8192K| 7  (29)| 00:00:01 |  Q1,00 | P->S | QC (RAND) |
|   3 |    PX BLOCK ITERATOR |   |   262K|  8192K |   7  (29)| 00:00:01 |  Q1,00 | PCWC |         |
|   4 |     TABLE ACCESS FULL| T1  |  262K|  8192K|  7  (29)| 00:00:01 |  Q1,00 | PCWP |        |

-----------------------------------------------------------------------------------------------
代价为7,比原来的544小了近百倍。效果明显。

六、修改db_file_multiblock_read_count参数,使每次的i/o尽量多读数据块,也会提高全表扫描性能。

SQL> conn / as sysdba
已连接。
SQL> alter system set db_file_multiblock_read_count=1;

系统已更改。

SQL> startup force
重新启动数据库

SQL> conn scott/tiger
已连接。
SQL> set autot trace expl
SQL> select * from t1;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   262K|  8192K|   419   (7)| 00:00:06 |
|   1 |  TABLE ACCESS FULL| T1   |   262K|  8192K|   419   (7)| 00:00:06 |
--------------------------------------------------------------------------
 

Oracle 数据库全表扫描的4种优化方法(DB)-LMLPHP

04-17 00:10