RE: Exadata Tuning Question+
Date: Mon, 10 Nov 2014 08:43:00 -0800
Message-ID: <011101cffd05$648734c0$2d959e40$_at_comcast.net>
Actually Jonathan I did see the intervening emails and it has been a very interesting discussion.
However a count(*) at the outer level, I have never seen piece an inner group by or order by to create some other level of optimization by Oracle.
Let's go back to the original post:
"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."
Simple question to Abdul, How long did it take before data started returning to the screen?
This is this true answer as to is the query bad or is it the returning of data to the screen.
The query can be inefficient which all of this discussion could help Abdul make it faster, but if it is the returning of data to the screen, then it is rather moot, although still a very interesting discussion.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Monday, November 10, 2014 8:20 AM
To: 'ORACLE-L'
Subject: RE: Exadata Tuning Question+
Matthew,
I think you must have missed some of the intervening emails - in particular the one that showed the query and the execution plan.
There are 5 scalar subqueries in the select list that each run parallel tablescans.
When you execute (select count(*) from (original_query)) the optimizer doesn't have to project every column from the original query - in particular it doesn't have to execute ANY of the scalar subqueries - which is where almost all the execution time is (probably) going.
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 Matthew Parker [dimensional.dba_at_comcast.net]
Sent: 10 November 2014 16:06
To: 'Iggy Fernandez'; 'Abdul.Ebadi_at_Level3.com'; tim_at_evdbt.com; 'ORACLE-L'
Subject: RE: Exadata Tuning Question+
You would have to see if the plan changed for the count(*). I have never seen the Oracle optimizer use a previous result set the next row source result set and optimize away the full table scans for all the next row sources if they were going to happen. I have seen the Oracle optimizer switch to a full index scan to replace the full table scan when there is a pk or unique index to perform the count(*) against.
In my example of performing
Select count(*) from
(original query);
The original query will run completely through to result set in a cursor before the count (*) is resolved. Check the explain on the count(*).
On a side note is the plan output from a simple explain or from the actual cursor execution plan? They could be significantly different.
General rule of thumb
Explain Plan: 85% accurate
Cursor Plan: 99.95% accurate
Yes .05% of the time the plan in V$SQL_PLAN may not represent the real plan. Sometimes the explanation is simple, sometimes you can't derive why there was not a proper recording or branching of the plan.
This normally happens where odd full table scans appear to come into play.
From: Iggy Fernandez [mailto:iggy_fernandez_at_hotmail.com]
Sent: Monday, November 10, 2014 7:24 AM
To: dimensional.dba_at_comcast.net; Abdul.Ebadi_at_Level3.com; tim_at_evdbt.com;
'ORACLE-L'
Subject: RE: Exadata Tuning Question+
Apparently, select count(*) from (original query) completes in seconds instead of hours but that's because Oracle can optimize away the 35 million scalar subqueries (5 for each row) because they don't change the cardinality of the result. A full-table scan is being used for each subquery, so that's 35 million scans of a large table (21 million rows).
Iggy
From: dimensional.dba_at_comcast.net
To: dmarc-noreply_at_freelists.org; Abdul.Ebadi_at_Level3.com; tim_at_evdbt.com;
oracle-l_at_freelists.org
Subject: RE: Exadata Tuning Question+
Date: Mon, 10 Nov 2014 07:18:21 -0800
You can time
Select count(*) from
(original query);
And see fully with the rowset resolving in memory and disk. If a simple count(*) returns quickly even with rowset resolution unless the rowset is so large you are spending all your time writing to slow tempfile disk which again means not really the query.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Nov 10 2014 - 17:43:00 CET