RE: Exadata Tuning Question+
- From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
- To: "dimensional.dba@xxxxxxxxxxx" <dimensional.dba@xxxxxxxxxxx>, "Abdul.Ebadi@xxxxxxxxxx" <abdul.ebadi@xxxxxxxxxx>, "tim@xxxxxxxxx" <tim@xxxxxxxxx>, 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 10 Nov 2014 07:24:06 -0800
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 ParkerChief Technologist425-891-7934
(cell)Dimensional.dba@comcast.netView 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
RELATEDSERVICEINSTANCEIDLEVEL5FROM 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:
