Search

OakieTags

Who's online

There are currently 0 users and 28 guests online.

Recent comments

Affiliations

August 2010

Exadata has arrived, part 2

The second installation step of the database machine aka Exadata by Oracle ACS (Advanced Customer Support) is configuring the database and storage (‘cell’) nodes/servers. The blades are delivered with default IP addresses, during this step they are configured to the IP addresses which fit in our environment. Also the cellservers are configured (‘LUN’s are carved’) to have storage for ASM.

The cellservers are configured with three diskgroups during a normal installation: DATA for data, RECO for the flash recovery area and a diskgroup for the clusterware (voting disks, cluster registry) called SYSTEMDG.

A RAC database is configured too. We have a half rack, which means 4 database nodes, so a 4 node RAC database is configured, called ‘dbm’. The database has no data in it, besides the data dictionary (obviously), and is using a ‘humble’ amount of memory (8GB on a 64GB machine).

Now it’s up to me to automate the creation (and deletion) RAC databases, adding and deleting instances of the RAC database, modifying the storage (to be able to test both half rack and quarter rack configurations) and also some optimising/configuration, like enabling hugepages, add rlwrap etc.

Busy, busy, busy :)

Tagged: oracle database machine exadata

一番遅いSQLは?


クリックで表示されたSQL文の全容は:

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;


Embarcadero® DB Optimizer™ XEではHint句をシュミレートしてくれる。
赤い棒グラフのHintは「使うとえらい目に合う」ことを示している。これでHintのテストがとても楽になる。

partitioning + compress で200%アップ

TPC-Hベンチマーク続き
前回のCompressを行う前は

次にLINEITEMをl_shipdateでPartitioningしたら

そして、各パーティションをCompressしたら、2倍以上のqphをたたき出した

Partitioning前はLINITEMが全体の70%の負荷オブジェクトだったが、今では負荷はある程度分散された。

Compressで40%アップ

TPC-Hの続き、

前回の分析でLINEITEMのdirect path readアクティビティが高いのが分かった。
そこで、表圧縮をしてみる:

SQL> ALTER TABLE LINEITEM MOVE COMPRESS;


前回の結果

TPC-Hベンチマーク指標のqphが40%以上改善された。

そして、DB Optimizerで比較してみると:
今回

Would You Like A Job in Database Security?

Lindsay from Cervello Consultants has asked me to promote a job he is recruiting for. Lindsays company specialises in data security assurance and particularly in designing, deploying, integrating and testing database security vulnerability scanning, enterprise scanning solutions, database activity monitoring....[Read More]

Posted by Pete On 13/08/10 At 09:29 AM

TPC-Hをチューニングする

TPC-Hベンチマークの続き
現在のディスク転送量は420MB/s強。

1セッションでこれ以上望めないのであれば、効率的な読み込み量を追求する。

オブジェクトを特定する

上の時系列スパイクチャートから、レスポンス要因はUser I/O

下のオブジェクトごとのDBアクティビティから、
LINEITEMのdirect path readが全体の78%の負荷が分かる。

だから、LINEITEMのREAD量を減らす、パーティショニング、コンプレス機能が効果的なことが分かる。

念のためにスパイクが飛び出た時のNo1 SQLのPlanを見てみると:
なるほど、LINEITEMへのアクセス負荷がポイントだな。。。

最後に、
Embarcadero® DB Optimizer™ XE を初めて使ってみたのだけど、
ものすごく簡単でした。

Oracle 11g R1/R2 Real Application Clusters Handbook… Review in process

A few years back I had the pleasure of meeting Ben Prusinski at Oracle Open world.  Ben has published several books on Oracle Internals and Debugging.  In his most recent project he teams with Guenadi Jilevski and Syed Jaffer Hussain to write
“Oracle 11g R1/R2 Real Application Clusters Handbook”.  I have only just cracked the cover but already know this will be a good resource for the beginner and seasoned professional.  If you want to look for yourself, you can preview or order the book through PACKT publishing.

Metric Collection Error

I’m in the later stages of implementing Oracle Enterprise Manager 11g for a customer. Right now there are rather too many metric collection errors for either myself or the customer to be truly happy with. There is remarkably little other than this post by Oracle’s Werner.de.Guyter on how to deal with these. Unfortunately whilst Werner’s [...]

APEX 4.0 Websheets are Cool!

Wow! I just finished presenting a webinar on APEX 4.0 New Features and Websheets. Websheets allow non-technical people to copy-and-paste spreadsheets into APEX and then manipulate them using sophisticated database features; so easy… You can see the webinar slides at http://www.parklane.com.au or http://www.kingtraining.com

Oracle RAC on VirtualBox…

With the recent news that the latest version of VirtualBox now supports shared disks, I thought I better give it a go and see if I could do a RAC installation on it. The good news is it worked as expected. You can see a quick run through here:

This is pretty good news as that was the last feature that tied me to VMware Server. I’ve now moved pretty much everything I do at home on to VirtualBox and it’s working fine.

It’s worth taking a little time looking at the VBoxManage command line. Some of the operations, like creating the shared disks, have to be done from the command line at the moment. It’s also handy for running VMs in headless mode if you don’t want the GUI screen visible all the time.

Cheers

Tim…