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