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
.
·
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
|