Search

OakieTags

Who's online

There are currently 0 users and 52 guests online.

Recent comments

Affiliations

Uncategorized

Comparisons

Catching up (still) from the Trivadis CBO days, here’s a little detail which had never crossed my mind before.


where   (col1, col2) < (const1, const2)

This isn’t a legal construct in Oracle SQL, even though it’s legal in other dialects of SQL. The logic is simple (allowing for the usual anomaly with NULL): the predicate should evaluate to true if (col1 < const1), or if (col1 = const1 and col2 < const2). The thought that popped into my mind when Markus Winand showed a slide with this predicate on it – and then pointed out that equality was the only option that Oracle allowed for multi-column operators – was that, despite not enabling the syntax, Oracle does implement the mechanism.

If you’re struggling to think where, it’s in multi-column range partitioning: (value1, value2) belongs in the partition with high value (k1, k2) if (value1 < k1) or if (value1 = k1 and value2 < k2).

Comparisons

Catching up (still) from the Trivadis CBO days, here’s a little detail which had never crossed my mind before.


where   (col1, col2) < (const1, const2)

This isn’t a legal construct in Oracle SQL, even though it’s legal in other dialects of SQL. The logic is simple (allowing for the usual anomaly with NULL): the predicate should evaluate to true if (col1 < const1), or if (col1 = const1 and col2 < const2). The thought that popped into my mind when Markus Winand showed a slide with this predicate on it – and then pointed out that equality was the only option that Oracle allowed for multi-column operators – was that, despite not enabling the syntax, Oracle does implement the mechanism.

If you’re struggling to think where, it’s in multi-column range partitioning: (value1, value2) belongs in the partition with high value (k1, k2) if (value1 < k1) or if (value1 = k1 and value2 < k2).

kksfbc child completion

#555555;">I’ve run into the wait “kksfbc child completion” a few times over the past but found very little written about it. I don’t have an explanation, but I have something that might be as good – a way to reproduce it. By being able to reproduce I at least test theories about it.
I ran a tight loop of

Redo log waits in Oracle

#555555;">

Redo

#555555;">
Redo is written to disk when
#555555;">
User commits
Log Buffer 1/3 full (_log_io_size)
Log Buffer fills 1M
Every 3 seconds
DBWR asks LGWR to flush redo
#555555;">
Sessions Committing wait for LGWR

#555555;">#4e7dbf;" name="TOC-Redo-Log-Wait-Events">Redo Log Wait Events

#555555;">

Redo over multiple weeks

#555555;">I’ve always wanted some sort of calendar view of load where I could see patterns across the same days of the week and same hours of the day and then be able to pick different periods and diff them:

#555555;">#2970a6;" href="http://dboptimizer.com/wp-content/uploads/2011/02/diff_diff_new.png">

#555555;">The first step of such a display would be selecting the data in such a way to represent the graphic. A graphic should be harder to do than a similar, though less powerful, ascii representation.

12c nasty with remote query optimization

We have a fairly common query process, where we run a MERGE command to compare a remote table to a local copy of it, as "poor mans" Golden Gate to bring that table up to date on a regular basis.  [Editors note: Writing MERGE's is more complicated but a lot cheaper than Golden Gate :-)]

After an upgrade to 12c, the performance of some of the MERGE’s went very bad…and you can see what happened with the (sanitised) example below:

The bold part is a join that we’ll be executing on the remote database (mydb). It’s been hinted to run in a particular way.

Testing…the surgeon’s approach

I played a lot of volleyball in a bygone life :-) and subsequently ruined my knees to the extent that I needed surgery. I got a shock when the surgeon (after a series of x-rays and checks) said to me: "Of course, we’ll only know once we’re in there".

So here’s a body part (a knee) that’s had hundreds of thousands of years to evolve, so you’d expect that knees are pretty much the same world wide, yet an experienced and qualified surgeon puts the "we cant be 100% sure" caveat before chopping me open.

Upgrade to 12c … credentials

We did a "real" upgrade to 12c this weekend, where "real" means a production system, as opposed to my laptop, a play VM etc etc :-)

It all went relatively smoothly except for one interesting thing, that I can’t 100% say was caused by the upgrade, but it would appear to be the case.

After the upgrade, our scheduler external jobs started failing.  A quick look in the alert log revealed:

Sun Jun 29 09:26:11 2014
ORA-12012: error on auto execute of job "FIRE_SCHED_DAILY"
ORA-27496: credential "LOCAL_OS_ACCT" is disabled

So its possible (not proven) that upgrading to 12c might disable credentials. In this particular case, the database went from standalone to a pluggable database.

The remedy was the simply drop and recreate the credential

Oracle Meetup

This is just a temporary note to remind London-based Oracle technical folks that the first free evening event arranged by e-DBA will take place this coming Thursday (3rd July), 6:30 – 9:00 pm.

The event is free and includes breaks for beer and food, but you do have to sign up in advance – places are limited. July

The theme for the evening is “Upgrades”: covering general principles (Jonathan Lewis), 12c specifics (Jason Arneil), and tools (Dominic Giles and James Anthony)

Even if you’re not interested in upgrades, you might want to attend if you haven’t heard about Swingbench and SLOB (Silly Little Oracle Benchmark).

Oracle Meetup

This is just a temporary note to remind London-based Oracle technical folks that the first free evening event arranged by e-DBA will take place this coming Thursday (3rd July), 6:30 – 9:00 pm.

The event is free and includes breaks for beer and food, but you do have to sign up in advance – places are limited. July

The theme for the evening is “Upgrades”: covering general principles (Jonathan Lewis), 12c specifics (Jason Arneil), and tools (Dominic Giles and James Anthony)

Even if you’re not interested in upgrades, you might want to attend if you haven’t heard about Swingbench and SLOB (Silly Little Oracle Benchmark).