DBARoad：我的DBA之路

重建OCR与Voting Disk

OoNiceDream — Thu, 10 May 2012 11:29:11 +0800

环境：AIX 5.3 RAC 10.2.0.4
问题描述：由于存储故障，OCR及Voting Disk都需要重建，而且重新划出来的OCR及Voting Disk名称与原来不同。

以下是重建步骤，除DD外操作在两个节点上都要执行：
1、修改/etc/oracle/ocr.loc

#cat /etc/oracle/ocr.loc
ocrconfig_loc=/dev/rvg_ua01_ocr_1
ocrmirrorconfig_loc=/dev/rvg_ua02_ocr2_2
local_only=FALSE

2、修改$CRS_HOME/install/paramfile.crs

CRS_OCR_LOCATIONS=/dev/rvg_ua01_ocr_1,/dev/rvg_ua02_ocr2_2
CRS_VOTING_DISKS=/dev/rvg_ua01_vot_1,/dev/rvg_ua02_vot2_2,/dev/rvg_ua02_vot3_2

3、修改$CRS_HOME/install/rootconfig

CRS_OCR_LOCATIONS=/dev/rvg_ua01_ocr_1,/dev/rvg_ua02_ocr2_2
CRS_VOTING_DISKS=/dev/rvg_ua01_vot_1,/dev/rvg_ua02_vot2_2,/dev/rvg_ua02_vot3_2

4、删除/etc/oracle/scls_scr/<节点名>/oracle/cssfatal文件

5、DD 清除OCR及VOTE

6、运行root.sh
这里我把$CRS_HOME及$ORACLE_HOME下的root.sh都运行了。

以上是OCR及Voting Disk名称变了的情况，如果OCR及Voting Disk名称未变，可以参考：
How to Recreate OCR/Voting Disk Accidentally Deleted [ID 399482.1]

本文参考：

http://space.itpub.net/13177610/viewspace-682268

— The End —

Oracle OS Block Header

OoNiceDream — Tue, 17 Apr 2012 18:52:24 +0800

oracle文件的第一个块（block 0）是OS block header，在数据库中查询不到信息，记录的是OS信息，以及文件大小的等信息：

SQL> select file_name,bytes from dba_data_files;
 
FILE_NAME                                               BYTES
-------------------------------------------------- ----------
/u01/app/oracle/oradata/PROD/user01.dbf              67108864
 
$ls -lrt
total 1390268
-rw-r-----  1 oracle oinstall  67117056 Apr 12 09:31 user01.dbf

从上面可以看出，OS上的大小比数据库里的大小多了一个BLOCK。

如果OS block header损坏，并不影响数据库打开、使用，但重建控制文件时会报错，用dbverify/rman也检测不到坏块，不过可以使用dbfsize来查看：
正常状态：

$dbfsize user01.dbf
 
Database file: user01.dbf
Database file type: file system
Database file size: 8192 8192 byte blocks

损坏：

$dbfsize user01.dbf
user01.dbf: Header block magic number is bad

编缉BLOCK 0，模拟损坏，可以正常启动、使用：

SQL> startup;
ORACLE instance started.
 
Total System Global Area  184549376 bytes
Fixed Size                  1266488 bytes
Variable Size             100666568 bytes
Database Buffers           79691776 bytes
Redo Buffers                2924544 bytes
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/PROD/user01.dbf'
 
 
SQL> recover datafile 4;  
Media recovery complete.
SQL> alter database open;
 
Database altered.
 
SQL> create table test01 tablespace USERS as select * from dba_objects;
 
Table created.

用dbv检查，未发现坏块：

$dbv file=user01.dbf
 
DBVERIFY: Release 10.2.0.4.0 - Production on Mon Apr 16 16:38:33 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
DBVERIFY - Verification starting : FILE = user01.dbf
 
 
DBVERIFY - Verification complete
 
Total Pages Examined         : 8192
Total Pages Processed (Data) : 357
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 11
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 7824
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 336969 (0.336969)

用dbfsize检查，报错：

$dbfsize user01.dbf
user01.dbf: Header block magic number is bad

如果重建控制文件，则会报错：

SQL> startup nomount;
ORACLE instance started.
 
Total System Global Area  184549376 bytes
Fixed Size                  1266488 bytes
Variable Size             100666568 bytes
Database Buffers           79691776 bytes
Redo Buffers                2924544 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 5
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 2
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 (
  9      '/u01/app/oracle/oradata/PROD/REDO1_1.log',
 10      '/u01/app/oracle/oradata/PROD/REDO1_2.log',
 11      '/u01/app/oracle/oradata/PROD/REDO1_3.log'
 12    ) SIZE 100M,
 13    GROUP 2 (
 14      '/u01/app/oracle/oradata/PROD/REDO2_1.log',
 15      '/u01/app/oracle/oradata/PROD/REDO2_2.log',
 16      '/u01/app/oracle/oradata/PROD/REDO2_3.log'
 17    ) SIZE 100M
 18  -- STANDBY LOGFILE
 19  DATAFILE
 20    '/u01/app/oracle/oradata/PROD/SYSTEM01.dbf',
 21    '/u01/app/oracle/oradata/PROD/undotbs01.dbf',
 22    '/u01/app/oracle/oradata/PROD/SYSAUX01.dbf',
 23    '/u01/app/oracle/oradata/PROD/user01.dbf'
 24  CHARACTER SET AL32UTF8
 25  ;
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/u01/app/oracle/oradata/PROD/user01.dbf'
ORA-27047: unable to read the header block of file
Additional information: 2

报ORA-27047错误。这里可以在数据库打开状态下，resize datafile，这样就可以重写OS block header信息：

SQL> alter database open;
 
Database altered.
 
SQL> alter database datafile '/u01/app/oracle/oradata/PROD/user01.dbf' resize 65M;
 
Database altered.
 
SQL> select file_name,bytes from dba_data_files;
 
FILE_NAME                                               BYTES
-------------------------------------------------- ----------
/u01/app/oracle/oradata/PROD/user01.dbf              68157440

dbfsize检查正常，重建控制文件正常：

$dbfsize user01.dbf
 
Database file: user01.dbf
Database file type: file system
Database file size: 8320 8192 byte blocks
 
SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 5
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 2
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 (
  9      '/u01/app/oracle/oradata/PROD/REDO1_1.log',
 10      '/u01/app/oracle/oradata/PROD/REDO1_2.log',
 11      '/u01/app/oracle/oradata/PROD/REDO1_3.log'
 12    ) SIZE 100M,
 13    GROUP 2 (
 14      '/u01/app/oracle/oradata/PROD/REDO2_1.log',
 15      '/u01/app/oracle/oradata/PROD/REDO2_2.log',
 16      '/u01/app/oracle/oradata/PROD/REDO2_3.log'
 17    ) SIZE 100M
 18  -- STANDBY LOGFILE
 19  DATAFILE
 20    '/u01/app/oracle/oradata/PROD/SYSTEM01.dbf',
 21    '/u01/app/oracle/oradata/PROD/undotbs01.dbf',
 22    '/u01/app/oracle/oradata/PROD/SYSAUX01.dbf',
 23    '/u01/app/oracle/oradata/PROD/user01.dbf'
 24  CHARACTER SET AL32UTF8
 25  ;
 
Control file created.
 
SQL> alter database open;
 
Database altered.

注：resize 原大小，重建还是报错，需要resize一个不同的大小。

— The End —

About JI and MS Enqueue

OoNiceDream — Thu, 12 Apr 2012 14:37:29 +0800

某系统突然出现200多个enq: JI – contention、enq: MS – contention等待事件，这两种enqueue比较少见，记录下：

INST_ID   SID SQL_HASH_VALUE EVENT                     P2  P3 TYPE  MODE         CTIME
------- ----- -------------- -------------------- ------- --- ----- ------- ----------
      1  3587              0 enq: JI - contention 1331436   0 JI    6          5
      1  1316              0 enq: JI - contention 1331436   0 JI    6          5
      1   695              0 enq: JI - contention 1331436   0 JI    6          5
      1  2400     4169753003 enq: JI - contention 1331436   0 JI    6          6
      1  1223              0 enq: JI - contention 1331436   0 JI    6          6
      1   699              0 enq: JI - contention 1331436   0 JI    6          6
      1  2128              0 enq: JI - contention 1331436   0 JI    6          6
      1   564              0 enq: JI - contention 1331436   0 JI    6          6
      1  4544              0 enq: JI - contention 1331436   0 JI    6         13
      1  4751              0 enq: JI - contention 1331436   0 JI    6         14
      1   933     3586441291 enq: JI - contention 1331436   0 JI    6         16

JI enqueue is used to serialize the refresh of an materialized view object, JI enqueue is acquired when a materialized view refresh is being performed on an aggregate join view (AJV), is used to ensure that two or more refresh processes do not try to refresh the same object.

If the JI enqueue holder is an intended refresh session, the other session(s) that intend to refresh the same mview may just need to wait until the first one finishes.

INST_ID     SID  SQL_HASH_VALUE EVENT                    P2  P3 TYPE  MODE  CTIME
------- ------- --------------- --------------------- ----- --- ---- ----- ------
      1    5440      3586441291 enq: MS - contention  77334   0 MS       6     23
      1     284      3586441291 enq: MS - contention  77334   0 MS       6     23
      1    3481      3586441291 enq: MS - contention  77334   0 MS       6     23
      1    2572      3586441291 enq: MS - contention  77334   0 MS       6     23
      1    2329      3586441291 enq: MS - contention  77334   0 MS       6     23
      1    2193      3586441291 enq: MS - contention  77334   0 MS       6     23
      1    2123      3586441291 enq: MS - contention  77334   0 MS       6     23
      1     741      3586441291 enq: MS - contention  77334   0 MS       6     23
      1    1826      3586441291 enq: MS - contention  77334   0 MS       6     23
      1    1949      3586441291 enq: MS - contention  77334   0 MS       6     23
      1    1934               0 enq: MS - contention  85067   0 MS       6      6

enq: MS – contention Lock held during materialized view refresh to setup MV log

最终根据P2值找到对应OBJECT，再找到基于这个OBJECT的物化视图，发现有的是新建而且是实时刷新的物化视图。
最后在DROP这些物化视图后，才恢复正常。

— The End —

10G下从ASM复制文件到文件系统

OoNiceDream — Sat, 17 Mar 2012 18:22:19 +0800

版本10.2.0.5
1、使用RMAN：

$ sqlplus "/as sysdba"   
 
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Mar 16 10:02:41 2012
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
Connected to an idle instance.
 
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area 2147483648 bytes
Fixed Size                  2097696 bytes
Variable Size             503320032 bytes
Database Buffers         1627389952 bytes
Redo Buffers               14675968 bytes
Database mounted.
 
SQL> select file#, name, bytes from v$datafile where file#=5;
 
     FILE# NAME                                                    BYTES
---------- -------------------------------------------------- ----------
         5 +DATA01/dbname/datafile/users.264.730552875           524288000
 
$ rman target / nocatalog
 
Recovery Manager: Release 10.2.0.5.0 - Production on Fri Mar 16 10:06:08 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
connected to target database: dbname (DBID=2555001420, not open)
using target database control file instead of recovery catalog
 
RMAN> copy datafile 5 to '/arch1/users.264.730552875';
 
Starting backup at 16-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1624 instance=dbname1 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+DATA01/dbname/datafile/users.264.730552875
output filename=/arch1/users.264.730552875 tag=TAG20120316T100622 recid=2 stamp=778068391
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 16-MAR-12
 
RMAN> exit
 
 
Recovery Manager complete.
$ ls -l /arch1/users.264.730552875
-rw-r----- 1 oracle oinstall 524296192 Mar 16 10:06 /arch1/users.264.730552875

2、使用dbms_file_transfer包：

SQL> select open_mode from v$database;
 
OPEN_MODE
----------
READ WRITE
 
SQL> create or replace directory SOURCE_DIR1 as '+DATA01/dbname/onlinelog/';
 
Directory created.
 
SQL> create or replace directory ORACLE_DEST as '/arch1/';
 
Directory created.
 
SQL> 
SQL> BEGIN
  2  dbms_file_transfer.copy_file(
  3  source_directory_object =>'SOURCE_DIR1', 
  4  source_file_name => 'group_1.274.730565825',
  5  destination_directory_object => 'ORACLE_DEST',
  6  destination_file_name => 'group_1.274.730565825'
  7  );
  8  END;
  9  / 
 
PL/SQL procedure successfully completed.
 
SQL> ! ls -l /arch1/group_1.274.730565825
-rw-r----- 1 oracle oinstall 524288512 Mar 14 10:49 /arch1/group_1.274.730565825

— The End —

ASM下复制多个控制文件

OoNiceDream — Fri, 16 Mar 2012 09:57:35 +0800

默认情况下，使用ASM，创建好数据库后只有一个控制文件，在图形界面上一直没找到在哪改添加合适，还是安装好后再改吧：

版本10.2.0.5
 
SQL> show parameter spfile
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA01/dbname/spfiledbname.ora
SQL> select name from v$controlfile;
 
NAME
--------------------------------------------------------------------------------
+DATA01/dbname/controlfile/current.256.730552789
 
SQL> alter system set control_files='+DATA01/dbname/CONTROLFILE/Current.256.730552789','+DATA01' scope=spfile sid='*';
 
System altered.
 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup nomount;
ORACLE instance started.
 
Total System Global Area 2147483648 bytes
Fixed Size                  2097696 bytes
Variable Size             503320032 bytes
Database Buffers         1627389952 bytes
Redo Buffers               14675968 bytes
 
$ rman target / nocatalog
 
Recovery Manager: Release 10.2.0.5.0 - Production on Wed Mar 14 10:19:48 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
connected to target database: dbname (not mounted)
using target database control file instead of recovery catalog
 
RMAN> restore controlfile from '+DATA01/dbname/CONTROLFILE/Current.256.730552789';
 
Starting restore at 14-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1627 instance=dbname1 devtype=DISK
 
channel ORA_DISK_1: copied control file copy
output filename=+DATA01/dbname/controlfile/current.256.730552789
output filename=+DATA01/dbname/controlfile/current.442.777896405
Finished restore at 14-MAR-12
 
RMAN> 
 
SQL> alter system set control_files='+DATA01/dbname/CONTROLFILE/Current.256.730552789','+DATA01/dbname/controlfile/current.442.777896405' scope=spfile sid='*';
 
System altered.
 
SQL> shutdown immediate;
ORA-01507: database not mounted
 
 
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
 
Total System Global Area 2147483648 bytes
Fixed Size                  2097696 bytes
Variable Size             503320032 bytes
Database Buffers         1627389952 bytes
Redo Buffers               14675968 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;
 
NAME
--------------------------------------------------------------------------------
+DATA01/dbname/controlfile/current.256.730552789
+DATA01/dbname/controlfile/current.442.777896405

— The End —

Using dbms_diskgroup copy file from ASM to FileSystem

OoNiceDream — Thu, 15 Mar 2012 10:17:21 +0800

情况是这样，Oracle版本10.2.0.5，ASM，REDO损坏了，数据库没法打开。在恢复前，想把REDO先备份下，结果10G的ASM到FileSystem，还是不太方便，特别是在数据库没法打开的情况下。如果是OPEN状态，那可以用dbms_file_transfer包。在MOUNT状态下，也可以用RMAN提复制数据文件，但REDO，还是没找到方法。

开了个SR，Oracle Support提供了个方法，用dbms_diskgroup包，测试了下，相当实用，数据文件、日志文件、控制文件，全都能复制到FS上：

$ export ORACLE_SID=+ASM1
$ sqlplus "/as sysdba"
 
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 14 13:42:16 2012
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
 
SQL> declare
  2  v_AsmFileName varchar2(4000);
  3  v_FsFileName varchar2(4000);
  4  v_filetype number;
  5  v_filesize number;
  6  v_lbks number;
  7  v_typename varchar2(4000);
  8  v_pblksize number;
  9  v_handle number;
 10  begin
 11  dbms_output.enable(500000);
 12  v_AsmFileName := '&ASM_file_name';
 13  v_FsFileName := '&FileSystem_file_name';
 14  dbms_diskgroup.getfileattr(v_AsmFileName,v_filetype,v_filesize,v_lbks);
 15  dbms_diskgroup.open(v_AsmFileName,'r',v_filetype,v_lbks,v_handle,v_pblksize,v_filesize);
 16  dbms_diskgroup.close(v_handle);
 17  select decode(v_filetype,1,'Control File',2,'Data File',3,'Online Log File',4,'Archive Log',5,'Trace File',6,'Temporary File',
 18  7,'Not Used',8,'Not Used',9,'Backup Piece',10,'Incremental Backup Piece',11,'Archive Backup Piece',12,'Data File Copy',
 19  13,'Spfile',14,'Disaster Recovery Configuration',15,'Storage Manager Disk',16,'Change Tracking File',17,'Flashback Log File',
 20  18,'DataPump Dump File',19,'Cross Platform Converted File',20,'Autobackup',21,'Any OS file',22,'Block Dump File',
 21  23,'CSS Voting File',24,'CRS') into v_typename from dual;
 22  dbms_output.put_line('File: '||v_AsmFileName); dbms_output.new_line;
 23  dbms_output.put_line('Type: '||v_filetype||' '||v_typename); dbms_output.new_line;
 24  dbms_output.put_line('Size (in logical blocks): '||v_filesize); dbms_output.new_line;
 25  dbms_output.put_line('Logical Block Size: '||v_lbks); dbms_output.new_line;
 26  dbms_output.put_line('Physical Block Size: '||v_pblksize); dbms_output.new_line;
 27  dbms_diskgroup.patchfile(v_AsmFileName,v_filetype,v_lbks,1,0,v_filesize,v_FsFileName,2,1,1);
 28  end;
 29  /
Enter value for asm_file_name: +DATA01/dbname/onlinelog/group_2.275.730565853
old  12: v_AsmFileName := '&ASM_file_name';
new  12: v_AsmFileName := '+DATA01/dbname/onlinelog/group_2.275.730565853';
Enter value for filesystem_file_name: /arch1/group_2.275.730565853
old  13: v_FsFileName := '&FileSystem_file_name';
new  13: v_FsFileName := '/arch1/group_2.275.730565853';
 
PL/SQL procedure successfully completed.
 
SQL> ! ls -l /arch1/group_2.275.730565853
-rw-r----- 1 oracle oinstall 524288512 Mar 14 13:46 /arch1/group_2.275.730565853

关于这个包：
dbms_diskgroupis an Oracle ‘internal package’ (it doesn’t show up as an object being that ASMhas no dictionary) called dbms_diskgroup. It is used to access the ASM withfilesystem-like calls. 11g asmcmd uses this package to implement the cpcommand.

11G里asmcmd也是调用这个包来实现cp复制。

— The End —

SNIPED状态会话处理

OoNiceDream — Wed, 11 Jan 2012 10:08:45 +0800

数据库profile中设置IDLE_TIME后，如果会话超过这个限制，PMON进程会：

 * Mark the V$SESSION as SNIPED
 * Clean up the database resources for the session
 * Remove the V$SESSION entry

在一个9208的数据库中，经常会有SNIPED状态的会话，这些会话不活动了，但却占用了用户的会话数：

SYS@DB2> select sid,serial#,paddr,username,status from v$session where status = 'SNIPED';
 
    SID    SERIAL# PADDR            USERNAME        STATUS
------- ---------- ---------------- --------------- --------
    116       4063 C000000826067820 USER_79        SNIPED
    236      31393 C00000082634BEA0 USER_79        SNIPED
    319      59368 C00000082606D8E0 USER_79        SNIPED
    552      21495 C000000826128AC0 USER_79        SNIPED
    769       3890 C00000082615C680 USER_79        SNIPED
    838      21427 C000000826164220 USER_79        SNIPED
   1009      29340 C000000826087C20 USER_70        SNIPED
   1023      17707 C000000826062CE0 USER_71        SNIPED
   1148       8085 C0000008263A03C0 USER_73        SNIPED
   1360      45212 C00000082621D3C0 USER_79        SNIPED
   1676       4466 C00000082615D140 USER_79        SNIPED
   1959       5966 C00000082639C8A0 USER_72        SNIPED
   2187       7000 C000000826355A80 USER_78        SNIPED
   2251      65047 C0000008263A4440 USER_72        SNIPED
   2462      14367 C00000082634AE80 USER_71        SNIPED
   2561      21989 C0000008262733C0 USER_74        SNIPED
   2639      10214 C0000008263972A0 USER_78        SNIPED
   2683      30762 C000000826071EC0 USER_76        SNIPED
 
18 rows selected.

这里我们看到的现象是v$session中，会话没有被清理。最好的情况是，当用户再次尝试连接该会话时，会收到ORA-02396错误，然后会话被清理。

[/home/oracle]$ oerr ora 2396
02396, 00000, "exceeded maximum idle time, please connect again"
//  *Cause:  as stated
//  *Action:

尝试在sqlnet.ora中设置SQLNET.EXPIRE_TIME，没什么用。

尝试用alter system kill session，v$session中标记为KILLED，v$process、os process都是不清理的。

    SID    SERIAL# PADDR            USERNAME        STATUS
------- ---------- ---------------- --------------- --------
    116       4063 C000000826067820 USER_79        SNIPED
 
SYS@DB2> select pid from v$process where addr='C000000826067820';
 
       PID
----------
        64
 
SYS@DB2> select spid from v$process where addr='C000000826067820';
 
SPID
------------
22907
 
[/home/oracle]$ ps -ef | grep 22907
  oracle 23300 23244  1 11:04:23 pts/ta    0:00 grep 22907
  oracle 22907     1  0 14:23:51 ?         0:03 oracleDB2 (LOCAL=NO)
 
 
SYS@DB2> alter system kill session '116,4063';
 
System altered.
 
SYS@DB2> select sid,serial#,paddr,username,status from v$session where sid=116;
 
       SID    SERIAL# PADDR            USERNAME                       STATUS
---------- ---------- ---------------- ------------------------------ --------
       116       4063 C0000008239C3A88 USER_79                       KILLED
 
SYS@DB2> select spid from v$process where addr='C000000826067820';
 
SPID
------------
22907  
 
[/home/oracle]$ ps -ef | grep 22907
  oracle 22907     1  0 14:23:51 ?         0:03 oracleDB2 (LOCAL=NO)
  oracle 25859 23244  0 11:07:56 pts/ta    0:00 grep 22907

最后，写个脚本，OS上kill -9，会话被清理。

— The End —

使用restore_table_stats还原统计信息

OoNiceDream — Thu, 22 Dec 2011 16:10:17 +0800

10G中可以使用dbms_stats.restore_table_stats恢复表老的统计信息，这功能还是挺不错的。因为统计信息的变化有时会引起某些语句执行计划变差，这时恢复原有统计信息，常常会有效果。当然分析前备份老的统计信息，始终是个好习惯。

查询统计信息保存时间，当然这个时间不一定能保证：

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
                         31

也可以通过execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45) 来修改这个保存时间。

查询统计信息能恢复到哪个时间点：

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
 
GET_STATS_HISTORY_AVAILABILITY
----------------------------------------------------------------------
20-NOV-11 10.08.13.843271000 PM +08:00

小测试：
当前统计信息：

SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T1'
  6     and a.owner = 'SYS';
 
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM     DENSITY LOW_VALUE       HIGH_VALUE      LAST_ANALYZED
---------- ------------ ----------- ---------- ---------- --------------- --------------- ----------------
ID               108046         100 HEIGHT BAL .000015395 C3020104        C30C512D        2011-12-22 14:53
                                    ANCED
 
NAME               7775         100 HEIGHT BAL .001328021 2F3130303065386 73756E2F7574696 2011-12-22 14:53
                                    ANCED                 4315F4C696E6B65 C2F427564646869
                                                          64486173684D617 737443616C656E6
                                                          056616C75654974 46172

恢复到分析之前：

SQL> select to_timestamp ('2011-12-22 14:52','yyyy-mm-dd hh24:mi') from dual;
 
TO_TIMESTAMP('2011-12-2214:52','YYYY-MM-DDHH24:MI')
---------------------------------------------------------------------------
22-DEC-11 02.52.00.000000000 PM
 
SQL> BEGIN
  2  DBMS_STATS.RESTORE_TABLE_STATS(
  3  ownname => 'SYS',
  4  tabname => 'T1',
  5  as_of_timestamp => '22-DEC-11 02.52.00.000000000 PM'
  6  );
  7  END;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T1'
  6     and a.owner = 'SYS';
 
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM     DENSITY LOW_VALUE       HIGH_VALUE      LAST_ANALYZED
---------- ------------ ----------- ---------- ---------- --------------- --------------- ----------------
ID               118045         100 HEIGHT BAL .000013915 C102            C30C512E        2011-12-22 14:45
                                    ANCED
 
NAME              11656         100 HEIGHT BAL  .00104712 2F3130343866633 73756E2F746F6F6 2011-12-22 14:45
                                    ANCED                 9355F5772617054 C732F747265652F
                                                          6F6B656E5772617 5768696C6553746
                                                          0546F6B656E496E 174656D656E74

再次恢复到2011-12-22 14:45之前的统计信息：

SQL> select to_timestamp ('2011-12-22 14:44','yyyy-mm-dd hh24:mi') from dual;
 
TO_TIMESTAMP('2011-12-2214:44','YYYY-MM-DDHH24:MI')
---------------------------------------------------------------------------
22-DEC-11 02.44.00.000000000 PM
 
SQL> BEGIN
  2  DBMS_STATS.RESTORE_TABLE_STATS(
  3  ownname => 'SYS',
  4  tabname => 'T1',
  5  as_of_timestamp => '22-DEC-11 02.44.00.000000000 PM'
  6  );
  7  END;
  8  /
 
PL/SQL procedure successfully completed.
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T1'
  6     and a.owner = 'SYS';
 
COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM     DENSITY LOW_VALUE       HIGH_VALUE      LAST_ANALYZED
---------- ------------ ----------- ---------- ---------- --------------- --------------- ----------------
ID               117773           1 NONE       8.4909E-06 C11B            C30C5108        2011-12-21 16:15
NAME               2617           1 NONE       .000382117 2F3130303562643 73756E2F746F6F6 2011-12-21 16:15
                                                          3305F4C6E6B6443 C732F747265652F
                                                          6F6E7374616E74  4F7245787072657
                                                                          373696F6E

— The End —

PARALLEL_INDEX与索引并发

OoNiceDream — Wed, 21 Dec 2011 17:27:09 +0800

使用PARALLEL_INDEX可以在索引上开并发，先来看看文档中的描述：
The PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans for partitioned indexes.

这里说的是PARALLEL_INDEX可以用在分区索引上开并发，其实还有一种就情况是，当查询走index fast full scan时，也可以开并发。

1、分区索引：

SQL> select index_name, index_type, status, partitioned, degree
  2    from dba_indexes
  3   where table_name = 'T1'
  4     and owner = 'SYS';
 
INDEX_NAME      INDEX_TYPE      STATUS     PARTIT DEGREE
--------------- --------------- ---------- ------ -------
IND_T1_ID       NORMAL          N/A        YES    1
IND_T1_NAME     NORMAL          VALID      NO     1
 
SQL> SELECT /*+ PARALLEL_INDEX(T1, ind_t1_id, 3) */ id from T1 where id between 1 and 3000;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2134138182
 
-------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Pstart| Pstop |    TQ  |IN-OUT| PQ Di
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |  2500 |       |       |        |      |
|   1 |  PX COORDINATOR          |           |       |       |       |        |      |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000  |  2500 |       |       |  Q1,00 | P->S | QC (R
|   3 |    PX PARTITION RANGE ALL|           |  2500 |     1 |     4 |  Q1,00 | PCWC |
|*  4 |     INDEX RANGE SCAN     | IND_T1_ID |  2500 |     1 |     4 |  Q1,00 | PCWP |
-------------------------------------------------------------------------------------------
 
SQL> SELECT /*+ PARALLEL_INDEX(T1, ind_t1_id, 3) */ id from T1;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2841388588
 
--------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Pstart| Pstop |    TQ  |IN-OUT| PQ Dis
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |   936K|       |       |        |      |
|   1 |  PX COORDINATOR         |           |       |       |       |        |      |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000  |   936K|       |       |  Q1,00 | P->S | QC (RA
|   3 |    PX BLOCK ITERATOR    |           |   936K|     1 |     4 |  Q1,00 | PCWC |
|   4 |     INDEX FAST FULL SCAN| IND_T1_ID |   936K|     1 |     4 |  Q1,00 | PCWP |
--------------------------------------------------------------------------------------------

2、非分区索引，需要走index fast full scan才能开并发:

SQL> SELECT /*+ INDEX_FFS(T1,IND_T1_NAME) PARALLEL_INDEX(T1, ind_t1_name, 2) */
  2  count(name) from T1;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1205896683
 
--------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Rows  |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |     1 |        |      |         |
|   1 |  SORT AGGREGATE           |             |     1 |        |      |         |
|   2 |   PX COORDINATOR          |             |       |        |      |         |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000    |     1 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |             |     1 |  Q1,00 | PCWP |         |
|   5 |      PX BLOCK ITERATOR    |             |   936K|  Q1,00 | PCWC |         |
|   6 |       INDEX FAST FULL SCAN| IND_T1_NAME |   936K|  Q1,00 | PCWP |         |
--------------------------------------------------------------------------------------
 
以下T2表为非分区表：
SQL> select /*+ PARALLEL_INDEX(T2, ind_t2_id, 2) */count(id) from T2;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 155381254
 
------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |     1 |        |      |         |
|   1 |  SORT AGGREGATE           |           |     1 |        |      |         |
|   2 |   PX COORDINATOR          |           |       |        |      |         |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000  |     1 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |           |     1 |  Q1,00 | PCWP |         |
|   5 |      PX BLOCK ITERATOR    |           |   949K|  Q1,00 | PCWC |         |
|   6 |       INDEX FAST FULL SCAN| IND_T2_ID |   949K|  Q1,00 | PCWP |         |
------------------------------------------------------------------------------------

另外还找到一个与PARALLEL_INDEX相关的BUG，使HINT无法在DBLINK中使用，该BUG在10.2.0.5中修复：
Bug 6621937 – [NO]PARALLEL_INDEX hint not sent to remote site for SQL over database link [ID 6621937.8]:

A SQL statement containing [NO_]PARALLEL_INDEX hints
which is sent to a remote site (over a database link)
does not send those hints with the remote SQL. This can
affect query performance.
 
eg:
  select /*+ driving_site(a) parallel_index(a,4) */
    a.*,b.* from test_aaa@test_link a,test_bbb b
  where a.col1 = b.col1 and a.col3=5;

— The End —

比较删除柱状图的三种方法

OoNiceDream — Tue, 20 Dec 2011 18:12:38 +0800

比较下常用的三种删除柱状图信息的方法。
测试环境：

SQL> select * from v$version;
 
BANNER
-----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

1、使用dbms_stats.delete_column_stats 册除柱状图信息：
删除前统计信息：

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
  2    FROM DBA_TABLES
  3   WHERE TABLE_NAME = 'T1'
  4     and owner = 'SYS';
 
TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED
----------- ---------- ---------- ----------- ----------------
T1              111757        200           7 2011-12-20 14:54
 
SQL> SELECT INDEX_NAME,NUM_ROWS,DISTINCT_KEYS "DISTINCT",
  2         LEAF_BLOCKS,CLUSTERING_FACTOR "CF",
  3         BLEVEL,AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY",
  4         LAST_ANALYZED
  5    FROM DBA_INDEXES
  6   WHERE table_name = 'T1'
  7     and owner = 'SYS'
  8   ORDER BY INDEX_NAME;
 
INDEX_NAME        NUM_ROWS   DISTINCT LEAF_BLOCKS    CF     BLEVEL   ALFBPKEY LAST_ANALYZED
--------------- ---------- ---------- ----------- ----- ---------- ---------- ----------------
IND_T1_ID           111111          6         249   200          1         41 2011-12-20 14:54
IND_T1_ID_NAME      111111          6         295   249          1         49 2011-12-20 14:54
IND_T1_NAME         111111          5         218   200          1         43 2011-12-20 14:54
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T1'
  6     and a.owner = 'SYS';
 
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM    DENSITY LOW_VALUE  HIGH_VALUE LAST_ANALYZED
----------- ------------ ----------- --------- ---------- ---------- ---------- ----------------
ID                     5           5 FREQUENCY .000004474 C164       C3646464   2011-12-20 14:54
NAME                   4           4 FREQUENCY .000004474 5232       5235       2011-12-20 14:54
 
SQL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
  2    FROM DBA_HISTOGRAMS
  3   WHERE TABLE_NAME = 'T1'
  4     and owner = 'SYS'
  5   ORDER BY COLUMN_NAME, ENDPOINT_NUMBER;
 
COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
ID                         1             99
ID                         5            999
ID                        49           9999
ID                       534          99999
ID                      5532         999999
NAME                       1     4.2678E+35
NAME                       5     4.2680E+35
NAME                      49     4.2682E+35
NAME                    5532     4.2684E+35

这里加一步删除所有数据，验证下删除柱状图后，表及索引的统计信息有没有变更：

SQL> delete from t1;
 
111111 rows deleted.
 
SQL> commit;
 
Commit complete.

删除柱状图后统计信息：

SQL> exec dbms_stats.delete_column_stats('SYS','T1','id');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
  2    FROM DBA_TABLES
  3   WHERE TABLE_NAME = 'T1'
  4     and owner = 'SYS';
 
TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED
----------- ---------- ---------- ----------- ----------------
T1              111757        200           7 2011-12-20 14:54
 
SQL> SELECT INDEX_NAME,NUM_ROWS,DISTINCT_KEYS "DISTINCT",
  2         LEAF_BLOCKS,CLUSTERING_FACTOR "CF",
  3         BLEVEL,AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY",
  4         LAST_ANALYZED
  5    FROM DBA_INDEXES
  6   WHERE table_name = 'T1'
  7     and owner = 'SYS'
  8   ORDER BY INDEX_NAME;
 
INDEX_NAME        NUM_ROWS   DISTINCT LEAF_BLOCKS    CF     BLEVEL   ALFBPKEY LAST_ANALYZED
--------------- ---------- ---------- ----------- ----- ---------- ---------- ----------------
IND_T1_ID           111111          6         249   200          1         41 2011-12-20 14:54
IND_T1_ID_NAME      111111          6         295   249          1         49 2011-12-20 14:54
IND_T1_NAME         111111          5         218   200          1         43 2011-12-20 14:54
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T1'
  6     and a.owner = 'SYS';
 
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM    DENSITY LOW_VALUE  HIGH_VALUE LAST_ANALYZED
----------- ------------ ----------- --------- ---------- ---------- ---------- ----------------
NAME                   4           4 FREQUENCY .000004474 5232       5235       2011-12-20 14:54
 
SQL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
  2    FROM DBA_HISTOGRAMS
  3   WHERE TABLE_NAME = 'T1'
  4     and owner = 'SYS'
  5   ORDER BY COLUMN_NAME, ENDPOINT_NUMBER;
 
COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
NAME                       1     4.2678E+35
NAME                       5     4.2680E+35
NAME                      49     4.2682E+35
NAME                    5532     4.2684E+35

小结：可以看出dbms_stats.delete_column_stats不仅删除了柱状图，且列上的基本统计信息如最大值最小值、唯一值等都删了，这对CBO计算成本还是有影响的，特别是谓词中带有这个条件时。表及索引、其它列上的统计信息，是不受影响的。

2、使用dbms_stats.gather_table_stats 重新收集：
删除前统计信息：

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
  2    FROM DBA_TABLES
  3   WHERE TABLE_NAME = 'T2'
  4     and owner = 'SYS';
 
TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED
----------- ---------- ---------- ----------- ----------------
T2              109636        200           7 2011-12-20 15:49
 
SQL> SELECT INDEX_NAME,NUM_ROWS,DISTINCT_KEYS "DISTINCT",
  2         LEAF_BLOCKS,CLUSTERING_FACTOR "CF",
  3         BLEVEL,AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY",
  4         LAST_ANALYZED
  5    FROM DBA_INDEXES
  6   WHERE table_name = 'T2'
  7     and owner = 'SYS'
  8   ORDER BY INDEX_NAME;
 
INDEX_NAME        NUM_ROWS   DISTINCT LEAF_BLOCKS    CF     BLEVEL   ALFBPKEY LAST_ANALYZED
--------------- ---------- ---------- ----------- ----- ---------- ---------- ----------------
IND_T2_ID           111111          6         384   203          1         64 2011-12-20 15:49
IND_T2_ID_NAME      111111          6         408   427          2         68 2011-12-20 15:49
IND_T2_NAME         111111          5         220   202          1         44 2011-12-20 15:49
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T2'
  6     and a.owner = 'SYS';
 
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM    DENSITY LOW_VALUE  HIGH_VALUE LAST_ANALYZED
----------- ------------ ----------- --------- ---------- ---------- ---------- ----------------
ID                     5           5 FREQUENCY 4.5605E-06 C164       C3646464   2011-12-20 15:49
NAME                   4           4 FREQUENCY 4.5605E-06 5232       5235       2011-12-20 15:49
 
SQL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
  2    FROM DBA_HISTOGRAMS
  3   WHERE TABLE_NAME = 'T2'
  4     and owner = 'SYS'
  5   ORDER BY COLUMN_NAME, ENDPOINT_NUMBER;
 
COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
ID                         1             99
ID                         5            999
ID                        51           9999
ID                       566          99999
ID                      5427         999999
NAME                       1     4.2678E+35
NAME                       5     4.2680E+35
NAME                      51     4.2682E+35
NAME                    5427     4.2684E+35

更改数据，增加唯一值个数，最大值等，观察分析时，会不会对ID、NAME列进行分析：

SQL> begin
  2  for i in 1..1000 loop
  3  insert into t2 values(8888888,'R8');
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL> begin
  2  for i in 1..1000 loop
  3  insert into t2 values(88888888,'R9');
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL> begin
  2  for i in 1..1000 loop
  3  insert into t2 values(999999999,'R10');
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.

删除柱状图后统计信息：

SQL> exec dbms_stats.gather_table_stats('SYS','T2',cascade=>true,method_opt=>'for columns ID size 1');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
  2    FROM DBA_TABLES
  3   WHERE TABLE_NAME = 'T2'
  4     and owner = 'SYS';
 
TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED
----------- ---------- ---------- ----------- ----------------
T2              113094        210           8 2011-12-20 15:57
 
SQL> SELECT INDEX_NAME,NUM_ROWS,DISTINCT_KEYS "DISTINCT",
  2         LEAF_BLOCKS,CLUSTERING_FACTOR "CF",
  3         BLEVEL,AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY",
  4         LAST_ANALYZED
  5    FROM DBA_INDEXES
  6   WHERE table_name = 'T2'
  7     and owner = 'SYS'
  8   ORDER BY INDEX_NAME;
 
INDEX_NAME        NUM_ROWS   DISTINCT LEAF_BLOCKS    CF     BLEVEL   ALFBPKEY LAST_ANALYZED
--------------- ---------- ---------- ----------- ----- ---------- ---------- ----------------
IND_T2_ID           114111          9         391   211          1         43 2011-12-20 15:57
IND_T2_ID_NAME      114111          9         418   439          2         46 2011-12-20 15:57
IND_T2_NAME         114111          8         228   212          1         28 2011-12-20 15:57
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T2'
  6     and a.owner = 'SYS';
 
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM    DENSITY LOW_VALUE  HIGH_VALUE LAST_ANALYZED
----------- ------------ ----------- --------- ---------- ---------- ---------- ----------------
ID                     8           1 NONE            .125 C164       C50A646464 2011-12-20 15:57
                                                                     64
 
NAME                   4           4 FREQUENCY 4.5605E-06 5232       5235       2011-12-20 15:49
 
SQL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
  2    FROM DBA_HISTOGRAMS
  3   WHERE TABLE_NAME = 'T2'
  4     and owner = 'SYS'
  5   ORDER BY COLUMN_NAME, ENDPOINT_NUMBER;
 
COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
ID                         0             99
ID                         1      999999999
NAME                       1     4.2678E+35
NAME                       5     4.2680E+35
NAME                      51     4.2682E+35
NAME                    5427     4.2684E+35

小结：可以看出这种方法，代价较大，重新收集了表和索引上的统计信息，只是收集时，不再收集ID列上的住状图。但这里需要注意的是，其它列上的统计信息，也没有重新收集的，因为method_opt 只指定了ID列。如果是method_opt=>’for all columns size 1′，那么NAME列上的柱状图也将不再收集。

3、使用dbms_stats.set_column_stats 手工删除柱状图：
删除前统计信息：

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
  2    FROM DBA_TABLES
  3   WHERE TABLE_NAME = 'T3'
  4     and owner = 'SYS';
 
TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED
----------- ---------- ---------- ----------- ----------------
T3              109252        200           7 2011-12-20 15:50
 
SQL> SELECT INDEX_NAME,NUM_ROWS,DISTINCT_KEYS "DISTINCT",
  2         LEAF_BLOCKS,CLUSTERING_FACTOR "CF",
  3         BLEVEL,AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY",
  4         LAST_ANALYZED
  5    FROM DBA_INDEXES
  6   WHERE table_name = 'T3'
  7     and owner = 'SYS'
  8   ORDER BY INDEX_NAME;
 
INDEX_NAME        NUM_ROWS   DISTINCT LEAF_BLOCKS    CF     BLEVEL   ALFBPKEY LAST_ANALYZED
--------------- ---------- ---------- ----------- ----- ---------- ---------- ----------------
IND_T3_ID           111111          6         373   203          1         62 2011-12-20 15:50
IND_T3_ID_NAME      111111          6         408   422          2         68 2011-12-20 15:50
IND_T3_NAME         111111          5         219   202          1         43 2011-12-20 15:50
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T3'
  6     and a.owner = 'SYS';
 
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM    DENSITY LOW_VALUE  HIGH_VALUE LAST_ANALYZED
----------- ------------ ----------- --------- ---------- ---------- ---------- ----------------
ID                     4           4 FREQUENCY 4.5766E-06 C20A64     C3646464   2011-12-20 15:50
NAME                   3           3 FREQUENCY 4.5766E-06 5233       5235       2011-12-20 15:50
 
SQL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
  2    FROM DBA_HISTOGRAMS
  3   WHERE TABLE_NAME = 'T3'
  4     and owner = 'SYS'
  5   ORDER BY COLUMN_NAME, ENDPOINT_NUMBER;
 
COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
ID                         5            999
ID                        58           9999
ID                       541          99999
ID                      5408         999999
NAME                       5     4.2680E+35
NAME                      58     4.2682E+35
NAME                    5408     4.2684E+35

更改数据，增加唯一值个数，最大值等，观察分析时，会不会对ID列进行分析：

SQL> begin
  2  for i in 1..1000 loop
  3  insert into t3 values(8888888,'R8');
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL> begin
  2  for i in 1..1000 loop
  3  insert into t3 values(88888888,'R9');
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> begin
  2  for i in 1..1000 loop
  3  insert into t3 values(999999999,'R10');
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.

删除后统计信息:

SQL> exec dbms_stats.set_column_stats('SYS','T3','ID',DISTCNT=>4);
 
PL/SQL procedure successfully completed.
 
SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN, LAST_ANALYZED
  2    FROM DBA_TABLES
  3   WHERE TABLE_NAME = 'T3'
  4     and owner = 'SYS';
 
TABLE_NAME    NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED
----------- ---------- ---------- ----------- ----------------
T3              109252        200           7 2011-12-20 15:50
 
SQL> SELECT INDEX_NAME,NUM_ROWS,DISTINCT_KEYS "DISTINCT",
  2         LEAF_BLOCKS,CLUSTERING_FACTOR "CF",
  3         BLEVEL,AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY",
  4         LAST_ANALYZED
  5    FROM DBA_INDEXES
  6   WHERE table_name = 'T3'
  7     and owner = 'SYS'
  8   ORDER BY INDEX_NAME;
 
INDEX_NAME        NUM_ROWS   DISTINCT LEAF_BLOCKS    CF     BLEVEL   ALFBPKEY LAST_ANALYZED
--------------- ---------- ---------- ----------- ----- ---------- ---------- ----------------
IND_T3_ID           111111          6         373   203          1         62 2011-12-20 15:50
IND_T3_ID_NAME      111111          6         408   422          2         68 2011-12-20 15:50
IND_T3_NAME         111111          5         219   202          1         43 2011-12-20 15:50
 
SQL> SELECT COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,
  2         HISTOGRAM,DENSITY,LOW_VALUE,
  3         HIGH_VALUE,LAST_ANALYZED
  4    FROM DBA_TAB_COL_STATISTICS a
  5   WHERE a.TABLE_NAME = 'T3'
  6     and a.owner = 'SYS';
 
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM    DENSITY LOW_VALUE  HIGH_VALUE LAST_ANALYZED
----------- ------------ ----------- --------- ---------- ---------- ---------- ----------------
ID                     4           1 NONE             .25 C20A64     C3646464   2011-12-20 16:02
NAME                   3           3 FREQUENCY 4.5766E-06 5233       5235       2011-12-20 15:50
 
SQL> SELECT COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE
  2    FROM DBA_HISTOGRAMS
  3   WHERE TABLE_NAME = 'T3'
  4     and owner = 'SYS'
  5   ORDER BY COLUMN_NAME, ENDPOINT_NUMBER;
 
COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE
------------ --------------- --------------
ID                         0            999
ID                         1         999999
NAME                       5     4.2680E+35
NAME                      58     4.2682E+35
NAME                    5408     4.2684E+35

小结：dbms_stats.set_column_stats 只对列进行了更新，而对表、索引，表上其它列信息，均无影响。
通过SQL TRACE观察dbms_stats.set_column_stats操作，发现以下两步：
1、更新列的统计信息：
update hist_head$ set bucket_cnt=:3, row_cnt=:4, cache_cnt=:5,null_cnt=:6, timestamp#=:7, sample_size=:8, minimum=:9, maximum=:10,distcnt=:11, lowval=:12, hival=:13, density=:14, spare1=:15, spare2=:16, avgcln=:17, col#=:18 where obj#=:1 and intcol#=:2
2、删除柱状图：
delete from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3

注意，这里只是更新列的统计信息，并没有对列的统计信息进行重新分析（最大、最小值无变化），虽然列的LAST_ANALYZED更新到了最新。

从上面的测试可以看出，正常情况下，最好还是使用dbms_stats.set_column_stats删除柱状图信息，代价小，副作用小。

— The End —