Question: I understand that SQL profiles are
used to lock down execution plans, but I don't understand how SQL
profiles are used to tune SQL. What are the features of SQL
profiles and when are SQL profiles used?
Answer:
A SQL Profile is a bundle of improved optimizer
statistics that is stored in the data dictionary. SQL Profiles
are create by running Automatic SQL Tuning.
Deploying SQL profiles can behave like optimizer plan stability to allow you to "swap" a bad
execution plan with a good execution plan! SQL profiles allow you
to tune vendor SQL without ever touching the application because SQL
profiles intercept the SQL as it enters the library cache.
Just like stored outlines in the past, SQL profiles can be
exported and imported. Tips on migrating SQL profiles are
available
HERE.
SQL optimization is a complex subject, and SQL profiles are
proving to be a key element in achieving a higher level of database
optimization than was available in early versions of Oracle due to
the limited ability of the DBA to impact the performance of poor
application code.
An integral part of the
SQL Tuning Advisor (STA), the SQL Profile is a collection of the
historical information of prior runs of the SQL statement,
comparison details of the actual and estimated cardinality, and
predicate selectivity, etc. SQL Profile is stored persistently in
the data dictionary, so it does not require any application code
changes.
SQL Profiles are one part the exciting
Automatic SQL Tuning feature rolled out with version 11g.
This feature can automatically generate and implement SQL
improvments at the discretion of the administrator.
Moving forward,
SQL Plan Management will be key to locking down the SQL
execution plans needed for optimal performance.
Additional information on SQL Profiles is available
HERE.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
