Oracle Siebel Analytics Data Warehouse: Part II

Following on from this, it occured to me this morning that accurate systems statistics may be an important performance factor for us, with all of these indexing options that the designers so thoughtfully provided. *ahem*

This is because there is much evidence that the SANs we connect to are tuned for OLTP-stlye high i/o rate performance, rather than high bandwidth BI performance. My usual instinct to promote full table scans in preference to index-based access methods when accessing a high proportion of a table have led me astray a couple of times recently and it is specifically the balance between index-based single block reads and the multiblock reads associated with table/partition scans (hypothetically supposing that we have partitions) that I want the optimizer to get right.

This very regular reason for statistics aside, we also have another interesting issue — a Very Important System that has a Very Particular Problem. Although a full-sized performance environment (PRF) is available (and used) before every release, the performance characteristics of the storage are exactly the opposite on the performance system as they are on the production (PRD) system. Specifically, PRF is fast to read and slow to write, and PRD is slow to read and fast to write (or is it the other way round? I forget). The mount points are on different SANs for security reasons (things are generally buttoned-down very tightly here, as you’d probably expect of a company with numerous external partners and a very public website) so that probably accounts for the effect in some way, but the implications are rather interesting — if we want to improve PRD performance, we have to tune the statements in PRF that are already fast.

So creating indexes in PRF is fast, because the read is large and the write is small. In production … well, let us just say it’s a bit of a time consumer. Creating a large summary table with a high volume of data performs about the same in both systems but the critical path for performance is different.

System statistics sadly do not distinguish between read and write performance in 10g (afaik), but wouldn’t it be interesting if they did? It’s a little thought experiment for later … “What database design and optimizer decisions might be affected by different balances between read and write performance?”

Hmmm …

Oh, I upped the optimizer_dynamic_sampling from 2 to 4 … or at least I raised a Remedy ticket to have a DBA do it. Another symptom of a buttoned-down environment :D


5 thoughts on “Oracle Siebel Analytics Data Warehouse: Part II

  1. Hi David,

    Impressive site you have here.

    Sorry for posting this out of context, but since I don’t have your e-mail address…

    I know you mentioned on joelonsoftware that you liked ModelRight…. I’d love to offer you a free license… and work with you in incorporating any feedback you have. We’re working to implement the best database design tool out there and really appreciate good feedback.

    Please contact me via e-mail if it sounds like something you want to do.


  2. quite an insightful article…
    New to siebel but very familiar with datawarehouse…
    trying to download the tool formerly known as siebel analytics on windows xp home…
    how do i go about it…seen quite a few confusing articles…
    did try downloading OBIEE(got the impression I have to download it before I can download siebel) from and I got some error messages…’not supported’

    would appreciate some information…

  3. I don’t really know, Ike. You might hop over to Mark Rittman’s place and see if he has anything on it — I’m less of an installer and more of a “what the heck do we have to do to get this thing to perform”‘er.

  4. Hello David,

    My name is Afzal Khan and I found your blog through Google search. I wanted to ask your permission to add our blog URL to your site.

    I am maintaining BI/DW, Oracle Blog over at I would like to display my Company Hexaware’s Blog on your website. Your blog is very much relevant to articles published at

    Although I was trying to reach but couldn’t able to find your email address, so I thought to comment.

    I request you to spare a minute & please take a look at the Hexaware Blog & see will it be alright to add link at your site. I hope you are aware that Search Engines do prefer link exchange with relevant sites. I am very much interested in adding Hexaware Blogs link on your Blog.
    Please let me know if there is any charge/free for adding link or we can exchange link.

    I am open to any option available.

    Looking forward for a favorable response & let me have your feedback.

  5. “This is because there is much evidence that the SANs we connect to are tuned for OLTP-stlye high i/o rate performance, rather than high bandwidth BI performance.”

    Just trying to understand this – how can you conclude this?

    Also, how can a SAN be tuned for random IO v/s Full Scans? Stripe Size?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s