Thanks to Tim Hull for organizing this day – I’m happy to give back to the community which has given me so much.
My favorite feature is with no doubt is the Data Guard.
Even though the Data Guard feature might looks like an excess of other features (redo log for recovery, archive log for backup) it is one of the more awesome features in the Enterprise Edition database.
For those of you who don’t (more...)
From version 11.2 onward, the PARALLEL hint supports two syntaxes: object-level and statement-level. The object-level syntax, which is the only one available up to version 11.1, overrides the DOP associated to a tables. The statement-level syntax can not only override the PARALLEL_DEGREE_POLICY initialization parameter at the SQL statement level, but also force the utilization of parallel processing.
The statement-level PARALLEL hint supports the following values:
- PARALLEL(DEFAULT) forces the default DOP. Note that the (more...)
Use of ANSI SQL is a personal thing.
Historically I have not been a fan apart from where it makes things easier/possible.
This reticence was mainly due to optimizer bugs and limitations in the earlier days.
Recently I have been using it much more because I find that the developers I interact with prefer it / understand it better.
You might/should be aware that Oracle will rewrite ANSI SQL to an Oracle syntax representation, this (more...)
As usual the latest version can be downloaded here.
This version comes only with minor changes, see the change log below.
Here are the notes from the change log:
- Finally corrected the very old and wrong description of "wait times" in the script comments, where it was talking about "in-flight" wait events but that is not correct. ASH performs a "fix-up" (more...)
The concept of cursor sharing is simple. If an application executes SQL statements containing literals and if cursor sharing is enabled (i.e. CURSOR_SHARING=FORCE), the database engine automatically replaces the literals with bind variables. Thanks to these replacements, hard parses might be turned into soft parses for the SQL statements that differ only in the literals.
The question raised by the title of this post is: in case cursor sharing is enabled, does literal replacement (more...)
Why you might want to think twice about using INSERT ALL.
One of those things I knew and then forgot.
So, let’s say you’ve got three tables or a partitioned table or something like that.
Let’s use regional tables for simplicity.
drop table t1_r1; drop table t1_r2; drop table t1_r3; create table t1_r1 (col1 varchar2(2) not null ,col2 number not null ,check( col1 in ('R1'))); create table t1_r2 (col1 varchar2(2) not null ,col2 number not (more...)
This blog post is more of a note for myself on configuring ASMLib.
ASMLib is an optional utility that can be used on Linux systems to manage Oracle ASM devices.
ASMLib consists of the following components:
An open source (GPL) kernel module package: kmod-oracleasm
An open source (GPL) utilities package: oracleasm-support
A closed source (proprietary) library package: oracleasmlib
On my Oracle VMBox, I performed the below steps –
1. Check the installed packages related to (more...)
For most of our database set-ups we use a different TEMP space for application users than for end-user/support/developer/reporting usage.
The intention is to minimise the potential impact of a rogue ad-hoc query on the main applicaiton.
However, turns out this is ineffective IF you use:
ALTER SESSION SET CURRENT_SCHEMA = main_app_schema;
This is documented but I wasn’t previously aware.
No surprise that this learning opportunity was presented as a direct result of a rogue query (more...)
They recently came up with a re-write of a core application functionality. Part of this code did start the same code path for different data sets potentially several times concurrently ending up with many (more...)
Snappy title, huh?
Aka: Why a sql plan baseline may be no guarantee of stability.
The other day, a problematic plan flip…
Cue much discussion about plan flips etc.
My thoughts on stability are that the priority for most application owners is stability and predictability but that does not tally with the defaul CBO behaviour and potentially you have to turn off a helluva lot to even get close.
I have (more...)
Plan degradations on upgrade are normal.
This one’s no different.
On further investigation, turned out application was setting optimizer_mode = first_rows somewhere.
First point about this is that first_rows really shouldn’t be used either as a hint or as an optimizer_mode.
What does FIRST_ROWS mean?
From 11g doco:
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
If any sort of (more...)
Below is a SQL statement from a performance problem I was looking at the other day.
This is a real-world bit of SQL which has slightly simplified and sanitised but, I hope, without losing the real-worldliness of it and the points driving this article.
You don’t really need to be familiar with the data or table structures (I wasn’t) as this is a commentary on SQL structure and why sometimes a rewrite is the best (more...)
It is well-known that AWR, and Statspack before, take snapshots of V$ views (or rather the underlying objects) to produce the data in AWR.
It is also well-known that, when considering sql and its statistics in the shared pool, if something big hitting happens but the big-hitter is no longer in the shared pool by the time of the snapshot, then it can’t be recorded in your AWR picture of activity.
But like many things (more...)
Here’s a query which I find useful in order to have a very quick comparison across AWR snapshots of the high level time model statistics.
The numbers should match those in the associated section in the AWR report.
If you feel compulsed, obsessively, with tuning then you may see some blips here and there which then encourage you to dive into the AWR detail for that snapshot.
Or quite often I get in in the (more...)
I had an interesting encounter with latch: cbc contention early this week. During my oncall I received page for Load of 206.81 exceeded threshold of 150. After I logged into server , the server load average was continously increasing and all the top PIDs were of oracle processes. After logging into database, I saw multiple sessions waiting on latch: cache buffer chains wait event
load average: 258.52, 244.27, 226.15 select username,sql_id,event,count(*) (more...)