|
 |
|
Oracle DBA Worst Practices
Oracle Database Tips by Burleson Consulting
Updated April
12, 2015 |
There is a great deal of discussion about
following Oracle best practices (e.g. using OFA, etc.), but little
has been said about Oracle worst practices. Contrary to an
Oracle "best practice", an Oracle "worst practice"
is an action (or
non-action) that causes poor performance and creates excessive
management overhead, essentially a non-standard approach to Oracle
database management.
Let's look at a few Oracle
worst practices I have run across in my experience:
Oracle Worst Practices for the DBA
Many Oracle shops commission an
Oracle Health Check to find
these issues, but worst practices are alive-and-well in the Oracle
community.
Suboptimal Database Design
Corporate America is where theory meets pragmatism.
Back in the 1980's when disk was $200k/gig, designing a database in
third normal form was a perfect approach, since 3NF is
non-redundant, and a DBA management goal was to save expensive disk
space.
Obviously, the rules of normalization are
required to understand the relationships & functional dependencies,
but BCNF (or 3NF for those non-purists) is just a starting point,
not a completed model.
No one can deny that legacy systems tended to
be more highly normalized because of the high cost of disk in the
1990's. Today, disk is incredibly cheap, and many Oracle
professionals use denormalization and pre-joins that our data
can be retrieved with far less runtime overhead.
In addition,
Oracle offers several popular denormalization tools, some that
create non first-normal form structures (0NF):
- Object tables - Oracle has nested tables and varray table
columns whereby repeating groups are stored within a row,
violating 1NF.
- Materialized Views - Tables are pre-joined together, queries
are re-written to access the MV, and a method (Oracle snapshots)
keeps the denormalization in-sync with the normalized
representation of the data.
How and where do we introduce redundancy to
remove table joins? The answer depends on the "redundancy
boundary", a function of the size and volatility of the
redundant item.
Today Oracle provides us with
tools to manage denormalization (materialized views) and it is an
Oracle worst practice to do an 8-way table join for every simple
transaction fetch.
Inadequate Indexing
One of the top
causes of excessive I/O during SQL execution is missing indexes,
especially function-based indexes. Failure to tune the instance
according to the SQL load is a major worst practice. It's no
coincidence that the Oracle 10g SQLAccess advisor makes
recommendations regarding missing
indexes.
See my notes:
Poor optimization of initialization
parameters
The worst Oracle practice of all is undertaking to
tune your SQL before these global parameters have been optimized to
the workload. Many shops fail to grasp the critical role of
silver bullet parameters such as optimizer_mode, db_file_multiblock_read_count, pga_aggregate_target,
optimizer_mode, and other
critical parameters.
As documented in the book "Oracle
Silver Bullets", global, instance-wide changes can improve
performance by orders of magnitude.
Prior to
Oracle 10g, adjusting powerful optimizer parameters such as optimizer_index_cost_adj was the only way to compensate for
sample size issues with dbms_stats.
But as of
Oracle 10g, improvements in system statistics collection using dbms_stats.gather_system_stats,
used to measure sequential vs. scattered disk I/O speed, plus improved sampling within
dbms_stats had made adjustments to the optimizer parameters a worst
practice exercise in most cases.
Ceteris Parabus, always adjust CBO statistics before adjusting optimizer
parms. For more details on optimizer parameters, see my latest book "Oracle
Tuning: The Definitive Reference".
For more on holistic instance
tuning, see my notes:
Poor Schema Statistics Management
The
Oracle worst practice (before 10g automatic statistics collection)
was to re-analyze the schema on a schedule, forgetting that the
purpose of re-analyzing schema statistics is to change your
production execution plans. This worst practice has become so
commonplace that it has been dubbed the "Monday Morning
Suprise". Shops with strict production change control
procedures forget that analyzing the production schema can effect
the execution of thousands of SQL statements.
Just as you
custom-set your pga_aggregate_target and db_cache_size
according to your environment, you need to set your metadata and
system stats
(via the dbms_stats package), according to your specific
environment. It's not a one-size-fits-all utility and it
must be set intelligently. See my notes here:
Bad disk I/O sub-system
It's amazing
how many shops fall into worst practices by following vendor advice
and implementing an inappropriate disk layout for Oracle.
RAID5 for high-DML systems, failure to mirror disk are a major
Oracle worst practice.
Using NOARCHIVELOG mode
While it is true that running in ARCHIVELOG
mode adds some significant overhead to an Oracle database,
especially those with high DML activity, it is likely a serious
mistake to simply make the change to NOARCHIVLOG mode without
carefully weighing the burned of that overhead against the
potential for catastrophic loss of data that can occur in the
event of an event like disk failure.
Oracle's ARCHIVELOG mode is a safety mechanism
whereby Oracle can "roll forward", by applying the "after
images" of all changed rows. It is where the entries in redo
logs are saved prior to the point in time where a new
incarnation of the log overwrites the previous log. Internally,
redo is a critical recovery component, and its entries contain
changes to data, indexes and undo segments (as well as commit
markers and other 'housekeeping' items such as end-of-job and
commit checkpoints). Archiving makes sure that the redo
component is protected.
User Profiles
It is a bad practice to use profiles.
All profiles do is limit the usage of a user, annoying them and
possibly interrupting their work!
Oracle
Environmental Worst Practices
No Instance
Consolidation The nightmarish approach of one Oracle
instance per server was never a benefit. It was a requirement of
the limited computing power of the tiny minicomputers of the
client server days of the 1990's. Remember, the
one-instance-per-server requirement was imposed by the tiny
servers of the past, and now that we have
large monolithic servers for UNIX/Linux, smart companies are
returning to the time-tested approach of having a single server
environment with many Oracle instances. (See OTN "Strategy
for Success: Consolidate!".
There are many
compelling benefits to having multiple instances on a large
scalable server (Oracle
instance consolidation), all part of the
second age
of mainframe computing:
-
Easier
maintenance - The Oracle Optimal Flexible Architecture
(OFA) is constructed so that dozens of instances can share
the same binaries making patching non-repetitive.
-
Better sharing
of computing resources - In a Grid environment you can
only add additional RAM of CPU resources by adding a new
server blade. In a large monolithic server, CPU
resources are shared instantly between instances.
Poor Change Control Testing
This is the worst of the worst practices, where an Oracle
shop relies on a "test case proof" to preview how a database
change will effect production behavior. Smart shops do a
full TPC benchmark, using Oracle benchmarking tools like the
Quest Benchmark Factory.
Inadequate Test Environments
One
way to guarantee sub-optimal SQL in production is to provide an
inadequate database for your developers. Remember, you can
export your production schema statistics to make your development
system look like production.
See my notes:
No Change Control Procedures
In
highly active development shops, version control for schema objects,
stored procedures and code chunks are absolute necessities.
Not Enough Testing Instances
Many
Oracle shops keep fours environments: DEV for development; TEST for
unit testing; QA for pre-production testing; and PROD for
production. Any less
Oracle testing
and you may risk unintended side effects.
No Performance Tracking
With STATSPACK
(free) and AWR starting with Oracle10g, there is no excuse for not tracking
your database performance. STATSPACK and AWR provide a great
historical performance record and set the foundation for DBA
predictive modeling.
Also see Oracle tuning
best practices and
Oracle performance
monitoring best practices.
Poor Security Management
Oracle offers
a host of access control mechanisms (grants, roles, VPD) yet it is
amazing how many shops have giant security holes.
No Alert Mechanisms
Oracle Enterprise
Manager (OEM) has built-in alert capability and you can write your
own alerts for many critical Oracle events (filesystem filling-up,
object cannot extend, replication backlog, etc.). The vast
majority of Oracle outages are preventable and it is a worst
practice to wait until Oracle is crippled before fixing an issue.
Non-standard External Environments
This is another of the worst of worst practices where every database uses
different shells, different aliases and non-standard file
locations.
See my notes:
References:
This article
covers the top 20 most common errors made by Oracle
professionals:
The author mentions many Oracle
DBA "worst practices" including no standards, rushing to
upgrade to the latest release of Oracle, poor Oracle
security and poor Oracle tuning practices.
PL/SQL Worst Practices
While not a DBA worst practice area, Steve
Feuerstein has this list
of PL/SQL worst practices:
Here are a few of the journeys we will take.
The bad old days of cursor FOR loops
In the bad old days, we didn't think twice about
using cursor FOR loops: quick and easy way to fetch a
single row, simplest way to iterate through multiple
rows. At back then (as in Oracle8!), this made perfect
sense. But with FORALL and BULK COLLECT, along with SAVE
EXCEPTIONS and INDICES OF clauses, our world and way we
should write SQL inside PL/SQL has changed forever.
Hard-coding? Let me count the ways....
Literal values, direct datatype declarations, SQL
statements, -20NNN error numbers...the list of hard-codings
inside PL/SQL is long, bewildering and intimidating.
It's not easy to keep hard-codings out of your code, but
once you acknowledge all the scenarios that constitute
hard-coding you can then proceed to remove them.
Optimistic programming or just plain laziness?
We are generally challenged sufficiently to get our
programs working properly. Thinking about and
incorporating error handling into our programs often
comes later or not at all. We will start with very
poorly implemented error handling section and apply a
variety of recent PL/SQL language enhancements to bring
it up to 21st century standards.
|
|
|
|
and include the URL for the page.
