Oracle SQL : Mastering Oracle SQL Performance Tuning

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.

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.

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.

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

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 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.

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.