Home » RDBMS Server » Server Utilities » Importing from 9i to 11g with compressed extents creating extents on target (Oracle 11g)
Importing from 9i to 11g with compressed extents creating extents on target [message #481348] Tue, 02 November 2010 12:54 Go to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
Good afternoon,
We are migrating from a 9i db to 11g and we've been testing our apps on a similar (but not exact) machine as our production box.

Normally when we take a full export of the production data (on 9i) and import it into another 9i DB, the tables and indexes are created with the initial size large enough to hold the entire table. We also do our export with the compress extents param set to 'Y'.

However, we've noticed that when we import our data into the 11g DB, that tables are being created with multiple extents...sometimes up to 10 or 15. This seems to happen even with tables that don't even have extents on db that the export was taken from.

There ARE some differences in our 11g DB that i imagine might be the culprit, i've just been unable to narrow one of them down.

the differences i know of are:

a) the target DB has locally managed tablespaces while the source 9i DB had dictionary managed tablespaces

b) the block size is larger on the target 11g DB. 8192 vs 2048

c) the nchar character set on the source DB is AL16UTF16 and the target is UTF8 (we actually only have an nchar column in one of our tables...and also, the UTF8 setting was actually a mistake that we're correcting this weekend with a fresh DB and fresh import)

d) ??

does anyone have any idea what would cause the import to produce all these extra tablespaces? Or have a recommendation of what to research to figure it out?

Thanks a ton!
Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481349 is a reply to message #481348] Tue, 02 November 2010 12:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>does anyone have any idea what would cause the import to produce all these extra tablespaces?
What "extra tablespaces"?

>Or have a recommendation of what to research to figure t out?
Stop worrying & accept that Oracle knows best ( or at least better than you).
You do not need to "fix" something that is NOT broken.
Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481350 is a reply to message #481348] Tue, 02 November 2010 13:00 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
another note...
the target machine is also 64 bit while the source was 32 bit, if that would make a difference.

Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481351 is a reply to message #481348] Tue, 02 November 2010 13:01 Go to previous messageGo to next message
joy_division
Messages: 4950
Registered: February 2005
Location: East Coast USA
Senior Member
chris32680 wrote on Tue, 02 November 2010 13:54
Good afternoon,


does anyone have any idea what would cause the import to produce all these extra tablespaces? Or have a recommendation of what to research to figure it out?



Easy answer. sorry to sound blunt, but who cares. Thousands, even ten of thousands of extents make no difference. This is something that went away with Oracle 8i. Why you have DMT in 9i is a good question.
I'd say really, move on and ignore extents. You'll spend many wasted days trying to come up with a combination of export, import, pre-creations to do what you want.
Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481352 is a reply to message #481349] Tue, 02 November 2010 13:07 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
BlackSwan wrote on Tue, 02 November 2010 13:57
>does anyone have any idea what would cause the import to produce all these extra tablespaces?
What "extra tablespaces"?

>Or have a recommendation of what to research to figure t out?
Stop worrying & accept that Oracle knows best ( or at least better than you).
You do not need to "fix" something that is NOT broken.


sorry...not "extra tablespaces". i meant multiple extents.

When we export a table from the source DB to another 9i DB, the table is created in one piece with no more than the 1 extent. Even if that table had multiple extents on the source.

But importing the table on the 11g DB will create the table (that never had any extents in the first place) with sometimes way more than 1 extents.

And, trust me...i DO accept that Oracle knows best. I just know that me telling my superiors to "stop worrying, Oracle knows best" will not go over very well. Smile


Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481354 is a reply to message #481352] Tue, 02 November 2010 13:10 Go to previous messageGo to next message
joy_division
Messages: 4950
Registered: February 2005
Location: East Coast USA
Senior Member
Then ask them WHY they want a single extent. Have them give you legitimate reasons backed up by empirical data that proves you need 1 extent. Again, the higher you go up, the less you know and more you want to bud into people's business who know how to do their jobs. It's seen all too often.
Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481356 is a reply to message #481352] Tue, 02 November 2010 13:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
If you REALLY, Really, really insist on not having multiple extents in the tables,
the you can manually (pre)CREATE TABLE before starting the import specifying IGNORE=Y

Some folks suffer from Compulsive Tuning Disorder!
Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481360 is a reply to message #481352] Tue, 02 November 2010 13:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>But importing the table on the 11g DB will create the table (that never had any extents in the first place) with sometimes way more than 1 extents.

Unless you can give specific versions of Oracle 11g,
Tablespace options you have used, we can only guess.
Very generically speaking,
LMT can be uniform or auto-allocated.
If Oracle cannot fit it one extent, it is going to allocate in more than one.

As others have already told,
Many extents is not bad.
Compress=Y was very annoying even with pre 9i and always not recommended to be used.
Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481361 is a reply to message #481354] Tue, 02 November 2010 13:18 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
Thanks for the responses.

I think my question came across too much as 'how can we prevent this' instead of 'why does it do this'. It's not that we don't want to let Oracle manage it the best way, we just like to understand why. I think the belief (for us) had always been that having a lot of extents would affect performance (even if minimally), and when doing a fresh import, we always assumed (because it had in the past) that the tables would be created in one large chunk with just the next extent allocated.

So from what I'm gathering, we need to read up on how post-8i, Oracle manages extents and what effect, if any, it has on performance.

Thanks again.
Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481362 is a reply to message #481361] Tue, 02 November 2010 13:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>So from what I'm gathering, we need to read up on how post-8i, Oracle manages extents and what effect, if any, it has on performance.
For what?

You are going to 11g. Just do not use compress=y.

[Updated on: Tue, 02 November 2010 13:20]

Report message to a moderator

Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481369 is a reply to message #481361] Tue, 02 November 2010 13:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
  1  select blocks, count(*) from dba_extents
  2* group by blocks order by 1
SQL> /

    BLOCKS   COUNT(*)
---------- ----------
	 8	10280
	16	    7
       128	 1014
      1024	   35
Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481378 is a reply to message #481348] Tue, 02 November 2010 15:25 Go to previous messageGo to next message
John Watson
Messages: 8592
Registered: January 2010
Location: Global Village
Senior Member
Perhaps your locally managed tablespaces were created with uniform extent size?
select TABLESPACE_NAME,ALLOCATION_TYPE from dba_tablespaces;

In that case, your segments will be divided into multiple extents. I often use a uniform extent size: theoretically, it makes the bitmaps more efficient (though I have never been able to prove this.)
Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481431 is a reply to message #481348] Wed, 03 November 2010 09:02 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
Thanks John.

So if we allow Oracle to manage the extents, does the DB keep an object from getting the ORA-1631 error for max # of extents reached?

Like i said before, we're not against letting the DB do the work for us...we just like to know WHAT it's going to do so that we're no surprised on our production machine.

Thanks again!
Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481445 is a reply to message #481431] Wed, 03 November 2010 10:01 Go to previous messageGo to next message
John Watson
Messages: 8592
Registered: January 2010
Location: Global Village
Senior Member
Have you tried testing the effect of setting and exceeding max_extents for a segment in a locally managed tablespace?
Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481471 is a reply to message #481431] Wed, 03 November 2010 12:52 Go to previous messageGo to next message
joy_division
Messages: 4950
Registered: February 2005
Location: East Coast USA
Senior Member
chris32680 wrote on Wed, 03 November 2010 10:02

So if we allow Oracle to manage the extents, does the DB keep an object from getting the ORA-1631 error for max # of extents reached?


Are you still using MAX_EXTENTS in your databases?
Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481483 is a reply to message #481471] Wed, 03 November 2010 14:48 Go to previous messageGo to next message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
> Are you still using MAX_EXTENTS in your databases?

No, we don't specify max extents on our tables/indexes...but we have run into the 121 limit that prevents anymore from being created.

Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481485 is a reply to message #481483] Wed, 03 November 2010 14:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
About the MaxExtents, normally with LMT this parameter is set to 2 billions so I doubt you'll hit this limit.
Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481493 is a reply to message #481485] Wed, 03 November 2010 15:18 Go to previous messageGo to next message
John Watson
Messages: 8592
Registered: January 2010
Location: Global Village
Senior Member
Forget it, man. It's deprecated.
jw> create table t1(c2 date) storage (maxextents 1);

Table created.

jw> alter  table t1 allocate extent;

Table altered.

jw> alter  table t1 allocate extent;

Table altered.

jw> select count(*) from user_extents where segment_name='T1';

  COUNT(*)
----------
         3
jw> select max_extents from user_segments where segment_name='T1';

MAX_EXTENTS
-----------
 2147483645

Re: Importing from 9i to 11g with compressed extents creating extents on target [message #481495 is a reply to message #481493] Wed, 03 November 2010 15:21 Go to previous message
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
Member
yeah, tha'ts what i'm finding out. i REALLY appreciate all you guys' help on this.

I'm fairly new to this, so i'm trying to teach myself the best i can. This board has been extremely helpful.

Thanks
Previous Topic: Loading data into tables
Next Topic: expdp oracle directory and stays on defining never executing
Goto Forum:
  


Current Time: Fri Sep 17 07:11:13 CDT 2021