Tagged articles
execution plan
87 articles · Page 1 of 1
This article details eight common SQL anti-patterns — including OFFSET pagination, implicit type conversion, correlated subqueries in updates, mixed sorting, EXISTS clauses, blocked condition pushdown, late filtering, and unoptimized intermediate results — with execution plans and rewrites that reduce query times from seconds to milliseconds.
CTEMySQLPagination
0 likes · 16 min read

This MongoDB cheat sheet guides SQL developers through the mindset shift to NoSQL, covering concept mappings, CRUD operations, indexing, aggregation pipelines, and execution plan analysis with practical code examples for each operation and key pitfalls to avoid.
CRUDMongoDBNoSQL
0 likes · 11 min read

This article details seven common MySQL index failure scenarios—including leftmost prefix violations, function usage on indexed columns, implicit type conversion, leading wildcards in LIKE, OR with non-indexed columns, IS NOT NULL, and NOT IN/EXISTS—with concrete SQL examples showing both failing and optimized queries.
Composite IndexIS NULLIndex Optimization
0 likes · 6 min read


liandkSep 16, 2026 · Databases
This comprehensive guide covers the complete slow SQL troubleshooting lifecycle: enabling slow query logs, interpreting EXPLAIN plans, diagnosing nine common index failure patterns, resolving transaction lock contention, and applying architectural optimizations for large datasets, plus emergency mitigation and long-term governance practices.
Database PerformanceIndex OptimizationMySQL
0 likes · 16 min read

This article provides a comprehensive guide to MySQL's EXPLAIN command, detailing each column in the execution plan output with practical examples to help developers analyze and optimize SQL query performance.
EXPLAINMySQLSQL Performance
0 likes · 8 min read

When a query suddenly slows down tenfold, adding an index often fails to help; the real solution is to read and understand MySQL's execution plan, which reveals how the optimizer works, which indexes are used, and where performance bottlenecks lie, enabling systematic query tuning.
Database PerformanceEXPLAINIndex Optimization
0 likes · 26 min read

This article walks through MySQL's EXPLAIN output column by column, explaining id, table, select_type, type rankings, possible_keys, key, key_len, ref, rows, filtered, and Extra, while demonstrating each concept with concrete SQL examples, JSON‑formatted plans, and SHOW WARNINGS insights.
EXPLAINJSON formatMySQL
0 likes · 20 min read

This article examines eight common SQL pitfalls—including misuse of LIMIT offsets, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, condition push‑down, early limiting, and intermediate result set handling—showing how each can degrade performance and providing rewritten queries with execution‑plan evidence that dramatically improve speed.
MySQLSQLexecution plan
0 likes · 12 min read

This article walks through a MySQL 5.6 case study where a sub‑query that originally took over eight hours is accelerated to a few milliseconds by analyzing the execution plan, adding single‑column and composite indexes, rewriting the query as EXISTS or JOIN, and applying best‑practice indexing techniques.
MySQLQuery PerformanceSQL Optimization
0 likes · 13 min read

The article walks through diagnosing a slow MySQL 8.0 query that scans 77,724 rows and performs a costly filesort, then rewrites it with a LATERAL join and a new index, reducing execution time to about 0.75 seconds on 30 k rows while explaining the differences between ordinary subqueries and LATERAL, and highlighting the need for deterministic ordering when timestamps duplicate.
IndexLATERALMySQL
0 likes · 8 min read

When a core order‑query suddenly slowed from milliseconds to seconds, the team discovered that using PostgreSQL’s EXPLAIN ANALYZE revealed hidden plan regressions, outdated statistics, and index misuse, leading to a systematic, production‑grade methodology for diagnosing and fixing query performance issues.
EXPLAIN ANALYZEPerformance TroubleshootingPostgreSQL
0 likes · 32 min read

This guide explains what PostgreSQL execution plans are, how they are generated, and how to interpret them using EXPLAIN and EXPLAIN ANALYZE, then demonstrates three real‑world case studies that show step‑by‑step optimizations with indexes, configuration tweaks, and query rewrites to dramatically improve query speed.
Database PerformancePostgreSQLSQL Optimization
0 likes · 16 min read

This guide introduces MySQL's EXPLAIN command, demonstrates how to create sample order and customer tables, runs various queries, and provides a detailed walkthrough of each column in the execution plan—including id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, and extra—plus indexing tips and visual examples.
EXPLAINSQLdatabase optimization
0 likes · 7 min read

This article compares the execution‑plan generation, caching, and query‑performance characteristics of Oracle, MySQL, and PostgreSQL, explains how to view plans in each system, and provides practical tips for DBAs to interpret and optimize them effectively.
DBAMySQLOracle
0 likes · 6 min read

This article examines a puzzling MySQL case where the optimizer selects a full‑table scan despite suitable indexes, explains the role of cost constants, execution‑plan analysis, and demonstrates how to force the optimizer to use the optimal index for faster queries.
Index SelectionMySQLSQL Performance
0 likes · 12 min read

A customer observed a SQL statement that runs twice as fast on Oracle as on OceanBase, prompting a detailed analysis of execution plans, leader distribution, and distributed query behavior, followed by practical hint and table‑group optimizations that reduce the runtime from several seconds to sub‑second levels.
Distributed DatabaseOceanBaseSQL Optimization
0 likes · 18 min read

This article explains how using bound (parameterized) queries in SQL Server improves performance by enabling execution plan reuse, reducing compilation overhead and memory usage, while also preventing SQL injection, and discusses potential pitfalls like parameter sniffing and how to mitigate them with OPTION (RECOMPILE).
Parameterized QueriesSQLSQL injection
0 likes · 6 min read


ITPUBOct 10, 2025 · Databases
This article compares how Oracle, MySQL, and PostgreSQL generate, view, and interpret execution plans, highlighting differences in hint support, caching, query efficiency, and providing practical commands and visual examples to help DBAs diagnose and optimize database performance.
DBAMySQLOracle
0 likes · 6 min read

This article explores eight common MySQL performance pitfalls—from inefficient LIMIT clauses and implicit type conversions to suboptimal JOIN updates, mixed sorting, EXISTS usage, condition pushdown, early result limiting, and intermediate result set handling—offering rewritten queries and execution plan analyses that dramatically improve execution times.
Database IndexesMySQLQuery Rewrite
0 likes · 13 min read

This article explains how MySQL's EXPLAIN and EXPLAIN ANALYZE statements work, compares their outputs, and demonstrates through three practical examples how to interpret actual execution costs, row counts, and loop information to identify indexing and query‑plan inefficiencies.
EXPLAINEXPLAIN ANALYZEMySQL
0 likes · 11 min read

This article explains MySQL’s index structures, SQL execution plan analysis, query execution flow, transaction isolation levels, MVCC mechanisms, and InnoDB buffer‑pool caching, providing detailed diagrams and practical examples to help developers optimize database performance.
InnoDBMVCCMySQL
0 likes · 22 min read


ITPUBJun 28, 2025 · Databases
This article explains Oracle's step‑by‑step processing of a SQL statement—including the client submission, parsing checks, optimizer decision‑making, row‑source generation, and final execution—while illustrating hard and soft parses, optimizer components, and practical query‑plan queries.
OptimizerOracleSQL
0 likes · 25 min read

This article analyzes a typical Alibaba interview question that asks for the best index on a table with 27 columns for the query SELECT a, b, c FROM table WHERE d = X ORDER BY e, covering index design, field ordering, comparisons with alternative indexes, index condition pushdown, and the detailed MySQL execution process.
Covering IndexMySQLexecution plan
0 likes · 28 min read

This article investigates a slow OceanBase SQL query by examining CPU and TopSQL metrics, dissecting the execution plan, identifying batch join inefficiencies, rewriting the SQL for better performance, and demonstrating a reduction in execution time from over 2600 seconds to under one second.
Batch JoinDatabase PerformanceOceanBase
0 likes · 18 min read

This article introduces MySQL's tree‑style execution plan output using EXPLAIN FORMAT=TREE, explains its advantages over traditional and JSON formats, and demonstrates its practical use through eight detailed SQL examples with full plan analysis and performance insights.
MySQLSQL OptimizationTree Format
0 likes · 12 min read

This comprehensive guide explains MySQL's underlying index structures (B‑tree, B+‑tree, hash), how execution plans are generated and interpreted, the mechanics of query execution, the role of locks and transaction isolation levels, the MVCC model, and the InnoDB buffer‑pool caching strategy.
Buffer PoolMVCCMySQL
0 likes · 19 min read

This article explains MySQL's layered architecture, how the optimizer generates multiple execution plans, evaluates their I/O and engine costs, and selects the plan with the lowest total cost, illustrated with example queries, cost tables, and JSON output.
MySQLOptimizerSQL
0 likes · 9 min read

This article provides a comprehensive overview of MySQL internals, covering index data structures such as B‑tree and B+‑tree, the differences between MyISAM and InnoDB indexes, SQL execution plan analysis, transaction isolation levels, lock mechanisms, MVCC implementation, and the InnoDB buffer‑pool caching process.
Buffer PoolMVCCexecution plan
0 likes · 19 min read

This article examines ODPS SQL performance by dissecting logical execution plans and Logview visualizations, explaining the underlying principles of various optimization techniques such as multi‑distinct handling, shuffle reduction, system parameters, and different join strategies, and demonstrates how to apply these methods to improve query efficiency in real‑world data engineering tasks.
ODPSShuffleexecution plan
0 likes · 17 min read

This article explains how to use MySQL's EXPLAIN statement to view execution plans, demonstrates creating sample order and customer tables, walks through each column of the EXPLAIN output, and shows how indexes affect query performance, providing a practical reference for database optimization.
EXPLAINIndexMySQL
0 likes · 7 min read

An urgent overnight incident revealed a MySQL server’s CPU soaring to 400% due to poorly written queries, prompting a detailed analysis of execution plans, identification of costly operations like filesort and temporary tables, and concrete recommendations for query and team improvements.
BackendMySQLdatabase
0 likes · 8 min read

This article analyzes the differences between MySQL's full table scan and full primary‑key scan by examining execution plans, source‑code paths, and runtime behavior, demonstrating that both ultimately read data from the primary‑key index and explaining why the optimizer distinguishes them.
InnoDBMySQLPrimary Key Scan
0 likes · 9 min read

This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, and intermediate result handling, and provides concrete SQL rewrites and execution‑plan analyses to dramatically improve query speed.
MySQLQuery RewriteSQL
0 likes · 13 min read


ITPUBMar 12, 2023 · Databases
This article walks through a practical SQL optimization workflow, showing how to detect problematic queries via system metrics and execution plans, analyze table structures and indexes, apply concrete ALTER statements and index changes, and iteratively verify performance improvements, illustrated with a detailed MySQL case study that achieved a twenty‑fold speed increase.
Index OptimizationMySQLSQL
0 likes · 10 min read

The article analyzes why an identical MySQL query runs faster in a QA environment than in DEV, examines execution‑plan differences caused by table driving choices, optimizer settings, and unnecessary conditions, and demonstrates how rewriting the SQL and adjusting hints can achieve a stable, efficient plan.
Database PerformanceIndexMySQL
0 likes · 17 min read

This article explains why database performance monitoring becomes critical as data grows, outlines common optimization techniques, dives into MySQL's storage architecture and index structures, and shares three real‑world cases that illustrate how proper indexing and execution‑plan analysis can prevent severe latency and CPU spikes.
Database PerformanceIndex OptimizationMySQL
0 likes · 15 min read

This article walks through a MySQL performance case study, showing how nested subqueries on large tables can take hours, and demonstrates step‑by‑step how creating single‑column, composite, and covering indexes, as well as rewriting queries as joins, reduces execution time from tens of thousands of seconds to milliseconds.
MySQLSQLexecution plan
0 likes · 12 min read

This article explains MySQL's execution order and presents eight practical optimizations—including smarter LIMIT usage, implicit conversion handling, join‑based updates, mixed sorting, EXISTS rewriting, condition push‑down, early range reduction, and intermediate result push‑down—each illustrated with SQL examples and performance measurements.
MySQLQuery PerformanceSQL Optimization
0 likes · 14 min read

A large‑scale user‑push system suffers from minutes‑long MySQL queries because the IN subquery materializes a temporary table and triggers a semi‑join that forces full table scans, but disabling the semi‑join optimizer or rewriting the query restores sub‑second performance.
MySQLSQL OptimizationSemi-Join
0 likes · 8 min read

This article presents five practical rules—return only needed columns, use proper indexes, avoid subqueries, replace OFFSET pagination with key‑based limits, and understand SQL's logical execution order—to help developers craft efficient SQL statements across major relational databases.
SQLSubqueriesdatabases
0 likes · 13 min read

This article examines why a MySQL query that uses an IN subquery on a massive users table becomes extremely slow, analyzes the execution plan revealing materialized temporary tables and semi‑join optimization, and demonstrates how disabling the optimizer or rewriting the query restores index usage and dramatically improves performance.
MySQLSemi-Joinexecution plan
0 likes · 8 min read

The article analyzes a slow MySQL query that uses an IN subquery on a massive user table, explains how the optimizer’s semi‑join and materialized temporary tables cause full‑table scans, and demonstrates practical fixes such as disabling the semi‑join optimizer switch or rewriting the SQL to regain index usage.
IndexMySQLSQL Optimization
0 likes · 8 min read


ITPUBMar 8, 2022 · Databases
This article explains how PostgreSQL generates execution plans, details the plan tree structure, cost parameters, scan and join methods, and provides practical tips for using EXPLAIN, tuning planner settings, and applying hints to optimize SQL performance.
Join TypesPostgreSQLcost estimation
0 likes · 17 min read

This article presents four real‑world Oracle SQL cases—from a Cartesian join that halted nightly reports to a varchar primary key causing full scans, non‑standard date syntax breaking partition pruning, and missing statistics leading to unstable plans—detailing analysis, execution‑plan insights, and concrete remediation steps.
OracleSQL Optimizationexecution plan
0 likes · 19 min read

This article explains what an execution plan is, how to use the EXPLAIN keyword in MySQL 5.7, interprets each column of the EXPLAIN output—including id, select_type, table, type, possible_keys, key, key_len, rows, and Extra—provides detailed examples of different query scenarios, and shows how optimizer settings like derived_merge affect the plan.
Derived TablesEXPLAINMySQL
0 likes · 20 min read

This article explains the three‑layer architecture of MySQL, how an execution plan is generated and interpreted, and provides a detailed walkthrough of the columns displayed by the EXPLAIN statement to help developers optimize their SQL queries.
EXPLAINMySQLSQL
0 likes · 8 min read

This article analyzes why certain join queries in DBLE distributed database middleware fail to push down to backend nodes, leading to high CPU usage and latency, and presents four concrete examples with step‑by‑step optimizations to ensure full query push‑down and improved performance.
DBLEDistributed DatabaseSQL pushdown
0 likes · 8 min read

This article explains the meaning of each column in MySQL's EXPLAIN output, including id, select_type, type, possible_keys, key, key_len, ref, rows, filtered, and Extra, with examples of various query patterns such as simple selects, joins, subqueries, UNIONs, and derived tables.
EXPLAINMySQLexecution plan
0 likes · 22 min read

This article documents a MySQL slow‑query case where a 5‑million‑row table took over 30 seconds to execute, explores several ineffective optimization attempts, uncovers a client‑side LIMIT issue, and finally resolves the performance problem by forcing the use of the idx_end_time index, cutting the runtime to under a second.
MySQLSQL Optimizationdatabase
0 likes · 5 min read


ITPUBJan 3, 2021 · Databases
This article presents five practical rules for optimizing SQL queries—including selecting only needed columns, ensuring proper indexes, avoiding subqueries, using keyset pagination instead of OFFSET, and understanding the logical execution order—along with concrete examples and execution‑plan analysis for major relational databases.
PaginationSQLexecution plan
0 likes · 14 min read

This article explains how to identify slow‑query symptoms, gather problematic SQL statements, apply common writing and indexing tips, read execution plans, and walk through a complete MySQL optimization case, enabling readers to become proficient database performance engineers.
MySQLSQLdatabase optimization
0 likes · 10 min read

This guide walks you through MySQL's EXPLAIN command, explaining each output column, demonstrating how to read execution plans for simple selects, joins, subqueries, UNIONs, and advanced variants, and showing how to use this information to improve query performance.
EXPLAINMySQLexecution plan
0 likes · 24 min read

This article explains how MySQL's EXPLAIN command reveals the execution plan of a query, detailing each column such as id, select_type, table, type, possible_keys, key, key_len, ref, rows, extra and filtered, while also covering its limitations and a practical case study.
EXPLAINMySQLSQL
0 likes · 14 min read

This article explains how to use MySQL's EXPLAIN statement to view execution plans, describes the meaning of each of the twelve output fields, provides practical examples with sample tables and queries, and offers optimization tips such as index creation and the left‑most prefix rule.
EXPLAINMySQLdatabase
0 likes · 15 min read

An in‑depth investigation shows that counting rows with COUNT(*) on a MySQL table using the PRIMARY key index can be slower than a full table scan, revealing differences in execution plans, optimizer behavior, logical reads, and profiling data that explain the 0.02‑second performance gap.
COUNTMySQLPrimary Key
0 likes · 15 min read

This article explains how MySQL processes a SELECT statement—from client request, cache check, parsing, optimization, and execution—to return results, using a sample User table, a detailed query example, and a step‑by‑step walkthrough of the parser, optimizer, and executor components.
MySQLSQLdatabase
0 likes · 9 min read

The article provides a comprehensive overview of SQL‑on‑Hadoop query engines such as Hive, Impala, Presto and Spark SQL, comparing their runtime frameworks, core components, compilation steps, optimizer strategies, CPU/IO efficiency techniques, storage formats like ORC and Parquet, and resource management in a unified perspective.
Query EngineSQL on Hadoopbig data
0 likes · 24 min read


ITPUBMay 30, 2020 · Databases
This guide walks through Oracle SQL execution steps, optimizer choices, indexing strategies, query rewrites, hint usage, and practical code examples to help developers dramatically improve query performance and reduce database workload.
HintsOracleexecution plan
0 likes · 20 min read

A production MySQL query on a table with five million rows took over 30 seconds, and after reproducing the issue, examining execution plans, trying various index and query tweaks, discovering that SQLyog adds a hidden LIMIT, and finally forcing the correct index, the runtime dropped to under a second, illustrating the importance of proper index usage and tool‑specific quirks.
Index OptimizationMySQLSQLyog
0 likes · 6 min read

This article documents a MySQL slow‑query case where a 5‑million‑row table took over 30 seconds to run a GROUP BY query, explores several ineffective optimization attempts, reveals a client‑side limit issue, and finally solves the problem by forcing the correct index, cutting the runtime to under 0.2 seconds.
Index OptimizationMySQLdistinct vs group by
0 likes · 5 min read

A MySQL 5.6 database with three large tables suffered a 30,000‑second query when searching for students scoring 100 in a specific subject; by analyzing the execution plan, adding indexes on filter columns, and rewriting the query as a join, execution time dropped to under a second.
MySQLSQL Optimizationexecution plan
0 likes · 8 min read


ITPUBJul 17, 2019 · Databases
This article explains how to use MySQL's EXPLAIN and EXPLAIN EXTENDED commands to analyze query execution plans, interpret each column such as id, select_type, type, and extra, and apply practical optimization techniques like indexing, avoiding full‑table scans, and forcing index usage to improve SQL performance.
MySQLSQL Optimizationexecution plan
0 likes · 12 min read

This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and composite indexes, converting subqueries to EXISTS or JOINs, and leveraging index coverage and ordering, with detailed examples and timing results.
Index OptimizationMySQLQuery Performance
0 likes · 12 min read


ITPUBApr 12, 2018 · Databases
This article explains SQL Server's simple and forced parameterization modes, shows when automatic parameterization occurs, illustrates the pitfalls such as parameter sniffing with concrete examples, and provides a practical fix using the OPTION(RECOMPILE) hint to avoid incorrect plan reuse.
SQL Serverauto-parameterizationexecution plan
0 likes · 8 min read


ITPUBAug 30, 2017 · Databases
Learn practical techniques to reduce unnecessary I/O and CPU consumption in SQL queries, including narrowing scan ranges, using partition filters, optimizing joins, leveraging hints, bind variables, and proper use of MERGE, UPDATE, EXISTS/IN, pagination, temporary tables, and other performance‑tuning strategies.
MySQLOracleSQL
0 likes · 8 min read

This article teaches how to read and manipulate Oracle execution plans using hints, SQL rewrites, design features, and statistics tricks, providing step‑by‑step examples, common pitfalls, and practical scripts to achieve predictable performance without costly statistics collection.
HintsOracleSQL
0 likes · 18 min read

This article explains the fundamentals of Oracle SQL execution plans, how to read and interpret them, the role of statistics in plan selection, methods for gathering statistics, various techniques for obtaining execution plans, and practical tips for identifying inefficient SQL statements.
OracleSQLexecution plan
0 likes · 16 min read

This article explains what an Oracle SQL execution plan is, how statistics guide the optimizer, various ways to retrieve plans, the classification of plan types, and practical tips for spotting and fixing inefficient plans using real‑world examples and code snippets.
OracleSQLexecution plan
0 likes · 17 min read

This article walks through a real‑world Oracle SQL tuning case, showing how to dissect a 12‑hour execution plan, fix cardinality and statistics issues, eliminate costly Filter operations, rewrite queries for efficient HASH JOINs, and leverage tools like 10053 and SQLT to dramatically improve performance.
Oracleexecution planstatistics
0 likes · 23 min read

A developer added a seemingly harmless filter to an Oracle SQL statement, causing the optimizer to switch the driving table and join method, which inflated execution time from 2 seconds to 90 seconds, prompting a deep dive into execution plans, indexing, and hint techniques.
Optimizer HintsOracleSQL
0 likes · 12 min read

This article compiles a DBAplus online session that presents a systematic SQL‑optimization methodology, rewrite tricks, common pitfalls, and a series of concrete Oracle examples—including CASE WHEN, ROWNUM pagination, hint usage, column selection, function‑call reduction, trigger avoidance, and ROWID access—plus a detailed Q&A that clarifies practical tuning questions.
Database PerformanceOracleQuery Tuning
0 likes · 19 min read

This article explains how Oracle's bind variable peeking influences execution plan selection, demonstrates the performance differences between full table scans and index scans with skewed data distributions, and provides practical methods—including 10046 events, v$sql_bind_capture, and AWR views—to capture and analyze bind variable values for better SQL tuning.
AWRBind VariablesOracle
0 likes · 14 min read

The article explains why a seemingly harmless delete statement in an Oracle RAC+DG environment can become extremely slow, covering bind variable peek, execution‑plan changes, histogram effects, AWR/ASH analysis, rolling invalidation, and clustering factor, and shows how to diagnose and mitigate each issue.
Bind VariablesClustering FactorOracle
0 likes · 26 min read

After migrating a core system from AIX to Linux and upgrading Oracle from 11.2.0.3 to 11.2.0.4, a critical SQL that previously ran in milliseconds began taking hundreds of seconds, prompting a detailed investigation of execution plans, CBO limitations, and three successive rewrite strategies that restored performance.
CBODatabase MigrationOracle
0 likes · 9 min read


ITPUBMar 10, 2017 · Databases
This article explains SQL Server's logical and physical query execution order, offers practical techniques such as converting loops to batch operations, using temporary tables, optimizing indexes, and avoiding common pitfalls, all illustrated with code samples and performance measurements.
SQL ServerTemp Tablesexecution plan
0 likes · 15 min read

Facing a tight September release deadline, the author tackled two critical Oracle SQL performance issues by analyzing execution plans, replacing a costly view, applying driver‑table hints, removing unnecessary CASTs and a random‑number function, ultimately cutting query time from 11 seconds to under 2 seconds.
OracleSQLdatabase optimization
0 likes · 12 min read


ITPUBFeb 7, 2017 · Databases
This article explains SQL Server’s simple and forced parameterization modes, shows how automatic parameterization can cause plan reuse and inaccurate row‑count estimates, and demonstrates how to detect and resolve these issues using OPTION(RECOMPILE) and proper indexing.
Automatic ParameterizationDatabase PerformanceOPTION(RECOMPILE)
0 likes · 8 min read

When a critical Oracle query suddenly slowed down on regional databases while the central server remained fast, I diagnosed the issue, discovered execution‑plan differences caused by missing predicate push‑down, attempted a plan‑binding solution, faced unexpected failures, and ultimately rewrote the SQL using a WITH clause to meet a tight deadline.
Database PerformanceOraclePredicate Pushdown
0 likes · 11 min read

This article explains what execution plans are, why seemingly identical SQL statements can produce different plans, and offers practical tips—such as standardizing query syntax, simplifying nested queries, using temporary tables, bind variables, appropriate transaction scopes, and careful use of NOLOCK—to improve database performance and avoid common pitfalls.
Bind VariablesSQLSQL Server
0 likes · 13 min read

A real‑world case study shows how a seemingly simple intersect query that took 20 seconds was optimized to 10 seconds by analyzing execution plans, rewriting the SQL, exploring parallel execution, and ultimately redesigning the business logic with a new timestamp column to enable incremental checks.
Business LogicSQLdatabase
0 likes · 10 min read

The article dissects a moderately complex Oracle SQL query from a data‑warehouse, shows how changing a parallel hint forces a full table scan that halves execution time, and then explores partitioning strategies and broader lessons on OLAP performance tuning.
OraclePartitioningSQL
0 likes · 12 min read


ITPUBSep 12, 2016 · Databases
This article explains how Oracle’s Cost‑Based Optimizer can produce incorrect row estimates for queries with multiple column predicates, demonstrates the problem with a test table, and shows that gathering multi‑column statistics resolves the misestimation, improving execution plans.
Multi-Column StatisticsOracleSQL
0 likes · 8 min read

This comprehensive presentation explores SQL’s history, its enduring relevance amid NoSQL and NewSQL trends, explains the execution order and optimizer mechanics, offers practical optimization rules and step‑by‑step execution processes, and illustrates concepts through multiple real‑world case studies and a detailed Q&A.
Case StudiesSQLdatabase optimization
0 likes · 23 min read

SQL Server’s parallel query execution leverages multiple CPUs to accelerate CPU‑bound workloads, and this article explains the underlying hardware prerequisites, scheduler/task/worker model, serial vs parallel plans, exchange operators, parallel joins, CXPACKET waits, and configuration settings such as cost threshold and max degree of parallelism.
CXPACKETDatabase PerformanceParallel Joins
0 likes · 15 min read


ITPUBMay 3, 2016 · Databases
This guide outlines concrete strategies to reduce I/O and CPU overhead in Oracle queries, covering scope reduction, partition pruning, join simplification, hint usage, bind variables, and common pitfalls like Cartesian products and inefficient filters.
JoinsOracleSQL
0 likes · 8 min read

This article explains how to read DB2 execution plans by focusing on data‑access methods and join strategies, walks through real‑world examples of full‑table scans, index scans, various join types, semi‑joins, and join‑order selection, and answers common practitioner questions.
DB2SQL Optimizationexecution plan
0 likes · 17 min read


ITPUBDec 1, 2015 · Databases
A slow Oracle OLTP query that consumed 56% of DB resources and averaged 28 seconds per execution was dramatically accelerated to millisecond response time by forcing the proper index, refreshing stale table statistics, and ultimately creating a cn + c_date composite index.
SQL Optimizationexecution planindexing
0 likes · 5 min read
