RE: Exadata Tuning Question+
Date: Fri, 7 Nov 2014 23:13:56 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901E5C8AD_at_exmbx06.thus.corp>
While it doesn't make sense to return 21M rows to the screen, the output (in general) shouldn't pause every couple of seconds, as this would (usually) be a measure of much work the database had to do to acquire the next batch of rows. The display should simply keep scrolling continuously. In the case of the OP each row from the sample code requires 5 parallel tablescans of a reasonably large table, so we could expect the server to take a couple of seconds to populate the next array fetchsize of rows before returning them.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Iggy Fernandez [iggy_fernandez_at_hotmail.com] Sent: 07 November 2014 21:10
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 Sat Nov 08 2014 - 00:13:56 CET