 |
|
Oracle Concepts by Burleson
Consulting |
Understanding SQL Execution
Now that we have a general understanding of
what happens to an SQL statement inside library cache, it's time to
take a closer look at how optimizer SQL within a relational database.
Some of the more sophisticated relational
databases such as Oracle and DB2 have specialized techniques that you
can use in order to make SQL statements fully reentrant. Many
database management systems also have an automated query rewrite
facility, where many sub-optimal SQL query form can be detected and
dynamically re-written into a more optimal form.
This section explores techniques to reduce
excessive re-parsing for SQL statements, and then takes a look at how
you can view the actual execution plan for specific SQL statements.
Once we are able to view the execution plan for an SQL statement, the
next step is to take a look at the table and index access methods that
are available to service a query.
As we discussed in the last tutorial, the
fall-back position for the SQL optimizer is to perform a full table
scan, reading every row in the table. Of course, it is far more
efficient for an SQL statement to use index access to get the table
rows, especially when the where clause of the SQL query has highly
restrictive clauses.
We are now ready to investigate the details of
how row data is accessed from SQL. We will start by exploring
the process of determining the optimal table join order because this
is one of the most important decisions to the optimizer must make.
For example, an SQL query that joins six tables together has six
factorial (6!) possible combinations of table joins. This is 6 x
5 x 4 x 3 x 2, or 720 possible ways that the SQL optimizer can join
these six tables together. This can take a huge amount of time!
Oracle provides a hint called ORDERED to allow us to manually specify
the table join order, and bypass the expense of determining the
optimal table join order.
Once the optimizer is determined the optimal
order in which to join the tables together, the SQL optimizer must
then decide upon the appropriate method for joining the tables
together. The Oracle database offers several table join methods.
Oracle has a sort merge join, a nested loop join, and a hash join
method, all of which serve the common purpose of joining to tables
together, but with radically different internal mechanisms and
external performance.
The most common table join mechanisms are
nested loop joins and hash joins. In a nested loop join, the
database reads and index, builds a list of ROW ID’s and then probes
into the second table for the matching rows.
In a hash join, Oracle uses RAM memory to
speed the join.
In a hash join, the Oracle database does a
full-scan of the driving table, builds a RAM hash table, and then
probes for matching rows in the other table. For certain types
of SQL, the hash join will execute faster than a nested loop join, but
the hash join uses more RAM resources. The propensity of the SQL
optimizer to invoke a hash join is heavily controlled by the setting
for the hash_area_size Oracle parameter. The larger the value
for hash_area_size, the more hash joins the optimizer will invoke. As
we will soon learn in , the hash_area_size defaults to double the
value of the sort_area_size parameter. Here is how to see these
values on your Oracle database:
SQL> show
parameters area_size
NAME
TYPE VALUE
------------------------------------ -----------
---------------------- bitmap_merge_area_size
integer 1048576
create_bitmap_area_size
integer 8388608
hash_area_size
integer 1048576
sort_area_size
integer 524288
workarea_size_policy
string MANUAL
Pay special attention to the readings on sort
operations, and understanding how Oracle first attempts to sort row
results sets inside the RAM memory of the SGA or PGA. Sorting
can occur whenever an SQL statement contains an order by, or group by
clause. If there is no room in the RAM memory region to sort the
results set quickly, Oracle will go to the temporary tablespace, and
complete the sort operation using disk storage. The management
of sorting is a very critical part of SQL tuning because RAM memory
sorts are many thousands of times faster than sort that have to be
done inside the temporary table space.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
