 |
|
Lillian Hobbs speaks on Oracle 11g SQL tuning transformations
Oracle11g Tips by Burleson Consulting
December 16, 2007 |
The
Oracle cost-based optimizer tries to improve its ability to re-write
and transform sub-optimal SQL query forms. While most Oracle
professionals are aware how the CBO will perform query
transformations to point to materialized views, replacing an “order
by” sort with index sort retrieval, and eliminating certain
subqueries (by replacing them with standard joins and NOT NULL
tests), the optimizer continues to advance in its ability to
transform query syntax into ever more efficient forms.
Lillian
Hobbs rcently gave a presentation on 11g SQL Tuning, and noted these
type of query transformations that are
used in SQL Tuning:
JPPD - join predicate push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SU - subquery unnesting
OBYE - order by elimination
CNT - count(col) to count(*) transformation
JE - Join Elimination
Under
subquery un-nesting we see these common applications:
These
are the abbreviations that are used withinan optimizer trace file to
indicate the type of optimizer transformation. For example, an
optimizwer tracefuile might refer to a join elimination thusly:
JE: eliminate table:
DEPARTMENTS
New 11g SQL
transformations
Ms.
Hobbs also listed these new SQL query transformations within
Oracle11g.
11g CBQT - cost-based query transformation
11g JPPD - join predicate push-down
11g CNT - count(col) to count(*) transformation
11g JE - Join Elimination
In
addition, she listed these types of CBO transformations from earlier
release of Oracle:
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
ST - star transformation
 |
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
