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: (more…)
May 29, 2007
May 28, 2007
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 22.214.171.124 autotrace (with CPU costing disabled) (more…)
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)
May 21, 2007
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: (more…)
May 20, 2007
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).
May 17, 2007
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.
May 16, 2007
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: (more…)
May 15, 2007
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. (more…)
May 11, 2007
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.
May 8, 2007
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: (more…)
May 7, 2007
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.
May 1, 2007
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.