freelist groups in RAC
Reader, May 25, 2004 - 6:23 pm UTC
index test allocate extent size 1m instance 2;
I get this error :
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
1.What is wrong with this syntax
2.If it works then is it equivalent of freelist groups ?
3.It means that instance 2 of this cluster can only insert into this extent .
4.The blocks after deletes will never cross the freelist groups which could be an issue .Does ASSM take care of this
automatically ?
Pl Enlighten .
Thanks ,
May 25, 2004 - 8:15 pm UTC
1) we have no commands that start with "index"? assume a missing ALTER, but they you are missing () around the size/instance parameters.
2) it works *with* freelist groups. it only allows the blocks in this extent to be on the freelist group used by instance "2"
3) it means (for an index) that instance 2 will pull the block from the freelist when it needs space, but after that anyone can modify it ('insert' into it) since pctfree is only meaningful to an index during creation and pctused is never meaningful.
4) ASSM is a replacement for trying to fine tune with freelists/freelist groups/pctused and so on.
alter table?
A reader, September 02, 2004 - 2:42 pm UTC
hi tom,
i did alter table t storage (freelists 20);
i got the msg table altered. now how can i cross check the value of freelists ? bcos when i logged in to enterprise manager console, clicked on tables, browsed to the table t and checked under the tab storage the freelists shows as 1. please help.
Thanks.
September 02, 2004 - 3:06 pm UTC
ops$tkyte@ORA9IR2> create table t (x int );
Table created.
ops$tkyte@ORA9IR2> select freelists from user_tables where table_name = 'T';
FREELISTS
----------
1
ops$tkyte@ORA9IR2> alter table t storage ( freelists 20 );
Table altered.
ops$tkyte@ORA9IR2> select freelists from user_tables where table_name = 'T';
FREELISTS
----------
20
freelists
A reader, September 02, 2004 - 3:19 pm UTC
hi tom,
i did
select freelists from user_tables where table_name = 'T';
i got the result as
FREELISTS
---------
What does this mean?
Thanks.
September 02, 2004 - 4:21 pm UTC
means you are using ASSM -- automatic segment space management -- and freelists/pctused are nothing you set
but if you were, you would get:
ops$tkyte@ORA9IR2> alter table t storage ( freelists 20 );
alter table t storage ( freelists 20 )
*
ERROR at line 1:
ORA-10620: Operation not allowed on this segment
soooo, are you sure? can you give us the entire from start to finish example? alter etc...
example
A reader, September 02, 2004 - 4:47 pm UTC
this is what i did
SQL> alter table t storage (freelists 20);
Table altered.
SQL> select freelists from user_tables
2 where table_name = 'T';
FREELISTS
----------
now i tried to just create a new table t1. for that when i did the same select statement i was able to see the value of freelists. so i don't know why for table t i am not able to see the value.
Thanks.
September 02, 2004 - 9:32 pm UTC
ok, is T a partitioned table or something "not just a normal heap"
show us the CREATE TABLE for that.
freelists
A reader, September 03, 2004 - 6:56 am UTC
yes the table is a partitioned table.
if we cannot use freelists for partitioned tables, then how else can we increase the performance???
September 03, 2004 - 11:52 am UTC
you can, the freelists would be on the PARTITIONS themselves.
ops$tkyte@ORA9IR2> CREATE TABLE t
2 (
3 temp_date date,
4 x int,
5 y int
6 )
7 PARTITION BY RANGE (temp_date)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
10 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 ;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select table_name, freelists from user_tables where table_name = 'T';
TABLE_NAME FREELISTS
------------------------------ ----------
T
ops$tkyte@ORA9IR2> select partition_name, freelists from user_tab_partitions where table_name = 'T';
PARTITION_NAME FREELISTS
------------------------------ ----------
PART1 1
PART2 1
JUNK 1
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t storage (freelists 20);
Table altered.
ops$tkyte@ORA9IR2> select table_name, freelists from user_tables where table_name = 'T';
TABLE_NAME FREELISTS
------------------------------ ----------
T
ops$tkyte@ORA9IR2> select partition_name, freelists from user_tab_partitions where table_name = 'T';
PARTITION_NAME FREELISTS
------------------------------ ----------
PART1 20
PART2 20
JUNK 20
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t modify partition part1 storage ( freelists 15 );
Table altered.
ops$tkyte@ORA9IR2> select partition_name, freelists from user_tab_partitions where table_name = 'T';
PARTITION_NAME FREELISTS
------------------------------ ----------
PART1 15
PART2 20
JUNK 20
Thanks Tom
A reader, September 03, 2004 - 12:01 pm UTC
Thanks a lot Tom. It worked.
How to change freelists on a partitioned table
Knut Lundefaret, March 04, 2005 - 9:56 am UTC
how to become paid member of your site
MSSVNG. Narasimham, June 15, 2005 - 7:09 pm UTC
Dear Tom,
I have gone through your answers and I dont find any input from my database. Is there any init parameter we need to set?
SQL> create table t (x int );
Table created.
SQL> select freelists from user_tables where table_name = 'T';
FREELISTS
----------
SQL> SQL>
SQL> set feedback 1
SQL> l
1* select freelists from user_tables where table_name = 'T'
SQL> /
FREELISTS
----------
1 row selected.
SQL> alter table t storage ( freelists 20 );
alter table t storage ( freelists 20 )
*
ERROR at line 1:
ORA-10620: Operation not allowed on this segment
SQL> alter table t move storage ( freelists 4
freelist grou 2 ps 2 );
Table altered.
SQL> select freelists from user_tables where table_name = 'T';
FREELISTS
----------
1 row selected.
SQL>
SQL> select partition_name, freelists from user_tab_partitions
where table_n 2 ame = 'T';
no rows selected
Thank you very much inadvance
June 16, 2005 - 3:37 am UTC
You appear to be using ASSM (automatic segment space management) whereby freelists are not used at all.
ORA-02243
Monika, November 03, 2005 - 3:31 am UTC
Hi Tom,
We were previously rebuilding indexes, in parallel with degree 5.
now we are trying to rebuild index online
Alter index x rebuild online
But it gives the following error
"ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option"
The exact sequence of sql statements are
Alter index x logging
--Alter index x rebuild tablespace y rebuild parallel 5-- we commented this line and added the line below
Alter index x rebuild online
Alter index x logging no parallel
Can you please advise , what is going wrong
November 03, 2005 - 6:57 am UTC
no parallel would be noparallel
ORA-02243
Monika, November 03, 2005 - 8:26 am UTC
sorry for the typo..in the code its noparallel
November 04, 2005 - 2:20 am UTC
then it runs and we would need to see a cut and paste from sqlplus showing it NOT running.
How to change freelists
Kishor B Upasani, December 07, 2006 - 7:13 am UTC
Excellent and got thorough insight on freelist and group alteration
Many many thanx !!!
ASSM
A reader, July 16, 2008 - 8:09 am UTC
Hi Tom,
I have created a table with with 5 freelists. Will freelists parameter help in concurrent inserts when ASSM is enabled on database.
July 16, 2008 - 10:16 am UTC
automatic segment space management (assm) is not enabled on a "database", it is a tablespace thing.
that aside, when you use assm, freelist and freelist groups have no meaning, they are obsolete as are initial, next, pctincrease and pctused (only pctfree has meaning with assm)
Freelist
A reader, July 24, 2008 - 8:17 pm UTC
The following is copied from Oracle SQL Reference:
FREELIST GROUPS
Specify the number of groups of free lists for the database object you are creating. The default and minimum value for this parameter is 1. Oracle uses the instance number of Real Application Clusters instances to map each instance to one free list group.
Each free list group uses one database block. Therefore:
If you do not specify a large enough value for INITIAL to cover the minimum value plus one data block for each free list group, then Oracle increases the value of INITIAL the necessary amount.
If you are creating an object in a uniform locally managed tablespace, and the extent size is not large enough to accommodate the number of freelist groups, then the create operation will fail.
**************
Oracle ignores a setting of FREELIST GROUPS if the tablespace in which the object resides is in automatic segment-space management mode. If you are running your database in this mode, please refer to the FREEPOOLS parameter of the LOB_storage_clause.
**************
But in Oracle Tuning The Definitive Reference, or from:
http://www.dba-oracle.com/oracle_tips_freelists.htm I got: "Of course, if you are using Oracle9i, the best solution is to redefine your tablespace with automatic segment space management (ASSM) and use the wonderful Oracle9i bitmap freelists."
It seems confilict with each other.
I am trying to use FREELISTS and FREELIST GROUPS to create a table, but I could not see freelists and freelist group got set with TOAD table stats/size tab. How do I know if table t was created as expected?
create table t ( x int ) tablespace test2_ts storage ( freelist groups 2 freelists 5 );
There is no value for FREELISTS and FREELIST GROUPS.
Please help!
Thanks.
July 29, 2008 - 9:30 am UTC
why would you ask me about another (not affiliated with Oracle) website? dba-oracle is not associated with Oracle corporation in any way shape or manner.
and then about how to use a tool not made or supported by Oracle?
there are two ways to manage space in Oracle
manual segment space management - you set percent used, freelists and freelist groups. You set these things, you figure out what they should be given your unique situation.
automatic segment space management - we figure out how to manage the space all by ourselves. We manage free space entirely different - there are no conventional freelists, freelist groups - we use what we call "bitmap blocks" in the segment itself to manage free space. Freelist and Freelist groups - along with pctused - are not set by you (if you do set them, we ignore them when using automatic segment space management)
ASSM
Deepak Jain, September 08, 2008 - 3:06 am UTC
Hi Tom,
The above post was really helpful in understanding the freelist and freelist group for ASSM and Manual Segment space management.
What i understood from the above discussion is that the freelist,freelist group parameter does not have any signifiacance in ASSM but they retain their meaning and importance, when they are used with Manual Segment space management.
But i got one query, when there is no meaning of Freelist and freelist group parameter in ASSM, why does it still accept and doesn't report error when we issue storage parameter including freelist and freelist group in ASSM ?
September 08, 2008 - 4:02 pm UTC
... But i got one query, when there is no meaning of Freelist and freelist group
parameter in ASSM, why does it still accept and doesn't report error when we
issue storage parameter including freelist and freelist group in ASSM ?
...
because it would cause too many create scripts to fail - so we just silently "ignore them"