How to find if your SQL is using SPM baseline
I’ve always wondered how many of my SQLs are NOT using baselines.
Of course, when you run DBMS_XPLAN.DISPLAY_CURSOR for the statement, it can put a nicely formatted note for you, something like:
-----
- SQL plan baseline "SQL_PLAN_01yu884fpund494ecae5c" used FOR this statement
But can you find it globally for ALL SQLs in the shared pool ?
How to add a hint to ORACLE query without touching its text
If you’ve been a DBA long enough, you’ve probably seen multiple cases where certain SQL queries just refuse to cooperate.
I.e. you KNOW that the query is supposed to use index IDX1, but ORACLE stubbornly decides to take IDX2. Rats!
The usual suspect here is bad statistics and thus the usual advise is: start re-collecting them. But while this might help, the success is far from guaranteed! Moreover, with a ‘real production’ data statistics collection could take hours (days?) and you already have people screaming about slow performance … In other words, you need to fix the problem NOW and waiting until stats collection maybe fixes the problem a few hours down the road is just not an option!
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:
| 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 …
What is the difference between SQL Profile and SPM Baseline ?
-----
- SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement
and SPM Baselines
-----
- SQL plan baseline "SQL_PLAN_01yu884fpund494ecae5c" used FOR this statement
are both relatively new features of ORACLE Optimizer with Profiles first appearing in version 10 and SPM Baselines in version 11.
Both SQL Profiles and SPM Baselines are designed to deal with the same problem: Optimizer may sometimes produce a very inefficient execution plan, and they are both doing it by essentially abandoning the idea that “all SQLs are created equal”. Instead, another idea is put forward: “Some SQLs are special and deserve individual treatment”.
What are SQL Profiles and why do we need them ?
If you use DBMS_XPLAN package to analyze execution plans for your SQL statements (and you really should these days), you might have noticed that at times the following line might be displayed along with your execution plan:
-----
- SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement
This seems to mean that:
- An external ‘helper’ object was used during evaluation of your SQL statement
- This ‘helper’ object changed (or, at least, influenced) its execution plan
While having some extra help is nice, one has to wonder: what exactly is this “profile” object ? What does it do ? And, in a bigger picture, why do we need “external” help evaluating SQL statements at all ?
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 …
ORACLE 11g SQL Plan Management: Creating and Using SPM Baselines. Part 3
In the previous post we learned what SPM building blocks or baselines are. In this post, we are going to see how we can get them to work to provide plan stability for our SQL statements.
In a nutshell, SQL Baselines will be used with any SQL statement when the following 3 conditions are met:
- SQL Plan Management must be active
- SPM Baseline for the SQL must exists (duh!)
- SPM Baseline must be ACCEPTED
ORACLE 11g SQL Plan Management: What exactly is SPM Baseline? Part 2
In the previous post we discussed the WHAT and WHY of ORACLE 11g SQL Plan Management, in this post will will begin discussing the HOW. In my personal experience, in order to understand (and appreciate) any new thing that comes from ORACLE you really need to go down into actual nuts and bolts and see how that thing works.
So, let’s get our hands dirty and go into the actual mechanics of SPM.
ORACLE 11g SQL Plan Management or yet another way why ORACLE may not use your index. Part 1
One of our developers called me recently and said that they had an interesting problem on their hands.
The essence of a problem was that the schema upgrade script misfired and did not create a very important index on one of the largest tables. As a result, critical queries that were supposed to hit that index were now doing full table scans and dragging on for minutes.
The weird thing was that a developer, realizing her mistake, connected to the schema and created the index manually. Yet even after that was done, target queries still full table scanned the table, completely ignoring the index.
A better way to find literal SQLs in ORACLE 10g
Not sure if it is news, but here we go …
I was recently looking at hard parsing SQL statements overtaking one of my systems and needed to find literal SQLs that caused the problem.
The traditional way to look for literal SQLs in ORACLE (at least as it was for me) is to search for statements that are exactly the same in the first N symbols, something like:
FROM v$sql
GROUP BY substr(sql_text, 1, 80)
HAVING COUNT(1) > 10
ORDER BY 2
/