Autoallocate and PX

Here’s a quirky little set of results from trace files. First a 10391 at level 64 – this shows the way in which an object was broken into granules for a parallel tablescan. I’ve just listed the first few granules:

Version Dependency

Here’s a very simple query, hinted to follow a particular execution plan.

 
select 
	/*+ ordered use_nl(t1) index(t1) */ 
	t2.n1, t1.n2 
from 
	t2,t1 
where 
	t2.n2 = 45 
and	t2.n1 = t1.n1 
; 

And here’s the execution plan – from 9.2.0.8 autotrace (with CPU costing disabled)

Oracle and I/O

Thanks to a recent link on Doug Burns’ website, I’ve been introduced to an article on Bob Sneed’s website that everyone ought to read – especially the bit about testing properly (The Proof of the Pudding)

Debugging

How do you discover the source of a problem ? It’s not an easy thing to teach, but here’s a recent example someone sent me that may give you a clue. This is a question that came to me by email:

System Stats strategy

A few days ago I received an email about system statistics. I decided it was worth replying to, provided I wrote my response up as a series of questions and answers on the blog. (I don’t tend to respond to individual questions –  it’s not an effective use of my time – so there has to be a good reason for replying).

Data Cluster

I received an email today containing the following question:

For packaged applications, like the Oracle EBS, some indexes tend to develop a high Clustering Factor over time, like the one shown below:

 
            BTREE    LEAF    DISTINCT  CLUSTERING       INDEX     TABLE       TABLE 
INDEX NAME  LEVEL  BLOCKS        KEYS      FACTOR    NUM ROWS  NUM BLKS    NUM ROWS 
----------  -----  ------  ----------  ----------  ----------  --------  ---------- 
XXX_PK          3  778150  77,842,100  17,163,350  77,842,100   865,805  77,043,200           

We have some queries that perform range scans on this index and run slower because they have to visit too many blocks from the table. How should one deal with this type of index?

This question raises a couple of important points that are worth reviewing.

v$session_wait_history

One of the (free) aids to trouble-shooting that appeared in 10g was the view v$session_wait_history, a view that lists the last 10 waits for the currently live sessions. For example:

Slow Code

No, this isn’t going to be about dealing with slow code – it’s about how you can slow your code down when you need to. This may seem a little strange, but for testing purposes you might (for example) want to create a long-running query which didn’t actually do very much work – all you need is a small query with my ‘slow down’ function built in.

Statspack notes

If you’re a user of statspack, have a little browse of Dan Fink’s blog, you’ll find some useful comments there. For future readers of this pointer, check particularly around the March 2007 timeframe.

LOB sizing

Some time ago, I was asked to take a quick look at an application that had to handle a lot of LOBs. The LOB-specific part of the application was actually quite simple – contracts were stored as LOBs – but only for occasional visual reference; all the “structured” information from the contract was extracted and stored in relational tables. Some time after a contract had expired, the LOB could be deleted to reclaim space (in theory).  Historically, the client had purged a load of LOBs from time to time, but didn’t have a deliberate house-keeping task to do the job on a regular basis.

More of a hash

No matter how simple a topic you pick, a few minutes thought invariably allow you to conjure up some new anomalies that could appear in the right (or possibly wrong) circumstances.

Yesterday I made a few comments about hash partitioning and performance. Today I suddenly realised that global indexes on hash-partitioned tables could exhibit an unfortunate behaviour pattern that would make them pretty useless – unless hacked or hinted. Consider the following table:

Hash Partitions

A question recently appeared on an article I wrote about partitioning a few months ago:

We are planning to create 8 HASH partitions. Looking only at PERFORMANCE would be there be any improvements, if we go for 16 or 32 partitions (maintainance and availability is not a problem in our case). There are only 2 indexes on our 350M table – one is LOCAL, another non-partitioned index.

Optimizer Environment

From time to time you may notice that a single SQL statement has several different child cursors visible in v$sql. One reason for this happening is that different sessions my be running with different optimizer environments, for example your optimizer_mode may be all_rows while mine is first_rows_1. If your optimizer environment is different from my optimizer environment, then we cannot share cursors.