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.

Introduction This is the third part of three detailed articles making up a mini-series about OBIEE monitoring. It demonstrates how to capture OBIEE performance information, and optionally graph it out and serve it through an auto-updating webpage. This final article describes how to bolt on to OAS a simple web page hosting the graphs that you created in part 2, plotting data from OBIEE collected in part 1. The webpage This is just an old-school basic HTML page, with a meta-refresh tag (which note that Chrome doesn’t work with) and img tags:

Introduction This is the second part of three detailed articles making up a mini-series about OBIEE monitoring. It demonstrates how to capture OBIEE performance information, and optionally graph it out and serve it through an auto-updating webpage. This article takes data that part one showed you how to collect into a tab-separated file that looks something like this: [sourcecode] 2010-11-29-14:48:18 1 0 11 0 3 2 1 676 340 0 53 1 0 41 0 3 0 2010-11-29-14:49:18 1 0 11 0 3 2 1 676 0 0 0 1 0 0 0 3 0 2010-11-29-14:50:18 2 0 16 1 4 3 1 679 0 0 0 1 0 0 0 4 0 2010-11-29-14:51:18 2 2 19 1 4 3 1 679 32 0 53 1 0 58 0 4 0 2010-11-29-14:52:18 2 1 19 1 4 3 4 682 0 0 0 1 0 0 0 4 0 2010-11-29-14:53:18 2 1 19 1 4 3 4 682 0 0 0 1 0 0 0 4 0 2010-11-29-14:54:18 2 0 19 1 4 3 1 682 0 0 0 1 0 0 0 4 0 [/sourcecode]

Introduction This is the first part of three detailed articles making up a mini-series about OBIEE monitoring. It demonstrates how to capture OBIEE performance information, and optionally graph it out and serve it through an auto-updating webpage. For some background on OBIEE’s Systems Management component, along with JMX and MBeans, see here and here. The following assumes you know your mbeans from coffee beans and jmx from a bmx. The metric collection is built around the jmxsh tool.

Those of you who read my blog regularly may have noticed I have a slight obsession with the OBIEE systems management capability which is exposed through JMX. Venkat has blogged this week about JMX in OBI11g, and it’s clearly a technology worth understanding properly. I’ve recently been tinkering with how to make use of it for monitoring purposes, most recently using JConsole and discussed here. What follows is an extension of this idea, cobbled together with a bit of shell scripting, awk, gnuplot, and sticky backed plastic.

Hot on the heels of one problem, another has just reared its head. Users started reporting an error with reports that included charts: [sourcecode] Chart server does not appear to be responding in a timely fashion. It may be under heavy load or unavailable. [/sourcecode] Set up is a OBIEE 10.1.3.4.1 two-server deployment with BI/PS/Javahost clustered and loadbalanced throughout. Diagnostics Javahost was running, and listening, on both servers: [sourcecode] $ps -ef|grep javahost obieeadm 14076 1 0 Nov 25 ?

They say about travelling that it’s the journey and not the destination, and the same is true with this problem we hit during a deployment to Production. We were deploying a new OBIEE 10g implementation, with authentication provided by Microsoft Active Directory (AD) through the LDAP functionality in OBIEE. As a side note, it’s a rather nice way to do authentication, although maybe I’m biased coming from our previous implementation which used EBS integrated authentication and was a bugger to set up and work with.

Folk from Yorkshire are tight, so the stereotype goes. So here’s a cheap-ass way to monitor OBIEE 10g using nothing but the OBIEE built-in systemsmanagement component, the jmx agent, and jconsole (which is part of the standard Java distribution): From here you can also export to CSV the various counters, and then store history, plot it out with gnuplot or Excel, etc. If anyone’s interested let me know and I’ll document a bit more about how I did this, but it’s basically building on previous work I’ve documented around jmx and OBIEE.