Re: Exadata Tuning Question+

Although it might be a "best practice" to remove indexes to encourage full scans in parallel, its important to remember that in some circumstances this might cause a lot of SORT-MERGE or HASH joins, especially if the query involves joins to lots of big tables. Those SM or HA joins will spend a lot of time reading and writing to the temporary tablespace, and that I/O on temporary tablespaces do not have any special off-loading or smart-scan optimizations. Exadata only has off-loading and smart-scan optimizations for I/O operations taking place in datafiles.

For tables this size, you can't increase the PGA size enough to cache the entire SM or HA join in memory, so that's probably why you haven't seen any benefit.

Chances are good that SQL Plan Monitor or DBMS_XPLAN.DISPLAY_CURSOR(option=>'ALLSTATS ALL') would show you that the time spent scanning the row-sources is negligible, but the time spent on joins is taking the majority of elapsed time.

How many and what type of join operations? Are they straight-forward INNER joins, or OUTER joins? Are there any FULL OUTER JOINs or sub-queries?

Hope this helps...

-Tim

On 11/7/14 13:59, Ebadi, Abdul wrote:
>
> We have a half rack 4-node Exadata (X2 high capacity) running several
> DW databases for us. We have a query going against a 21 million row
> table with several self-joins in it. This query returns 7 million
> rows takes way too long too run (hrs).
>
> We have made sure it is running in parallel using cell offloading
> (full storage scans) and when we put a count(*) around the query it
> returns in only 4 seconds for 7 million rows returned.
>
> However, when we display the output to the screen it takes hours for
> it to finish and we see pauses in the display every second or two
> while it is running.
>
> Trying to figure out what is causing these pauses? The wait is PX Deq:
> type waits when it runs with these pauses. SQL Monitor doesn’t tell
> us much either except cell efficiency is negative 85%!
>
> We have increased PGA size and didn’t make much difference. We are
> considering putting TEMP tablespace on flash cache possibly. Another
> DBA added an index to it just to see (bad idea on Exadata) and did’t
> improve it. Before making any more change we would like to see some
> evidence for root cause.
>
> We were told for best practices on Exadata it is better to remove
> indexes and hints (if possible) and let the machine full scan in
> parallel using storage offloading. Do you guys agree and are there
> other best practices on Exadata also?
>
> Any other suggestions on tuning this query and also general Exadata
> best practices?
>
> Thanks,
>
> Abdul
>

--
http://www.freelists.org/webpage/oracle-l