DB Version : 19c (19.14.0.0) on RAC
OS : Oracle Linux 7
I have an expensive SQL query on Prod DB and I have executed tuning advisor, which suggested to accept a SQL profile which will generate a better new plan (with new plan_hash_value). Before accepting the SQL Profile in Prod, I want to copy the new plan to my non-prod database and apply it and test it. I know the steps how to copy the execution plan from a plan_hash_value using a tuning set & Staging Table and export/import process.
However, in this case, since the SQL profile from the tuning advisor is not accepted, the new plan_hash_value is not available or attached to the SQL ID yet. So, I am not able to copy the new plan using the known steps.
Does anyone know, how to copy the SQL profile from tuning advisor recommendation to Non-Prod DB, before accepting the recommendation on Prod ?
Any help is greatly appreciated.