RE: Exadata Tuning Question+
- From: "Matthew Parker" <dimensional.dba@xxxxxxxxxxx>
- To: <iggy_fernandez@xxxxxxxxxxx>, "'Abdul.Ebadi@xxxxxxxxxx'" <abdul.ebadi@xxxxxxxxxx>, "'Oracle-L Freelists'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 7 Nov 2014 13:16:06 -0800
If you really want to see the rows of data, run it into a file locally on
the database server and tail the file if you want to.
If you just need the output row count, then change query into a count(*)
instead of returning the whole row set.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Iggy Fernandez
Sent: Friday, November 7, 2014 1:10 PM
To: Abdul.Ebadi@xxxxxxxxxx; Oracle-L Freelists
Subject: RE: Exadata Tuning Question+
re: 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.
I think that's exactly what you ought to expect and has nothing to do with
Oracle Database. Why would you expect anything different? It can't be a
produciton requirement to display 21 million rows on a Putty screen so why
try to improve this?
Iggy
_____
From: Abdul.Ebadi@xxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Subject: Exadata Tuning Question+
Date: Fri, 7 Nov 2014 20:59:39 +0000
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
Other related posts:
