 |
|
Other Initialization
Parameters that Affect SQL Execution
Oracle Tips by
Burleson
|
There are also other initialization parameters
that affect the speed of SQL queries, and these should be established
prior to searching for high-impact SQL statements.
-
db_block_buffers Of course, the
size of the Oracle data buffers will impact the performance of SQL
statements. The data buffer hit ratio should always be checked, and
the data buffer hit ratios for the buffer_pool_keep and
buffer_pool_recycle should also be reviewed.
-
hash_join_enabled This defaults
to TRUE in Oracle8 and is required to utilize hash joins.
-
Hash_area_size If this value is
too small, the optimizer will often invoke a nested loop join
instead of a hash join. A too-small hash_area_size is one of
the most common reasons that a use_hash hint fails to invoke
a hash join.
-
hash_multiblock_io_count Because
a hash join often invokes a full-table scan, the
hash_multiblock_io_count parameter determines the read-ahead
capabilities for hash joins.
Once you have established the baseline
optimizer_mode, you should already see a significant overall
performance improvement for your SQL. This author has cut response
time in half in Oracle7 databases, simply by changing the default
optimizer_mode=rule. Of course, it gets trickier in Oracle8 and
Oracle8i because of the improvements to the CBO.
Running the SQL Baseline Test
When performing a SQL baseline test, it is
often a good idea to involve the end-user community. The SQL tuning
professional generally meets with the end-user representatives and
arranges to run the database on different days with different default
optimizer modes. The end users are assured that the optimizer_mode
can be changed back quickly by bouncing the database. Remember, for
OLTP databases, the end users will have a very quick idea how
effective the baseline change is on their system. You also ask the end
users to note which transactions appear faster or slower, since this
information will be invaluable to you when tuning the individual SQL
statements.
By allowing the end-user community to determine
the default optimizer_mode according to their perception of
overall SQL response time, the DBA wins, and the end users win as
well. The end users get an immediate improvement in SQL performance,
and the DBA has less SQL statements to manually tune.
When measuring the overall performance of the
database, you need to remember that the perception of the end users is
far more important than internal Oracle statistics. By allowing your
end users to determine the default optimizer_mode, you ensure
their buy-in for your SQL tuning effort, and you also get some
valuable tips on where to begin the manual tuning. If you request that
your end users keep track of those transactions that run slower in
each optimizer_mode, you can get right to work and immediately
tune the SQL statements on your end users' list.
Establishing the SQL baseline is only the first
step in SQL tuning. Next, let’s look at how to locate significant SQL
statements for tuning.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
