col_usage$字典基表现象

oracle培训根据Metalink Note:

Database Shutdown Immediate Takes Forever, Can Only Do Shutdown Abort [ID 332177.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.4.0

This problem can occur on any platform.

Symptoms

 

The database is not shutting down for a considerable time when you issue the command :

shutdown immediate

 

To shut it down in a reasonable time you have to issue the command

shutdown abort

 

To collect some diagnostics before issuing the shutdown immediate command set a trace event as follows:

 

Connect as SYS (/ as sysdba)

 

SQL> alter session set events '10046 trace name context forever,level 12';

 

SQL> shutdown immediate;

 

In the resultant trace file (within the udump directory) you see something similar to the following :-

 

PARSING IN CURSOR #n

delete from sys.col_usage$ c where not exists   (select 1 from sys.obj$ o where o.obj# = c.obj# )

 

...followed by loads of.....

 

WAIT #2: nam='db file sequential read' ela= 23424 p1=1 p2=4073 p3=1

....

WAIT #2: nam='db file scattered read' ela= 1558 p1=1 p2=44161 p3=8

 

etc

 

Then eventually

 

WAIT #2: nam='log file sync' ela= 32535 p1=4111 p2=0 p3=0

 

...some other SQL....then back to

 

WAIT #2: nam='db file sequential read' ela= 205 p1=1 p2=107925 p3=1

WAIT #2: nam='db file sequential read' ela= 1212 p1=1 p2=107926 p3=1

WAIT #2: nam='db file sequential read' ela= 212 p1=1 p2=107927 p3=1

WAIT #2: nam='db file scattered read' ela= 1861 p1=1 p2=102625 p3=8

etc....

 

To verify which objects are involved here you can use a couple of the P1 & P2 values from above

:-

 

a) a sequential read

SELECT owner,segment_name,segment_type

FROM dba_extents

WHERE file_id=1

AND 107927 BETWEEN block_id AND block_id + blocks

 

b) a scattered read

SELECT owner,segment_name,segment_type

FROM dba_extents

WHERE file_id=1

AND 102625 BETWEEN block_id AND block_id + blocks

 

The output confirms that the objects are

 

SYS.I_COL_USAGE$  (INDEX)   and   SYS.COL_USAGE$ (TABLE)

 

Finally, issue select count(*) from sys.col_usage$;

 

Cause

 

If the number of entries in sys.col_usage$ is large then you are very probably hitting the issue raised in

 

Bug: 3540022 9.2.0.4.0 RDBMS Base Bug 3221945

Abstract: CLEAN-UP OF ENTRIES IN COL_USAGE$

 

Base Bug 3221945 9.2.0.3 RDBMS

Abstract: ORA-1631 ON COL_USAGE$

 

Closed as "Not a Bug"

 

However, when a table is dropped, the column usage statistics are not dropped. They are left as they are.

When the database is shutdown (in normal mode), then these "orphaned" column usage entries are deleted. The code

which does this gets called only during normal shutdown.

 

Unless and until the database is shutdown, the col_usage$ table will continue to grow.

Solution

To implement the workaround, please execute the following steps.