Re: [SPAM] oracle-l Digest V8 #84

Timo wrote:

<<
Date: Wed, 23 Mar 2011 16:00:04 +0200
From: Timo Raitalaakso <rafu_at_iki.fi>
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!".

--
http://www.freelists.org/webpage/oracle-l