文档介绍：Oracle 10g
Optimization Everywhere
Jonathan Lewis
Agenda
Who am I
Areas of optimisation
Past, Present and Future
Q and A
Most slides have a foot-note. This is a brief summary of ments that I
Jonathan Lewis Oracle 10g
© 2003-2004 should have made whilst displaying the slide, and is there for later reference. Evolution 2
1
Who am I ?
Independent Consultant.
19+ years in IT
16+ using Oracle
Strategy, Design, Review
Briefings, Seminars
Trouble-shooting
.
Jonathan Lewis Oracle 10g
© 2003-2004 Evolution 3
What is "Optimisation" ?
• What do we want ?
– Better performance for less cash
• Better application code
– done by the dba / developer
• Better tools
– let the dba/developer do a better job, faster
• Better kernel code
When you think of optimisation, it's not just about ways to rewrite the SQL
Jonathan Lewis Oracle 10g
© 2003-2004 to make it go faster - Oracle keeps inventing new strategies, and new tools. Evolution 4
2
Case Study - Unnesting
• Subqueries can e inline views
• Subqueries can e anti or semi-joins
• The optimizer may do it for you
• You may have to do it by hand
• Sometimes it is the wrong thing to do
• Sometimes there are better options anyway
As an indication of how things keep changing - and how we have to keep
Jonathan Lewis Oracle 10g
© 2003-2004 reviewing code on upgrades - we look at subquery unnesting. Evolution 5
Correlated Subquery (1)
select
outer.*
from emp outer
where > (
select /*+ unnest */
avg()
from emp inner
where =
);
If you did not include the hint (which is not needed in Oracle 9) a query like
Jonathan Lewis Oracle 10g
© 2003-2004 this could be slow and expensive in Oracle 8. Evolution 6
3
Correlated Subquery (2)
Default execution path in (has to be forced in 9i and 10g)
FILTER
TABLE ACCESS (FULL) OF EMP (Cost=34 Card=1000)
SORT (AGGREGATE)
TABLE ACCESS (FULL