Intermediate SQL Color Coded SQL, UNIX and Database Essays

18Feb/112

How to freeze ORACLE database by strace

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 11.2.0.2 on Linux 2.6.18 ×64).

6Oct/103

The troubling global nature of SQL Profiles and SPM Baselines

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:

BEHAVIORSQL ProfileSPM Baseline
“Fire” for object in a different schemaYESYES
“Fire” for object with a different DATAYESYES
“Fire” for object with a different STRUCTUREYESYES
“Fire” when indexes are differentYESMAYBE, will “fire” if the same execution plan is still produced
“Fire” when indexes are missingYESNO
“Fire” when TABLE is replaced by VIEWYESNO
“Fire” when TABLE is replaced by MVIEWYESNO

And, if you do not believe me, read on …

18Jun/1016

ORACLE 11g SQL Plan Management: The Dark Side of SPM. Part 4

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 …

26Dec/092

SQLPlus might show incorrect explain plan

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:

WHERE char_column = :bind

will produce different execution plans with NUMBER and CHAR binds.