Have you defined CLIENT_ID in your OBIEE RPD yet? You really ought to. As well as helping track down users of troublesome queries, it also tags dump files with the OBIEE user of an offending query should the worst occur: For details, see: Identify your OBIEE users by setting Client ID in Oracle connection Instrumenting OBIEE for tracing Oracle DB calls

A new version of OBI 10g (remember that?) has just been released, the Oracle twitter machine announced: Along with presumably a bunch of bugfixes, the release notes list new functionality in catalog manager: Download 10.1.3.4.2 from here

Trying to connect to a repository in ODI using OCI. Target database is Oracle 11.1.0.7. Throws this error: [sourcecode] com.sunopsis.sql.l: Oracle Data Integrator Timeout: connection with URL jdbc:oracle:oci8:@ODIPRD and user ODI_USER. at com.sunopsis.sql.SnpsConnection.a(SnpsConnection.java) at com.sunopsis.sql.SnpsConnection.t(SnpsConnection.java) at com.sunopsis.sql.SnpsConnection.connect(SnpsConnection.java) at com.sunopsis.tools.connection.DwgRepositoryConnectionsCreator.a(DwgRepositoryConnectionsCreator.java) at com.sunopsis.tools.connection.DwgRepositoryConnectionsCreator.a(DwgRepositoryConnectionsCreator.java) at com.sunopsis.graphical.l.oi.a(oi.java) […] [/sourcecode] Normally this error would be caused by a misconfigured Oracle client. For example, a missing or incorrect tnsnames.ora entry. I validated these and got a successful response using tnsping.

July’s Critical Patch Update from Oracle includes CVE-2011-2241, which affects OBIEE versions 10.1.3.4.1 and 11.1.1.3. No details of the exploit other than it “allows remote attackers to affect availability via unknown vectors related to Analytics Server.” It is categorised with a CVSS score of 5 (on a scale of 10), with no impact on Authentication, Confidentiality, or Integrity, and “Partial+” impact on Availability. So to a security-unqualified layman (me), it sounds like someone could remotely crash your NQSServer process, but not do any more damage than that.

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 -w<# wait seconds> -c<# cancel interval seconds> -n<# number of loops> -r<# number of requests per shared session> -t<# number of threads> -T (a flag to turn on time statistics) -SmartDiff (a flag to enable SmartDiff tags in output) -P -impersonate -runas [/sourcecode]

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.

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.

Chucking a stick in the spokes of your carefully-tested ETL/BI … 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.

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.

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.