6.分区表坏块导致统计信息收集失败进而影响sql执行

sql执行效率下降，从每次时间50ms降到每次执行20s。
sql：

select *
  from pdms_testdata
 where limitid = 26275724
   and opertime >= to_date('2018-03-21 16:48:47', 'yyyy-mm-dd hh24:mi:ss')
   and opertime <= to_date('2018-03-22 16:48:47', 'yyyy-mm-dd hh24:mi:ss');

查看sql执行计划,发现sql在pdms_testdata_2 和 pdms_testdata_3表上的走错索引。
查看每天的收集统计信息作业 ----------正常
查看表pdms_testdata_2 和 pdms_testdata_3的统计信息 dba_tab_partitions 和 dba_tables，发现表的全局统计信息缺失。

nologging方式导入数据到历史分区时存在逻辑坏块，由于没有logging信息，数据库无法自动修正该数据。
后来收集统计信息时但凡扫描到该block，就会报错，导致表的全局统计信息收集失败，进而影响了CBO选择正确的索引。
解决方式：考虑到历史分区的数据已超过保留期限，truncate掉有坏块的表分区，重新收集统计信息即可。

解决：
1、尝试收集表的全局统计信息 ， 但是报错

begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname =>'PDMS01',
tabname =>'PDMS_TESTDATA_2',
partname => 'SYS_P2362',
estimate_percent => 0.01,
degree =>4,
granularity=> 'ALL',
NO_INVALIDATE=>false);
end;
/

报错信息：

ERROR at line 1:
ORA-12801: error signaled in parallel query server P015
ORA-01578: ORACLE data block corrupted (file # 249, block # 1348668)
ORA-01110: data file 249: '/data01/oradata01/PDMSDB/pdms_new_data56.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 2

	
$ oerr ora 01578
01578, 00000, "ORACLE data block corrupted (file # %s, block # %s)"
// *Cause:  The data block indicated was corrupted, mostly due to software
//          errors.
// *Action: Try to restore the segment containing the block indicated. This
//          may involve dropping the segment and recreating it. If there
//          is a trace file, report the errors in it to your ORACLE
//          representative.
	
$ oerr ora 26040
26040, 00000, "Data block was loaded using the NOLOGGING option\n"
//* Cause: Trying to access data in block that was loaded without
//*        redo generation using the NOLOGGING/UNRECOVERABLE option
//* Action: Drop the object containing the block.	

2、查看alert日志，发现在2017-12-11使用nologging方式导入表的历史分区p_history时就有逻辑坏块产生。

Mon Dec 11 08:38:05 2017
Errors in file /data01/u01/app/oracle/diag/rdbms/pdmsdbp/PDMSDB/trace/PDMSDB_ora_17870.trc  (incident=1203817):
ORA-01578: ORACLE data block corrupted (file # 249, block # 1349266)
ORA-01110: data file 249: '/data01/oradata01/PDMSDB/pdms_new_data56.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

3、通过ORA-报错从alert日志中找出坏块的 file id 和block id

grep "ORA-01578" alert_PDMSDB.log >tmp.log
awk '{print $8,$11}' tmp.log| uniq -c | sort -rn
      11 249, 1349290)
      6 249, 1349262)
      3 252, 1276464)
      3 250, 1323651)
      3 249, 1349266)
      3 248, 1355565)
      2 252, 1276331)
      2 251, 1290846)
      2 251, 1290834)
      2 251, 1290826)
      2 251, 1290825)
      2 250, 1323158)
      2 249, 1349431)
      2 249, 1348713)
      2 249, 1348701)
      2 249, 1348679)
      2 249, 1348668)

4、查看坏块对应的表和分区

select a.owner,a.segment_name,a.partition_name,a.tablespace_name  
  from dba_extents a  
  where file_id = 249   
  and 1349290 between block_id and block_id + blocks - 1;  

OWNER      SEGMENT_NAME         PARTITION_NAME       TABLESPACE
---------- -------------------- -------------------- ----------
PDMS01     PDMS_TESTDATA_3      P_HISTORY            PDMS_NEW_DATA

5、truncate 存在逻辑坏块的分区 ，然后重新收集全局统计信息

SQL> alter table pdms01.pdms_testdata_1 truncate partition p_history;
SQL> alter table pdms01.pdms_testdata_2 truncate partition p_history;
SQL> alter table pdms01.pdms_testdata_3 truncate partition p_history;
SQL> alter table pdms01.pdms_testdata_4 truncate partition p_history;
SQL> alter table pdms01.pdms_testdata_5 truncate partition p_history;

SQL> begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname =>'PDMS01', 
tabname =>'PDMS_TESTDATA_1', 
partname => 'SYS_P2369',
estimate_percent => 0.01,
degree =>4,
granularity=> 'ALL',
NO_INVALIDATE=>false);
end;
/

SQL> begin
     DBMS_STATS.GATHER_TABLE_STATS (
     ownname =>'PDMS01',
     tabname =>'PDMS_TESTDATA_2',
     partname => 'SYS_P2362',
     estimate_percent => 0.01,
     degree =>4,
     granularity=> 'ALL',
     NO_INVALIDATE=>false);
     end;
     /

PL/SQL procedure successfully completed.

SQL> begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname =>'PDMS01', 
tabname =>'PDMS_TESTDATA_3', 
partname => 'SYS_P2358',
estimate_percent => 0.01,
degree =>4,
granularity=> 'ALL',
NO_INVALIDATE=>false);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname =>'PDMS01', 
tabname =>'PDMS_TESTDATA_4', 
partname => 'SYS_P2366',
estimate_percent => 0.01,
degree =>4,
granularity=> 'ALL',
NO_INVALIDATE=>false);
end;
/


SQL> begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname =>'PDMS01', 
tabname =>'PDMS_TESTDATA_5', 
partname => 'SYS_P2367',
estimate_percent => 0.01,
degree =>4,
granularity=> 'ALL',
NO_INVALIDATE=>false);
end;
/