Databases Reference
In-Depth Information
Performance and Optimization
The purpose of the optimizer is to select the best execution plan for your queries. But
there is a lot more to optimizing the overall performance of your database. Oracle per‐
formance is the subject of
Chapter 7
of this topic.
SQL Translation
The Oracle Database supports a rich set of SQL syntax; however, other databases also
have their own SQL syntax, which may differ from the exact syntax supported in Oracle.
In the past, these differences would require a sometimes burdensome change in the
actual SQL statements in an application.
Oracle Database 12
c
introduces a SQL Translation Framework. This feature uses SQL
Translators to intercept non-Oracle SQL statements and translate the statements into
Oracle syntax. The SQL statements are captured and translated, and the translations are
automatically used once they are created.
If a SQL statement cannot be translated, the Oracle Database returns an error. You can
add custom translations using the same framework, allowing you to address SQL issues
without having to change the actual SQL statements in the application.
You would use SQL Developer, a very popular tool for the Oracle Database, to implement
not only SQL translation, but to migrate data structures and data from other databases.
SQL Developer is available as a free download from the Oracle Technology Network,
and includes a great deal of useful functionality for managing and administration of
your Oracle Database and Oracle Database Cloud Service, discussed in
Chapter 15
.
Understanding the Execution Plan
Oracle's query optimizer uses an execution plan for each query submitted. By and large,
although the optimizer does a good job of selecting the execution plan, there may be
times when the performance of the database suggests that it is using a less-than-optimal
execution plan.
The only way you can really tell what path is being selected by the optimizer is to see
the layout of the execution plan. You can use two Oracle character-mode utilities to
examine the execution plan chosen by the Oracle optimizer. These tools allow you to
see the successive steps used by Oracle to collect, select, and return the data to the user.
The first utility is the SQL EXPLAIN PLAN statement. When you use EXPLAIN PLAN,
followed by the keyword FOR and the SQL statement whose execution plan you want
to view, the Oracle cost-based optimizer returns a description of the execution plan it
will use for the SQL statement and inserts this description into a database table. You can
