Oracle SQL : Mastering Oracle SQL Performance Tuning

Explore Oracle storage architecture, including data, index, rollback/undo, and temp data files (DIRT), along with control files, redo log files, and archive log files for reliable database operations.

Explore Oracle background processes and their roles, including ckpt, dbwr, lgwr, arch, checkpoint, smon, and pmon, and how they manage dirty blocks, redo logs, archiving, and cleanup.

Explore Oracle terminology, covering database architecture (datafiles, control files, redo log files), memory structures (SGA, db buffer cache, library cache, result cache), and RAC with CDB/PDB in 12c.

Explore Oracle storage from physical data files to logical structures like table spaces, segments, extents and blocks. Learn how Oracle reads data in blocks and allocates memory in extents.

Learn how execution plans reveal how a SQL statement runs, compare explain plan and auto trace, and see how the optimizer selects the lowest-cost plan using statistics and plan components.

Learn how to generate and view explain plans in Oracle SQL using explain plan and dbms_xplan, covering plan_table, SQL_ID retrieval, and formats like basic, typical, and all.

Learn how Oracle uses table, column, index, and system statistics to estimate query costs. Discover where statistics live in the database, including user_tab, user_tab_histogram, user_tab_statistics, and the stat$ system table.

Learn how Oracle's cardinality feedback compares estimated against actual rows to reoptimize and improve plans, using v$ views and optional hints to enable or disable feedback.

Explore extended statistics in Oracle to fix cardinality mismatches by generating multicolumn statistics and virtual columns, improving query estimates and performance.

Histograms are a special type of column statistics that reveal detailed data distribution, helping Oracle optimize plans by distinguishing skewed values and guiding index versus full scans.

Discover how Oracle indexes speed up data retrieval and reduce input/output, and explore index types such as B-tree and bitmap indexes.

Explore how a B-tree index organizes data into branch and leaf blocks, enabling fast exact and range lookups by pointing to table rows.

Understand descending indexes in Oracle SQL performance tuning: data stored in descending order, and mixed ascending/descending in composite indexes can drive plans and improve performance when recreated in descending order.

Explore how key composite index and prefix compression reduce leaf and branch blocks, save space, and boost performance, with Oracle's analysis guiding optimal compression.

Use a reverse key index to store index keys in reverse order, distributing inserts across leaf blocks to reduce right-hand block contention; it can disrupt key order and range queries.

Learn how a cluster stores joined employee and department data in one data block using the department data key, and how to create a cluster index on that key.

Discover index organized tables, where data lives in the index with leaf blocks ordered by the primary key and non-key columns stored there for fast lookups.

Explore bitmap indexes, their two-dimensional storage and cardinality considerations, and how the degree of cardinality guides when to use bitmap indexes versus b-tree indexes.

Explore how the Oracle optimizer uses access methods to read data via the explain plan, including index scans, fast full scans, and full table scans, and their performance impact.

Discover why a full table scan may be efficient, as the optimizer reads blocks and filters rows. Understand the high watermark and how deletions and fragmentation affect space and performance.

Learn how index full scan reads sorted index data, letting the optimizer avoid sorts by reading leaf blocks; contrast with fast index scan and assess cost.

Learn how the optimizer uses index range scan to fetch a continuous range of index leaf blocks, leveraging selectivity and predicates like between, like, and comparison operators to minimize reads.

Read a specific item from a unique index with index unique scan, returning at most one row, and let the optimizer stop after locating the leaf block entry.

Learn index skip scan for composite indexes and impact of the leading column's distinct values. See a gender and email example on customers and a union all from two scans.

Explore the nested loop join, where the outer table matches each row against the inner table. Indexing join columns speeds up performance and reduces full table scans.

Master the sort-merge join in Oracle SQL: sort both inputs and merge them, a two-phase method that outperforms nested loops on large data but lags behind hash joins.

Explore hash join: build an in-memory hash table from the smaller table, probe the larger one to efficiently join datasets and enhance performance insights.

Learn how the append hint accelerates large inserts by bypassing the buffer cache and reducing redo log, demonstrated with a 2.7 million-row insert and significant runtime improvement.

discover how the create table as select (ctas) combines table creation and data population to boost bulk data moves into staging tables, delivering faster performance and less redo than insert.

Compare union and union all to understand performance and how duplicates are handled. Use union when duplicates may exist, and union all when you are certain all rows are distinct.

Learn to combine multiple scans into one scan by using a case statement to compute aggregates on salary ranges, reducing client-server data transfer and improving performance.

Explore how an expression on indexed columns can hinder query performance and learn to resolve it with a function-based index on the sum of sold and quantity sold.

Compare truncate and delete to see how truncating tables reduces query cost by removing data pages, avoiding reads of empty blocks, and affecting the optimizer.

Avoid unnecessary selection of columns by using star in select clauses. Replacing star with only required columns reduces query time and shifts the execution plan toward index scans.

Compare having and where clauses in a group by. Filtering before grouping with a where clause improves the execution plan and reduces full table scan and index scan.

Use aliases in joins to qualify columns from tab one and tab two, reducing parsing overhead and eliminating column ambiguity, thus improving oracle sql performance.

Explore the bind peeking problem in Oracle SQL, where parameterized queries trigger different execution plans due to bind variables and data skewness, and learn how to overcome it.

Explore how adaptive cursor sharing addresses bind peeking and data skew by generating and selecting execution plans (full table scans and index scans) based on histogram statistics.

Explore how the bind aware hint resolves bind peeking issues by making queries bind aware, enabling adaptive cursor sharing to select correct execution plans for Q1–Q3.