We have successfully migrated 600 Gb database for a banking client from
SUN to AIX in 7 hrs.we had several pilot runs before the final ball.

we used following parameters during export

db_file_multiblock_read_count=20480
db_file_multiblock_io_count=128

and for import
parallel_max_servers=60
parallel_automatic_tuning=TRUE


AIX box had just 7 cpu's.
export ON the basis of table size run parallely in 7 diff sessions.(3
hrs)
import without indexes (for big tables) in 7 diff session.(4 hrs)
index creation for big tables (2 hrs)

Interesting number..

What kind of disk subsystem is/was the database sitting on, may I ask?
And have you do any tweaking on it before and during this import
export exercise?

Thanks.
"Kris" <krishnaprasadn@gmail.com> wrote:

>We have successfully migrated 600 Gb database for a banking client from
>SUN to AIX in 7 hrs.we had several pilot runs before the final ball.
>
>we used following parameters during export
>
>db_file_multiblock_read_count=20480
>db_file_multiblock_io_count=128
>
>and for import
>parallel_max_servers=60
>parallel_automatic_tuning=TRUE
>
>
>AIX box had just 7 cpu's.
>export ON the basis of table size run parallely in 7 diff sessions.(3
>hrs)
>import without indexes (for big tables) in 7 diff session.(4 hrs)
>index creation for big tables (2 hrs)

Kris, I think the db_file_miltiblock_read_count is overkill considering
the fact no OS I know of supports an IO of this size.

>From both the version 9.2 and 10g Reference manual entries for the
database parameter:

>> The maximum value is always less than the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum. <<

On most systems the maximum is 128K and on AIX I have heard but have
never verified that AIX can do a 1M IO. The disk unit might limit the
IO size also.

HTH -- Mark D Powell --

"Kris" <krishnaprasadn@gmail.com> wrote in message
news:1132594695.027155.258340@o13g2000cwo.googlegr oups.com...
> We have successfully migrated 600 Gb database for a banking client from
> SUN to AIX in 7 hrs.we had several pilot runs before the final ball.
>
> we used following parameters during export
>
> db_file_multiblock_read_count=20480
> db_file_multiblock_io_count=128
>
> and for import
> parallel_max_servers=60
> parallel_automatic_tuning=TRUE
>
>
> AIX box had just 7 cpu's.
> export ON the basis of table size run parallely in 7 diff sessions.(3
> hrs)
> import without indexes (for big tables) in 7 diff session.(4 hrs)
> index creation for big tables (2 hrs)
>

Mmm ... I did some tests on AIX 5L recently (with storage on Symmetrix) and
couldn't find any performance improvements with DMRC > 32. So i'm really
puzzled by that great number.
Other question : how did you calculate the table and index statistics after
the import ?

Matthias

Mark,

One can obtain an IO size of 1024 kilobytes even on win32 for file
operations such as a full table scan or a fast full index scan. Agreed
that the values he posted fail a sanity check and will likely be
ignored or sanitized.

Whether or not one obatins anything close to that is a matter of
testing it out in your environment. In practice, even for a carefully
constructed test case, the average read size was less than 50% of the
theoretical limit (8192 db_block size, dbfmbrc = 128). Reads such as
block headers, smaller extents, block in the buffer cache cause the
reads to be smaller than the dbfmbrc.

Grab a copy of filemon.exe from sysinternals to log the filesystem
calls.
It allows you to filter on what files of interest you wish to log
access to.

Jonathan Lewis covers this in detail in his latest book and elsewhere.


SQL> show parameter db_file_multiblock_read_count

NAME TYPE VALUE
------------------------------------ ----------- -----
db_file_multiblock_read_count integer 32

1 select pname, pval1
2 from sys.aux_stats$
3 where pval1 is not null
4* order by 1
SQL> /

PNAME PVAL1
------------------------------ ----------
CPUSPEED 587
CPUSPEEDNW 1051.42512
FLAGS 1
IOSEEKTIM 10
IOTFRSPEED 4096
MAXTHR 3555328
MBRC 7
MREADTIM 10.513
SREADTIM 4.536

9 rows selected.

10.1.0.4 std ed. 32 bit
w2k adv svr sp4
HP DL530G2
dual xeon mp 2.4 GHz
datafiles exclusively on an 8 drive RAID 10 volume, stripe size 256 KB

-bdbafh

I agree, in fact I have done extensive testing on AIX and found that
the optimal max i/o size is 64K which with 8k blocks would means a
DFMBRC of 8. I found that 64K will make full block I/Os on any size
object segments , 64, 128, 256, 512, 1024, 2048, 4096, etc. You can
certainly set a higher max I/O size but the fact is most of your I/O is
smaller so setting a scattered read to 1Mb means a lot of extra I/O if
you are reading a 64k extent.

bdbafh@gmail.com wrote:

> 1 select pname, pval1
> 2 from sys.aux_stats$
> 3 where pval1 is not null
> 4* order by 1
> SQL> /
>
> PNAME PVAL1
> ------------------------------ ----------
> CPUSPEED 587
> CPUSPEEDNW 1051.42512
> FLAGS 1
> IOSEEKTIM 10
> IOTFRSPEED 4096
> MAXTHR 3555328
?????????????????????????????????????
> MBRC 7
?????????????????????????????????????
> MREADTIM 10.513
> SREADTIM 4.536
>
> 9 rows selected.
>
> 10.1.0.4 std ed. 32 bit
> w2k adv svr sp4
> HP DL530G2
> dual xeon mp 2.4 GHz
> datafiles exclusively on an 8 drive RAID 10 volume, stripe size 256 KB
>

I don't get it. Why "7"? And why is it different from
the set MBRC in init.ora?

well i had guesssed that everyoen wd b surprised to see those number.

We had done around 10 pilot runs b4 we did the final migration.
object validity and table count was also matched during each and every
run.
index creatino of big tables took around an hr as i said earlier.


as far as the parametes go - we did some research on the net abt the
same. but we also tested them during the dry runs and found great
improvements.

storage was hitachi san - dunno the number.

Kris wrote:

> We have successfully migrated 600 Gb database for a banking client from
> SUN to AIX in 7 hrs.we had several pilot runs before the final ball.
>
> we used following parameters during export
>
> db_file_multiblock_read_count=20480
> db_file_multiblock_io_count=128
>
> and for import
> parallel_max_servers=60
> parallel_automatic_tuning=TRUE
>
>
> AIX box had just 7 cpu's.
> export ON the basis of table size run parallely in 7 diff sessions.(3
> hrs)
> import without indexes (for big tables) in 7 diff session.(4 hrs)
> index creation for big tables (2 hrs)

Hi Kris,

Thanks for "posting", it is interesting to know (I always curious ,how
long it take to import that big data.
And I understand, I have different hardware as you. But nice to know).

I am current using the same disk as you (I think).
I am using the hitachi san too.

Someday, I might have to move the oracle off from SUN to Linux
platform. (this is my guessing only)

Though, if I finish upgrade my production db to oracle 10g r2, I could
use
transportation tablespace to move off to a different platform, instead
of running export and import.

My db is around 300Gig.



Dominica

Hi Kris,

Thanks for "posting", it is interesting to know (I always curious ,how
long it take to import that big data.
And I understand, I have different hardware as you. But nice to know).

I am current using the same disk as you (I think).
I am using the hitachi san too.

Someday, I might have to move the oracle off from SUN to Linux
platform. (this is my guessing only)

Though, if I finish upgrade my production db to oracle 10g r2, I could
use
transportation tablespace to move off to a different platform, instead
of running export and import.

My db is around 300Gig.



Dominica