Tuning Oracle with Hibernate: 12.5s to 362ms

⚠️ “Query looks simple… but why is it taking 5+ seconds?” This was a production issue that didn’t make sense at first. The API logic was clean. The dataset wasn’t massive. But performance? Terrible. 🔍 So I went deeper into Oracle. 🧠 The real issue was hidden in the execution plan: • Full Table Scans on high-volume tables • Inefficient JOIN strategy (nested loops on large datasets) • Missing indexes on filter columns • High cost due to poor cardinality estimation ⚠️ Impact: • Slow API responses • Increased DB CPU usage • System not scaling under load 🛠️ What I did (step by step): 1️⃣ Analyzed Execution Plan → Used EXPLAIN PLAN and DBMS_XPLAN.DISPLAY → Identified high-cost operations 2️⃣ Index Optimization → Added composite indexes on frequently filtered columns → Eliminated unnecessary indexes affecting writes 3️⃣ Query Refactoring → Reduced complex joins → Replaced subqueries with optimized joins where needed → Applied proper filtering early (predicate pushdown) 4️⃣ Optimizer Improvements → Updated table statistics using DBMS_STATS → Ensured accurate cardinality estimation 5️⃣ Connection & ORM Tuning → Tuned Hibernate/JPA queries to avoid N+1 issues → Optimized connection pooling ⏱️ Result: Query time reduced from 5+ seconds → under 500ms ✅ Significant performance gain ✅ Reduced DB load ✅ Improved scalability 💡 Key Takeaway: Performance tuning isn’t about guessing… it’s about understanding how the database thinks. #Oracle #SQL #PerformanceTuning #Java #SpringBoot #Database #Backend #Softwa

WHERE and HAVING, NOT IN and NOT EXISTS do similar things in SQL, but when should you use which? WHERE and HAVING: The main difference WHERE → works on individual rows. It is applied before GROUP BY — it filters the ‘raw’ data. HAVING → works with the results of aggregate functions (COUNT, SUM, AVG, …), in simpler terms, with groups. It is applied after GROUP BY — filtering already aggregated groups. When to use: WHERE → whenever possible (faster, reduces the volume of data in advance) HAVING → only when filtering by aggregates or by GROUP BY results is required Together, when WHERE comes before GROUP BY and HAVING comes after. NOT IN and NOT EXISTS: The main difference NOT IN → comparison with a list. Checks that the value is not in the list. But there is a critical nuance: if there is at least one NULL in the subquery, the result may be empty. The reason is the peculiarities of SQL’s three-valued logic (TRUE / FALSE / UNKNOWN). May be less efficient on large datasets. NOT EXISTS → checking for the existence of rows Checks that no row exists that satisfies the condition. Not affected by NULL values. Often optimises better (especially with indexes). When to use: Use NOT EXISTS by default if there is a subquery, NULL values are possible, and reliability is important. You can use NOT IN if you are 100% certain there are no NULL values and the list of values is simple. #Java #Backend #Developer #JavaDevelompent #Software #Programming #SQL #Relational #Database #DB #PostgreSQL #Oracle #MySQL #MariaDB

🚀 Bank Management System | Database Integration with MySQL In this video, I am showing how my Spring Boot application is connected to the MySQL database. The project uses two main tables: Account table to store account details like name and balance Transaction table to store all operations such as deposit, withdrawal, and transfer All data is automatically stored and retrieved using Spring Data JPA and Hibernate without writing manual SQL queries. Whenever an operation like deposit or transfer is performed, the changes are reflected in the database immediately. This video explains how backend applications interact with databases and ensure proper data storage and consistency. #MySQL #Database #JPA #Hibernate #SpringBoot #BackendDevelopment

🤯 𝙋𝙧𝙚𝙥𝙖𝙧𝙚𝙙𝙎𝙩𝙖𝙩𝙚𝙢𝙚𝙣𝙩 𝙞𝙣 𝙅𝘿𝘽𝘾 — 𝙒𝙝𝙮 𝙬𝙖𝙨 𝙞𝙩 𝙞𝙣𝙩𝙧𝙤𝙙𝙪𝙘𝙚𝙙 ? We already have Statement in JDBC Then why do we need PreparedStatement? Let’s understand the real reason 👇 Problem with Statement 📃 𝐖𝐡𝐞𝐧 𝐰𝐞 𝐮𝐬𝐞 "𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭" ? Statement st = con.createStatement(); st.executeQuery("SELECT * FROM users WHERE id = " + id); ❌ Query is created every time ❌ Compilation happens again and again ❌ Vulnerable to SQL Injection ⚠️ 𝐖𝐡𝐲 𝐏𝐫𝐞𝐩𝐚𝐫𝐞𝐝𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭 𝐢𝐬 𝐢𝐧𝐭𝐫𝐨𝐝𝐮𝐜𝐞𝐝? ➡️ To solve these problems: ✔ Improve performance ✔ Prevent SQL Injection ✔ Handle dynamic values safely ⚙️ 𝐇𝐨𝐰 𝐏𝐫𝐞𝐩𝐚𝐫𝐞𝐝𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭 𝐰𝐨𝐫𝐤𝐬 PreparedStatement ps = con.prepareStatement( "SELECT * FROM users WHERE id = ?" ); ps.setInt(1, id); ResultSet rs = ps.executeQuery(); 🔍 What happens internally? ➡️ Query is precompiled once ➡️ Only values are replaced at runtime ✔ Faster execution ✔ Safer queries 🔐 🔐 SQL Injection Prevention ❌ Statement: SELECT * FROM users WHERE id = '1 OR 1=1' Can break your database SELECT * FROM users WHERE id = '1 OR 1=1' Can break your database 📝 PreparedStatement: Treats input as data, not SQL ➡️ Prevents injection ✅ 💭 One Line Summary 🖇️PreparedStatement = Precompiled + Secure + Efficient Have you used PreparedStatement in your projects? 🤔 Or still using Statement? Let’s discuss 👇💬 #Java #JDBC #PreparedStatement #JavaDeveloper #BackendDevelopment #Programming #TechJourney #LearnBySharing #Database #InterviewPrep

I’ve been diving into Oracle → PostgreSQL migration from a backend engineer’s perspective (Java/Spring Boot). One thing became clear very quickly: this isn’t just a database migration The more I explore, the more I see how tightly coupled application logic is with database behavior. What looks like a simple DB switch on the surface starts revealing deeper challenges at the application layer: • Oracle-specific functions like NVL don’t directly translate (→ COALESCE) • Pagination logic using ROWNUM needs to be redesigned using LIMIT/OFFSET • Sequence handling vs auto-increment changes how inserts and IDs are managed • Query execution plans differ significantly, impacting performance even when the logic stays the same • ORM layers (like Hibernate) require dialect and ID strategy adjustments • Datasource configurations, connection pooling, and transaction handling also need careful tuning But beyond syntax, the real challenge lies in assumptions: Many backend systems are built around how Oracle behaves from query structure to performance expectations. When the database changes, those assumptions break. It forces you to: • Revisit how queries are written • Understand how the ORM/JDBC layer interacts with the database • Rethink performance tuning strategies instead of relying on previous optimizations Still early in this journey, but it’s already clear that understanding these nuances at the backend layer is just as critical as the migration itself. #BackendDevelopment #Java #SpringBoot #PostgreSQL #Oracle #DatabaseMigration #SoftwareEngineering #TechLearning #SystemDesign #Developers

🔹 Rethinking Performance in PL/SQL Most developers start with row-by-row processing. It works. It’s simple. It’s easy to write. But at scale, it quickly becomes a bottleneck. That’s where BULK COLLECT and FORALL make a difference. By reducing context switching between SQL and PL/SQL engines, bulk processing helps handle large datasets faster, improves scalability, and keeps code clean and efficient. PL/SQL teaches an important mindset shift: don’t just make it work , make it scale. In real systems, data keeps growing, users keep increasing, and performance expectations never stop. Better code isn’t longer code , it’s smarter, intentional, and built for performance. 👉 Do you still use row-by-row processing, or have you switched to bulk operations? #PerformanceTuning #SQL #TechCareers #Programming #OracleDeveloper #PLSQL #OracleAPEX #CareerGrowth #DatabaseDevelopment #TechJourney #C2C #C2H

Hidden performance bomb with Spring Boot that could be in your project. It appear in my code base recently as I forget small JPA/Hibernate detail and paid for this - extra long processing. I'm sharing this two code snippets - one problem - different solutions one in configured by default Spring Data JPA and other with Spring Data JDBC. Many of people argued Spring Data JPA because of performance, but relatively it's good but not for all of the cases. I write for two methods simple performance tests that do the same logic. - Spring Data JPA - 1205 ms - Spring Data JDBC - 167 ms The same local PostgreSQL instance, dropped tables before running tests. No matter how much time I ran tests - JPA always dramatically slower than JDBC. Take a look on the picture and try to figure it out what the root cause of the problem, not saying the problem is in using Spring Data JPA 😄

🚀 Day 3 – Learning JDBC (PreparedStatement Interface) Today I explored PreparedStatement, which is a more secure and efficient way to execute SQL queries in JDBC. 📌 Here are the concepts I learned: 1️⃣ Creating PreparedStatement Used Connection object to create PreparedStatement: ➡️ PreparedStatement ps = con.prepareStatement(query); 2️⃣ Using Placeholders (?) Wrote parameterized queries: ➡️ select * from Student where email=? and password=? 3️⃣ Setting Values Dynamically Assigned values using methods: ➡️ ps.setString(1, email); ➡️ ps.setString(2, password); 4️⃣ Executing Query Executed query using: ➡️ ResultSet rs = ps.executeQuery(); 5️⃣ Validating Data (Login Example) Used rs.next() to check if user exists and display result 💡 Key Learning: PreparedStatement helps prevent SQL Injection and improves performance by pre-compiling SQL queries. 🔥 Excited to explore next: ➡️ Batch Processing ➡️ Transactions in JDBC ➡️ Real-time project implementation #Java #JDBC #PreparedStatement #BackendDevelopment #FullStackDevelopment #LearningJourney #MySQL #Day3

weblogic-rest-openapi v0.4.0: https://lnkd.in/etfahpRM The first version was hand written: curl against two WebLogic instances. It covered serverRuntimes, JDBC, threading, JVM, basic edit tree. Oracle's Remote Console is open source. It contains a weblogic-bean-types/ directory with one YAML per MBean per version. Five versions, ~850 files each. Names, properties, types, descriptions, defaults, enums, containment. Harvested by introspection, published on GitHub, never as standard OpenAPI. I built a Python transformer that consumes those YAMLs and produces OpenAPI 3.0. Plus a manual overlay layer for what the YAMLs don't carry: operational quirks, enriched descriptions for the most-used subsystems, real samples, nullability fixes from validating those samples, and manual bodies for 12 polymorphic subtypes Oracle declares in UI overlays but not in harvested. v0.4.0: five generated specs (12.2.1.3, 12.2.1.4, 14.1.1, 14.1.2, 15.1.1). 600-660 schemas per version, 1100-2400 paths, validated with no warnings. Things found along the way: 1248 REST paths disappear between 12.2.1.4 and 14.1.1. Multi-Tenant deprecation. Scripts against domainPartitionRuntimes or resourceGroupLifeCycleRuntimes will break on upgrade. 222 new properties between 14.1.1 and 14.1.2. The three buzz* cluster transport fields are gone. Virtual threads and self-tuning thread pool. Java 21. 20 fields where the harvested schema says non-null but the live API returns null. Found by validating samples against schemas. Schema corrections, not workarounds. #WebLogic #OracleWebLogic #Middleware #OpenAPI

🚨 Production Issue That Wasn’t a Code Bug! Recently hit a confusing issue where everything was working perfectly on my local environment, but the same feature failed on the client side. Initial reaction? “Did I miss something in the code?” 🤔 After digging deeper, the reality was different… 💡 It was NOT a code issue. The root cause was a difference in MySQL configuration/version between environments. 👉 Locally, the query worked fine 👉 On the client side, it failed with a strict SQL error Reason? The client environment had a stricter SQL mode enabled (ONLY_FULL_GROUP_BY), which enforces tighter rules on how grouped queries should be written. Sometimes: Your code is correct Your logic is correct Your testing is correct But still, things break… ➡️ Because of environment differences (DB config, version, SQL modes) 💭 Takeaways ✔ Never assume local = production ✔ Always check database configurations across environments ✔ Understand how strict modes like ONLY_FULL_GROUP_BY impact queries ✔ Write queries that are unambiguous and compliant This was a great reminder that debugging isn’t always about fixing code — sometimes it’s about understanding the system around it. #MySQL #BackendDevelopment #Debugging #ProductionIssues #SpringBoot #SoftwareEngineering #SpringBatch #Databases