Search

OakieTags

Who's online

There are currently 0 users and 32 guests online.

Recent comments

Affiliations

Optimizer

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: