V$DB_OBJECT_CACHE shows you that the metadata for a few data dictionary clusters and tables is marked for keeping in the library cache. That does not however affect the caching of their data blocks in the buffer cache in any way. Therefore, the keeping of the library cache metadata does not mitigate the performance impact of block chaining in the data dictionary clusters at all.

The question about keeping tables and clusters is interesting. Despite documentation to the contrary in the dbmspool.sql script, it is in fact possible to keep all manner of things. The following demonstration is from 8.1.6 on NT. First we query X$KGLOB to get the object type, keep mask, address and hash value for all the objects owned by SCOTT. Then we use the address and hash value to keep a table, a non-existent object for public synonym translation, and a subscription, just as though they were cursors. Thereafter V$DB_OBJECT_CACHE shows that they are kept.

        SQL> select kglnaobj, kglobtyp, kglhdkmk, kglhdadr, kglnahsh from x$kglob where kglnaown = 'SCOTT';

        KGLNAOBJ                         KGLOBTYP   KGLHDKMK KGLHDADR   KGLNAHSH
        ------------------------------ ---------- ---------- -------- ----------
        DEPT                                    2          0 0A7A990C 2273037119
        EMP                                     2          0 0A7FDA1C  161107996
        OBJ                                     0          0 0A7B492C 2689422592
        BONUS                                   2          0 0A7A72D4 3447117924
        DUAL                                    0          0 0A7C8A00 1789751130
        SALGRADE                                2          0 0A7A54F0 2716698706
        DBMS_APPLICATION_INFO                   0          0 0A7BA90C 2654501694
        SCOTT                                  39          0 0A7CE3C4 1999087133

        SQL> execute dbms_shared_pool.keep('0A7A990C,2273037119', 'c');

        SQL> execute dbms_shared_pool.keep('0A7B492C,2689422592', 'c');

        SQL> execute dbms_shared_pool.keep('0A7CE3C4,1999087133', 'c');

        SQL> select name, type, kept from v$db_object_cache where owner = 'SCOTT';

        NAME                           TYPE                         KEP
        ------------------------------ ---------------------------- ---
        DEPT                           TABLE                        YES
        EMP                            TABLE                        NO
        OBJ                            NOT LOADED                   YES
        BONUS                          TABLE                        NO
        DUAL                           NOT LOADED                   NO
        SALGRADE                       TABLE                        NO
        DBMS_APPLICATION_INFO          NOT LOADED                   NO
        SCOTT                          PUB_SUB                      YES

I believe that this has in fact been possible since release 7.1.3 if you were careful to workaround the various bugs that returned incorrect hash values (see Hash values don't match).

To check for cluster block chaining in the data dictionary you can temporarily gather statistics on the SYS schema as follows:

        execute dbms_utility.analyze_schema('SYS', 'COMPUTE');
        select cluster_name, avg_blocks_per_key from dba_clusters where owner = 'SYS';
        execute dbms_utility.analyze_schema('SYS', 'DELETE');

First off, thanks for all the valuable info in your tips section. It's appreciated. Next, I have a question about your tip entitled "Planning Extents". In that tip, you make the case against allowing too many extents in a tablespace, which can thus fill the tablespace's free extent block in the FET$ table and cause block chaining in the cluster. This would then lead to poor performance for access of the FET$ table.

I noticed that in my databases, the FET$ and UET$ tables are "kept" when querying V$DB_OBJECT_CACHE. If these tables are pinned or kept in memory, would the chaining still be a performance issue? They are only a few extents. However, I've also read that tables can't be kept, so I'm a little confused.

Also, how would I check for chaining in an existing database? Can I safely analyze data dictionary clusters/tables?