RE: Exadata Tuning Question+
Date: Fri, 7 Nov 2014 13:16:06 -0800
Message-ID: <003e01cffad0$11e11920$35a34b60$_at_comcast.net>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Iggy Fernandez
Sent: Friday, November 7, 2014 1:10 PM
To: Abdul.Ebadi_at_Level3.com; 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_at_Level3.com
To: oracle-l_at_freelists.org
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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Nov 07 2014 - 22:16:06 CET