Transport Plan from One Database to Another Database in Oracle

Share via:

Dear Readers,

In this article, we will see the Transport Plan from One Database to Another Database in Oracle .

Transport Good Execution Plan(Hash Plan) from one database to another database

ETL was taking longer period of time in Prod database where as in UAT it was completing within 25 minutes.
Optimizer also could not help to recommend a better plan for particular sql id so we found an approach to transport the good execution plan from UAT to Prod.

1. Create Empty Tuning Set

2. Load the SQL Information for the SQL ID into this Tuning Set

3. Display the content of Sql Tuning set

4. Create a Staging table to hold the exported SQL Tuning set

5. Load the SQL Tuning Set information to the Staging Table

6. Export of The table

7. Import of the Staging table

8. Unpack the SQL Tuning set from the staging table to the destination server

9. Load the plan from SQL Tuning Set to SQL Plan Baseline

10. Flush the two SQLs from the shared pool, so that the optimizer will pick the new plan

11. To verify Baseline created in the database

12. Drop SQL tunung Set from Source


Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook :
LinkedIn :
Twitter :
YouTube :
Instagram :


Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Add Comment