RE: Exadata Tuning Question+
- From: "Matthew Parker" <dimensional.dba@xxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 10 Nov 2014 08:43:00 -0800
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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
@jloracle
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Matthew Parker [dimensional.dba@xxxxxxxxxxx]
Sent: 10 November 2014 16:06
To: 'Iggy Fernandez'; 'Abdul.Ebadi@xxxxxxxxxx'; tim@xxxxxxxxx; '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@xxxxxxxxxxx]
Sent: Monday, November 10, 2014 7:24 AM
To: dimensional.dba@xxxxxxxxxxx; Abdul.Ebadi@xxxxxxxxxx; tim@xxxxxxxxx;
'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@xxxxxxxxxxx
To: dmarc-noreply@xxxxxxxxxxxxx; Abdul.Ebadi@xxxxxxxxxx; tim@xxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
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.
Other related posts:
