| |
 |
|
Monitoring Database
Tables, Indexes, Clusters, Snapshots, and Objects
Oracle Tips by Burleson
|
Since the days of SQLDBA or nothing (for you
new folks, that was version 6), Oracle has come a long way with its
database monitoring tools. Server Manager, Enterprise Manager, and the
numerous other Oracle-provided monitoring tools make the task of
monitoring databases easier.
That said, be aware that many of these tools
still only monitor the very top level of the data dictionary, to
answer questions such as how many users, how many tables, and how many
indexes; they do little in the way of in-depth monitoring (to answer
questions such as what is user X doing, when will table Y extend
next).
Note: Server Manager (SVRMGR), which
was heavily used in Oracle7, 8, and 8i, is no longer available in 9i.
Several companies offer excellent monitoring
tools: Precise, with its Q and Precise*SQL packages; the Quest product
suite; Patrol from BMC; ECO Tools; and the many CA offerings spring to
mind. All of these tools, however, while freeing some DBAs, end up
crippling others because the DBAs never learn the underlying
structures of the data dictionary. I am a firm believer in not
providing complex, feature-rich tools to inexperienced DBAs, because
they need to understand the data dictionary first. The point is, tools
only go so far, and a DBA has to be able to dig into the data
dictionary to find and correct problems. If you believe great tools
make great works, try giving the best materials and the best tools to
ineffective or inexperienced workers: the result may be better than
with poor materials and tools, but it will probably still be
unsatisfactory. As with all things, when using Oracle, knowledge means
power: the power to solve problems or, better yet, to prevent them
from happening in the first place.
In the past few years, since the publication
of my first three books, I have interviewed dozens of candidates for
Oracle DBA and developer positions. Some had great resumes, but when I
began asking about the data dictionary and V$ tables, really getting
under the hood of Oracle, I found many were clueless. To prevent
anyone saying that about you, I refer you to Appendices D and E; they
should become your map, guide, and bible to the Oracle data dictionary
and support tables. Also, refer to those appendices (available on the
Wiley Web site) throughout this chapter for definitions of the various
views and tables used by the monitoring scripts. I have attempted to
provide a “road map” of the views that relate to a specific topic
(such as table or indexes). Use this map and The reference manual on
the techent.oracle.com web page to see how the data dictionary views
are laid out.
The data dictionary tables (which are usually
suffixed with a dollar sign ($), though there are exceptions)
are owned by the SYS user and normally shouldn’t be accessed except
when the supporting views don’t have the required data. These are
documented in Appendix D. Instead, Oracle has provided DBA_, USER_,
and ALL_ views into these tables, which should be used whenever
possible; the DBA_ views are documented in Appendix E. In addition,
Oracle has provided the dynamic performance tables (DPTs), which
provide running statistics and internals information. These DPTs all
begin with V$ or, in their pristine form, V_$. The DPTs are also
documented in Reference manual..
The data dictionary views are created by the
catalog.sql script, located in the $ORACLE_HOME/rdbms/admin directory.
The catalog.sql script is a required readme file. The script has
numerous remarks and comments that will improve your understanding of
the data dictionary views severalfold. I warn you, though, the
catalog.sql script is over 200 pages long.
Using the Oracle8, Oracle8i, and Oracle
Data Dictionary
These tables, views, and DPTs provide detailed
information about the system, the data dictionary, and the processes
for the Oracle database. Reports can access the data dictionary
objects to give the DBA just about any cut on the data he or she
desires. Many, if not all, of the reports given in the following
sections utilize these objects either directly or indirectly via the
$tables, V$ DPTs, or DBA_ views.
The data dictionary objects can also be
queried interactively during a DBA user session to find the current
status of virtually any system parameter. The use of dynamic SQL
against the DBA_ views can shorten a DBA task, such as switching users
from one temporary tablespace to another or dropping a set of tables,
by a factor of 10 or more.
In the days of SQLDBA, it was often easier to
monitor with scripts than with the Oracle-provided monitor screens.
The SVRMGR product, with its GUI interface, was an improvement, as was
the Enterprise Manager. However, these still don’t provide the
flexibility available from user-generated scripts. The Performance
Pack can be customized to add user-generated monitoring queries.
However, the Performance Pack is a cost add-on (and a performance
cost) to the system DBA tools.
However, all of the above lack adequate report
capabilities. To remedy this shortfall, DBAs must be prepared to
create SQL, SQL*Plus, and PL/SQL reports that provide just the cut of
information they require. This chapter discusses these reports and
shows examples of scripts used to generate them. I suggest that, you,
the DBA, review the contents of the V$ and DBA_ views as listed in
either the Oracle, Oracle8i or Oracle Administrator’s Guide (Release
1 (9.0.1), June 2001, Part No. A90117-01, Oracle Corporation).
Additional information is contained in the Oracle8, Oracle8i or
Oracle Reference (Release 1 (9.0.1), June 2001, Part No. A90190-02,
Oracle Corporation).
Using the Views and Dynamic Performance
Tables to Monitor Tables, Clusters, Snapshots, Types, and Indexes
Now that we know all about the views and DPTs,
let’s look at how they are used to monitor various database
table-related constructs.
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. |
|
