Oracle Database 11g SQL Tuning Enhancements

Maclean Liu(刘相兵

Oracle Database 11g SQL Tuning Enhancements

Operational SQL Enhancements in 11g
Oracle introduced two new tools in 11g: the SQL Performance Analyzer and
the oddly named Real Application Testing framework. These are milestone
tools in Oracle SQL tuning because they finally codify the holistic tuning
approach that embrace global SQL optimization. With RAT and SPA, we can
optimize our environment (optimizer parameter settings, metadata statistics)
using real-world workloads, tuning the entire workload. While Oracle
professionals have been doing this manually for years, this new approach is
revolutionary because it discourages the bottom-up approach whereby
individual SQL statements are tuned first, only to have the changes un-done
by changes to global parameters.
Oracle 11g has 71 documented hints, some great, some trivial. Here are my
favorite new 11g hints for SQL tuning.
?? The opt_param hint – Actually new in 10gr2, the opt_ param hint is very
similar to the "alter session" method for changing parameters. However, it
only applies to that specific SQL statement. For testing, you can quickly test
the effect of another optimizer parameter value at the query level without
using an “alter session” command, using the new opt_ param SQL hint.
For example, the SQL below turn-off hash_join_enabled (to forced a
nested loop, usually), but only for that SQL statement:
select /*+ opt_param('hash_join_enabled','false') */
dept_no,
emp_name
from emp e, dept d where e.ename=d.dname;
The opt_ param hint is also useful for testing the effect of global
parameters such as optimizer_mode and optimizer_cost_model.
select /*+ opt_param('optimizer_mode','first_rows_10') */
select /*+ opt_param('_optimizer_cost_model','io') */
select /*+ opt_param('optimizer_index_cost_adj',20) */
select /*+ opt_param('optimizer_index_caching',20) */
?? The result_cache hint – I’m not a big fan of this hint, but some people
love it. Oracle PL/SQL has always allowed us to keep intermediate data
in RAM arrays for later use by the session (using an array heap called a
"collection"). The result_cache hint expands on traditional collections
to allow for inter-session access to the collection. But there are some
important differences. PL/SQL collections are stored in the PGA, whereas
the result_cache output is stored in the SGA region. But we must
remember the result_cache is only useful in applications where many
sessions query the exact same data.
Automating SQL Tuning with Plan Baselines
One great way to tune SQL in 11g is to allow Oracle to test the run-time SQL
performance and use the better execution plan whenever a SQL statement
runs more than three times faster than an older, existing plan. Oracle 11g
now enhances their SQL profiles with SQL Plan baselines, a new tool that lets
you automatically capture execution plans. You simply set the parameter
optimizer_capture_sql_plan_baselines=true and Oracle will begin collecting
and storing your SQL execution plans. Once you have captured the execution
plans for your most popular SQL you can use the 11g SQL Plan Management
tool (implemented via the dbms_spm package), you evaluate real-world SQL
workloads (aka SQL tuning sets), and compare their before-and-after SQL
performance, choosing only to change execution plans for SQL that runs at
least three times faster.
Tuning When you Cannot Touch the SQL
One important 11g enhancement is with SQL profiles, a replacement for stored
outlines (plan stability) where you can capture and store the execution plan for
SQL statements, and swap-out a bad execution plan with a good one without
ever touching the application SQL. This swapping of SQL profiles is useful for
tuning vendor applications and ad-hoc SQL tools like Crystal Reports, where
you cannot directly change the SQL statement. With SQL that hides inside a
pre-compiled program, changing the SQL is impossible without access to the
source code. In other cases, the software vendor may explicitly prohibit any
changes to the source code, and you must come-up with a creative way to tune
the SQL without touching the source code. The central idea behind swapping
SQL profiles is simple. You define a SQL profile that specifies the SQL statement
that you want to tune, and an alternative execution plan, in the form of hints.
When this SQL is executed and hits the library cache, Oracle detects that a SQL
profile exists for this statement, and automatically applies the hints to change
the execution plan.
Oracle 11g also has a way-cool technique for adding a hint to a stored SQL
profile using the dbms_sqltune.import_sql_profile procedure. In this example,
we easily change the optimizer mode from first_rows_10 to all_rows:
begin
dbms_sqltune.import_sql_profile(
name => 'test',
category => 'default',
sql_text => 'select /*+ first_rows(10) */ * from emp order by emp_id',
profile => sqlprof_attr('all_rows','ignore_optim_embedded_hints')
);
end;
/
Put all of this together, and we have a powerful arsenal for tuning SQL
statements without ever touching the SQL statement. To do this we use the
dbms_sqltune package which has an import_sql_profile procedure that
allows you to swap hints from one SQL profile into another SQL profile.
continued on page 22
Page 22 ■ 3rd Qtr 2010
dbms_sqltune.import_sql_profile(
sql_text => 'select * from emp',
profile => sqlprof_attr('ALL_ROWS','IGNORE_OPTIM_EMBEDDED_HINTS')
category => 'DEFAULT',
name => 'change_emp',
force_match => &&6
);
To see working examples of swapping SQL profiles, just Google for the
dbms_sqltune package and the import_sql_profile procedure.
The 11g Flash Cache and SQL Tuning
There is no question that I/O is the one of the important part of SQL tuning.
As RAM process fall below $1,000 per gigabyte, many shops are moving to
solid-state storage, using solid-state disks or giant data buffer regions to fully
cache their important data. Today a millisecond is an eternity, and the ancient
spinning platter disks that we have been using since the 1970s are finally
becoming obsolete.
Oracle 11gR2 includes a feature called flash_cache, a table and index
argument that resembles the assignment of high-use objects to the KEEP pool.
However, unlike the KEEP pool that uses volatile RAM disk, the flash_cache is
used for tertiary storage on solid-state disk (SSD).
Although it is officially only available for Exadata servers and Linux, Guy
Harrison describes a special patch that allows you to use Oracle flash_cache
without buying the Exadata server. In plain English, Oracle flash_cache is
recognizing a hierarchy of storage hardware, organized by access speed:
1. Data Buffer RAM – The KEEP pool can cache objects for super-fast access
at RAM speeds. Of course, this data is not persistent.
2. Solid-state disk – SSD is not as fast as native RAM, but it remains
hundreds of times faster than platter disks. However, all SSD suffers from
a severe write penalty and is not ideal for high DML data.
3. Disk spindles – The old magnetic-coated spinning platters from the
1960s comprise the bulk of Oracle data storage, with access speeds
expressed in milliseconds.


Oracle Database 11g SQL Tuning Enhancements.pdf (384.7 KB, 下载次数: 14, 售价: 10 金钱)

2012-3-21 21:27 上传

点击文件名下载附件
售价: 10 金钱  [记录]

aiirii

只想看看啊，钱不够啊

1112061

只想看看啊，钱不够啊

aiirii

回复多一次，攒分下载

paranoimer

虽然钱不够 ,也不买

wjl7813

钱不够啊！

xduron

支持，谢谢分享！

bjchangxiao

下载下来学习一下

falent

这个资料非常不错，一直有参考意义