Performance tunning after database refresh
753231Dec 7 2011 — edited Dec 7 2011Hi DBas,
Everything looks good in DB 11g (optimizer version 10.2). All queries returns within seconds. I exported the data (from good database) and
imported my data using datapump into another 11g (opitmizer verison 11.2.0.2). Serveral queries then suffering and hung up (data not being returned).
This is the roadmap to fix most of the queries:
1- Set ACCEPT_SQL_PROFILES to a value "true".
2- Set optimizer_capture_sql_plan_baselines to value "true".
3- Serveral queries were slow and were hung. collected statistics for the schema.
4- The slow queries automatically fixed by oracle using auto tune in 11g. after few days though.
4- I fixed most slow queries manually using tuning tasks (by creating a profile, sql plan, and baselines....) it fixed dozen SQLs. all working well now.
5- Now after more than a week (auto tune is still enabled) I have only few queires that hung when being executed.
6- Manually created a SQL profile for the hung SQLs. and also 2 SQL plan history exist (for a given sql) in the data dictionary.
7- I did read something about force_match (when creating SQL profiles) with no success.
Any suggesstion would be appreciated to make the long running queries work with optimizer 11.2.0.2.
Thanks in advance