RE: Options to speed up a 7 table join executed frequently.
Date: Wed, 23 Mar 2011 08:32:57 -0400
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1615348F75_at_JAXMSG01.crowley.com>
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_at_parexel.com] Sent: Wednesday, March 23, 2011 8:21 AM
To: Patterson, Joel; oracle-l_at_freelists.org 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com Sent: Wednesday, March 23, 2011 8:08 AM
To: oracle-l_at_freelists.org
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 23 2011 - 07:32:57 CDT