Re: expdp very sllllllllloooooooooowwwww

On Nov 22, 1:05 am, sybra..._at_hccnet.nl wrote:
> On Fri, 21 Nov 2008 19:00:16 -0500, dk <kigh..._at_bellsouth.net> wrote:
> >I have a database with quite a few partition tables.  Each of the tables
> >has 751 partitions.  In addition the tables have about 30millions rows each.
>
> >Why would expdp be so slow it looks like i†'s not moving(status command).
>
> >I use the parallel option.
>
> >I've opened a SR with oracle suppor† - no luck.
>
> >ANy ideas would be greatly appreciated!
>
> Let me guess: all partitions are located on one single disk.
>
> My experience is anything parallel doesn't increase throughput, when
> parallel results in an I/O bottleneck.
>
> The common approach to answer any performance problems is to start
> answering the question
>
> What is it waiting for
>
> by querying v$sesstat, v$sess_io, v$session_waits and maybe
> v$session_long_ops.
> Apparently you didn't conduct that investigation yet.
> Please start investigating and report back later.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

What degree of parallelism was used? How many cpu are available? How many disks are the data files spread over? How many channels into the disk exist? What is the rated capacity of the disk?

Actually what Sybrand said can be true even when the data is spread over what appear to be multiple disks. One risk when using PQO and other parallel operations is that you can allocate too many parallel sessions for the resources available to support the database activity to manage well. It is not just the number of disks available but also the number of channels (IO paths) to the disk, number cpu's, and memory.

Another not always apparent issue is the true physical disk layout behind the file systems. While the LUNs that the database data files and backup directories are on are different it is possible that some or all of the data files are allocated to portions of the same physical disks. This results in a lower limit to the benefits of parallel processing than would seem apparent. This happens because some SAN's management software handles the actual allocation of LUNs. The administrator just asks for space and the software determines where it comes from. While the system may have been set up correctly initially so the backup area and database data files areas did not share any physical disks with additional space allocation over time this may no longer be true and could be an issue.

Finally while it is possible all the backup area and database data files are on different physical disk, that sufficient IO capacity exists to select the data that the backup target may not have enough disks to support accepting all the data as fast as you can pull it from Oracle. This means you need to set the degree of parallelism not based on what Oracle can read but rather on what the target can support.

HTH -- Mark D Powell -- Received on Sat Nov 22 2008 - 08:32:37 CST