🎭 Some recommendations about using ORM's that automatically create long format SQL statements and its challenges to making a preferred Databases execution plan
Alireza Kamrani
04/07/2025
🔻As a database administrator, have you ever been in a situation where you had to convert your ORM code to Lazy or native SQL?
🎗When dealing with complex SQL queries generated by ORMs like Hibernate, which produce long aliases and suboptimal execution plans, you can follow these standard solutions to optimize them without necessarily forcing developers to switch to Lazy loading.
1. Analyze Execution Plan and Identify Bottlenecks
• Use EXPLAIN (ANALYZE) (PostgreSQL), EXPLAIN PLAN (Oracle), or SET STATISTICS IO, TIME ON (SQL Server) to analyze the query execution plan.
• Look for issues like full table scans, index scans vs. index seeks, sorts, hash joins, etc.
• Identify whether inefficient joins, unnecessary columns, or incorrect index usage is causing slow performance.
2. Apply Query Rewriting & Optimization
• Use CTEs or materialized views: If queries are deeply nested, consider Common Table Expressions (CTEs) or materialized views to precompute parts of the query.
• Reduce unnecessary columns: Hibernate queries often select more columns than needed. Encourage the development team to use projection queries instead of SELECT *.
• Optimize JOIN order: Ensure the most selective tables are joined first.
• Limit the result set: If possible, use LIMIT or TOP to reduce result size.
3. Use Indexing Strategies
• Ensure proper indexing: Verify that the query filters and joins utilize indexes efficiently.
• Use covering indexes: If specific columns are frequently queried together, create a covering index.
• Partitioning: If the dataset is large, partitioning can help improve performance.
4. Force a Better Execution Plan (DB-Specific)
• SQL Server: Use query hints like OPTION (FORCE ORDER, RECOMPILE, OPTIMIZE FOR, HASH JOIN, MERGE JOIN).
• PostgreSQL: Use SET enable_nestloop = OFF, SET enable_hashjoin = OFF for fine-tuning execution.
• Oracle: Use optimizer hints like /*+ INDEX(table index_name) */, /*+ LEADING(table) */.
5. Use Hibernate-Specific Optimizations
• Enable query caching: Hibernate has second-level cache and query caching mechanisms to reduce redundant executions.
• Fine-tune fetch strategy: Instead of changing to Lazy loading globally, use:
• @BatchSize(size = X) to optimize IN clause fetches.
• JOIN FETCH to prefetch related entities efficiently.
6. Consider SQL Plan Baselines (Oracle) or Query Store (SQL Server)
• SQL Server Query Store: Helps force a good execution plan when the optimizer picks a bad one.
• Oracle SQL Plan Baselines: Store an optimal plan and prevent regressions.
• PostgreSQL pg_hint_plan: Allows manual hints to guide the optimizer.
7. Encourage Better Query Practices in Development
• Avoid SELECT N+1 problem: Use batch fetching strategies.
• Encourage use of DTOs: Instead of fetching entire entity objects, fetch only necessary fields.
• Optimize Hibernate mappings: Tune fetch, batch size, lazy/eager loading, and caching.
8. Monitor and Profile Regularly
• Use AWR Reports (Oracle), Query Store (SQL Server), or pg_stat_statements (PostgreSQL) to track slow queries.
• Set up automated performance alerts when query execution time exceeds a threshold.
Final Thought
If ORM-generated queries are consistently problematic, consider a hybrid approach:
• Allow Hibernate for general queries.
• Use hand-written SQL (native queries or stored procedures) for critical performance-sensitive operations.
🔴Recommendations for Oracle Database
For Oracle, here’s how you can optimize complex SQL queries generated by Hibernate or other ORMs when the optimizer struggles to generate an efficient execution plan.
1. Analyze and Understand the Execution Plan
• Use EXPLAIN PLAN EXPLAIN PLAN FOR <your_query>; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
• Use DBMS_XPLAN.DISPLAY_CURSOR (for live execution) SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
• Look for key issues like:
• Full Table Scans (TABLE ACCESS FULL)
• Inefficient Index Usage
• Expensive Nested Loops
• High TEMP usage (implies sorting or hash joins)
• High Cardinality Estimates
2. Optimize Query Structure
• Rewrite queries using Common Table Expressions (CTEs) (WITH clause) to break down large queries.
• Reduce unnecessary joins and subqueries: Hibernate often generates redundant subqueries.
• Remove unnecessary columns: Hibernate sometimes selects all columns (SELECT *), impacting performance.
3. Force a Better Execution Plan
If the Oracle optimizer picks a suboptimal plan, try Optimizer Hints:
• Force Index Usage SELECT /*+ INDEX(my_table my_index) */ col1, col2 FROM my_table WHERE col1 = 'value';
• Control Join Order SELECT /*+ LEADING(t1 t2) */ t1.*, t2.* FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id;
• Force a Hash Join SELECT /*+ USE_HASH(t1 t2) */ t1.*, t2.* FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id;
• Force Nested Loop Join SELECT /*+ USE_NL(t1 t2) */ t1.*, t2.* FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id;
• Parallel Execution for Large Queries SELECT /*+ PARALLEL(4) */ * FROM my_large_table;
4. SQL Plan Management (SQL Baselines)
If you find a good execution plan and want Oracle to stick to it:
BEGIN DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'your_sql_id'); END; /
And then set preferred plan as a Fixed plan.
🔆Also using Spm you can Rewriting a SQL statement as your better format in the fly.
This prevents the optimizer from changing the plan unexpectedly.
5. Adaptive Query Optimization (11g and above)
Oracle 12c+ introduced Adaptive Query Optimization. If Hibernate-generated queries struggle, consider adjusting these parameters:
ALTER SESSION SET optimizer_adaptive_features = FALSE; -- For 12c
ALTER SESSION SET optimizer_adaptive_statistics = FALSE; -- For 19c
These settings prevent Oracle from making unstable plan choices.
6. Hibernate-Specific Optimizations
• Enable Second-Level Cache (EhCache, OSCache, Infinispan) to reduce redundant queries.
• Use @BatchSize(size = X) to improve fetching
• Avoid JOIN FETCH when unnecessary: This loads large result sets.
• Use native queries for performance-critical reports.
Example:
@Query(value = "SELECT /*+ INDEX(t my_index) */ t.* FROM my_table t WHERE t.status = :status", nativeQuery = true) List <MyEntity> findByStatus(@Param("status") String status);
7. Monitor Performance Proactively
• AWR Reports (@?/rdbms/admin/awrrpt.sql)
• SQL Monitoring SELECT * FROM V$SQL_MONITOR WHERE sql_text LIKE '%your_query_pattern%';
• Active Session History (ASH) SELECT sql_id, sql_text, elapsed_time FROM V$ACTIVE_SESSION_HISTORY WHERE sql_text LIKE '%your_query_pattern%';
8. SQL Patch - SQL Transformation
You can use a SQL Patch to apply an internal transformation to rewrite the SQL, reducing excessive aliasing:
BEGIN DBMS_SQLDIAG.CREATE_SQL_PATCH( sql_text => 'SELECT long_alias_format_generated_by_ORM', hint_text => '/*+ NO_MERGE(alias) INLINE(alias) FULL(alias) */' ); END; /
This can help Oracle simplify execution, but it won't fully rewrite the SQL into a more compact form.
9. SQL Profiles
(DBMS_SQLTUNE.IMPORT_SQL_PROFILE)
If you have a better manually rewritten query that performs well, you can apply a SQL Profile to guide the optimizer. SQL Profiles provide statistical corrections rather than full rewrites but can improve performance.
10. Use a Materialized View or a SQL Macro
If your ORM frequently generates complex views with excessive joins, consider creating a Materialized View or SQL Macro (introduced in 19c) to simplify the execution path.
11. Hibernate-Level Optimization
Instead of forcing changes at the database level, tuning the Hibernate Query Generation Strategy can reduce aliasing. Consider:
• Using DTO projections instead of fetching full entities.
• Enabling query transformations in Hibernate.
• Using native SQL queries for complex reporting use cases.
Final Recommendation
If you want to force Oracle to interpret a complex ORM query in a simpler form, SQL Patch with a transformation hint or SPM to change Sql statement.
But it is better to try to have a complete rewrite, you may need to adjust ORM settings or use a specific database view to expose a simpler query structure.
• If Hibernate is causing issues, consider a hybrid approach: Use Hibernate for standard queries and PL/SQL procedures or native queries for performance-sensitive operations.
• SQL Plan Baselines can be your best friend for ensuring a stable, optimized plan over time.
• Indexing and proper join strategies should be reviewed regularly to adapt to data growth.
I hope this post was helpful to you.
Note: All recommendations that reviewed in this topic, needs test and customizing base on your workload and structures/design, before applyingin production.
Alireza Kamrani
