explain plan

[Roshan]

Hi,

kindly advise on slow query which is fast on Oracle X8 server but slow on exadata.
explainplan.txt

Thanks,
Roshan

[ErmanArslansOracleBlog]

Fetch Time: The dominant factor is the fetch time, consuming 712.45 seconds (over 99% of the total time). This indicates the query spends most of its time retrieving data from storage.

Nested Loops and Hash Joins: The execution plan relies heavily on nested loop joins and hash joins, which can be inefficient for large datasets on Exadata.

Full Table Scans: There are indications of full table scans or scans that access a significant portion of the data, further contributing to the fetch time.

so, you should consider transforming the query, or the underlying structure (indexing, partitioning etc..).. Check -> Tuning SQLs to Optimize Smart Scan Performance ..
Tuning SQLs to Optimize Smart Scan Performance (Doc ID 2608210.1)

[ErmanArslansOracleBlog]

Note that

TABLE ACCESS STORAGE FULL DIM_CUSTOMER - This line shows a full table scan on the DIM_CUSTOMER table. Concentrate on that..

 253619     253619     253619                     TABLE ACCESS STORAGE FULL DIM_CUSTOMER (cr=111060598 pr=108746279 pw=0 time=814732681 us starts=31703 cost=111414 size=210 card=5)

[Roshan]

Thanks a lot :)