Oracle Data Pump
Technology evolves for the betterment and simplification of the existing task .
What is Oracle Data Pump?
Oracle Data Pump is a more up to date, quicker and increasingly adaptable option to the “exp” and “imp” utilities utilized in past Oracle renditions. Notwithstanding essential import and fare usefulness information siphon gives a PL/SQL API and backing for outer tables.
Oracle® Data Pump (expdp, impdp) is a utility for sending out and bringing in database questions in and between databases. Albeit most database heads know about Data Pump, support for the multi-inhabitant design in Oracle Database 12c has made changes to how Data Pump fares and imports data.
Data Pump Performance
The Data Pump utilities are designed especially for very large databases. If your site has very large quantities of data versus metadata, then you should experience a dramatic increase in performance compared to the original Export and Import utilities. This chapter briefly discusses why the performance is better and also suggests specific steps you can take to enhance the performance of export and import operations.
This blog contains the following section
- Data Performance Improvements for Data Pump Export and Import
- Tuning Performance
- Initialization Parameters That Affect Data Pump Performance
Execution of metadata extraction and database object creation in Data Pump Export and Import remains bcomparable tothat of the first Export and Import utilities.
Data Performance Improvements for Data Pump Export and Import
The improved exhibition of the Data Pump Export and Import utilities is owing to a few variables, including the accompanying:
*Multiple worker processes can perform iinsertableaand interpretationparallelism to load and unload tables in multiple, parallel, direct-path streams.
*For very large tables and partitions, single worker processes can choose intrapartition parallelism through multiple parallel queries and parallel DML I/O server processes when the external tables method is used to access data.
*Data Pump uses parallelism to build indexes and load package bodies.
*Dump files are read and written directly by the server and, therefore, do not require any data movement to the client.
*The dump file storage format is the internal stream format of the direct path API. This format is very similar to the format stored in Oracle database data files inside of tablespaces. Therefore, no client-side conversion to INSERT statement bind variables is performed.
*The supported data access methods, direct path, and external tables, are faster than conventional SQL. The direct path API provides the fastest single-stream performance. The external tables feature makes efficient use of the parallel queries and parallels DML capabilities of the Oracle database.
*Metadata and data extraction can be overlapped during export.
Tuning Performance
Data Pump technology fully uses all available resources to maximize throughput and minimize elapsed job time. For this to happen, a system must be well balanced across CPU, memory, and I/O. Also, standard performance tuning principles apply. For example, for maximum performance , you should ensure that the files that are members of a dump file set reside on separate disks, because the dump files are written and read in parallel. Also, the disks should not be the same ones on which the source or target tablespaces reside.
Any performance tuning activity involves making trade-offs between performance and resource consumption.
Controlling Resource Consumption
The Data Pump Export and Import utilities enable you to dynamically increase and decrease resource consumption for each job. This is done using the PARALLEL parameter to specify a degree of parallelism for the job. (The PARALLEL pthe arameter is the only tuning parameter that is specific to Data Pump.) For maximum throughput, do not set PARALLEL to much more than twice the number of CPUs (two workers for each CPU).
As you increment the level of parallelism, CPU usage, memory consumption, and I/O data transfer capacity usage likewise increment. You must ensure that adequate amounts of these resources are accessible. IIfimportant, you can distribute files crosswise over various circle gadgets or channels to get the required I/O data transmission.
To expand parallelism, you must supply at any rate one file for every level of parallelism. The least complex method for doing this is to use substitution factors in your file names (for instance, file%u.dmp). In any case, contingent on your circle set up (for instance, straightforward, non-striped disks), you might not have any desire to put all dump files on one gadget. For this situation, it is ideal to determine multiple ffilenamesusing substitution factors, with each in a different catalog setting out to a different circle. Indeed, even with fast CPUs and fast disks, the way between the CPU and the plate might be the obliging component in the level of parallelism that can be sustained.
The PARALLEL parameter is substantial just in the Enterprise Edition of Oracle Database 11g.
Initialization Parameters That Affect Data Pump Performance
The settings for certain initialization parameters can affect the performance of Data Pump Export and Import. In particular, you can try using the following settings to improve performance, although the effect may not be the same on all platforms.
DISK_ASYNCH_IO=TRUEDB_BLOCK_CHECKING=FALSEDB_BLOCK_CHECKSUM=FALSE
The following initialization parameters must have values set high enough to allow for maximum parallelism:
PROCESSESSESSIONSPARALLEL_MAX_SERVERS
Additionally, the SHARED_POOL_SIZE and UNDO_TABLESPACE initialization parameters should be generously sized. The exact values depend upon the size of your database.
Setting the Size Of the Buffer Cache In a Streams Environment
Oracle Data Pump uses Streams functionality to communicate between forms. IIfthe SGA_TARGET istatementparameter is set, at that point ,the STREAMS_POOL_SIZE introduction parameter is automatically set to a sensible value.
On the off chance that the SGA_TARGET instatement parameter isn’t set and the STREAMS_POOL_SIZE introduction parameter isn’t characterized, at that point the size of the streams pool automatically defaults to 10% of the size of the common pool.
At the point when the streams pool is made, the required SGA memory is taken from memory distributed to the buffer store, reducing the size of the reserve to not as much as what was determined by the DB_CACHE_SIZE instatement parameter. This implies on the off chance that the buffer reserve was configured with just the insignificant required SGA, at that point Data Pump activities may not work appropriately. A minimum size of 10 MB is prescribed for STREAMS_POOL_SIZE to ensure successful Data Pump activities.
Thanks for reading!!