Who's online

There are currently 0 users and 32 guests online.

Recent comments

November 2011

Digging My iPhone/iPad

As many of you know I’ve had an iPad for a couple of years but just joined the ranks of smart phone users this summer. My wife has a newer iPad 2 with the 3G and I immediately started lusting for more! I’ve added a portable WiFi hotspot to the list or portable toys and now can be connected perhaps a little too often. Because I need to run databases, application servers, and development IDEs I still carry a laptop (often two of them); but my iPad and iPhone get more and more use. I’ve made presentations from my iPad and frequently it’s the only electronics I use while flying. I’ll be adding Objective-C to our course list to show others how to program them sometime early in 2012; I’m really excited.

Some preliminaries

There are a few remarks to be made on DML event triggers. Just to be sure we are all at the same level on the playing field. The first one is that since these triggers fire as part of the execution of a DML statement, the trigger code is not allowed to execute any DDL (which causes an implicit commit) or (explicit) transactional control statements, such as commit, rollback, savepoint or rollback to savepoint. This restriction is true for both statement level as well as row level DML event triggers.

Quiz Night

Inspired by Martin Widlake’s series on IOTs, I thought I’d throw out this little item. In the following, run against, tables t3 and t4 are index organized tables, in the same tablespace, with a primary key of (id1, id2) in that order.

How To Cancel A Query Running In Another Session

This is not really anything new - in fact Tanel Poder has already blogged about it a while ago. Tanel has specifically covered the handling of "urgent" TCP packets and how this could be used to signal a "cancel" to another process, however this only works on Unix environments and not with Windows SQL*Plus clients. In Tanel's article it is also mentioned that there is an officially documented way of doing this via the Resource Manager if you happen to have an Enterprise Edition license.

In my quick tests however the call to DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS using "CANCEL_SQL" as consumer group only errors out with ORA-29366 saying that the specified consumer group is invalid.

EXPLAIN PLAN shared memory

Recently I did an investigation of an ORA-04031 which happens almost regularly on a Oracle database server with 9G of memory allocated to shared pool and disabled Automatic Shared Memory Management (sga_target=0). The problem query is a report that is a very big SQL query – more than 200K of plain text. The exact error message is “ORA-04031: unable to allocate 235704 bytes of shared memory (“shared pool”,”select * from (…”,”Typecheck”,”qry_text : qcpisqt”)”. Here I’ll briefly describe what I did to identify root cause of the error and will talk about one interesting detail of the EXPLAIN PLAN as it was used to reproduce the issue.

So there’re quite a number of good docs out there on the subject of ORA-04031, such as

Index Hints

A new form of index hint appeared in 10g – and it’s becoming more common to see it in production code; instead of naming indexes in index hints, we describe them. Consider the following hint (expressed in two ways, first as it appeared in the outline section of an execution plan, then cosmetically adjusted to look more like the way you would write it in your SQL):

index(@sel$1 prd@sel$1(product_group  id))

Lack of Index and Constraint Comments

Something I’ve just reminded myself of is that under Oracle you cannot add a comment on an index or a constraint. You can only add comments on tables, views, materialized views, columns of those object types and a couple of esoteric things like Operators, Editions and Indextypes.

Here is an example of adding comments to tables and columns:

Tuning Oracle to Make a Query Slower

I had an interesting little project this morning. Of course it takes longer to write it down than to do actually do it, but it was kind of interesting and since I haven’t done a post in quite some time (and it’s the day before Thanksgiving, so it’s pretty quite at the office anyway) I decided to share. One of the Enkitec guys (Tim Fox) was doing a performance comparison between various platforms (Exadata using it’s IB Storage Network, Oracle Database Appliance (ODA) using it’s direct attached storage, and a standard database on a Dell box using EMC fiber channel attached storage). The general test idea was simple – see how the platforms stacked up for a query that required a full scan of a large table. More specifically, what Tim wanted to see was the relative speed at which the various storage platforms could return data.

Why Isn’t My Index Used… When USER2 Executes this Query?

November 23, 2011 I previously wrote a couple of articles that mention reasons why an index might not be use for a particular query, including an article that was formatted as a True or False quiz with several reference articles.  A few days ago I saw an OTN thread that caught my curiosity, where the [...]