Tuesday, December 9, 2008

DBA_TAB_STATISTICS

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================




DBA_TAB_STATISTICS displays optimizer statistics for all tables in the database. Its columns are the same as those in ALL_TAB_STATISTICS.

Related Views
· ALL_TAB_STATISTICS displays optimizer statistics for the tables accessible to the current user.
· USER_TAB_STATISTICS displays optimizer statistics for the tables owned by the current user. This view does not display the OWNER column.
Column
Datatype
NULL
Description
OWNER
VARCHAR2(30)
Owner of the object
TABLE_NAME
VARCHAR2(30)
Name of the table
PARTITION_NAME
VARCHAR2(30)
Name of the partition
PARTITION_POSITION
NUMBER
Position of the partition within the table
SUBPARTITION_NAME
VARCHAR2(30)
Name of the subpartition
SUBPARTITION_POSITION
NUMBER
Position of the subpartition within the partition
OBJECT_TYPE
VARCHAR2(12)
Type of the object:
· TABLE
· PARTITION
· SUBPARTITION
NUM_ROWS
NUMBER
Number of rows in the object
BLOCKS
NUMBER
Number of used blocks in the object
EMPTY_BLOCKS
NUMBER
Number of empty blocks in the object
AVG_SPACE
NUMBER
Average available free space in the object
CHAIN_CNT
NUMBER
Number of chained rows in the object
AVG_ROW_LEN
NUMBER
Average row length, including row overhead
AVG_SPACE_FREELIST_BLOCKS
NUMBER
Average freespace of all blocks on a freelist
NUM_FREELIST_BLOCKS
NUMBER
Number of blocks on the freelist
AVG_CACHED_BLOCKS
NUMBER
Average number of blocks in the buffer cache
AVG_CACHE_HIT_RATIO
NUMBER
Average cache hit ratio for the object
SAMPLE_SIZE
NUMBER
Sample size used in analyzing the table
LAST_ANALYZED
DATE
Date of the most recent time the table was analyzed
GLOBAL_STATS
VARCHAR2(3)
Indicates whether statistics were calculated without merging underlying partitions (YES) or not (NO)
USER_STATS
VARCHAR2(3)
Indicates whether statistics were entered directly by the user (YES) or not (NO)
STATTYPE_LOCKED
VARCHAR2(5)
Type of statistics lock:
· DATA
· CACHE
· ALL
STALE_STATS
VARCHAR2(3)
Indicates whether statistics for the object are stale (YES) or not (NO)
1.
select owner, table_name, object_type, num_rows, blocks,
empty_blocks, avg_space, avg_row_len, sample_size,
last_analyzed, stale_stats global_stats
from DBA_TAB_STATISTICS;
OWNER
TABLE_NAME
OBJECT_
TYPE
NUM_
ROWS
BLOCKS
EMPTY_
BLOCKS
AVG_
SPACE
AVG_
ROW_
LEN
SAMPLE_
SIZE
LAST_
ANALYZED
GLOBAL_
STATS
SDE
COLUMN_REGISTRY
TABLE
16431
93
0
0
73
16431
2008-11-05 10:56:34 PM
NO
SDE
DBTUNE
TABLE
1158
11
0
8285
67
1158
2008-02-26 10:03:14 PM
NO
SDE
GEOMETRY_COLUMNS
TABLE
928
9
0
0
92
928
2008-09-22 11:28:47 PM
NO
SDE
LAYERS
TABLE
960
13
0
0
156
960
2008-12-08 11:06:59 PM
NO
SDE
LINEAGES_MODIFIED
TABLE
2
7
0
0
12
2
2008-12-08 10:02:02 PM
NO
SDE
LOCATORS
TABLE
27
1
0
0
64
27
2006-09-23 7:16:23 AM
NO
SDE
METADATA
TABLE
6518
43
0
0
89
6518
2006-09-23 7:16:24 AM
NO
SDE
OBJECT_LOCKS
TABLE
0
7
0
0
0
0
2008-12-08 11:10:54 PM
NO
SDE
PROCESS_INFORMATION
TABLE
467
21
0
0
72
467
2008-12-08 10:03:41 PM
NO
SDE
SDE_TABLES_MODIFIED
TABLE
5
1
0
0
20
5
2008-12-08 10:02:40 PM
NO
SDE
SERVER_CONFIG
TABLE
40
7
0
0
18
40
2008-11-13 10:02:46 PM
NO
SDE
SPASTATS
TABLE
6075
373
0
0
873
6075
2008-07-07 10:05:48 PM
NO
SDE
SPASTATS_SESS
TABLE
3
1
0
0
13
3
2008-12-08 10:02:13 PM
NO
SDE
SPATIAL_REFERENCES
TABLE
31
7
0
0
185
31
2008-11-12 10:59:57 PM
NO
SDE
STATES
TABLE
29
7
504
15724
52
29
2008-12-08 11:07:28 PM
NO
SDE
TABLE_REGISTRY
TABLE
1052
11
0
0
109
1052
2008-11-24 11:02:22 PM
NO
SDE
VERSION
TABLE
1
1
0
0
113
1
2006-09-23 7:20:38 AM
NO
SDE
VERSIONS
TABLE
12
7
0
0
64
12
2008-12-08 10:02:03 PM
NO

Oracle data dictionary views