oracle性能优化-索引,统计信息,指定在线重定义的锁定时间

ALTER TABLE CMK_BD_MATERIAL ENABLE ROW MOVEMENT;

ALTER TABLE CMK_BD_MATERIAL SHRINK SPACE CASCADE;

ALTER TABLE CMK_BD_MATERIAL DISABLE ROW MOVEMENT;


--索引重建
alter index K3CLOUD.I_CMK_BD_MTRLCOM rebuild;
alter index K3CLOUD.PK_CMK_BD_MATERIAL rebuild;
alter index K3CLOUD.I_CMK_BD_MATERIAL_FMATERIALID rebuild;



EXEC dbms_stats.gather_table_stats(ownname => 'K3CLOUD', tabname => 'CMK_BD_MATERIAL');



SELECT table_name,
       ROUND((blocks * 8/1024), 2) "高水位空间 M",
       ROUND((num_rows * avg_row_len / 1024/1024), 2) "真实使用空间 M",
       ROUND((blocks * 10 / 100) * 8, 2) "预留空间(pctfree) M",
       ROUND((blocks * 8 - (num_rows * avg_row_len / 1024) -blocks * 8 * 10 / 100), 2) "浪费空间 M",
       ((blocks * 8-(num_rows * avg_row_len / 1024))/1024)/(blocks * 8/1024) "浪费空间 %"
  FROM user_tables
 WHERE table_name ='T_BD_MATERIALSALE';

exec dbms_redefinition.finish_redef_table(user,'K3CLOUD','T_BD_MATERIALSALE');

Oracle 12c 新特性 --- dbms_redefinition.finish_redef_table指定在线重定义的锁定时间

*=================================================================================================


dbms_stats.gather_table_stats 统计表,列,索引的统计信息（包含该表的自身-表的行数、数据块数、行长等信息；
 
列的分析--列值的重复数、列上的空值、数据在列上的分布情况；索引的分析-索引页块的数量、索引的深度、索引聚合因子）.
 

dbms_stats.gather_table_stats (  ownname          VARCHAR2,    

　　　　　　　　　　　　　　　　　　　　　tabname          VARCHAR2,    

　　　　　　　　　　　　　　　　　　　　　partname         VARCHAR2,  

　　　　　　　　　　　　　　　　　　　　　estimate_percent NUMBER,    

　　　　　　　　　　　　　　　　　　　　　block_sample     BOOLEAN,  

　　　　　　　　　　　　　　　　　　　　　method_opt       VARCHAR2,  

　　　　　　　　　　　　　　　　　　　　　degree           NUMBER,  

　　　　　　　　　　　　　　　　　　　　　granularity      VARCHAR2,    

　　　　　　　　　　　　　　　　　　　　　cascade          BOOLEAN,  

　　　　　　　　　　　　　　　　　　　　　stattab          VARCHAR2,    

　　　　　　　　　　　　　　　　　　　　　statid           VARCHAR2,  

　　　　　　　　　　　　　　　　　　　　　statown          VARCHAR2,  

　　　　　　　　　　　　　　　　　　　　　no_invalidate    BOOLEAN,  

　　　　　　　　　　　　　　　　　　　　　force            BOOLEAN);

参数说明:

ownname:要分析表的拥有者

tabname:要分析的表名.

partname:分区的名字,只对分区表或分区索引有用.

estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.

block_sapmple:是否用块采样代替行采样.

method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下(默认值为FOR ALL COLUMNS SIZE AUTO):

　　(1)for all columns:统计所有列的histograms.

　　(2)for all indexed columns:统计所有indexed列的histograms.

　　(3)for all hidden columns:统计你看不到列的histograms

　　(4)for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;

　　　　AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data

degree:决定并行度.默认值为null.

granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.

cascade:是收集索引的信息.默认为FALSE.

stattab:指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,

统计信息会直接更新到数据字典.

no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.

force:即使表锁住了也收集统计信息.


==========================================================================================================================================

关于shrink的思考

一次在ocp书中看到shrink的说明，但说的不太清楚，故讨论下。本人做了些测试，给大家分享下：
 用shrink有两个前提条件:
1、表必须启用row movement，如：
alter table shenlu enable row movement; 这是必要条件，因为行要重组，rowid会变化。
alter table shenlu shrink space;  #开始收缩表。
2、注意表段所在表空间的段空间管理(segment space management)必须为auto 
因表shenlu用的表空间是d_para.检查如下
select dbms_metadata.get_ddl('TABLESPACE','D_PARA') FROM DUAL ；
发现是SEGMENT SPACE MANAGEMENT AUTO 符合要求。

segment shrink分为两个阶段：
  SHRINK SPACE有两种选项:   COMPACT 和 CASCADE
  COMPACT是基于行的Shrink, 减少segment的碎片，使列变得更紧凑，但不会改变高水位线, 不会锁表。
   后者会修改HWM， 会对segment产生锁，但时间比较短。做好不要放在忙时操作。
1、数据重组(compact):通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表要移动的行上加锁。由于涉及到rowid的改变，需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。
2、HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。
注意：shrink space语句两个阶段都执行。
shrink space compact只执行第一个阶段。
如果系统业务比较繁忙，可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。

举例如下：
--------
ALTER TABLE  shenlu ENABLE ROW MOVEMENT  ;
ALTER TABLE  shenlu SHRINK SPACE ;
ANALYZE TABLE shenlu  COMPUTE  STATISTICS ；
select * from dba_tables where table_name='SHENLU'
ALTER  TABLE shenlu DISABLE  ROW movement；
----------------------------------------------------------------------