Intermediate SQL Color Coded SQL, UNIX and Database Essays

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 …

12Sep/1030

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:

Note
-----
   - SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement

This seems to mean that:

  1. An external ‘helper’ object was used during evaluation of your SQL statement
  2. 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 ?

19Aug/105

Can ORACLE 11g memory_target work with AIX large pages ?

One of my readers recently asked me if ORACLE 11g can use memory_target along with AIX large pages … (Thanks Randolf!)

At the first blush this seems to be impossible. The main reason, of course, is that memory_target and large pages are used for the purposes that are, in fact, completely opposite!

  • The goal of 11g memory_target is to flow memory efficiently between ORACLE SGA and PGA to the place where it is most needed. This means that SGA may reduce its size from time to time, releasing memory to the operating system
  • AIX large pages, on the other hand, are designed to never leave physical memory

I hope, you can see a contradiction here …

9Jul/103

ORACLE 11g XML alert logs. Surprise: They are actually more convenient to work with

I have to admit, I was initially very skeptical about ORACLE 11g Automated Diagnostic Repository or ADR.

Why do I need to use “special” tool to access database alert log ? (and not vi) Who decided to move trace directories to some weird location ?? (why not keep them where they’ve always been?) Why store alert records as XML ??!! (it is meant for humans, you know …)

These things have “inconvenience” and “pain in the a%%.” written all over them … And, of course, the usual ORACLE attitude: “we decided to change it, so suck it up and learn how it works” did not help much …

Old habits die hard, but lately, I’ve started to come around using ADR. It turns out that with a bit of adrci knowledge and a few tweaks in the environment, working with alert logs from a regular UNIX shell becomes not only manageable but actually (gulp!) much more convenient …

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 …

8Jun/102

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:

  1. SQL Plan Management must be active
  2. SPM Baseline for the SQL must exists (duh!)
  3. SPM Baseline must be ACCEPTED
6Jun/104

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.

23May/101

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.

30Apr/106

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:

SELECT substr(sql_text, 1, 80), COUNT(1)
FROM v$sql
GROUP BY substr(sql_text, 1, 80)
HAVING COUNT(1) > 10
ORDER BY 2
/
26Mar/106

How ORACLE Uses Memory on AIX. Part 4: Having Fun with 11g Memory_target

This is going to be a long post but don’t be discouraged: most of it will involve snapshots and screen examples, so it shouldn’t be too bad … đŸ™‚

Anyway, here is the short recap from the previous 3 posts (Part 1, Part 2, Part 3):

  • ORACLE Instance Memory consists of 2 parts: process memory and shared (SGA) memory
  • Process memory is a bunch of memory segments allocated in individual ORACLE processes and their collective size is (attempted to be) managed by pga_aggregate_target parameter. AIX improves process memory usage by identifying sharable segments (such as program or shared library text) and not duplicating them for each individual process.
  • SGA memory is allocated as a single AIX shared memory segment (which, in reality, turns out to be a bunch of smaller VMM segments) and (in ORACLE 10g) is managed by sga_target and sga_max_size parameters. AIX, by default, helps with shared memory usage by allocating it only as needed. However, you can overwrite this behavior and force AIX to allocate all the shared memory at once and, additionally, put a “pin” on it in order to prevent paging.