In my previous post, I mentioned some of the common datapump performance tweaks we see. In this one, I want to talk about one that's never mentioned, and it might be the best of all.
5. ADOP - The last tweak...I don't think I've seen any blog posts or Oracle documentation about this as it applies to datapump...is ADOP-Auto degree of parallelization. This can be a *huge*...by 4X or more...improvement on imports, which is typically where most of your datapump time is spent. This has been around since 11.2, but until recently (12.1,12.2) its been a little difficult to control how parallel things would run at. To enable it, you simply set:
parallel_degree_policy=auto
parallel_min_time_threshold=60
(which means, if the optimizer thinks this statement will take more than 60 seconds, it will consider parallelizing it)
This is nice because quick queries will run without the overhead of parallelism, and long running queries might find value in parallelism.
Today in 12c, we have the parallel_degree_level parameter, but in 11.2 we could tweak the parallelism by adjusting max_pmbps in sys.resource_io_calibrate$. From my testing, 200=~parallel 2 or 3. A SMALLER value increases the amount of parallelism (50=~parallel 20.) Effectively this gives us the same effect as the new 12c feature...which is to make Oracle make rational decisions on how parallel the auto parallelism should be.
Datapump is a logical copy (as opposed to a physical backup/restore) so it can't copy the original indexes, it has to rebuild them. If you have a typical import with 10,000 tables and indexes, the last 100 are big, the last 10 are huge. Datapump's parallelism will rip through the small objects very quickly, with one process per object. When the time comes to rebuild the indexes on the huge tables, datapump will again assign one process per index rebuild. When the create index statement is analyzed by the optimizer, it will create it in parallel based on the algorithm derived from max_pmbps (even though the create index statement may be parallel 2 or noparallel). This will save many hours on a large datapump import. Its crazy to see a serialized create index statement with 50 busy parallel slaves...but that's what can happen.
When its done, the indexes all have the original parallel spec they started with. Nothing is any different than it would have been if you hadn't used ADOP (other than it was done much, much faster.)
One word of caution: You have to watch your system resources and parallel limits. If you have datapump running at parallel 50, that means potentially you'll be rebuilding 50 indexes simultaneously. If they're each "large" and the optimizer thinks they'll take over [parallel_min_time_threshold] seconds to rebuild, each of them could be built parallel and you could have hundreds (datapump 50 * adop 50) of parallel processes. This is a wonderful thing if your system can handle it. Depending on your parallel limit parameters, ADOP may queue the statements until you have enough parallel processes...to prevent the system from overloading. IMHO, that's also a wonderful thing, but it may be unexpected. The truly unfortunate situation is when you have them too high. You'll use up your server resources and inefficiently use CPU...and may even swap if you run low on RAM. So test!
...but that's what dry runs are for. I hope this last tweak helps you. I've seen it make miraculous differences meeting otherwise impossible SLA's for datapump export/imports. Two other posts you may want to read are:
1. Gwen Shapira has the best post on it, IMHO)
2. Kerry Osborne has a nice post on the 12c changes.
Previous post -> The last secret tweak for improving datapump performance (Part 1)
This post -> The last secret tweak for improving datapump performance (Part 2)
5. ADOP - The last tweak...I don't think I've seen any blog posts or Oracle documentation about this as it applies to datapump...is ADOP-Auto degree of parallelization. This can be a *huge*...by 4X or more...improvement on imports, which is typically where most of your datapump time is spent. This has been around since 11.2, but until recently (12.1,12.2) its been a little difficult to control how parallel things would run at. To enable it, you simply set:
parallel_degree_policy=auto
parallel_min_time_threshold=60
(which means, if the optimizer thinks this statement will take more than 60 seconds, it will consider parallelizing it)
This is nice because quick queries will run without the overhead of parallelism, and long running queries might find value in parallelism.
Today in 12c, we have the parallel_degree_level parameter, but in 11.2 we could tweak the parallelism by adjusting max_pmbps in sys.resource_io_calibrate$. From my testing, 200=~parallel 2 or 3. A SMALLER value increases the amount of parallelism (50=~parallel 20.) Effectively this gives us the same effect as the new 12c feature...which is to make Oracle make rational decisions on how parallel the auto parallelism should be.
Datapump is a logical copy (as opposed to a physical backup/restore) so it can't copy the original indexes, it has to rebuild them. If you have a typical import with 10,000 tables and indexes, the last 100 are big, the last 10 are huge. Datapump's parallelism will rip through the small objects very quickly, with one process per object. When the time comes to rebuild the indexes on the huge tables, datapump will again assign one process per index rebuild. When the create index statement is analyzed by the optimizer, it will create it in parallel based on the algorithm derived from max_pmbps (even though the create index statement may be parallel 2 or noparallel). This will save many hours on a large datapump import. Its crazy to see a serialized create index statement with 50 busy parallel slaves...but that's what can happen.
When its done, the indexes all have the original parallel spec they started with. Nothing is any different than it would have been if you hadn't used ADOP (other than it was done much, much faster.)
One word of caution: You have to watch your system resources and parallel limits. If you have datapump running at parallel 50, that means potentially you'll be rebuilding 50 indexes simultaneously. If they're each "large" and the optimizer thinks they'll take over [parallel_min_time_threshold] seconds to rebuild, each of them could be built parallel and you could have hundreds (datapump 50 * adop 50) of parallel processes. This is a wonderful thing if your system can handle it. Depending on your parallel limit parameters, ADOP may queue the statements until you have enough parallel processes...to prevent the system from overloading. IMHO, that's also a wonderful thing, but it may be unexpected. The truly unfortunate situation is when you have them too high. You'll use up your server resources and inefficiently use CPU...and may even swap if you run low on RAM. So test!
...but that's what dry runs are for. I hope this last tweak helps you. I've seen it make miraculous differences meeting otherwise impossible SLA's for datapump export/imports. Two other posts you may want to read are:
1. Gwen Shapira has the best post on it, IMHO)
2. Kerry Osborne has a nice post on the 12c changes.
Previous post -> The last secret tweak for improving datapump performance (Part 1)
This post -> The last secret tweak for improving datapump performance (Part 2)
Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog on. Oracle Soa Online course
ReplyDeletenic post dear,
ReplyDeletelatest tips and tricks and much more visit https://mkbloggers.blogspot.com/