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. And I like playing with new toys :)

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. So we might say that his Pork Pies per Hour (PP/h) rate is 4.

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. This reminded me of a post that I made almost exactly 12 months ago here, where I explained how to pass through the username of the OBIEE user to the database. Initially I thought it would be useful simply for being able to pin a rogue query to an end-user, but reading Cary’s paper made me realise there is more potential to it.

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. We had tried and discarded “FAST” refresh as being completely non-performant for our volumes.

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. Here’s the link to it:

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. For a more detailed description, see the above links. It’s important to note that this is not the same as aggregated global statistics (which Doug Burns covers in detail here)

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.45.52.750 34.649 14-SEP-10 07.30.03.394 10.953 14-SEP-10 08.15.15.243 57.833 14-SEP-10 09.00.27.180 30.177 [/sourcecode] So, it looks like early in the morning we’re using about 200 MB/s throughput, and by about 9am somewhere around 30-50 MB/s ?

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. Fair enough, for a corporation as big as Oracle with a product portfolio in the [hundreds? thousands?] creating a unified, comprehensive, easy-to-navigate point of reference for documentation must be near on impossible - but that’s their problem to figure out and mine to bitch about until it’s done ;-)

Introduction

We’re in the process of implemention Resource Manager (RM) on our Oracle 11gR1 Data Warehouse. We’ve currently got one DW application live, but have several more imminent. We identified RM as a suitable way of - as the name would suggest - managing the resources on the server.

In the first instance we’re looking at simply protecting CPU for, and from, future applications. At some point it would be interesting to use some of the more granular and precise functions to demote long-running queries, have nighttime/daytime plans, etc. I’d also like to explore the management of IO but for us the pain is in bandwidth that a query consumes, and it looks like RM can only work with total session MB, or IOPS. Reading about Exadata it sounds like the Exadata I/O Resource Management might do this ([…]It allows intra and inter-database I/O bandwidth to be defined and managed[..]). But for that I’ll have to write to Santa and promise to be a good boy this year.

This problem, which in essence is bad behaviour from Informatica bringing down Oracle, is a good illustration of unintended consequences of an apparently innocuous security setting. Per our company’s security standards, database passwords expire every 90 days. When this happens users are prompted to change their password before they can continue logging into Oracle. This applies to application user IDs too. It appears that Informatica 8.6.1 HF6 (part of OBIA 7.9.6.1) doesn’t handle an expired password well, spawning multiple connections to the database, eventually bringing Oracle down through memory SWAP space exhaustion.

What’s going on here? The username/password is definitely valid, proved by the sqlplus connection.

Configuring DAC in OBIA 7.9.5.1:

[sourcecode] What can I do for you?

1 - Enter repository connection information 2 - Test repository connection 3 - Enter email account information 4 - Send test email 5 - Save changes 6 - Exit

Please make your selection: 1

These are your connection type choices:

1 - MSSQL 2 - DB2 3 - Oracle (OCI8) 4 - Oracle (Thin) 5 - Keep current ( Oracle (Thin) )

This is a note-to-self really. When playing around with Oracle and something’s not working - RTFAL: Read The Flippin Alert Log!

After resizing a VM I was getting this problem: [sourcecode] [oracle@RNMVM01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 24 17:44:44 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount ORA-00845: MEMORY_TARGET not supported on this system SQL> [/sourcecode]

I’ve been playing around with SQL Tuning Sets, and was trying to clear up my mess.

To list all the tuning sets: [sourcecode language=“sql”] SET WRAP OFF SET LINE 140 COL NAME FOR A15 COL DESCRIPTION FOR A50 WRAPPED

select name,created,last_modified,statement_count,description from DBA_SQLSET [/sourcecode]

[sourcecode] NAME CREATED LAST_MODI STATEMENT_COUNT DESCRIPTION ————— ——— ——— ————— —————————————————————————————- sts_test_02 09-MAR-10 09-MAR-10 1 Test run 1 sts_test_01 12-FEB-10 12-FEB-10 1 an old STS test test test [/sourcecode]

After it was ripped down last week, Chris Warticki’s blog is back online, albeit with the last posting redirecting visitors to a new location on Oracle Communities.

Maybe I’m getting too old for this s##t, but I’m yet to really get a handle on how Oracle want to interact with real people on the ground. Oracle Communities is a fairly new site that I’ve not explored so much because of no OBIEE area. My Oracle Support has feedback and comment sections, but in general usability stinks. A support blog is torn down and then reappears. Oracle Forums are popular as ever but the software blows and some forums (eg OBIEE) are withering under a very high noise to quality-content ratio. Oh, and Oracle Mix (“Bringing Oracle Customers, Employees, and Developers together”) too, which looked quite neat but I have no idea how that’s supposed to fit it the picture.

You get a call from your friendly DBA. He says the production database is up the spout, and it’s “that bee eye thingumy causing it”. What do you do now? All you’ve got to go on is a program name in the Oracle session tables of “nqsserver@MYSERVER (TNS V1-V3)” and the SQL the DBA sent you that if you’re lucky will look as presentable as this: The username against the SQL is the generic User ID that you had created for connections to the database from OBIEE.

Metalink was retired this weekend and made way for the new My Oracle Support system. It didn’t go as smoothly as it could have done.

This post is going to be a bit of a rambling rant.

Ultimately people, including me, don’t like their cheese being moved (not unless there’s a really runny piece of Camembert at the end of it). That makes it a bit more difficult to discuss because some of people’s complaints will just be geeks being stubborn (and boy, can geeks be stubborn). Arguments descend into minutiae of detail and flash vs DHTML - whilst the bigger picture gets lost.

Want to find all PDFs from Oracle about OBIEE?

There’s some interesting ones that this turned up on Oracle’s public FTP. In particular:

• BI EE Environmental and Multi-User Development Migration Processes
• Securing Oracle BI Enterprise Edition

(There’s also PowerPoint files on there including this one but Google doesn’t seem to index them)

You can use Google’s Advanced Search page to build similar queries:

A frequent question on the OTN OBIEE forum is how to fix this error:

[nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database.

The error is simply OBIEE reporting that it tried to connect from the BI Server to an Oracle database and the Oracle client returned an error. Distilling it down gives us this error: