 |
|
Monitoring Table Columns
Oracle Tips by Burleson Consulting |
To round out the general table reports, we
need a report on the columns within a table. The script in Source 10.8
fulfills this need.
SOURCE 10.8 Script to report table columns by
owner and table.
rem
rem tab_col.sql
rem
rem FUNCTION: Report on Table and View Column Definitions
rem
rem MRA 9/18/96
rem MRA 6/14/97 Added table level selectivity
rem
COLUMN owner FORMAT a10 HEADING Owner
COLUMN table_name FORMAT a30 HEADING "Table or View Name"
COLUMN COLUMN_name FORMAT a32 HEADING "Table or
View|Attribute"
COLUMN data_type FORMAT a15 HEADING "Data|Type"
COLUMN data_type_owner FORMAT a13 HEADING "Type|Owner"
COLUMN data_length HEADING Length
COLUMN nullable FORMAT a5 HEADING Null
BREAK ON owner ON table_name SKIP 1
SET LINES 132 PAGES 48 FEEDBACK OFF VERIFY OFF
START title132 "Table Columns Report"
SPOOL rep_out/&db/tab_col
SELECT
a.owner,
table_name||' '||object_type table_name,
column_name,
data_type,
data_type_owner,
data_length,
DECODE(nullable,'N','NO','YES') nullable
FROM
dba_tab_columns a, dba_objects b
WHERE
a.owner=UPPER('&owner') AND
a.owner=b.owner AND
a.table_name LIKE UPPER('%&table%') AND
a.table_name=b.object_name AND
object_type IN ('TABLE','VIEW','CLUSTER')
ORDER BY
owner,
object_type,
table_name,
column_id
/
SPOOL OFF
TTITLE OFF
SET LINES 80 PAGES 22 FEEDBACK ON VERIFY ON
The script in Source 10.8 allows you to specify for a specific owner
the table for which you want to see thecolumns. If a naming convention
that includes prefix or suffix designations is used when naming
tables, then the prefix or suffix can be specified to pull the values
for a specific type of table. The output from the script is shown in
Listing 10.7.
LISTING 10.7 Example of the output from the
table column report.
Enter value for
owner: tele_dba
Enter value for table: CLIENTS_INFO_NUMBERSV8i
Date:
05/09/99
Page: 1
Time: 11:43 AM Table Columns
Report SYS
ORTEST1 database
Table or View
Data Type
Owner Table or View Name Attribute
Type Owner Length Null
-------- ---------------------------- --------------------
--------------- -------- ------ ---
TELE_DBA CLIENTS_INFO_NUMBERSV8i TABLE CLIENTS_INFO_NMBRS_ID
NUMBER 22 NO
CLIENTS_ID_R CLIENT_T
TELE_DBA 50 YES
LISTED_NAME
VARCHAR2 100 YES
EARNING_NUMBER
CHAR 13 YES
SERVICE_CLASS
VARCHAR2 5 YES
NO_OF_LINES
NUMBER 22 YES
DISCONNECT_DATE
DATE 7 YES
DISCONNECT_REASON
CHAR 2 YES
BILLING_NAME
VARCHAR2 40 YES
PHONE
VARCHAR2 10 YES
BTN
CHAR 13 YES
OLD_CLIENTS_NUMBER
CHAR 13 YES
SERVICE_ADDRESS
VARCHAR2 100 YES
CON_CTRL_NUMBER
CHAR 15 YES
TERM_AGREEMENT
CHAR 13 YES
SHARED_TENANT_SVCS
VARCHAR2 10 YES
INSTALLATION_DATE
DATE 7 YES
CONTRACTS
CONTRACT_LIST TELE_DBA 16 YES
CIRCUITS
CIRCUIT_LIST TELE_DBA 16 YES
Monitoring Table Column Statistics
Oracle has provided column-level statistics in
the DBA_TAB_COLUMNS view. Several of the statistics, such as average
length, number of null values, and so on, are useful to the DBA. These
table column statistics aren’t populated unless the table is analyzed.
Source 10.9 shows a report script for these table column statistics.
The output from the script in Source 10.9 is shown in Listing 10.8.
SOURCE 10.9 Example of table column
statistics.
rem
rem tab_col_stat.sql
rem
rem FUNCTION: Report on Table and View Column Definitions
rem
rem MRA 9/18/96
rem MRA 6/14/97 Added table level selectivity
rem MRA 5/8/99 Converted to do stats
rem
COLUMN owner FORMAT a12 HEADING Owner
COLUMN table_name FORMAT a20 HEADING "Table Name"
COLUMN COLUMN_name FORMAT a13 HEADING "Table|Attribute"
COLUMN data_type FORMAT a10 HEADING "Data|Type"
COLUMN avg_col_len FORMAT 99,999 HEADING "Aver|Length"
COLUMN density FORMAT 9.9999 HEADING "Density"
COLUMN last_analyzed Heading "Analyzed"
COLUMN num_distinct HEADING "Distinct|Values"
COLUMN num_nulls HEADING "Num.|Nulls"
COLUMN sample_size HEADING "Sample|Size"
BREAK ON owner ON table_name SKIP 1
SET LINES 132 PAGES 48 FEEDBACK OFF VERIFY OFF
START title132 "Table Column Stats Report"
SPOOL rep_out/&db/tab_col
SELECT
owner,table_name,column_name,data_type,
num_distinct,density,num_nulls,
TO_CHAR(last_analyzed,'dd-mon-yyyy hh24:mi') last_analyzed,
sample_size, avg_col_len
FROM
dba_tab_columns
WHERE
owner LIKE UPPER('%&owner%')
and table_name LIKE UPPER('%&tabname%')
/
SPOOL OFF
TTITLE OFF
SET LINES 80 PAGES 22 FEEDBACK ON VERIFY ON
LISTING 10.8 Example of output from the table
column statistics report.
Enter value for
owner: graphics_dba
Enter value for tabname:
Date: 05/09/99
Page: 1
Time: 01:39
PM Table Column Stats Report
SYS
ORTEST1 database
Table Data Distinct
Num.
Sample Aver
Owner Table Name Attribute Type Values Density
Nulls
Analyzed Size Length
------------
----------------- ------------- -------- ------ -------- -----
----------------- ------- -------
GRAPHICS_DBA
BASIC_LOB_TABLE X VARCHAR2 0 .0000 0
09-may-1999 10:44 0
B BLOB
C CLOB
GRAPHICS_TABLE BFILE_ID NUMBER 32 .0313 0
09-may-1999 10:44 32 2
BFILE_DESC VARCHAR2 0
.0313 32 09-may-1999 10:44 0
BFILE_LOC BFILE 30
.0333 0 09-may-1999 10:44 32 40
BFILE_TYPE VARCHAR2 2
.5000 0 09-may-1999 10:44 32 1
INTERNAL_GRAPHICS GRAPHIC_ID NUMBER 32 .0313 0
09-may-1999 10:45 32 2
GRAPHIC_DESC VARCHAR2 31
.0323 0 09-may-1999 10:45 32 18
GRAPHIC_BLOB
BLOB
GRAPHIC_TYPE VARCHAR2 2
.5000 0 09-may-1999 10:45 32 1
Monitoring Table Keys
Per the requirements of Third Normal Form,
each table is required to have a unique identifier that consists of
one or more of the table’s columns. As an alternative, a derived key
can be used that consists of a number pulled from an Oracle sequence,
but this method should be used only if the key would be excessively
long (over three columns). This is called the primary key of the
table, and it should be identified using a constraint clause when the
table is created. A second type of key, called a foreign key, is also
present in most tables. The foreign key is used to enforce
relationships between two or more tables. The foreign key consists of
the primary key from the related table. The foreign key, too, should
be identified by a constraint clause when the table is created.
If the two types of keys have been identified
via the constraint clause during table creation, they can be readily
monitored via the PK_FK_RPT.SQL script, which is available on the
Wiley Web site. For an example of this script’s output, see Listing
10.9.
LISTING 10.9 Example of output of the
primary/foreign key report.
Date:
05/09/99
Page: 1
Time: 03:13
PM Primary Key - Foreign Key
Report SYSTEM
ORTEST1
database
Pri Table Pri
Table Pri Key For Table For Table For
Key
Owner
Name COLUMNs Owner Name
COLUMNs
---------- ----------------- ----------------- ----------
-------------------- -----------------TELE_DBA BBS_DUNS_PROFILE
SITE_ID TELE_DBA BBS_FRANCHISE_CODES SITE_ID
SITE_ID
BBS_SIC_CODES SITE_ID
BBS_EARNINGS_INFO EARNING_NO_DOCID TELE_DBA
BBS_AUDIT_RECORD EARNING_NO_DOCID
EARNING_NO_DOCID
BBS_CIRCUIT_ID_INFO EARNING_NO_DOCID
EARNING_NO_DOCID STI_ADDRESS
EARNING_NO_DOCID
CUSTOMER_SITES SITE_ID TELE_DBA
BBS_DUNS_PROFILE SITE_ID
SITE_ID
BBS_EARNINGS_INFO SITE_ID
TEMP_USER DEPT DEPTNO TEMP_USER EMP
DEPTNO
Monitoring Tables for Chained Rows
Row chaining occurs as data is added to an
existing record. When there is insufficient room for the addition, the
row is chained to another block and added there. Block chaining occurs
when a row is too long to fit into a single block (such as with long
raw or LOB columns or when a row has more than 255 columns). If
chaining is occurring regularly, it can lead to significant
performance degradation. This degradation is caused by the requirement
to read multiple blocks to retrieve a single record. An example of a
script to monitor a single table for chained rows is shown in Source
10.10. Note that this script is limited, in that the table must have a
primary or unique key defined in order for it to work. With a
companion script, all tables in an application can be checked with
this script. As an alternative, you can analyze the tables of concern
and use the table statistics report, as described in the following
subsection.
This is an excerpt from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
|
|
|
