Summary of Key Wait Events in Non-RAC and RAC

Category Non-RAC Wait Events RAC Wait Events

I/O Waits db file sequential read, db file scattered gc buffer busy, gc cr request, gc
read, direct path read current block

CPU and Memory latch free, buffer busy waits, PGA memory gc current block busy, gc cr
Waits block

Concurrency Waits enq: TX - row lock contention gc buffer busy, global enqueue

Network Waits SQL*Net message from/to client gc buffer busy, gc current block

Other Waits log file sync, log file parallel write gc cr write, buffer busy waits

Interconnect Waits N/A gc cr block, gc cr write

Summary of Key Oracle Lock Types:

Lock Type Purpose Acquired During

TX (Transaction Row-level lock to manage access to specific

UPDATE, DELETE, INSERT
Lock) rows.

TM (Table Metadata Table-level lock to prevent structural

ALTER, TRUNCATE, DROP
Lock) changes.

Prevents all other operations on the locked

X (Exclusive Lock) UPDATE, DELETE, INSERT
resource.

Allows multiple transactions to read but not

S (Share Lock) SELECT FOR UPDATE
write.

Allows read-only operations but prevents

RS (Row Share Lock) SELECT FOR UPDATE
updates.

SRX (Share Row Allows inserts but prevents updates or INSERT while preventing updates
Exclusive) deletes. or deletes

Acquired during multi-level lock

Indicates intent to acquire more restrictive
IS/IX (Intent Locks) acquisition (e.g., table-level
locks.
locks)

Key RAC Wait Events

In a RAC (Real Application Clusters) environment, additional wait events related to inter-instance
communication and distributed transactions come into play. Below are key RAC wait events:
1. Global Cache (GC) Wait Events: These are the most important RAC-specific wait events,
related to the Global Cache Service (GCS) and Global Enqueue Service (GES), which manage
data consistency and coordination between RAC nodes.
o gc buffer busy: Waits for a buffer that is being modified by another instance. This
typically happens when an instance is waiting for a data block held in another node’s
buffer cache.
o gc current block: Waits for a block that is currently being modified (exclusive lock).
o gc cr request: Waits for a consistent read copy of a block from another instance.
 o gc cr block: Waits for a consistent read block that is being fetched from another
instance.
o gc buffer busy: Waits for a buffer in the global cache that is being modified by
another instance.
2. Global Enqueue (GES) Wait Events:
o ges enqueue: Waits for a global enqueue on a resource in the cluster (e.g., for locks
held by another instance).
o ges transaction: Waits for a transaction-related lock.
3. Interconnect Wait Events: The interconnect network is the backbone of communication
between RAC nodes, and its performance can have a huge impact on RAC performance.
o gc current block busy: Waits for the current block in the global cache while other
instances are modifying it.
o gc cr write: Waits for the transmission of a consistent read (CR) block across the
interconnect.
4. Redo and Log File Wait Events:
o log file sync: In a RAC environment, this event waits for the redo log to be written to
disk and acknowledges the commit.
o log file parallel write: Occurs when the redo logs are written in parallel to disk.
5. Other Key RAC Wait Events:
o buffer busy waits: In RAC, this wait occurs when two or more instances are trying to
access the same block simultaneously, often involving global cache.
o global enqueue: Waits for an enqueue to be released by another instance.
o cr block: A block from another node is being requested in consistent read mode,
which can result in a wait.
RAC Wait Events, Actions to Resolve, and Relevant Oracle Views
RAC Wait Description Action to Resolve Relevant Oracle Views
Event
gc buffer Waits for a buffer that is - Optimize the V$WAITSTAT,
busy being modified by another application to reduce V$SESSION_WAIT,
instance. Common when contention for the same V$GC_STATS
two instances are trying to blocks.
access the same block. - Increase buffer cache
size to reduce frequent
data block swaps.
- Use local undo
tablespaces to minimize
undo contention.
gc current Waits for the current block - Consider re- V$SESSION_WAIT,
block that is being modified partitioning data to V$WAITSTAT,
(exclusive lock). Occurs reduce the likelihood of V$INSTANCE_RECOVERY
when one instance has concurrent access.
exclusive access to a block. - Use application-level
data locking to ensure
better distribution of
access.
gc cr Waits for a consistent read - Optimize queries to V$SESSION_WAIT,
request copy of a block from reduce the number of
 another instance. Typically consistent read V$GC_STATS, V$WAITSTAT
occurs during read-only requests.
queries. - Review the indexing
strategy to ensure
queries are more
efficient, minimizing
block transfers.
gc cr block Waits for a consistent read - Review the query plan V$GC_STATS,
block from another and check if indexes are V$SESSION_WAIT,
instance. Similar to gc cr being used efficiently. V$WAITSTAT
request, but a more - Consider in-memory
general term. databases to reduce the
need for consistent
reads in RAC setups.
gc current Waits for a block that is - Optimize partitioning V$SESSION_WAIT,
block busy currently being modified so that the data is less V$GC_STATS, V$WAITSTAT
by another instance. The likely to be modified
block is currently being simultaneously by
written to or changed. multiple instances.
- Consider reducing DML
contention with the use
of parallel DML or row-
level locking.
ges Waits for a global enqueue - Ensure that lock V$GES_STAT,
enqueue on a resource (lock) in the management is efficient V$SESSION_WAIT,
cluster. A global enqueue by avoiding excessive V$WAITSTAT
ensures that a particular locking.
resource is only accessed - Use partitioned tables
by one instance at a time. and distributed locking
mechanisms to reduce
contention.
ges Waits for a transaction- - Review transaction V$SESSION_WAIT,
transaction related lock in the cluster. sizes and break large V$GES_STAT, V$WAITSTAT
Often related to the Global transactions into smaller
Enqueue Service (GES), ones to reduce
which is responsible for contention.
transaction consistency - Ensure that commit
across RAC nodes. operations are evenly
distributed across RAC
instances.
gc cr write Waits for the transmission - Optimize network V$GC_STATS, V$WAITSTAT,
of a consistent read (CR) configuration to reduce V$SESSION_WAIT
block across the interconnect latency.
interconnect. Can indicate - Ensure that the
high interconnect latency interconnect bandwidth
or congestion. is sufficient to handle
the volume of data
transfer between RAC
nodes.
- Monitor interconnect
latency using Oracle's
netstat and v$gc_stats
views.
log file sync Waits for the redo log to - Optimize redo log V$LOG, V$SESSION_WAIT,
be written to disk and configuration by
 acknowledged. In a RAC ensuring that V$WAITSTAT
environment, this occurs synchronous commit is
when redo is synchronized minimized and redo
across instances. writes are appropriately
distributed.
- Ensure log file
parallelism and that all
RAC nodes have access
to fast disk systems for
redo log writes.
log file Occurs when multiple RAC - Increase redo log V$LOG, V$SESSION_WAIT,
parallel instances write redo log group sizes to handle V$WAITSTAT
write data in parallel. It waits for larger writes efficiently.
the parallel write to disk to - Tune disk I/O to
complete. handle the parallel
writes more effectively.
global Waits for an enqueue on a - Use local undo V$GES_STAT,
enqueue resource in the global tablespaces to reduce V$SESSION_WAIT,
cache (shared resource). global undo contention. V$WAITSTAT
This can occur when - Partition tables
multiple instances are properly to avoid
trying to acquire locks on multiple instances
the same resource. accessing the same
blocks or rows
simultaneously.
buffer busy Occurs when multiple RAC - Increase buffer cache V$SESSION_WAIT,
waits instances try to access the size to reduce V$WAITSTAT,
same buffer (memory contention. V$BUFFER_POOL
page). This can cause - Use automatic block
delays when a block is management (ABM) for
being read from the cache. better buffer
management.
- Optimize application
logic to avoid concurrent
access to the same
blocks.
gc cr block Waits for a block that is - Minimize cross- V$GC_STATS, V$WAITSTAT,
being read in consistent instance queries by V$SESSION_WAIT
read mode (CR) in a properly distributing
different RAC instance. workload across RAC
instances.
- Use local undo to
reduce contention when
modifying data in
different instances.

Explanation of Key Views Used for Monitoring RAC Wait Events

1. V$WAITSTAT: Displays statistics on wait events for all sessions. It helps to track and monitor
wait statistics at the instance level.
2. V$SESSION_WAIT: Provides detailed session-level information about the current wait events,
helping you to diagnose issues related to specific sessions.
3. V$GC_STATS: Displays Global Cache statistics, specifically related to Global Cache and Global
Enqueue operations, which are crucial in RAC environments.
 4. V$GES_STAT: Displays statistics on Global Enqueue Service (GES) operations, which are
involved in managing enqueues across RAC nodes.
5. V$LOG: Provides information on redo log activity, crucial for diagnosing redo log
synchronization and write-related wait events in RAC.
6. V$BUFFER_POOL: Shows statistics about buffer pool usage and helps you understand buffer
cache performance in relation to wait events such as buffer busy waits.

Oracle RAC (Real Application Clusters) involves multiple resources that need to be monitored for
proper functioning and performance. Below is a detailed table listing key Oracle RAC resources and
their corresponding Oracle views, procedures, functions, or packages used to monitor their status.
Oracle RAC Resources and Corresponding Monitoring Views/Procedures/Functions/Packages
RAC Resource Oracle Description
View/Procedure/Function/Package for
Monitoring
Instance Status V$INSTANCE Provides status of the Oracle
instance, including whether it is
open, mounted, or down.
Cluster V$CR_BLOCKED, Monitors cluster interconnect
Interconnect (network) issues and global
Status V$GC_STATS, cache statistics.
Global Cache (GC) V$GC_STATS, Provides statistics on global
Status cache performance, such as
V$CACHE block transfers, misses, and
waits.
Global Enqueue V$GES_STAT, Monitors global enqueue service
Service (GES) (GES), which manages locks
V$SESSION_WAIT across RAC nodes.
Global Resource V$GCS_RESOURCE Displays information about
Directory (GRD) resources managed by Global
Cache Service (GCS).
Redo Log Sync V$LOG, V$SESSION_WAIT, Monitors redo log
V$LOG_HISTORY synchronization activity across
RAC instances.
Shared Memory V$SGA Provides statistics about the
(SGA) shared memory, including buffer
cache, redo log buffer, and other
SGA components.
Clusterware V$CLUSTER Displays information about
Status Oracle Clusterware status (such
as whether the node is part of
the cluster).
Disk Group (ASM) V$ASM_DISKGROUP, V$ASM_DISK, Monitors Oracle ASM disk
V$ASM_FILE groups and disks for status,
usage, and performance.
Instance Alert V$DIAG_INFO Provides diagnostic information,
Logs including alert logs for instances
 in the RAC setup.
RAC Service V$SERVICE Monitors database services and
their status in the RAC
environment.
Instance Recovery V$INSTANCE_RECOVERY Provides information related to
instance recovery in the RAC
setup.
Log Writer (LGWR) V$LOG Provides status on the log writer
process, specifically its activity
related to redo logs and log file
synchronizations.
Instance Waits V$SESSION_WAIT, V$WAITSTAT, Provides session-level and
V$SYSTEM_EVENT system-level wait events, helping
to analyze instance resource
contention.
Enqueue Locks V$GES_ENQUEUE Provides information on
enqueue lock contention and
usage across RAC instances.
Cluster Cache V$CACHE, V$CACHE_TRANSFER, Monitors cache fusion activity
Fusion V$GLOBAL_CACHE_STAT across RAC instances.
Data Guard V$DATAGUARD_STATUS, Monitors Data Guard status and
V$ARCHIVED_LOG log apply activity, including the
standby database.
Process/Session V$SESSION, V$PROCESS, Monitors individual processes
Information V$SESSION_WAIT, V$BACKGROUND and sessions, as well as the
background processes of RAC
nodes.
File System Status V$ASM_FILE, V$FILESTAT, V$DATABASE Monitors status and
performance of file systems and
tablespaces in the RAC system.
Locking V$LOCK, V$GES_STAT Monitors lock contention in the
Mechanism RAC environment (global
enqueues and local locks).
SQL Performance V$SQLAREA, V$SQL_PLAN, V$SQLSTATS Monitors SQL execution
in RAC performance in the RAC
environment, including plan
execution times and resource
usage.
Redo Transport V$ARCHIVE_DEST, Provides information about the
V$ARCHIVE_DEST_STATUS, status of redo log transport
V$DATAGUARD_STATUS across RAC instances, especially
for Data Guard setups.
Cache Fusion V$GLOBAL_CACHE_STATS, Monitors global cache fusion
V$GLOBAL_CACHE_TRANSFER, V$CACHE (GCF) and its transfer statistics in
a multi-node RAC environment.
Flashback V$FLASHBACK_LOG Provides status and statistics of
the flashback technology,
especially important for disaster
recovery.
 Instance Failover V$INSTANCE_FAILOVER, Monitors instance failover
V$CLUSTER_DATABASE activity, and provides cluster
database health status.
Dynamic Database V$SERVICE_STATS, V$SERVICE Provides the status of dynamic
Services services, which can be moved or
relocated between nodes in a
RAC setup.
Node Failures V$NODE_FAILURE Monitors node failure status,
including details of a node going
down or being recovered in the
cluster.
Interconnect V$INTERCONNECT Monitors the latency between
Latency Oracle RAC instances connected
via the interconnect.

Key Oracle Views Explained

 V$INSTANCE: Provides instance-level status information like startup mode, instance name,
and state of the instance (open/mounted).
 V$CR_BLOCKED: Displays statistics about cluster resource blocking, especially related to
global cache and block contention.
 V$GC_STATS: Contains information on Global Cache (GC) statistics and provides insight into
the number of blocks transferred between RAC nodes.
 V$GES_STAT: Displays statistics on global enqueue services, which manage locks across RAC
nodes. Useful for monitoring global locks and enqueues.
 V$ASM_DISKGROUP: Gives details on Oracle ASM disk groups, including their status, space
usage, and failures.
 V$ARCHIVE_DEST_STATUS: Monitors redo log archiving status and error reporting for Data
Guard and RAC environments.
 V$LOG: Provides information related to the status and activity of redo log groups and log
writes.
 V$SERVICE: Displays details of database services and their status in a RAC configuration,
helping with service availability and performance.
 V$SESSION_WAIT: Provides real-time information on wait events for sessions, helping in
diagnosing performance issues like I/O bottlenecks.
 V$DATABASE: Shows information on the database, such as whether it is open or in mount
state, providing the current database status.

how you can read and understand an execution plan and check for performance issues beyond the
cost. I'll provide a detailed nested SQL example and explain how to interpret the execution plan using
different scenarios like full table scans, index scans, nested loops, joins, sorting, and more.
Steps to Analyze Execution Plan:
1. Check the Execution Order: The execution plan is a tree, and the order of operations starts
from the leaf nodes (most granular operations like full table scans or index scans) and goes
up to the root node (final result set).
 2. Check Access Methods:
o Full Table Scan (FTS): Indicates a full scan of the table, which can be inefficient for
large tables.
o Index Scan: Using an index to retrieve data. This is faster than a full table scan, but it
depends on the selectivity of the index.
o Index Range Scan: A more efficient version of an index scan where only a subset of
index keys are retrieved.
o Index Unique Scan: Efficiently retrieves a single row based on a unique index.
3. Look at Join Methods:
o Nested Loop Join: Good for small result sets and index lookups.
o Merge Join: Typically used when both sides are sorted and when large result sets are
involved.
o Hash Join: Used for large datasets, particularly when the join condition is non-equi.
4. Look for Expensive Operations:
o Sorts: Sorting operations (especially large sorts) can be expensive and may require
temp space.
o Filters/Predicates: Conditions that filter out rows. If they are applied after expensive
joins, the cost will be higher.
5. Look for Missing Indexes: If there's a full table scan, it could indicate that an index could
improve the performance.
Sample Execution Plan:
Let’s break down the execution plan for this query. You can get the execution plan by running the
following:
EXPLAIN PLAN FOR
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.salary > 50000
ORDER BY e.last_name;

Here’s a simplified example of the execution plan (you would see more details in the real output):

Operation Options Object Name Cost Cardinality Bytes

SORT ORDER BY 20 1000 5000

NESTED LOOPS 15 1000 5000

TABLE ACCESS FULL EMPLOYEES 10 1000 5000

TABLE ACCESS BY INDEX ROWID DEPARTMENTS 5 1000 5000

INDEX RANGE SCAN IDX_DEPT_ID 5 10 100

Explanation of Execution Plan:

 Step 1 (Order By - Sorting):
o Operation: SORT ORDER BY
 o This step sorts the results by e.last_name. Sorting requires temporary space and can
be expensive if the result set is large. The cardinality of 1000 means it will sort 1000
rows.
 Step 2 (Join - Nested Loops):
o Operation: NESTED LOOPS
o A nested loop join is used to match rows from employees with the corresponding
departments. This means that for each row in employees, Oracle is performing a
lookup in the departments table (via index or rowid). Nested loops are good for
small datasets, but they can become inefficient if there are many rows in employees.
 Step 3 (Accessing Employees - Full Table Scan):
o Operation: TABLE ACCESS FULL
o The employee’s table is accessed using a full table scan (FTS), which indicates that
Oracle is scanning the entire table to retrieve the rows where salary > 50000. FTS can
be slow, especially with large tables. An index on salary might improve this part.
 Step 4 (Accessing Departments - Index Scan):
o Operation: TABLE ACCESS BY INDEX ROWID
o Oracle uses an index range scan (IDX_DEPT_ID) on the departments table to
efficiently find the matching rows based on department_id. This is faster than a full
table scan, as it uses the index to directly locate the row corresponding to each
department_id.

To provide a comparison of the execution plans for the different approaches I shared (e.g., traditional
join, EXISTS, IN, WITH, CROSS JOIN, LEFT JOIN), I will explain how to analyze the execution plans in
Oracle for each query type. Then, I will describe the likely execution plan differences and
performance implications.
1. Execution Plan for Traditional Join
SQL:
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 50000;

Execution Plan:
When you use a standard JOIN, Oracle will most likely perform a Nested Loops Join or Hash Join,
depending on the data distribution and indexes. The execution plan might look like this:

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

|-----|-------------------------|-------------------|-------|-------|------------|----------|-------|-------|
| 0 | SELECT STATEMENT | | 100 | 2000 | 10 (10)| 00:00:01 |
| 1 | NESTED LOOPS | | 100 | 2000 | 10 (10)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 100 | 1000 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 100 | 800 | 5 (20)| 00:00:01 |
| 4 | INDEX RANGE SCAN | DEPT_ID_INDEX | 100 | 2000 | 5 (20)| 00:00:01 |

 Nested Loops is often used when the join condition is indexed, and the outer query fetches a
small result set.
 Cost represents the relative cost of the operation. The lower the cost, the better.
2. Execution Plan for EXISTS
SQL:

SELECT e.employee_id, e.first_name, e.last_name

FROM employees e
WHERE e.salary > 50000
AND EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id);

Execution Plan:
In the case of EXISTS, Oracle will likely use a Semi Join or Correlated Subquery. Here's how the
execution plan might look:

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |

|-----|-------------------------|-------------------|-------|-------|------------|----------|-------|-------|
| 0 | SELECT STATEMENT | | 100 | 1000 | 8 (10)| 00:00:01 |
| 1 | TABLE ACCESS FULL | EMPLOYEES | 100 | 1000 | 8 (10)| 00:00:01 |
| 2 | FILTER | | | | | | | |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 100 | 800 | 3 (20)| 00:00:01 |

 Semi Join indicates that the subquery will return rows only once it finds a match, reducing
unnecessary processing.
 Filter is used for subquery checks and will stop once it finds a match.
You're correct to point out that when using the EXISTS clause, Oracle may not necessarily choose
Hash Join, Merge Join, or Nested Loops Join depending on the query structure and data distribution.
Specifically, Full Table Scan (FTS) might be preferred, even if the columns involved in the query are
indexed.
Let's break down the reasoning behind this choice and the behavior of the EXISTS clause:

How the EXISTS Clause Works:

The EXISTS clause is typically used to check if a subquery returns at least one row for each row in the
outer query. It doesn't care about the actual data returned by the subquery but only checks for
existence. This behaviour influences the query plan and can result in the following:
 Correlated Subquery: When using EXISTS, Oracle executes the subquery for each row of the
outer query. Since it's evaluating the condition row by row, the optimizer may not always use
an index scan but may instead choose a Full Table Scan (FTS) on the inner table (the one in
the subquery).
 Index Usage: Even if the inner table has an index, Oracle might decide not to use it,
depending on factors such as:
o Table size: If the inner table is small, a full scan could be more efficient than using an
index.
o Data distribution: If a significant number of rows match the EXISTS condition, a full
table scan might be preferred because the cost of using an index might not offer
significant benefits.
Why Oracle May Choose Full Table Scan (FTS) in an EXISTS Query:
1. Row-by-row Subquery Execution:
o When Oracle evaluates a correlated subquery with EXISTS, it checks the subquery
condition for each row of the outer query.
o If the condition in the subquery is likely to match many rows in the inner table (the
one being checked with EXISTS), Oracle might avoid the index and perform a Full
Table Scan to minimize the overhead of multiple index lookups.
2. Indexed Column or Filter Doesn't Guarantee Index Usage:
o Even if the columns used in the EXISTS condition are indexed, Oracle may still prefer
an FTS if the data in the table is not selective enough (i.e., a large percentage of rows
match the filter condition).
o In this case, using an index would result in random I/O, which could be less efficient
than performing a full scan.
3. Cost-Based Optimization:
 oOracle's optimizer calculates the cost of using an index versus performing a full scan
based on factors like table size, row cardinality, and statistics. If it estimates that an
FTS will be cheaper (based on these statistics), it will prefer FTS even if the columns
are indexed.
Why Hash Join, Merge Join, or Nested Loops May Not Be Chosen:
 Hash Join: A Hash Join is typically used when the optimizer expects large sets of data to be
involved in the join. For an EXISTS query, Oracle doesn't need to join the data; it only needs
to check if a row exists, so it doesn't need the hashing mechanism of a hash join.
 Merge Join: A Merge Join is used when both sides of the join are sorted, but since EXISTS
involves checking for row existence (not fetching rows to join), this join method doesn't
apply.
 Nested Loops Join: A Nested Loops Join is useful when there is an index to efficiently access
the inner table for each row from the outer table. However, with EXISTS, Oracle may opt for a
full scan of the inner table rather than trying to match rows using an index and looping
through them.
Conclusion:
 Does EXISTS always result in FTS even with indexes?
o Not always, but often yes. Oracle may choose Full Table Scan for the subquery even
if the relevant columns are indexed. This is due to factors like data distribution, the
row-by-row nature of the subquery, and the optimizer's cost-based decision-making
process.
 Does EXISTS avoid joins like Hash Join or Merge Join?
o Yes, because EXISTS doesn’t require a join but just checks for existence, meaning
Oracle doesn't need to join tables but merely checks if a condition is met for each
row in the outer query.

.
Example for such big query :

SELECT
c.customer_name,
SUM(o.order_total) AS total_sales,
COUNT(DISTINCT p.product_id) AS unique_products
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id
WHERE o.order_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31',
'YYYY-MM-DD')
AND (p.product_category = 'Electronics' OR p.product_category = 'Clothing')
GROUP BY c.customer_name
HAVING COUNT(DISTINCT p.product_id) > 2
ORDER BY total_sales DESC;

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart|

Pstop |
|-----|-----------------------------------------|------------------|-------|-------|-------------|----------|-------|-------|
| 0 | SELECT STATEMENT | | 1000 | 15000| 20 (10)| 00:00:01 |
| 1 | SORT ORDER BY | | 1000 | 15000| 20 (10)| 00:00:01 |
| 2 | HASH GROUP BY | | 1000 | 12000| 15 (10)| 00:00:01 |
| 3 | NESTED LOOPS | | 1000 | 6000| 10 (10)| 00:00:01 |
| 4 | NESTED LOOPS | | 1000 | 3000| 5 (10)| 00:00:01 |
| 5 | TABLE ACCESS FULL | CUSTOMERS | 50 | 500 | 3 (10)| 00:00:01 |
| 6 | TABLE ACCESS FULL | ORDERS | 1000 | 5000 | 2 (10)| 00:00:01 |
| 7 | TABLE ACCESS FULL | ORDER_DETAILS | 10000 | 40000 | 4 (10)| 00:00:01 |
| 8 | TABLE ACCESS FULL | PRODUCTS | 5000 | 15000| 4 (10)| 00:00:01 |

Correct Order of Execution:

 1. Full Table Scans (Steps 5 & 6 in the execution plan):
o Oracle first retrieves the data from the CUSTOMERS and ORDERS tables via Full Table
Scans (since there’s no index to use or the optimizer chooses to scan the whole
table).
o Similarly, the ORDER_DETAILS and PRODUCTS tables are also accessed via Full Table
Scans (as shown in steps 7 and 8).
At this point, Oracle reads all the rows from these tables to begin processing them, even before
performing any join or filter operation.
2. Nested Loops Joins (Steps 4 and 3 in the execution plan):
o Once the data is retrieved from the individual tables, Oracle starts performing
Nested Loops Joins (steps 4 and 3) to combine the rows.
o In step 4, it joins CUSTOMERS to ORDERS based on customer_id. The nested loop
join will take one row from the CUSTOMERS table and then loop over the rows in the
ORDERS table, finding matching rows.
o In step 3, the ORDER_DETAILS table is joined with PRODUCTS based on the
product_id.
This is the point where the results of individual tables begin to be combined.
3. Hash Grouping (Step 2 in the execution plan):
o After the tables are joined, Oracle performs a Hash Grouping operation. This
operation computes aggregates like SUM(o.order_total) and COUNT(DISTINCT
p.product_id) based on the GROUP BY condition.
o This is a necessary step to perform the aggregation of the sales data, i.e., summing
the total sales and counting distinct products for each customer.
4. Sort Order By (Step 1 in the execution plan):
o After the data is grouped and the aggregates are calculated, the Sort operation is
performed based on total_sales DESC (as per the ORDER BY clause). This sorts the
aggregated data in descending order of the total sales.
This is an additional step that takes place after grouping because the result needs to be presented in
a sorted order.
5. Final Output (Step 0 in the execution plan):
o Finally, the SELECT statement fetches the sorted, grouped data and returns it as the
result set to the user or application.
Summarizing the Execution Flow:
The execution flow follows this bottom-up order:
1. Full Table Scans (Steps 5, 6, 7, 8): Oracle retrieves all rows from the tables involved.
2. Nested Loops Joins (Steps 4, 3): Oracle joins CUSTOMERS to ORDERS, and ORDER_DETAILS to
PRODUCTS.
3. Hash Grouping (Step 2): Oracle groups the result by customer_name and performs the
aggregation (sum and count).
4. Sort (Step 1): The results are sorted by total_sales DESC.
5. Final Output (Step 0): The final, sorted, grouped results are returned to the user.
Important Notes:
 Join Order: Even though CUSTOMERS and ORDERS are joined first, followed by
ORDER_DETAILS and PRODUCTS, Oracle optimizes this order based on the table sizes and
statistics. It might choose a different plan (e.g., using a hash join) based on the available
indexes or estimated costs.
 Full Table Scans: Full table scans are used because Oracle might have determined that
scanning the entire table is more efficient than using indexes, especially if the table is small
or the selectivity of the query is low.
So, in summary, your understanding is correct in terms of the flow, but keep in mind that Oracle
optimizes the plan internally and might choose different join methods or operations based on
statistics and cost estimations.
Let me know if you'd like further clarification on any part of this!

Below is a tabular format with the columns Purpose, When to Use, Syntax, and Conditions for some
commonly used Oracle hints:
Hint Purpose When to Use Syntax Conditions
Use when an index is
SELECT /*+ INDEX(table_name available and
When you want to ensure
Forces the query index_name) */ column1 beneficial,
INDEX an index is used instead of a
to use an index FROM table_name WHERE particularly for large
full table scan (FTS).
column1 = value; tables with high
cardinality.
Use when the table is
small or has very few
When an index scan is not SELECT /*+ FULL(table_name)
Forces a full qualifying rows
FULL optimal and a full table scan */ column1 FROM table_name
table scan (FTS) where FTS may be
is faster. WHERE column2 = value;
more efficient than
an index scan.
SELECT /*+ USE_NL(table1 Best when the outer
When joining a small table
Forces a nested table2) */ column1 FROM table is small, making
USE_NL with a large one and nested
loop join table1, table2 WHERE the nested loop join
loops are ideal.
table1.col = table2.col; efficient.
Suitable when both
SELECT /*+ USE_MERGE(table1
When both tables are large tables are large, and
USE_ME Forces a merge table2) */ column1 FROM
and a merge join would be sorting or indexing
RGE join table1, table2 WHERE
more efficient. can be used to speed
table1.col = table2.col;
up the merge join.
SELECT /*+ USE_HASH(table1 Hash joins are ideal
USE_HA Forces a hash When neither table is sorted table2) */ column1 FROM for large tables
SH join and both are large. table1, table2 WHERE without indexes on
table1.col = table2.col; the join columns.
SELECT /*+ NO_MERGE(table1 Use if the optimizer's
NO_ME Prevents the use When a merge join would table2) */ column1 FROM automatic choice of a
RGE of a merge join be inefficient. table1, table2 WHERE merge join is causing
table1.col = table2.col; performance issues.
Use when the index
SELECT /*+
is poorly maintained
When an index is not NO_INDEX(table_name
NO_IND Prevents the use or when a full table
helpful or can cause index_name) */ column1
EX of an index scan might be more
inefficiency. FROM table_name WHERE
efficient than using
column1 = value;
an index.
Ensure your system
When the query is large or SELECT /*+ supports parallelism
PARALL Forces parallel complex and parallel PARALLEL(table_name, degree) (multiple CPUs,
EL execution execution would improve */ column1 FROM table_name sufficient resources).
speed. WHERE column2 = value; Useful for large
datasets.
SELECT /*+
STAR_T Use in a star schema
Forces star When joining a fact table STAR_TRANSFORMATION(table
RANSFO when a fact table is
transformation with multiple dimension 1 table2) */ column1 FROM
RMATIO joined with multiple
optimization tables (star schema). table1, table2 WHERE
N dimension tables.
table1.col = table2.col;
Use when the query
SELECT /*+ LEADING(table1
Specifies the When you want to control involves multiple
LEADIN table2) */ column1 FROM
join order the order of joins in a multi- joins, and the
G table1, table2 WHERE
explicitly table query. optimizer’s join order
table1.col = table2.col;
is suboptimal.
ORDERE Forces the When you need to enforce a SELECT /*+ ORDERED */ Use when the
D optimizer to use specific join order. column1 FROM table1, table2 optimizer does not
the join order as WHERE table1.col = table2.col; choose the best join
specified in the order, and the order
Hint Purpose When to Use Syntax Conditions
of joins needs to be
FROM clause
enforced.
Forces
Use when the
materialization
SELECT /*+ MATERIALIZE */ optimizer avoids
of a subquery When a subquery should be
MATERI column1 FROM (SELECT materializing a
(turns a materialized for better
ALIZE column1 FROM table1) subquery that could
subquery into a performance.
subquery; improve
temporary
performance.
table)
Use when sorting is
redundant or
When sorting operations are SELECT /*+ NO_SORT */ unnecessary, such as
NO_SO Prevents sorting
unnecessary and can be column1 FROM table_name when data is already
RT operations
avoided. WHERE column1 = value; sorted or sorting
operations are too
costly.

Here's a comparison of key access patterns between Oracle and PostgreSQL in a table format. The
comparison focuses on joins, table scans, and index usage in execution plans for both databases.
Access Oracle PostgreSQL
Pattern
Full Table Used when no index is available or Similar to Oracle. PostgreSQL uses a "Seq Scan"
Scan when a large portion of the table for full table scans.
needs to be accessed.
Index Scan INDEX RANGE SCAN, INDEX UNIQUE Index Scan and Index Only Scan for efficient data
SCAN, INDEX FULL SCAN. Can be retrieval using indexes.
range-based or unique-based scans.
Bitmap Index BITMAP INDEX SCAN, used for PostgreSQL doesn't natively support bitmap
Scan columns with low cardinality (e.g., indexes, but can simulate bitmap-based filtering
gender, status). using Bitmap Index Scan in certain cases with GIN
or GiST indexes.
Hash Join HASH JOIN, used for large, unsorted PostgreSQL also uses a Hash Join for joining large,
datasets, where a hash table is built unsorted datasets.
for one input table.
Merge Join SORT MERGE JOIN, when both Merge Join in PostgreSQL, similar to Oracle, when
inputs are sorted on the join key. inputs are pre-sorted or can be sorted.
Efficient for ordered data.
Nested Loop NESTED LOOP, common when one Nested Loop Join in PostgreSQL is used when
table is small or an index is used on there's a small outer relation or index on the inner
the inner table. relation.
Bitmap Index BITMAP INDEX AND, BITMAP INDEX PostgreSQL does not have direct bitmap indexing
Operations OR, combines multiple bitmap index but uses GIN (Generalized Inverted Index) or GiST
scans for complex conditions. (Generalized Search Tree) for similar operations.
Sort Merge A hybrid approach for joining sorted Merge Join in PostgreSQL, similar to Oracle, for
Join tables. More efficient when sorting joining sorted datasets.
is already done or possible.
Cartesian Join CROSS JOIN, occurs when no join Cross Join in PostgreSQL, same behavior.
condition is provided, resulting in a
cross product of rows.
Index Join Used when an index can directly Index Scan with Nested Loop for joining via index
retrieve rows for a nested loop or joins.
hash join.
Hash Anti Join HASH ANTI JOIN, used when the Anti Join can be simulated with NOT EXISTS or
first table's rows need to be LEFT JOIN with a filter in PostgreSQL.
returned only if there's no matching
row in the second table.
 Hash Semi HASH SEMI JOIN, used to return Simulated in PostgreSQL using EXISTS or IN
Join rows from the first table if there is a clauses.
matching row in the second table.
Merge Anti SORT MERGE ANTI JOIN, a variant of Similar logic can be achieved with NOT EXISTS or
Join merge join for non-matching rows. LEFT JOIN with a filter.
Merge Semi SORT MERGE SEMI JOIN, returns Can be simulated in PostgreSQL with EXISTS or IN.
Join rows from the first table if there's a
match in the second table.
Clustered Join Usually occurs in specialized Not native in PostgreSQL. Can be simulated with
configurations like RAC, where partitioning or using table clustering based on a
tables are co-located for better specific index.
access.
Index Scan INDEX RANGE SCAN with additional Index Scan with additional WHERE conditions in
with Filter filters for more efficient lookups. the scan.
Clustered Used when Oracle finds that data is Not native in PostgreSQL. However, partitioning
Nested Loop co-located (usually in RAC setups), or parallel query execution can help reduce disk
reducing disk I/O. I/O.
Table Scan A table scan combined with an Seq Scan combined with Index Scan where an
with Index index filter. index is used to filter rows after the table scan.
Filter

Key Differences:
1. Bitmap Indexes: Oracle has built-in support for bitmap indexes, while PostgreSQL relies on
GIN and GiST indexes for similar functionality.
2. Clustered Joins: Oracle may use clustered joins in highly optimized environments (like RAC),
while PostgreSQL doesn't natively support this but can achieve similar effects using
partitioning or clustering.
3. Anti/Semi Joins: PostgreSQL does not have a native "anti join" or "semi join" operator in
execution plans, but they can be simulated using NOT EXISTS, LEFT JOIN, and IN clauses.
Overall, while both Oracle and PostgreSQL offer similar join and access patterns, Oracle provides
more fine-tuned options (like Bitmap Index Operations and Clustered Joins) out of the box, whereas
PostgreSQL relies on general-purpose strategies and flexible indexing systems (like GIN and GiST) to
achieve similar results.