Re: [SPAM] oracle-l Digest V8 #84
- From: Michael Fontana <michael.fontana@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 24 Mar 2011 13:08:22 -0500 (CDT)
Timo wrote:
<<
Date: Wed, 23 Mar 2011 16:00:04 +0200
From: Timo Raitalaakso <rafu@xxxxxx>
Subject: Re: Options to speed up a 7 table join executed frequently.
Fiqure out which part of the query plan takes the time.
Read the plan executed, trace a long running session, take a look on
11.2 sql monitor if tuning pack is available. Use
http://tech.e2sn.com/apps/planviz. The problem most often is not there
seen in the trace, but where e-rows does not correspond to a-rows.
Take a pen and paper or dboptimizer. Draw a VST
http://sites.google.com/site/embtdbo/tuner/graphic-sql diagram. Fiqure
out unnecessary joins, table sizes, tables mentioned in where clause,
cardinality in each table with all restricting predicates, are your
where clause predicates together with join conditions indexed correctly.
Aim for three star indexes in the problematic parts. Sometimes it is the
order by part that needs an semi fat index. Buy and read
http://books.google.fi/books?id=3UoGGIbkqkkC&printsec=frontcover
<http://books.google.fi/books?id=3UoGGIbkqkkC&printsec=frontcover> page 51
It is not the 7 table join that takes the time, but the rows processed.
MINIMIZE logical io. If the indexing is not enough try hints, outlines
or sql profiles. After figuring out the VST diagram one might guess a
good /*+leading(a b c)*/ hint. And yes you can hint a Hibernate query.
Hibernate is not a reason for a problematic query. It is just another
SQL generator.
>>
I respectfully disagree. While Hibernate IS a SQL generator, it is a
particularly horrible one.
The concept is sound, but the product fails in actual practice, especially when
implemented against
Oracle databases. My client project is way behind schedule because it costs
them hundreds of thousands
of dollars in billable hours before they realized this fact.
When asked my opinion of hibernate, I always answer "Only bears should do it!".
--
//www.freelists.org/webpage/oracle-l
Other related posts:
