Just as the
v$sql_plan view
revolutionized Oracle9i tuning, the new Oracle10g AWR tables have
revolutionized SQL tuning. Most Oracle experts note that SQL
optimization is one of the most important factors in database
tuning, yet the transient nature of SQL execution has made it
difficult to see the impact of SQL execution over time. All of this
changed with Oracle10g.
The AWR tables contain useful information about
the time-series execution plans for SQL statements, and this
repository can be used to display details about the frequency of
usage for table and indexes.
The following basic relationships between
database objects and SQL statements are important:
§
Each SQL statement may generate many access plans:
From the information on dynamic sampling and
dbms_stats, the execution
plans for SQL statements will change over time to accommodate
changes in the data they access. It is important to understand how
and when a frequently executed SQL statement changes its access
plan.
§
Each object is access by many access plans: In most
OLTP systems, tables and indexes show repeating patterns of usage
and clear patterns can be detected when averaging object access of
day-of-the-week and hour-of-the-day.
Figure 15.18:
Time-series relationships between SQL and database objects
Figure 15.18 shows that there is a many-to-many
relationship between any given SQL statement and the tables they
access. Once this fundamental relationship is clear, the AWR tables
can be used to perform time-based SQL tuning. We have the following
AWR tables for SQL tuning as shown in 15.19:
§
dba_hist_sqlstat
§
dba_hist_sql_summary
§
dba_hist_sql_workarea
§
dba_hist_sql_plan
§
dba_hist_sql_workarea_histogram
Figure 15.19:
The dba_hist views for SQL
tuning
These simple tables represent a revolution in
Oracle SQL tuning, and time-series techniques can be employed to
optimize SQL with better results than ever before. The following
section provides a closer look at these AWR tables and the amazing
details that they can provide about SQL execution over time.
The
Ion tool is
also excellent for identifying SQL to tune and it can show SQL
execution over time with stunning SQL graphics.
and include the URL for the page.
