dbms_metadata

The dbms_metadata package has a few convenient functions and procedures that allow you to generate the SQL to recreate parts of your database. The best-known function is probably dbms_metadata.get_ddl(), but every example of its use that I’ve seen on the Internet seems to use a “select from dual”, e.g.

select dbms_metadata.get_ddl('TABLE','T1','TEST_USER') from dual;

As a consequence of the available examples, I think it’s been commonly assumed that this is the only way to use the get_ddl function – but it’s just a pl/sql function returning a CLOB, and you can use it on any data set. For example, if you want to generate the DDL for all the tablespaces on which a particular user (“test_user”, in my example) has a quota, you could run the following:

set long 20000
set pagesize 0
set linesize 180
set trimspool on
break on row skip 1

select
	dbms_metadata.get_ddl('TABLESPACE',tablespace_name)
from
	dba_ts_quotas
where
	username = 'TEST_USER'
;

Short Sorts

I posted a little holiday quiz – timed to appear just before midnight (GMT) on 24th December – that asked about the number of rows sorted and the memory used for queries like:

select sortcode
from
        (
  select sortcode
  from t1
        order by
  sortcode
        )
where
  rownum <= 10
;

The number and variety of the responses was gratifying. It’s always interesting to see how many important little details appear as people start to tackle even fairly straight-forward questions like this.

Holiday Quiz

I have a table with one million rows, there are no indexes on the table. The table has a column called sortcode which has no nulls, and has been generated in a highly random way so that no value appears more than four times. Consider the following queries:

select 
        sortcode
from    t1
order by 
        sortcode
;

select  sortcode 
from
        (
        select  sortcode
        from    t1 
        order by 
                sortcode
        )
where
        rownum <= 10
;

How many rows are sorted in each of these two queries – and roughly how much memory would you expect Oracle to use ?

Addendum: in the light of comment #2, assume sortcode is char(6).

Btree / Bitmap

In a recent ‘philosophy’ post I focused on the critical mental image that should be adopted when comparing B-tree and bitmap indexes. I was a little surprised, however, to discover that the idea I proposed needed further explanation. So here’s a note that expands on the original comment.

Optimizer Features

Each time you upgrade the Oracle server (even with a patch release), you may find that some strange things happen to a few execution paths. Every release carries some changes to the optimizer code – sometimes enhancements, sometimes bug fixes – and every change might be one that just happens to do something nasty with your existing code.

A little feature that may help when you upgrade is the view v$system_fix_control. This is a view which lists a number of bug fixes that you can disable with the _fix_control parameter. (The parameter and view appeared 10.2.0.2, I believe).

Simple scripts

Latest addition: Reporting just the branch blocks from an index treedump.

Since many of my customers are fairly hot on security I’m often banned from plugging USB drives into local PCs or getting my laptop onto the local network or downloading from the Internet, so I’ve often had to type up ad-hoc queries whenever I’ve had a thought about some useful piece of information that I want to pull out of the database.

On the plus side this means that I usually manage to keep up to date with the information that’s available in the data dictionary and dynamic performance views – because I have to check it from time to time; on the minus side, I’ve never really got around to creating a tidy library of useful scripts – after all it only takes a few minutes to create an ad hoc query but it takes a couple of hours to write the sort of “production quality” that I would normally feel happy about publishing.

However, I’ve been asked for ideas and scraps so often that I’ve decided to start supplying some of the quick and dirty solutions that I’ve hacked together in a few minutes. Even though they’re very basic scripts, and not intended to cover all situations, you may find some of them useful.

For future reference, I’ve also added a link to this page near the top of the panel to the right.

Partition Indexing

From time to time I get asked if it’s possible to index a partitioned table so that recent partitions have different (local) indexes from older partitions. The answer is “not really, but there’s a couple of dirty tricks which aren’t very nice and aren’t very stable“. (You can always play around – dangerously – with unusable indexes or function-based indexes).

With Oracle 11.2 there’s a new optimizer feature called “table expansion” which I’m guessing has been created to address this issue. Christian Antognini introduces it in this posting – which is actually starts by talking about zero-sized segments and unusable indexes.

Adaptive Optimisation ?

Here’s an interesting post and test case from Gregory Guillou (WeDoStreams blog). It features an SQL statement that is re-optimised the second time you run it.

Since it’s running on 11.2.0.1 your first thought is likely to be “SQL Plan Management”, or “Adaptive Cursor Sharing” – except the first feature wasn’t enabled, and the statement doesn’t include any bind variables.

Gregory emailed me about this one, and  it was the thing that finally persuaded me to tear down a laptop and install 64-bit OEL with 11.2 – and I ran his test case and got the same results. (If you build his sample schema, you’ll need about 1GB of free space).