Search

Top 60 Oracle Blogs

Recent comments

August 2010

Wirth’s Law…

I was scooting around the net and I stumbled on a reference to Wirth’s Law and had a flashback (not Nam related) to a conversation I had about 14 years ago with my boss at the time. We were setting up the kit for a new automated warehouse solution (Oracle 7, HP 9000s and ServiceGuard if I remember correctly) and he said something along the lines of, “Why is it that for each customer we buy faster and more expensive computers, yet they take the same length of time to produce the results?”

The answer was pretty simple in that case. We were refurbishing the existing (fairly simple) warehouse as well as adding a completely new one. We were replacing some AVGs with a very complex conveyor layout, which required some difficult routing decisions. The basic “find me a space in the warehouse” decisions were replaced by pretty complex searches that had to take account of conveyor routing, system load and potentional sorting (and defragmentation) of the content in the warehouse. The customer needed a highly available solution, hence the use of ServiceGuard, so we more than doubled the hardware and software costs for no perceivable performance improvement. From the outside looking in it seemed like nothing had changed. It was still, “Here’s a pallet, put it in the racking”, but the process required to do that operation efficiently had increased in complexity manyfold.

Welcome to the universe (again)…

A little over two years ago I wrote about a new arrival in New Zealand. I got a call last night to tell me about a new addition to the population of New Zealand, so I would like to take a moment to formally welcome Naomi Freya Lukic to the universe. Thanks to the wonders of Skype I also got to see the young lady in question.

Daughter number one (Ruby) is blonde with fair skin and pale eyes. Daughter number two (Naomi) has black hair, olive skin and dark blue eyes. I guess they need to get to work on daughter number three, who should be a red head to complete the set. :)

It’s fantastic news and it has left me with a permanent grin this morning.

Using my psychic abilities I predict lots of sleepless nights, crying and smelly stuff… :)

Cheers

Tim…

OS File Cacheで6倍のスピード

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

前回の「result_cacheで更に50%アップ」と同じようにOS File Cacheも働いてくれる。

例によって、Heavy Top5から(今回はSSDではなくHDDを使用)、

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 1;
SQL> alter system flush buffer_cache;
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:01:13.63

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

パラレル度=1で全てfull scanされ1分13秒かかった。

もう一度、buffer_cacheをflushしてから実行すると:

SQL> alter system flush buffer_cache;
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:12.60

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

physical reads量は同じにもかかわらず、12.6秒で終わった。
約6倍短縮された。

今度はflushしないで、もう一度:

SQL> /

10行が選択されました。

経過: 00:00:03.01

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

physical readsなしで、3秒で終わった。

まとめてみると、

  • 実際のHDDにアクセスがあると1分13秒
  • OS file cacheが実際のHDDへのアクセスをエミレートすると12秒
  • 実メモリ上のOracle Buffer_CacheだけのScanでは3秒
  • ということになる。

    Exadata V2はメモリが72GB搭載」の中で、

    対して、こちらはRACではない。メモリが多ければ余りはファイルキャッシュとして働いてもらえる。だからその分を見越してたくさん余らしておく。

    と書いた。Windows7で16GBの実メモリを搭載し、そのメリットを改めて確認した。

    そして「Oracle Closed World 」の中で書いた、

    例えば、夕方に出力する「出荷ステータス・レポート」が毎日1時間かかる。
    このコストを削るのに「廉価なminiスナップショットマシン」を作ればいい。1分で帳票を出してくれるDWHの出来上がりだ。

    という話に、この機能は大きく貢献する。

    流行だからって何でもかんでもLinuxでやる必要はない。。。とWindows嫌いが、心を入れ替えました。
    因みに、LinuxでもUnixでもOS file cacheは有ります。

    最後に、
    今回購入したメモリ(DDR3メモリ)は、現在4GBで1万円以下となった。(3ヶ月弱で30%近く安くなった)
    16GBだと35000円程度で買える。

    The Expendables…

    I watched The Expendables tonight. As a whole package I don’t think it worked very well. The dialog was poor and they didn’t really do enough to make me give a crap about any of the characters, so it just decended into a bunch of fights and explosions with nothing to really string it all together.

    Having said that, there were some elements that I thought had potential. It was quite cool to see fighters from different disciplines working together or fighting each other. I’m not talking about X could beat Y in a fight. I just mean the physical dynamic between the people. Seeing Jason Statham and Jet Li double team Gary Daniels was kinda fun. Seeing Steve Austin fighting Randy Couture was interesting too. The fight scenes weren’t executed as well as they could have been. There was some serious talent available but it wasn’t really made to work.

    In-Memory non Parallel Queryじゃダメなんだよ!

    Heavy SQL Top5の実行時間(秒数)を表にしてみた
    A=non parallel(all physical reads)
    B=in-memory nonParallel
    C=parallel(DOP=6)

    SQL# A B C A/B
    1 7.95 3.63 3.24 1.12
    2 7.67 2.60 2.71 0.96
    3 5.24 1.01 1.51 0.67
    4 8.61 3.95 3.82 1.03
    5 2.81 1.02 2.82 0.36

    Heavy SQLだけで比較するとそれほどIn-memoryが優れているわけではないことに気づく。
    初めから時間のかかる(負荷の高い)SQLでは、むしろParallel Queryの方が速い。
    In-Memory nonParallelはTPC-Hの総合点には大きく貢献したが、実際のデータウェアハウスではParallel Queryの方が優れていることが確認できた。

    そして、Parallel Queryは初めから限界ディスク転送量を出してしまうので「同時実行制御」の工夫が必要だということが、今回のテストを通じて理解できた。

    1 select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
    from customer, orders, lineitem
    where o_orderkey in
    ( select l_orderkey
    from lineitem
    group by l_orderkey
    having sum(l_quantity) > 313)
    and c_custkey = o_custkey
    and o_orderkey = l_orderkey
    group by c_name, c_custkey
    , o_orderkey
    , o_orderdate
    , o_totalprice
    order by o_totalprice desc, o_orderdate;
    2 select nation, o_year, sum(amount) as sum_profit
    from ( select n_name as nation, extract(year from o_orderdate) as o_year
    , l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
    from part, supplier, lineitem, partsupp, orders, nation
    where s_suppkey = l_suppkey
    and ps_suppkey = l_suppkey
    and ps_partkey = l_partkey
    and p_partkey = l_partkey
    and o_orderkey = l_orderkey
    and s_nationkey = n_nationkey
    and p_name like '%navy%') profit
    group by nation, o_year
    order by nation, o_year desc;
    3 select l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count
    , sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 else 0 end) as low_line_count
    from orders, lineitem
    where o_orderkey = l_orderkey
    and l_shipmode in ('SHIP', 'FOB')
    and l_commitdate < l_receiptdate
    and l_shipdate < l_commitdate
    and l_receiptdate >= date '1997-01-01'
    and l_receiptdate < date '1997-01-01' + interval '1' year
    group by l_shipmode
    order by l_shipmode;
    4 select s_name, count(*) as numwait
    from supplier, lineitem l1, orders, nation
    where s_suppkey = l1.l_suppkey
    and o_orderkey = l1.l_orderkey
    and o_orderstatus = 'F'
    and l1.l_receiptdate > l1.l_commitdate
    and exists
    ( select * from lineitem l2
    where l2.l_orderkey = l1.l_orderkey
    and l2.l_suppkey <> l1.l_suppkey)
    and not exists
    ( select * from lineitem l3
    where l3.l_orderkey = l1.l_orderkey
    and l3.l_suppkey <> l1.l_suppkey
    and l3.l_receiptdate > l3.l_commitdate)
    and s_nationkey = n_nationkey
    and n_name = 'CHINA'
    group by s_name
    order by numwait desc, s_name
    5 select supp_nation, cust_nation, l_year, sum(volume) as revenue
    from
    ( select n1.n_name as supp_nation, n2.n_name as cust_nation
    , extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume
    from supplier, lineitem, orders, customer, nation n1, nation n2
    where s_suppkey = l_suppkey
    and o_orderkey = l_orderkey
    and c_custkey = o_custkey
    and s_nationkey = n1.n_nationkey
    and c_nationkey = n2.n_nationkey
    and ( (n1.n_name = 'JAPAN' and n2.n_name = 'ETHIOPIA')
    or (n1.n_name = 'ETHIOPIA' and n2.n_name = 'JAPAN'))
    and l_shipdate between date '1995-01-01'
    and date '1996-12-31') shipping
    group by supp_nation, cust_nation, l_year
    order by supp_nation, cust_nation, l_year;

    だから、In-Memory Parallel Queryが最強なんだね。

    Index Space

    I’ve just been reminded of a thread on OTN which turned into a Q&A about index space usage and various related topics. On re-reading it, I decided it was too good to waste in the backwaters of OTN, so here’s a link to it.

    Index Space Utilization.

    Fedora 13 and Oracle…

    Until a couple of days ago I hadn’t even realized that Fedora 13 was out. I guess that shows how interested I am in Fedora these days. :)

    Anyway, I had a play around with it.

    Cheers

    Tim…

    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 を設定している。

    Parallel Query と In-Memory nonParallelの比較

    TPC-Hベンチマーク続き

    前回のIn-Memory nonParallel Queryでは「同時セッション数」の増加とともに、スケーラブルにqphは上がった。そして、8セッションでCPUの限界となった。

    しかし、Parallel Queryでは、そうは行かない。
    同時1セッションでは:

    同時4セッションでは:

    同時6セッションでは:
    qphはそれほど変わらない。

    結果として、6セッションのときのユーザ・レスポンスは1セッションのときの6倍遅いということになる。

    今回使用したKingstonのお買い得SSD4本で構築したRAID-0のCrystalDiskMarkで計った限界量は

    In Memory QueryでTPC-H 10000qphの大台に!!!

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

    Parallel Queryをやめて、Buffer_Cache=5GBですべてをOn Memoryとした:

    同時ユーザを4セッションに増やしてみた:

    その時点でCPU%は50%。

    倍の8セッションに増やす:
    CPU 100%ととなり、Intel Core i7 860(実売価格25000円)は限界。

    qphは10000を超えた。

    Parallel Queryで「ほぼ安定する」最高qphは: