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...)
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...)
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...)
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...)
My thoughts on SQL plan management decision points:
SQL Patches are also available and not covered in the above flowchart.
Note to self because it’s just one of those date/timezone-related topics which just doesn’t seem to stick…
Epoch/Unix time – See https://en.wikipedia.org/wiki/Unix_time
Unix time (also known as POSIX time or erroneously as Epoch time) is a system for describing instants in time, defined as the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970,
Firstly when converting from Oracle date or timestamp – we need to (more...)