Thank-you
March 10, 2008 - 4pm Central time zone
Reviewer: Tom from VA
Thanks Tom!
Determining the limit for rowid creation
March 27, 2008 - 6pm Central time zone
Reviewer: William Rose from Melbourne, Australia
Hi Tom,
Even though the limit for a particular table is block and
row size dependent, is there still a theoretical maximum
(e.g. based on the width of the portion of the rowid devoted
to encoding the row number)?
This is of particular interest when creating rowids. You
use, in other examples, dbms_rowid.rowid_create to create
rowid ranges. There is typically a query snippet like this:
select dbms_rowid.rowid_create
( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
dbms_rowid.rowid_create
( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
The 10000 in the last line implies that you assume there are
never more than 10000 rows in a block. Experimenting on my
Oracle 9.2 (terminal release) dev database, I can make this
number 32767 (2^15 - 1), but beyond that I get
ORA-01410: invalid ROWID, suggesting a limit of 32767.
But this might also simply be the block size (32K) and
therefore the limit is the number of bytes in the block,
and is just block size dependent as you said first.
So I guess the question then becomes: is your 10000 a
reflection of a known limit, or is it just unlikely that
there are more than 10000 rows in a block? And what is a
good value to use when creating rowids as an upper bound
for the row number?
I tried to work this out with empirical testing, and found
that on the production databases I support I can quite
happily increase this number to 4294967295 (2^32 - 1) when
calling dbms_rowid.rowid_create without triggering ORA-01410
errors. That makes me think that, even if there is a limit
based on block size, dbms_rowid.rowid_create will take any
overflow for that limit and treat it as a block offset or
something similar. In production that doesn't matter
because the overflowed block offset still produces a valid
rowid. But an alternative is that it means there is some
other limit in the number of rows/block. Can you shed any
light on this?
Followup March 30, 2008 - 8am Central time zone:
... or is it just unlikely that
there are more than 10000 rows in a block? ....
it is that.
If you wanted to be absolutely "safe", use the NEXT highest block number and a less than instead of less than or equal in the predicate.
sort of like getting all of the data for a "day" you could:
where dt between to_char( '01-jan-2008','dd-mon-yyyy')
and to_char( '01-jan-2008 23:59:59', 'dd-mon-yyyyy hh24:mi:ss')
or you could:
where dt >= to_char('01-jan-2008','dd-mon-yyyy')
and dt < to_char('02-jan-2008','dd-mon-yyyy')
the second one might be "safer" if you ever switched to timestamps for example - in you could miss records in the first case if they happened just after 23:59:59 and before midnight...
Addendum
March 30, 2008 - 9pm Central time zone
Reviewer: William Rose from Melbourne, Australia
Thanks for that!
I had tried using the next highest block number (e.g. query
with LEAD(), or PL/SQL loop that remembers last value), and
this sidesteps the problem of calculating the upper bound
for rowids in an extent very nicely, at the expense of a less
friendly condition like:
...
AND rowid >= this_extent_start_rowid
AND (rowid < next_extent_start_rowid OR next_extent_start_rowid IS NULL)
vs.
AND rowid BETWEEN start_rowid AND end_rowid
In case it's useful to future generations, I note in the 11g
database documentation that the maximum block size appears to
be 32K, as it is in 9i.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/create005.htm#i1014092
This would explain the upper limit I saw in my empirical
testing, as > 32767 rows means rows < 1 byte, even in the
maxiumum block size, and thus is impossible. So if creating
rowids for the upper limit I guess 32767 is as close as it
gets to an "upper limit" on row numbers.
May 21, 2008 - 6am Central time zone
Reviewer: Karteek from India
Tom...I have touble in generating correct rowid from v$session. Please see below. I have generated rowid for a sessoin existing for DUAL. and when I use the same rowid it to select from dual its throwing invalid rowid error.
I see the problem with ROW_WAIT_BLOCK#. In sessoin details its showing as 0, where the actual rowid has it as 389. Can you help me please?
SQL> select ROW_WAIT_OBJ#,
2 ROW_WAIT_FILE#,
3 ROW_WAIT_BLOCK#,
4 ROW_WAIT_ROW#,
5 dbms_rowid.rowid_create(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#,
ROW_WAIT_ROW
#) "RID"
6 from v$session a, dba_objects b
7 where a.ROW_WAIT_OBJ# = b.object_id
8 and b.object_name = 'DUAL';
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# RID
------------- -------------- --------------- ------------- ------------------
195 1 0 0 AAAADDAABAAAAAAAAA
SQL> select * from dual where rowid = 'AAAADDAABAAAAAAAAA';
select * from dual where rowid = 'AAAADDAABAAAAAAAAA'
*
ERROR at line 1:
ORA-01410: invalid ROWID
SQL> select rowid from dual;
ROWID
------------------
AAAADDAABAAAAGFAAA
SQL> select dbms_rowid.ROWID_RELATIVE_FNO('AAAADDAABAAAAGFAAA') object_id,
2 dbms_rowid.ROWID_BLOCK_NUMBER('AAAADDAABAAAAGFAAA') lo_fno,
3 dbms_rowid.ROWID_ROW_NUMBER('AAAADDAABAAAAGFAAA') lo_block from dual;
OBJECT_ID LO_FNO LO_BLOCK
---------- ---------- ----------
1 389 0
Thanks!
Followup May 21, 2008 - 8am Central time zone:
why would something be waiting on dual for heavens sake? Whats going on here.
that information is only valid if the session in question is waiting for that row.
if I use two sessions:
a) session 1 "select * from dual for update"
b) session 2 "select * from dual for update" (blocks)
c) session 1 queries the above - it gets a valid rowid.
unless session 2 is actually BLOCKED, this information is not valid.
You do not describe the precise steps to get where you are - so, we cannot comment further.
May 22, 2008 - 3am Central time zone
Reviewer: Karteek from India
Tom,
I also wondered when I noticed that in our production yesterday. We are still trying to figure out what causing a lock on DUAL.
In the mean while, I have again taken created one rowid from v$session which is showing as invalid. Please see below.
SQL> select
2 'select * from ' || b.owner||'.'||b.object_name || ' where rowid = ''' ||
dbms_rowid.rowid_crea
te(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) || '''' "Query"
3 from v$session a, dba_objects b
4 where a.ROW_WAIT_OBJ# = b.object_id
5 and b.owner <> 'SYS'
6 and rownum < 2;
Query
--------------------------------------------------------------------------------
select * from EDW.HOUSE_DIM where rowid = 'AAACvLAAIAAAAAAAAA'
SQL> select * from EDW.HOUSE_DIM where rowid = 'AAACvLAAIAAAAAAAAA';
select * from EDW.HOUSE_DIM where rowid = 'AAACvLAAIAAAAAAAAA'
*
ERROR at line 1:
ORA-01410: invalid ROWID
SQL>
Thanks!
Followup May 22, 2008 - 7am Central time zone:
you have not shown that you are meeting all of the requirements here for using these columns.
I'll repeat:
unless session 2 is actually BLOCKED, this information is not valid.
May 22, 2008 - 8am Central time zone
Reviewer: Karteek from India
Thanks Tom, I got the point. I ignoed your point (BLOCKED) in yesterday's reply. In fact other session is not blocked, it was a shared lock. Actually all I was trying to do was to find a row in a table (by usgin rowid) that is locked by a session.
So, from your words what I have understood is shared locks cann't give you a way to find correct rowid (using createrowid proc). I'm not sure..could you please explain me. And any reason to support why they don't give correct rowid...
Thanks!
Followup May 23, 2008 - 7am Central time zone:
how are you thinking you are getting a shared lock on a row.... one a single row...
May 24, 2008 - 3am Central time zone
Reviewer: Karteek from India
I have created this statement to find which tables/records have locks of any type. May be I am wrong at the joins. Please correct me, and make me understand. I am wondering if ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# is v$session doesn't locate a correct rowid.
To answer your question on lock type iwas basing on v$locked_object.lock_type. Please share your suggestions Tom.
select
b.sid,
b.serial#,
b.process,
d.ctime "LOCK TIME (sec)",
c.object_name,
decode(a.locked_mode, 1, 'NULL', 2, 'ROW SHARED', 3,'ROW EXCLUSIVE', 4, 'SHARED', 5,'S/ROW
EXCLUSIVE', 6,'EXCLUSIVE' ) "LOCK MODE",
b.last_call_et "INACTIVE FOR(sec)",
decode(d.block, 1, 'Yes', 0, 'No') "BLOCKING",
dbms_rowid.rowid_create(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) "LOCKed
ROWID"
from v$locked_object a,
v$session b,
dba_objects c,
v$lock d,
where b.sid = a.session_id
and c.object_id = a.object_id
and d.sid = a.session_id
and d.id1 = a.object_id
Thanks!
Followup May 24, 2008 - 7pm Central time zone:
...
I have created this statement to find which tables/records have locks of any type.
....
v$lock and dba objects would be all you would need, you don't need the others to achieve your stated goal.
dbms_rowid.rowid_block_number(rowid)
July 6, 2008 - 3pm Central time zone
Reviewer: A reader
Hi Tom
What does the following statement show?Can you explain what it is used for?
dbms_rowid.rowid_block_number(rowid)
Followup July 7, 2008 - 11am Central time zone:
it simply returns the block number that the rowid points to.
a rowid is a file, block, slot on block and object id information - it tells us where a row "lives"
that just shows the block the row's head row piece is located on.
block if for index entry
September 23, 2011 - 2pm Central time zone
Reviewer: sonu from US
Hello sir,
dbms_rowid.rowid_block_number(rowid) is used to find out where row "lives". is it possible to know
where index entry lives? i.e. Is it possible to know that one particular index entry stored in so
and so block id?
Followup September 23, 2011 - 3pm Central time zone:
Not in a straight forward manner, no. You'd have to do some tracing or some dumping.
why do you want to know? what are you trying to do?
September 23, 2011 - 5pm Central time zone
Reviewer: sonu from US
I am trying to understand Index internals. I read few articles/blogs but still not clear. How index
stored in database for e.g. suppose in my table TEST I have a column NO and I am creating index on
that column. (let's assume in one index Block it stores 7 entry. I have 8192 block size). Now If I
store no. in a sequence 1,2,.. 7 then all nos will fit in first block and there is no branch block.
the moment I add one more no it will store in second block and it also create one branch block.
that way If I store 35 no in a sequence then it use total 7 leaf block and 1 branch block. but If I
store different 35 nos. then it use more leaf block and more branch block too. and here it also
possible my index size is bigger than my table size. so how Oracle store index entry internaly? I
am thinking 2 possibilities but not sure which one is correct.
1. When first record insert oracle will put it one first block (X). second row will go in same
block(X) if it is nearest no. third row will go in same block(X) if it is nearest no. (still there
is a space to write another 4 entry) but fourth records is some different no. (lets say) 100 then
oracle won't write it in first block(X) but will write it in second block (X1). (I am sure It won't
skip 100/7 blocks but just skip that first block). By any chance now if new nos are greater than
100 then it never use first block even though there is space available.
2. first record will go in first block, second record will go in same block even if it is not in
sequence (i.e first record is no. 1, second record is no. 100) third record is 101, 102, 103, 104,
105 all will go in first block and now there is no room and No. 2 came then it will make room in
first block my transfering 100 no to block 2 and no. 2 will go in block 1.
Is there any one possibility correct? Or Oracle is doing something different internaly?
Regards
Sonu
Followup September 24, 2011 - 1pm Central time zone:
and here it also possible my
index size is bigger than my table size.
how did you arrive at that conclusion? One would assume there are *other* columns in this table - and therefore, the table would not fit even 7 rows/block. On the index leaf block - you store just the key value plus the rowid.
So, unless your index key is about 1 Kbyte in size, you'd get more than seven rows/leaf block. But assuming your index key is 1 Kbyte - and assuming there are some *other* columns - it is highly unlikely for your index to be bigger than your table.
1) that is incorrect. When you put in the data - all of the rows up to the first seven would go onto the first index block. We'll fill it up with data in sorted order (you can put 1, 2, 3, 100, 9999999999999999, 42, 55 - for example) and we'll just store that sorted.
When the block fills up - when you try to add the 8th entry - we'll typically do a 60/40 split (60% of the data would stay on the leaf block - 40% of it will move to a new leaf block that will be linked into the structure right next to the current full block).
We reuse space in indexes all of the time.
sonu
September 25, 2011 - 12am Central time zone
Reviewer: A reader
Thank you sir for the explanation but somehow I am still not clear and so I have few more
questions.
1. We'll fill it up with data in sorted order - so you will store data in sorted order. i.e. if I
enter 1, 3, 5, 7, 2, 4, 6 then will it store like 1,2,3,4,5,6,7 (Assuming we have that 7 rows in 1
block).
2. we'll typically do a 60/40 split - so even if 8th record is in a sequence still oracle will keep
first 4(60% of 7 rows) record in first block and 3 record move to second block? is there any logic
which rows will stay in first block and which will go in next block?
now answer of your question "how did you arrive at that conclusion?" and my original assumption
no. 1, I did following test in my oracle database.
21:59:37 SQL> create table test (name varchar2(1000));
Table created.
21:59:42 SQL> create index ind_test on test(name);
Index created.
21:59:57 SQL> ed
Wrote file afiedt.buf
1 insert into test
2 select name from
3 (select substr(rpad('X', 1000, 'X') || level, -1000) name, level l from dual connect by
level<=5 ) a
4 where --mod(l,10)=0 and
5* not exists (select 1 from test b where a.name = b.name)
22:00:05 SQL> /
5 rows created.
22:00:06 SQL> analyze index ind_test validate structure;
Index analyzed.
22:00:14 SQL> analyze index ind_test compute STATISTICS;
Index analyzed.
22:00:14 SQL> SELECT * from index_stats where name = 'IND_TEST';
HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS
LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS
DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY
BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ------------------------------ ------------------------------ ----------
---------- ----------- ---------- ---------- ---------- ----------- ---------- -----------
--------------- ------------- ----------------- ----------- ---------- ---------- ------------
-------------------- ---------- ------------ -------------- ----------------
1 8 IND_TEST 5
1 5065 8000 0 0 0 0 0
0 5 1 8000 5065 64 1
2 0 0 0 0
Elapsed: 00:00:00.00
22:00:46 SQL> insert into test
22:00:57 2 select name from
22:00:57 3 (select substr(rpad('X', 1000, 'X') || level, -1000) name, level l from dual connect
by level<=60 ) a
22:00:57 4 where mod(l,10)=0 and
22:00:57 5 not exists (select 1 from test b where a.name = b.name)
22:01:00 6 /
6 rows created.
22:01:01 SQL> analyze index ind_test validate structure;
Index analyzed.
22:01:06 SQL> analyze index ind_test compute STATISTICS;
Index analyzed.
22:01:09 SQL> SELECT * from index_stats where name = 'IND_TEST';
HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS
LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS
DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY
BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ------------------------------ ------------------------------ ----------
---------- ----------- ---------- ---------- ---------- ----------- ---------- -----------
--------------- ------------- ----------------- ----------- ---------- ---------- ------------
-------------------- ---------- ------------ -------------- ----------------
2 8 IND_TEST 11
3 11143 8000 2 1 2017 8032 0
0 11 1 32032 13160 42 1
3 0 0 1 7
22:01:15 SQL> select * from test; -- to fit output here I kept just last 5 characters of this
table.
NAME
-----
XXXX1
XXXX2
XXXX3
XXXX4
XXXX5
XXX10
XXX20
XXX30
XXX40
XXX50
XXX60
11 rows selected.
Elapsed: 00:00:00.00
22:01:35 SQL>
If you see here it took 3 leaf block to store 11 rows which suppose to fit in 2 blocks only. I do
not understand why it is taking 3 leaf block??
I did few more insert..
22:14:22 SQL> insert into test
22:14:48 2 select name from
22:14:48 3 (select substr(rpad('X', 1000, 'X') || to_char(level), -1000) name, level l from dual
connect by level<=120 ) a
22:14:48 4 where mod(l,10)=0 and
22:14:48 5 not exists (select 1 from test b where a.name = b.name)
22:14:48 6 /
6 rows created.
22:15:52 SQL> insert into test
22:15:52 2 select name from
22:15:52 3 (select substr(rpad('X', 1000, 'X') || to_char(level), -1000) name, level l from dual
connect by level<=200 ) a
22:15:52 4 where mod(l,10)=0 and
22:15:52 5 not exists (select 1 from test b where a.name = b.name)
22:15:52 6 /
8 rows created.
22:15:53 SQL> analyze index ind_test validate structure;
Index analyzed.
22:16:00 SQL> analyze index ind_test compute STATISTICS;
Index analyzed.
22:37:44 SQL> select HEIGHT, BLOCKS, NAME, LF_ROWS, LF_BLKS, LF_ROWS_LEN, BR_ROWS, BR_BLKS from
22:38:25 2 index_stats where name = 'IND_TEST';
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN BR_ROWS
BR_BLKS
---------- ---------- ------------------------------ ---------- ---------- ----------- ----------
----------
2 16 IND_TEST 25 7 25325 6
1
22:38:30 SQL> select SEGMENT_NAME, SUM(BYTES)/1024 FROM dba_segments
22:38:34 2 where segment_name in ('TEST', 'IND_TEST')
22:38:34 3 group by segment_name
22:38:34 4 ;
SEGMENT_NAME SUM(BYTES)/1024
--------------------------------------------------------------------------------- ---------------
IND_TEST 128
TEST 64
Now here if we see to store 25 index entry it took 7 blocks which suppose to fit in 4 blocks.
(25/7=3.5) and as per dba_Segments, index size is bigger than table size.
I do not know what I am missing here or what I misunderstood but based of this test I thought
possibility no. 1 and to verify that I was looking something which shows me which block contains
which index entry? Pl. help me to understand this concept.
Thanks
sonu
Followup September 25, 2011 - 11am Central time zone:
21:59:37 SQL> create table test (name varchar2(1000));
As I said above, one would ASSUME there are other columns. I was able to accurately guess your schema without you providing it. If you read what I wrote above, you wouldn't have need to post anything here. We discussed this. You would need a single column table in order to have this happen in your case.
quote:
One would assume there are *other* columns in this table - and therefore, the table would not fit even 7 rows/block. On the index leaf block - you store just the key value plus the rowid.
So, unless your index key is about 1 Kbyte in size, you'd get more than seven rows/leaf block. But assuming your index key is 1 Kbyte - and assuming there are some *other* columns - it is highly unlikely for your index to be bigger than your table.
sonu
September 25, 2011 - 1pm Central time zone
Reviewer: A reader
Thank you sir for the clarification.. and I am clear about the size related stuff.. can you pl.
give me answer of my index's question.
1. We'll fill it up with data in sorted order - so you will store data in sorted order. i.e. if I
enter 1, 3, 5, 7, 2, 4, 6 then will it store like 1,2,3,4,5,6,7 (Assuming we have that 7 rows in 1
block). pl. confirm. (YES/No would be fine)
2. we'll typically do a 60/40 split - so even if 8th record is in a sequence still oracle will keep
first 4(60% of 7 rows) record in first block and 3 record move to second block? is there any logic
which rows will stay in first block and which will go in next block?
I try to find out something related to this but could not find, if you have such link handy then
pl. give it here..
thanks again
sonu
Followup September 25, 2011 - 2pm Central time zone:
1) yes, that is what indexes do
2) the rows have to go where they have to go. Indexes store data in sorted order in leaf blocks. The rows during a block split would be split based on their order in the index. 1,2,3,4 would stay on block one and 5,6,7,8 would go onto block two in general (there are also 90/10 splits - you'd see those when the index is known to be monotonically increasing like you would have for a sequence or date populated column...)
You are over analyzing this a bit if you ask me. An index is a sorted list of the rows in a table in order of their key. If you have access to Expert Oracle Database Architecture - I cover the structure in some depth. Also there is the concepts guide:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/indexiot.htm#CBBECABB
B-Tree cluster size - chained blocks for a key
September 27, 2011 - 12pm Central time zone
Reviewer: Basil
Hi Tom,
Into a 10.2.0.4 database I create a cluster and a table as following:
CREATE CLUSTER CU_CLU(ACCOUNT_NO NUMBER(10))PCTUSED 0 PCTFREE 3 SIZE 8K INDEX;
CREATE INDEX CU_CLU_IDX ON CLUSTER CU_CLU;
CREATE TABLE CU(f1 DATE,f2 NUMBER(10),f3 NUMBER(3), ACCOUNT_NO NUMBER(10)) CLUSTER
CU_CLU(ACCOUNT_NO);
If my understanding is right, considering the cluster size of 8K I would expect to get at least one
block for every distinct account_no. The cluster index will point to this first block. If the
records for an account will no fit into one block then Oracle starts to chain the blocks from this
"first block".( max chain length 65535 blocks).
I expect to get the chain length for an account using:
select count(distinct dbms_rowid.rowid_block_number(rowid)) from cu where account_no=12
I would also expect no blocks with more than an account in it.(because of 8K size)
Is this right or I miss something ? I ask this because of ORA-02475 which I could not explain.
thank you.
Basil
Followup September 27, 2011 - 1pm Central time zone:
1) that should be more or less true - you might not get the exact length if some of the blocks are currently empty.
2) if your blocksize is 8k, yes
B-Tree cluster size - chained blocks for a key
September 28, 2011 - 10am Central time zone
Reviewer: Basil
Hi Tom,
known facts:
- I have reorganized the cluster (truncate cluster CU_CLU drop storage)
- I have inserted the records (no parallel DMLs)- one insert in fact.
- no deletes in this table
I would expect the free blocks smaller than freelist blocks=5 .
The distinct block count for one account brings back 33301 blocks. I have tried to insert a new row
and I've got ORA-02475.
I came to the idea of "walking through" the chained blocks for an account by selecting the rows
into "natural" order.
Here is the select :
select nr, prev_block
from
(
select nr, lag(block,1) over (order by nr) prev_block,block
from
(
select rownum nr,dbms_rowid.rowid_block_number(rowid) block from cu where
account_no=47143978
)
)
where prev_block!=block
I've got indeed 65533 rows(chained blocks) - but there were only 33301 distinct blocks.
3)Any idea why Oracle is not fully using a block before allocating a second and how comes to
decision to reuse it later on ?
Here is an example( 4 blocks):
NR ,PREV_BLOCK
====================
27312, 224171
51731, 200423
59732, 213132
87436, 207031
7619237, 207031
8062353, 213132
8278680, 200423
8286682, 224171
4)Is there any option which might be responsible for this(initrans) ?
thank you again.
Basil
Followup September 28, 2011 - 12pm Central time zone:
I would think you would want to associate row_number() over (order by rowid) to the inner query to walk the list. It would probably work the way you did it - assuming we are not using a normal index to access the data (assuming a cluster index access).
I've got indeed 65533 rows(chained blocks) - but there were only 33301 distinct
blocks.
I didn't understand that - how do you know you have 65,533 again?
You don't tell us what these rows look like. do they look truly just like your example above?
do you have a simple script to generate data to reproduce the issue with?
B-Tree cluster size - chained blocks for a key
September 29, 2011 - 7am Central time zone
Reviewer: A reader
Hi Tom,
this cluster is filled with real data. My problem was that I've got the error ORA-02475 for the
mentioned account (so a have a chain which reached 65535 chained blocks) but when I have "measured"
number of blocks with dbms_rowid package I get only 33301 blocks.
This means for me the rest are empty blocks (impossible as no deletes) or the same block is chained
twice.
The output you see is from included statement(with lag) for the first 4 blocks. As you could see
with this statement I measure the number of times I skip to a new block - so I come to 65533
blocks.
Another check I've done was to find the number of rows per block and I found a number which was
double than the "first skip".
For eg. for the next statement
select rownum nr,dbms_rowid.rowid_block_number(rowid) block from cu where account_no=47143978
I get by row 132 a new block :
NR BLOCK
============
27180 224171
27181 224171
............
27311 224171
27312 124432
27313 124432
but the statement:
select count(*) from cu where account_no=47143978 and dbms_rowid.rowid_block_number(rowid)=224171
returns 262 rows.
My only doubt was if I "walk" the chain right.
thank you for your support.
Basil
Followup September 30, 2011 - 11am Central time zone:
you'll have to help me reproduce this issue, I tried:
set echo on
set linesize 1000
drop table cu;
drop cluster cu_clu;
CREATE CLUSTER CU_CLU(ACCOUNT_NO NUMBER(10))PCTUSED 0 PCTFREE 3 SIZE 8K INDEX;
CREATE INDEX CU_CLU_IDX ON CLUSTER CU_CLU;
CREATE TABLE CU(f1 DATE,f2 NUMBER(10),f3 NUMBER(3), ACCOUNT_NO NUMBER(10) ) CLUSTER
CU_CLU(ACCOUNT_NO);
insert into cu select sysdate, 1, 2, object_id from all_objects where object_id > 99;
insert into cu select * from cu;
insert into cu select * from cu;
insert into cu select * from cu;
commit;
declare
cluster_overload exception;
pragma exception_init( cluster_overload, -2475 );
begin
for i in 1 .. 1000000000
loop
begin
insert into cu (f1, f2, f3, account_no ) values (sysdate,mod(i,3),mod(i,3),42);
commit;
exception
when cluster_overload
then
dbms_output.put_line( 'cluster key filled at ' || i );
exit;
end;
end loop;
end;
/
select count(distinct dbms_rowid.rowid_block_number(rowid)) from cu where account_no = 42;
but it "worked"
ops$tkyte%ORA11GR2> select count(distinct dbms_rowid.rowid_block_number(rowid)) from cu where
account_no = 42;
COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
65534
ops$tkyte%ORA11GR2> select count(*) from cu where account_no = 42;
COUNT(*)
----------
26410202
B-Tree cluster size - chained blocks for a key
October 6, 2011 - 11am Central time zone
Reviewer: Basil
Hi Tom,
I have managed to reproduce the problem with generated data. Looks not very elegant but because I
don't know why this happen I've tried to keep it as close to initial configuration as possibe. The
number of "double chained" blocks is also not as big as into initial case but still exist.(there
are 8160 double blocks)
v$version - BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit(HP-UX)
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
CREATE CLUSTER CU_CLU(ACCOUNT_NO NUMBER(10))
PCTUSED 0
PCTFREE 3
INITRANS 3
SIZE 8K
INDEX
NOROWDEPENDENCIES
NOCACHE
NOPARALLEL;
CREATE INDEX CU_CLU_IDX ON CLUSTER CU_CLU
PCTFREE 50
INITRANS 5
MAXTRANS 255
NOPARALLEL;
CREATE TABLE CU
(
THE_DATA_PARTITION_KEY DATE,
KEY_ID NUMBER(10),
KEY_ID2 NUMBER(3),
ACT_ID_SERV NUMBER(3),
GRIFT_ROW_NUM NUMBER(3),
ACCOUNT_NO NUMBER(10),
ACTSCR_NO NUMBER(10),
ACTSCR_NO_RESETS NUMBER(6),
MESG_ID_USG NUMBER(10),
TRANS_DT DATE,
NAME_DT DATE
)
CLUSTER CU_CLU(ACCOUNT_NO)
NOCOMPRESS ;
insert into cu
select trunc(sysdate),242906497,3,2,1,47143978 as account_no,48613975,0,32478,sysdate,sysdate
from dual connect by rownum<=8582160
union all
select trunc(sysdate),242906497,3,2,1,47143948 as account_no,48613975,0,32478,sysdate,sysdate
from dual connect by rownum<=300000
order by account_no;
commit;
-- this should already raise the error ORA-02475
insert into arbor.cu
select trunc(sysdate),242906497,3,2,1,47143978 as account_no,48613975,0,32478,sysdate,sysdate
from dual connect by rownum<=10000
--next statement return 57353
select count(distinct dbms_rowid.rowid_block_number(rowid)) from arbor.cu a where
account_no=47143978
--next statement return 65512 - very close to my 65534 blocks
select count(*) from
(
select nr, prev_block
from
(
select nr, lag(block,1) over (order by nr) prev_block,block
from
(
select rownum nr,dbms_rowid.rowid_block_number(rowid) block from arbor.cu where
account_no=47143978
)
)
where prev_block!=block
)
Looking forward for your comments.
beside dbms_rowid, any other way to find which file a given extent/segment belongs to?
January 12, 2012 - 9pm Central time zone
Reviewer: A reader
Hi Tom,
I am trying to understand the relationship between tablespace,datafile,segment,extent well.
As we know:
1> 1 tablespace can have many datafiles
i can see this select * from dba_data_files where tablespace='USER'
2> 1 segment can only exists inside 1 single tablespace
can understand this by the syntax below
create table ... tablespace USER;
create index ... tablespace INDTS;
3> so 1 segment can spread into more than one datafiles
i want to prove it by query some data dictionary, but can not figure out how
there is no fileno or something clue inside the dba/user_segments
please help?
4> 1 segment is composed of many extents, can understand this
5> 1 extent can only inside one datafile
again, there is no clue inside the dba/user_extents, please help?
BTW, i know dbms_rowid can find the relative FILENO for a row, then we can know above info.
I am just curious whether there is any dictionary can prove above concept? Or it is just desired
so and only way is using dbms_rowid?
Followup January 13, 2012 - 9am Central time zone:
dba_extents has the file_id. you can query dba_extents to see what segments have data in more than one file. something like:
select owner, segment_name from dba_extents group by owner, segment_name, partition_name, segment_type having count(distinct file_id) > 1
|