Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Reports in Status.SQL

Oracle Tips by Burleson

The reports in STATUS.SQL fall into three general categories: Internals Monitoring, Space Utilization Monitoring, and Object Status Monitoring. In the subsections that follow, the output from each of the included scripts is covered in detail, to identify what to look for in each report.


This report explains the different scripts contained in the status.sql grouping and how to interpret their results.

The status.sql script calls the scripts listed in Table 13.2 The purpose of these scripts is also listed in the table.

Table 13.2 Scripts in Status.sql




Calls the DBA_UTILITIES.just_statistics procedure to calculate several database statistics.


 Reports library cache statistics relating to pins, hits, reloads.


Reports on freespace and fragmentation status for all tablespaces.


User provides number of extents and script shows objects with >= that number of extents.


Shows first set of redo log storage and usage statistics.


Shows second set of redo log storage and usage statistics.


Summarizes rollback segment health.


Shows redo log statistics.


Shows stored objects that have been marked invalid.


Shows tables stored in the SYSTEM tablespace that do not belong to SYS schema.


Runs several shared-pool health reports.



Reports on SQL usage sorted by CPU usage

I have grouped these scripts in a single wrapper because they give a good representation of what your database status is for a given point in time. If you find other scripts that you wish to add to this list, please feel free to do so; however, if you do, I suggest that the script be renamed to prevent it from being overwritten during upgrades.

The reports run by the CHECK_POOL.SQL script have the functions shown in Table 13.3.

Table 13.3 Scripts Called from CHECK_POOL.SQL




Gives current used size, max size, and percent used for shared pool,


Shows for each user the total amount of reusable and nonreusable code.


Gives the amount of reusable code by user.

The following subsections examine the various output reports from these scripts and explain what the various statistics indicate.


The first script called by status.sql, do_calst2.sql, runs the DBA_UTILITIES.running_stats procedure, which calculates numerous useful database-level statistics and places them in a temporary file called DBA_TEMP. The do_calst2.sql script next generates a report based on the contents of the DBA_TEMP table. The results of a do_cals2.sql report are shown in Listing 13.9 (I already covered its meanings in the tuning contention section.)


The next report is generated by the libcache.sql script. An example of the output from the libcache.sql script is shown in Listing 13.20. As its name implies, the libcache.sql report generates statistics on the library cache contents. Other scripts I provide to look at the library area are o_cache.sql, o_cache2.sql, o_kept.sql, and obj_stat.sql. The output from the libcache.sql script is shown in Listing 13.20.

LISTING 13.20 Output from libcache.sql script.

 Date: 11/07/01                                         Page:   1
Time: 03:37 PM        Library Caches Report            DBAUTIL 
                         aultdb1 database                    

Library Object    Gets Get Hit%   Pins Pin Hit%  Reloads Invalid
--------------- ------ -------- ------ -------- -------- -------
SQL AREA        22,585    95.29 76,547    97.79       22     365
TABLE/PROCEDURE 13,423    88.24 10,756    74.09       12       0
BODY                30    40.00     42    23.81        0       0
TRIGGER             26    15.38     45     8.89        0       0
INDEX              355     1.13    355     1.13        0       0
CLUSTER            429    98.14    431    97.45        0       0
OBJECT               0   100.00      0   100.00        0       0
PIPE                 0   100.00      0   100.00        0       0
JAVA SOURCE          0   100.00      0   100.00        0       0
JAVA RESOURCE        2    50.00      2    50.00        0       0
JAVA DATA            1      .00      4    50.00        0       0

The report is based on the v$library view. Essentially, you want the GET HIT% and PIN HIT% values to be as high as possible, preferably greater than 95 percent. The RELOADS value should be as low as possible, as should the invalidations indicated by the INVALID column. Note that if automated flushing is used, or if the size of the shared pool is reduced to counter thrashing, the RELOADS value for the SQL AREA may increase, but this is normal under these conditions. To decrease RELOADS in systems where reusable SQL forms a major portion of SQL areas, use the DBMS_SHARED_POOL package to pin packages (and cursors and triggers in later than version 7.3) and increase the shared-pool size. Pinning objects prevents their being aged. Also consider using the SHARED_POOL_RESERVED_SIZE and SHARED_POOL_RESERVED_SIZE_MIN_ALLOC parameters to reserve space for large packages.


The next report shows database tablespace size, freespace, and fragmentation status. The report name is free2_spc.sql, and it uses the FREE_SPACE view that is a summary of the DBA_FREE_SPACE master view; an example output is shown in Listing 13.21.  The Free Space report shows: number of files, number of fragments, maximum free megabytes, biggest contiguous chunk of freespace in megabytes, the percentage of available freespace the largest chunk occupies, the total megabytes in the tablespace, the total amount of used megabytes in the tablespace, and the percentage of the total megabytes that is free.

If the number of fragments exceeds the number of files (an example is the TEMP tablespace shown in the Listing 13.21), then you should consider an export/import from the users in that tablespace to consolidate the freespace into a contiguous chunk. Consolidation should definitely be considered when the biggest contiguous chunk is not a major percentage of the total freespace; an example would be the TEMP tablespace in Listing 13.21.  You should be able to glance at the freespace report and tell if there are problems. The total used meg should be trended for your major tablespaces so growth rate can be predicted and allowed for. I provide several freespace reports showing various cuts on the freespace data. Additional information on datafiles can be generated using datafile.sql; tablespace-specific information can be obtained by using tbsp_rct.sql or tablesp.sql scripts.

LISTING 13.21 Example freespace report.

Date: 11/07/01                                                        Page:   1
Time: 06:15 PM                 FREE SPACE REPORT                        DBAUTIL 
                                aultdb1 database                                


                               Biggest Percent of   Total    Total Percent Free
Name     FILES  Frag Free Meg Free Meg   Free Meg     Meg Used Meg    Total Meg
-------- -----  ---- -------- -------- ---------- ------- -------- ------------
DRSYS        1     1    15.43    15.43   100.000   20.000     4.57        77.15
INDX         1     1    19.99    19.99 
  100.000               .01         99.96
PERFSTAT     1     1   389.81   389.81   100.000  500.000   110.19        77.96
RBS          1    13    41.99    15.50    36.912   70.000    28.01        59.99
SYSTEM       1     6    12.33     7.42    60.203  344.000   331.67         3.58
TEMP         1  1465    91.80      .30      .332   93.750     1.95        97.93
TOOLS        1     1      .22      .22   100.000   45.625    45.41          .48
USERS        1     1    13.12    13.12   100.000   20.000     6.88        65.59


The next report is generated by the extents_chk.sql report and shows, for a specified number of extents, the database tables and indexes that exceed this number of extents. The user against which the extent report is run can also be specified. In a single-owner environment, running the report against only that user can save time. In this report, you are concerned with the total number of extents. If they get to be more than 1,000 or so, or more than you have used to partition the table or index, I suggest you consolidate the objects involved (Oracle suggests 4,000 but that seems a bit heavy to me). I provide several scripts to monitor tables, indexes, and clusters: tab_col.sql, tab_grnt.sql, tab_rct.sql, table.sql, tab_stat.sql, in_rep.sql, in_rct.sql, in_size.sql, in_stat.sql, brown.sql, clu_typ.sql, clus_rep.sql, and act_size.sql. An example extents report is shown in Listing 13.22.

LISTING 13.22 Example extents report.

Date: 11/07/01                                             Page: 1
Time: 06:25 PM              EXTENTS REPORT                 DBAUTIL
                           aultdb1 database

Tablespace Segment    Extents Max Extents        Size Owner  Type
---------- ---------- ------- ----------- ----------- ------ -----
SYSTEM     I_SOURCE1      108  2147483645  28,065,792 SYS    INDEX
           IDL_UB1$       903  2147483645 118,243,328        TABLE
           IDL_UB2$        79  2147483645  10,240,000        TABLE
           SOURCE$        282  2147483645  73,678,848        TABLE

Rollback Segment Report rbk3_sql

The rollback segment report breaks out important information from  the DBA_ROLLBACK_SEGS view. The vital stats on the report are the optimal size (OPTL SIZE), shrinks, average shrinks, and average transaction size. You are looking to see that shrinks are minimized and that the optimal size is greater than the average transaction size. Usually, it is suggested that the initial and next extents be sized for the average of the average transaction sizes, and that optimal is set to the max transaction size (also shown on the report). If you are getting shrinks, it indicates that the transaction sizes are exceeding the extent sizes, meaning, perhaps, that the initial and next sizes need to be better thought out. Additional reports include: rbk1.sql, rbk2.sql, rbk_rct.sql, rbk_wait.sql, tx_rbs.sql, and rollback.sql.

The rollback segment report shows transactional statistics for all online rollback segments as well. The statistics reported show if there are any current transactions, the largest transaction size, the current rollback segment size, and the number of waits, wraps, and extents.

If the number of waits is excessive, increase the number of extents available by increasing optimal size or by adding rollback segments. If the number of wraps is excessive, increase the size of the initial and next extents; and, finally, if the number of extends is excessive, either increase the size of initial and next extents or increase the value for optimal size . The optimal size should be at or a multiple of the max transaction size.

An example of the report is shown in Listing 13.23.

LISTING 13.23 Rollback health report.

Date: 11/07/01                                                        Page: 1
Time: 06:35 PM             ROLLBACK SEGMENT HEALTH                    DBAUTIL
                              aultdb1 database

                       OPTL             AVE     AVE LARGEST
---------- -------- ------- ------- ------- ------- ------- ----- ----- ------
RBS        RBS0     4194304       0       0  142081 4186112     0     3       0
RBS        RBS1     4194304       0       0  141262 4186112     0     3       0
RBS        RBS2     4194304       0       0  142081 4186112     0     3       0
RBS        RBS3     4194304       0       0  179637 4186112     0     4       0
RBS        RBS4     4194304       0       0  141417 4186112     0     3       0
RBS        RBS5     4194304       0       0  179704 4186112     0     4       0
RBS        RBS6    4194304        0       0  180301 4186112     0     4       0
SYSTEM     SYSTEM                 0       0       0  401408     0     0       0
                            ------- ------- ------- ------- ----- ----- -------avg                               0       0  138310 3713024     0     3       0


The next report is generated by the log_stat.sql script, and it shows current redo log status. An example of the output from the log_status.sql script is shown in Listing 13.24. The report shows one line per redo log group and a status line for each. The status should be one of inactive, current, or stale; any other status needs to be investigated. This report should be monitored to ensure the logs are all a standard size. This report also shows if the redo logs have been archived and gives the system change number (SCN) and date when the log was switched. Check that redo log switches aren’t happening too frequently or too slowly. Additional reports include: log_hist.sql, log_file.sql, and redo.sql.

LISTING 13.24 Redo log status report.

Date: 11/07/01                                              Page: 1
Time: 06:40 PM            Current Redo Log Status           DBAUTIL
                             aultdb1 database 

Th# Grp#  Seq#   BYTES Mem Arc? STATUS      Change# Time
--- ---- ----- ------- --- ---- --------- --------- -----------------
  1    1 1,426 1048576   1 NO   CURRENT   2,441,716 07-nov-2001 16:47
       2 1,424 1048576   1 NO   INACTIVE  2,441,492 07-nov-2001 16:31
       3 1,425 1048576   1 NO   INACTIVE  2,441,618 07-nov-2001 16:44 


The next report is generated by the inv_obj.sql script. Sample output from it is shown in Listing 13.25. The invalid objects report shows invalid database objects. Ideally, all objects in a production database should be valid and this report shouldn’t appear. However, sometimes packages, procedures, functions, or views can become invalid and may need to be recompiled. The com_proc.sql procedure can facilitate the recompilation effort; or you can use the Oracle-provided dbms_utility.compile_schema() procedure. There is also the utlrp.sql script, which will recompile all invalid PL/SQL objects in the database.

LISTING 13.25 Invalid objects report.                                                                 

Date: 11/07/01                                   Page:   1
Time: 06:46 PM       Invalid Database Objects      DBAUTIL
                         aultdb1 database

Object  Object           Object       Last Change
Owner   Name             Type         Date
------- ---------------- ------------ --------------------
SYS     DBMS_JOB         PACKAGE BODY 15-OCT-01 10:37:48
SYS     DBMS_STATS       PACKAGE BODY 15-OCT-01 10:38:36
ORDSYS  ORDTEXP          PACKAGE BODY 15-OCT-01 12:04:21
ORDSYS  ORDTGET          PACKAGE BODY 15-OCT-01 12:04:45
MDSYS   MDGEN            PACKAGE BODY 15-OCT-01 12:03:29
MDSYS   MDLEXR           PACKAGE BODY 15-OCT-01 12:04:09
MDSYS   RTREE_IDX        PACKAGE BODY 15-OCT-01 12:04:04
MDSYS   SDO_3GL          PACKAGE BODY 15-OCT-01 12:03:51
MDSYS   SDO_GEOM         PACKAGE BODY 15-OCT-01 12:03:56
CTXSYS  DRIDDL           PACKAGE BODY 15-OCT-01 12:04:06
CTXSYS  DRIDDLR          PACKAGE BODY 15-OCT-01 12:05:04
DBAUTIL SMP_VDG          PACKAGE      07-NOV-01 03:30:38
DBAUTIL SMP_VDG          PACKAGE BODY 07-NOV-01 03:30:38
DBAUTIL SMP_VDI          PACKAGE BODY 07-NOV-01 03:30:50
DBAUTIL SMP_VDJ          PACKAGE BODY 07-NOV-01 03:31:19


The next report is generated by the systabs.sql script; it shows non-SYSTEM-owned tables that reside in the SYSTEM tablespace.  Listing 13.26 shows an example of the output from systabs.sql. SYS should own all objects in SYSTEM; all other objects should be located in other tablespaces. Sometimes, SYSTEM-owned objects are allowed in SYSTEM tablespace; however, it is best to add a TOOLS tablespace and create all of SYSTEM-owned objects there. There can be problems exporting items in the SYSTEM tablespace. In addition, all objects in the SYSTEM tablespace use the SYSTEM rollback segment, which can result in fragmentation of the SYSTEM tablespace. Never allow users to have SYSTEM as either a default or temporary tablespace, and never create users without specifying their default and temporary tablespace assignments.

LISTING 13.26     Example SYSTEM tables report.

Date: 11/07/01                                Page:   1
Time: 06:43 PM Non-SYS Owned Tables in SYSTEM DBAUTIL
                        aultdb1 database

OWNER           TABLE_NAME                     TABLESPACE
--------------- ------------------------------ ----------
OUTLN           OL$                            SYSTEM
OUTLN           OL$HINTS                       SYSTEM
SYSTEM          AQ$_QUEUE_TABLES               SYSTEM
SYSTEM          AQ$_QUEUES                     SYSTEM
SYSTEM          AQ$_SCHEDULES                  SYSTEM
SYSTEM          DEF$_AQCALL                    SYSTEM
SYSTEM          DEF$_AQERROR                   SYSTEM
SYSTEM          DEF$_ERROR                     SYSTEM
SYSTEM          DEF$_DESTINATION               SYSTEM
SYSTEM          DEF$_CALLDEST                  SYSTEM
SYSTEM          DEF$_DEFAULTDEST               SYSTEM
SYSTEM          DEF$_LOB                       SYSTEM
SYSTEM          DEF$_TEMP$LOB                  SYSTEM
SYSTEM          DEF$_PROPAGATOR                SYSTEM
SYSTEM          DEF$_ORIGIN                    SYSTEM
SYSTEM          REPCAT$_REPCAT                 SYSTEM
SYSTEM          REPCAT$_FLAVORS                SYSTEM


The Test_pool.sql report is the first from the CHECK_POOL.SQL calling script. It gives a summary of shared-pool usage. I discussed all of the reports run by the CHECK_POOL.SQL script in the section on shared-pool tuning.


The CPU.SQL script generates a report of the top CPU-using SQL statements. This report should be used to find and correct problem SQL. Listing 13.27 shows an example CPU report. (Note: This can only be used on Oracle or later database versions.)

LISTING 13.27 Example SQL by CPU report.

Date: 11/07/01                                                          Page: 1
Time: 06:59 PM                 SQL By CPU Usage                         DBAUTIL
                               aultdb1 database

SQL                                         CPU Elapsed  Disk  Buffer      Rows
Text                                       Time    Time Reads    Gets Processed
--------------------------------------- ------- ------- ----- ------- ---------
select 1 nopr, a.users users,           2483571 4396000  2080  309258        10
garbage, to_char(b.good,'9,999,999,999')
'9,999,999.999') good_percent from
sql_garbage a, sql_garbage b where
a.users=b.users and a.garbage is not
null and b.good is not null union select
2 nopr, '-------------'
garbage,'--------------' good,
'--------------' good_percent from dual
union select 3 nopr,
to_char(count(a.users)) users,
good_percent from sql_garbage a,
sql_garbage b where a.users=b.users and
a.garbage is not null and b.good is not
null order by 1,3 desc
SELECT                                  1191714 1583000  5106  792136        11
))*100 from sql_garbage a, sql_garbage b
where a.users=b.users and a.garbage is
not null and b.good is not null

Further DBA Reading

The DBA may find these references of interest when planning to do Oracle internals tuning activities:

Adams, Steve. Oracle8i Internal Services for Waits, Latches, Locks, and Memory. Sebastopol, CA: O'Reilly and Associates Inc., 1999.

Alomari, Ahmed. Oracle8i & UNIX Performance Tuning. Upper Saddle River, NJ: Prentice-Hall, 2001.

Burleson, Donald K. Oracle High-Performance Tuning with STATSPACK. Berkeley, CA: Osborne/McGraw-Hill, 2001.

Feuerstein, Steven, and Charles Dye and John Beresniewicz. Oracle Built-in Packages. Sebastopol, CA: O’Reilly and Associates, Inc., 1998.

Oracle Administrator’s Guide, Release 1 9.0.1, Part No. A90117-01, Oracle Corporation, June 2001.

Oracle Concepts, Release 1 9.0.1, Part No. A88856-02, Oracle Corporation, June 2001.

Oracle Database Performance Guide and Reference, Release 1 9.0.1, Part No. A87503-02, June 2001, Oracle Corporation, June 2001.

Oracle Database Performance Methods, Release 1 9.0.1, Part No. A87504-01, Oracle Corporation, June 2001.

Oracle Reference, Release 1, 9.0.1, Part No. A90190-01, , Oracle Corporation, June 2001.

Oracle SQL Reference, Release 8.1.5, Part No. A90125-01, Oracle Corporation, June 2001.

Oracle Supplied PL/SQL Packages and Types Reference, Release 1, (9.0.1), Part No. A89852-02, Oracle Corporation, June 2001.

PL/SQL User’s Guide and Reference, Release 1 9.0.1, Part No. A89856-01, Oracle Corporation, June 2001.


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.

Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter