Hi,
in my server stats it gives that pctfree for a table is low
Now how do i find out which table is that which haslow pctfree?
Thanx
Sam
Printable View
Hi,
in my server stats it gives that pctfree for a table is low
Now how do i find out which table is that which haslow pctfree?
Thanx
Sam
Hi,
Check for the tables with more updates in your database.
Angel
How are you getting your server stats ?
Analyze your tables.
select chain_cnt, table_name, owner
from dba_tables
where chain_cnt > 0
If any of your tables have row chaining in them, increase pct_free for table.
search forums on how to resolve row chaining.
If I remember correctly, you will have to copy chained rows to another table.
delete chained rows from table
copy chained rows back to table with higher pct_free setting.
Hi,
I have this table that has 8 varchar2(4000) cols and 1 nmber col
this table has 6000 rows
And after the inserts i found that i has a lottt of chained rows
this table has just inserts and seldom updated so i kept the pctfree as 5 and pctused as 60
but still the when i analyze i find that it has chained rows.
How do i go about with this?
Thanx a lot
Sam
are most of your tables chained ? Increasing your db blocksize may help.
5 were...
increase in the pctfree of 4 worked..but for 1 it dint...
4454 cols out of 6000 rows are chained in this table
Just to remind:Quote:
Originally posted by vinit
are most of your tables chained ? Increasing your db blocksize may help.
Increasing your db blocksize means you have to recreate your db. :-))
so would me increasing the MINEXTENTS and MAXEXTENTS help?
BTW can I alter this?
thanx
Sam
It can't help since chaining/migration issues is in a block level.
Yes, below 9i.Quote:
Just to remind:
Increasing your db blocksize means you have to recreate your db. :-))
For a 9i running instance, you can create a new tablespace with, say, 16K block size:
create tablespace tbs16K
datafile '/ora900/oradata/tbs16K.dbf' size 256M
blocksize 16k;
Well nice to hear from you Julian about that realistic feature of 9i, how about that one they called a self-DBAdministered system, is this true also? they say all you need is to identify your system if it is an OLTP,Quote:
Originally posted by julian
Yes, below 9i.Quote:
Just to remind:
Increasing your db blocksize means you have to recreate your db. :-))
For a 9i running instance, you can create a new tablespace with, say, 16K block size:
create tablespace tbs16K
datafile '/ora900/oradata/tbs16K.dbf' size 256M
blocksize 16k;
DW/Datamart or HYBRID, and 9i system will do the DBA works for the system.
Anyway, thus helpme's db is 9i?
no :(
what is your AVG_ROW_LEN
db_block_size?
Yes, in 9i, you can use OMFs (Oracle Managed Files):Quote:
Well nice to hear from you Julian about that realistic feature of 9i, how about that one they called a self-DBAdministered system, is this true also? they say all you need is to identify your system if it is an OLTP,
DW/Datamart or HYBRID, and 9i system will do the DBA works for the system.
Anyway, thus helpme's db is 9i?
In init.ora set DB_CREATE_FILE_DEST=/oradata/9i/disk7/
Then create the tablespace using the OMF syntax:
create tablespace OMT datafile size 128M;
Then add a datafile to the tablespace via the OMF syntax:
alter tablespace OMT add datafile '=/oradata/9i/disk7/file_4.dbf';
It is not a good idea to name the fiels by yourself, not recommeneded at all.
If you drop the tablespace then the good thing is that all datafiles are removed from the operating system, good isn't it! Up top now, we had to do this manually.
If you have a tablespace ala_bala with both OMF and non-OMF datafiles then you can drop it with:
drop tablespace ala_bala including contents and datafiles;
And so on, and so on...
During the ILT course "Oracle 9i: New features for administraotors" I was told that you should not to use OMFs if you know how to manage non-OMFs.
These new features are meant for DBSs, not for DBAs :-)
[Edited by julian on 11-23-2001 at 06:40 AM]
the avg row len for that table is 4330
and the db block size is 2048 :p
and i suppose i cannot change this..rite cuz mine is 8x :(
Thanx
Sam
This is another INPUT Julian.... thanx alot :-))
I haven't handled 9i yet, I told my boss to wait until next year.
Good idea! Around March Oracle will realease 9.0.2. Probably then is the right time to upgrade production (if needed).Quote:
Originally posted by reydp
This is another INPUT Julian.... thanx alot :-))
I haven't handled 9i yet, I told my boss to wait until next year.
with 2048 as db_block size, that's small !!!
If you can afford to recreate your db, then that is your best option.
and make you db block size to 16k
If not, one option is to modify in the design level:
make that 1 table into 2 table(split)
let's say original table is:
f1 number(4)
f2 varchar2(4000)
f3 varchar2(4000)
f4 varchar2(4000)
f5 varchar2(4000)
and f1 is your primary then:
table 1:
f1 number(4)
f2 varchar2(4000)
f3 varchar2(4000)
table 2:
f1 number(4)
f4 varchar2(4000)
f5 varchar2(4000)
Sorry for the 2nd option, even splitting the table still there will be a problem. since db block size is not even half of the average row length.
I hope this thing would nor really affect affect my db performance..cuz there are quite a lot of select on this table...
Thanx
Well if you have free space to create a test db(or a test server), then you can do the tricks there first. And if everything goes well, then you can apply it to your production in one weekends(if not 24x7 system).
How huge is your db right now?
Well i dont think the real DBA and the sys administartor of my comp would agree to that...
And the database
now i have a diff question:
178061312 rbs01.dbf
183502848 system01.dbf
197296128 temp01.dbf
is it normal to have such huge datafiles?
Hi ,
You can rename the table and create the table with the same name by specifying the STORAGE parameter, I mean INITIAL extent, NEXT extent size inside the STORAGE parameter. Then this may solve the problem evenif you are specifying a low PCTFREE value for that table.
Pl get back to me if you have any concerns.
Those are small, not huge assuming bytesQuote:
Originally posted by helpme
178061312 rbs01.dbf
183502848 system01.dbf
197296128 temp01.dbf
I think 175 MB of System Tablespace and is too big
isn't it?
Regards
Shruti
not really satyendu
this was the storage clause in my original table
INITIAL 51200
NEXT 51200
MINEXTENTS 1
MAXEXTENTS 2147483645
and this in the new dummy 1
INITIAL 1048576
NEXT 10485760
MINEXTENTS 1
MAXEXTENTS 2147483645
even the new 1 has chained row....
thanx
Sam
thats wat xactly 1 of my freind react as when i told him bout my datafile size.
Is it really that huge?
175 is not that big, depending on what options you have installed and specially on a production database
Hi!!
But I think I never had System tablespace more than some 50 MB with all the options offered by DBCA.
I'm reffering to 8i. I think most of the tablespace might be fragmented that's reson for such huge size.
Regards
Shruti
Hi Shruti,Quote:
Originally posted by ShrutiM
Hi!!
But I think I never had System tablespace more than some 50 MB with all the options offered by DBCA.
I'm reffering to 8i. I think most of the tablespace might be fragmented that's reson for such huge size.
Regards
Shruti
If you install all the options offered by Oracle 8i like Advanced Replication, Spatial, Intermedia Text ... you must be sure that your System Tablespace will be over 300 MB :-)
Regards
Angel
Hi!!
Thats what I'm saying even after all those options I never had such big size.
Thanks anyway,
Shruti
About 64M is standartf for 8i, if you install extra options it might grow up to a couple of hundread M.Quote:
Originally posted by ShrutiM
I think 175 MB of System Tablespace and is too big
isn't it?
Regards
Shruti
I cant really believe you dont get over 250MB SYSTEM tablespace after install all the options, I have done quite a few times, even just by installing the standard it goes almost to 100MB, if you start to install all the java j0nx it jumps!