Who's online

There are currently 0 users and 39 guests online.

Recent comments



Rebuilding Indexes and the Clustering Factor Solution (Move On)

Excellent !! This quiz created quite a bit of debate and it was nice to sit back and read some interesting discussions. The Clustering Factor is basically the measurement of how well aligned the data in the underlining table is in relation to the index and is the number of table related I/Os required to read the entire table via a [...]

Rebuilding Indexes and the Clustering Factor Quiz (One Of The Few)

Today’s question has been prompted by various recent comments regarding the Clustering Factor (CF) of an index and how to change the CF requires a reorg of the underlining table. It used to be quite a common myth that if the CF of an index was greater that “X” or based on some nonsensical formula the [...]

Big Tables, Sorts and Indexes Solution (Right On Mother)

My, what a clever lot we have reading this blog Indeed, most of the work has already been done for me as we already have a couple of excellent demos in the list of comments. The answer is Yes, the CBO will consider using the index and Yes, there may be a number of scenarios when the [...]

Big Tables, Sorts and Indexes Quiz (Candidate)

Following on from the previous quiz on Descending indexes. Simple scenario. You have a huge table, 10 Million plus rows. You have an index on a column with a NOT NULL constraint but there are various other columns in the table not included in the index. You want to select all columns and all rows [...]

Descending Indexes Solution (Yellow Submarine)

Answers to the quiz on Descending Indexes and hopefully some useful dangers and tips on using them. The answer to the first question is Yes, a “normal” Ascending Index can be used by the CBO to retrieve data in descending order and hence possibly avoid a sort. The reason being that leaf blocks in the [...]

Descending Indexes Quiz (Up On The Ladder)

OK, you won’t find the answer to these questions on my blog, so using my search facility won’t be of any help Actually, it’s quite an easy one this, honest If you have a query such as: SELECT * FROM bowie WHERE id BETWEEN 42 and 84 ORDER BY id DESC; 1) Can a default B-Tree index on [...]

Best Method To Select One Row From Small Table Quiz (Each Small Candle)

Assume you have a tiny little table with just 42 rows (naturally) that all fit in one table block. Order the following options in order of “efficiency” (most efficient option first) when accessing just one of these rows: 1) Full Table Scan of Heap Table 2) PK access of an Index Organised Table 3) Index access [...]

Quiz Night

I've recently come across an interesting variation of a "famous" ASSM bug. Probably some of you will remember that ASSM bug that was caused by row migrations in larger block sizes (16K/32K).

If you don't remember or don't know what I'm talking about, you can have a look here where Greg Rahn provides a summary of the issue or check My Oracle Support bug description 6918210.

Greg also links to a script originally created by Jonathan Lewis that allows to reproduce the issue at will.

So far the issue was only reproduced on block sizes greater 8K - the variation I've encountered however allows to reproduce the issue on 8K and 4K, possibly also on 2K, but I haven't tested 2K yet.

Below is my version of script. If you compare it to Jonathan's version you'll notice that it is very similar, if not to say almost the same except for additional optional instrumentation, that you can simply un-comment if you've installed my Advanced Oracle Troubleshooting script package that is based on Tanel Poder's awesome "tpt_public" tool set.

The SESSPACK tool can be found in Tanel's tool set (tools/sesspack_0.05_release) and the SNAP_KCBSW package has been developed by Jonathan a long time ago - it can be found here. Note that it only works for versions below 11g - this instrumentation has been "optimized away" in 11g, unfortunately.

In order to reduce the runtime, I've simply limited the number of rows in the table to 50,000 rows.

set echo on timing on

drop table t1;

purge table t1;

TABLESPACE &tblspace;

INSERT --+ append
SELECT TRUNC(dbms_random.VALUE(10000000,100000000)) n1,
FROM dual

BEGIN dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1');

SELECT num_rows,blocks FROM user_tables WHERE table_name = 'T1';

/* Uncomment for instrumentation
@trci assm_bug



exec sesspack.snap_me

execute snap_kcbsw.start_snap

@46on 8

alter session set events '10046 trace name context forever, level 8';

UPDATE t1 SET n2 = n1;


/* Uncomment for instrumentation
@trci assm_bug_off


alter session set events '10046 trace name context off';

/* Uncomment for instrumentation
set serveroutput on size 1000000 format wrapped
set linesize 120
set trimspool on

execute snap_kcbsw.end_snap

exec sesspack.snap_me

BEGIN dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1');

SELECT num_rows,blocks FROM user_tables WHERE table_name = 'T1';

/* Uncomment for instrumentation
@trc_orasrp &trc_p &trc_f

@trc_tvdxtat &trc_p &trc_f

Here is the task: You are allowed to modify the script at exactly one single location - the modification can take a maximum of four keywords, which means you can add/modify/remove at most four keywords.

With the correct modification you will be able to reproduce the bug even in 8K and lower block sizes.

So, what to modify and why?

If you want to actually run the script yourself you need to use database versions prior 11.2 because the bug is obviously fixed there - this includes, which interestingly doesn't have the bug fixed.

I've used a 8K/4K ASSM tablespace with UNIFORM 1M extents for my tests, but I don't think that the extent management matters in that case. My test database uses 8K as default block size.

You'll notice the bug when checking the runtime and the trace file. If you encounter the bug, the runtime for the update will be several seconds (more than 10 seconds seen on my test system in some cases) and the number of current mode gets for the update will be in the millions.

If you've enabled the additional instrumentation it will tell you that the reasons for the buffer gets where "ktspfsrch" and "ktspscan_bmb" for most of the gets. You can also take stack traces (e.g. using Tanel's OStackProf tool) if you use more than 50,000 rows to have a longer runtime of the update statement which will show you similar function names on the stack.

If you don't hit the bug, the update usually takes max. 1-2 seconds, and the current mode gets should be far less than one million when sticking to the 50,000 rows.

P.S.: There is more than one correct answer - and it is possible to hit the bug for 8K block sizes with a single keyword modification (full points!).

Update 24th Jan: P.P.S: No takers yet... So here's an additional hint: The issue is caused by row migration...

Update 26th Jan: OK, time to post a quick answer here. As pointed out by Narendra below, simply setting PCTFREE to 0 already was sufficient to reproduce the issue with smaller block sizes. However, there is much more to tell about and therefore this deserves a separate post that I'll publish the next couple of days.

For the time being here are the correct answers that I'm aware of at present:


But as I already said, there is much more, in particular when partitioning comes into the picture - and I hope to cover all these details in the upcoming post.

User objects created in the SYS schema and the (cost based) optimizer

The answer - as already disclosed by Nicolas Gasparotto - to the question that I asked here in the "Weekend Quiz" is to run the script as SYS user, and then run the query shown against these objects in the SYS schema (tested against 10g XE, and on Win32).

Note: It's not recommended to create any non-SYS objects in the SYS schema and you should only perform this (if at all) in a test database.

All this came up in this recent OTN forum thread where it became obvious that the issue can only be reproduced if the objects are owned by SYS.

There are two interesting points to derive from this (apart from the obvious that one should not create any user objects in the SYS schema):

1. The optimizer seems to treat objects owned by SYS differently, in particular regarding the transformations applied. Note that the crucial point is not that the query is executed as SYS user, but that the objects are owned by the SYS user. Granting appropriate privileges to a non-SYS user on the objects owned by SYS allows to reproduce the issue even with a non-SYS user.

2. It's something to remind if there is the need to understand a performance issue with a recursive dictionary query performed on SYS-owned objects. Although you obviously can't influence the SQL generated by Oracle itself it might help to understand the issue and take appropriate steps to rectify the issue.

Oh, by the way, have I already mentioned that it's really a bad idea to create user objects in the SYS schema?

Weekend quiz

Quiz questions seem to be in nowadays, so here's one for you regarding the cost based optimizer:

Given this simple script (Run this on any 10.2.x (including XE) or

create table test1
select * from dba_objects;

create table test2
select * from dba_objects;

create index i_test1_1 on test1(object_name);

create index i_test1_2 on test1(object_id);

create index i_test2_1 on test2(object_name);

create index i_test2_2 on test2(object_id);

exec dbms_stats.gather_table_stats(null, 'TEST1', estimate_percent=>null, method_opt=>'for all columns size 1');

exec dbms_stats.gather_table_stats(null, 'TEST2', estimate_percent=>null, method_opt=>'for all columns size 1');

And this simple query:

explain plan for
and object_id in (
select /*+ unnest */

with this plan:

Plan 1 (the obvious one):

Plan hash value: 2107173885

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 2 | 212 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 2 | 212 | 6 (0)| 00:00:01 |