Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Introduction to the Oracle database machine

For those of you who haven’t followed all the Oracle Exadata and database machine information, and want a brief introduction to the database machine: here it is!

The confusion

There is some confusion about ‘exadata’ and ‘the database machine’. If we look at the official product names, ‘Exadata’ is the storage server, and the ‘Database machine’ is the complete boxed machine, including exadata for storage. But…in the real world, in different kinds of papers (technical, advertising) exadata sometimes is used as an alias for the database machine.

What is the database machine?

Essentially, the database machine is a version of the Oracle database on balanced, optimised hardware from Oracle. But that is a very short description, and does harm to the unique feature of the database machine: the Oracle Exadata storage server. Exadata offers database offloading by execution some of the database functions on the storage layer. This is a schematic drawing of the architecture of the database machine (cell server is the internal name of the exadata software):

The database machine, a little more verbose

One of the basic design principles of the database machine is fault tolerancy. This means any component may fail, whilst the database still is able to function. So if you look at the schematic drawing above, it means there are multiple instances of the database (=Oracle RAC), there are TWO infiniband switches (in a quarter and half rack, there are three infiniband switches in a full rack), and there are multiple storage servers (3/quarter-, 7/half-, 14/full-rack). The redundancy of the database is NOT done by the storage servers, but is done using Oracle’s ASM (Automatic Storage Management) ‘normal redundancy’, which essentially means data is stored double.

It is possible to use a single server (non RAC) version of the database on the database machine, but that does harm to the architecture of the database machine (in my opinion).

The storage servers are fitted with 384 GB flash memory attached via PCI-X. This flash memory can be used as flash disks, but is configured as flash memory for caching of the storage server by default.

Last but not least the database is able to offload some database actions to the storage:
- Smart scans: row filtering, column filtering, join filtering, incremental backup filtering, data mining model scoring. Transparently.
- Storage indexes: stores min and max values of columns of database blocks every 1MB. Transparently.
- Hybrid Columnar Compression: database compression mechanism where data is stored by column, then compressed. (HCC is using several database blocks). Needs to be specified explicitly as a compression option during creation of modification.

Conclusion

I hope this article was able to get you a basic understanding of the architecture of the database machine. Perhaps it might even make you enthusiastic about it, because it offers several unique features:
- A hardware infrastructure purely built for database performance. Something encountered ‘in the wild’ very rarely.
- State of the art hardware.
- Common Oracle database, no need to alter anything when moving to the database machine.

Tagged: oracle database machine exadata introduction

Content theft on my blog

I have received an email today from Don Burleson who informed me that a number of my articles have mysteriously appeared on another weblog hosted by wordpress on the name of http://gjilevski.wordpress.com/.  For instance:

My post: http://martincarstenbach.wordpress.com/2010/01/21/upgrade-clusterware-10... has reappeared as http://gjilevski.wordpress.com/2009/11/24/upgrade-oracle-crs-10-2-to-ora.... Even the host names are identical! The next one is a bit better as he managed to remove my “the-playground.de” example with “gj.com”:

http://gjilevski.wordpress.com/2009/09/25/install-oracle-11-2-rac-deploy... is my http://martincarstenbach.wordpress.com/2009/10/02/build-your-own-11-2-ra... but he forgot to add the corrections I made. I recognize my DNS server, auxOEL5 :)

The story goes on.

Copy: http://gjilevski.wordpress.com/2009/09/23/install-oracle-11-2-rac-using-... from original: http://martincarstenbach.wordpress.com/2009/09/26/build-your-own-11-2-ra...

Copy: http://gjilevski.wordpress.com/2009/10/13/adding-node-in-oracle-11g-r2-g.... Original: http://martincarstenbach.wordpress.com/2009/10/12/build-your-own-rac-sys...

Copy: http://gjilevski.wordpress.com/2009/10/08/archivelog-retention-policy-in... Original: http://martincarstenbach.wordpress.com/2009/10/08/archivelog-retention-p...

And many more. It seems my blog is replicated almost 1:1 to his.

I am very disappointed to see that someone else has to resort to such extreme measures to boost his profile. Copyright aside, this is very unprofessional and I would have expected something else. If you are interested in the whole story, have a look at http://timurakhmadeev.wordpress.com/2010/06/02/content-thief/

Oh, and it’s not only me, more established authors such as Alex Gorbachev, Mark Bobak and Alex Fatkulin are also victims of this.

So, and that’s it from me about this matter.

Almost.

Having complained to WordPress and leaving comments on my articles they mysteriously disappeared overnight. Everyone affected should do the same, and I urge you to file complaints to wordpress to have the blog removed.

FBI Bug

Here’s an odd little bug (I think) in the optimizer that showed itself in a thread on the OTN database forum some time ago. The optimizer can choose an index which is NOT the cheapest index for satisfying a query against a single table. Here’s the demo – which I’ve run against 11.1.0.6 using an 8KB block size, ASSM and system allocated extent sizes:

create table t1(
	DATE1              DATE,
	STRING1            VARCHAR2(40),
	NUM1               NUMBER,
	NUM2               NUMBER,
	NUM3               NUMBER,
	NUM4               NUMBER,
	NUM5               NUMBER,
	STRING2            VARCHAR2(3),
	NUM6               NUMBER,
	STRING3            VARCHAR2(240),
	STRING4            VARCHAR2(240),
	STRING5            VARCHAR2(240),
	STRING6            VARCHAR2(240),
	STRING7            VARCHAR2(240),
	STRING8            VARCHAR2(240),
	STRING9            VARCHAR2(10)
);

insert into t1(
	date1, num1, num3, num6, string9
)
select
	sysdate + dbms_random.value(-180, +180),
	trunc(dbms_random.value(0,10)),
	trunc(dbms_random.value(0,10)),
	trunc(dbms_random.value(0,10)),
	dbms_random.string('U',6)
from dual
connect by
	rownum <= 40000
;

commit;

CREATE INDEX t1_i1 ON t1 (TRUNC(DATE1), NUM3);
CREATE INDEX t1_i2 ON t1 (NUM3, NUM1, TRUNC(DATE1));

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		estimate_percent => 100,
		block_sample 	 => true,
		method_opt 	 => 'for all columns size 1',
		cascade		 => true
	);
end;
/

explain plan for
SELECT
	NVL(SUM(num6),0) num6_SUM
FROM t1
WHERE
	1 = 1
and	num3 = :b1
AND	TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3
and	string9 = :b4
;

select * from table(dbms_xplan.display)
;

explain plan for
SELECT
	/*+ index(t1 t1_i1) */
	NVL(SUM(num6),0) num6_SUM
FROM t1
WHERE
	1 = 1
AND	num3 = :b1
AND	TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3
and	string9 = :b4
;

select * from table(dbms_xplan.display)
;

The mixture of upper and lower case combined with the presence of redundant columns is because I copied most of the code from the original thread and kept the table and index structures – I don’t usually write messy code.

You’ll notice that I have a query that could use either of the indexes to get to the table, but index t1_i2 starts with a column that holds only 10 distinct values and has an extra column “in the way” so the nature of the query means Oracle will have to scan at least 10% of this index to satisfy the query. Here are the two execution plans.

First the unhinted version of the query – which uses the t1_i2 index. Note the cost, of which the index range scan cost (line 4) is a significant fraction:

-----------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |    21 |    33 |
|   1 |  SORT AGGREGATE               |       |     1 |    21 |       |
|*  2 |   FILTER                      |       |       |       |       |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |    21 |    33 |
|*  4 |     INDEX RANGE SCAN          | T1_I2 |    18 |       |    15 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYSDATE@!-TO_NUMBER(:B2)<=SYSDATE@!-TO_NUMBER(:B3))
   3 - filter("STRING9"=:B4)
   4 - access("NUM3"=TO_NUMBER(:B1) AND
              TRUNC(INTERNAL_FUNCTION("DATE1"))>=SYSDATE@!-TO_NUMBER(:B2) AND
              TRUNC(INTERNAL_FUNCTION("DATE1"))<=SYSDATE@!-TO_NUMBER(:B3))
       filter(TRUNC(INTERNAL_FUNCTION("DATE1"))>=SYSDATE@!-TO_NUMBER(:B2)
              AND TRUNC(INTERNAL_FUNCTION("DATE1"))<=SYSDATE@!-TO_NUMBER(:B3))

Now the version hinted to use t1_i1. Again note the cost – and that the difference is due only to the lower cost of the index range scan.

-----------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |    21 |    20 |
|   1 |  SORT AGGREGATE               |       |     1 |    21 |       |
|*  2 |   FILTER                      |       |       |       |       |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |    21 |    20 |
|*  4 |     INDEX RANGE SCAN          | T1_I1 |    18 |       |     2 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYSDATE@!-TO_NUMBER(:B2)<=SYSDATE@!-TO_NUMBER(:B3))
   3 - filter("STRING9"=:B4)
   4 - access(TRUNC(INTERNAL_FUNCTION("DATE1"))>=SYSDATE@!-TO_NUMBER(:B2)
              AND "NUM3"=TO_NUMBER(:B1) AND TRUNC(INTERNAL_FUNCTION("DATE1"))<=
              SYSDATE@!-TO_NUMBER(:B3))
       filter("NUM3"=TO_NUMBER(:B1))

Left to its own devices, the optimizer chose the more expensive index – even though in both cases the calculations show that the number of visits to the table to collect the result set is the same.

I haven’t really worked out exactly what causes the optimizer to do the wrong thing but it seems, from a couple of other tests, that if you have two indexes that could be used to satisfy a query with the same table access cost but one of the indexes starts with a range scan on a “virtual column” then the other index will be chosen, even if it happens to be the more expensive index to use.

"PX Deq Credit: send blkd" アイドルイベント?

PX Deq Credit: send blkdイベントの検証

パラレル度=1で
test1. insert into lineitem_work (select /*+ parallel(L 1) */ * from lineitem L)
パレレル度=40で
test2. insert into lineitem_work (select /*+ parallel(L 40) */ * from lineitem L)

cpu sec elapsed(sec)
test1 8.92 25.53
test2 0.00 60.19

実行時間は2.4倍パラレルのほうが遅くなる。

test2での問題待機イベントは:


Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Execution Msg 16 0.01 0.21
Disk file operations I/O 1 0.03 0.03
direct path read 16 0.00 0.04
PX Deq Credit: send blkd 1777 2.01 59.63
PX qref latch 4 0.00 0.00
PX Deq Credit: need buffer 15 0.00 0.09
asynch descriptor resize 11 0.00 0.00

前回のテストでも出てきたPX Deq Credit: send blkdだ。

selectがparallelでもInsertがnon parallelだとこうなる。
そこで、InsertもParallelにしてみる、
test3. ALTER SESSION FORCE PARALLEL DML PARALLEL 40;

cpu sec elapsed(sec)
test1 8.92 25.53
test2 0.00 60.19
test3 0.12 10.05

待機イベントPX Deq Credit: send blkdもなくなった:


Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 27.71 34.72
PX Deq: Join ACK 30 0.00 0.00
PX Deq: Parse Reply 30 0.01 0.06
PX Deq: Execute Reply 455 0.13 8.76
Disk file operations I/O 2 0.04 0.04
rdbms ipc reply 34 0.00 0.00
reliable message 30 0.00 0.00
enq: CR - block range reuse ckpt 30 0.84 1.07
PX Deq: Signal ACK RSG 1 0.00 0.00

appendヒントをつけてdirect path writeに変えてみる:
test4. insert /*+ append */ into lineitem_work (select /*+ parallel(L 40) */ * from lineitem L)

またPX Deq Credit: send blkd待機イベントで待ちが発生した:


Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Execution Msg 16 0.01 0.14
direct path read 16 0.01 0.05
PX Deq Credit: send blkd 1765 0.02 7.69
asynch descriptor resize 15 0.00 0.00
PX Deq Credit: need buffer 21 0.00 0.06
PX qref latch 1 0.00 0.00

そこで、DMLもパラレルにして、
test5. ALTER SESSION FORCE PARALLEL DML PARALLEL 40;

cpu sec elapsed(sec)
test1 8.92 25.53
test2 0.00 60.19
test3 0.12 10.05
test4 0.10 08.14
test5 0.00 09.09

あれ?遅くなった。でもdirect path writeで遅くなったのだから、ディスクの問題です:


Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Execution Msg 17 0.10 0.12
Disk file operations I/O 3 0.00 0.00
direct path read 41 0.01 0.08
buffer busy waits 3 0.08 0.09
direct path write 63 0.21 7.23
asynch descriptor resize 12 0.17 1.26
reliable message 1 0.00 0.00
rdbms ipc reply 1 0.00 0.00

parallelでdirect path writeを「問題なく」行うディスク環境は?Readの2倍以上だよな???

最後に、

"PX Deq Credit: send blkd"はアイドルイベントとして扱われるみたいだが、Parallel Queryのチューニングでは重要なイベントです。

MOTS starts one month from today!

The Michigan OakTable Symposium is only one month away!

Have you registered yet? Some of the top Oracle speakers in the world are coming to Ann Arbor, MI September 16-17th, 2010!

The speaker list includes Cary Millsap, Jonathan Lewis, Tanel Poder, Riyaj Shamsudeen, Jeremiah Wilton, and many more!

See the complete list of speakers, with abstracts, and complete registration information, at: MOTS Home page

Don’t miss it!

Refactoring SQL Applications

"The application design is the most important factor in performance"

I've heard the phrase so many times, it doesn't even register on me. Sure the application design is the most important factor in performance, but what do I do about it? Well, "Refactoring SQL Applications" (RSA) is the book I've been waiting for. RSA lays out examples of how application coders tend to think either procedurally or object oriented and how this thinking can completely miss the boat when it comes to writing SQL and interacting with the database.

In RSA, Stephane Faroult, lays out examples of Java code, user defined functions and views and how a coders notions of modular procedural programing and/or object instantiation can run undermine database performance from cookie cutter reuse of code to table joins written in Java. If you've ever seen Tom Kyte's "Developer Super Session - The Best Way", then you know the best way is not to do it and his example is of a coders doing "count(*)" to find out how many loops to do or how big an array to layout. Tom explains the disasters this can create and how the best way is just to not do the count(*) and how this works by letting the database do the work. Letting the database do the work is explained clearly and with many examples from different perspectives in "Refactoring SQL Applications".

Parallel Query 10046 trace

前回の続き、

Parallel Query Trace

USE_CONCATヒントを入れないと、PX関連のeventがwait eventとなっていた:

select A.* from lineitem A, lineitem_org B, lineitem_comp C
where A.l_orderkey = B.l_orderkey
and A.l_orderkey = C.l_orderkey
and (
(A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 3.21 222.80 57423 57838 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.21 222.80 57423 57838 0 0
...
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Deq: Execution Msg 67 0.01 0.03
Disk file operations I/O 1 0.00 0.00
direct path read 506 0.00 1.16
PX Deq Credit: need buffer 253 0.06 0.64
PX Deq Credit: send blkd 8375 0.25 217.64
PX qref latch 5 0.01 0.02
asynch descriptor resize 43 0.00 0.00


PX Deq Credit: send blkd待機イベント

この待機Eventを減らすための定石はParallel度を上げる。
しかし、Parallel度を上げると:

CPUは100% Coreが増える。
そこで、サービスのParallel度を下げると、
PX Deq Credit: send blkd待機イベントは更に増える。

前回の結果一覧表からも分かるとおり、3つのテーブルで1.8GB程度。
それでもDOP=8で3分以上CPU 100%が続く影響は大きい。

Ad-Hocサービスのためには、避けて通れない、重要な監視イベントだ。

Parallel Queryのチューニング

適当に考えて作った問題のSQL:

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
2 where A.l_orderkey = B.l_orderkey
3 and A.l_orderkey = C.l_orderkey
4 and (
5 (A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
6 or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
7 or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
8 or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
9 or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
10 )


少しやり方を変えてみる:


SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
2 where A.l_orderkey = B.l_orderkey
3 and A.l_orderkey = C.l_orderkey
4 and A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%'
5 union
6 select A.* from lineitem A, lineitem_org B, lineitem_comp C
7 where A.l_orderkey = B.l_orderkey
8 and A.l_orderkey = C.l_orderkey
9 and A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%'
10 union
11 select A.* from lineitem A, lineitem_org B, lineitem_comp C
12 where A.l_orderkey = B.l_orderkey
13 and A.l_orderkey = C.l_orderkey
14 and A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%'
15 union
16 select A.* from lineitem A, lineitem_org B, lineitem_comp C
17 where A.l_orderkey = B.l_orderkey
18 and A.l_orderkey = C.l_orderkey
19 and A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%'
20 union
21 select A.* from lineitem A, lineitem_org B, lineitem_comp C
22 where A.l_orderkey = B.l_orderkey
23 and A.l_orderkey = C.l_orderkey
24 and A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%'
25 /

レコードが選択されませんでした。

経過: 00:00:35.94


実行計画
----------------------------------------------------------
Plan hash value: 3432766224

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17M| 2697M| | 1035K (87)| 03:27:08 | | |
| 1 | SORT UNIQUE | | 17M| 2697M| 2868M| 1035K (87)| 03:27:08 | | |
| 2 | UNION-ALL | | | | | | | | |
|* 3 | HASH JOIN | | 2432K| 340M| 87M| 61557 (2)| 00:12:19 | | |
|* 4 | HASH JOIN | | 599K| 80M| 39M| 39831 (2)| 00:07:58 | | |
| 5 | PARTITION RANGE ALL| | 299K| 36M| | 11547 (3)| 00:02:19 | 1 | 84 |
|* 6 | TABLE ACCESS FULL | LINEITEM | 299K| 36M| | 11547 (3)| 00:02:19 | 1 | 84 |
| 7 | TABLE ACCESS FULL | LINEITEM_ORG | 5997K| 80M| | 18849 (2)| 00:03:47 | | |
| 8 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 34M| | 12190 (1)| 00:02:27 | | |
|* 9 | HASH JOIN | | 2497K| 419M| 102M| 93414 (1)| 00:18:41 | | |
| 10 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 34M| | 12190 (1)| 00:02:27 | | |
|* 11 | HASH JOIN | | 615K| 99M| 15M| 70731 (1)| 00:14:09 | | |
|* 12 | TABLE ACCESS FULL | LINEITEM_ORG | 299K| 12M| | 18934 (2)| 00:03:48 | | |
| 13 | PARTITION RANGE ALL| | 5997K| 726M| | 11504 (2)| 00:02:19 | 1 | 84 |
| 14 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| | 11504 (2)| 00:02:19 | 1 | 84 |
|* 15 | HASH JOIN | | 4929K| 691M| 148M| 65572 (2)| 00:13:07 | | |
| 16 | TABLE ACCESS FULL | LINEITEM_ORG | 5997K| 80M| | 18849 (2)| 00:03:47 | | |
|* 17 | HASH JOIN | | 1216K| 154M| 39M| 30903 (2)| 00:06:11 | | |
| 18 | PARTITION RANGE ALL| | 299K| 36M| | 11547 (3)| 00:02:19 | 1 | 84 |
|* 19 | TABLE ACCESS FULL | LINEITEM | 299K| 36M| | 11547 (3)| 00:02:19 | 1 | 84 |
| 20 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 34M| | 12190 (1)| 00:02:27 | | |
|* 21 | HASH JOIN | | 2497K| 419M| 102M| 93414 (1)| 00:18:41 | | |
| 22 | TABLE ACCESS FULL | LINEITEM_ORG | 5997K| 34M| | 18773 (1)| 00:03:46 | | |
|* 23 | HASH JOIN | | 615K| 99M| 15M| 64149 (1)| 00:12:50 | | |
|* 24 | TABLE ACCESS FULL | LINEITEM_COMP | 299K| 12M| | 12351 (3)| 00:02:29 | | |
| 25 | PARTITION RANGE ALL| | 5997K| 726M| | 11504 (2)| 00:02:19 | 1 | 84 |
| 26 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| | 11504 (2)| 00:02:19 | 1 | 84 |
|* 27 | HASH JOIN | | 4929K| 827M| 102M| 98597 (1)| 00:19:44 | | |
| 28 | TABLE ACCESS FULL | LINEITEM_ORG | 5997K| 34M| | 18773 (1)| 00:03:46 | | |
|* 29 | HASH JOIN | | 1215K| 197M| 15M| 64149 (1)| 00:12:50 | | |
|* 30 | TABLE ACCESS FULL | LINEITEM_COMP | 299K| 12M| | 12351 (3)| 00:02:29 | | |
| 31 | PARTITION RANGE ALL| | 5997K| 726M| | 11504 (2)| 00:02:19 | 1 | 84 |
| 32 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| | 11504 (2)| 00:02:19 | 1 | 84 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
4 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE"<"B"."L_COMMITDATE")
6 - filter("A"."L_COMMENT" LIKE '%obata%')
9 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
11 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE">"B"."L_COMMITDATE")
12 - filter("B"."L_COMMENT" LIKE '%tanaka%' AND "B"."L_COMMENT" IS NOT NULL)
15 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE"<>"B"."L_COMMITDATE")
17 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
19 - filter("A"."L_COMMENT" LIKE '%ichiro%')
21 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
23 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE">"C"."L_COMMITDATE")
24 - filter("C"."L_COMMENT" LIKE '%tanaka%' AND "C"."L_COMMENT" IS NOT NULL)
27 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
29 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")
30 - filter("C"."L_COMMENT" LIKE '%ichiro%' AND "C"."L_COMMENT" IS NOT NULL)


統計
----------------------------------------------------------
313 recursive calls
0 db block gets
732359 consistent gets
213779 physical reads
0 redo size
1441 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed


おぉ!Non ParallelでParallel度=2とほぼ同じ実行時間の36秒になった。

そうか!!!Parallel度=2は、1 slaveでシーケンシャルにReadするだけだから、結果としてこれと同じような動きになるんだ。

USE_CONCATヒントを入れOR条件をUNION ALLに変えてもらうようにすると:

No Hint

DOP MI:SS physical reads x8K(block size)
no parallel 14:33.61 990101 7.55GB
parallel 2 00:32.26 637866 4.87GB
parallel 3 04:38.49 229981 1.8GB
parallel 4 03:48.20 229981 1.8GB
parallel 8 02:58.65 229981 1.8GB

USE_CONCAT

DOP MI:SS physical reads x8K(block size)
no parallel 00:30.57 285878 2.23GB
parallel 2 00:16.03 357962 2.8GB
parallel 3 00:12.57 357962 2.8GB
parallel 4 00:10.68 357962 2.8GB
parallel 8 00:07.90 357962 2.8GB

ちゃんと、Parallel度の増加とともに実行時間は減りました。

Parallel Queryを使っていると、Hintを入れるという感覚を忘れてしまう。
でも、このパターンはAd-Hoc SQLの定番なんだよな。

Alex Hutton Podcast on data breach

Lindsay blogged about the recent data breach report from Verizon last week. Alex Hutton, one of the authors has just re-tweeted DennisF's tweet that he has done a podcast about the data breach report . Enjoy! EDITED: I incorrectly added....[Read More]

Posted by Pete On 16/08/10 At 07:46 PM

Parallel Queryを検証する

TPC-Hベンチマークの続き

CPUはたいして回らなかった、そしてdirect path read待ちばかり、、、
ディスク転送量は限界に近づくどころか、むしろ下がっていった。
絶対におかしい!!!

Parallel Queryを検証する

初心に帰って、Parallel Queryが「どのくらい価値のあるものなのか?」を検証してみた。

過去の検証で作った大きなテーブルを使い負荷の高そうなSQLを適当に作った。
LINEITEM     : partitioning+compress
LINEITEM_COMP  : compress
LINEITEM_ORG   : オリジナル

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
2 where A.l_orderkey = B.l_orderkey
3 and A.l_orderkey = C.l_orderkey
4 and (
5 (A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
6 or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
7 or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
8 or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
9 or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
10 )


まずはNO_PARALLELで動かしてみる:

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 1;

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
2 where A.l_orderkey = B.l_orderkey
3 and A.l_orderkey = C.l_orderkey
4 and (
5 (A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
6 or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
7 or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
8 or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
9 or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
10 )
11 /

レコードが選択されませんでした。

経過: 00:14:33.61


実行計画
----------------------------------------------------------
Plan hash value: 2728639899
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17M| 3521M| | 331K(1)| 01:06:23 | | |
|* 1 | HASH JOIN | | 17M| 3521M| 468M| 331K(1)| 01:06:23 | | |
| 2 | TABLE ACCESS FULL | LINEITEM_ORG | 8476K| 371M| | 18970(2)| 00:03:48 | | |
|* 3 | HASH JOIN | | 24M| 3943M| 314M| 79115(1)| 00:15:50 | | |
| 4 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 245M| | 12309(2)| 00:02:28 | | |
| 5 | PARTITION RANGE ALL| | 5997K| 726M| | 11504(2)| 00:02:19 | 1 | 84 |
| 6 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| | 11504(2)| 00:02:19 | 1 | 84 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_COMMENT" LIKE '%obata%' AND "A"."L_RECEIPTDATE"<"B"."L_COMMITDATE" OR
"B"."L_COMMENT" LIKE '%tanaka%' AND "A"."L_RECEIPTDATE">"B"."L_COMMITDATE" AND "B"."L_COMMENT" IS NOT
NULL OR "A"."L_COMMENT" LIKE '%ichiro%' AND "A"."L_RECEIPTDATE"<>"B"."L_COMMITDATE" OR "C"."L_COMMENT"
LIKE '%tanaka%' AND "A"."L_RECEIPTDATE">"C"."L_COMMITDATE" AND "C"."L_COMMENT" IS NOT NULL OR
"C"."L_COMMENT" LIKE '%ichiro%' AND "C"."L_COMMENT" IS NOT NULL AND
"A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")
3 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")


統計
----------------------------------------------------------
6136 recursive calls
0 db block gets
230506 consistent gets
990101 physical reads
7516 redo size
1441 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


なるほど遅い。14分33秒かかった。

次にパラレル度2で動かしてみる:

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
2 where A.l_orderkey = B.l_orderkey
3 and A.l_orderkey = C.l_orderkey
4 and (
5 (A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
6 or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
7 or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
8 or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
9 or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
10 )
11 ;

レコードが選択されませんでした。

経過: 00:00:32.26


実行計画
----------------------------------------------------------
Plan hash value: 854440364

---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17M| 3521M| 118K (2)| 00:23:47 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10012 | | | | | | | Q1,12 | P->S | QC (RAND) |
| 3 | BUFFER SORT | | 17M| 3521M| | | | | Q1,12 | PCWP | |
| 4 | CONCATENATION | | | | | | | | Q1,12 | PCWP | |
|* 5 | HASH JOIN | | 6966K| 1435M| 23787 (2)| 00:04:46 | | | Q1,12 | PCWP | |
| 6 | PX RECEIVE | | 1215K| 197M| 13240 (2)| 00:02:39 | | | Q1,12 | PCWP | |
| 7 | PX SEND HASH | :TQ10005 | 1215K| 197M| 13240 (2)| 00:02:39 | | | Q1,05 | P->P | HASH |
| 8 | BUFFER SORT | | 17M| 3521M| | | | | Q1,05 | PCWP | |
|* 9 | HASH JOIN | | 1215K| 197M| 13240 (2)| 00:02:39 | | | Q1,05 | PCWP | |
| 10 | PX RECEIVE | | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,05 | PCWP | |
| 11 | PX SEND BROADCAST | :TQ10000 | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,00 | P->P | BROADCAST |
| 12 | BUFFER SORT | | 17M| 3521M| | | | | Q1,00 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,00 | PCWC | |
|* 14 | TABLE ACCESS FULL| LINEITEM_COMP | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,00 | PCWP | |
| 15 | PX BLOCK ITERATOR | | 5997K| 726M| 6374 (2)| 00:01:17 | 1 | 84 | Q1,05 | PCWC | |
| 16 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| 6374 (2)| 00:01:17 | 1 | 84 | Q1,05 | PCWP | |
| 17 | PX RECEIVE | | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,12 | PCWP | |
| 18 | PX SEND HASH | :TQ10006 | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,06 | P->P | HASH |
| 19 | BUFFER SORT | | 17M| 3521M| | | | | Q1,06 | PCWP | |
| 20 | PX BLOCK ITERATOR | | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,06 | PCWC | |
| 21 | TABLE ACCESS FULL | LINEITEM_ORG | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,06 | PCWP | |
|* 22 | HASH JOIN | | 3353K| 690M| 23783 (2)| 00:04:46 | | | Q1,12 | PCWP | |
| 23 | PX RECEIVE | | 585K| 94M| 13240 (2)| 00:02:39 | | | Q1,12 | PCWP | |
| 24 | PX SEND BROADCAST | :TQ10007 | 585K| 94M| 13240 (2)| 00:02:39 | | | Q1,07 | P->P | BROADCAST |
| 25 | BUFFER SORT | | 17M| 3521M| | | | | Q1,07 | PCWP | |
|* 26 | HASH JOIN | | 585K| 94M| 13240 (2)| 00:02:39 | | | Q1,07 | PCWP | |
| 27 | PX RECEIVE | | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,07 | PCWP | |
| 28 | PX SEND BROADCAST | :TQ10001 | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,01 | P->P | BROADCAST |
| 29 | BUFFER SORT | | 17M| 3521M| | | | | Q1,01 | PCWP | |
| 30 | PX BLOCK ITERATOR | | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,01 | PCWC | |
|* 31 | TABLE ACCESS FULL| LINEITEM_COMP | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,01 | PCWP | |
| 32 | PX BLOCK ITERATOR | | 5997K| 726M| 6374 (2)| 00:01:17 | 1 | 84 | Q1,07 | PCWC | |
| 33 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| 6374 (2)| 00:01:17 | 1 | 84 | Q1,07 | PCWP | |
| 34 | PX BLOCK ITERATOR | | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,12 | PCWC | |
| 35 | TABLE ACCESS FULL | LINEITEM_ORG | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,12 | PCWP | |
|* 36 | HASH JOIN | | 6451K| 1328M| 23786 (2)| 00:04:46 | | | Q1,12 | PCWP | |
| 37 | PX RECEIVE | | 1126K| 182M| 13240 (2)| 00:02:39 | | | Q1,12 | PCWP | |
| 38 | PX SEND HASH | :TQ10008 | 1126K| 182M| 13240 (2)| 00:02:39 | | | Q1,08 | P->P | HASH |
| 39 | BUFFER SORT | | 17M| 3521M| | | | | Q1,08 | PCWP | |
|* 40 | HASH JOIN | | 1126K| 182M| 13240 (2)| 00:02:39 | | | Q1,08 | PCWP | |
| 41 | PX RECEIVE | | 299K| 36M| 6397 (3)| 00:01:17 | | | Q1,08 | PCWP | |
| 42 | PX SEND BROADCAST | :TQ10002 | 299K| 36M| 6397 (3)| 00:01:17 | | | Q1,02 | P->P | BROADCAST |
| 43 | BUFFER SORT | | 17M| 3521M| | | | | Q1,02 | PCWP | |
| 44 | PX BLOCK ITERATOR | | 299K| 36M| 6397 (3)| 00:01:17 | 1 | 84 | Q1,02 | PCWC | |
|* 45 | TABLE ACCESS FULL| LINEITEM | 299K| 36M| 6397 (3)| 00:01:17 | 1 | 84 | Q1,02 | PCWP | |
| 46 | PX BLOCK ITERATOR | | 5997K| 245M| 6819 (2)| 00:01:22 | | | Q1,08 | PCWC | |
| 47 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 245M| 6819 (2)| 00:01:22 | | | Q1,08 | PCWP | |
| 48 | PX RECEIVE | | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,12 | PCWP | |
| 49 | PX SEND HASH | :TQ10009 | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,09 | P->P | HASH |
| 50 | BUFFER SORT | | 17M| 3521M| | | | | Q1,09 | PCWP | |
| 51 | PX BLOCK ITERATOR | | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,09 | PCWC | |
| 52 | TABLE ACCESS FULL | LINEITEM_ORG | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,09 | PCWP | |
|* 53 | HASH JOIN | | 15328 | 3233K| 23755 (2)| 00:04:46 | | | Q1,12 | PCWP | |
| 54 | PX RECEIVE | | 4082 | 689K| 16914 (2)| 00:03:23 | | | Q1,12 | PCWP | |
| 55 | PX SEND BROADCAST | :TQ10010 | 4082 | 689K| 16914 (2)| 00:03:23 | | | Q1,10 | P->P | BROADCAST |
| 56 | BUFFER SORT | | 17M| 3521M| | | | | Q1,10 | PCWP | |
|* 57 | HASH JOIN | | 4082 | 689K| 16914 (2)| 00:03:23 | | | Q1,10 | PCWP | |
| 58 | PX RECEIVE | | 21191 | 951K| 10518 (2)| 00:02:07 | | | Q1,10 | PCWP | |
| 59 | PX SEND BROADCAST | :TQ10003 | 21191 | 951K| 10518 (2)| 00:02:07 | | | Q1,03 | P->P | BROADCAST |
| 60 | BUFFER SORT | | 17M| 3521M| | | | | Q1,03 | PCWP | |
| 61 | PX BLOCK ITERATOR | | 21191 | 951K| 10518 (2)| 00:02:07 | | | Q1,03 | PCWC | |
|* 62 | TABLE ACCESS FULL| LINEITEM_ORG | 21191 | 951K| 10518 (2)| 00:02:07 | | | Q1,03 | PCWP | |
| 63 | PX BLOCK ITERATOR | | 5997K| 726M| 6374 (2)| 00:01:17 | 1 | 84 | Q1,10 | PCWC | |
| 64 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| 6374 (2)| 00:01:17 | 1 | 84 | Q1,10 | PCWP | |
| 65 | PX BLOCK ITERATOR | | 5997K| 245M| 6819 (2)| 00:01:22 | | | Q1,12 | PCWC | |
| 66 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 245M| 6819 (2)| 00:01:22 | | | Q1,12 | PCWP | |
|* 67 | HASH JOIN | | 306K| 63M| 23780 (2)| 00:04:46 | | | Q1,12 | PCWP | |
| 68 | PX RECEIVE | | 81629 | 13M| 16939 (2)| 00:03:24 | | | Q1,12 | PCWP | |
| 69 | PX SEND BROADCAST | :TQ10011 | 81629 | 13M| 16939 (2)| 00:03:24 | | | Q1,11 | P->P | BROADCAST |
| 70 | BUFFER SORT | | 17M| 3521M| | | | | Q1,11 | PCWP | |
|* 71 | HASH JOIN | | 81629 | 13M| 16939 (2)| 00:03:24 | | | Q1,11 | PCWP | |
| 72 | PX RECEIVE | | 299K| 36M| 6397 (3)| 00:01:17 | | | Q1,11 | PCWP | |
| 73 | PX SEND BROADCAST | :TQ10004 | 299K| 36M| 6397 (3)| 00:01:17 | | | Q1,04 | P->P | BROADCAST |
| 74 | BUFFER SORT | | 17M| 3521M| | | | | Q1,04 | PCWP | |
| 75 | PX BLOCK ITERATOR | | 299K| 36M| 6397 (3)| 00:01:17 | 1 | 84 | Q1,04 | PCWC | |
|* 76 | TABLE ACCESS FULL| LINEITEM | 299K| 36M| 6397 (3)| 00:01:17 | 1 | 84 | Q1,04 | PCWP | |
| 77 | PX BLOCK ITERATOR | | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,11 | PCWC | |
| 78 | TABLE ACCESS FULL | LINEITEM_ORG | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,11 | PCWP | |
| 79 | PX BLOCK ITERATOR | | 5997K| 245M| 6819 (2)| 00:01:22 | | | Q1,12 | PCWC | |
| 80 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 245M| 6819 (2)| 00:01:22 | | | Q1,12 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
9 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")
14 - filter("C"."L_COMMENT" LIKE '%ichiro%' AND "C"."L_COMMENT" IS NOT NULL)
22 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
26 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE">"C"."L_COMMITDATE" AND (LNNVL("C"."L_COMMENT" LIKE '%ichiro%') OR LNNVL("C"."L_COMMENT" IS NOT
NULL) OR LNNVL("A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")))
31 - filter("C"."L_COMMENT" LIKE '%tanaka%' AND "C"."L_COMMENT" IS NOT NULL)
36 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE"<>"B"."L_COMMITDATE")
40 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter((LNNVL("C"."L_COMMENT" LIKE '%tanaka%') OR LNNVL("A"."L_RECEIPTDATE">"C"."L_COMMITDATE") OR LNNVL("C"."L_COMMENT" IS
NOT NULL)) AND (LNNVL("C"."L_COMMENT" LIKE '%ichiro%') OR LNNVL("C"."L_COMMENT" IS NOT NULL) OR
LNNVL("A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")))
45 - filter("A"."L_COMMENT" LIKE '%ichiro%')
53 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter((LNNVL("C"."L_COMMENT" LIKE '%tanaka%') OR LNNVL("A"."L_RECEIPTDATE">"C"."L_COMMITDATE") OR LNNVL("C"."L_COMMENT" IS
NOT NULL)) AND (LNNVL("C"."L_COMMENT" LIKE '%ichiro%') OR LNNVL("C"."L_COMMENT" IS NOT NULL) OR
LNNVL("A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")))
57 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE">"B"."L_COMMITDATE" AND (LNNVL("A"."L_COMMENT" LIKE '%ichiro%') OR
LNNVL("A"."L_RECEIPTDATE"<>"B"."L_COMMITDATE")))
62 - filter("B"."L_COMMENT" IS NOT NULL AND "B"."L_COMMENT" LIKE '%tanaka%')
67 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter((LNNVL("C"."L_COMMENT" LIKE '%tanaka%') OR LNNVL("A"."L_RECEIPTDATE">"C"."L_COMMITDATE") OR LNNVL("C"."L_COMMENT" IS
NOT NULL)) AND (LNNVL("C"."L_COMMENT" LIKE '%ichiro%') OR LNNVL("C"."L_COMMENT" IS NOT NULL) OR
LNNVL("A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")))
71 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE"<"B"."L_COMMITDATE" AND (LNNVL("B"."L_COMMENT" LIKE '%tanaka%') OR
LNNVL("A"."L_RECEIPTDATE">"B"."L_COMMITDATE") OR LNNVL("B"."L_COMMENT" IS NOT NULL)) AND (LNNVL("A"."L_COMMENT" LIKE '%ichiro%') OR
LNNVL("A"."L_RECEIPTDATE"<>"B"."L_COMMITDATE")))
76 - filter("A"."L_COMMENT" LIKE '%obata%')


統計
----------------------------------------------------------
616 recursive calls
8 db block gets
566686 consistent gets
637866 physical reads
0 redo size
1441 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
22 sorts (memory)
4 sorts (disk)
0 rows processed

なるほど速い。32秒で終わった。
27倍の効果があった。

Parallel度をもう少し増やしてみる:

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 3;

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
2 where A.l_orderkey = B.l_orderkey
3 and A.l_orderkey = C.l_orderkey
4 and (
5 (A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
6 or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
7 or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
8 or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
9 or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
10 )
11 ;

レコードが選択されませんでした。

経過: 00:04:38.49

実行計画
----------------------------------------------------------
Plan hash value: 3950163066

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17M| 3521M| | 38329 (2)| 00:07:40 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 17M| 3521M| | 38329 (2)| 00:07:40 | | | Q1,03 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 17M| 3521M| | 38329 (2)| 00:07:40 | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 5997K| 245M| | 4871 (2)| 00:00:59 | | | Q1,03 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 5997K| 245M| | 4871 (2)| 00:00:59 | | | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 5997K| 245M| | 4871 (2)| 00:00:59 | | | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 245M| | 4871 (2)| 00:00:59 | | | Q1,00 | PCWP | |
|* 8 | HASH JOIN | | 34M| 5671M| 156M| 33083 (1)| 00:06:37 | | | Q1,03 | PCWP | |
| 9 | PX RECEIVE | | 8476K| 371M| | 7507 (2)| 00:01:31 | | | Q1,03 | PCWP | |
| 10 | PX SEND HASH | :TQ10001 | 8476K| 371M| | 7507 (2)| 00:01:31 | | | Q1,01 | P->P | HASH |
| 11 | PX BLOCK ITERATOR | | 8476K| 371M| | 7507 (2)| 00:01:31 | | | Q1,01 | PCWC | |
| 12 | TABLE ACCESS FULL| LINEITEM_ORG | 8476K| 371M| | 7507 (2)| 00:01:31 | | | Q1,01 | PCWP | |
| 13 | PX RECEIVE | | 5997K| 726M| | 4552 (2)| 00:00:55 | | | Q1,03 | PCWP | |
| 14 | PX SEND HASH | :TQ10002 | 5997K| 726M| | 4552 (2)| 00:00:55 | | | Q1,02 | P->P | HASH |
| 15 | PX BLOCK ITERATOR | | 5997K| 726M| | 4552 (2)| 00:00:55 | 1 | 84 | Q1,02 | PCWC | |
| 16 | TABLE ACCESS FULL| LINEITEM | 5997K| 726M| | 4552 (2)| 00:00:55 | 1 | 84 | Q1,02 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter("A"."L_COMMENT" LIKE '%obata%' AND "A"."L_RECEIPTDATE"<"B"."L_COMMITDATE" OR "B"."L_COMMENT" LIKE '%tanaka%' AND
"A"."L_RECEIPTDATE">"B"."L_COMMITDATE" AND "B"."L_COMMENT" IS NOT NULL OR "A"."L_COMMENT" LIKE '%ichiro%' AND
"A"."L_RECEIPTDATE"<>"B"."L_COMMITDATE" OR "C"."L_COMMENT" LIKE '%tanaka%' AND "A"."L_RECEIPTDATE">"C"."L_COMMITDATE" AND
"C"."L_COMMENT" IS NOT NULL OR "C"."L_COMMENT" LIKE '%ichiro%' AND "C"."L_COMMENT" IS NOT NULL AND
"A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")
8 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")


統計
----------------------------------------------------------
31 recursive calls
0 db block gets
231720 consistent gets
229981 physical reads
0 redo size
1441 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

げっ!遅くなった。
11倍遅くなった。

結果を表にすると

DOP MI:SS physical reads x8K(block size)
no parallel 14:33.61 990101 7.55GB
parallel 2 00:32.26 637866 4.87GB
parallel 3 04:38.49 229981 1.8GB
parallel 4 03:48.20 229981 1.8GB
parallel 8 02:58.65 229981 1.8GB

なぜ???