Hi,
I have an Oracle RDBMS 11gR2 EE using Exadata.
I have a very long running query (and also very complex, when I look at the execution plan). Very long means that it take days to execute.
I see that the "smart scans" feature is used with about 78% I/O saved.
Yet, from an AWR report I can see that the wait event "cell single block physical read" for User I/O class is on the Top Timed Foreground Events.
This is usually a sign that indexes are used, and indeed I can see it in the excution plan.
I have no idea if this is good or not, but in the query there are WHERE-clauses with IN/EXSISTS-clauses.
The statistics for this database are not computed using the Exadata feature.
I could use hints to fore the query (and all its correlated subqueries not to used the indexes as Exadata is designed for full table scan.
Does someone has any idea how to make this query running fatster?
Thanks by advance for any tips
Kind Regards.