Search

OakieTags

Who's online

There are currently 0 users and 31 guests online.

Recent comments

Affiliations

July 2010

Cloning Oracle Home from RAC to Stand-Alone

This post originally appeared over at Pythian. There are also some very smart comments over there that you shouldn’t miss, go take a look!

This should have been the easiest task on my todo list: Install Oracle 10.2.0.3 EE standalone on a new Linux RHEL 5 server, later to be used as a standby for a production RAC system. This means 2 lines of “runinstall -silent …”, less than 5 minutes of DBA work and maybe 20 minutes of waiting. I did not expect to spend over 5 hours doing this.

Problems started when I discovered that I don’t have the 10.2.0.3 patchset and another patch that exists on production and should be installed on the standby. I had to wait for my Metalink credentials to be approved for this customer CSI before I could download the patches for them.

“Why don’t you just clone the software from production?” asked a helpful colleague.

Exadata v2 Smart Scan Performance Troubleshooting article

I finally finished my first Exadata performance troubleshooting article.

This explains one bug I did hit when stress testing an Exadata v2 box, which caused smart scan to go very slow – and how I troubleshooted it:

Thanks to my secret startup company I’ve been way too busy to write anything serious lately, but apparently staying up until 6am helped this time! :-) Anyway, maybe next weekend I can repeat this and write Part 2 in the Exadata troubleshooting series ;-)

Enjoy! Comments are welcome to this blog entry as I haven’t figured out a good way to enable comments in the google sites page I’m using…

Share/Bookmark

書き込みキャッシュと読み込みキャッシュ

1.書き込みキャッシュ
Write Cacheがパフォーマンスに及ぼす影響は今回のテストで分かった。
また、揮発性のWrite Cacheだけの書き込みがブロック障害の原因になる可能性もあるということも気になる。
しかし、それを言っているときりがない。HDD自体にもWrite Cacheはあるのだから

今回のディスク WD6000HLHX

32 MB キャッシュ   600 GB,SATA 6 Gb/s,10,000 RPM

同タイプでRAID用「高ストライピング前提」「少Write Cacheタイプ」のモデルもある
WD3000BLFS  16 MB キャッシュ   300 GB,SATA 3 Gb/s,10,000 RPM
→だから容量が少ない。だから安全性優先でWrite Cacheが少ない。

2.読み込みキャッシュ

ハイブリッド型RAIDカードタイプ
Adaotec社HPより

アダプテックMaxIQ SSD キャッシュ - 8倍のIO/秒
アダプテックMaxIQ SSDキャッシュソフトウェアは、最高のリードパフォーマンスを発揮するために、読み出し頻度の高い(「ホット」)データを見分けて、このデータをSSDキャッシュ内にコピーする特許出願中の「ラーンドパス」アルゴリズムを導入しています。

SSD in HDDタイプ  シーゲイト Momentus® XT ドライブ
シーゲートHPより

14 Minute video overview of DB Optimizer

There are a lot of powerful features in DB Optimizer so I put together a quick 14 minute video to go over them:

  1. Profiler - view database load, find

Scalability Conflict

Here’s an example of how you have to think about conflicts of interest when dealing with problems of scalability. It starts with a request (that I won’t give in detail, and was a little different from the shape I describe below)  from a client for advice on how to make a query go faster. Basic [...]

書き込みキャッシュは禁じ手か?

前回のテストで書き込みキャッシュの有効性を確認できた。

しかし、Shared Diskで使う場合はこの設定をしてはいけない

停電対応のためだけにDisableにしなさいというのであればUPS電源を確保した環境であれば補える。
RACクラスター構成も使わないのであれば、メモリ障害などの危険性もないわけではないが、

これほど有効な設定を使わない手はない。

前にやったベンチマーク結果をもう一度おさらいすると:
メモリのWriteアクセススピードは約7.6GB/s

ディスクはTPC-Cの場合はランダムWriteを注目すべきなので約2.6MB/s

おおよそ2900倍の差がある。

最後に、
書き込み負荷でtpmが回らなかったのだから、4本のHDDでストライピングし解決したとすると、
当然tpmは上がり、CPUもある程度Busyになってくる。
そこでRAC構成にしてスケーラビリティを上げようとすると、書き込みキャッシュがいきなり「禁じ手」になる。
結果、2台のRACで1台で出したtpmと「同じ程度」しか出せない。
RACでスケーラビリティを上げようと思ったら4本のストライピングじゃ全然足りない。なぜならばWrite Busyを少しでも発生させると2900倍の差が発生するからだ。

VM環境テストで性能が上がらないケースにも当てはまる。

C based XML tools in your $ORACLE_HOME

Being triggered by Laurent Schneider’s post “extract xml from the command line“; I completely forgot about the C-based XDK tooling you nowadays can find in your $ORACLE_HOME. You, probably just like me, weren’t even aware, there were some (C-based that is). Most of these are executable’s and not “just” Java tools, although xsql is a shell script that still starts Java. More information can be found here in the “Oracle® XML Developer’s Kit Programmer’s Guide 11.2

I mean in principle they are not “new”, they were there since 8.1.x, but now they are compiled executables which you can use on the shell prompt and or in scripting and that is, at least for me, easier than doing the same via their $ORACLE_HOME/xdk Java counterparts.

A shortlist:

Working with long columns

Various techniques for querying and manipulating long columns. July 2010 (updated March 2012)

BAAG, Best Practices and Multiple Choice Exams

(This post originally appeared at the Pythian blog)

I’ve been following the discussion in various MySQL blogs regarding the sort_buffer_size parameters. As an Oracle DBA, I don’t have an opinion on the subject, but the discussion did remind me of many discussions I’ve been involved in. What’s the best size for SDU? What is the right value for OPEN_CURSORS? How big should the shared pool be?

All are good questions. Many DBAs ask them hoping for a clear cut answer – Do this, don’t do that! Some experts recognize the need for a clear cut answer, and if they are responsible experts, they will give the answer that does the least harm.

External Views (XML based)

Something new? Eh? Should you do this? Eh?

In all, probably not, but for me this was a good exercise towards some more updated demo scripting for my “Boost your environment with XMLDB” presentation or hopefully more clearer relabeled Oracle Open World name for the almost same presentation called “Interfacing with Your Database via Oracle XML DB” (S319105). Just up front, there are some issues with the following:

  • Why should you do it at all. You should have a good reason doing so…
  • It can cause a lot of Physical I/O, at least initially when not cached in the SGA
  • Until current versions, AFAIK, it will do a lot of “Pickler Fetching”, serializing in memory, which is very resource intensive (CPU/PGA)
  • …and its probably not supported…?

…but it is good fun for a small exercise based on the following OTN Thread: “Error with basic XMLTable“…

Let me show you what I mean.

Via “bfilename” you are able, since a long time, I guess Oracle 9.2 and onwards, to read a file as a BLOB and because an “XMLTYPE” can swallow almost any datatype, you could do the following…