RE: Options to speed up a 7 table join executed frequently.
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <rafu@xxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Mar 2011 13:18:57 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Timo Raitalaakso
Sent: Wednesday, March 23, 2011 10:00 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx<mailto:Joel.Patterson@xxxxxxxxxxx> 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
Other related posts:
