Extents explaination.. 1

[MCubitt]

I am learning day-by-day about Oracle .. a kind of "on the job training" with no tutor. Not an ideal situation but that's how it is.

We are using 9i but the supplier of our system who installed and configured Oracle, appear to have not used LMT so guess who has to MT!

Can I confirm that my understanding of extents is correct, please? If not, a simple no with comments on my misunderstanding will be fine, shooting me down in flames would be a little hurtful...!

1st... Segments relate to tables, indexes, packages.. any object contained inside a scheme?

2nd... Segments are created with an INITIAL size, NEXT EXTENT value and MAX ENTENT value.
The NEXT EXTENT value tells Oracle how much space to automatically add to the segment if it fills up.


So if segment EMPLOYEES was set at inital size 30MB and next extent of 8MB, when the segment becomes full it will grow to 38MB and the 'number of extents' will increase to 1.

If the 'number of extents' passes the 'max extents' an Oracle error message is issued.


So, to take my "real life" example, I have the following *problem*:

OWNER     SEGMENT_NAME                   TYPE       EXTENTS        BYTES NEXT_EXTENT MAX_EXTENTS
---------- ------------------------------ -------- --------- ------------ ----------- -----------
IFSAPP     PERIODICAL_TRANSACTION_PK      INDEX          231   30,408,704      106496        4096
IFSAPP     CUSTOMER_ORDER_LINE_HIST_TAB   TABLE          466   61,079,552      131072        4096
IFSAPP     CUSTOMER_ORDER_LINE_TAB        TABLE          317   41,549,824      131072        4096
IFSAPP     CUST_OPEN_ITEM_RPT             TABLE          742   97,255,424      131072        4096
IFSAPP     INVENTORY_TRANSACTION_HIST_TAB TABLE          513   67,239,936      131072        4096
IFSAPP     MANUF_STRUCTURE_JOURNAL_TAB    TABLE         2785  432,013,312    67108864        4096
IFSAPP     MANUF_STRUCTURE_TAB            TABLE          222   62,521,344    33554432        4096
IFSAPP     MPCCOM_ACCOUNTING_TAB          TABLE          706   92,536,832      131072        4096
IFSAPP     MRP_PART_SUPPLY_DEMAND_TAB     TABLE          261   34,209,792      131072        4096
IFSAPP     ORDER_INFO_SERVICES_RPT        TABLE          469   61,472,768      131072        4096
IFSAPP     PERIODICAL_TRANSACTION_TAB     TABLE          348   45,744,128      106496        4096
IFSAPP     REP_UPDATE_GENLED_RPT          TABLE          293   38,404,096      131072        4096

If my theory is right, segment MANUF_STRUCTURE_JOURNAL_TAB will add 67MB each extent. It seems a rather high value but who am I to argue?

To "reset" the problem, I have a number fo options, I believe...

Increasing the NEXT EXTENT would mean less extents would be needed because each one would do the job of many (if you see what I mean, 80MB would do the job of 10 8MB extents).

Increasing the MAX EXTENTS just prolongs the problem.

Ideally, resize with new storage parameters, e.g.
alter table TABLENAME move tablespace TABLESPACENAME storage (initial INITIAL_SIZE next NEXT_EXTENT);


Probably the worst ever explanation but am I even close? ;o)


Thanks.



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[carp]

1. Extents apply to segments (e.g., tables, indexes, clusters). Since a package is a code object (not a segment), extents do not have relevance.

2. Right (almost). However, segments are also defined with one other parameter - PCTINCREASE. This is a growth factor that is applied for subsequent extent allocation. So if your table is defined with INITIAL = 10M, NEXT = 100M, PCTINCREASE = 50, the first extent allocated will be 10M and is allocated when the table is created. When this space is exhausted, the NEXT extent will be allocated. So now the table has two extents for a total of 10M + 100M = 110M. When this space is depleted, another extent is allocated; this one will be 100M * 1.50 = 150M, and the table now has 260M. Then next extent will be 150M * 1.5 = 225M and the table will be 485M. This pattern continues until no more growth is required, MAXEXTENTS is reached, or the next extent cannot be allocated.

3.

So if segment EMPLOYEES was set at inital size 30MB and next extent of 8MB, when the segment becomes full it will grow to 38MB and the 'number of extents' will increase to 1.

No - the number of extents will increase to two. The first extent was allocated when the table was created and is sized to whatever INITIAL was set for (or at least, rounded to the next block size multiple thereof).

4.

If my theory is right, segment MANUF_STRUCTURE_JOURNAL_TAB will add 67MB each extent. It seems a rather high value but who am I to argue?

Each extent will be 67M if PCTINCREASE is set to 0. Also, given that you currently have 2,785 extents allocated to this table, 67M does not seem high at all. Given your (correct) argument that one 80M extent provides about the same amount of storage as eight 10M extents, how can 67M be to high? If your intent is to minimize the number of extents, wouldn't it seem that 67M is too low?

Something to keep in mind is that an extent consists of contiguous blocks. So creating a relatively large extent may become difficult (since finding many contiguous blocks can be harder than finding a few contiguous blocks). Because of this, it may be possible to make the eight 10M extents but not the single 80M extent. This is due to what is called tablespace fragmentation. To help alleviate this problem, Oracle recommends that tables within the same tablespace have similar (if not identical) extent sizes.

alter table TABLENAME move tablespace TABLESPACENAME storage (initial INITIAL_SIZE next NEXT_EXTENT);

You can't alter the INITIAL setting, but you CAN alter the NEXT_EXTENT and PCTINCREASE settings.

I hope this helps and that you don't feel flamed.

 

[jimbopalmer]

Close.
In your EMPLOYEES example extent 1 stays at 30 meg, and a 8 meg extent is added. (elsewhere on the disk) For a full table scan both extents must be read.

Since they are not contiguous, some additional Disk IO is needed compared to a extent size larger than 38 meg.

In your real life example that 'extra Disk IO' is only going to happen every 67 meg so it will not add much time to the full table scans. More worrisome is that some day you will run out of 4096 extents, increasing the size of Next extent can slow that. (doing so sooner is better than later)

I tried to remain child-like, all I acheived was childish.

 

[MCubitt]

Carp/Jim,

Thank you very much for your confimation and corrections where applicable.

I think "MANUF_STRUCTURE_JOURNAL_TAB"'s extent had recently been increased to 67MB but I take your point.

So would I be correct in saying that my 1st mission is to minimise the chance of the max extents being reached, which can be done by changing the extent size to a higher value.

I think the PCT is 0 for all tables, but I am unsure. Additionally, I did not entirely understand the use of PCT. Does the actual size increased = extent size + PCT% of current size? If so, then keeping 0 PCT keeps things simple from my view (even if not efficient!).

Thank you,



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[carp]

You have the PCTINCREASE concept correct. And having all extents the same size is not necessarily inefficient. While an ever-increasing extent size seeks to minimize the overall number of extents required, having all extent sizes within a tablespace makes dynamic allocation of extents more efficient.

 

[SantaMufasa]

And MCubitt,

By no means detracting from the excellent topic treatment that has gone before, there is another alternative you have available to greatly simplify your life and ensure the continued high performance of your database: Create a new tablespace with "extent management local autoallocate", then MOVE the contents of your old tablespace to the new tablespace. I have discovered that the MOVE activity can be amongst the fastest methods of data transfer within Oracle.

I have scripts that handle the move from "old" to "new" (LMT) tablespaces if this alternative intrigues you. There is no impact on your third-party-software-vendor's application.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:15 (14Jul04) UTC (aka "GMT" and "Zulu"), 11:15 (14Jul04) Mountain Time)

 

[carp]

Oh, SURE, Dave! Take the easy way!!!

 

[MCubitt]

Santa,

I have asked our supplier's Oracle expert on their opinion for me doing this and depending on that, we will make a descision. Of course, some may say: If it ain't broke...

Thanks

There's no need for sarcastic replies, we've not all been this sad for that long!

 

[carp]

MCubitt -

Beware the supplier's expert! They don't always know what they're talking about! I had one client whose "supplier expert" got rid of their redundent control files and redo log files in order to "make the application run correctly". Of course, this put the company's entire database at risk for no apparent reason and the application still didn't run correctly, but "their expert said so" so they stayed with just one control file and one redo log file per group.

If they say that transitioning to a LMT would be a bad thing, press them hard for reasons!

 

[SantaMufasa]

MCubitt,

Also keep in mind that you will be asking an "expert" that was probably too ignorant of LMTs in the first place to know how important they are. If they answer correctly ("No, it will not cause any problems to the application,") then that is tantamount to their admitting that the did it wrong in the first place. They may very likely give you some mumbo-jumbo answer that makes it sound like their application depends upon using obsolete Oracle methodology.

So, as Carp says, beware "Supplier's Experts".

Also, if you choose to convert to LMT, let us know so we can offer scripts and advice on how to best accomplish your task.

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:38 (15Jul04) UTC (aka "GMT" and "Zulu"), 08:38 (15Jul04) Mountain Time)

 

[MCubitt]

Carp/Santa,

Thank you for your continued support, help and advice.

I think, to be fair, the supplier probably has a standard load regardless of which version of Oracle is in place. Although this means that each site may not utilise maximum performance from their DB technology, the supplier has an easier and consistent job. I am not saying this approach is right, but is the most likely reason.

I reckon they will say it is ultimately our choice and that they recommend it - unofficially, of course!

I do have 4 databases, 1 live, 1 pilot, 1 test and 1 development so will be able to TRY it out first..

Thanks





There's no need for sarcastic replies, we've not all been this sad for that long!

 

[carp]

I suspect you are right - the current configuration is either an artifact of earlier versions and has not been changed to reflect current technology, or else a "one size fits all" approach to product installation. Either way, they should come up with the response you are anticipating. If they come up with the aforementioned "mumbo jumbo", then rig out all sensors and start pressing them for specific, technical reasons. This will tell you (1)why they don't want you to go to LMT, (2) the level of their expertness, and (3)whether or not to trust their answers on the NEXT issue!

 

[MCubitt]

Very true, very true... )



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

Carp...As usual, very well said, matey

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:15 (15Jul04) UTC (aka "GMT" and "Zulu"), 10:15 (15Jul04) Mountain Time)

 

[mrn]

Dave,

I would be interested in the scripts, as I'm in the process of moving over and was going to use

exec dbms_space_admin.tablespace_migrate_to_local('TSNAME');

but I understand this is not a clean way.

Mike

"A foolproof method for sculpting an elephant: first, get a huge block of marble, then you chip away everything that doesn't look like an elephant.

 

[MCubitt]

Okay, I did get some replies from our supplier.

Answer one:
LMT weren't certified for that release of <THE APPLICATION> at the time of the installation, however they are now. If you want to use LMT's then you will have to do a full database export and import. You cannot migrate the tablespaces you must do export and import.


Answer two:
LMT support was release after your installation unfortunately. The options
we support cannot be changed against the tablespaces these can only be set
when you create them.

This means you would have to export your database re-create using support
options (provided by <THE APPLICATION>) and then re-import your database. Depending on its size this can take some time.


Well, time is something I have none of at the moments, especially to "play" so sadly this will have to wait until a rainy day (or week!).




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[mrn]

I've tried

exec dbms_space_admin.tablespace_migrate_to_local('TSNAME');

on a QA database and it took seconds, it also knock around 2 hours from a 7 hour batchwork run.

Mike

"A foolproof method for sculpting an elephant: first, get a huge block of marble, then you chip away everything that doesn't look like an elephant.

 

[alexhu]

I've just done a test to 9.2.0.5 from 8.0.5 and using the migration tool corrupted the tablespaces :-(

Alex

 

[SantaMufasa]

Alex,

Yes, I have experienced corruptions involving the migration tool. When I'm migrating version (especially major versions) I always export/import for safety's sake.

MCubitt,

Your supplier is giving an incomplete answer. Although exp/imp will work, by no means is that the only or best method. The fastest method is to MOVE tables, REBUILD indexes, and exp/imp tables with LONG columns. Moving and rebuilding is eons faster than exp/imp. I'll guarantee that there is nothing about their application that requires exp/imp and disallows MOVE/REBUILD.

Mike,

Here are my scripts to MOVE tables, REBUILD indexes, and exp/imp tables with LONG columns:

1) Create a new, tablespace(s) that will replace the old tablespace(s):

CREATE TABLESPACE <name> DATAFILE <'file_name'> SIZE <nnM>
AUTOEXTEND ON NEXT <nnM> MAXSIZE <nnnnM>
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

2) Move your tables and indexes from the old tablespace to the new tablespace. The best/easiest method is to use a SQL script that creates a SQL script to do all your moving/rebuilding for you:

set echo off
set feedback off
accept old_tsname prompt "Enter the name of the old (source) tablespace: "
accept new_tsname prompt "Enter the name of the new (target) tablespace: "
set pagesize 0
set verify off
set trimspool on
spool TempMove&old_tsname..sql
prompt Set echo on
prompt set feedback off
select 'alter table '||owner||'.'||segment_name||' /* size: '||sum(bytes)||
' */ move parallel nologging tablespace &new_tsname;'
from dba_extents
where tablespace_name = upper('&old_tsname') and segment_type = 'TABLE'
group by owner,segment_name
order by sum(bytes) desc,owner, segment_name
/
select 'alter index '||owner||'.'||segment_name||' /* size: '||sum(bytes)||
' */ rebuild parallel tablespace &new_tsname;'
from dba_extents
where tablespace_name = upper('&old_tsname') and segment_type = 'INDEX'
group by owner,segment_name
order by owner, sum(bytes) desc, segment_name
/
prompt set feedback on
spool off
prompt
prompt Wrote 'TempMove&old_tsname..sql'
prompt
set pagesize 354

3) If you have any tables with LONG columns, they will not "MOVE"; you must export them, then import them. The following script does the following:
a) Creates scripted commands to export any remaining tables in the old tablespace that did not move as a result of the previous script. (Resulting script: "TempExp<old_tsname>.bat")
b) Creates scripted commands to drop the remaining tables from the old, tablespace and to modify the DEFAULT TABLESPACE for users from the old tablespace to the new tablespace. (Resulting script: "TempDropLongs<old_tsname>.sql")
c) Creates scripted commands to import the objects dumped into "TempExp<old_tsname>.bat". (Resulting script: "TempImp<old_tsname>.bat")

set pagesize 0
set trimspool on
set linesize 500
set feedback off
set verify off
set echo off
accept tsname prompt "Enter the tablespace name that holds the tables with LONGs: "
accept newtsname prompt "Enter the new, target tablespace name: "
accept syspw prompt "Enter the password for SYS: "
spool TempExp&tsname..bat
select 'exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y '
||'file='
||owner||'.'||segment_name||'.dump tables='||owner||'.'||segment_name
||' userid=sys/&syspw@'||instance_name
from dba_extents, v$instance
where tablespace_name = upper('&tsname') and segment_type = 'TABLE'
group by owner,segment_name,instance_name
order by sum(bytes),owner,segment_name
/
spool off
prompt
prompt Wrote "TempExp&tsname..bat"
prompt
spool TempDropLongs&tsname..sql
prompt set echo on
prompt set feedback off
select 'drop table '||owner||'.'||table_name||' cascade constraints;'
from dba_tables
where tablespace_name = upper('&tsname')
order by owner,table_name
/
select 'alter user '||username||' default tablespace &newtsname;'
from dba_users
where default_tablespace = upper('&tsname')
order by username
/
spool off
prompt
prompt Wrote "TempDropLongs&tsname..sql"
prompt
spool TempImp&tsname..bat
select 'imp buffer=15000000 grants=y feedback=1000 full=Y file='
||owner||'.'||segment_name||'.dump '
||' userid=sys/&syspw@'||instance_name
from dba_extents, v$instance
where tablespace_name = upper('&tsname') and segment_type = 'TABLE'
group by owner,segment_name,instance_name
order by sum(bytes),owner,segment_name
/
spool off
set feedback on
set linesize 180
prompt
prompt 'Wrote "TempImp&tsname..bat"'
prompt

4) Successfully run script "TempExp<old_tsname>.bat".
Successfully run script "TempDropLongs<old_tsname>.sql".
Successfully run script "TempExp<old_tsname>.bat"
5) Drop the old, tablespace.
6) From the operating system prompt, rm/erase files that supported the old tablespace.

Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:54 (27Jul04) UTC (aka "GMT" and "Zulu"), 08:54 (27Jul04) Mountain Time)

 

[MCubitt]

Santa,

Thank you for the scripts.. they look very complete and concise.

One question.. this script is, I presume per tablespace, so DATA, INDEXES, TEMP, etc are all processed individually?

We have 11 datafiles for our DATA tablespace, of which only 50% is used (deleted a few VERY large tables!). Do we have to re-create these or can we take the opportunity to consolidate the datafiles - presumably yes?

Or, in the case of LMT is a single datafile used and managed by Oracle, rather than several?

Thank you,






There's no need for sarcastic replies, we've not all been this sad for that long!