Search

Top 60 Oracle Blogs

Recent comments

Humor

12c Adaptive Optimization

Since everyone seems to be all twitterpated about Oracle Database 12c this week, I thought I’d post a quick note to let you know that the slides from the presentation on 12c Adaptive Optimization I did at the Hotsos Symposium 2013 (with a lot of help from Maria) are now available in the Whitepapers / Presentations section of this blog.

While I’m on the topic, I found this little blurb in the Oracle Database 12c Release 1 New Features Guide:

New Exadata Prototype

I got a look a new prototype for the next generation Exadata last week while doing some work with a company in Europe. Apparently there is a big push to be environmentally friendly there now and so Oracle is trying to come up with a model that uses less power and is biodegradable. The word on the street is that it won’t be available until after release 2 of the 12c database.

The new model has a few drawbacks though. For one thing, it only lasts a few weeks before you must either replace it or higher some rocket surgeon consultants to come in and tune it. From the early version of the prototype I saw, it does appear to be smaller and more tasty than previous models though.

 

 

Christmas Explain Plan Patterns

Here’s a lovely Candy Striped pattern in an Explain Plan. Looks like the traditional Christmas candy canes. Just in time for the Holiday Season!

Incident Notification – Pythian internal – Jul 29th 3PM EDT

We have incident reporting procedures at Pythian. This incident report was sent just recently internally at Pythian. We learned some good lessons from it so I hope it would be useful to the community as well – copying it below as is… As part of our incident management process, you will find below a summary [...]

Informative Error Messages

You have probably all experienced situations where you get an error message from Oracle and it turns out to be a total Red Herring. Occasionally though the error messages can be really helpful. I got an email from a co-worker today (thanks Stephan) that read like this:

I was working on an external table today and fat fingered something. This is the error report-

Error report:
SQL Error: ORA-30657: operation not supported on external organized table
30657.0000 – “operation not supported on external organized table”
*Cause: User attempted on operation on an external table which is
not supported.
*Action: Don’t do that!

Gotta love it. They don’t actually tell us what we did wrong, but they do tell us how to resolve the issue!

Apparently some of the Oracle developers have a sense of humor.

By the way, I’m sure you already know this but there is a nifty little Oracle provided tool called oerr that spits out this information:

 
SYS@SANDBOX1> !oerr ora 30657
30657,0000, "operation not supported on external organized table"
// *Cause:  User attempted on operation on an external table which is
//          not supported.
// *Action: Don't do that!

Bare Metal Programming

I used to get a kick out of the recollections of the slightly older guy I worked with (Rob Weinberg). He used to tell me about programming on a system where they didn’t even have a keyboard. As he explained it, the computer was programmed by flipping switches for each bit and pushing a button to store assembly instructions. This would be done repetitively until all the instructions were entered into the computer and then the “program” could be run. I thought that was really funny having started with 4GL programming. Well I ran across a couple of videos on youtube of a guy programming a Dec PDP computer which was the machine that was in use just before I started my career on Dec Vaxen. And the videos show how it was done. (you younger guys might want to sit down before you watch this, I don’t want anyone to get dizzy and hurt themselves)

The switches allowed assembly language to be input (looks like 4 – 3 bit characters to me). They also had the ability to read programs off of paper tapes as long as you could enter the program to read the tape. Here’s a guy doing just that.

If you want to see what a variac is, have a look at this one:

It’s basically a power conditioner. I’m not sure why they needed that. I have seen people use them with guitar amps which can be sensitive (from a sound perspective) to low voltage or variable voltage.

Anyway, I thought the videos were entertaining.

Funny Developer Tricks – (substr(cust_id,1,length(:b1))

Ha. This one was a little surprising. I ran across a SQL statement that gets fired off hundreds at a time in rapid succession. Each execution taking several seconds – too long for thousands of executions in a row. The statement looked like this (cleaned up to protect the guilty):

b1 := '10355P034001SGL00066';
b2 := '10355P034001SGL00066';
 
select count(cust_id) 
from customers
where substr(cust_id,1,length(:b1)) = :b2;

What was the developer trying to do? How can we fix it? Your comments are welcomed.

Well it only took Jeremiah a couple of minutes. Yes – it looks like the developer had learned to use the substr function, but not the LIKE operator. Maybe the developer didn’t know you could use the same bind variable twice in the same statement as well. Or maybe the variables could have different values, but that seems unlikely. Anyway, here’s the stats for the before and after.

SYS@FYIDOCS> @fss
Enter value for sql_text: 
Enter value for sql_id: f0n7vkcdhqp3v
 
SQL_ID        PLAN_HASH_VALUE      EXECS ROWS_PROCESSED  AVG_ETIME    AVG_CPU    AVG_PIO      AVG_LIO SQL_TEXT
------------- --------------- ---------- -------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------
f0n7vkcdhqp3v       182380728      9,933           9933       7.68       1.89      83.06       11,627 select count(cust_id) from customers
                                                                                                      where substr(cust_id,1,length(:b1)) = :b2
 
 
 
SYS@FYIDOCS> @fss                   
Enter value for sql_text: %test f0n7vkcdhqp3v_3.sql%
Enter value for sql_id: 
 
SQL_ID        PLAN_HASH_VALUE      EXECS ROWS_PROCESSED  AVG_ETIME    AVG_CPU    AVG_PIO      AVG_LIO SQL_TEXT
------------- --------------- ---------- -------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------
4j1apzncj8dps       611149136          1              1        .00        .00        .00            8 select count(cust_id) from customers
                                                                                                      where cust_id like :b2||'%'

Oracle XMonth

I spent most of the last week in California at Oracle’s XMonth (it was really a week so I’m not sure why they called it XMonth). Which reminds me of a tuning engagement I did a few years ago. I showed up at the client’s facilities and we started discussing the problem. They told me that their daily job was running too slow and it was causing major problems. So I asked them what time the daily job ran each day. And they told me that it ran every 20 minutes. Ha! “And why do you call it the daily job?”, I asked. They weren’t really sure. The job was taking about an hour to complete as I recall, but I digress.

At XMonth, they had three tracks: Exadata (and two others that I can’t remember). I did learn a few things I thought were worth sharing.

  1. Exalogic is not shipping yet, but we did cover it in some detail. Exalogic’s biggest advantage appears to be the Infiniband fabric. It can be connected directly to any DB server using IB (Exadata for example) and can communicate with the extremely low latency RDS protocol.
  2. Oracle has relaxed their “no changes to the Exadata configuration” stance (but only very slightly). They said that it was OK to change out the Cisco switch and replace it with some other equivalent switch.
  3. A competitive analysis of Sun’s server line was provided. It included T series, X series, and M series servers along with Exadata. Exadata pretty much kicks all other options in the teeth (in my opinion). M series are still suitable for very large applications that are unable to scale out via RAC – such are Oracle’s own MRP package which uses the dbms_pipe package limiting its ability to scale in a RAC environment. But in general, the advice to the sales team was that if you are in a competitive situation, in most cases you should lead with Exadata.

So that’s about it. Oh they also let me talk about our experiences with Exadata. That was fun and I got to try my hand at a virtual presentation, as there were participants all over the world following along with Webex. The software has gotten pretty good for doing these kinds of presentations by the way. It was good practice for the Virtual Oracle Conference we have coming up next month. (note that there are only a couple of days left to sign up at the discounted rate)

I’m in the Wrong Business

Well I stayed at home today to do some writing on a book project while one of my sons and one of my daughters and my wife went to the Fort Worth Natural Science Museum. So I was feeling a little overworked and like I was missing out because I’ve been slaving away writing “scholarly technical material”. Then I got a text from my son. Just a picture that’s all.

I’m sure these two books will probably sell a few orders of magnitude more copies than any book that I contribute to. Kind of puts it all in perspective. I think next weekend I’ll go to the zoo with them.