Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-1654 , Oracle asking for very large extent

Re: ORA-1654 , Oracle asking for very large extent

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Mon, 31 Jul 2006 20:31:49 GMT
Message-ID: <J3ABp9.Lx5@igsrsparc2.er.usgs.gov>


> Thank you. Seems I was wrong about the max-extent nolonger applies for
> lmt tablespace. Is this true for 9i as well? I don't have access ot
> unix now so can't test.

Yes, and it has been true ever since LMT's debuted.

> Can you do a :
> select bytes, count(*) from dba_extents where segment_name='YOUR BIG
> SEG' GROUP BY BYTES,
>
> See at which stage, it start to use 512M? The last time I did some test
> and I was unable to get to a larger size than 64M, with something like
> 500gb space; --data could be differnet, it was 3 years ago.

In my case, the object in question is part of the Data Dictionary:

SQL> select bytes,count(*) from dba_extents

   2 where segment_name='C_OBJ#_INTCOL#' group by bytes order by 1;

               BYTES COUNT(*)

------------------- ----------
              16,384          2
              24,576          1
              40,960          1
              81,920          1
             122,880          1
             163,840          1
             245,760          1
             368,640          1
             532,480          1
             778,240          1
           1,146,880          1
           1,720,320          1
           2,580,480          1
           3,891,200          1
           5,816,320          1
           8,724,480          1
          13,107,200          1
          19,660,800          1
          29,491,200          1
          44,195,840          1
          66,281,472          1
          99,426,304          1
         149,143,552          1
         223,715,328          1
         335,577,088          1
         503,365,632          1
-------------------
       1,510,219,776


> Also, I believe for next_extent, oracle won't break it up. If there is
> no free extent >= next_extent, oracle fails. if you can test to prove I
> was wrong, I appreciate it. Can't test now.

That is incorrect. The following should prove the point (test run on 10.2.0.2 running on RHEL):

First, create a tablespace to be used for the test:

SQL> create tablespace test_ts

   2 datafile '/edclxs69/orasata01/gasl/test_ts01.dbf'    3 size 1M
   4 extent management local autoallocate;

Tablespace created.

Next, create some tables to allocate extents in the tablespace.

SQL> create table test1 (id number) tablespace test_ts;

Table created.

SQL> create table test2 (id number) tablespace test_ts;

Table created.

SQL> create table test3 (id number) tablespace test_ts;

Table created.

SQL> create table test4 (id number) tablespace test_ts;

Table created.

SQL> create table test5 (id number) tablespace test_ts;

Table created.

SQL> create table test6 (id number) tablespace test_ts;

Table created.

SQL> create table test7 (id number) tablespace test_ts;

Table created.

Now, let's look at a mapping of these extent in the tablespace. To do that, I'll use a canned script which I have had for many years. The canned script (ts_map.sql) shows a map of the tablespace extents. The script contents are as follows:

SET ECHO OFF
SET VERIFY OFF
SET PAGESIZE 40 SELECT tablespace_name FROM dba_tablespaces;

ACCEPT ts PROMPT 'Enter tablespace name: '

COLUMN tablespace FORMAT a15
COLUMN file_id FORMAT 990
COLUMN block_id FORMAT 9,999,990
COLUMN blocks FORMAT 999,990
COLUMN segment FORMAT a38

SELECT tablespace_name as tablespace,

        file_id,
        block_id,
        blocks,
        owner||'.'||segment_name as segment
FROM dba_extents
WHERE tablespace_name = UPPER('&ts')
UNION
SELECT tablespace_name as tablespace,
        file_id,
        block_id,
        blocks,
        '<free>' as segment

FROM dba_free_space
WHERE tablespace_name = UPPER('&ts');

Now I'll use that script to show the map of the extents and where they lie in the tablespace:

SQL> @ts_map

TABLESPACE_NAME



DATA_LOAD1
SDE
SYSAUX
SYSTEM
TEMP
TEST_TS
UNDOTBS1
USERS 8 rows selected.

Enter tablespace name: test_ts

TABLESPACE FILE_ID BLOCK_ID BLOCKS SEGMENT

------------- ------- ---------- -------- ----------------------------
TEST_TS              18          5        8 SYSTEM.TEST1
TEST_TS              18         13        8 SYSTEM.TEST2
TEST_TS              18         21        8 SYSTEM.TEST3
TEST_TS              18         29        8 SYSTEM.TEST4
TEST_TS              18         37        8 SYSTEM.TEST5
TEST_TS              18         45        8 SYSTEM.TEST6
TEST_TS              18         53        8 SYSTEM.TEST7
TEST_TS              18         61        4 <free>

8 rows selected.

I will now remove segments TEST2 and TEST4 to illustrate fragmented free space. I will show the output from my ts_map.sql script as well:

SQL> drop table test2 purge;

Table dropped.

SQL> drop table test4 purge;

Table dropped.

SQL> @ts_map

<snip>

TABLESPACE FILE_ID BLOCK_ID BLOCKS SEGMENT

--------------- ------- ---------- -------- ----------------------------
TEST_TS              18          5        8 SYSTEM.TEST1
TEST_TS              18         13        8 <free>
TEST_TS              18         21        8 SYSTEM.TEST3
TEST_TS              18         29        8 <free>
TEST_TS              18         37        8 SYSTEM.TEST5
TEST_TS              18         45        8 SYSTEM.TEST6
TEST_TS              18         53        8 SYSTEM.TEST7
TEST_TS              18         61        4 <free>

Notice the "holes" of free space. I will now create a new segment with INITIAL set to a value larger than any of these holes. My db block size is 16KB and the largest hole (as seen above) is 8 blocks, so if I use INITIAL larger than 128KB (16KB * 8 = 128KB) than we can see how LMT's allocate subextents when it does not have a contiguous chunk of space for the extent to be allocated. First, we'll create the table:

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- 
------------------------------
db_block_size                        integer     16384

SQL> create table frag_table (id number)

   2 tablespace test_ts storage (initial 256k);

Table created.

Now, we'll verify the table's INITIAL extent in USER_SEGMENTS:

SQL> select initial_extent,extents from user_segments

   2 where segment_name='FRAG_TABLE';

INITIAL_EXTENT EXTENTS
-------------- ----------

         262144 3

Notice that the INITIAL extent value is 256K (256*1024 bytes = 262,144 bytes) but all of that is really 3 extents now!!! That's because Oracle could not find one chunk of free space large enough and it broke this down into what some of us have been calling "subextents". Let's see how Oracle broke this down inside the tablespace:

SQL> @ts_map

<snip>

TABLESPACE FILE_ID BLOCK_ID BLOCKS SEGMENT --------------- ------- ---------- --------


TEST_TS              18          5        8 SYSTEM.TEST1
TEST_TS              18         13        8 SYSTEM.FRAG_TABLE
TEST_TS              18         21        8 SYSTEM.TEST3
TEST_TS              18         29        4 SYSTEM.FRAG_TABLE
TEST_TS              18         33        4 SYSTEM.FRAG_TABLE
TEST_TS              18         37        8 SYSTEM.TEST5
TEST_TS              18         45        8 SYSTEM.TEST6
TEST_TS              18         53        8 SYSTEM.TEST7
TEST_TS              18         61        4 <free>


Notice that FRAG_TABLE is now occupying the space in multiple chunks, or extents. Even though I only attempted to allocate the INITIAL extent for the tablespace. I've done this test for INITIAL, but the same holds true for the NEXT extent as well.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
Received on Mon Jul 31 2006 - 15:31:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US