Statspack Aid

As an aside to comments I’m planning to make on statspack, I thought you might like to look at some of the material written by Tim Gorman about using a statspack repository.

For his collection on whitepapers, including a Powerpoint presentation and Word document on statspack.

And for his collection of scripts, including several that manipulate statspack data.

Analysing Statspack (1)

[Forward to Part 2] 

“How do you interpret statspack data ?”

If you go for a generic answer, it’s likely to be a long answer – but the first step is always the classic  “Tom Kyte turnaround”  … Why do you want to interpret Statspack data ?

As “a starter for ten” [Ed: a reference that may be familiar only to UK viewers of the quiz show Univeristy Challenge], I’m just going to float one thought: there are three reasons for looking at Statspack data and the reason dictates the approach, and the significance you attach to the data.

Bitmap Indexes

Coming a close second to the “rebuild indexes regularly” fairy tale is the “a column with a small number of distinct values is a good candidate for a bitmap index” legend.

I’ve had to explain the error in the legend three times in the last month, so I thought I’d just point to a series of articles I wrote some time ago that might save people from wasting too much effort the next time they start to play with bitmap indexes.

• Introduction to bitmap indexes (ca. 1999)
• Bitmap indexes (ca. 2003)
• Star Transformations (ca. 2003)
• Bitmap Join indexes (ca. 2003)

All three articles were written several years ago; so when you review them, remember the advice I gave in an earlier post.

low_value / high_value

If you take a look at view user_tab_columns (or equivalent) you will find that its definition includes columns low_value and high_value, which give the currently known low and high value for the data. For example:

Object IDs

If you’ve read my note on free buffer waits you will have seen a comment that, in x$bh, objects with obj (data object id) values in the region of 4 billion were undo segments. There is another numbering anomaly you need to be aware of. 

Objects with obj value in the region of N * power(2,22) – i.e. multiples of roughly 4 million, are the physical instantiation of global temporary tables (GTTs) and their indexes. Oracle cannot use a real data_object_id as there isn’t one and several sessions may be using their own private copies of the same global temporary table definition).

For global temporary tables and their indexes obj is derived from the file id and block number of the start of the extent used to hold the data. Specifically, the obj value is power(2,22) * tempfile id + block number.

This leads to a worrying thought. I have already seen sites which regularly, and frequently, do DDL against partitioned tables; and a couple of sites I have visited are already using data_object_ids in the range of 1.2M to 1.8M – what happens when a real data_object_id hits the critical power(2,22) limit ? Is there any code in the buffer handler that will automatically treat it as a temporary object ?

Event 10132

From 9i onwards, if you enable event 10132 in your session, then every statement you subsequently optimise will be dumped into your trace file, along with the structure of the actual execution plan that was used. For example, from an early version of 9i:

Autotrace

A quick tip if you use autotrace in versions of Oracle prior to 10.2.

There are a number of column definitions that affect the output of autotrace, and you might want to set these in a login.sql, or glogin.sql script so that you have a better chance of getting a tidy output. For example, my typical login.sql holds the lines:

Disabling AWR

I see that in a comment on  the AWR Dilemma that Alex Gorbachev has pointed out the existence of the parameter _awr_restrict_mode  as a possible attack on disabling AWR.

For a few more options to disable the monitoring overheads of 10g when you aren’t licenced to use the diagnostic and performance packs, you might also want to investigate the effect of the following parameters – quoted here with their descriptions from x$ksppi.

_addm_auto_enable           governs whether ADDM gets run automatically after every AWR snapshot
_ash_enable                 To enable or disable Active Session sampling and flushing
_ash_disk_write_enable      To enable or disable Active Session History flushing
_swrf_mmon_flush            Enable/disable SWRF MMON FLushing
_swrf_mmon_metrics          Enable/disable SWRF MMON Metrics Collection

There are several more related parameters but the selection above appears to enough to eliminate ASH, AWR, and ADDM. 

As always, you need to remember that you should not set hidden parameters on production systems without approval from Oracle support. Note especially, I have only run a couple of small tests on the effects of these parameters – so I haven’t looked for any side-effects to changing their defaults.

Statspack level 6

While teaching a session on explain plan and associated technology today, I mentioned the sprepsql.sql script that can be used to report execution plans. At the end of the session one of the attendees asked me if I was aware of a bug with v$sql_plan that could cause the statspack snapshot call to hang the database on a library cache lock until you killed the process.

In fact I was aware of it, but had completely forgotten about it. So at the end of the day I did a quick search on Metalink to see if I could find a reference. It looks like bug 3778541, opened on 20th July 2004, last updated 20th July 2006 (with predicted fix in version 9.2.0.99 – so that looks like a long lifetime for 9.2 ).  However this bug is, in fact, reported as fixed in the patch list for 9.2.0.7.

x$ksmsp

Metalink bug number 5637916 was brought to my attention earlier on today:

The customer ran the following query, as suggested and the entire database seemed to freeze.

select
        ksmchcom        ChunkComment,
        ksmchcls        Status,
        sum(ksmchsiz)   Bytes
from    x$ksmsp
group by
        ksmchcom, ksmchcls
;

Can you tell us how costly this query is, and why it may be causing this problem?

I wonder if it’s anything to do with this issue

Tuning Updates

Consider the following update statement:

update /*+ index(t1 t1_n1) */ t1
set
        n1 = 0,
        n2 = n2 + 1,
        status = 'X'
where
        n1 > 100
and     status = 'A'
;

Free Buffer Waits

I think that many DBAs are likely to be familar with the event “free buffer waits” and recognise it as an indication that DBWR (the database writer) is not clearing the buffer cache of dirty blocks fast enough. But that’s not the only cause of free buffer waits.

AWR Dilemma

Here’s an odd little observation that came out of the UKOUG conference last week:

If you’re running 10g, and don’t have the relevant performance pack and diagnostic licences there are various views you are not allowed to query, packages you are not allowed to run, and reports you are not allowed to read.

To support the performance diagnostics, though, your database is probably churning away constantly dumping all sorts of useful data into the memory structures and tables that you are not allowed to access.

But that’s no longer a problem – it looks as if you can disable the AWR collection very easily in 10.2 with the following procedure call:

execute dbms_workload_repository.modify_snapshot_settings(interval => 0)

There’s just one problem with this – you can’t run this package unless you’re licensed to run the AWR. So if you want to disable the AWR, it looks like you have to pay to run it first.

How long …

… should it take Oracle to run my query ?

It’s a perfectly reasonable question, and one that you should be able to answer before you start to worry about tuning the query – but I wonder how many people actually formulate the question, and then do a scratch calculation to get an answer. And if you don’t do it, how do you know what your tuning target is ?

11g benefits

Posting from the UKOUG (UK Oracle User Group) conference. A couple of useful details from Tom Kyte’s technical keynote on “Things which might be in 11g but we’re not making any promises and you can’t hold us to it”.

The optimizer will be extended to allow us to collect some statistical information about correlated columns. This should help the optimizer to deal with combining predicates like: “Orders made in the last two weeks” with “Orders that have not yet been delivered” – if they’re recent orders, they’re more likely to be undelivered. I’m looking forward to seeing how far the Optimizer team has got with handling this rather difficult problem.

Partition handling: one of the current irritations for partitioning is that you have to disable referential integrity between partitioned tables if you want to drop old partitions. (Drop child partition Jan2001, followed by an attempt to drop the “obviously matching” parent partition Jan2001 currently results in Oracle error “ORA-02266: unique/primary keys in table referenced by enabled foreign keys”). 11g will give us the ability to declare that the partitioning of the child table is dependent on the partitioning of the parent table, and therefore guaranteed to be in-synch with the parent table. Apart from handling the drop partition problem, this should also help to avoid accidents that manage to disable partition-wise joins.