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
/
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.
How ORACLE Uses Memory on AIX. Part 3: Locking SGA
In the previous post we discussed how ORACLE allocates shared memory for SGA in AIX and one of the conclusions was that AIX does not give all the requested memory to ORACLE instance right away but merely promises it.
While this technique allows to use memory more efficiently and you (at least temporarily), can request more memory for processes and shared segments than what AIX physically has, it also has a rather unpleasant consequence – when we get to the limit of physical memory, AIX will have no choice but to start paging memory.
Paging is not necessarily a bad thing – moving older and not-so-often used data out of memory is something that will be done rather routinely – this is how AIX keeps a healthy system. However, when SGA memory starts to page out (and, more importantly, page back in) things can go bad quickly as, well, ORACLE does not really expect SGA to be a disk based area … (ORACLE would have called it “SDA” if that was the case 
)
You probably know that in the vast majority of configurations, it is strongly advised to size SGA so that it fits entirely into physical memory and never pages out. The question becomes: how can we accomplish that on AIX?
How ORACLE Uses Memory on AIX. Part 2: SGA
In the previous post we discussed memory usage by ORACLE processes and, if you remember, it took us some effort to get to their actual memory usage.
In this post we are going to talk about the other large memory area that ORACLE instance uses – System Global Area or SGA.
In many respects, finding out SGA memory usage is going to be simpler as we have to deal with only one large entity (AIX shared memory segment) instead of many small memory chunks in separate ORACLE processes. But as we will see, this process is still rather “involved” for AIX has a few tricks up its sleeve for shared memory as well.
How much memory is used by ORACLE SGA. Really …
Let’s start with a simple question.
What happens if we try to allocate 12 Gb SGA on a machine with only 8 Gb of physical memory?
How ORACLE Uses Memory on AIX. Part 1: Processes
In this post I am going to talk about how ORACLE allocates and uses memory when running on AIX, but I will also talk about the power of approximation and how it can sometimes be misused for ill purposes
On the outset, ORACLE/AIX memory “deal” seems simple enough – obviously, ORACLE will use memory when it runs and many AIX commands (such as vmstat or ps) will show memory usage both system wide and specific to particular process. But, as always, the devil is in the details and the effect of those “details” may be far from subtle.
So, why don’t we go ahead and find that devil, shall we ?
The straightforward (but naive) approach
Let’s say that we have an ORACLE instance running on one of our AIX servers. How do we find out how much memory it is using ?
How ORACLE Implicit Type Conversion Works. Part 2
Those of you who read my previous post would remember that ORACLE implicit type conversion approach
contradicts common sense, yet, you would probably agree, that there are some excellent reasons for ORACLE to go with it.
This post will explore implicit type conversion further and, in a way, turn everything you read about in part 1 upside down.
If the motto of the first part of this post was: Always question your common sense, the motto of the second part will be: Do NOT accept anything as dogma (even if your read it on this blog )
How ORACLE Implicit Type Conversion Works. Part 1
It is said that when you hear the words this is common sense or everybody knows that you should take notice because when enough people say it – it is likely that the results are exactly the opposite. Especially, when you are dealing with ORACLE
Here is a short puzzle to prove the point.
Imagine that you have a table with ID column that stores, say, 100000 unique numbers from 1 to 100000. The column has a unique constraint (and a unique underlying index to enforce it).
Assume also that the hardware is regular (we are not yet on Exadata), table is large enough, all ORACLE metadata is correct, statistics for both table and indexes are collected and are up-to-date, there are no unusual optimizer directives or nasty bugs etc
Here is the question:
What would be the expected execution plan for the following query: