Optimizer

適当に考えて作った問題の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  /

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

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

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

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:

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