RE: Exadata Tuning Question+

  • From: "Matthew Parker" <dimensional.dba@xxxxxxxxxxx>
  • To: "'Iggy Fernandez'" <iggy_fernandez@xxxxxxxxxxx>, "'Abdul.Ebadi@xxxxxxxxxx'" <abdul.ebadi@xxxxxxxxxx>, <tim@xxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 10 Nov 2014 08:06:37 -0800

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.

 

Matthew Parker

Chief Technologist

425-891-7934 (cell)

Dimensional.dba@xxxxxxxxxxx

 <http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew
Parker's profile on LinkedIn

 

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of David Fitzjarrell (Redacted sender "oratune@xxxxxxxxx" for
DMARC)
Sent: Monday, November 10, 2014 7:03 AM
To: Abdul.Ebadi@xxxxxxxxxx; tim@xxxxxxxxx; ORACLE-L
Subject: Re: Exadata Tuning Question+

 

From what you've reported it appears to be the output buffering that's
causing this problem; a count(*) returns in record time (probably because
the output is a single line/column rather than a 7+ million row result set).
As suggested in a prior response a CTAS may be of benefit; selecting from
the 'temporary' table containing the current result set might be faster.

 

David Fitzjarrell

Principal author, "Oracle Exadata Survival Guide"

 

On Friday, November 7, 2014 3:04 PM, "Ebadi, Abdul" <Abdul.Ebadi@xxxxxxxxxx>
wrote:

 

Tim/others. thanks for your replies!  Below is a slightly generalized
version of the query to give you an idea of the joins and an actual explain
plan:

 

 

SELECT  bunch of stuff....., 

        

        (

                SELECT  RTRIM(XMLSERIALIZE(CONTENT EXTRACT(
XMLAGG(XMLELEMENT("e", sr1.RELATED_SID

                        ||

                        ',')

                ORDER BY sr1.RELATED_SID), '//text()' ) ) , ',' )

                FROM    service_relationship sr1

                WHERE   sr1.SID                    = slv.SID

                        AND sr1.RELATIONSHIP_LEVEL = '1'

                GROUP BY sr1.SID

        ) AS RELATEDSERVICEINSTANCEIDLEVEL1,

        (

                SELECT  RTRIM(XMLSERIALIZE(CONTENT EXTRACT(
XMLAGG(XMLELEMENT("e", sr2.RELATED_SID

                        ||

                        ',')

                ORDER BY sr2.RELATED_SID), '//text()' ) ) , ',' )

                FROM    service_relationship sr2

                WHERE   sr2.SID                    = slv.SID

                        AND sr2.RELATIONSHIP_LEVEL = '2'

                GROUP BY sr2.SID

        ) AS RELATEDSERVICEINSTANCEIDLEVEL2,

        (

               SELECT  RTRIM(XMLSERIALIZE(CONTENT EXTRACT(
XMLAGG(XMLELEMENT("e", sr3.RELATED_SID

                        ||

                        ',')

                ORDER BY sr3.RELATED_SID), '//text()' ) ) , ',' )

                FROM    service_relationship sr3

                WHERE   sr3.SID                    = slv.SID

                        AND sr3.RELATIONSHIP_LEVEL = '3'

                GROUP BY sr3.SID

        ) AS RELATEDSERVICEINSTANCEIDLEVEL3,

        (

                SELECT  RTRIM(XMLSERIALIZE(CONTENT EXTRACT(
XMLAGG(XMLELEMENT("e", sr4.RELATED_SID

                        ||

                        ',')

                ORDER BY sr4.RELATED_SID), '//text()' ) ) , ',' )

                FROM    service_relationship sr4

                WHERE   sr4.SID                    = slv.SID

                        AND sr4.RELATIONSHIP_LEVEL = '4'

                GROUP BY sr4.SID

        ) AS RELATEDSERVICEINSTANCEIDLEVEL4,

        (

                SELECT  RTRIM(XMLSERIALIZE(CONTENT EXTRACT(
XMLAGG(XMLELEMENT("e", sr5.RELATED_SID

                        ||

                        ',')

                ORDER BY sr5.RELATED_SID), '//text()' ) ) , ',' )

                FROM    service_relationship sr5

                WHERE   sr5.SID                    = slv.SID

                        AND sr5.RELATIONSHIP_LEVEL = '5'

                GROUP BY sr5.SID

        ) AS RELATEDSERVICEINSTANCEIDLEVEL5

FROM    service_lookup slv

        LEFT JOIN service_location sl

        ON      sl.service_location_id = slv.service_location_id;

 

 

 

 

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------------

Plan hash value: 1570133209

 

----------------------------------------------------------------------------
----------------------------------------------------------

| Id  | Operation                        | Name                 | Rows  |
Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

----------------------------------------------------------------------------
----------------------------------------------------------

|   0 | SELECT STATEMENT                 |                      |  7331K|
5593M|  1877   (5)| 00:00:01 |        |      |            |

|   1 |  SORT GROUP BY                   |                      |     1 |
22 |   368   (6)| 00:00:01 |        |      |            |

|   2 |   PX COORDINATOR                 |                      |       |
|            |          |        |      |            |

|   3 |    PX SEND QC (RANDOM)           | :TQ10000             |    25 |
550 |   368   (6)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |

|   4 |     PX BLOCK ITERATOR            |                      |    25 |
550 |   368   (6)| 00:00:01 |  Q1,00 | PCWC |            |

|*  5 |      TABLE ACCESS STORAGE FULL   | SERVICE_RELATIONSHIP |    25 |
550 |   368   (6)| 00:00:01 |  Q1,00 | PCWP |            |

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------------

|   6 |  SORT GROUP BY                   |                      |     1 |
22 |   368   (6)| 00:00:01 |        |      |            |

|   7 |   PX COORDINATOR                 |                      |       |
|            |          |        |      |            |

|   8 |    PX SEND QC (RANDOM)           | :TQ20000             |    25 |
550 |   368   (6)| 00:00:01 |  Q2,00 | P->S | QC (RAND)  |

|   9 |     PX BLOCK ITERATOR            |                      |    25 |
550 |   368   (6)| 00:00:01 |  Q2,00 | PCWC |            |

|* 10 |      TABLE ACCESS STORAGE FULL   | SERVICE_RELATIONSHIP |    25 |
550 |   368   (6)| 00:00:01 |  Q2,00 | PCWP |            |

|  11 |  SORT GROUP BY                   |                      |     1 |
22 |   368   (6)| 00:00:01 |        |      |            |

|  12 |   PX COORDINATOR                 |                      |       |
|            |          |        |      |            |

|  13 |    PX SEND QC (RANDOM)           | :TQ30000             |    25 |
550 |   368   (6)| 00:00:01 |  Q3,00 | P->S | QC (RAND)  |

|  14 |     PX BLOCK ITERATOR            |                      |    25 |
550 |   368   (6)| 00:00:01 |  Q3,00 | PCWC |            |

|* 15 |      TABLE ACCESS STORAGE FULL   | SERVICE_RELATIONSHIP |    25 |
550 |   368   (6)| 00:00:01 |  Q3,00 | PCWP |            |

|  16 |  SORT GROUP BY                   |                      |     1 |
22 |   368   (6)| 00:00:01 |        |      |            |

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------------

|  17 |   PX COORDINATOR                 |                      |       |
|            |          |        |      |            |

|  18 |    PX SEND QC (RANDOM)           | :TQ40000             |    25 |
550 |   368   (6)| 00:00:01 |  Q4,00 | P->S | QC (RAND)  |

|  19 |     PX BLOCK ITERATOR            |                      |    25 |
550 |   368   (6)| 00:00:01 |  Q4,00 | PCWC |            |

|* 20 |      TABLE ACCESS STORAGE FULL   | SERVICE_RELATIONSHIP |    25 |
550 |   368   (6)| 00:00:01 |  Q4,00 | PCWP |            |

|  21 |  SORT GROUP BY                   |                      |     1 |
22 |   368   (6)| 00:00:01 |        |      |            |

|  22 |   PX COORDINATOR                 |                      |       |
|            |          |        |      |            |

|  23 |    PX SEND QC (RANDOM)           | :TQ50000             |    25 |
550 |   368   (6)| 00:00:01 |  Q5,00 | P->S | QC (RAND)  |

|  24 |     PX BLOCK ITERATOR            |                      |    25 |
550 |   368   (6)| 00:00:01 |  Q5,00 | PCWC |            |

|* 25 |      TABLE ACCESS STORAGE FULL   | SERVICE_RELATIONSHIP |    25 |
550 |   368   (6)| 00:00:01 |  Q5,00 | PCWP |            |

|  26 |  PX COORDINATOR                  |                      |       |
|            |          |        |      |            |

|  27 |   PX SEND QC (RANDOM)            | :TQ60002             |  7331K|
5593M|  1877   (5)| 00:00:01 |  Q6,02 | P->S | QC (RAND)  |

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------------

|* 28 |    HASH JOIN RIGHT OUTER BUFFERED|                      |  7331K|
5593M|  1877   (5)| 00:00:01 |  Q6,02 | PCWP |            |

|  29 |     PX RECEIVE                   |                      |  3175K|
920M|   366   (3)| 00:00:01 |  Q6,02 | PCWP |            |

|  30 |      PX SEND HASH                | :TQ60000             |  3175K|
920M|   366   (3)| 00:00:01 |  Q6,00 | P->P | HASH       |

|  31 |       PX BLOCK ITERATOR          |                      |  3175K|
920M|   366   (3)| 00:00:01 |  Q6,00 | PCWC |            |

|  32 |        TABLE ACCESS STORAGE FULL | SERVICE_LOCATION     |  3175K|
920M|   366   (3)| 00:00:01 |  Q6,00 | PCWP |            |

|  33 |     PX RECEIVE                   |                      |  7331K|
3467M|  1507   (5)| 00:00:01 |  Q6,02 | PCWP |            |

|  34 |      PX SEND HASH                | :TQ60001             |  7331K|
3467M|  1507   (5)| 00:00:01 |  Q6,01 | P->P | HASH       |

|  35 |       PX BLOCK ITERATOR          |                      |  7331K|
3467M|  1507   (5)| 00:00:01 |  Q6,01 | PCWC |            |

|  36 |        TABLE ACCESS STORAGE FULL | SERVICE_LOOKUP       |  7331K|
3467M|  1507   (5)| 00:00:01 |  Q6,01 | PCWP |            |

----------------------------------------------------------------------------
----------------------------------------------------------

 

 

From: Tim Gorman [mailto:tim@xxxxxxxxx] 
Sent: Friday, November 07, 2014 2:36 PM
To: ORACLE-L; Ebadi, Abdul
Subject: Re: Exadata Tuning Question+

 

Abdul,

Although it might be a "best practice" to remove indexes to encourage full
scans in parallel, its important to remember that in some circumstances this
might cause a lot of SORT-MERGE or HASH joins, especially if the query
involves joins to lots of big tables.  Those SM or HA joins will spend a lot
of time reading and writing to the temporary tablespace, and that I/O on
temporary tablespaces do not have any special off-loading or smart-scan
optimizations.  Exadata only has off-loading and smart-scan optimizations
for I/O operations taking place in datafiles.

For tables this size, you can't increase the PGA size enough to cache the
entire SM or HA join in memory, so that's probably why you haven't seen any
benefit.

Chances are good that SQL Plan Monitor or
DBMS_XPLAN.DISPLAY_CURSOR(option=>'ALLSTATS ALL') would show you that the
time spent scanning the row-sources is negligible, but the time spent on
joins is taking the majority of elapsed time.

How many and what type of join operations?  Are they straight-forward INNER
joins, or OUTER joins?  Are there any FULL OUTER JOINs or sub-queries?

Hope this helps...

-Tim

On 11/7/14 13:59, Ebadi, Abdul wrote:

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

 

 

Other related posts: