RE: Options to speed up a 7 table join executed frequently.
Date: Wed, 23 Mar 2011 13:18:57 -0400
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA16153825D7_at_JAXMSG01.crowley.com>
Thanks everyone, and this gives me the opportunity to try and master the VST portion of the DB Optimizer. I was never good at writing SQL, so this should be a treat. I have been waiting for an excuse to devote some time to it. (Not like the last two days untangling the Referential Integrity lines in Erwin after a reverse engineering of a schema. That's like a puzzle and an art).
Joel Patterson
Database Administrator
904 727-2546
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Timo Raitalaakso Sent: Wednesday, March 23, 2011 10:00 AM To: oracle-l_at_freelists.org
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 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<mailto:Joel.Patterson_at_crowley.com> wrote: 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
Received on Wed Mar 23 2011 - 12:18:57 CDT