Here’s a little quiz: If I take the average row length of the rows in a table, multiply by the number of rows, and convert the result to the equivalent number of blocks, how can the total volume of data in the table be greater than the total number of blocks below the table high water mark ? I’ve got three tables in a schema, and they’re all in the same (8KB block, 1M uniform extent, locally managed) tablespace, but here’s a query, with results, showing their space utilisation – notice that I gather schema stats immediately before running my query:

Lately I have been drawn into to a fare number of discussions about IO characteristics while helping customers run benchmarks.  I have been working with a mix of developers, DBAs, sysadmin, and storage admins.  As I have learned, every group has there own perspective – certainly when it comes to IO and performance.

• Most DBA’s want to see data from the DB point of view so AWR’s or EM works just fine.
• Most System Admin’s look at storage from the Filesystem or ASM disk level.
• Storage Admins want to see what is going on within the array.
• Performance geeks like myself, like to see all up and down the stack

As part of pulling back the covers, I came up with a simple little tool for show IOPS at the cell level.

A recent posting on OTN came up with a potentially interesting problem – it started roughly like this:

I have two queries like this:

select * from emp where dept_id=10 and emp_id=15;
select * from emp where dept_id=10 and emp_id=16;

When I run them separately I get the execution plan I want, but when I run a union of the two the plans change.

Following the webinars about 11g stats that I presented on Monday John Goodhue emailed me a few questions that had come through the chat line while I was speaking, but hadn’t appeared on my screen. He’s emailed them to me, so here are the questions and answers.

1. I’d like to know what parameter to use for faster results on dbms_stats.gather_dictionary_stats

When you create a table with a primary key or a unique constraint, Oracle automatically creates a unique index, to ensure that the column does not contain a duplicate value on that column; or so you have been told. It must be true because that is the fundamental tenet of an Oracle database, or for that matter, any database.

Well, the other day I was checking a table. There is a primary key on the column PriKey. Here are the rows:

Select PriKey from TableName;

PriKey
------
1
1

I got two rows with the same value. The table does have a primary key on this column and it is enforced. I can test it by inserting another record with the same value - “1”:

SQL> insert into TableName values (1,…)

I came across a rather annoying little bug yesterday…

One of the guys was trying to compile an Oracle Form on the app server and got this message.

*** ASSERT at file pdw1.c, line 4061
PSDGON missing. Can't get object number
Source Location = XNSPC0P99_2013_06_12_17_44_38__AB[71, 7]

Uncle Google pointed me in the direction of this MOS Note [ID 1276725.1].

It turns out it is a problem with the 11.1.0.7 client, which happens to be the version that ships with Forms and Reports Services. The Forms compilation fails when the form references a database function that uses the Function Result Cache in 11g.

Actually it’s probably not the NOT IN that’s nasty, it’s the thing you get if you don’t use NOT IN that’s more likely to be nasty. Just another odd little quirk of the optimizer, which I’ll demonstrate with a simple example (running under 11.2.0.3 in this case):

Here’s a funny little bug – which probably won’t cause any damage – that may remind you that (most of) the people who work for Oracle are just ordinary people like you and me who make ordinary little mistakes in their programming. It’s a bug I discovered by accident because I just wanted to check something about how a particular undo tablespace had been defined, and I called dbms_metadata instead of querying dba_tablespaces. Here’s the cut-n-paste from an SQL*Plus session on 11.2.0.2: