 |
|
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.
Status.sql
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
|
Script |
Purpose |
|
do_cals2.sql |
Calls the DBA_UTILITIES.just_statistics
procedure to calculate several database statistics. |
|
libcache.sql |
Reports library cache statistics relating
to pins, hits, reloads. |
|
free2_spc.sql |
Reports on freespace and fragmentation
status for all tablespaces. |
|
extents.sql |
User provides number of extents and script
shows objects with >= that number of extents. |
|
Rbk1.sql |
Shows first set of redo log storage and
usage statistics. |
|
Rbk2.sql |
Shows second set of redo log storage and
usage statistics. |
|
Rbk3.sql |
Summarizes rollback segment health. |
|
Log_stat.sql |
Shows redo log statistics. |
|
Inv_obj.sql |
Shows stored objects that have been marked
invalid. |
|
Systabs.sql |
Shows tables stored in the SYSTEM
tablespace that do not belong to SYS schema. |
|
Check_pool.sql |
Runs several shared-pool health reports. |
|
Cpu.sql |
|
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
|
Script |
Purpose |
|
Test_pool.sql |
Gives current used size, max size, and
percent used for shared pool, |
|
Sql_garbage.sql |
Shows for each user the total amount of
reusable and nonreusable code. |
|
Reuse_sql2.sql |
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.
Do_cals2.sql
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.)
Libcache.sql
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.
Free2_spc.sql
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
Extents_chk.sql
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
TABLESPACE ROLLBACK SIZE SHRINKS SHRINK TRANS TRANS WAITS
WRAPS EXTENDS
---------- -------- ------- ------- ------- ------- ------- -----
----- ------
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
Log_stat.sql
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
First
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
Inv_obj.sql
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_AQADM_SYS PACKAGE BODY 15-OCT-01 10:41:12
SYS DBMS_IREFRESH PACKAGE BODY 15-OCT-01 10:43:15
SYS DBMS_JOB PACKAGE BODY 15-OCT-01 10:37:48
SYS DBMS_PCLXUTIL PACKAGE BODY 15-OCT-01 10:37:22
SYS DBMS_PRVTAQIP PACKAGE BODY 15-OCT-01 10:39:58
SYS DBMS_SNAPSHOT PACKAGE BODY 15-OCT-01 10:57:17
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
ORDSYS TIMESCALE PACKAGE BODY 15-OCT-01 12:04:48
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_CATALOG PACKAGE BODY 15-OCT-01 12:03:59
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
Systabs.sql
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 DEF$_PUSHED_TRANSACTIONS SYSTEM
SYSTEM SQLPLUS_PRODUCT_PROFILE SYSTEM
SYSTEM REPCAT$_REPCAT SYSTEM
SYSTEM REPCAT$_FLAVORS SYSTEM
Test_pool.sql
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.
CPU.SQL
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
to_char(a.garbage,'9,999,999,999')
garbage, to_char(b.good,'9,999,999,999')
good,
to_char((b.good/(b.good+a.garbage))*100,
'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, '-------------'
users,'--------------'
garbage,'--------------' good,
'--------------' good_percent from dual
union select 3 nopr,
to_char(count(a.users)) users,
to_char(sum(a.garbage),'9,999,999,999')
garbage,
to_char(sum(b.good),'9,999,999,999')
good,to_char(((sum(b.good)/(sum(b.good)+sum(a
.garbage)))*100),'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 order by 1,3 desc
SELECT 1191714 1583000 5106
792136 11
(sum(b.good)/(sum(b.good)+sum(a.garbage)
))*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. |
|
