To see this working, head to your live site.

Need to copy SQL plan from one Env to another EnV ..

Appl Team told that a SQL which is running well on DEV env and same SQL performance is bad on Prod. Now they want to apply DEV env Explain Plan to Prod ENV SQL Query.

What is the best approach to copy Explain Plan from one env to another env (Dev to Prod) ?

Version 19c

Thanks in Advance

2 answers0 replies

2 Comments

Sachin Bonde

Dec 27, 2021

•

I prefer to run coe_xfr_sql_profile.sql on source DB with input sql_id and Plan# value and which creates .sql file that can copy and run on target DB.

Also we can get outline data of sql query from source DB and run the query on target db with outline data to get that plan on target DB but this can be done if issue with select statement.

Arun Kumar

Dec 22, 2021

•

Here is a great article that demonstrates how to copy the SQL plan: https://docs.rackspace.com/blog/transfer-sql-plans-by-using-sql-plan-baseline/