Re: Options to speed up a 7 table join executed frequently.
From: Tim Gorman <tim_at_evdbt.com>
Date: Wed, 23 Mar 2011 06:57:54 -0600
Message-ID: <4D89EE52.3060208_at_evdbt.com>
The idea with materialized views might be useful -- look for the "expensive" part of the seven-table join and consider just materializing that, leaving the rest of the query intact. For example, look for long expensive INDEX RANGE SCAN operations between 2-3 of the seven tables. If these can't be tuned any better, perhaps consider encapsulating the 2-3 table join into a materialized, which the query can then use, allowing the remaining 4-5 "easy" joins to still occur at run-time. Of course, of any of those seven joins involve aggregation, then right there is an opportunity for an MV.
MVs in an OLTP environment can be problematic for refresh and there is a whole book to write on optimizing refresh, but the starting point is first determining if the MV is worth the effort before worrying about refresh.
Since the seven table join is generated, be sure to decompose the query and look for nonsense conditions caused by invalid metadata in Hibernate. It could be something as simple as only five of the seven tables being necessary to logically complete the query, but the two additional joins being thrown in there because Hibernate thinks they're necessary.
Just a few idea to look for...
<rant>
I am currently going through my own issues with a development team using Hibernate, and it is painful. I'm pretty sure they use the term "persistence layer" to refer to the database simply to irk, and their insistence that "all business logic belongs in the application" is patently foolish.
But, they're the ones writing the code, and they seem to think that the conveniences of developing in Hibernate outweighs the pain it causes at run-time, as if the business community had made "developer productivity" one of the main goals of the project. I've seen functional and performance goals, but not a mention of "development productivity".
And don't even get me started on the charade of "database portability", as if it is the database which tends to be switched over an application's lifetime, and not the application "framework". Funny how the business hasn't indicated "portability" across platforms as a goal or even a concern, yet the development teams seem fixated on how "portable" their app could be. One developer even argued that "database portability" was a good way to counter the absurd license fees charged by Oracle. Since Java is owned by Oracle, I asked in response why that is not a concern in the Java-based app-tier as well, but that it was a good point since there is certainly a greater need for portability and vendor/license management in the far-more-volatile "application layer" than in the far-more-persistent "persistence layer". :-)
</rant>
Good luck,
On 3/23/2011 6:08 AM, Joel.Patterson_at_crowley.com wrote:
Date: Wed, 23 Mar 2011 06:57:54 -0600
Message-ID: <4D89EE52.3060208_at_evdbt.com>
Joel,
The idea with materialized views might be useful -- look for the "expensive" part of the seven-table join and consider just materializing that, leaving the rest of the query intact. For example, look for long expensive INDEX RANGE SCAN operations between 2-3 of the seven tables. If these can't be tuned any better, perhaps consider encapsulating the 2-3 table join into a materialized, which the query can then use, allowing the remaining 4-5 "easy" joins to still occur at run-time. Of course, of any of those seven joins involve aggregation, then right there is an opportunity for an MV.
MVs in an OLTP environment can be problematic for refresh and there is a whole book to write on optimizing refresh, but the starting point is first determining if the MV is worth the effort before worrying about refresh.
Since the seven table join is generated, be sure to decompose the query and look for nonsense conditions caused by invalid metadata in Hibernate. It could be something as simple as only five of the seven tables being necessary to logically complete the query, but the two additional joins being thrown in there because Hibernate thinks they're necessary.
Just a few idea to look for...
<rant>
I am currently going through my own issues with a development team using Hibernate, and it is painful. I'm pretty sure they use the term "persistence layer" to refer to the database simply to irk, and their insistence that "all business logic belongs in the application" is patently foolish.
But, they're the ones writing the code, and they seem to think that the conveniences of developing in Hibernate outweighs the pain it causes at run-time, as if the business community had made "developer productivity" one of the main goals of the project. I've seen functional and performance goals, but not a mention of "development productivity".
And don't even get me started on the charade of "database portability", as if it is the database which tends to be switched over an application's lifetime, and not the application "framework". Funny how the business hasn't indicated "portability" across platforms as a goal or even a concern, yet the development teams seem fixated on how "portable" their app could be. One developer even argued that "database portability" was a good way to counter the absurd license fees charged by Oracle. Since Java is owned by Oracle, I asked in response why that is not a concern in the Java-based app-tier as well, but that it was a good point since there is certainly a greater need for portability and vendor/license management in the far-more-volatile "application layer" than in the far-more-persistent "persistence layer". :-)
</rant>
Good luck,
Tim Gorman consultant -> Evergreen Database Technologies, Inc. postal => 13087 West Cedar Drive #225, Lakewood CO 80228 website => http://www.EvDBT.com/ email => Tim_at_EvDBT.com mobile => +1-303-885-4526 fax => +1-303-484-3608 Lost Data? => http://www.ora600.be/ for info about DUDE...
On 3/23/2011 6:08 AM, Joel.Patterson_at_crowley.com wrote:
-- http://www.freelists.org/webpage/oracle-l Received on Wed Mar 23 2011 - 07:57:54 CDTA 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 PattersonDatabase Administrator904 727-2546