Please send a sql monitor output from your query. Everything else is just guessing, The sql monitor will show the exact issue and we will know for sure in no time.
When you put a count around it is a totally different query.The easiest would be a CTAS (Create table as select) to avoid that too much time is spend spooling. Get it running. Most of the time you don't need to led the query finish as the issue often becomes visible rather soon in sql monitor.
Thanks Lothar Am 07.11.2014 22:16, schrieb Matthew Parker:
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 <mailto:Abdul.Ebadi@xxxxxxxxxx> To: oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx> Subject: Exadata Tuning Question+ Date: Fri, 7 Nov 2014 20:59:39 +0000We 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
-- --- Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv. http://www.avast.com
