RE: Exadata Tuning Question+
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 10 Nov 2014 16:19:37 +0000
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<mailto:dimensional.dba@xxxxxxxxxxx>
To: dmarc-noreply@xxxxxxxxxxxxx<mailto:dmarc-noreply@xxxxxxxxxxxxx>;
Abdul.Ebadi@xxxxxxxxxx<mailto:Abdul.Ebadi@xxxxxxxxxx>;
tim@xxxxxxxxx<mailto:tim@xxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto: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:
