| |
 |
|
Monitoring Table
Statistics
Oracle Tips by Burleson Consulting
|
Under Oracle7 and Oracle8, the Remote DBA_TABLES view
has several additional columns that are populated once a table has
been analyzed using the ANALYZE TABLE command. The columns document
table-specific data such as number of rows, number of allocated
blocks, number of empty blocks, average percentage of free space in a
table, number of chained rows, and average row length. In Oracle8i,
the view gained the avg_space_freelist_blocks and num_freelist_blocks
columns. In Oracle9i, the dependencies column was added. This provides
the Remote DBA with a more detailed view of the tables in the database than
ever before. This also shows the need for a new report to document
this data in hard-copy format so a Remote DBA can easily track a table’s
growth, space usage, and chaining. The sample script in Source 10.5
shows such a report. Example output from the report is shown in
Listing 10.4.
SOURCE 10.5 Script to report additional table
statistics.
rem
rem NAME: tab_stat.sql
rem
rem FUNCTION: Show table statistics for user's tables or
all tables.
rem 10/08/01 Updated for 9i Mike Ault
rem
SET PAGES 56 LINES 132 NEWPAGE 0 VERIFY OFF ECHO OFF feedback OFF
rem
COLUMN owner
FORMAT a12
HEADING "Table Owner"
COLUMN table_name FORMAT a20
HEADING "Table"
COLUMN tablespace_name FORMAT a20
HEADING "Tablespace"
COLUMN num_rows FORMAT
999,999,999 HEADING "Rows"
COLUMN blocks
FORMAT 999,999 HEADING
"Blocks"
COLUMN empty_blocks FORMAT 999,999
HEADING "Empties"
COLUMN space_full FORMAT 999.99
HEADING "% Full"
COLUMN chain_cnt FORMAT
999,999 HEADING "Chains"
COLUMN avg_row_len FORMAT 99,999,999
HEADING "Avg Length|(Bytes)"
rem
START title132 "Table Statistics Report"
DEFINE OUTPUT = 'rep_out\&db\tab_stat..lis'
SPOOL &output
rem
BREAK ON OWNER SKIP 2 ON TABLESPACE_NAME SKIP 1;
SELECT owner, table_name, tablespace_name, num_rows, blocks,
empty_blocks,
100*((num_rows *
avg_row_len)/((GREATEST(blocks,1)+empty_blocks)*value))
space_full,
chain_cnt, avg_row_len
FROM Remote DBA_tables, v$parameter
WHERE OWNER NOT IN ('SYS','SYSTEM')
and num_rows>0
and name='db_block_size'
ORDER BY owner, tablespace_name;
SPOOL OFF
PAUSE Press enter to continue
SET PAGES 22 LINES 80 NEWPAGE 1 VERIFY ON feedback ON
CLEAR COLUMNS
CLEAR BREAKS
TTITLE OFF
LISTING 10.4 Example of output of report in
Source 10.5.
Date:
10/07/01
Page:
Time: 11:29 PM
Table Statistics Report
Remote DBAUTIL
galinux1 database
Avg Length
Table Owner
Table Tablespace Rows
Blocks Empties % Full Chains
(Bytes)
----------------------------------------------------------------------------------------Remote DBAUTIL
Remote DBA_TEMP
Remote DBAUTIL_DATA 50
1 23 .740
29
HR REGIONS
EXAMPLE 4
1 6
.120 17
LOCATIONS 23
1 0 13.760
49
DEPARTMENTS
27 1
0 6.590 20
EMPLOYEES
107 2
0 44.410 68
JOB_HISTORY
10 1
0 3.780 31
JOBS
19 1
0 7.650
If indicated by the actual space report, or if
the report shown in Source 10.5 shows improper space utilization or
excessive chaining, the table(s) involved should be rebuilt. One
oddity to notice in the above report is that the Empties columns for
many of the HR schema tables show 0 as a value and yet the used space
calculations (% Full) show there should be some empty blocks. I
analyzed the REGIONS table and, viola! They appeared. Oracle has
apparently used the DBMS_STATS package to load statistics in to the
data dictionary for their example tables. The selection of the
db_block_size will only work in 9i if the tablespace in which the
table resides is using the default blocksize. If you will be using the
new capability of Oracle9i to use multiple blocksizes in the same
database, change the query to use the block_size column in the
Remote DBA_TABLESPACES view instead. Note: I have left this script generic so
it can be used with older releases of Oracle.
One method of rebuilding a table is as
follows:
1. Using a SQL script,
unload the table into a flat file.
2. Drop the table and
re-create it with a more representative storage clause.
3. Use SQLLOADER to reload
the table data.
A second method is:
1. Using the CREATE TABLE. .
.AS SELECT. . .FROM command, build a second table that is a mirror
image of the first table (SELECT * FROM first table) with a storage
clause that specifies a larger initial extent.
2. Delete the first table.
3. Use the RENAME command to
rename the second table with the first table’s name.
Of course, the easiest method (since late
version 7.3) is to use the Oracle ALTER TABLE…MOVE command without
specifying a new tablespace (unless you actually want to move the
table to a new tablespace); this will allow the Remote DBA to re-create the
table in place, changing the desired storage characteristics.
Monitoring Table Types: Partition, Nested,
IOT, External
With Oracle8, the new type of tables caused
numerous new columns to be added to the Remote DBA_TABLES view. These rows
tell a Remote DBA whether a table is nested or partitioned, whether or not a
table is an index-only or overflow table, as well as its logging
status. A simple report like that shown in Source 10.6 provides a
convenient format for managing this data.
SOURCE 10.6 Example of script to document
extended table parameters.
REM
REM Name:
tab_rep.sql
REM FUNCTION: Document table extended
parameters
REM Use:
From SQLPLUS
REM MRA 6/13/97 Created for ORACLE8
REM MRA 5/08/99 Updated for ORACLE8i
REM MRA 10/08/01 Updated for Oracle9i
REM
COLUMN owner
FORMAT a10 HEADING 'Owner'
COLUMN table_name FORMAT a15
HEADING 'Table'
COLUMN tablespace_name FORMAT a13 HEADING 'Tablespace'
COLUMN table_type_owner FORMAT a10 HEADING 'Type|Owner'
COLUMN table_type FORMAT a13
HEADING 'Type'
COLUMN iot_name FORMAT
a10 HEADING 'IOT|Overflow'
COLUMN iot_type FORMAT
a12 HEADING 'IOT or|Overflow'
COLUMN nested
FORMAT a6 HEADING 'Nested'
COLUMN extern
FORMAT a3 HEADING 'Ext'
UNDEF owner
SET LINES 130 VERIFY OFF feedback OFF PAGES 58
START title132 'Extended Table Report'
SPOOL rep_out\&&db\ext_tab.lis
SELECT
owner,
table_name,
tablespace_name,
iot_name,
logging,
partitioned,
iot_type,
'N/A' table_type_owner,
'N/A' table_type,
DECODE(temporary,'N','No',temporary),
nested,
'N/A' extern
FROM
Remote DBA_tables
WHERE
owner LIKE UPPER('%&&owner%')
UNION
SELECT
owner,
table_name,
tablespace_name,
iot_name,
logging,
partitioned,
iot_type,
table_type_owner,
table_type,
DECODE(temporary,'N','No',temporary),
nested,
'N/A' extern
FROM
Remote DBA_object_tables
WHERE
owner LIKE UPPER('%&&owner%')
UNION
SELECT
Owner,
'None' tablespace_name,
'N/A' Iot_name,
'N/A' logging,
'N/A' partitioned,
'N/A' Iot_type,
type_owner table_type_owner,
type_name table_type,
'N/A' temporary,
'N/A' nested,
'Yes' extern
FROM
Remote DBA_external_tables
WHERE
Owner LIKE UPPER('%&&owner%');
SPOOL OFF
SET VERIFY ON LINES 80 PAGES 22 feedback ON
TTITLE OFF
UNDEF OWNER
CLEAR COLUMNS
The output from the report on extended table
parameters is shown in Listing 10.5. This is about the only place you
will find documentation on index-only tables, unless you go back to
the XX$ table level. Notice the UNION command: for Oracle8, the
first half can be removed and the table name changed to Remote DBA_tables.
The Remote DBA_OBJECT_TABLES view was added and the type-related columns
placed there in Oracle8i.
LISTING 10.5 Example of output from the
extended table parameters report.
Date: 10/08/01
Page: 1
Time: 12:06 AM
Extended Table Report
Remote DBAUTIL
galinux1 database
IOT IOT or Type
Owner Table
Tablespace Ovf LOG PAR Ovf
Owner Type
Tmp Nest Ext
---------- --------------- ------------- ---- --- --- ------ ------
------------- --- ---- ---
Remote DBAUTIL Remote DBAUTIL_KEPT_OB Remote DBAUTIL_DATA
YES NO N/A
N/A No NO N/A
Remote DBAUTIL Remote DBAUTIL_UPD_TAB Remote DBAUTIL_DATA
YES NO N/A
N/A No
NO N/A
Remote DBAUTIL Remote DBA_RUNNING_STA Remote DBAUTIL_DATA
YES NO N/A
N/A No
NO N/A
Remote DBAUTIL Remote DBA_TEMP
Remote DBAUTIL_DATA YES NO
N/A N/A
No NO N/A
Remote DBAUTIL HIT_RATIOS
Remote DBAUTIL_DATA YES NO
N/A N/A
No NO N/A
Remote DBAUTIL SQL_SCRIPTS None
N/A N/A N/A N/A SYS
ORACLE_LOADER N/A N/A Yes
Remote DBAUTIL SQL_SCRIPTS SYSTEM
YES NO N/A
N/A No
NO N/A
Remote DBAUTIL SQL_SCRIPTS2 None
N/A N/A N/A N/A SYS
ORACLE_LOADER N/A N/A Yes
Remote DBAUTIL SQL_SCRIPTS2 SYSTEM
YES NO N/A
N/A No
NO N/A
Remote DBAUTIL STAT_TEMP
Remote DBAUTIL_DATA YES NO
N/A N/A
No NO N/A
Remote DBAUTIL TEMP_SIZE_TABLE Remote DBAUTIL_DATA
YES NO N/A
N/A No
NO N/A
The report output in Listing 10.5 shows the following information for
Oracle8, Oracle8i, and oracle9i tables. (Note: For Oracle8 and
Oracle8i tables, you will have to remove sections that don't have
counterparts to Oracle9i’s new features, such as the UNION to
Remote DBA_EXTERNAL_TABLES.)
Owner. The owner of the table.
Table. The table name.
Tablespace. The tablespace name.
IOT Overflow. Gives the name of the IOT
tables overflow table.
LOG. Does this table use redo logging?
PAR. Is this table partitioned?
IOT or Overflow. Is this table an IOT
or overflow table?
Type Owner. The owner of the type used
to build this table.
Type. The main type used to build this
table.
Tmp. Is this a temporary table?
Nest. Is this a nested table store
table?
Ext. Is this an external table (9i
only)?
Note that there are entries for the external
tables stored in both the Remote DBA_TABLES and Remote DBA_EXTERNAL_TABLES views; in
the Remote DBA_TABLES views, the tablespace for all external tables is
SYSTEM, and the initial extent, next extent, and pctincrease will be
null.
If a table has been analyzed, then its
relative statistics will be shown in the Remote DBA_TABLES or
Remote DBA_OBJECT_TABLES views. Source 10.7 shows an example of a report for
monitoring the statistics of analyzed tables. The Remote DBA should pay
attention to the statistics gathered by the analyzer since they are
used by the optimizer to tune the queries when Choose mode is set. If
rule-based optimization is being used, there should be no statistics
present for the tables in the application. The output from the
tab_stat.sql report is shown in Listing 10.6.
SOURCE 10.7 Example of script to report table
statistics.
rem
rem NAME: tab_stat.sql
rem
rem FUNCTION:Show table statistics for a user's tables or all
tables.
rem
set pages 56 lines 130 newpage 0 verify off echo off feedback off
rem
COLUMN owner
FORMAT a12
HEADING "Table Owner"
COLUMN table_name FORMAT a17
HEADING "Table"
COLUMN tablespace_name FORMAT a13
HEADING "Tablespace"
COLUMN num_rows FORMAT 99,999,999
HEADING "Rows"
COLUMN blocks
FORMAT 99,999 HEADING
"Blocks"
COLUMN empty_blocks FORMAT 99,999
HEADING "Empties"
COLUMN space_full FORMAT 999.99
HEADING "% Full"
COLUMN chain_cnt FORMAT 99,999
HEADING "Chains"
COLUMN avg_row_len FORMAT 9,999,999
HEADING "Avg|Length|(Bytes)"
COLUMN num_freelist_blocks FORMAT 99,999 HEADING "Num|Freelist|Blocks"
COLUMN avg_space_freelist_blocks FORMAT 99,999 HEADING "Avg|Space|Freelist
Blocks"
rem
START title132 "Table Statistics Report"
DEFINE OUTPUT = 'rep_out\&db\tab_stat..lis'
SPOOL &output
rem
BREAK ON OWNER SKIP 2 ON TABLESPACE_NAME SKIP 1;
SELECT
owner, table_name, tablespace_name,
num_rows, blocks,empty_blocks,
100*((num_rows * avg_row_len)/((GREATEST(blocks,1) + empty_blocks)
* 2048)) space_full,
chain_cnt, avg_row_len,avg_space_freelist_blocks,
num_freelist_blocks
FROM
Remote DBA_tables
WHERE
owner NOT IN ('SYS','SYSTEM')
UNION
SELECT
owner, table_name, tablespace_name,
num_rows, blocks,empty_blocks,
100*((num_rows * avg_row_len)/((GREATEST(blocks,1) + empty_blocks)
* 2048)) space_full,
chain_cnt, avg_row_len,avg_space_freelist_blocks,
num_freelist_blocks
FROM
Remote DBA_object_tables
WHERE
owner NOT IN ('SYS','SYSTEM')
ORDER BY
owner, tablespace_name;
SPOOL OFF
PAUSE Press enter to continue
SET PAGES 22 LINES 80 NEWPAGE 1 VERIFY ON feedback ON
CLEAR COLUMNS
CLEAR BREAKS
TTITLE OFF
LISTING 10.6 Example of table statistics report.
Date: 05/09/99
Page: 1
Time: 02:12 PM
Table Statistics Report
SYS
ORTEST1
database
Avg
Avg
Num
Length Space
FL
Table Owner Table
Tablespace Rows Blocks Empties % Full
Chains (Bytes) FL Blocks Blocks
------------ ----------------- --------------- ---- ------ -------
------ ------ ------- ----------- -----GRAPHICS_Remote DBA BASIC_LOB_TABLE
GRAPHICS_DATA 0
0 259 .00
0 0
0 0
GRAPHICS_TABLE
32 1 258
.31 0 52
2,276 1
INTERNAL_GRAPHICS
32 2 257
.93 1 154
2,212 1
MIGRATE FET$
SYSTEM 5,482
55 0 175.21
0 36 3,768
13
TS$
24 55 0
2.22 0 104
3,768 13
OUTLN OL$
SYSTEM
4 1
1 16.80 0
172 3,308 1
OL$HINTS
175 5
0 90.58 0
53 3,168 3
TELE_Remote DBA ADDRESSESV8i
GRAPHICS_DATA 0
0 4 .00
0 0
0 0
CIRCUITSV8i
0 0
4 .00 0
0 0 0
CLIENTSV8i
0 0 5,119
.00 0
0 0 0
CONTRACTSV8i
0 0
4 .00 0
0 0 0
DEPT
1 1
3 .52 0
43 3,959 1
EMP
1 1
3 .40 0
33 3,971 1
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
