Mathews, first of all you are going through the problem that gets created with using of 50 percent in pctincrease parameter. This increase has made your next extent of size close to 6GB which is too much. You need to take the 50 pct increase out and recreate your table with more reasonable extents. I would take about one half of total space occupied this table currently into the first or initial extent and then the remaining half I would divide by about 20 to set the next extent. This way you next extent will come down to something like a couple of hundred of megs from 6GB now.
Now, think of your table has a total of 17 GB space allocated to it. The last extent that got allocated, say yesterday is of size 6 GB which is one-third of the total space allocated to this table. Now, let us assume the hwm on this table is set to more towards the end of the 17 GB. Do you know some of the operations on this table such as full table scans would have to read the table upto 17 GB [until the high water mark] and not until 11 GB until which the data is actually loaded. When you have to take these extra 6GB not necessary and empty otherwise data blocks into memory against no space being there in the db_buffer it pushes your operating system into what is called swapping and becomes expensive time wise to do any operation on transactions involving that much of the data. This is what happens whenever full table scans are warranted for one or other reason.
In short you don't want to go through this futile exercise of having to read empty blocks for any reason if you want your system to work fast. So you have to bring down your extremely large next extent into more reasonably size next extent.
Besides, it would be good idea for you to think in terms of removing row-chaining and reorganizing your table to take care of fragmentation in the table. You could do that easily by way of rebuilding your table online via alter table <table name> move statement. Actually you could take care of your extremely large next extent also in the same statement when you define the storage clause.
After this I will suggest you to check all your sqls against this table and be sure that each one of them is covered by some index to avoid any full table scans.
Finally, you can also take a second look on fixing your pctfree, pctused numbers also setting them such that a minimum of space is wasted in each block at any given time.
Gurmohan
www.onlymath.com - The place to learn and teach math.
-----Original Message-----
From: Mathews Ansamma
[mailto:oracledba-ezmlmshield-x17006714.[Email address protected]
Sent: Thursday, October 27, 2005 1:01 PM
To: LazyDBA Discussion
Subject: Doubts about EXTENT allocation
Dear DBAs,
I have a table that is not responding well. I liiked at the storage
parameters and found the following.
Select min_extents, initial_extent, next_extent, max_extents,
extents,PCT_INCEASE from dba_segments where segment_name='MY TABLE';
MIN_EXTENTS INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS EXTENTS
PCT_INCREASE
1 65536 8600518656 505
31 50
Then I looked at the DBA_EXTENTS to get more info.
select extent_id, bytes from DBA_EXTENTS where segment_name='ID_LOGON' order
by extent_id;
0 81920
1 40960
2 81920
3 122880
4 163840
5 245760
6 368640
7 532480
8 778240
9 1146880
10 1720320
11 2580480
12 3891200
13 5816320
14 8724480
15 13107200
16 19660800
17 29491200
18 44195840
19 66314240
20 99450880
21 149176320
22 223723520
23 335585280
24 503365632
25 755048448
26 1132576768
27 1698865152
28 2548301824
29 3822477312
30 5733679104
I assume that the extent_id 0 is for oracle internal work. extent ID 1&2 are
multiples of my block size (8192 *5 and 8192 * 10) .
Then it seems to be going per the PCT _INCREASE factor. My question is what
is the effect of setting next_extent in the
storage clause? Does oracle allocates the minimun of the value by
PCT_INCREASE and next_extent ?
Appreciate your advice.
Thanks,
Ancy
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page