Sometimes when you trace things, you can discover some really interesting (and unexpected) stuff.
For example, here is a simple way to “freeze” your ORACLE database, which I “discovered” while tracing system calls in LGWR process (ORACLE 18.104.22.168 on Linux 2.6.18 ×64).
Did you know that SQL profiles and SPM baselines collected for SQL statements in one schema can apply to “the same” SQL statements in another schema ?
They do ! And, besides, objects that these SQLs apply to do NOT need to be exactly the same … And I’m not just talking about different data values or different number of records … The objects can have different structure! Or, they can actually be of different type, i.e. views instead of tables …
The only things that matter are: SQL text and object names.
This strange behavior is summarized in the table below:
|BEHAVIOR||SQL Profile||SPM Baseline|
|“Fire” for object in a different schema||YES||YES|
|“Fire” for object with a different DATA||YES||YES|
|“Fire” for object with a different STRUCTURE||YES||YES|
|“Fire” when indexes are different||YES||MAYBE, will “fire” if the same execution plan is still produced|
|“Fire” when indexes are missing||YES||NO|
|“Fire” when TABLE is replaced by VIEW||YES||NO|
|“Fire” when TABLE is replaced by MVIEW||YES||NO|
And, if you do not believe me, read on …
With SQL Plan Management being relatively new, it is inevitable that many people run into problems using it.
Most of those problems are caused by our (relative) ignorance: SPM does change the way how ORACLE runs SQL statements and it simply takes time to get used to how it works. Yet, some of the consequences of using SPM are truly bizarre and will surprise many people (including, probably, some ORACLE developers).
In this post I will describe the 3 scenarios where we have recently run into problems using SPM and you can judge for yourself …
Be careful when using SQLPlus to investigate performance problems as it might show you incorrect results in certain conditions.
Here is one example:
In the following scenario, we are trying to find out if a simple expression:
will produce different execution plans with NUMBER and CHAR binds.