Search

OakieTags

Who's online

There are currently 0 users and 24 guests online.

Recent comments

Optimizer

automatic DOP

nocompressでIn-Memory Parallel Query の中でCOMPRESS->NOCOMPRESSにしたらパラレル度が自動的に4->6になった。

NOCOMPRESSだとパラレル度は上がるのか?

NOCOMPRESSのlineitemをSelectする:

SQL> alter system flush buffer_cache;

システムが変更されました。

SQL> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty
2 , sum(l_extendedprice) as sum_base_price
3 , sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
4 , sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
5 , avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price
6 , avg(l_discount) as avg_disc
7 , count(*) as count_order
8 from lineitem
9 where l_shipdate <= date '1992-12-01' - interval '68' day (3)
10 group by l_returnflag, l_linestatus
11 order by l_returnflag, l_linestatus
12 /

経過: 00:00:02.03

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

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 135 | 6956 (1)| 00:01:24 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 5 | 135 | 6956 (1)| 00:01:24 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 5 | 135 | 6956 (1)| 00:01:24 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 5 | 135 | 6956 (1)| 00:01:24 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 5 | 135 | 6956 (1)| 00:01:24 | Q1,00 | P->P | RANGE |
| 6 | HASH GROUP BY | | 5 | 135 | 6956 (1)| 00:01:24 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 634K| 16M| 6941 (1)| 00:01:24 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| LINEITEM | 634K| 16M| 6941 (1)| 00:01:24 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

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

8 - filter("L_SHIPDATE"<=TO_DATE(' 1992-09-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
- automatic DOP: Computed Degree of Parallelism is 3


統計
----------------------------------------------------------
22 recursive calls
4 db block gets
106301 consistent gets
102004 physical reads
0 redo size
1456 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed

DOP=3が自動的に設定され、2.03秒かかった。
automatic DOP: Computed Degree of Parallelism is 3

次にCOMPRESSされたlineitemをSelectしてみる:


SQL> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty
2 , sum(l_extendedprice) as sum_base_price
3 , sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
4 , sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
5 , avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price
6 , avg(l_discount) as avg_disc
7 , count(*) as count_order
8 from lineitem_comp
9 where l_shipdate <= date '1992-12-01' - interval '68' day (3)
10 group by l_returnflag, l_linestatus
11 order by l_returnflag, l_linestatus
12 /

経過: 00:00:01.51

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

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 135 | 6787 (2)| 00:01:22 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 5 | 135 | 6787 (2)| 00:01:22 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 5 | 135 | 6787 (2)| 00:01:22 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 5 | 135 | 6787 (2)| 00:01:22 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 5 | 135 | 6787 (2)| 00:01:22 | Q1,00 | P->P | RANGE |
| 6 | HASH GROUP BY | | 5 | 135 | 6787 (2)| 00:01:22 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 634K| 16M| 6765 (1)| 00:01:22 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| LINEITEM_COMP | 634K| 16M| 6765 (1)| 00:01:22 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------

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

8 - filter("L_SHIPDATE"<=TO_DATE(' 1992-09-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
- automatic DOP: Computed Degree of Parallelism is 2


統計
----------------------------------------------------------
16 recursive calls
4 db block gets
67041 consistent gets
66958 physical reads
0 redo size
1456 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
2 rows processed

DOP=2が自動的に設定され、1.51秒で終わった。

まとめてみる

NOCOMPRESS   automatic DOP=3
COMPRESS    automatic DOP=2

NOCOMPRESSの方がDOPが大きく設定される。 --->アンコンプレスの処理負荷を考慮しているのだろうか?

実行時間は
NOCOMPRESS  2.01秒
COMPRESS   1.51秒

COMPRESSの方が速い。それはphysical read量に比例する
 102004  physical reads
  66958  physical reads

だから、Parallel QueryはCOMPRESSが有利
でも、In-Memory Parallel Queryだと

上記のテスト後にもう一度二つのSQLを実行してみる:


SQL> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty
2 , sum(l_extendedprice) as sum_base_price
3 , sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
4 , sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
5 , avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price
6 , avg(l_discount) as avg_disc
7 , count(*) as count_order
8 from lineitem
9 where l_shipdate <= date '1992-12-01' - interval '68' day (3)
10 group by l_returnflag, l_linestatus
11 order by l_returnflag, l_linestatus
12 /

経過: 00:00:00.33

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

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 135 | 6956 (1)| 00:01:24 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 5 | 135 | 6956 (1)| 00:01:24 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 5 | 135 | 6956 (1)| 00:01:24 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 5 | 135 | 6956 (1)| 00:01:24 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 5 | 135 | 6956 (1)| 00:01:24 | Q1,00 | P->P | RANGE |
| 6 | HASH GROUP BY | | 5 | 135 | 6956 (1)| 00:01:24 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 634K| 16M| 6941 (1)| 00:01:24 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| LINEITEM | 634K| 16M| 6941 (1)| 00:01:24 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

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

8 - filter("L_SHIPDATE"<=TO_DATE(' 1992-09-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
- automatic DOP: Computed Degree of Parallelism is 3


統計
----------------------------------------------------------
34 recursive calls
4 db block gets
106301 consistent gets
0 physical reads
0 redo size
1456 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed

SQL> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty
2 , sum(l_extendedprice) as sum_base_price
3 , sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
4 , sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
5 , avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price
6 , avg(l_discount) as avg_disc
7 , count(*) as count_order
8 from lineitem_comp
9 where l_shipdate <= date '1992-12-01' - interval '68' day (3)
10 group by l_returnflag, l_linestatus
11 order by l_returnflag, l_linestatus
12 /

経過: 00:00:00.62

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

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 135 | 6787 (2)| 00:01:22 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 5 | 135 | 6787 (2)| 00:01:22 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 5 | 135 | 6787 (2)| 00:01:22 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 5 | 135 | 6787 (2)| 00:01:22 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 5 | 135 | 6787 (2)| 00:01:22 | Q1,00 | P->P | RANGE |
| 6 | HASH GROUP BY | | 5 | 135 | 6787 (2)| 00:01:22 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 634K| 16M| 6765 (1)| 00:01:22 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| LINEITEM_COMP | 634K| 16M| 6765 (1)| 00:01:22 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------

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

8 - filter("L_SHIPDATE"<=TO_DATE(' 1992-09-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
- automatic DOP: Computed Degree of Parallelism is 2


統計
----------------------------------------------------------
16 recursive calls
4 db block gets
67041 consistent gets
0 physical reads
0 redo size
1456 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
2 rows processed

一回目のSQLでBuffer_Cache上にデータがあるのでphysical reads=0となる。

NOCOMPRESS   automatic DOP=3
COMPRESS    automatic DOP=2

実行時間は
NOCOMPRESS  0.33秒
COMPRESS   0.62秒

consistent gets量が多いにもかかわらずNOCOMPRESSの方が2倍速い。
 106301   consistent gets
 67041   consistent gets

でも、その差はたった0.3秒。しかし、その僅かな差を積み上げると:

という結果になった

result_cacheで更に50%アップ

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

In-Memory-nonParallelでの最高結果は同時8セッションで:

今度はResult_Cacheを使ってみると:
同じ8セッションで最高値15000qphを記録した。

result_cacheが効く場合と効かない場合

TPC-HベンチマークのHeavy SQL Top5からひとつ選んで「Where条件」を変えるテストを行ってみた。

buffer_cache flush後


SQL> select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
2 from customer, orders, lineitem
3 where o_orderkey in
4 ( select l_orderkey
5 from lineitem
6 group by l_orderkey
7 having sum(l_quantity) > 312)
8 and c_custkey = o_custkey
9 and o_orderkey = l_orderkey
10 group by c_name, c_custkey
11 , o_orderkey
12 , o_orderdate
13 , o_totalprice
14 order by o_totalprice desc, o_orderdate;
...
...
15行が選択されました。

経過: 00:00:08.52

統計
----------------------------------------------------------
1 recursive calls
0 db block gets
145349 consistent gets
83465 physical reads
0 redo size
1725 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
15 rows processed

8.52秒かかった(physical readsが発生しているから)

having sum(l_quantity) > 312)を313に変えて実行してみる


SQL> select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
2 from customer, orders, lineitem
3 where o_orderkey in
4 ( select l_orderkey
5 from lineitem
6 group by l_orderkey
7 having sum(l_quantity) > 313)
8 and c_custkey = o_custkey
9 and o_orderkey = l_orderkey
10 group by c_name, c_custkey
11 , o_orderkey
12 , o_orderdate
13 , o_totalprice
14 order by o_totalprice desc, o_orderdate;
...
...
12行が選択されました。

経過: 00:00:00.01

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
1563 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed

択された行数も違うのにresult_cacheが効いて0.01秒で終わった。

もう一度312に戻すと


SQL> select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
2 from customer, orders, lineitem
3 where o_orderkey in
4 ( select l_orderkey
5 from lineitem
6 group by l_orderkey
7 having sum(l_quantity) > 312)
8 and c_custkey = o_custkey
9 and o_orderkey = l_orderkey
10 group by c_name, c_custkey
11 , o_orderkey
12 , o_orderdate
13 , o_totalprice
14 order by o_totalprice desc, o_orderdate;
...
...
15行が選択されました。

経過: 00:00:00.02

統計
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
1725 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed

recursive callが発行され0.02秒となったが、result_cacheは効いた。

今度は314にしてみる


SQL> select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
2 from customer, orders, lineitem
3 where o_orderkey in
4 ( select l_orderkey
5 from lineitem
6 group by l_orderkey
7 having sum(l_quantity) > 314)
8 and c_custkey = o_custkey
9 and o_orderkey = l_orderkey
10 group by c_name, c_custkey
11 , o_orderkey
12 , o_orderdate
13 , o_totalprice
14 order by o_totalprice desc, o_orderdate;
...
...

10行が選択されました。

経過: 00:00:04.17

統計
----------------------------------------------------------
1 recursive calls
0 db block gets
145349 consistent gets
0 physical reads
0 redo size
1463 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

result_cacheは効かなかった。しかし、buffer_cache scan(physical reads=0)で4.17秒で終わった。

結果がまったく同じでなくともresult_cacheは働く。
その理屈は、僕には説明できない。

因みに、Exadata1のTPC-Hベンチマークでも:
result_cache_mode = FORCE を設定している。

"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

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

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 /

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

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

Daylight Saving Time Causes Performance Issues!

Since I’m starting employment with Pythian on Monday, this is the last technical post that is exclusive to this blog. Future technical tips, discoveries and stories will appear on the Pythian blog, and either copied here or linked to from here. BTW. I already made my first post in the Pythian blog today, as the editor of the Log Buffer – and I’m not even an employee yet :)

So, lets end this with a bang. Real life story on how Daylight Saving Time caused one query to take twice as long as before! And its not even an April Fools joke :)

Optimizer partition oddities, part 2: List partitioning

Back to part 1

Some time ago on the OTN forum the following table layout was part of a discussion regarding performance issues and it revealed an interesting anomaly regarding list partition pruning:

If you're using list partitioning with partitions that use multiple values that map to a single list partition then the optimizer obviously uses a questionable approach when you're using multiple values on the partition key to prune to a single partition.

Consider the following table layout:

CREATE TABLE XYZ
(
TICKER VARCHAR2(22 BYTE) NOT NULL,
EXCH_CODE VARCHAR2(25 BYTE) NOT NULL,
ID_ISIN VARCHAR2(12 BYTE),
HIGH_52WEEK NUMBER(28,10),
LOW_52WEEK NUMBER(28,10),
PX_OPEN NUMBER(28,10),
PX_HIGH NUMBER(28,10),
BLOOMBERG_FILE_SOURCE VARCHAR2(100 BYTE),
LATEST_VERSION_FLAG CHAR(1 BYTE)
)
PARTITION BY LIST (EXCH_CODE)
(
PARTITION BBO_ASIA VALUES ('SL','IS','SP','JF','JN','PK','KP','VM','JS','IN','TB','KQ','JP','NV','JJ','MK','HK','IJ','JT','TT','PA','CS','JX',
'IB','AU','FS','VN','NZ','KS','PM','CH','BD','JQ','VH','CG','JO')
,
PARTITION BBO_NAMR VALUES ('UO','US','UN','PQ','TR','UD','UP','TX','UL','UB','UU','UX','UT','TN','UQ','UR','UW','UV','TA','CT','CV','UC','CJ','UA','UM','CN','UF','CF')
,
PARTITION BBO_LAMR VALUES ('AR','BS','AC','CR','EG','EK','VB','BN','EQ','PE','AF','CX','KY','CC','MM','BM','TP','BV','BH','UY','BZ','ED','VC','VS','BO','CI','CB','PP','BA','JA','CE')
,
PARTITION BBO_EURO VALUES (DEFAULT)
);

I'm now going to populate that table using this sample data: