RE: Options to speed up a 7 table join executed frequently.

  • From: <Joel.Patterson@xxxxxxxxxxx>
  • To: <Richard.Goulet@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 23 Mar 2011 08:32:57 -0400

Well, I have to agree with that, and in the initial meetings I suggested just 
that - just under the premise that they 'figured' they wouldn't need any 
'oracle' guy for SQL.


J       I tried logic, like no program can create optimal SQL or even uses all 
the tools available, ie windows functions.... But if you don't know how vast 
the subject is, then one is only looking at money, and is easily persuaded by 
third parties that they will save lots of money by using hibernate to generate 
all the sql.


What do we do?  They just had there congratulations party.   It would cost to 
revamp no?   Wait until next year when they expect five times the usage.


Joel Patterson
Database Administrator
904 727-2546

________________________________
From: Goulet, Richard [mailto:Richard.Goulet@xxxxxxxxxxx]
Sent: Wednesday, March 23, 2011 8:21 AM
To: Patterson, Joel; oracle-l@xxxxxxxxxxxxx
Subject: RE: Options to speed up a 7 table join executed frequently.

First suggestion, throw Hibernate out.  They use to use it here, caused a lot 
of trouble and was replaced by WebFocus.  Things now much more stable and 
predictable.


Dick Goulet
Senior Oracle DBA/NA Team Leader


________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Joel.Patterson@xxxxxxxxxxx
Sent: Wednesday, March 23, 2011 8:08 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Options to speed up a 7 table join executed frequently.
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: