Thursday, November 10, 2016

How to create manual profile in oracle 10g database?

issue:
oracle 10g database sql execution plan got changed and sql performing badly.
root cause:
oracle picked up the new plan which is a bad plan and same time we have good plan in AWR.
we have to force oracle to pick the good plan. but in 10g we dont have baseline features,so we cant forcefully oracle to pick the new plan.
solution:
In oracle 10g database version we don't have SPM and we have some  other  method to pick  the old plan.

we have SQLT feature is there and using it we can create manual profile by passing the SQL_ID and plan_hash_value.

coe_xfr_sql_profile.sql

SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
it will create new SQL file to create a profile on the database and we can create the profile on the database.
check the profile status using dba_sql_profiles

No comments:

Post a Comment