quote:
You want the number of freelists in general to be close to the number of concurrent insert|update sessions you think will be happening on a table|index(upto some reasonable number -- say 20).
You would like the initrans to be the number of sessions you believe will be concurrently accessing a given BLOCK within a table.

I am confused here, doesnt number of concurrent insert|update sessions equal to the number of sessions accessing a given BLOCK?

how can we determine which table/index is causing data block waits..?

hi

what is the difference between contention of data block waits and freelist waits in v$waitstat??

Hi Tom,
I am using the version 8.1.5.
I want to change the freelists of one of my tables.
The thing is that table is partitioned table.
So I can not use "alter table move tablespace" option for it. What do I need to do so that I can change its freelists.
Can you give me options and steps ?

Thank You
Ravi

Tom,

The output of the query suggested returned the following information in our environment.

NAME COUNT
-------------------- --------

/orbicore/oradata/or 133
bicore/hist01.dbf

/orbicore/oradata/or 46
bicore/idx02.dbf

/orbicore/oradata/or 34
bicore/idx03.dbf


How to drill down further and find out the correct objects?
Can you please help me out as this is often an issue for us.

Thanks and Regards,
Venkat

Tom,

If the table has , say 5 indices, should they also have the same number of freelists? if not, would increasing the freelists on just the table have any effect?

Hi Tom,

Good Input. A reader has asked how to find out whether which table or index is waiting for freelist or initrans. I have a query which is helpful in finding out waits on an objects. The query is

select e.event, substr(s.segment_name,1,30) object_name,
substr(s.segment_type,1,30) object_type,
substr(f.file_name,17,20) datafile_name
from dba_extents s, dba_data_files f,
(select substr(event,1,30) event, p1, p2 from v$session_wait
where event = 'buffer busy waits') e
where s.file_id = e.p1
and e.p2 between s.block_id and s.block_id+s.blocks-1
and s.file_id = f.file_id;

I think this query should work. Am I correct ? Correct me if I am wrong.

Thanks & Regards
Vivek

"If the table has , say 5 indices, should they also have the same number of
freelists? if not, would increasing the freelists on just the table have any
effect?
"
I think the reader meant, if the table has 10 freelists then should the indexes on the table also, have 10 freelists each or should they be left at the default.

I think it is "yes", the indexes on the table should have the same number of freelists as we have for the table. Since an insert or an update will probably do the same operation on the index.

Thanks

I meant, if there is lot of contention on the table say "x" and the freelists are increased, should all the indices of "x" also have to have the same number of "freelists".

I understand, there is no relation between number of indices and freelists.

hello Sir..

i create a table with 1 initrans and maxtrans 1
.. but i m able to
update this table from two session concurrently..
mean half rows from one session and rest from other.

sir where i m wrong to understand the effect of maxtrans..
.. pls explain ..

thanx Sir

Dear Tom,

DB Version 8.1.7, Solaris.

We are in the process of moving from DMT to LMT(to take advantages of LMT) and the same time trying to "find out"/"fine tune" the storage clause parameters as well.

In order to find out the freelists contention, i did execute the following SQL query during peak hrs and getting "no records selected",

1)Meaning no object is waiting for freelists. Am i correct?

2)If so i don't need to change FREELISTS and INITRANS parameter, Correct? Please let me whether i'm right or wrong?

select e.event, substr(s.segment_name,1,30) object_name,
substr(s.segment_type,1,30) object_type,
substr(f.file_name,17,20) datafile_name
from dba_extents s, dba_data_files f,
(select substr(event,1,30) event, p1, p2 from v$session_wait
where event = 'buffer busy waits') e
where s.file_id = e.p1
and e.p2 between s.block_id and s.block_id+s.blocks-1
and s.file_id = f.file_id;

Expecting your valuable comments as always..

Dear tom,
The query
SELECT substr(name,1,50), count
FROM x$kcbfwait, v$datafile
WHERE indx + 1 = file#
and count>0
order by count desc

resulted in following output

SUBSTR(NAME,1,50) COUNT
-------------------------------------------------- ----------
/ofin6/app/proddata/applsysd01.dbf 513
/ofin7/app/proddata/applsysx03.dbf 167
/ofin7/app/proddata/applsysx02.dbf 150
/ofin6/app/proddata/rbs02.dbf 134
/ofin6/app/proddata/apd01.dbf 116
/ofin7/app/proddata/applsysx01.dbf 100
/ofin6/app/proddata/rbs03.dbf 88
/ofin6/app/proddata/applsysd02.dbf 81
/ofin6/app/proddata/rbs01.dbf 71
/ofin6/app/proddata/applsysd03.dbf 69
/ofin7/app/proddata/applsysd04.dbf 61
/ofin6/app/proddata/system03.dbf 12
/ofin6/app/proddata/system05.dbf 7
/ofin6/app/proddata/system01.dbf 2
/ofin6/app/proddata/axd01.dbf 1
/ofin6/app/proddata/igid01.dbf 1

The count for some of the files is blow 5 is there a need of worry???

Waiting for a quick response...

tom, is it a fair statement to say that number of freelists correspond to number of cpus? I mean on a single cpu system, defining a table with more than one freelist does not help the concurrent transactions inserting. Thanks.

Hi

Since we dont have pctused in index segments what#'s the use of freelists in index?

Tom,

For a particular segment, oracle is always inserting into one particular extent (not much deletes in the table lets say) correct?, thus is there some way to make inserts round robin between all available extents.

To clarify what I am looking for. Lets say I have a very busy table T, in tablespace TBSP

TBSP is made up of

/d01/tbsp1.dbf
/d02/tbsp2.dbf
/d03/tbsp3.dbf
/d04/tbsp4.dbf

Thus all I/O will go to one disk at any point in time for inserts (assuming there is not much deletes). If however, I have preallocated one extent in each file, can oracle use all of them such that some users inserts end up in d01 some in d02 etc?

Thank you

I didn't mean to say freelist is avoided. I was trying to word question such that can contention for freelist reduce and/or concurrency on the block reduce

Thank you

Hi

How is freelist controlled in index if there are no pctused...? In tables a block goes to freelist when it's usage drops below pctused how about index?

Tom,

I am not understanding per earlier discussion how to interpret following.
Can you please tell why data was inserted from one extent to next in
very orderly fashion. Although, I admit I didn't give any freelist clause

This 9.2.0.3 database

Thank you


create tablespace testtablesp datafile '/db01/testtablesp.dbf' size 10m
extent management local uniform size 40k;

create table t tablespace testtablesp storage (minextents 254)
as select * from dba_objects where 1=2;                                       

alter table t add (rn number);

insert into t select d.*,rownum from dba_objects d;

commit;

set linesize 255
column to_block format a30

SQL> l                                                    
  1  select extent_id,block_id from_block,                           
  2         '--> '||(block_id+blocks-1)|| '( '||blocks||' )' to_block
  3  from dba_extents where segment_name='T' and segment_type='TABLE'
  4* order by 1,2                                                    
SQL> /                                      
                                                       
 EXTENT_ID FROM_BLOCK TO_BLOCK                         
---------- ---------- ------------------------------   
         0          9 --> 13( 5 )                      
         1         14 --> 18( 5 )                      
         2         19 --> 23( 5 )                      
         3         24 --> 28( 5 )                      
         4         29 --> 33( 5 )                      
         5         34 --> 38( 5 )                      
         6         39 --> 43( 5 )                      
         7         44 --> 48( 5 )                      
         8         49 --> 53( 5 )                      
         9         54 --> 58( 5 )                      
        10         59 --> 63( 5 )                      
        11         64 --> 68( 5 )                      
        12         69 --> 73( 5 )                      
        13         74 --> 78( 5 )                      
        14         79 --> 83( 5 )                      
        15         84 --> 88( 5 )                      
        16         89 --> 93( 5 )                      
        17         94 --> 98( 5 )                      
        18         99 --> 103( 5 )                     
        19        104 --> 108( 5 )                     
        20        109 --> 113( 5 )                     
        21        114 --> 118( 5 )                     
        22        119 --> 123( 5 )                     
        23        124 --> 128( 5 )                     
        24        129 --> 133( 5 )                     
        25        134 --> 138( 5 )                     
        26        139 --> 143( 5 )                     
        27        144 --> 148( 5 )                     
        28        149 --> 153( 5 )                     
        29        154 --> 158( 5 )                     
        30        159 --> 163( 5 )                     
        31        164 --> 168( 5 )                     
        32        169 --> 173( 5 )                     
        33        174 --> 178( 5 )                     
        34        179 --> 183( 5 )                     
        35        184 --> 188( 5 )                     
        36        189 --> 193( 5 )                     
        37        194 --> 198( 5 )                     
        38        199 --> 203( 5 )                     
        39        204 --> 208( 5 )                     
        40        209 --> 213( 5 )                     
        41        214 --> 218( 5 )                     
        42        219 --> 223( 5 )                     
        43        224 --> 228( 5 )                     
        44        229 --> 233( 5 )                     
        45        234 --> 238( 5 )                     
        46        239 --> 243( 5 )                     
        47        244 --> 248( 5 )                     
        48        249 --> 253( 5 )                     
        49        254 --> 258( 5 )                     
        50        259 --> 263( 5 )                     
        51        264 --> 268( 5 )                     
        52        269 --> 273( 5 )                     
        53        274 --> 278( 5 )                     
        54        279 --> 283( 5 )                     
        55        284 --> 288( 5 )                     
        56        289 --> 293( 5 )                     
        57        294 --> 298( 5 )                     
        58        299 --> 303( 5 )                     
        59        304 --> 308( 5 )                     
        60        309 --> 313( 5 )                     
        61        314 --> 318( 5 )                     
        62        319 --> 323( 5 )                     
        63        324 --> 328( 5 )                     
        64        329 --> 333( 5 )                     
        65        334 --> 338( 5 )                     
        66        339 --> 343( 5 )                     
        67        344 --> 348( 5 )                     
        68        349 --> 353( 5 )                     
        69        354 --> 358( 5 )                     
        70        359 --> 363( 5 )                     
        71        364 --> 368( 5 )                     
        72        369 --> 373( 5 )                     
        73        374 --> 378( 5 )                     
        74        379 --> 383( 5 )                     
        75        384 --> 388( 5 )                     
        76        389 --> 393( 5 )                     
        77        394 --> 398( 5 )                     
        78        399 --> 403( 5 )                     
        79        404 --> 408( 5 )                     
        80        409 --> 413( 5 )                     
        81        414 --> 418( 5 )                     
        82        419 --> 423( 5 )                     
        83        424 --> 428( 5 )                     
        84        429 --> 433( 5 )                     
        85        434 --> 438( 5 )                     
        86        439 --> 443( 5 )                     
        87        444 --> 448( 5 )                     
        88        449 --> 453( 5 )                     
        89        454 --> 458( 5 )                     
        90        459 --> 463( 5 )                     
        91        464 --> 468( 5 )                     
        92        469 --> 473( 5 )                     
        93        474 --> 478( 5 )                     
        94        479 --> 483( 5 )                     
        95        484 --> 488( 5 )                     
        96        489 --> 493( 5 )                     
        97        494 --> 498( 5 )                     
        98        499 --> 503( 5 )                     
        99        504 --> 508( 5 )                     
       100        509 --> 513( 5 )                     
       101        514 --> 518( 5 )                     
       102        519 --> 523( 5 )                     
       103        524 --> 528( 5 )                     
       104        529 --> 533( 5 )                     
       105        534 --> 538( 5 )                     
       106        539 --> 543( 5 )                     
       107        544 --> 548( 5 )                     
       108        549 --> 553( 5 )                     
       109        554 --> 558( 5 )                     
       110        559 --> 563( 5 )                     
       111        564 --> 568( 5 )                     
       112        569 --> 573( 5 )                     
       113        574 --> 578( 5 )                     
       114        579 --> 583( 5 )                     
       115        584 --> 588( 5 )                     
       116        589 --> 593( 5 )                     
       117        594 --> 598( 5 )                     
       118        599 --> 603( 5 )                     
       119        604 --> 608( 5 )                     
       120        609 --> 613( 5 )                     
       121        614 --> 618( 5 )                     
       122        619 --> 623( 5 )                     
       123        624 --> 628( 5 )                     
       124        629 --> 633( 5 )                     
       125        634 --> 638( 5 )                     
       126        639 --> 643( 5 )                     
       127        644 --> 648( 5 )                     
       128        649 --> 653( 5 )                     
       129        654 --> 658( 5 )                     
       130        659 --> 663( 5 )                     
       131        664 --> 668( 5 )                     
       132        669 --> 673( 5 )                     
       133        674 --> 678( 5 )                     
       134        679 --> 683( 5 )                     
       135        684 --> 688( 5 )                     
       136        689 --> 693( 5 )                     
       137        694 --> 698( 5 )                     
       138        699 --> 703( 5 )                     
       139        704 --> 708( 5 )                     
       140        709 --> 713( 5 )                     
       141        714 --> 718( 5 )                     
       142        719 --> 723( 5 )                     
       143        724 --> 728( 5 )                     
       144        729 --> 733( 5 )                     
       145        734 --> 738( 5 )                     
       146        739 --> 743( 5 )                     
       147        744 --> 748( 5 )                     
       148        749 --> 753( 5 )                     
       149        754 --> 758( 5 )                     
       150        759 --> 763( 5 )                     
       151        764 --> 768( 5 )                     
       152        769 --> 773( 5 )                     
       153        774 --> 778( 5 )                     
       154        779 --> 783( 5 )                     
       155        784 --> 788( 5 )                     
       156        789 --> 793( 5 )                     
       157        794 --> 798( 5 )                     
       158        799 --> 803( 5 )                     
       159        804 --> 808( 5 )                     
       160        809 --> 813( 5 )                     
       161        814 --> 818( 5 )                     
       162        819 --> 823( 5 )                     
       163        824 --> 828( 5 )                     
       164        829 --> 833( 5 )                     
       165        834 --> 838( 5 )                     
       166        839 --> 843( 5 )                     
       167        844 --> 848( 5 )                     
       168        849 --> 853( 5 )                     
       169        854 --> 858( 5 )                     
       170        859 --> 863( 5 )                     
       171        864 --> 868( 5 )                     
       172        869 --> 873( 5 )                     
       173        874 --> 878( 5 )                     
       174        879 --> 883( 5 )                     
       175        884 --> 888( 5 )                     
       176        889 --> 893( 5 )                     
       177        894 --> 898( 5 )                     
       178        899 --> 903( 5 )                     
       179        904 --> 908( 5 )                     
       180        909 --> 913( 5 )                     
       181        914 --> 918( 5 )                     
       182        919 --> 923( 5 )                     
       183        924 --> 928( 5 )                     
       184        929 --> 933( 5 )                     
       185        934 --> 938( 5 )                     
       186        939 --> 943( 5 )                     
       187        944 --> 948( 5 )                     
       188        949 --> 953( 5 )                     
       189        954 --> 958( 5 )                     
       190        959 --> 963( 5 )                     
       191        964 --> 968( 5 )                     
       192        969 --> 973( 5 )                     
       193        974 --> 978( 5 )                     
       194        979 --> 983( 5 )                     
       195        984 --> 988( 5 )                     
       196        989 --> 993( 5 )                     
       197        994 --> 998( 5 )                     
       198        999 --> 1003( 5 )                    
       199       1004 --> 1008( 5 )                    
       200       1009 --> 1013( 5 )                    
       201       1014 --> 1018( 5 )                    
       202       1019 --> 1023( 5 )                    
       203       1024 --> 1028( 5 )                    
       204       1029 --> 1033( 5 )                    
       205       1034 --> 1038( 5 )                    
       206       1039 --> 1043( 5 )                    
       207       1044 --> 1048( 5 )                    
       208       1049 --> 1053( 5 )                    
       209       1054 --> 1058( 5 )                    
       210       1059 --> 1063( 5 )                    
       211       1064 --> 1068( 5 )                    
       212       1069 --> 1073( 5 )                    
       213       1074 --> 1078( 5 )                    
       214       1079 --> 1083( 5 )                    
       215       1084 --> 1088( 5 )                    
       216       1089 --> 1093( 5 )                    
       217       1094 --> 1098( 5 )                    
       218       1099 --> 1103( 5 )                    
       219       1104 --> 1108( 5 )                    
       220       1109 --> 1113( 5 )                    
       221       1114 --> 1118( 5 )                    
       222       1119 --> 1123( 5 )                    
       223       1124 --> 1128( 5 )                    
       224       1129 --> 1133( 5 )                    
       225       1134 --> 1138( 5 )                    
       226       1139 --> 1143( 5 )                    
       227       1144 --> 1148( 5 )                    
       228       1149 --> 1153( 5 )                    
       229       1154 --> 1158( 5 )                    
       230       1159 --> 1163( 5 )                    
       231       1164 --> 1168( 5 )                    
       232       1169 --> 1173( 5 )                    
       233       1174 --> 1178( 5 )                    
       234       1179 --> 1183( 5 )                    
       235       1184 --> 1188( 5 )                    
       236       1189 --> 1193( 5 )                    
       237       1194 --> 1198( 5 )                    
       238       1199 --> 1203( 5 )                    
       239       1204 --> 1208( 5 )                    
       240       1209 --> 1213( 5 )                    
       241       1214 --> 1218( 5 )                    
       242       1219 --> 1223( 5 )                    
       243       1224 --> 1228( 5 )                    
       244       1229 --> 1233( 5 )                    
       245       1234 --> 1238( 5 )                    
       246       1239 --> 1243( 5 )                    
       247       1244 --> 1248( 5 )                    
       248       1249 --> 1253( 5 )                    
       249       1254 --> 1258( 5 )                    
       250       1259 --> 1263( 5 )                    
       251       1264 --> 1268( 5 )                    
       252       1269 --> 1273( 5 )                    
       253       1274 --> 1278( 5 )                    
                                                       
254 rows selected.                                     
                                                       



SQL> set pagesize 20                                                
SQL> set pause on                                                   
SQL> select rowid,rn from t order by rn                             
/                                                                              
  2                                                                            
                                                                               
ROWID                      RN                                                  
------------------ ----------                                                  
AACHiFANGAAAAAKAAA          1                                                  
AACHiFANGAAAAAKAAB          2                                                  
AACHiFANGAAAAAKAAC          3                                                  
AACHiFANGAAAAAKAAD          4                                                  
AACHiFANGAAAAAKAAE          5                                                  
AACHiFANGAAAAAKAAF          6                                                  
AACHiFANGAAAAAKAAG          7                                                  
AACHiFANGAAAAAKAAH          8                                                  
AACHiFANGAAAAAKAAI          9                                                  
AACHiFANGAAAAAKAAJ         10                                                  
AACHiFANGAAAAAKAAK         11                                                  
AACHiFANGAAAAAKAAL         12                                                  
AACHiFANGAAAAAKAAM         13                                                  
AACHiFANGAAAAAKAAN         14                                                  
AACHiFANGAAAAAKAAO         15                                                  
AACHiFANGAAAAAKAAP         16                                                  
AACHiFANGAAAAAKAAQ         17                                                  
                                                                               
                                                                               
ROWID                      RN                                                  
------------------ ----------                                                  
AACHiFANGAAAAAKAAR         18                                                  
AACHiFANGAAAAAKAAS         19                                                  
AACHiFANGAAAAAKAAT         20                                                  
AACHiFANGAAAAAKAAU         21                                                  
AACHiFANGAAAAAKAAV         22                                                  
AACHiFANGAAAAAKAAW         23                                                  
AACHiFANGAAAAAKAAX         24                                                  
AACHiFANGAAAAAKAAY         25                                                  
AACHiFANGAAAAAKAAZ         26                                                  
AACHiFANGAAAAAKAAa         27                                                  
AACHiFANGAAAAAKAAb         28                                                  
AACHiFANGAAAAAKAAc         29                                                  
AACHiFANGAAAAAKAAd         30                                                  
AACHiFANGAAAAAKAAe         31                                                  
AACHiFANGAAAAAKAAf         32                                                  
AACHiFANGAAAAAKAAg         33                                                  
AACHiFANGAAAAAKAAh         34                                                  
                                                                               
                                                                               
ROWID                      RN                                                  
------------------ ----------                                                  
AACHiFANGAAAAAKAAi         35                                                  
AACHiFANGAAAAAKAAj         36                                                  
AACHiFANGAAAAAKAAk         37                                                  
AACHiFANGAAAAAKAAl         38                                                  
AACHiFANGAAAAAKAAm         39                                                  
AACHiFANGAAAAAKAAn         40                                                  
AACHiFANGAAAAAKAAo         41                                                  
AACHiFANGAAAAAKAAp         42                                                  
AACHiFANGAAAAAKAAq         43                                                  
AACHiFANGAAAAAKAAr         44                                                  
AACHiFANGAAAAAKAAs         45                                                  
AACHiFANGAAAAAKAAt         46                                                  
AACHiFANGAAAAAKAAu         47                                                  
AACHiFANGAAAAAKAAv         48                                                  
AACHiFANGAAAAAKAAw         49                                                  
AACHiFANGAAAAAKAAx         50                                                  
AACHiFANGAAAAAKAAy         51                                                  
                                                                               
^C

  1  select dbms_rowid.ROWID_BLOCK_NUMBER(rowid), min(rn), max(rn) from      
  2  (select rn,rowid from t order by rn)                                    
  3* group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid)                           
SQL> /                                                            
                                                                             
                                                                             
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)    MIN(RN)    MAX(RN)                   
------------------------------------ ---------- ----------                   
                                  10          1         80                   
                                  11         81        159                   
                                  12        160        237                   
                                  13        238        316                   
                                  14        317        396                   
                                  15        397        475                   
                                  16        476        553                   
                                  17        554        632                   
                                  18        633        709                   
                                  19        710        783                   
                                  20        784        858                   
                                  21        859        933                   
                                  22        934       1010                   
                                  23       1011       1084                   
                                  24       1085       1160                   
                                  25       1161       1240                   
                                  26       1241       1324                   
                                                                             
                                                                             
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)    MIN(RN)    MAX(RN)                   
------------------------------------ ---------- ----------                   
                                  27       1325       1411                   
                                  28       1412       1500                   
                                  29       1501       1588                   
                                  30       1589       1676                   
                                  31       1677       1759                   
                                  32       1760       1839                   
                                  33       1840       1919                   
                                  34       1920       1998                   
                                  35       1999       2080                   
                                  36       2081       2160                   
                                  37       2161       2241                   
                                  38       2242       2320                   
                                  39       2321       2402                   
                                  40       2403       2482                   
                                  41       2483       2561                   
                                  42       2562       2640                   
                                  43       2641       2720                   
^C                                                                              

Was it due to fact I had just one datafile that extents got used in orderly manner?

Thanks

Hi

I read in Practical Oracle 8i by Jonathan Lewis that hash partitioning can reduce buffer busy waits. I dont see how... do you?

You may well have to globally range partition other indexes on this table -- to avoid hurting your retrievals -- but they'll benefit from being chopped up as well if you have this really busy table.


Hmm range partitioned also benefits? I can see Hash partition indexes benefits from hot index block but range partitioned... how?

Hi,
You mentioned in response to the first question that block contention can be reduced by increasing the freelist.
My question is how ??
since the free list is stored in the segment header, it makes sense that increasing free list would help segment header contenetion but how would it help block contention ?

Hi,

What do you mean by you would wait for one onother ??

Once the process has got the block from the free list it would release the lock on segment header block so that next process can look for a free block.

Hence 2nd process would register only a segment header wait not a data block wait .

I know I am wrong somewhere could you please point where ??

Moreover suppose a the free list has 3 block 1-2-3 .
Now process 1 gets block 2 for insert a releases the lock on segment header. Would process 2 also get block 2 since block 2 would be still on the free list (it would be removed from free list only when process 1's insert completes and if the free space hase gone below pctfree)

Hi Tom,

Here is scenario. I have a table T1 on which some set of records have to be processed. I separate PK ID's column of this records for processing into 4 groups and each ID appears only ones in one of these groups. I start the procedure P1 4 times for each set of ID's. Procedure P1 is doing some calculation and at the end update record in table - commit is at the end of procedure. I am only one who is processing this table. table Initrans param is set to 4, but I have noticed that this sessions are blocking each other. Also, I have found that they are waiting on data block on update operation. I tried to increase initrans parameter on a table to 8 - blocking, 10 - blocking. If I put commit after every update, data block waits and session blockings are gone.
Any idea what I am doing wrong?

Thanks in advance.

Sven

And I forgot to add that on DB block is enough space for expanding transaction table.

Here is output from v$lock:

select sid, trunc(id1/power(2,16)) rbs,
bitand(id1, to_number('ffff', 'xxxx')) + 0 slot,
id2 seq,
lmode,
request
from v$lock
where 1=1
and v$lock.type = 'TX'
and v$lock.SID in (58,44,28,57)
order by sid;


SID RBS SLOT SEQ LMODE REQUEST
28 1 13 610901 6 0
44 5 0 615589 6 0
44 1 13 610901 0 4
57 10 14 539602 6 0
57 7 23 613021 0 4
58 7 23 613021 6 0
58 10 14 539602 0 4

One more info, there is no bitmap index on a table, only normal b-tree indexes.

I do not have access to trace file (maybe I will manage to get access during this week), but here is what I got from v$session_wait which should delivery similar information like in trace file.

SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
42 8452 enqueue name|mode 1415053316 54580004 id1 393253 60025 id2 628160 00000000000995C0 0 33 WAITING
60 9486 enqueue name|mode 1415053316 54580004 id1 393253 60025 id2 628160 00000000000995C0 0 50 WAITING
38 12788 db file sequential read file# 122 000000000000007A block# 130829 000000000001FF0D blocks 1 1 1 0 WAITED KNOWN TIME
48 10847 db file sequential read file# 120 78 block# 110107 000000000001AE1B blocks 1 1 0 0 WAITING

They are waiting on enqueue and when I analyze parameter P1 from this query:

P1 := 1415053316

select chr(bitand(:p1,-16777216)/16777215)||chr(bitand(p1, 16711680)/65535) "Lock",
bitand(:p1, 65536) "Mode"
from dual;
----------------
Lock, Mode
TX 0


P1 := 1415053316

select chr(bitand(:p1,-16777216)/16777215)||chr(bitand(p1, 16711680)/65535) "Lock",
bitand(:p1, 65536) "Mode"
from dual;
----------------------
Lock, Mode
TX 0

and this is similar what I see in the view v$lock (I posted that earlier).
If this can be of some help to you, fine if not I will try to get trace file. If I don't get them, this is what I have.

Thanks,

Sven

Yes, that I understand, but what I do not understand is why? If you refer to my first post where I have explained the scenario, you will see that each session is processing unique set of ID's (records), which on the other side can be on the same block (and they are). So, one session is updating one row in DB block 1 and no jet commited and the other session is trying to update on the same block some other row and become blocked by the first session. I have increased number of initrans but no help. Only commit can resolve the lock. It seems to me (but really impossible) that lock is on a block level not on a row level. Why, no idea!

So, alter table where I increase a initrans parameter affects only new blocks added to table and the existing one are with the old value. I thought that is also implemented to old blocks. Is this means that also pctfree and pctused on change are implemented on only new blocks not on a old?

When you issue an alter statement, it only affects new extents. From the Oracle documentation:

"When you alter a cluster, index, materialized view, materialized view log, rollback segment, table, or partition, you can change the values of storage parameters. The new values affect only future extent allocations."

- Mark

Thanks a lot Tom!

I read the follow-up too quickly... obviously you are not talking about physical storage parameters... mea culpa.

- Mark

<quotes>
Another option is to use ROWID ranges instead of primary key values. This gives you nice non-overlapping (data wise) ranges.
</quotes>

Could you elaborate that?

snippet from my book "Effective Oracle By Design"

Parallelization

This final, complex example will show you the power of the analytics functions and walk through my thought processes when building a query. The problem we wish to solve is as follows:

o We have a large table.
o We process this table using PL/SQL row by row (employing bulk fetches, of course!).
o We have a large machine with many CPUs and a lot of disk space.
o We find our PL/SQL routine is not very scalable; it uses only a single CPU and doesn't make good use of the entire machine.
o A parallel query won't work because we have an inherently single-threaded process processing the rows.

Enter "do-it-yourself" parallelism. We want to break the table into N nonoverlapping chunks and start N copies of the PL/SQL routine running simultaneously. There are many ways we could do this, such as by using primary key ranges or by using the analytic functions (NTILE would be appropriate; it is useful for "binning" problems like this). But all of these would require either of the following:

o Reading every row via an index (primary key range). This would be very slow and inefficient, with a lot of single-block I/O.
o Each of the N processes reading the entire table and ignoring the rows it is not tasked with processing. This incurs contention for the blocks on disk (everyone is reading them) and causes the PL/SQL routines to fetch N-1 times as much data as necessary.


We can avoid both of those issues using ROWID ranges. In fact, if you watch Oracle do a parallel query (monitoring the active SQL), you'll see that this is exactly how Oracle breaks up the problem. It assigns the parallel query slaves different ranges of a table to full-scan by ROWID ranges. All we need to do is assign different, nonoverlapping (yet covering) ROWID ranges to our PL/SQL routine, and we can achieve our own parallelism.


Our goal is to take the set of extents for a table using DBA_EXTENTS, break it up into N more or less equal-sized portions, and come up with ROWID ranges that cover that in a nonoverlapping fashion. We should be able to support objects that span datafiles. The output from this query will be something like this:

ops$tkyte@ORA920> @split BIG_TABLE_COPY 4

       GRP MIN_RID            MAX_RID
---------- ------------------ ------------------
         0 AAAMgQAAGAAAAAJAAA AAAMgQAAGAAABaICcQ
         1 AAAMgQAAGAAABaJAAA AAAMgQAANAAAA6ICcQ
         2 AAAMgQAANAAAA6JAAA AAAMgQAAOAAAAgICcQ
         3 AAAMgQAAOAAAAgJAAA AAAMgQAAOAAAB8IccQ

That script split the big table into four ROWID ranges (I used BIG_TABLE_COPY, as explained in a moment). We could now start four copies of a PL/SQL routine, each of which would execute this:

select * from big_table_copy where ROWID between l_lo_rid and l_hi_rid;

We would get a query plan much like this:

ops$tkyte@ORA920> select * from big_table_copy 
  2 where ROWID between 'AAAMgQAAGAAAAAJAAA' and 'AAAMgQAAGAAABaICcQ';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE 
          (Cost=2248 Card=4584 Bytes=412560)
   1    0   TABLE ACCESS (BY ROWID RANGE) OF 'BIG_TABLE_COPY' 
            (Cost=2248 Card=4584 Bytes=412560)

This would read all of the records in that ROWID range, with minimal contention and greatest efficiency.

... cut to the chase ...

set linesize 121
set verify off
set echo on
define TNAME=BIG_TABLE
define CHUNKS=5

select grp,
       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
  from (
select distinct grp,
       first_value(relative_fno) over
          (partition by grp order by relative_fno, block_id
           rows between unbounded preceding and unbounded following) lo_fno,
       first_value(block_id    ) over
          (partition by grp order by relative_fno, block_id
           rows between unbounded preceding and unbounded following) lo_block,
       last_value(relative_fno) over
          (partition by grp order by relative_fno, block_id
           rows between unbounded preceding and unbounded following) hi_fno,
       last_value(block_id+blocks-1) over
          (partition by grp order by relative_fno, block_id
           rows between unbounded preceding and unbounded following) hi_block,
       sum(blocks) over (partition by grp) sum_blocks
  from (
select relative_fno, block_id, blocks,
       trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
              (sum(blocks) over ()/&CHUNKS) ) grp
  from dba_extents
 where segment_name = upper('&TNAME') and owner = user order by block_id
       )),
       (select data_object_id from user_objects where object_name = upper('&TNAME') )
/


 

Hi Tom,

How does the freelist contention show in v$system_event - i.e. against what event?

We have a process (call it the 'local DB process') inserting into a table on a remote DB and another process on the remote DB inserting into the same table. The v$waistat would show up only on the remote DB for the contention.

Now after I change the freelists on the remote DB, how do I measure performance improvement (or waits reduction) on the 'local DB process'.

Thanks.

Hi Tom,

Thanks for the previous response.

One other question I have is:

Given a correctly set pctfree value, would updates give freelist contention? meaning, there is sufficient space for updated values on the block where a row resides (when the pctfree is set correctly) - so why would the update look for a new block in the first place?

After reading thus far, I assume one would want to change freelists of a primary key (which really is an index) in addition to changing the freelists of the table itself. I can't seem to do it:

stage@web1> alter table sl_nl_send storage (freelists 10);

Table altered.

stage@web1> alter index sl_nl_send_pk storage (freelists 10);
alter index sl_nl_send_pk storage (freelists 10)
*
ERROR at line 1:
ORA-25176: storage specification not permitted for primary key

Questions:
1) Is it even necessary to change the freelists storage parameter for a primary key if the table itself also got changed?

2) If yes, how do I do it? We are running 8.1.7.4.

drop table t;
alter user ops$tkyte default tablespace manual;
create table t ( x int );
alter table t add constraint t_pk primary key(x);
alter index t_pk storage( freelists 10 );


works in 8i for me -- so.... how did you create t, given that:

[tkyte@localhost tkyte]$ oerr ora 25176
25176, 00000, "storage specification not permitted for primary key"
// *Cause: Storage parameters cannot be defined for a PRIMARY KEY constraint
//         <b>for a table with this organization</b>
// *Action: Remove storage specification for primary key


maybe you have something "different" 

Oops, I forgot to tell you how sl_nl_send was created. It's a range-partition index organized table:

create table sl_nl_send (
partition_key integer not null,
system_list_id integer not null,
indexer integer not null,
member_id integer not null,
constraint sl_nl_send_pk primary key (partition_key, system_list_id, indexer, member_id)
) organization index
compress 2
storage (freelists 10)
partition by range (partition_key) (
partition sl_nl_send_01 values less than (2) tablespace &&sl_partition_ts_1,
partition sl_nl_send_02 values less than (3) tablespace &&sl_partition_ts_2,
partition sl_nl_send_03 values less than (4) tablespace &&sl_partition_ts_3,
partition sl_nl_send_04 values less than (5) tablespace &&sl_partition_ts_4
);

We had the storage freelists 10 parameter in this original DDL, but it never seems to take effect.

stage@O817> select index_name, freelists, freelist_groups from user_indexes where index_name='SL_NL_SEND_PK';

INDEX_NAME FREELISTS FREELIST_GROUPS
------------------------------ ---------- ---------------
SL_NL_SEND_PK

1 row selected.

stage@O817> select table_name, freelists, freelist_groups from user_tables where table_name='SL_NL_SEND';

TABLE_NAME FREELISTS FREELIST_GROUPS
------------------------------ ---------- ---------------
SL_NL_SEND

1 row selected.

I understand that an IOT is both a table and an index, but as you can see in my 2 queries above, both user_tables and user_indexes currently don't register any value for the freelists column (even though I already ran the alter table sl_nl_send storage (freelists10) statement). Thus, I can't tell whether Oracle really knows that I want a freelists of 10 or not. Is there another table/way to tell?

Yep, I found the freelists setting in user_segments. Thanks Tom! By the way, I'm not implying you are not answering my question above... I'm just wondering why user_tables and user_indexes didn't show freelists value.

Hi Tom,

I have the requirement as above, wherein I need to implement the do-it-yourself parallism. I tried the script provided above for 2215445 with 30 chunks, but the number of records between the rowid's are not equally divided. for instance... .


SELECT /*+ FIRST_ROWS */ COUNT(1) , 'group 1' FROM AKV_RB_ACCT A, GRP_TBLS B
WHERE B.GRP_NO=1
AND A.ROWID BETWEEN B.MIN_ROWID AND B.MAX_ROWID
UNION ALL
SELECT /*+ FIRST_ROWS */ COUNT(1) , 'group 2' FROM AKV_RB_ACCT A, GRP_TBLS B
WHERE B.GRP_NO=2
AND A.ROWID BETWEEN B.MIN_ROWID AND B.MAX_ROWID
UNION ALL
SELECT /*+ FIRST_ROWS */ COUNT(1) , 'group 3' FROM AKV_RB_ACCT A, GRP_TBLS B
WHERE B.GRP_NO=3
AND A.ROWID BETWEEN B.MIN_ROWID AND B.MAX_ROWID
UNION ALL
SELECT /*+ FIRST_ROWS */ COUNT(1) , 'group 4' FROM AKV_RB_ACCT A, GRP_TBLS B
WHERE B.GRP_NO=4
AND A.ROWID BETWEEN B.MIN_ROWID AND B.MAX_ROWID

SQL> /

  COUNT(1) 'GROUP1
---------- -------
     60776 group 1
    121568 group 2
     60783 group 3
     54675 group 4

I was expecting that every group should have the same number of records..... Was my assumption wrong???

Regards,
NOTNA
 

Hi Tom,

Thanks for the response. Is there anyway I can achieve the above requirement without any contention the the blocks?

Regards,
NOTNA

(A) In your first book, in the topic of IOT (chapter on Database Tables), you write :
"... data must go where it belong ..... If the data belongs on a given block because of its values, it will go there regardless of how full or empty the block is."

(B) Then, in the same paragraph, at the end, you write :
"The same considerations for the FREELISTs we have on heap organized tables apply in whole to IOT."

Here, I fail to understand how can oracle use more than 1 FREELISTs ?
If two sessions do inserts in the same table, at the same time, using a sequence for the Primary Key, many times both will need to insert in the SAME block. Will FREELISTs > 1 cause a wait ?

But, I believe that, in most of the OLTP transactions, they will mostly go for the same block.

What I want to know is - Can oracle really have more than 1 FreeLists for Index / IOT ? Or, the FreeList clause is only for the name sake ?

If two transations go for the same block - well, they cannot - 1 block on 2 FreeLists ?

Okey - NOT using IOT...
But, an Index for a Primay Key...
OR, for that matter, any index...
Even a NonUniqe Index...

How *can* oracle use the *same* index-block for insertion in parallel by using 2 FreeLists ? (*same* block on *2* FreeLists ?)

Well, I do not know how can we check, for an index, that more than 1 FreeLists are being used.
You are a better person at that.

- An Index-block becomes a free-block when it is comletely empty.
- PctFree is used only while creation of an index.
- Index differs a lot from the hap organization.

I *tend* to believe that Index cannot have freelist > 1.
Of course, this is purely a logical thinking.

I do not visit the internet frequenty; so delay in respose.

I think I am failing to draw your attention to the point.

I am talking about the 2 effects of Index and its FreeLists (one each).

Index Effect : Index puts the index-entries of identical (or even close) values close to each other, irrespective of their values - probably in the same block.

*Multiple* FreeLists effect : *Multiple* FreeLists on the *Index* puts the parallel (in time) index-entries away from each other - probably NOT in the same block.

Both effects are *significantly* *opposite*.

create table t (n1 number);

-- a simple table created

create index t_i1 on t (n1) storage (
FreeLists 2 -- our focal point
);

-- index created
Empty table - so empty index - only initial extents

Now, two transactions execute the *same* following insert statement(s) in parallel (at the same time) :

insert into t values (1); -- 2 trans in parallel

The index t_i1 will *try* to put both the index-entries on the same block (say, block#0).

BUT

The 2 FreeLists will *try* to put both the index-entries on *different* blocks; one will go to block#0 - but the other will go to any block other than block#0 !

the "irrespective of their values" applies to the "FreeLists effect" - By mistake I put that in the "Index Effect"

"You keep presenting a case where obviously they would not be used"

Well, that is agreed by you.

I also understand & agree whatever you say
" - but I keep
trying to tell you they CAN BE used. big difference... "

But - "I don't care if you have
a case where by they would not be used because we are modifying the SAME BLOCK. "

I am not asking about the *purpose* of using the multple freelists on Index - I am asking about the *effects*

Every developer checks *every known* possibilty - One case of failure / problem issue is *not* ignored *knowingly*

Hereby, by developer, I am refering to the systems programmers - the team who made *oracle* software. If they knew that there is a case (which is of course not infrequent), they might have already put the logic just to ignore "FreeLists 2" and make the index with "FreeLists 1".

But, you say,
"It *can be* used."
I wanted to hear,
"It *is* (not) used".

Anyway, thanks a lot for the patience.

Oracle (8.1.7.4) lets us dynamically change the values BUT the manual states that changing the initrans value only effects NEW blocks.

If that is true then ... bummer.

And is it also true of Freelists?

Tom

I "think" I understand what INITRANS does on a table (Increase the initial number of ITLs on each block) but I am not sure what INITRANS does on a Index. Do Indexes also have ITLs? Somehow it doesn't seem to make sense - why would you want to lock an Index.

I had an issue with highly concurrent access on a table which had initrans set to 1 which I managed to fix by an alter table move initrans 10 followed by alter index rebuild on it's indexes. But the indexes still have initrans 2 (not sure why they were not 1 like the table but there you go). Should I rebuild them with initrans 10 as well? I am thinking not - as the issue has been resolved. But I am curious as to when you might need to bump them up - and if so - how.

Regards

Ian

Tom

Sorry to flog a dead horse - but what exactly happens when you lock a row and then update it - and what is the sequence?

Is it - Lock the row - make an update - then Oracle locks any affected index entries (thus taking up Index ITL slots) and makes the required Index updates. Or does the lock propagate out from the row to all it's index entries when the lock is taken? The former seems to make more sense - but "seems" is such a dangerous word. Or is the word "Neither" working it's way into the response?

Also - what's the best way to bump the initrans of existing index blocks?

Thanks and Regards

Ian

you work with indexes in the same fashion as tables -- they are just segments, so, the same technique you use for tables, you use for indexes.  

to bump the initrans of existing blocks requires a rebuild.
to bump the initrans of blocks that will be added to the structure after this point in time requires an alter.

ops$tkyte-ORA9IR2> create table t ( x ) as select rownum from all_users;
Table created.
 
ops$tkyte-ORA9IR2> create index t_idx on t(x);
Index created.
 
ops$tkyte-ORA9IR2> alter index t_idx initrans 5;
Index altered.
 
ops$tkyte-ORA9IR2> alter index t_idx rebuild initrans 20;
Index altered.
 

The enqueue is on the table, the table is "first" and the indexes "second" 

Tom

With regard to bumping the initrans of existing index blocks I am using alter index myindex rebuild initrans 10

Will that do the trick?

Regards

Ian

Tom

I was running several parallel processes against the same tables - one of the processes was showing large enqueue waits and was taking much longer than the other processes. So I increased initrans from 1 to 10 on the tables and the enqueue waits *all but* disappeared. I know for a fact that it cannot be a blocker-blockee situation - all processes have their own subset of data. So I am trying to change the initrans on the indexes from 2 to 10 to see if that is where the enqueues are coming from.

Out of interest - is there any meaning to be gleaned from p2 and p3 of the wait event - I decoded p1 to TX Mode 4

WAIT #91: nam='enqueue' ela= 3000032 p1=1415053316 p2=655395 p3=710257

Thanks and Regards

Ian

Tom

Thanks. Will keep plugging away at it.

Regards

Ian

Tom,

I have written a paper myself regarding space estimation of B-tree indexes. In my first two releases of that paper I mentionned : "a dba should not configure ITL' s anymore if ASSM tablespaces are used". I' m almost 100% sure this is a mistake and I would like to correct it in this way.
...
Since release 9 Oracle encourage us to implement ASSM tablespaces. With ASSM Oracle has introduced a new free block management, a dba should not consider anymore freelists and pct_used. However even with ASSM tablespaces a dba can still configure manually the ITL' s . (with initrans and maxtrans). According to the Oracle documentation 1 ITL slot takes 23 bytes. If more ITL’ s are required then initially created in both table and index blocks Oracle expand dynamically the ITL list assuming there is free space left in the block. As such we are not exactly sure about the number of ITL’ s in an index leaf block.
...
and further in a footnote
...
v$segment_statistics can be queried in order to list segments suffering from ITL_waits (rather uncommon)
...
At the end I will refer to Metalink doc id
Note.247752.1 Oracle9i Space Management Demystified White Paper

May I kindly ask you how you rate the above short explanation of ASSM & ITL (in fact ASSM does not make the itl config obsolete)

May I also assume Mr Tom you have to much to do to read my paper (about 20 pages). If you would be interested to read it anyway, it would be a honour.

Regards
Guy

Its really helpful

Tom

not sure I understand whether freelists have apurpose defined at the "global" level ?

If I have a range partioned table with say 20 partitions,
and I have 20 concurrent inserts into this table, would there be any reason to specify a nondefault freelist value at the table level, as opposed to the partition level ?

Thanks

Syed

"You want the number of freelists in general to be close to the number of
concurrent insert|update sessions you think will be happening on a table|index
(upto some reasonable number -- say 20). "

You state clearly that it matters for updates as well - and the Oracle doc also indicates that is the case. But most of the examples I have seen show freelists in conjunction with "insert" operation. Even in your book (the old Expert One on One) in the last para on page 211 (Wrox edition), you say (being picky here):
"If you anticipate heavy insertion into a table by many users, cnofiguring more than one FREELIST can make a mjor positive impact.."..

Thanx!

"It will be used during updates in the event the update causes the row to become
too large to fit on the block. We need to find a new block to put it on."

That is what I thought too but is not that somewhat rare (typically)? Multiple processes doing updates concurrently that result in row migration?

" And
then there are the indexes on the modified columns as well."

Do you mean freelists on the index storage here?


"
It will definitely affect insertions.
It can definitely affect updates. "

That agrees with what I think. It is of course relevant for inserts. In relatively rare cases, it can improve updates.

Looks like you are enjoying your "hot sos" from your blog!

hi, tom i am dinesh. i requst to u i know some abt oracle
i think u accept my quesitions...
Defnation of alert ? diff types of alrets & wht r we used?
wht is the major role of alerts in oracle apps?
and i know abt major role on cursors and diff types of cursors?

Ok I ran the following

browndd@QC1.WORLD> select * from v$waitstat
2 order by count desc;

CLASS COUNT TIME
------------------ ---------- ----------
undo block 16683850 645232
data block 11985224 12138419
1st level bmb 210080 2343
undo header 55705 3688762

and then ran

SELECT substr(name,1,55) name, to_char(count,'999,999,999,999') count
FROM x$kcbfwait, v$datafile
WHERE indx + 1 = file#

The data block waits are on the following files. And the tablespace parms are using the SEGMENT SPACE MANAGEMENT AUTO. Is there a way to determine if its a freelist problem or an inittrans problem? And using undo I can't realistically add more undo segments ( using auto management ) can I?

NAME COUNT
------------------------------------------------------- ----------------
/oracle/QC1/sapdata3/undo_1/undo.data1 7,259,728
/oracle/QC1/sapdata3/undo_2/undo.data2 7,167,410
/oracle/QC1/sapdata3/undo_3/undo.data3 2,314,006
/oracle/QC1/sapdata1/tcblined_1/tcblined.data1 2,044,521
/oracle/QC1/sapdata2/tcblined_2/tcblined.data2 1,943,651
/oracle/QC1/sapdata3/tcblined_3/tcblined.data3 1,915,449
/oracle/QC1/sapdata4/tcblined_4/tcblined.data4 1,897,114
/oracle/QC1/sapdata5/tcblined_5/tcblined.data5 1,764,376

but v$waitstat is from the beginning of the life of the instance.  do you really have a problem here or is this representing "lots of time".

v$segment_statistics will tell you things like:

ops$tkyte@ORA10GR2> select distinct statistic_name from v$segment_statistics;

STATISTIC_NAME
----------------------------------------------------------------
gc buffer busy
db block changes
space used
segment scans
gc cr blocks received
gc current blocks received
row lock waits
buffer busy waits
physical reads
physical reads direct
physical writes
space allocated
logical reads
physical writes direct
ITL waits


about the segments, segments reside in these datafiles. you might be able to use that.

but I'd rather find out if this is a real problem first :)

 

Yes this instance has been running for about 1 week for these statistics.

browndd@QC1.WORLD> select startup_time from v$instance;

STARTUP_T
---------
07-JUN-06

If you can believe it, the batch process currently is not able to finish in the allotted 10 hours and due to this being a vendor supplied system, lots of proof needs to be supplied about causes. Won't bore you with those details but I did query the v$segment_statistics as suggested and found these details for the one tablespace which holds the worst culprit I feel

OBJECT_NAME TABLESPACE_N STATISTIC_NAME VALUE
--------------- ------------ ------------------------------ ----------------
/CPC/TCBLINE PSAPTCBLINED ITL waits 0
/CPC/TCBLINE PSAPTCBLINED logical reads 966,820,720
/CPC/TCBLINE PSAPTCBLINED physical reads 72,454,955
/CPC/TCBLINE PSAPTCBLINED row lock waits 1
/CPC/TCBLINE PSAPTCBLINED physical writes 5,743,133
/CPC/TCBLINE PSAPTCBLINED db block changes 135,273,664
/CPC/TCBLINE PSAPTCBLINED buffer busy waits 10,838,032
/CPC/TCBLINE PSAPTCBLINED physical reads direct 235,521
/CPC/TCBLINE PSAPTCBLINED physical writes direct 0
/CPC/TCBLINE PSAPTCBLINED global cache cr blocks served 0
/CPC/TCBLINE PSAPTCBLINED global cache current blocks se 0

11 rows selected.
Again this is for 1 week so now I guess I need to watch another week and see what the results are since we don't bounce this database weekly. From these numbers is there a pointer regarding inittrans or freelist problems, pctused, or just miserable index usage due to the high number of logical reads?

Hello Tom,

I am back with the same point after a long time.

During Nov 30, 2004 to Dec 09, 2004
I asked "Can Index have *multiple* FreeLists ?"

That time you said it is possible.
I believed (and argued) that it is not possible.
Today also I believe the same.
I could not convince myself to be convinced by you !

That time I took (gave ?) example of two trnsactions in parallel.
But, this time I am refering to only one - single - transaction;
that too, only one - single - insert statement.

I tried following quite simple steps
on Oracle 8.1.7 (compatiblity 8.0.0 ?!) on Windos/2003.

But, I am presenting it from the (human) memory.
Meaning no copy / paste here.

create table t1 as select * from all_objects;

select count(*) from t1;

-- table created with more than 4400 rows

create index t1_i1 on t1 (object_id)
storage (freelists 50);

-- index created

create index t1_i2 on t1 (object_name)
storage (freelists 50);

-- index created


Now, I executed following insert statement multiple times.
At the end, no. of rows became
more than 9 lacs (i.e., more than 9,00,000)

insert into t1 select * from t1;

...
...
...


select count(*) from t1;


Each and every time,
the insert statement duplicated
all the existing rows in the table.

What is more of my focus / interest is that,
it also duplicated each and every index-entry.

Meaning that, the insert statement was capable to
insert into each and every block in (of) the index-segments.

This is possible only when number of FreeLists is = 1.

In fact, I even doubt that the list being accessed
while updating the blocks in the Index-Segment
can be called a *FreeList*.
The block being accessed need not be really a *Free* block;
It can be a fully packed block !
So, the list may contain entry of the Non-Free blocks as well.

In your books also you mention that PctFree is applicable
only when the index-segment is created; not after that.

To go further, possibly,
Oracle may not be even maintaing /using any list !

Thanks for your support to the Oracle community.
I have learned lot many new ideas
from your 2 (+1 old & new as well) books
and this invaluable web-site.

Tom,

Is there a way to find the optimum number of freelists for a table or an index?? Stated differently, how can one find out the highest water-mark of the number of users, who tried to insert/update a table/block at the same time.

Thanks,
Neeraj

Tom,

Most of my tables are composite partitioned -- with 50 range and 64 hash subparitions -- I have alocated 4 freelists for each of these tables( at the global level only.) But I still see some buffer busy waits( on the file header blocks). Is it that I need to add multiple freelists for all partitions and subpartitions as well??? Which I really doubt, because by definition, these partitions and subpartitions should have their individual freelists. Moreover, it is very less likely that multiple users will be working on the same block of the same hash subpartitions -- at the same time. Please confirm my assumption.

Thanks,
Neeraj

Tom,

Most of my tables are composite partitioned -- with 50 range and 64 hash subparitions -- I have allocated 4 freelists for each of these tables( at the global level only.) But I still see some buffer busy waits( on the segment header blocks). Is it that I need to add multiple freelists for all partitions and subpartitions as well??? Which I really doubt, because by definition, these partitions and subpartitions should have their individual freelists. Moreover, it is very less likely that multiple users will be working on the same block of the same hash subpartitions -- at the same time. Please confirm my assumption.

Thanks,
Neeraj

Tom,

Thanks very much for your response. Yes I did query the data dictionary, and as expected, I just found one freelist for each of these sub-partitions. Also, you were right in saying that it is not about "working on the same block". What I really meant to say was that -- the chances of having multiple users hashing new rows into the same hash-sub partitions were pretty slim -- and for that reason my assumption was that I did not need multiple free lists for each hash sub-partition. Now my question is would it be a better if I add multiple free lists for all of these sub-partitions and their corresponding indexes?? If yes, then what's the one command, which can go ahead and do this change for all these sub-partitions or else do I have to do this 
“Alter table PROPERTIES modify default attributes storage (freelists 4)”

Also thanks for pointing out that this may not be the sole reason for the BUFFER BUSY wait; From what I have read in your other threads -- there are around 8 possible causes  for buffer-busy waits. I am just in the process of narrowing down the possible cause for the wait.


  1   select partition_name,subpartition_name, freelists, freelist_groups
  2        from user_tab_subpartitions
  3*      where table_name = 'PROPERTIES'
10:46:05 ADBPROD@PROD SQL> /

PARTITION_NAME                 SUBPARTITION_NAME               FREELISTS FREELIST_GROUPS
------------------------------ ------------------------------ ---------- ---------------
ALABAMA                        SYS_SUBP119255                          1               1
 .
 .
 .
 .
ALASKA                         SYS_SUBP119328                          1               1
ALASKA                         SYS_SUBP119329                          1               1
ALASKA                         SYS_SUBP119330                          1               1
.
.
.

ALASKA                         SYS_SUBP119382                          1               1
ARIZONA                        SYS_SUBP119383                          1               1
.
.
.
ARIZONA                        SYS_SUBP119395                          1               1
 

ops$tkyte%ORA10GR2> select subpartition_name, freelists, freelist_groups
  2    from user_tab_subpartitions
  3   where table_name = 'T';

SUBPARTITION_NAME               FREELISTS FREELIST_GROUPS
------------------------------ ---------- ---------------
SYS_SUBP647                             1               1
SYS_SUBP648                             1               1
SYS_SUBP649                             1               1
SYS_SUBP650                             1               1
SYS_SUBP651                             1               1
SYS_SUBP652                             1               1

6 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t modify partition junk storage ( freelists 4 );

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select subpartition_name, freelists, freelist_groups
  2    from user_tab_subpartitions
  3   where table_name = 'T';

SUBPARTITION_NAME               FREELISTS FREELIST_GROUPS
------------------------------ ---------- ---------------
SYS_SUBP647                             1               1
SYS_SUBP648                             1               1
SYS_SUBP649                             1               1
SYS_SUBP650                             1               1
SYS_SUBP651                             4               1
SYS_SUBP652                             4               1

6 rows selected.
 

In this article:
</code> http://www.dbazine.com/oracle/or-articles/nanda3 <code>
it is stated that if a single transaction updates multiple rows in the same block, an ITL slot is required for each row being updated.

Q1: Is this assertion correct? I have no doubt that multiple transactions would require multiple slots, but the docs are less clear about the behavior for a single transaction.

I have a table that typically experiences deletes of 100+ rows (single statement) followed by 100+ inserts (multiple statements) in a single transaction. The rows are likely to be colocated in the same block in the primary-key index as they are children of the same parent, and the parent key is the first column of this index. The INITRANS for the index is currently 64, and we are seeing ITL waits on the index. If the answer to Q1 is 'Yes', then I should not be surprised to see ITL waits if we need 100+ (or is it 200+?) ITL slots just for a single transaction, and the index only reserves 64.

I have read (in the Oracle documentation, so I'm not asking for confirmation on this :-)) that if transactions require more ITL slots than are currently available, then more slots are created from the available PCTFREE space. If that is exhausted, then the transaction experiences an ITL wait, until transactions holding the existing ITL slots commit or otherwise free their slots.

Q2: What happens if a transaction goes into an ITL wait, but all of the ITL slots are held by the same transaction that is waiting?

We are at 10.2.0.1.0.

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t
  2  select 1
  3    from all_objects
  4   where rownum <= 660;

660 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select dbms_rowid.rowid_block_number(rowid), count(*)
  2    from t
  3   group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ----------
                                4189        660

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> update t set x = 0;

660 rows updated.


if each row update required an ITL entry of its own, then that update could never work.  There are 255 max, we have 660 rows.

I gave Arups article a quick scan - I did not see the quote you attribute to him, can you be more specific? 

To "Mike from Cleveland" -- Did you check the title of this thread? Is it “ITL slots”??

Why don't people give attention to the topic of the threads? This has a direct implication on the usefulness of that thread, and over the years if this practice is not discouraged, it is going to pollute the entire ASKTOM website.

Neeraj

To "Mike from Cleveland" -- one of my collegues brought another perspective to my attention and made me realize that your question is NOT out of place.. so never mind ..

Neeraj

The statement I am referring to is in the third paragraph:
When the same transaction or another one locks another row, the information is stored in another slot, and so on.

This index has been showing about 10,000 ITL waits per week - 30 times the next-highest index. The stats show an average of only 85 rows per block, so the rows affected by one of these delete/insert transactions would typically be straddling at least two, possibly three blocks. And the application has logic that prevents multiple simultaneous transactions on the same parent.

I do not know how long ago the INITRANS for the index was bumped up to 64, and I strongly doubt that the index was rebuilt since such a change was made. So the majority of the blocks were probably created with a much lower INITRANS.

But even if it was as low as 2 (the default), I have a hard time understanding why we would get so many ITL waits, if a block really can't contain more than 2 loans (parents) and the application rejects multiple simultaneous transactions on the same loan.

Unless, of course, a single transaction would need more than 1 ITL slot in the same block. But your example demonstrates that it's not true (or, at least, not that simple).

ops$tkyte%ORA10GR2> create table t
  2  tablespace manual
  3  as
  4  select rownum id
  5    from all_objects
  6   where rownum <= 500;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          for x in (select rowid rid from t)
  3          loop
  4                  update t set id = id+1 where rowid = x.rid;
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> alter system checkpoint;

System altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> column file_id new_val f
ops$tkyte%ORA10GR2> column block_id new_val b
ops$tkyte%ORA10GR2> select dbms_rowid.rowid_block_number(rowid) block_id,
  2         dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,user,'T') file_id,
  3             count(*)
  4    from t
  5   group by dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,user,'T'), dbms_rowid.rowid_block_number(rowid);

  BLOCK_ID    FILE_ID   COUNT(*)
---------- ---------- ----------
       394         20        500

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter system dump datafile &f block &b;
old   1: alter system dump datafile &f block &b
new   1: alter system dump datafile         20 block        394

System altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure update_it( p_id in number, p_iter in number )
  2  as
  3          pragma autonomous_transaction;
  4  begin
  5          if (p_iter > 20)
  6          then
  7                  return;
  8          end if;
  9          update t set id = id+1 where id = p_id;
 10          update_it(p_id+1,p_iter+1);
 11          commit;
 12  end;
 13  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec update_it( 2, 1 )

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> alter system checkpoint;

System altered.

ops$tkyte%ORA10GR2> alter system dump datafile &f block &b;
old   1: alter system dump datafile &f block &b
new   1: alter system dump datafile         20 block        394

System altered.



Block header dump:  0x05000182
 Object id on Block? Y
 seg/obj: 0x115d6  csc: 0x00.b28488  itc: 3  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00b28482
0x02   0x0006.021.00000c2d  0x0080c31f.0c14.13  --U-  500  fsc 0x0000.00b2848f
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

data_block_dump,data header at 0xcc0f474
===============
tsiz: 0x1f88
hsiz: 0x3fa
pbl: 0x0cc0f474
bdba: 0x05000182
     76543210
flag=--------
ntab=1
nrow=500
frre=-1
fsbo=0x3fa
fseo=0xdb9
avsp=0x9fa
tosp=0x9fa

...... snipped ....

Block header dump:  0x05000182
 Object id on Block? Y
 seg/obj: 0x115d6  csc: 0x00.b284b2  itc: 20  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.010.00000c8e  0x00800a34.0c5b.3d  --U-    1  fsc 0x0000.00b284c7
0x02   0x000c.013.0000005f  0x00800038.009d.33  --U-    1  fsc 0x0000.00b284c6
0x03   0x0009.02a.00000c93  0x0080c6ba.0c86.03  --U-    1  fsc 0x0000.00b284c8
0x04   0x0012.027.0000001c  0x008002b3.004a.07  --U-    1  fsc 0x0000.00b284c5
0x05   0x0010.01e.0000001c  0x00800043.0026.0a  --U-    1  fsc 0x0000.00b284c4
0x06   0x000d.01b.00000062  0x00800053.008c.07  --U-    1  fsc 0x0000.00b284c3
0x07   0x0004.018.00000f3f  0x0080c21e.0d5a.14  --U-    1  fsc 0x0000.00b284c2
0x08   0x0008.02b.00000ea8  0x0080007e.0bd3.1c  --U-    1  fsc 0x0000.00b284c1
0x09   0x0013.019.00000020  0x008006b1.0041.3e  --U-    1  fsc 0x0000.00b284c0
0x0a   0x000b.026.00000062  0x008003ee.00bd.0e  --U-    1  fsc 0x0000.00b284bf
0x0b   0x0001.00c.00000e22  0x008009dc.0a39.0b  --U-    1  fsc 0x0000.00b284be
0x0c   0x0007.024.00000c74  0x008001e6.0c86.38  --U-    1  fsc 0x0000.00b284bd
0x0d   0x0002.013.00000cf0  0x0080001f.0bc4.26  --U-    1  fsc 0x0000.00b284bc
0x0e   0x000e.00e.00000067  0x00800493.00c9.07  --U-    1  fsc 0x0000.00b284bb
0x0f   0x0011.02f.0000001d  0x008000c0.005e.28  --U-    1  fsc 0x0000.00b284ba
0x10   0x0014.000.00000020  0x00800345.0048.05  --U-    1  fsc 0x0000.00b284b9
0x11   0x0006.013.00000c2e  0x0080c31f.0c14.14  --U-    1  fsc 0x0000.00b284b8
0x12   0x000f.023.00000064  0x008000a3.00d1.07  --U-    1  fsc 0x0000.00b284b7
0x13   0x000a.022.00000e08  0x00800065.0b8f.10  --U-    1  fsc 0x0000.00b284b6
0x14   0x0005.013.00000cab  0x0080bda6.0d34.0a  --U-    1  fsc 0x0000.00b284b4

data_block_dump,data header at 0xcc0f60c
===============
tsiz: 0x1df0
hsiz: 0x3fa
pbl: 0x0cc0f60c
bdba: 0x05000182
     76543210
flag=--------
ntab=1
nrow=500
frre=-1
fsbo=0x3fa
fseo=0xc21
avsp=0x862
tosp=0x862


that shows that the ITL list grows by transaction - not by number of rows modified by a single transaction.




as for the 10,000 itl waits - how "long" did we wait (10k sounds big, but is it relevant?) 

Upon further review, we determined that the query that reported 10,000 ITL waits in a week was incorrect. The correct number is about 150. So we do not consider this to be a problem worth pursuing.

For what it's worth, I'm wondering if the waits are not in the leaf blocks - but may be in the branch blocks.

Thank you very much for your assistance and demonstrations - I have certainly learned from this.

This refers to your followup to my review dated
05-July-2006. Since I do not surf the net regularly,
this has taken a long time.

You have given an example that we both insert at the same
time, but different vaules (you insert -9999999 and
I insert 9999999) and succeed "in parallel".

Well, now we want to "test"
(even for the sake of testing) and swith our roles.

Now, you insert 9999999 and I insert -9999999 in our "same"
sessions. Will this also succeed "even in series"
(if not in parallel) ? In fact, first time you used your freelist and allocated a block in that. So, this block is is not available to me in my freelist.

Or, now the freelist is not looked into;
only the LRU list is used now ?

Tom,
  I am in 10.2.0.2. I used below script to simulate itl wait in 9i. While i try to simulate the same in 10.2.0.2 i is going with out any issues. Can you please help me?

create table x ( no number ) initrans 2 maxtrans 2;
insert into x values (1);
insert into x values (2);
insert into x values (3);
commit;
first session,
SQL> update x set no=no where no=1;

1 row updated.
--don't commit
second session,
SQL> update x set no=no where no=2;

1 row updated.
--don't commit

third session,
SQL> update x set no=no where no=3;

third session will hang due to ITL wait(in 9i). Is this different in 10g as maxtrans is deprecated?

Regards,
Satheesh Babu.S
 

Tom,
I tried populating the block full, but not able to simulate the itl wait. Not sure what i am missing. I tried the same example given above, but this time with many more rows.

Regards,
Satheesh Babu.S

Hello,

The ITL section is 24 bytes in size. So, just because a block looks full because it does not have enough space to accept a new row does not necessarily mean that there is not enough space for the ITL to grow. For example, what did you set PCTFREE to when you created the test table? That can leave free space in the block if you did not set it to 0 (zero).

Here is a small example on my 8K blocksize system using system allocated extents and manual segment space management:

drop table itl_test purge;

-- table will be created with 2 ITL entries
create table itl_test (c1 number) pctfree 0;

-- this will 'fill and pack' a single block
begin
for i in 1..734 loop
insert into itl_test values (i);
end loop;
end;
/

commit;

-- gather stats to check number of blocks
exec dbms_stats.gather_table_stats(user,'ITL_TEST');

-- verify table is 1 block
-- not required but just keeps it simple
select blocks from user_tables where table_name='ITL_TEST';

Now, on my system if I create three sessions to update rows such as:

session 1:
update itl_test set c1=1 where c1=1;

session 2:
update itl_test set c1=2 where c1=2;

session 3:
update itl_test set c1=3 where c1=3;

The 3rd session will experience ITL waits.

Not sure if that is helpful at all, but anyway, there you have it.

Corrections, better ideas, etc. always welcome.

- Mark

I am able to simulate the itl wait by setting PCTFREE=0.
Tom/Mark, thanks a lot for the help.

Regards,
Satheesh babu.S

Thanks Tom for your explanation of initrans and its effect on UPDATE, which is illustrated in your book and also this 9i example:

create table x ( no number ) initrans 2 maxtrans 2;
insert into x values (1);
insert into x values (2);
insert into x values (3);
commit;
first session,
SQL> update x set no=no where no=1;

1 row updated.
--don't commit
second session,
SQL> update x set no=no where no=2;

1 row updated.
--don't commit

third session,
SQL> update x set no=no where no=3;

My question is about the effect of initrans on INSERT. If I substitute the 3 UPDATE statements in the 9i example above with 3 INSERT statements, I do not seem to get any problem.

It appears that multiple inserts are counted as one ITL for the purpose of initrans. In other words, I could have 5 sessions doing insert, and 1 session doing an update, and they work fine. In fact, if I change maxtrans to 3 in the above example, then I could have 100 sessions doing insert and 2 sessions doing update without any blocking.

Could you please confirm this behavior? Does that mean inserts from multiple sessions use only one ITL slot?

Thank you!

[quote]
Or, you can try using ASSM( automatic segment space management ) designed to give best overall
concurrent throughput - at the expense of wasting a bit of disk
[/quote]

Tom,
what do you think about this article.
http://www.akadia.com/services/ora_freelists.html

abstract:
even if we have ASSM we need to check for FREELIST and may be in some cases we have to change.

Tom,
you are write, it is my abstract, sorry if I wasn't clear enough, but I will tell where my understanding came from.

in the section
"ASSM Auto with 5 Freelist"
assm_auto tablespace has been created with ASSM option, then the "test" table was created on this tbs using FREELIST storage option. Oracle Docs says that the FREELIST will ignored if ASSM tbs is used.
but the test case that was introduced in this article shows that statspack report
buffer busy waits 5,925 111 10.69

which is different from the prev section
"ASSM Auto with 1 Freelist"
which shows statspack report as follows.
buffer busy waits 5,669 94 9.10

please explanation is highly appreciated

ops$tkyte%ORA9IR2> create table t ( x int ) tablespace tools storage ( freelists 5 );

Table created.

ops$tkyte%ORA9IR2> select freelists from user_tables where table_name = 'T';

 FREELISTS
----------


ops$tkyte%ORA9IR2> drop table t;

Table dropped.

ops$tkyte%ORA9IR2> create table t ( x int ) tablespace system storage ( freelists 5 );

Table created.

ops$tkyte%ORA9IR2> select freelists from user_tables where table_name = 'T';

 FREELISTS
----------
         5

ops$tkyte%ORA9IR2>

The article only says that with ASSM increasing freelists did not have much effect while with manual segment space manegement we could improve TPS a lot by increasing freelist from 1 to 5.

Hi Tom,
Please consider the following case:
Version 8174.
A table XYZ and its indexs have FREELIST and INITRANS set to 10 . FREELIST GROUP=1
I have a shell script that run 5 process at the same time,
and update millions of records in this table.
All the updates are done by rowid. each process have a large set of unique rowids.
I ran 10046 event and found that the most top event is ENQUEUE.
At the beggining I thought that setting freelist to 10 will be enough , but its seems that i am still waiting.
Do you thing that in this case setting FREELIST GROUP to value larger than 1 can help ?
Thanks

Greeting thoams,

--just because you update different sets of rows does not mean you are not waiting on rows.

Please explain this statement, and if i do not have any constraint on the table, Only an index on a one column. does this statement apply to this table also?

thanks

greeting thomas,

does segment header contention cause blocking, in a tablespace that has segment space management auto, in a high active database?

thanx

Tom:
Is it possible to trace a session and figure out that the contention is caused because of setting low value for inittrans? If so please explain how.

Thanks

 desc v$segment_statistics

Tom:
Could you please explain how autosegment space management works? How is it different from manual space management; where DBA has to set pctused, pctfree. Also in autosegment space management, is there any default value of PCT USED set by oracle internally? ( I guess not as I see that oracle ignores pctfree and pctused settings; if not please explain). Is there any performance impact if I opt for auto segment space management? Could you please point to any metalink note as well.

Thanks

Dear Tom,

We are seeing too much of ITL wait (Segment by ITL waits in AWR). Tablespace is ASSM enabled and INITRANS is 5 and MAXTRANS is 255. There are more than 300 session accessing the segment at same time.

Do I have to increase the initrans for table and index? If yes, how much. If no, why?
Kindly clarify. Thanks

Hi Tom,

In AWR report we have seen some ITL waits on some table and indexes in batch process.After checking the table/index init trans is 169.Do we need to increase again i mean set it to 255 ?

Hi Tom,

ITL wait time is 8.

We have total 64 CPU.And have 70 parallel process.Out of which 35 runs in instance 1 and 35 runs in instance 2.


Buffer gets is 7,732,395 as per AWR while calling the functions inside which insert on this table is processed.

Hi Tom,

Thanks a lot for your suggestion.

just to know the case where table initrans is 1 and index initrans is 169 and we are running 70 parallel streams.Is this will cause ITL wait for insert in this table ?????

Hi Tom,

In our database (Oracle 10g) we have multiple user concurrently Inserting/Updating in some tables where statistics are locked.

If we increase the Freelists on these tables, whether Oracle will use those freelists as table statistics are already locked?

Hi Tom,

As I understood it when a table is partitioned each partition maintains its own freelist. If so does that mean that one partition cannot get a block from another if needs to?

For example, table A has 2 partitions(part1 and part2) and both partition are full(i.e. 0 in the freelist). If I delete half of the rows in part2 and I insert some records in part1, part1 has to allocate new blocks.

Hi Tom,

Thanks for that quick reply.

Now let's say you have a range interval partition (say by month). How do you reclaim the space if you delete (a) some or (b)all the records in the partition.

For example, one partition holds data form Jan 1 - Jan 31 and you only deleted from Jan 1 - Jan 15. How do you free up the space? By table reorg? Assume here that no more rows will go into this partition so those freelist will never be used.

And if you delete all the records in a partition (delete all Jan. records) do you have to drop that partition?