Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

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で計った限界量は
シーケンシャルReadで最高522MB/s出る。

そして、1,2,4,6同時セッション(上から)のディスク転送量は:
上に見える青線はDisk Busy%。 緑のエリアチャートは転送量(1メモリ100MB/s)。

瞬間的ではあるがCrystalDiskMarkで出した限界性能近くが、出ていることが分かる。

Parallel Queryでスケーラビリティを上げるために、マルチノード(RAC)化をする意味を改めて認識した。
ストライピング数を上げて転送量を確保することは、言わずもがな。
SSD8本にすれば、qphは倍近くにはなるけど、スケーラブルには上がらない。
その時でも、6セッションのときのユーザ・レスポンスは1セッションのときの6倍遅いということになる。

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は:

In-Memory-nonParallel QueryParallel Query2.5倍の性能を出した。

最後に、
このテストをExadataと同じXeon Dulal CPUでやれば、
25000円が150000円になるけど、
20000qphを超えると思う。

Do-It-Yourself Exadata-Level Performance! Really? Part IV.

In my post entitled Do-It-Yourself Exadata-Level Performance? Really? I invited readers to visit the Oracle Mix page and vote for my suggest-a-session where I aimed to present on DIY Exadata-level performance. As the following screenshot shows I got a lot of good folks to vote on that. It must have been an interesting sounding topic! Yes, [...]

More Copyright Theft (Update)…

Regarding my previous post (here), it seems Guenadi N Jilevski has now removed the articles that are direct copies of mine. Thankyou for the quick action.

There is still at least one article remaining that contains large chunks of text scraped from my site. I guess the fact he has included his own screen grabs and some minor alterations to the text lead him to believe it is original content. Sigh.

I’m also glad to see he has removed the blog post where he attempts to defend his stance. I’ve taken copies of all the important posts for my records, but I’m hoping this marks an end to this little affair.

Cheers

Tim…

Update: I found a new batch of stolen stuff I’m attempting to get removed. The list from the previous post has been extended accordingly.

More Copyright Theft…

Thanks to Don Burleson for pointing me at this article by Timur Akhmadeev that lists a whole bunch of articles that have been stolen by Guenadi N Jilevski.

Unfortunately, this is only the tip of the iceberg. Just looking at the stuff he’s stolen from me, there are three types ripoff:

1) Complete Copies.

http://gjilevski.wordpress.com/2009/09/29/edition-based-redefinition-in-...

http://www.oracle-base.com/articles/11g/EditionBasedRedefinition_11gR2.php

http://gjilevski.wordpress.com/2009/10/22/dbms_parallel_execute/

http://www.oracle-base.com/articles/11g/dbms_parallel_execute_11gR2.php

http://gjilevski.wordpress.com/2010/01/30/acfs-in-oracle-database-11g-re...

http://www.oracle-base.com/articles/11g/ACFS_11gR2.php

2) Articles where the bulk of the copy is stolen word for word, with a couple of sections added and different screen grabs, which at first glance would make it seem original. Make no mistake, this is still stolen.

http://gjilevski.wordpress.com/2010/03/15/oracle-database-11g-release-2-...

http://www.oracle-base.com/articles/11g/DataGuardSetupUsingGridControl_11gR2.php

3) Articles that, although they are not direct copies, have very similar names, order of content and are published very soon after my articles were released. A quick scan through makes it pretty obvious that they are copies that are trying hard not be be copies. The sort of schoolboy tricks teachers spot a mile off. Since these are pale imitations of the originals it’s hardly worth bothering about.

Fortunately, WordPress.com (and most other services/ISPs) have very clear guidelines on this matter, see “http://automattic.com/dmca/“, so I’m expecting a speedy resolution. I’ve posted comments on the offending posts asking for them to be removed. If they are not I will contact Automattic directly to get them removed. I’m guessing if everyone concerned does the same the blog will be pretty empty very soon.

I think it’s strange that in an industry that relies so heavily on trust and intellectual property a person would think nothing of stealing someone elses work. You might as well put a tag line on your blog reading, “Don’t hire me. I’m gonna steal everything I can from you!”

Cheers

Tim…

Update:

  1. I was advised to break the links to his content to avoid giving him extra publicity. :)
  2. It seems trust is not a big deal these days: http://www.theregister.co.uk/2010/08/19/rogue_workers_survey/
  3. Looks like this issue is mostly resolved from my perspective. See here.
  4. I found another bunch of stolen stuff:
http://gjilevski.wordpress.com/2007/02/20/server-configuration-in-oracle-database-10g/
http://gjilevski.wordpress.com/2007/10/24/improved-vldb-support-in-oracle-database-10g/
http://gjilevski.wordpress.com/2007/11/20/performance-tuning-enhancements-in-oracle-database-10g/
http://gjilevski.wordpress.com/2009/01/12/trigger-enhancements-in-oracle-database-11g-release-1/
http://gjilevski.wordpress.com/2010/02/06/scheduler-enhancements-in-oracle-database-11g-release-2/
http://gjilevski.wordpress.com/2010/02/01/automatic-memory-management-amm-in-oracle-database-11g-release-1/
http://gjilevski.wordpress.com/2009/02/12/data-pump-enhancements-in-oracle-database-11g-release-1/
http://gjilevski.wordpress.com/2009/01/12/virtual-columns-in-oracle-database-11g-release-1/
http://gjilevski.wordpress.com/2008/12/13/sql-trace-10046-trcsess-and-tkprof-in-oracle-10g/
http://gjilevski.wordpress.com/2007/11/06/automatic-database-diagnostic-monitor-addm-in-oracle-database-10g-2/
http://gjilevski.wordpress.com/2007/10/22/services-in-oracle-database-10g/

Finding thread IDs and names of SQL Server background threads

In Microsoft SQL Server, session IDs of 50 or less are dedicated background threads. These are analogous to Oracle’s background processes.  Suppose you needed to obtain the Windows thread ID of a SQL server background thread.  Just try finding out how to do that in the Microsoft documentation or by Googling.  I tried, and came up empty-handed.

Why might you need to know this kind of thing?  Well suppose one of those threads was misbehaving or hanging. You would need to debug it and obtain a stack trace.  For that you would need the OS thread ID.

A little sleuthing shows that you need to query three dynamic system views: sys.dm_os_threads, sys.dm_os_workers and sys.dm_exec_requests.  The following SQL should do the trick in MSSS 2005 and 2008:

1> select session_id, command, os_thread_id
2> from sys.dm_exec_requests as r
3> join sys.dm_os_workers as w on r.task_address = w.task_address
4> join sys.dm_os_threads as t on t.thread_address = w.thread_address
5> where session_id <= 50
6> order by session_id
7> go
session_id command          os_thread_id
---------- ---------------- ------------
1          RESOURCE MONITOR         2776
2          XE TIMER                 2908
3          XE DISPATCHER            2416
4          LAZY WRITER              1316
5          LOG WRITER               2404
6          LOCK MONITOR             1592
7          SIGNAL HANDLER           1516
9          TRACE QUEUE TASK         1544
10         BRKR TASK                1540
11         TASK MANAGER             1524
13         CHECKPOINT               1520
14         BRKR EVENT HNDLR         1556
15         BRKR TASK                1552
16         BRKR TASK                1536

With these IDs in hand, you can use a thread debugging tool like Sysinternals Process Explorer to kill, debug or otherwise troubleshoot the problem.

Related posts:

  1. Finding non-default configuration settings in SQL Server
  2. SQL Server

Introduction to the Oracle database machine

For those of you who haven’t followed all the Oracle Exadata and database machine information, and want a brief introduction to the database machine: here it is!

The confusion

There is some confusion about ‘exadata’ and ‘the database machine’. If we look at the official product names, ‘Exadata’ is the storage server, and the ‘Database machine’ is the complete boxed machine, including exadata for storage. But…in the real world, in different kinds of papers (technical, advertising) exadata sometimes is used as an alias for the database machine.

What is the database machine?

Essentially, the database machine is a version of the Oracle database on balanced, optimised hardware from Oracle. But that is a very short description, and does harm to the unique feature of the database machine: the Oracle Exadata storage server. Exadata offers database offloading by execution some of the database functions on the storage layer. This is a schematic drawing of the architecture of the database machine (cell server is the internal name of the exadata software):

The database machine, a little more verbose

One of the basic design principles of the database machine is fault tolerancy. This means any component may fail, whilst the database still is able to function. So if you look at the schematic drawing above, it means there are multiple instances of the database (=Oracle RAC), there are TWO infiniband switches (in a quarter and half rack, there are three infiniband switches in a full rack), and there are multiple storage servers (3/quarter-, 7/half-, 14/full-rack). The redundancy of the database is NOT done by the storage servers, but is done using Oracle’s ASM (Automatic Storage Management) ‘normal redundancy’, which essentially means data is stored double.

It is possible to use a single server (non RAC) version of the database on the database machine, but that does harm to the architecture of the database machine (in my opinion).

The storage servers are fitted with 384 GB flash memory attached via PCI-X. This flash memory can be used as flash disks, but is configured as flash memory for caching of the storage server by default.

Last but not least the database is able to offload some database actions to the storage:
- Smart scans: row filtering, column filtering, join filtering, incremental backup filtering, data mining model scoring. Transparently.
- Storage indexes: stores min and max values of columns of database blocks every 1MB. Transparently.
- Hybrid Columnar Compression: database compression mechanism where data is stored by column, then compressed. (HCC is using several database blocks). Needs to be specified explicitly as a compression option during creation of modification.

Conclusion

I hope this article was able to get you a basic understanding of the architecture of the database machine. Perhaps it might even make you enthusiastic about it, because it offers several unique features:
- A hardware infrastructure purely built for database performance. Something encountered ‘in the wild’ very rarely.
- State of the art hardware.
- Common Oracle database, no need to alter anything when moving to the database machine.

Tagged: oracle database machine exadata introduction