I noticed on Nico’s wiki (which is amazing by the way, it has so much information in it) a bunch of additional parameters for nqcmd other than those which are displayed in the default helptext (nqcmd -h).
These are the additional ones: [sourcecode] -b
Here’s a scenario that’ll be depressingly familiar to most reading this: after ages of running fine, and no changes to the code, a query suddenly starts running for magnitudes longer than it used to.
In this instance it was an ETL step which used to take c.1 hour, and was now at 5 hours and counting. Since it still hadn’t finished, and the gods had conspired to bring down Grid too (unrelated), I generated a SQL Monitor report to see what was happening: [sourcecode language=“sql”] select DBMS_SQLTUNE.REPORT_SQL_MONITOR( type=>‘HTML’, report_level=>‘ALL’,sql_id=>‘939abmqmvcc4d’) as report FROM dual; [/sourcecode] (h/t to Martin Berger for this)
There is a well-documented problem relating to DBMS_STATS.COPY_TABLE_STATS between partitions where high/low values of the partitioning key column were just copied verbatim from the source partition. This particular problem has now been patched (see 8318020.8). For background, see Doug Burns’ blog and his excellent paper which covers the whole topic of statistics on partitioned tables.
This post Maintaining statistics on large partitioned tables on the Oracle Optimizer blog details what the dbms_stats.copy_table_stats does with regards to the high/low values:
My opinion is that automated stats gathering for non-system objects should be disabled on Oracle Data Warehouses across all environments.
All it does it cover up poor design or implementation which has omitted to consider statistics management. Once you get into the realms of millions or billions of rows of data, the automated housekeeping may well not have time to stat all of your tables on each run. And then it becomes a quasi-lottery when your tables will get processed. Or what if you’re working with intra-day loads (eg. near real-time) - the housekeeping job only runs once a day by default.
Just because something produces the correct numbers on the report, it doesn’t mean you can stop there.
How you are producing those numbers matters, and matters a lot if you have an interest in the long-term health of your system and its ability to scale.
OBIEE is the case in point here, but the principle applies to any architecture with >1 tiers or components.
Let me start with a rhetorical question. The user has got a report which has ten rows of data. Which of the following methods is going to be a more efficient way to generate the report?
Chris Mellor at The Register posted an interesting article a couple of days ago, entitled HP and Violin build Oracle Exadata killer. The slidedeck has been removed from HP’s FTP site, but a bit of Google magic throws up a couple of mirror copies.
It’s an entertaining read (“Do a Proof of Concept! 94% win rate!! We can and do win against Exadata!!”), and a nice illustration of the FUD techniques that companies use in marketing their products against others. Greg Rahn has taken Netezza to task in the past for this, and to be fair at least Netezza had a serious white paper to back up their arguments. HP’s deck (including choice sections such as “How to sell against Exadata”) is IMHO nothing more than a biased set of arguments for salespeople to use to attempt to bullshit customers.
Whilst perusing the Oracle database documentation, I noticed something which caught my eye:
As well as reading the documentation online as HTML or downloading as PDF for viewing on your computer etc, you can also download it in formats (Mobi and ePub) designed for eReaders such as the Kindle and iPad (the latter obviously isn’t “just” an eReader). For information on format support, there’s a handy table on Wikipedia.
It looks like the availability of mobi/epub files isn’t universal. For example, the OBI 11g documentation appears still to be HTML & ZIP only. And whilst the Database documentation doesn’t link to the mobi files on the contents page, only each document’s TOC, the Essbase documentation does:
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:
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:
Over the last few months I’ve been doing a lot of exploring of OBIEE Systems Management data, covered in a mini-series of blog posts, Collecting OBIEE systems management data.
There are a vast number of counters exposed, ranging from the very interesting (Active Sessions, Cache Hits, etc) to the less so (Total Query Piggybacks, although for some seriously hardcore performance tuning even this may be of interest).
This short blog post is about a couple of counters which I’ve been monitoring but which looks to not be entirely reliable. Both are in the Oracle BI DB Connection Pool, and are:
I’ve added two new toys to my geek arsenal today. First is one with which I’ve dabbled before, but struggled to master. The second is a revelation to me and which I discovered courtesy of twitter.
rrdtool is a data collection and graphing tool which I’ve been aware of for a while. I wanted to use it when I wrote about Collecting OBIEE systems management data with JMX, but couldn’t get it to work. I’ll not lie to you - it is a bitch to work with at first. Or put a more polite way, it has a steep learning curve. But when you reach the top of the curve and realise its potential…wow. You’ll soon understand why it is so widely used. I plan to write this up soon, but it let me draw nice graphs like this: 
A chap called Kevin posted a comment on a previous posting of mine asking
did you ever come across anything that could be used to change the LDAP server settings from a command line (admintool.exe, UDML, or otherwise)?
I did a quick play around with some UDML and it appears that you can.
First I added a dummy LDAP server to samplesales.rpd: 
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:
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)
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.
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:
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]
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. This is similar to jmxterm and both provide command-line access to jmx. Once it’s commandline, it’s scriptable :)