mike, October 13, 2001 - 12:32 pm UTC
I tried it on my laptop:
SQL> select owner, segment_name, segment_type, extents, max_extents
2 from dba_segments
3 where max_extents-extents <= 3;
OWNER SEGMENT_NAME SEGMENT_TYPE EXTENTS MAX_EXTENTS
----- --------------- ------------------ --------- -----------
SYS 1.537 CACHE 1 0
what can we do about it?
it is 8.1.5
October 13, 2001 - 1:36 pm UTC
That one you need not worry about.
When the database is created, a segment of type 'CACHE' is created. It is
owned by 'SYS' and resides in the system tablespace. It can been found
by querying DBA_SEGMENTS or USER_SEGMENTS for a segment_type of 'CACHE'
and has one extent allocated but MAX_EXTENTS = 0.
The name of the segment will be 1.<number> which is derived from the values
of HEADER_FILE.HEADER_BLOCK in DBA_SEGMENTS. This file and block marks
the beginning of non-bootstrap data dictionary objects. All objects which
are created prior to bootstrap$ in SQL.BSQ are required for warmstarting
the instance. These objects can only be created from CREATE DATABASE.
The CACHE object SYS.1.<header_block_number> marks the starting block
where non-bootstrap objects will reside.
Reader
Reader, October 13, 2001 - 12:34 pm UTC
Tom,
"
ops$tkyte@8i> create table t ( x int, y char(2000) default '*' )
2 storage ( initial 40k next 40k minextents 5 )
3 tablespace system;
EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
2 40960 5
3 81920 10
4 57344 7
0 40960 5
1 40960 5
----------
sum 32
"
Why the 4th extent took 80K and 5th extent took 7K instead
of 5K
Thanks
October 13, 2001 - 1:54 pm UTC
well, without knowing the default pctincrease, its hard but...
The extents took:
40k 40k 40k 80k 56k
(not 80k and then 7k -- which would be impossible as you have a 8k blocksize apparently)
When we look for space, we do not like to leave chunks of 5 or less blocks behind (too small for anything). Hence, we allocate them all for you.
see
</code>
http://docs.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c02block.htm#2846 <code>
to read how extents are allocated in a dictionary managed tablespace such as system.
Reader
Reader, October 13, 2001 - 12:40 pm UTC
My previous posting, rephrasing my question ;)
why the 4th extent took 10 blocks and 5th 7 blocks instead
of 5 blocks (40K / 8K db_block_size)
Thanks
Reader
Reader, October 13, 2001 - 2:28 pm UTC
October 13, 2001 - 2:48 pm UTC
Well, that makes it a little more clear then.
My pctincrease was probably 0% (it was a while ago, that database doesn't exist anymore)
my extents should have been 40k apiece.
my free space wasn't full of 40k chunks. We don't like to leave little bits and pieces about so when we hit a free extent with a couple more blocks then we need -- we use it.
Hence, we had some nice 40k chunks we used -- then we allocated the 80k chunk and then moved onto the 56k chunk -- rather then leaving bits of free space around.
See the referenced link (above) to see the search algorithm we go through.
If and only if you use locally managed tablespaces will you get predicable extent allocations. Otherwise they are rounded up to fit better.
Max extents
A reader, June 26, 2002 - 1:07 pm UTC
We are monitoring extents utilization, to avoid obtaining ORA-1631 max extents reached error.
Can we alter all the tables and indexes to have max extents UNLIMITED? Is there any negative effect of doing this?
Thanks
June 26, 2002 - 3:25 pm UTC
negative is in the eye of the beholder.
There are arguments both ways. I would not want objects in 10's of thousands of extents, but hundreds or thousands -- that is OK.
If you really truly honestly don't have a clue about the ultimate size of your objects, you should use a locally managed tablespace with system managed extent sizes. They will have unlimited extents but the extents will grow larger as the number goes up. Works well when you quite simply "have no idea". In 9i-- its the default tablespace mgmt policy!
Sagi, October 17, 2002 - 9:37 am UTC
Hi Tom,
You said that
"extents will grow larger as the number goes up" if we use LMT.
It would of great help if you could give us an example. It not that we dont believe. It would make use understand more.
Regards,
Sagi
Regards,
Sagi
October 17, 2002 - 1:53 pm UTC
ops$tkyte@ORA920.US.ORACLE.COM> create table t as select * from all_objects;
Table created.
ops$tkyte@ORA920.US.ORACLE.COM> begin
2 for i in 1 .. 5
3 loop
4 insert /*+ append */ into t select * from t;
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> exec print_table( 'select * from dba_tablespaces where tablespace_name = ''USERS'' ' )
TABLESPACE_NAME : USERS
BLOCK_SIZE : 8192
INITIAL_EXTENT : 65536
NEXT_EXTENT :
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
MIN_EXTLEN : 65536
STATUS : ONLINE
CONTENTS : PERMANENT
LOGGING : LOGGING
FORCE_LOGGING : NO
EXTENT_MANAGEMENT : LOCAL
ALLOCATION_TYPE : SYSTEM
PLUGGED_IN : NO
SEGMENT_SPACE_MANAGEMENT : AUTO
-----------------
PL/SQL procedure successfully completed.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select extent_id, blocks
2 from user_extents
3 where segment_name = 'T'
4 /
EXTENT_ID BLOCKS
---------- ----------
0 8
1 8
2 8
3 8
4 8
5 8
6 8
7 8
8 8
9 8
10 8
11 8
12 8
13 8
14 8
15 8
16 128
17 128
18 128
19 128
20 128
21 128
22 128
23 128
24 128
25 128
26 128
27 128
28 128
29 128
30 128
31 128
32 128
33 128
34 128
35 128
36 128
37 128
38 128
39 128
40 128
41 128
42 128
43 128
44 128
45 128
46 128
47 128
48 128
49 128
50 128
51 128
52 128
53 128
54 128
55 128
56 128
57 128
58 128
59 128
60 128
61 128
62 128
63 128
64 128
65 128
66 128
67 128
68 128
69 128
70 128
71 128
72 128
73 128
74 128
75 128
76 128
77 128
78 128
79 1024
80 1024
81 1024
82 1024
83 1024
84 rows selected.
<b>there you go, see how the extent sizes just increased all by themselves in this SYSTEM allocation type tablespace...</b>
Extents for an Audit LOG table
sm, October 17, 2002 - 4:14 pm UTC
Tom,
We are using Dictionary Managed Tablespaces :( .. moving hopefully to LMT in a month or so.. :)
database size is about 2.5 gig.
We have a table that we use for auditing the DMLs for lot of tables...
Of course..this table is growing "really" fast..
and it gave ora-01631 today...
max extent on the tablespace as well as the table were set to 505 (8k db_block_size)..
We can increase that..to fix the issue...or set it to unlimited..
but the real reason this happened was because of the small next extent..
The table has 200000 rows now..
It just gets inserts...no updates or deletes
Question
1. Is there a method (a mathematical equation of some sort) to estimate the size of the initial and next extent
2. Will this table/tablespace be a good candidate for unlimited maxextents...
3.UNLIMITED sort of scares me...
What are some other guidelines for managing these large..always growing tables..besides watching them with free.sql...
4.Can you recommend a good book....that has lot of real world cases...please...
Thanks a zillion...
October 17, 2002 - 4:32 pm UTC
1) you want initial = next and pctincrease = 0. Thats easy.
You want the object to maybe extend once or twice a week at most perhaps.
So, figure out how much data goes into it in a week. Make the extent size that big.
2) sure, why not. you are constrained by disk space at the end of the day.
3) LMT's only do unlimited!!! you have *no choice* going forward
4) Jonathan Lewis's Practical Oracle8i Building Efficient databases is a favorite of mine....
number of extents for index
June, January 29, 2003 - 10:02 am UTC
Hi Tom,
I have one snapshot with two indexes on it, see following detail info:
select index_name, column_name,column_length,column_position
from all_ind_columns where table_name='SNPSH_TRD_CNCLN_ASOF_TRD_DTL'
and table_owner='IFDF'
SQL> /
INDEX_NAME COLUMN_NAME COLUMN_LENGTH COLUMN_POSITION
-------------------- --------------- ------------- --------
SYS_C003576 FDTP 7 1
SYS_C003576 SBMTG_FIRM_ID 22 2
SYS_C003576 CLRG_FIRM_ID 22 3
SYS_C003576 CLRG_MPID_TX 5 4
SYS_C003576 CRSPT_FIRM_ID 22 5
SYS_C003576 CRSPT_MPID_TX 5 6
SYS_C003576 SEQ_NB 22 7
PF1_xxx FIRM_PRCSD_DT 7 1
For these two indexes, the extent information is as following:
SEGMENT_NAME EXTENTS INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS
------------------ ---------- -------------- ----------- ---
SYS_C003576 100 1048576 1048576 500
PF1_xxx 747 2097152 2097152 999
Notice that index of PF1_xxx is an index on one column however have more 600 more extents than SYS_C003576, while the later one indexes on more columns and bigger sizes. We manually increase max_extents for PF1_xxx when the ratio extents/max_extents exceeds 80% not long ago, and it seems extents number for PF1_xxx is still growing. The table size is about 1.1million records, with about 180 distinct FIRM_PRCSD_DT values. the growth is about 20k records/day.
They are in same snapshot table (completed refreshed daily). How does it happen? Will drop and rebuild PF1_xxx with bigger extent and smaller help reduce the extent growth?
I thought the index size is based on total column size; however this is conflict case. What should I do?
any suggestion is appreciated.
January 29, 2003 - 10:19 am UTC
what kind of index is pf1_xxx
number of extents for index
June, January 29, 2003 - 5:06 pm UTC
followup:
it is regular one as:
create index PF1_xxx on snpsh_trd_cncln_asof_trd_dtl
(firm_prcsd_dt)
tablespace snpsh_index_tablespace
storage(initial 2m next 2m pctincrease 0)
nologging
;
January 30, 2003 - 8:23 am UTC
the only thing I can think then is you load data like this:
01-jan-2003 <<<=== this is the 1st entry on leaf block 1
02-jan-2003
03-jan-2003
....
31-jan-2003 <<<==== this is the last entry on leaf block 1
01-feb-2003 <<<==== this is the first entry on leaf block 2
....
<<<==== and so on
then, you come back later and delete 02-jan-2003, ... 29-jan-2003. ALMOST emptying block 1, but not quite. Now, that space is available to be reused but ONLY by values around january (data goes in "sorted"). But you don't insert anymore jan-2003 data so that space effectively goes unused (it is available for reuse, you just are not inserting data that will reuse it).
As this goes on over time, most of the blocks in the index are near empty but have some data. So, they cannot be moved within the index structure and reused just anywhere but due to the nature of your data -- you never will actually reuse the space.
This index is a candidate for coalescing -- to undo block splits if you will -- to merge jan/feb/mar together after a while. Check out the coalesce option of the alter index command.
This is the one case where index maintenance is deserved if I've guessed right. You might want to do a one time rebuild (to compact and free up the space) and then coalesce from time to time -- to get some empty blocks onto the freelists for reuse after "compacting"
Algorithm for "SYSTEM allocation type tablespace"
SB, January 30, 2003 - 7:52 pm UTC
Hi Tom,
We monitor a lot of customer databases thru standard database scripts. One such script is to find the availability of space for next extent of a segment. For DMTs we just query the size of next_extent, and initial_extent size for LMTs. Now, my problem is determining the size of next extent for "SYSTEM allocation type" LMTs. From your example above, as you can see, the next extent size increased from 8 to 128 and then to 1024 blocks for extent ids 16 and 79 respectively. Can you please explain me how these next extent sizes are determined so that I can incorporate this logic in my script.
Thankx Tom
January 31, 2003 - 7:59 am UTC
Nope -- it is undocumented and I can show you examples where it doesn't follow that same progression.
You'll have to make assumptions, seems to go
64k
1m
8m
64m
Here is from a 10gig table:
ops$tkyte@ORA920>
1 select sum(mbytes*extents) over ()/1024 gig,
2 sum(mbytes*extents) over (order by start_ext)/1024 cumgig,
3 sum(extents) over () extents,
4 a.*
5 from (
6 select bytes/1024/1024 mbytes,
7 min(extent_id) start_ext,
8 max(extent_id) end_ext,
9 max(extent_id)-min(extent_id)+1 extents
10 from user_extents
11 where segment_name = 'T'
12 group by bytes/1024/1024
13* ) a
ops$tkyte@ORA920> /
GIG CUMGIG EXTENTS MBYTES START_EXT END_EXT EXTENTS
---------- ---------- ---------- ---------- ---------- ---------- ----------
9.9375 .000976563 342 .0625 0 15 16
9.9375 .0625 342 1 16 78 63
9.9375 1 342 8 79 198 120
9.9375 9.9375 342 64 199 341 143
so, the first 200extents are 8meg or less, you would be safe for those assuming that next is 8m (if they have less then 8m free, it is as good as full in my opinion). If an object gets above 200 extents (larger then about 1gig in size), then you should start assuming at least 64m must be free.
Thankx, Tom
SB, January 31, 2003 - 4:50 pm UTC
Why 3
A reader, June 24, 2004 - 10:39 am UTC
select owner, segment_name, segment_type, extents, max_extents from dba_segments where max_extents-extents <= 3;
June 24, 2004 - 10:57 am UTC
why not?
just saying -- look at max_extents, subtract out the current number of extents. If the different is less than or equal to 3, you are get close to hitting max extents.
just a threshold.
so why not 5 or 4 ? any specific reason behind number '3'
A reader, June 24, 2004 - 11:10 am UTC
June 24, 2004 - 2:53 pm UTC
I really liked 3 that day.
although 42 might be the ultimate answer (geek alert)
A reader, June 24, 2004 - 4:43 pm UTC
Hahaha.
A reader, June 30, 2004 - 1:54 pm UTC
June 30, 2004 - 2:05 pm UTC
hey, how incredibly useful -- always the same IP coming in, as "reader", with nothing useful at all to say about anything.
perfect -- have a great day.
thanks for the equally useful one below, awesome the amount of energy you put into this.
A reader, June 30, 2004 - 1:54 pm UTC
Space reclaim
Pravesh Karthik, September 08, 2004 - 7:54 am UTC
Tom,
I have a tablespace with 1682mb in size and when i query for the space used, it shows me 177mb used.
Previously, i moved those tables and indexes in that tablespace to another with new intial extent of 10K ...so, i got back the space. i did this because inital extent was more for those tables.
Now, the scenario is the intial extent is only 1mb .. there many tables, but all tables have initial extent of 1mb only.
why is that i am not able to resize the tablespace, it gives me the error
SQL> ALTER DATABASE
DATAFILE '/u05/oradata/devdb/stage_d1_01.dbf' RESIZE
500M 2 3 ;
ALTER DATABASE
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
how can i reclaim the space?
Thanks a lot for all your time and considerations
Pravesh Karthik
Problem about ORA-1631: max # extents 4000 reached in table ARADMIN.B151C541870867
sanpapol pinpibal, December 20, 2004 - 1:02 pm UTC
I have a problem about "RA-1631: max # extents 4000 reached in table ARADMIN.B151C541870867"
I already increase max_extents to be 8000 from 4000. But still give same error. Please help.
SQL> select tablespace_name TABLESPACE, max(extent_id) EXTENSION_COUNT from dba_extents group by tablespace_name;
TABLESPACE EXTENSION_COUNT
------------------------------ ---------------
ARSYSTEM 3999
IND_L 76
IND_M 185
IND_S 168
IND_XS 290
LOB_L 47
LOB_S 50
LOB_XS 16
RBS 23
SYSTEM 61
TAB_L 247
TAB_M 152
TAB_S 195
TAB_XL 151
TAB_XS 539
TAB_XXL 124
TOOLS 0
17 rows selected.
SQL> select TABLESPACE_NAME, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, STATUs from dba_tablespaces;
TABLESPACE_NAME NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS STATUS
------------------------------ ----------- ----------- ----------- ---------
SYSTEM 65536 1 2147483645 ONLINE
TOOLS 32768 1 4096 ONLINE
RBS 524288 8 4096 ONLINE
TEMP 65536 1 ONLINE
USERS 131072 1 4096 ONLINE
INDX 131072 1 4096 ONLINE
ARTMPSPC 40960 1 505 ONLINE
ARSYSTEM 262144 1 8000 ONLINE
TAB_XS 16384 1 505 ONLINE
TAB_S 65536 1 505 ONLINE
TAB_M 262144 1 505 ONLINE
TAB_L 1048576 1 505 ONLINE
TAB_XL 4194304 1 505 ONLINE
TAB_XXL 16777216 1 505 ONLINE
IND_XS 16384 1 1010 ONLINE
IND_S 65536 1 505 ONLINE
IND_M 262144 1 505 ONLINE
IND_L 1048576 1 505 ONLINE
LOB_XS 16384 1 505 ONLINE
LOB_S 65536 1 505 ONLINE
LOB_L 1048576 1 505 ONLINE
SQL> run
1 select owner, segment_name, segment_type, extents, max_extents
2 from dba_segments
3* where max_extents-extents <= 3
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE EXTENTS MAX_EXTENTS
------------------ ---------- -----------
ARADMIN
T69
TABLE 505 505
ARADMIN
B151C541870867
TABLE 4000 4000
SYS
1.173
CACHE 1 0
December 20, 2004 - 2:04 pm UTC
you have to alter the TABLE
the tablespace has the defaults for any newly create object that doesn't specify their own default.
you have an existing table, modifying the tablespace default attributes won't do a thing to an existing table.
alter the table.
How many CACHE segments are there
Hans Wijte, February 28, 2005 - 9:14 am UTC
SQL> connect USERA/xxxxxxxx@nl_prd
SQL> column owner format a12
SQL> column segment_name format a20
SQL> select owner, segment_name, segment_type, extents, max_extents
2 from dba_segments
3 where max_extents-extents <= 3;
OWNER SEGMENT_NAME SEGMENT_TYPE EXTENTS MAX_EXTENTS
------------ -------------------- ------------------ ---------- -----------
SYS 1.128 CACHE 1 0
SYS 1.24749 CACHE 1 0
SQL> column file_name format a50
SQL> select file_name, tablespace_name from dba_data_files
2 where tablespace_name = 'SYSTEM';
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/oracle/data/NL_PRD/sys/NL_PRD_SYSTEM_01.dbf SYSTEM
SQL> connect USERA/xxxxxx@ek_plfrd
Connected.
SQL> /
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/oracle/data8/tab/EK_PLFRD/EK_PLFRD_system01.dbf SYSTEM
SQL> select owner, segment_name, segment_type, extents, max_extents
2 from dba_segments
3 where max_extents-extents <= 3;
OWNER SEGMENT_NAME SEGMENT_TYPE EXTENTS MAX_EXTENTS
------------ -------------------- ------------------ ---------- -----------
SYS 1.173 CACHE 1 0
How come I have 2 CACHE segments in database A while there is only 1 CACHE segment in database B ?
(both databases have 1 datafile in the SYSTEM tablespace)
changing the intial extent size
reddy, October 04, 2005 - 12:47 pm UTC
Hi Tom,
In one of my Databases, most of the tables are created with larger intial extent sizes. The no.of rows in that tables are very less and some of them are dont have any data.
My duty is to reduce the size of the database as less as possible.
Because most of the tables are with 0 rows and bigger intial extent sizes. I would like to recreate the tables with small inttial extent sizes.
Can you please suggest me the best way for this.
Thanks you
October 04, 2005 - 5:18 pm UTC
create a locally managed tablespace with system allocated extents (let Oracle size them, they start small and get bigger as the table gets bigger)
and then
alter table T move
tablespace new_tablespace
storage ( initial 1k next 1k minexents 1 pctincrease 0 );
and
alter index I rebuild
tablespace new_tablespace
storage ( initial 1k next 1k minexents 1 pctincrease 0 );
for each index on the table.
Automatic extent monitoring
DJB, October 05, 2005 - 6:36 am UTC
For those still using dictionary managed tablespaces here is a procedure which should be compiled as SYS into your database which will create alerts in a report file which can then be interrogated by a batch job:-
START OF PROCEDURE
---------------------
rem Compile as SYS
CREATE OR REPLACE PROCEDURE monitor_extents AS
db_name_var varchar2(2000);
area1 number;
area2 number;
ts_name dba_tablespaces.tablespace_name%TYPE;
alert1 boolean;
alert2 boolean;
next number;
error_type varchar2(9);
error_message varchar2(255);
required_fix varchar2(255);
status varchar2(20);
--
cursor db is select global_name from global_name;
--
cursor ts is
select tablespace_name
from dba_tablespaces
where tablespace_name not in ('TEMP','RBS','SYSTEM')
order by tablespace_name;
--
cursor areas is
select bytes
from dba_free_space
where tablespace_name = ts_name
order by bytes desc;
--
cursor segs is
select segment_name,
next_extent,
extents,
max_extents
from dba_segments
where owner = 'FG_HELPDESK'
and tablespace_name = ts_name
and segment_name not like '%$%';
--
BEGIN
--
open db;
fetch db into db_name_var;
close db;
---
--- Loop for all tablespaces, get 2 largest free areas, get all segs in tablespace
--
for ts_rec in ts loop
dbms_output.put_line('ts = '||ts_rec.tablespace_name);
ts_name := ts_rec.tablespace_name;
open areas;
fetch areas into area1;
-- dbms_output.put_line('area1 = '||area1);
fetch areas into area2;
-- dbms_output.put_line('area2 = '||area2);
close areas;
--
for seg_rec in segs loop
-- dbms_output.put_line('seg = '||seg_rec.segment_name||', next = '||seg_rec.next_extent);
alert1 := FALSE;
alert2 := FALSE;
next := seg_rec.next_extent;
if ((2*next > area1) and (next > area2)) then
alert1 := TRUE;
dbms_output.put_line('******************** ALERT 1 ****************');
dbms_output.put_line('No room for 2 additional extents for '||seg_rec.segment_name);
end if;
if (next > area1) then
alert2 := TRUE;
dbms_output.put_line('******************** ALERT 2 ****************');
dbms_output.put_line('No room for next extent for '||seg_rec.segment_name);
end if;
--this section added so if 2 alerts occur at the same time, both are
--signalled.
if (alert1) then
status := 'ALERT1';
end if;
if (alert2) then
status := 'ALERT2';
end if;
if (alert1 or alert2) then
dbms_output.put_line('tablespace = '||ts_name||', Object = '||seg_rec.segment_name);
dbms_output.put_line('Next extent size = '||next);
end if;
alert1 := FALSE;
alert2 := FALSE;
--end of added section
--
if (seg_rec.extents > seg_rec.max_extents/2) then
alert1 := TRUE;
dbms_output.put_line('******************** ALERT 3 ****************');
dbms_output.put_line('50% + extents used by '||seg_rec.segment_name);
end if;
if (seg_rec.extents = seg_rec.max_extents) then
alert2 := TRUE;
dbms_output.put_line('******************** ALERT 4 ****************');
dbms_output.put_line('Max extents reached '||seg_rec.segment_name);
end if;
if (alert1) then
status := 'ALERT1';
end if;
if (alert2) then
status := 'ALERT2';
end if;
if (alert1 or alert2) then
dbms_output.put_line('Tablespace = '||ts_name||', Object = '||seg_rec.segment_name);
dbms_output.put_line('Next extent size= '||next);
end if;
end loop;
end loop;
----
END monitor_extents;
/
________________________
END OF PROCEDURE.
The steps to use tis are as follows:-
1. Edit the procedure as required for tablespace names etc.
2. Compile into the database as SYS.
3. Create a batch job to run at regular intervals which executes the procedure and writes to a report file.
4. Run another job to interrogate the output for 'alert' and send a message/e-mail etc to report the alert.
Regarding Table extents !
Geddam Chaitanya, October 24, 2005 - 1:30 pm UTC
Referring to your answer regarding minimum available extents.
You Said...
I really liked 3 that day.
although 42 might be the ultimate answer (geek alert)
I never got what you mean by "42"
October 25, 2005 - 1:11 am UTC
plug 42 into google.
Kathy, March 01, 2006 - 7:09 pm UTC
This is exactly what I was looking for, but I need the syntax of the alter table command to increase max_extents. This contained everything I needed to know except exactly how to do it.
blocks vs used_bklk
A reader, July 12, 2006 - 7:33 pm UTC
I have a table which is 15 gig in size. I use BYTES from dba_segments column to determine the size of the table (object). The value of blocks in dba_segments is 800,000
Say I have to delete half of the table using DELETE statement.
After a while (say 60 minutes), if I see the value 65,000 in USED_UBLK column under v$transaction table for the session which I am deleting the data, can I assume it takes around 7 hours to delete half of the table (400k blocks)?
65K blocks--- one hour
400,000 blocks---- approximate 7 hours?
Thanks,
July 12, 2006 - 7:50 pm UTC
No, not really, because there are indexes to consider, overhead to consider.
Delete in general consumes the most undo (as the entire row image is there pretty much).
If you are doing the delete via a full scan - v$session_longops might be useful.
I kicked off a big delete and waited a bit:
.SID : 148
.SERIAL# : 863
.OPNAME : Table Scan
.TARGET : BIG_TABLE.BIG_TABLE
.TARGET_DESC :
.SOFAR : 2870
.TOTALWORK : 146312
.UNITS : Blocks
.START_TIME : 12-jul-2006 19:44:50
.LAST_UPDATE_TIME : 12-jul-2006 19:45:13
.TIMESTAMP :
.TIME_REMAINING : 1150
.ELAPSED_SECONDS : 23
.CONTEXT : 0
.MESSAGE : Table Scan: BIG_TABLE.BIG_TABLE: 2870 out of
146312 Blocks done
.USERNAME : BIG_TABLE
.SQL_ADDRESS : 43D6D61C
.SQL_HASH_VALUE : 3150445350
.SQL_ID : ah89u6kxwgyt6
.QCSID : 0
-----------------
PL/SQL procedure successfully completed.
shows how far along the delete was...
.SID : 148
.SERIAL# : 863
.OPNAME : Table Scan
.TARGET : BIG_TABLE.BIG_TABLE
.TARGET_DESC :
.SOFAR : 6167
.TOTALWORK : 146312
.UNITS : Blocks
.START_TIME : 12-jul-2006 19:44:50
.LAST_UPDATE_TIME : 12-jul-2006 19:45:38
.TIMESTAMP :
.TIME_REMAINING : 1091
.ELAPSED_SECONDS : 48
.CONTEXT : 0
.MESSAGE : Table Scan: BIG_TABLE.BIG_TABLE: 6167 out of
146312 Blocks done
.USERNAME : BIG_TABLE
.SQL_ADDRESS : 43D6D61C
.SQL_HASH_VALUE : 3150445350
.SQL_ID : ah89u6kxwgyt6
.QCSID : 0
-----------------
PL/SQL procedure successfully completed.
Why do extents run out in this case...
Enzo, July 24, 2006 - 5:54 pm UTC
Using Oracle 9206 and I have an index tablespace that only has 1 datafile which is autoextensible, locally managed tablespace, segment mgmt. audo and plenty of free space on disk.
I get a ORA-1654: Unable to extend THEINDEX by 8192 error. Max extents on the index are set to 2147483645 on the index.
How can I get an ORA-1654? I'm nowhere near using 2147483645 extents and the datafile is on autoextend with plenty of free space.
I understand that Oracle's telling me it cannot find a contiguous extent free, but if Oracle cannot find an extent to use, the datafile should autoextend since that's the purpose of autoextend, no?
Guess I'm confused here on space management and re-read the docs, but according to what I read the data files should auto extend.
From the Oracle 9.2 docs when AUTOEXTEND on a datafile is on it "Ensures applications will not halt because of failures to allocate extents"
Can you please clarify how to set up a tablespace that has plenty of free disk space so that I never have to worry about extents?
July 24, 2006 - 6:39 pm UTC
so, the datafile is autotextend. what is the MAXSIZE of the file however.
Doh....that seems to be the culprit....
Enzo, July 24, 2006 - 7:21 pm UTC
MAXBLOCKS = 4194302 x BLOCKSIZE 8192 = 31.99 gb datafile max.
and wouldn't you know that the datafile size shows right at 32 gb.
Thanks. I won't forget that one anytime soon. Usually I don't put a max size on a datafile and I didn't create this database so that didn't even cross my mind. Doh. It's a good
thing though, it means my understanding of extents is still intact. :-)
reader
A reader, October 13, 2006 - 11:40 am UTC
On 9.2.0.7 unix platform when I create a tablespace extent management local uniform size 1m ( without specifying initial and next extents );
I get min_extlen 66k ( 11 times blocksize)
Is there a default min_extlen allocated by oracle based on block size
October 13, 2006 - 2:35 pm UTC
eh? not following you, if you create a LMT with uniform size of 1m, the minimun and maximum AND ONLY extent length will be.....
1m
nothing else but nothing else will happen. Nothing else matters, next, pctincrease - nothing like that will affect the extent size at all.
reader
A reader, October 13, 2006 - 1:04 pm UTC
Following up the above posting, could it be 64k rounded up to nearest block size ? I do not have a test system to test with. My laptop is hosed up
extent management local?
Roderick, October 14, 2006 - 12:06 am UTC
66K is a rare number to see since it's rare to have a database with a 6K block size. Perhaps you were seeing min_extlen = 65536? If so, I guess could be rounded up and written by some people as 66KB (66*1000bytes) or 64KiB (64 * 1 Kilobyte or 1024 bytes).
If somehow the "uniform extent size 1m" clause was left out or ignored from the CREATE TABLESPACE statement, then the default of autoallocate is implied, which is only one of possibly many reasons you could see min_extlen=65536 (allocation_type = 'SYSTEM').
Just my wild guess.
Number of extents
A reader, October 25, 2006 - 9:52 am UTC
One of the developers complained that any operations on a table (table A) with a little more than 10,000 rows is taking a very long time. I compared that table to another similar table (table B) with 7,000 rows. The main difference that I see is that the no of extents for table A is 15,700 and the no of extents for table B is 20. Also, if I see the Block IDs, for table B it is somewhat contiguous but for table A it is all over the place. This could directly impact the performance of the table, right? How do I make the performance better ?
October 25, 2006 - 10:53 am UTC
you have a table with more extents than rows? interesting.
what is up with this table
how do you access it?
if you use indexes, so what if there is 1 or 1,000,000,000,000 extents - we use a rowid (file/block/slot on block) to get the data.
if you frequently full scan it, sounds like you need to look at how you use this table and rethink your approach - sounds like the table is mostly "empty". And hence your full scan is reading tons of blocks only to find they are empty.