September 2017

SystemTap and Oracle RDBMS: I/O and Network Traffic

Now that I am able to aggregate SytemTap probes by Oracle database, let’s focus on I/O and Network Traffic.

For this purpose a new SystemTap script (traffic_per_db.stp) has been created and has been added into this github repository.

traffic_per_db

This script tracks the I/O and Network traffic per database and also groups the non database(s) traffic.

Oaktable World 2017 @ Oracle Open World

 

http://www.oaktable.net/blog/oak-table-world-2017-oracle-open-world

The Oak Table members will be discussing their latest technical obsessions and research on Monday and Tuesday, (Oct. 2nd and 3rd, 2017).  The truth is, folks-  The Oak Table experts are an AWESOME group, (if I don’t say so myself! :)) as we could have easily done another day of incredible sessions, but alas, two days is all we have available for this year’s event.

 

Screen Shot 2017-09-29 at 11.10.53 AM

“_suppress_identifiers_on_dupkey” – the SAP workaround for bad design

In SQL, ‘upsert’ is a conditional insert or update: if the row is there, you update it, but if it is not there, you insert it. In Oracle, you should use a MERGE statement for that. You are clearly doing it wrong if you code something like:

begin
insert...
exception
when dup_val_on_index then update...
end;


But it seems that there are many applications with this bad design, and Oracle has introduced an underscore parameter for them: “_suppress_identifiers_on_dupkey”. You won’t be surprised that this one is part of the long list of parameters required for SAP.

Let’s investigate this.

Insert – Exception – Update

So the idea is to try first an insert, rely on the unique constraint (primary key) to get an exception if the row exists, and in this case update the existing row. There are several flows with that.

My OpenWorld theme song

(With apologies to any Simon & Garfunkel fans out there)

Hello jetlag my old friend,
You’ve come to mess with me again.
The bedside clock is stuck on 10 to 4
And my eyes are just so bloody sore.

The decision to get on that frickin’ plane.
Was just insane.

Suffering …. my jetlag in silence

 

A few gin and tonics tonight and I might even put it to musicSmile

The devastating death PC emoji of doom

So there I am.  Sitting at Perth airport.  My flight from Perth to Sydney, the first leg on my trip to OpenWorld 2017 is delayed.  Of course, delays are not unusual and do not normally bother me.  Because I can just flip open my laptop, knock off a few AskTOM questions while I wait.

But not this time.  I press the Power button and I get this:

Image result for windows 10 collectin memory dump

That is the first time I’ve ever seen that on my nice Dell XPS 13 laptop, but I figure “No big drama.  Probably just some sleep glitch” and let the machine reboot.

That is when the real fun started.  As it is restarting I get a similar screen (which I can’t screen dump…because the laptop isn’t booting!) saying the machine cannot boot.

When deterministic function is not

When you declare a function-based index, the function is deterministic, which means that calling it with same arguments will always return the same result. This is required because the indexed values, which are the result of the function, are stored in the index. But what happens if you declare a function deterministic when it is not?

I wanted to do this test after reading the following documents about Postgres HOT and WARM.

Oracle Open World 2017 presentation

Hi,
I will be presenting about ASM internals in Oracle Open World 2017 conference on Sunday October 1st. Following are the details:

Session ID: SUN5682

Session Title: Oracle Automatic Storage Management and Internals

Room: Moscone South – Room 155 Date: 10/01/17
Start Time: 12:45:00 PM
End Time: 01:30:00 PM

See you there!

Update: Added the presentation file. ASM_internals_Riyaj_OOW2017

Activating and Deactivating Performance Feedback

Performance feedback is one of the adaptive query optimizer features introduced in Oracle Database 12c. The aim of this short post isn’t to explain how it works, but to simply warn you about a buggy behavior in its configuration.

The parameters that control performance feedback are the following:

  • OPTIMIZER_ADAPTIVE_FEATURES (12.1 only): if set to TRUE (default), performance feedback is enabled. Otherwise, it is disabled.
  • OPTIMIZER_ADAPTIVE_STATISTICS (12.1 with patch 22652097, and 12.2): if set to TRUE, performance feedback is enabled. Otherwise, which is the default, it is disabled.
  • PARALLEL_DEGREE_POLICY (12.1 and 12.2): it has to be set to ADAPTIVE (default is MANUAL) to enable performance feedback.

So far, so good.

Oracle Indexing Myths (Telling Lies)

I’m currently hard at work fully revising and updating my “Indexing Internals and Best Practices” seminar in time for a series of events I’ll be running in Australia in October/November. This has been a highly popular and acclaimed 2 day seminar that is a must for any DBA, Developer, Solutions Architect or anyone else interested […]