We might be using both Direct and Conventional path exports regularly. But here are some more interesting facts about Direct path approach…
1. Oracle introduced direct path export from its 7.3 version
2. Normally export will follow the process of SELECT statement i.e data from disk will be copied to buffer cache and then it will be written to dump file. When we use direct path by specifying DIRECT=Y in export command, then oracle will copy data directly from disk to PGA and from there it is written to dumpfile.
This is the reason why direct path is faster than conventional path
3. To improve the performance of Direct path still further, we can use RECORDLENGTH parameter in export. The values that can be possible for RECORDLENGTH are multiples of OS block size / multiples of DB_BLOCK_SIZE. If we don’t specify RECORDLENGTH and still use direct=y, then oracle will take default OS block size (In most of the env, it is 1024 bytes)
Example : Direct path can be improved by 50% when used with RECORDLENGTH of 64kb
Now, till the time we saw advantages. Lets talk about restrictions of using Direct path
1. Direct path will not work if your export release version is 8.1.4 or lower for LOB’s. Oracle will not take rows export in such case. But from 8i and above, even if you use direct on LOB’s, it will be switched to conventional path
Because of this reason, I always use direct path in my daily usage which makes export faster –> ofcourse only for 9i databases :-)
2. QUERY and BUFFER parameters cannot be used along with DIRECT=Y in export command
3. Direct path will export the data only if NLS_LANG variable is equal to database characterset. If different, it will throw following error
EXP-00041: Export done in server’s UTF8, different from user’s character set WE8ISO8859P1
EXP-00000: Export terminated unsuccessfully.
Hope this information will help you to understand better about Direct path.
1. Oracle introduced direct path export from its 7.3 version
2. Normally export will follow the process of SELECT statement i.e data from disk will be copied to buffer cache and then it will be written to dump file. When we use direct path by specifying DIRECT=Y in export command, then oracle will copy data directly from disk to PGA and from there it is written to dumpfile.
This is the reason why direct path is faster than conventional path
3. To improve the performance of Direct path still further, we can use RECORDLENGTH parameter in export. The values that can be possible for RECORDLENGTH are multiples of OS block size / multiples of DB_BLOCK_SIZE. If we don’t specify RECORDLENGTH and still use direct=y, then oracle will take default OS block size (In most of the env, it is 1024 bytes)
Example : Direct path can be improved by 50% when used with RECORDLENGTH of 64kb
Now, till the time we saw advantages. Lets talk about restrictions of using Direct path
1. Direct path will not work if your export release version is 8.1.4 or lower for LOB’s. Oracle will not take rows export in such case. But from 8i and above, even if you use direct on LOB’s, it will be switched to conventional path
Because of this reason, I always use direct path in my daily usage which makes export faster –> ofcourse only for 9i databases :-)
2. QUERY and BUFFER parameters cannot be used along with DIRECT=Y in export command
3. Direct path will export the data only if NLS_LANG variable is equal to database characterset. If different, it will throw following error
EXP-00041: Export done in server’s UTF8, different from user’s character set WE8ISO8859P1
EXP-00000: Export terminated unsuccessfully.
Hope this information will help you to understand better about Direct path.
No comments:
Post a Comment