BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Redneck

DBA_CLUSTERS view tips

Oracle Tips by Burleson Consulting

DBA_CLUSTERS

<< Return to the index

Oracle 11g makes the following comments about the DBA_CLUSTERS table: "Description of all clusters in the database"

Related notes on DBA_CLUSTERS:

Column description of the DBA_CLUSTERS view:

OWNER

Description of DBA_CLUSTERS.OWNER: "Owner of the cluster"

CLUSTER_NAME

Description of DBA_CLUSTERS.CLUSTER_NAME: "Name of the cluster"

TABLESPACE_NAME

Description of DBA_CLUSTERS.TABLESPACE_NAME: "Name of the tablespace containing the cluster"

PCT_FREE

Description of DBA_CLUSTERS.PCT_FREE: "Minimum percentage of free space in a block"

PCT_USED

Description of DBA_CLUSTERS.PCT_USED: "Minimum percentage of used space in a block"

KEY_SIZE

Description of DBA_CLUSTERS.KEY_SIZE: "Estimated size of cluster key plus associated rows"

INI_TRANS

Description of DBA_CLUSTERS.INI_TRANS: "Initial number of transactions"

MAX_TRANS

Description of DBA_CLUSTERS.MAX_TRANS: "Maximum number of transactions"

INITIAL_EXTENT

Description of DBA_CLUSTERS.INITIAL_EXTENT: "Size of the initial extent in bytes"

NEXT_EXTENT

Description of DBA_CLUSTERS.NEXT_EXTENT: "Size of secondary extents in bytes"

MIN_EXTENTS

Description of DBA_CLUSTERS.MIN_EXTENTS: "Minimum number of extents allowed in the segment"

MAX_EXTENTS

Description of DBA_CLUSTERS.MAX_EXTENTS: "Maximum number of extents allowed in the segment"

PCT_INCREASE

Description of DBA_CLUSTERS.PCT_INCREASE: "Percentage increase in extent size"

FREELISTS

Description of DBA_CLUSTERS.FREELISTS: "Number of process freelists allocated in this segment"

FREELIST_GROUPS

Description of DBA_CLUSTERS.FREELIST_GROUPS: "Number of freelist groups allocated in this segment"

AVG_BLOCKS_PER_KEY

Description of DBA_CLUSTERS.AVG_BLOCKS_PER_KEY: "Average number of blocks containing rows with a given cluster key"

CLUSTER_TYPE

Description of DBA_CLUSTERS.CLUSTER_TYPE: "Type of cluster: b-tree index or hash"

FUNCTION

Description of DBA_CLUSTERS.FUNCTION: "If a hash cluster, the hash function"

HASHKEYS

Description of DBA_CLUSTERS.HASHKEYS: "If a hash cluster, the number of hash keys (hash buckets)"

DEGREE

Description of DBA_CLUSTERS.DEGREE: "The number of threads per instance for scanning the cluster"

INSTANCES

Description of DBA_CLUSTERS.INSTANCES: "The number of instances across which the cluster is to be scanned"

CACHE

Description of DBA_CLUSTERS.CACHE: "Whether the cluster is to be cached in the buffer cache"

BUFFER_POOL

Description of DBA_CLUSTERS.BUFFER_POOL: "The default buffer pool to be used for cluster blocks"

SINGLE_TABLE

Description of DBA_CLUSTERS.SINGLE_TABLE: "Whether the cluster can contain only a single table"

DEPENDENCIES

Description of DBA_CLUSTERS.DEPENDENCIES: "Should we keep track of row level dependencies?"

DBA_CLUSTERS View Source

Oracle 11g's data dictionary defines the DBA_CLUSTERS view using the following source query:

select u.name, o.name, ts.name,
mod(c.pctfree$, 100),
decode(bitand(ts.flags, 32), 32, to_number(NULL), c.pctused$),
c.size$,c.initrans,c.maxtrans,
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.lists, 0, 1, s.lists)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.groups, 0, 1, s.groups)),
c.avgchn, decode(c.hashkeys, 0, 'INDEX', 'HASH'),
decode(c.hashkeys, 0, NULL,
decode(c.func, 0, 'COLUMN', 1, 'DEFAULT',
2, 'HASH EXPRESSION', 3, 'DEFAULT2', NULL)),
c.hashkeys,
lpad(decode(c.degree, 32767, 'DEFAULT', nvl(c.degree,1)),10),
lpad(decode(c.instances, 32767, 'DEFAULT', nvl(c.instances,1)),10),
lpad(decode(bitand(c.flags, 8), 8, 'Y', 'N'), 5),
decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
lpad(decode(bitand(c.flags, 65536), 65536, 'Y', 'N'), 5),
decode(bitand(c.flags, 8388608), 8388608, 'ENABLED', 'DISABLED')
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.clu$ c, sys.obj$ o
where o.owner# = u.user#
and o.obj# = c.obj#
and c.ts# = ts.ts#
and c.ts# = s.ts#
and c.file# = s.file#
and c.block# = s.block#

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books