|
The Limitations of the DBA
There are many database problem areas
that are beyond the scope of the Oracle DBA’s control, and this
can be very frustrating. The following is a short list of
Oracle problems that the DBA may be prohibited from fixing:
-
Poorly designed application or
schema
-
Inefficient PL/SQL within the
application
-
Excessive Transparent Network
Substrate (TNS) calls within the application
-
Dynamic SQL
-
SQL without host variables
-
Poorly-formed SQL statements
-
Sub-optimal server kernel parms
So, do these bindings mean that the
Oracle DBA is powerless to tune their databases? No, of course
is doesn’t. The DBA has a wealth of tuning tools at their
disposal and many of these tools can have a positive effect on
an entire database. The silver bullets allow for the tuning of
many performance issues when the code cannot be changed
directly:
-
Materialized Views - Allows the DBA
to fix bad schema normalization by pre-joining tables and
pre-summarizing data values. Best of all, Oracle’s
materialized views (MV) feature uses Oracle replication to
allow the DBA to pre-summarize and pre-join tables.
-
Indexes - One of the best silver
bullets, adding missing indexes or improving index
selectivity with function-based indexes, can improve the
efficiency of thousands of SQL statements.
-
Instance Parameters - There are
hundred of init.ora parameters that can have a huge effect
on the performance of an Oracle database.
-
Object Parameters - Internal table
parameters such as freelists, pctfree and pctused can
relieve contention within the I/O subsystem.
-
Tablespace Parameters - New
tablespace features such a Locally-Managed Tablespaces (LMT),
Automatic Segment Space Management (bitmap freelists), and
read-only tablespace can have a huge effect in reducing
physical disk I/O on an Oracle database.
-
Table Structures - The correct use
of Index-Organized Tables (IOT) and table clusters can
greatly reduce both Logical I/O (LIO) and Physical I/O (PIO)
by grouping related data rows together in adjacent data
blocks.
-
Segment Structures - Controlling the
block sizes for specific tablespaces can help reduce stress
on the Oracle database by making more efficient use of
expensive RAM data buffers.
-
Faster Hardware - Once a database is
fully-tuned, faster CPU and solid-state disk (RAM-SAN) can
improve the throughput of the Oracle database.
These
are the main focal areas of the use of silver bullets that will
be examined in this book. |
