Re: Options to speed up a 7 table join executed frequently.
Date: Wed, 23 Mar 2011 16:00:04 +0200
Message-ID: <4D89FCE4.6050200_at_iki.fi>
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.
-- Timo Raitalaakso http://rafudb.blogspot.com On 23.3.2011 14:08, Joel.Patterson_at_crowley.com wrote:Received on Wed Mar 23 2011 - 09:00:04 CDT
> A developer asked for suggestions to speed up a query in oracle
> 10.2.0.4 Solaris 10, soon to be 11gr2.
> He comes in around 2 hours from now, and I have asked to see the
> query. However...., here is the background.
> The query is generated by hibernate.... The query is called very
> frequently. The underlying data in the tables changes constantly.
> The query joins around seven tables.
> I thought perhaps a seven table join would be to complicated for a
> materialized view.
> I figure a normal view doesn't stop the execution of the query...
> I thought perhaps a stored procedure where the data could be gathered
> manually via cursors, and all tools available there.
> A temporary table... yet that doesn't solve the problem either, and so
> many people generate the query...
> So, I'm looking for ideas, I am just in the beginning stage.
> P.S. I will be out after today until Monday.
> Joel Patterson
> Database Administrator
> 904 727-2546
-- http://www.freelists.org/webpage/oracle-l