Oracle XE 11gR2 beta has just been released, some details here and download here. It’s not a great deal of use for sandboxing DWH-specific stuff, given this list of excluded functionality (and this is by no means everything that’s not included): Bitmapped index, bitmapped join index, and bitmap plan conversions Oracle Partitioning Parallel Data Pump Export/Import Parallel query/DML Parallel Statement Queuing (source) However, it’s always interesting to have to hand for trying out other things.

This post expands on one I made last year here about sampling frequency (of I/O throughput, but it’s a generic concept). The background to this is my analysis of the performance and capacity of our data warehouse on Oracle 11g. Before I get too boring, here’s the fun bit: Pork Pies per Hour (PP/h) Jim wants to enter a championship pork-pie eating competition. He’s timed himself practising and over the course of an hour he eats four pork pies.

I wrote last year about Graphing I/O data using gnuplot and Oracle V$SYSSTAT, using a script from Kevin Closson in his article How To Produce Raw, Spreadsheet-Ready Physical I/O Data With PL/SQL. Good For Exadata, Good For Traditional Storage. Here I’ve got a simple comparison of the data recorded through this script (in essence, Oracle’s V$SYSSTAT), and directly on the OS through HP’s MeasureWare. It’s graphed out with my new favourite tool, rrdtool:

Cary Millsap recently published a paper “Mastering Performance with Extended SQL Trace” describing how to use Oracle trace to assist with troubleshooting the performance of database queries. As with all of Cary Millsap’s papers it is superbly written, presenting very detailed information in a clear and understandable way. (and yes I do have a DBA crush ;-)) It discusses how you can automate the tracing of specific sessions on the database, and requiring the application to be appropriately instrumented.

I’ve been doing some work recently that involved the use of Materialised Views on Oracle 11g (11.1.0.7), particularly around PCT refresh. There are some things that are not clear from the documentation, or are actually bugs so far as I’m concerned, and I’ve detailed these below. In this example I was working on part of a DWH with c.2 millions rows aggregated up daily. One of the things that I spent a long time trying to get to work was Partition Truncation when using PCT refresh.

Poking around on My Oracle Support today, I found a link to a white paper dated November 2010, titled “Best Practices for a Data Warehouse on Oracle Database 11g”. It’s new to me and I’ve not noticed a blog post announcing it, so I thought I’d share it here. It’s by Maria Colgan, who has posted in the past on both the Inside the Oracle Optimizer blog and The Data Warehouse Insider blog.

This is a series of posts where I hope to humbly plug some gaps in the information available (or which has escaped my google-fu) regarding statistics management in Oracle 11g specific to Data Warehousing. Incremental Global Statistics is new functionality in Oracle 11g (and 10.2.0.4?) and is explained in depth in several places including: Oracle® Database Performance Tuning Guide - Statistics on Partitioned Objects Greg Rahn - Oracle 11g: Incremental Global Statistics On Partitioned Tables Inside the Oracle Optimiser - Maintaining statistics on large partitioned tables Amit Poddar - One Pass Distinct Sampling (ppt - slides 52 onwards are most relevant) In essence, Oracle maintains information about each partition when statistics is gathered on the partition, and it uses this to work out the global statistics - without having to scan the whole table.

Continuing in the beard-scratching theme of Unix related posts (previously - awk), here’s a way to graph out the I/O profile of your Oracle database via the Oracle metrics in gv$sysstat, and gnuplot. This is only the system I/O as observed by Oracle, so for belts & braces (or to placate a cynical sysadmin ;-)) you may want to cross-reference it with something like sar. First, a pretty picture of what you can get:

This query, based on AWR snapshots on sys.wrh$_sysstat, includes in its metrics the I/O read throughput for a given snapshot duration. However it’s important to realise the huge limitation to this figure - it’s an average. It completely shoots you in the foot if you’re looking at capacity requirements. Consider this real-life example extracted from the above query: [sourcecode] Timestamp Total Read MBPS =========================================== 14-SEP-10 05.15.12.660 113.748 14-SEP-10 06.00.40.953 202.250 14-SEP-10 06.

I’m a geek. I like understanding things in their absolute entirety. It frustrates me to have to make presumptions or assumptions about something. I like to get down ’n dirty and find out what makes things tick. So that necessitates reading manuals. And unfortunately, Oracle don’t make that easy all the time. Some of the manuals are not very good, and others are difficult to find. Given the complexity of the OBIEE stack and proliferation of terminology and product names, sorting the wheat from the chaff can be a headache.