TIP 51# : Performance issue after upgrade to 10g.




I faced with a performance issue for a client right after upgrade from 9i to 10g.
We all know that optimizer in 10g is different animal which is supposed to work better and more inteligent.This is almost true but not always.
Some queries after upgrade from 9i to 10g may have performance issue which means they may run slower in 10g.
For figuring out whether or not performance issue is because of new enhanced features in 10g please turn off the following options and run query in 10g.

alter session set "_optimizer_cost_based_transformation" =off; (Disable subquery unnesting and view merging -- New 10g optimizer feature).

alter session set "_gby_hash_aggregation_enabled" = FALSE; (Disable Hash group by aggregation -- New 10g optimizer feature).

If disabling above options resolve the situation, the performance issue is definitely because of new features in 10g optimizer. Otherwise, optimizer would not be culprit.


TIP #50: Opatch failed after uncompleted/interrupted patch

I faced with a problem today during my AS 10g upgrade which involves applying different patches sequentially.
Opatch failed with the following message :

Problems with the lock file
Lock file exists, details are:
Interim Patch is holding the lock from xxxxxxx,
probably due to previous unsuccessful operation

ERROR: OPatch failed during pre-reqs check


Opatch creates patch_locked in $ORACLE_HOME/.patch_storage during running of patch.If for some reason opatch run was interrupted or canceled , this file may not be removed.As the result, further opatch run can find this patch and will fail because of this file. Solution is to delete this file.