Linux is a wonderful operating system. However there are a number of things that one needs to do to make sure it runs as efficiently as possible. Today, I would like to share one of them. It has to do with using ASM (Automatic Storage Manager) disks.

In Linux, there are 2 major ways to create ASM disks.

1. you can use ASMlib kernel driver
2. you can use devmapper devices

You could also use /dev/raw devices, but I don’t recommend this at all. I will write another blog explaining why.

Regardless of which approach you take, you have to create partitions on your LUNs. Starting with version 2, ASMlib won’t let you use the entire disk. You have to create a partition.

The reason to force the creation of this partition is to make explicit that something exists on that device, and that it’s not empty. Otherwise, some OS tools assume the disk is unused and could mark it, or just begin using it, and override your precious Oracle data.

(Read more after the jump.)

(more…)

As Doug Burns already mentioned, here’s another video from the Pythian Goodies project.

As usual, the Goodies are intended to be debates, so please post any questions or follow-ups with the relevant time-index from the video.

Featuring Doug himself, he discusses Parallel Basics. This video is a good introduction to using Oracle Parallel features and most importantly, what problems you may have with it. Topics discussed:

• Basic Parallel Execution (PX) architecture
• Degree of Parallelism and number of slaves
• Instance configuration for parallel operation
• Hints for Parallelism
• Common problems
• Dictionary queries for monitoring parallel operations

Online Videos by Veoh.com

P.S.: I love the moment that the thumbnail captured.

Two weeks ago, I released a video about Flash Recovery Area as part of the Pythian Goodies project. Here is the next video in the sequence.

Oracle I/O Basics

This video covers:

• Disk I/O Concepts
• Async disk I/O concepts - when does Oracle use it
• Monitoring via vmstat - explained
• Monitoring via iostat - explaining the meaning of the %util (disk busy time) column, queue depth, service time, wait time
• Concepts of larger queue, faster disk I/O, slower response time, more through-put.

http://www.veoh.com/videos/v237408rJb8XQWX

As usual, the Goodies are intended to be debates, so please post any questions of follow-ups with the relevant time-index from the video.

N.B.: YouTube decided to limit director’s accounts to 10 minutes and decided to have a new account type called the “10+ minutes” account. Unfortunately, the form to apply for such accounts is disabled until further notice. We had to find an alternative and we picked veoh. It comes in handy as it offers an iPod version for direct download.

Just recently I got a call from a developer. He had a table with a primary key populated by a sequence, a timestamp column with the current date and some other columns. He had a specific set of data that, when ordered by the primary key had out of order timestamps. He was puzzled how this could be. This is a RAC database and the sequence was created with the default values.

Not only the sequence’s cache was the default of 20, but it was “noordered”. Being “noordered” Oracle will not guarantee the order in which numbers are generated.

Example of “noorder” sequence in 10g RAC:

Session 1 on node-A: nextval -> 101
Session 2 on node-A: nextval -> 102
Session 1 on node-B: nextval -> 121
Session 1 on node-B: nextval -> 122
Session 1 on node-A: nextval -> 103
Session 1 on node-A: nextval -> 104

The sequence cache is in the shared pool, therefore sessions on the same node can share the cached entry, but sessions on different nodes cannot. I wonder why Oracle doesn’t make “ordered” the default for sequences.

So I explained to the developer how sequences work in RAC and how each node has its own “cache”.

We changed the sequence to “ordered” and increased the cache to 1000. Now selecting on either node gets the next number as he expected. I warned him that there would be some performance implications due to cluster synchronization. Him been a responsive developer, asked me what would be the impact, so I tested it out.

How does RAC synchronize sequences?

(more…)

On Friday, I announced the Pythian Goodies project, and now here it is, the very first video of the series. The topic is “Flash Recovery Area,” and how can it make your life easier. Of course, we discuss some caveats too.

Don’t forget, the Goodies are intended to be debates, so if you have any questions or follow ups, please post them with the relevant time index from the video.

Flash Recovery Area

Link to YouTube movie: http://www.youtube.com/watch?v=e1UQCJOYzc8

Downloadable version of the video for iPod:

What is Pando?

P.S.: I know the whiteboard is not very visible. I’ve corrected the camera position in the next one.

It’s been a while since I blogged. As Alex Gorbachev mentioned in his earlier posts, December was a very busy month. January was better, but still, work from December was left over for January. We’re back to normal now — just busy, as opposed to extremely busy.

It’s the new year now, and I have an exciting announcement to make.

Here at Pythian, we always look to increase our knowledge and stay on top of the technology. Often we find ourselves explaining how a specific technology works. Sometimes it’s one-on-one, sometimes there is a group of people that needs information.

I’ve been wondering for a while now how best to transfer knowledge efficiently and in a fun way. Presentations are good, but they require a lot of effort in preparation and research, and they are not as interactive as they could be when the group is smaller.

To answer some of these issues, I would like to introduce you to “Pythian Goodies”.

(more…)

My apologies — there will be no broccoli in this post.

Many people would like to know how well their application will run in RAC. Would it be faster or slower? Would it run at all?

Well, I have a query that can answer that question. There’s a caveat however. You have to first put your application in RAC, then the query can tell you how well it runs.

Here is a bit of theory: The whole idea behind RAC is centuries old, and comes from big empires in the past: divide and conquer.

Read more after the jump, a buffer cache status query and the logic behind it.

(more…)

As I was poking around metalink, I found the following extremely interesting section. It’s in a very obvious place, but it’s new, so many of you may have not noticed it.

It’s called “Support case studies” and provides some amazing articles, such as :

Resolving High CPU Usage on Oracle Servers
The Mysterious Performance Drop
Diagnosing Unsuccessful CRS root.sh Issues
Intense and Random Buffer Busy Wait Performance

Here’s how to go there:
1. Login to metalink
2. Click the Knowledge tab
3. Look at the left column for “Support Case Studies”

When I read Note: 391116.1 with the full list, I noticed the following bugs that we’ve encountered are fixed.

• the ‘hash group by’ bug with incorrect results: bug 4604970
• the listener may hang in RAC: bug 4518443
• ora-600 [19004] on multitable join - a problem with histograms described in Note:371333.1 and Bug 5041016

Unfortunately, an important bug in 10.2.0.2 posted on the Sept. 29 is not listed as fixed in this patch list. The bug has to do with both data corruption, and security vulnerabilities when running queries that can be interpreted in multiple ways by the SQL parser — depending on what schema has namespace precedence for your signon. In this regard, I would recommend anyone who is running, for example, a test or development copy of their production schemas on the same database, to read the following metalink note without delay.

Note: 392673.1 Bug 5458753 “SQL can execute in wrong schema”.

This post belongs in the “tricks” category.

Have you ever had a performance problem on your database server? Have you ever received a call to let you know that “the database is slow,” and when you login to the server via SSH it takes forever? (It’s funny that I even ask this, of course you have.) You run top (it also takes forever) and you see a gazillion database sessions using up the CPU.

The next step? I usually login with some kind of GUI query tool such as TOra, Golden, or the new SQL Developer from Oracle, and run a few of my “scripts” to check what is currently running and what it is doing . I have a whole diagnostic suite. Unfortunately, there’s almost no CPU left for my session, so logging in takes forever, running my scripts is sluggish, and during such performance problems seconds just seem like minutes.

The story so far should be quite familiar. One day I came up with the following neat idea. Start a second listener, on a different port, calling it the “emergency listener”. Then renice the listener process with higher priority. Now, every time I connect to the database via my “emergency listener”, my connection gets higher priority, and thus feels like there’s no problem with the database’s resource use.

There is one little caveat however. You need to either have access to root, or have a nice SA that will add renice to your sudoers file .

Here are the commands to setup the “emergency listener”, after the jump.

(more…)