Performance Model and Diagnostics

A lakehouse query spends time finding data and processing it. Measure these stages separately before changing the file layout or database settings.

Cost	What creates it	Primary controls
Planning and listing	Finding the folders, files, catalog objects, snapshots, and manifests needed by the query.	Path partitioning, strict_column_order for implicit partitioning, efficient Iceberg metadata, catalog metadata cache.
Remote data I/O	Reading object-store files, Parquet row groups, ORC stripes, and Iceberg data files.	Partition pruning, file-level filtering, column projection, Lake Cache.
Database processing	Decompression, conversion, joins, aggregation, sorting, and residual filtering.	Statistics, join order, predicate design, parallel query, materialization.
Network and egress	Cross-region or cross-cloud transfer and repeated remote retrieval.	Data locality, Lake Cache, materialization.
Concurrency	Many sessions competing for database CPU, network bandwidth, cache space, or object-store request limits.	Resource management, cache sizing, controlled parallelism, load or materialize hot data.

Inspect the Executed Plan

SELECT /*+ GATHER_PLAN_STATISTICS */
       region, SUM(amount)
FROM orders_xt
WHERE order_date >= DATE '2026-07-01'
AND order_date < DATE '2026-08-01'
GROUP BY region;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

Use the executed cursor plan, not only EXPLAIN PLAN, to compare estimates with runtime rows and to see applicable predicate, partition, and parallel information. Use SQL Monitor for long-running or parallel operations. See:

• DBMS_XPLAN
• Displaying Execution Plans

Diagnostics: Determining whether time is spent:
a. before the file reads begin and while remote bytes are being read, or
b. after rows reach the database
enables you to decide to fix metadata and listing, pruning and caching, or SQL and database processing, respectively.