Oracle Apps DBA

Oracle Index Skip Scan - Practical Deep Dive Oracle Performance Series Index Skip Scan How Oracle can use a composite index without the leading column, when that helps, and when it does not Index skip scan is one of those optimizer features that is often summarized too loosely. It does not make column order unimportant, and it does not mean every predicate on a nonleading column will suddenly benefit from a composite index. What it does mean is that Oracle has an extra access path in specific cases: when the leading column is absent, the later column is selective enough, and the leading column has relatively few distinct values. Leading key absent Core prerequisite Few distinct prefixes Why it can work Later key selective Why it can be worth it Still cost-based Not a guarantee Table of contents 01. Mental model What skip scan actually is 02. Optimizer criteria When Oracle considers it 03. How it works Logical subindexes and why distinctness matters 04. When it helps Good fits...

Oracle PLAN_TABLE - Practical Deep Dive Oracle Performance Series PLAN_TABLE What EXPLAIN PLAN stores, how DBMS_XPLAN reads it, and where plan analysis goes wrong PLAN_TABLE is the staging area Oracle uses for EXPLAIN PLAN output. That makes it foundational, but also easy to misuse. A plan stored in PLAN_TABLE shows what the optimizer estimated for the explained statement. It does not prove what a live cursor actually did at runtime. To use it well, you need to understand the table’s role, how DBMS_XPLAN formats it, how to read the tree, and when to move from explained plans to cursor-based evidence. EXPLAIN PLAN Writes plan rows here DBMS_XPLAN Formats and displays Estimated, not executed Critical distinction Cursor stats matter For runtime truth Table of contents 01. Mental model What PLAN_TABLE is and is not 02. Anatomy The columns that matter most 03. Explain-plan workflow How statements land in PLAN_TABLE 04. DBMS_XPLAN formats DISPLAY, DISPLAY_CURSOR, and output cho...

By Gowthami | apps-dba.com | Oracle Performance Series Oracle Parallel Query (PQ) allows a single SQL statement to be executed by multiple parallel execution servers simultaneously, dramatically reducing elapsed time for large data operations. It is the primary tool for accelerating full table scans, large sorts, and bulk DML on multi-CPU systems. Key Insight: Parallel query trades throughput for response time. A query using DOP 8 uses 8x the CPU and I/O resources but may run 6-7x faster. Use parallelism for batch jobs and large reports — not for OLTP queries where resource contention hurts all users. Parallel Query Architecture A parallel query uses a Query Coordinator (QC) — the session that submits the query — and multiple Parallel Execution Servers (PX servers) that perform the actual work. The QC distributes work, collects results, and returns them to the user. -- Check current parallel configuration SHOW PARAMETER parallel_max_servers; -- max PX servers in pool SHOW PAR...