| Once you know where the
hotspots in your database are with respect
to storage structures, it is time to drill
further down and locate the objects that
are most in demand. There is no doubt
that hub tables in a system can cause a
major I/O bottleneck if they are not
correctly designed and implemented.
To get an idea of
which objects have been the "favorite" of
a database's SQL calls, you can run the
following toptables.sql query,
which gets the top 100 objects as
determined by SQL statement execution:
The complete listing
of the toptables.sql query can be
obtained from the online Code Depot at
http://www.dba-oracle.com/bp/bp_book5_perf.htm.
The script queries the sys.v$sql,
sys.v$object_dependency, and
sys.v$db_object_cache views
Observing a single
table with a lot of DML activity provides
a clue that it may be a potential
bottleneck for your system. Other things
to consider when reviewing output from
this query include:
-
Small, frequently-accessed tables should
be considered candidates for the Oracle
KEEP buffer pool (Oracle8i and higher)
or be set to CACHE (Oracle7 and
higher).
-
Large tables that are often accessed and
scanned should be reviewed to determine
if they could be partitioned.
Partitioning can reduce scan times if
only one or a handful of partitions can
be scanned instead of the entire table.
High amounts of disk reads for tables in
the above query are red flags that can
help you identify partitioning
possibilities.
If you think that
large tables are being scanned, and you
are using Oracle9i, you can make use of
the new v$sql_plan view to
validate your suspicions. The
largescan9i.sql query uses this new
view to show which large tables (defined
in the query as tables over 1MB) are being
scanned in the database:
The complete listing
of the largescan9i.sql query can
be obtained from the online Code Depot at
http://www.dba-oracle.com/bp/bp_book5_perf.htm.
After finding out what is being
accessed the most, you next move into
finding out who is causing all the
activity.
If you're a DBA who's looking for real
world Oracle tuning techniques, Oracle
scripts, and advice on how to get to the
heart of critical Oracle performance
problems, then you've come to the right
place. Oracle Performance
Troubleshooting: With Dictionary Internals
SQL & Tuning Scripts was written by
one the world's most widely-read DBAs and Oracle
internals experts. Robin Schumacher
focuses his incredible knowledge of the
Oracle data dictionary into a superb book
that shows how to quickly troubleshoot and
correct Oracle performance problems.
Plus! The online code depot is available
immediately!
http://www.dba-oracle.com/bp/bp_book5_perf.htm |
