Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

OakTable member!

Just like Tim Hall I was nominated and approved as a member of the OakTable network. I am very happy to have been accepted to a group of such gifted individuals!

Tagged: oaktable oracle oaktablenetwork

OakTable…

I was recently nominated and approved as a member of the OakTable Network .

Do you ever get that feeling that one day people are going to realize you don’t have a clue what you are talking about? I think that day just got a little closer. :)

Cheers

Tim…

Cardinalilty One

I think anyone who has read Wolfgang Breitling’s material about the optimizer will be familiar with the concept of Cardinality Feedback and one particular detail that when Oracle gets a cardinality estimate of one for a “driving” table then there’s a good chance that the execution plan will go wrong. (That’s not rule, by the way, just a fairly common observation after things have gone wrong.)

A recent note on OTN reminded me of a particular scenario where this specific problem can occur. It’s not particularly common, but it may hit people who are building data warehouses from multiple different sources. We start with an unlikely looking data set and very simple query:

drop table t1;

create table t1 as
select
	rownum id1,
	rownum id2
from
	all_objects
where
	rownum <= 10000
;

execute dbms_stats.gather_table_stats(user,'t1');

set autotrace traceonly

select
	count(*)
from
	t1
where
	id1 = id2
;

What do you think Oracle estimated cardinality will be for this predciate ? We know, because we saw the data being built, that we’re going to identify 10,000 rows. But the optimizer doesn’t see it that way – check line 2 of the execution plan. The optimizer thinks it will find just one row:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |     7 |     8   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID1"="ID2")

Roughly speaking the problem is that Oracle doesn’t know how to compare the two columns, so it considers two options: “id1 = {unknown constant}” and “id2 = {unknown constant}”, and uses the arithmetic that gives the lower cardinality. But the selectivity of “column = {constant}”, in the absence of histograms, is 1/(number of distinct values). Since our columns are unique, the resulting cardinality is one (and in any case where one of the columns was “close to unique” the cardinality would be very small.

If you get caught in this trap here are a couple of straightforward solutions to consider.


select /*+ dynamic_sampling(t1 2) */
	count(*)
from
	t1
where
	id1 = id2
;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 10000 | 70000 |     8   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("ID1"="ID2")

Note
-----
   - dynamic sampling used for this statement

The first workaround is to add a hint to force Oracle to take a dynamic sample of the critical table. In this case I’ve instructed Oracle to use a 64 block sample. For small tables (and big queries) this is probably sufficient, and cheap enough, to be a good option. Note how Oracle has got the correct cardinality in line 2.

alter table t1 add n1 generated always as (id1 - id2) virtual;
execute dbms_stats.gather_table_stats(user,'t1');

select
	count(*)
from
	t1
where
	id1 - id2 = 0
;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 10000 | 90000 |     8   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("T1"."N1"=0)

For users of 11g, this is a wonderful example of how virtual columns (or, in other cases, extended statistics) can help. Assuming id1 and id2 are numeric we know that id1 – id2 is zero if and only if id1 = id2 (we don’t have to worry about the effects of nulls in this case). So create a virtual column on id1 – id2 and query for the rows where the virtual column is zero.

Note, again, that the cardinality is correct in line 2. Note also that the predicate from my query “id1 – id2 = 0″ has been rewritten to substitute the virtual column “n1 = 0″.

The stats collection was necessary after creating the virtual column – but I was a little lazy in typing, I could have restricted it to method_opt => ‘for columns n1 size 1′.

I have to say that I would probably use this specific example only where the two columns were declared to be integers – there’s always a slight possibility that some odd rounding error in real number arithmetic could result in a zero when the two inputs weren’t identical.

This example is just one demonstration of why virtual columns (and extended statistics) are such a fantastic feature to have in the database.

More PC support…

One of my Yoga buddies was given a laptop by is dad and wanted to get it connected over wireless. His dad also gave him a wireless ADSL router, but couldn’t get it set up.  This sounds like a job for Captain Support…

The router wasn’t able to connect to the internet. It turned out that the router was not working properly and needed a firmware update. Next issue was the wireless connection between the router and the laptop was kinda funky. The connection would never work when any form of encryption was turned on. In the end I had to turn off encryption and stopped the router from broadcasting in an attempt to reduce the chances of people piggy-backing on it.

How are normal folk meant to cope with this? The answer is they don’t and they need Captain Support… :)

Cheers

Tim…

In-Memory nonParallel + nocompressで16000qph達成

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

今までの結論は、

In-Memory nonParallelはTPC-Hの総合点には大きく貢献したが、実際のデータウェアハウスではParallel Queryの方が優れていることが確認できた。

もう少し、総合点を上げるためにIn-Memory nonParallelのテストを追加した。

Parallel Queryを速くするCompressオプションを外してみる。

1セッション COMPRESS:

NOCOMPRESSにすると

同時4セッションCOMPRESS:

NOCOMPRESSにすると

同時8セッションCOMPRESS:

NOCOMPRESSにすると

そして、Resut_Cacheを使った、今までの最高点:

NOCOMPRESSにすると

COMPRESSされたOracle BlockをUNCOMPRESSするCPU負荷がなくなったことが高結果の要因だと思う。
COMPRESSでディスクI/O回数を減らすコストの方がUNCOMPRESSのコストより数倍大きい。でも今回はディスクI/OゼロだからUNCOMPRESSの負荷が目立ったんだな。。。

今回の結果はあくまで参考値。
大規模データウェアハウス環境ではParallel Query効率化のためのCOMPRESSは非常に有効。
In-Memory nonParallelは、TPC-Hベンチマークの点数を上げるためには都合の良いテクニックだったけど、せっかくのSSD RAID0の意味がなくなっている。

因みに、Exadata1のTPC-Hベンチマークでは、sub partition(composit key)を使っているのでCOMPRESSは使えない筈。そしてV1なので、カラムCOMPRESSも使えない。

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.

So Wirth’s Law, “Software is getting slower more rapidly than hardware becomes faster”, is true because people’s expectation of what software can do for them is constantly expanding, without realizing the impact those expanding expectations have on the programming and hardware requirements. Added to that we have a generation of cut & paste developers and DBAs who also don’t understand the impact their lack of understanding has on the software they develop (see Gate’s Law).

I look forward to writing a post in 10 years where I can moan about Exadata V12 boxes struggling to complete my weekly loads before the end of the weekend. Of course I will forget to qualify that I’m loading Yottabytes of data in that time… :)

Cheers

Tim…

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.

    Statham is really fluid and always works nice angles. Jet Li is one of my favorite martial artists, but rarely gets to show what he’s capable of. Gary Daniels is very flexible and can do some really great techniques, but here he was involved in close-up stuff which doesn’t show him at his best. And of course, you know if Randy Couture, Dolph Lundgren, or Steve Austin hit you it’s going to hurt. :)

    So all in all it wasn’t a great showing, but I hope they do another with better dialog and get someone in to sort out the fight scenes. It could very easily be awesome…

    Cheers

    Tim…

    PS. Forgot to mention I saw Karate Kid recently. It was quite good fun from a nostalgia perspective.

    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が最強なんだね。