BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter