OK, in exactly the same database, I repeat the demo again with the same 3 sessions populating the data in exactly the same way using exactly the same procedure with 100% accurate statistics, but there’s just one tiny little difference in the setup script:

Share this:

Like this:

Related

Has tablespace USER_DATA been created with freelist space management and USER_DATA1 with ASSM by any chance?

This example has effects very similar to a demonstration of how freelist management can badly affect clustering factor of indexes. Jonathan Lewis has a similar example in his ‘Cost Based Oracle Fundamentals’ book.

forgot to add why the different freelist strategies would cause an issue with clustering factor.

Basically with traditional free list management the three sessions inserting rows are more likely to be inserting the sequence numbers in roughly the same data blocks and hence getting a clustering factor that favours the index.

With ASSM, the sessions may create records with sequence numbers in vastly different data blocks and therefore increase clustering factor to make the index less favourable.

Tablespaces user_data and user_data1 have hugely different block sizes? The index stays in the user’s default tablespace (which might be user_data, user_data1, or something completely different), but the table uses different numbers of blocks, hence the index has different clustering factor AND the cost of a full scan of the table differs significantly.
It might be that they have the same block sizes but different default storage parameters (pctfree comes to mind) which would affect the stats and the relative costs in a similar way.
I’m not sure what the significance of the three concurrent inserting sessions is – they should crash the index (50-50 splits, index ITL explosion if using the default initrans of 2) the same way in both cases.

First of all, I wasn’t able to produce the desired effect on my 11.2.0.3 database. Even the table populated from 3 concurrent sessions was not causing a full table scan. Without being able to reproduce, I can only speculate.

Each session is operating on its own extent, clustering factor will be much larger if you populate the table concurrently. I executed your example and I got clustering factor of 303 for the serial load and 398 for 3 concurrent sessions, which is almost 33% increase. Depending on the DB version and the exact text of the concurrent loading scripts, your clustering factor may be significantly increased, which would make the full table scan faster for the large number of rows.

Obviously the difference is ASSM. Then I remembered something I had read somewhere: “Generally, rows with monotonically increasing column values are physically inserted in the order of the monotonically increasing columns. This may not be the case however with tables in ASSM tablespaces or tables with multiple freelists or freelist groups as concurrent inserts will be directed to differing blocks. In these cases we may actually have data that is quite well clustered but may have quite poor CF values due to the manner in which the CF is calculated.” So I think Tony already gave a correct explanation.

A range scan, which turns into a table scan …
First, it makes me think that’s a problem with
Clustering Factor …
so I think(as others have already responded) the difference is called “ASSM” (or “MSSM”) associated with multiple concurrent inserts.In particular, the erroneous calculation of the clustering factor performed by DBMS_STATS is due to the parameter History
the function

sys_op_countchg(substrb(t.rowid,1,15),1)

History, the last parameter of the function sys_op_countchg,
represented by the number 1, that is the number of blocks that are remembered to determine if the block has changed.
In the case of use of ASSM, to calculate the correct CF
you should replace the parameter “History” with the number
concurrent processes that populate the table or the number
of freelist if the number of processes exceeds that of the freelist.
But this suspicion, if you remember, is what I had in mind for the
Alberto problem