Reading AWR Report – Part 1

Once in a while I get requests for some information about reading and analyzing an AWR report. I have been thinking for a long time about writing such a post, but always postponed it as it is a very tricky topic. The AWR (or statspack for that matter) report is huge and contains so much information that it’s easy to get lost. It also requires a lot of knowledge about the database and the different mechanisms so it’s very difficult to explain all of this in a blog post (or even a series of posts). In this post I’ll try to start from the beginning, explaining a little bit about the AWR report and the analysis process and we’ll see where it takes us.

Continue reading

Scanning an Index

The internet is full of information about indexes, and for a reason. Indexes in a database is probably the most important performance related topic. There are so many cases, properties, and different ways to use indexes that there is simply a lot to write about. In this post I’d like to talk about a specific use case that I’ve seen a few times, and is related to index scans and performance.

Continue reading

GI Timezone

Lately I had a weird problem with one of my customers regarding timezone in the database. They copied a database server from US (eastern time zone) to Europe (GMT) and changed all the OS setting to the new timezone. The application uses “sysdate” to insert data to the database, and they saw that the sysdate returns the wrong time. When I checked that, it seemed that when we used sqlplus locally on the server everything worked perfectly, sysdate, current_timestamp and systimestamp all returned the correct times with GMT. In SQL developer, however, even when running from the same timezone (GMT) the result of sysdate and systimestamp were still eastern time and not the GMT (current_timestamp returned the correct time).

Continue reading

Using Different Languages in Oracle – Part 1

Being in North America (the English speaking side), made me understand that many people are not aware (and don’t actually care) about character sets too much. Everything supports English, and everything works. Coming from Israel, I realized that some of the language issues we have in computerized systems are less understood in the western world. For example, we write from right to left (we are not the only ones, Arabic is the same for example), and I always get strange looks when I sign a piece of paper. Another example is the completely different letters (unlike English and most European languages), and more. But we are not the only ones, many countries must have these difficulties, so I decided to write this post.

Continue reading

How does the Listener Work?

My previous post about bypassing the listener was a big success. It actually broke my daily record (the previous one was the Oracle Challenge).

Anyway, because of this success, I’ve decided to write another post on the same topic. Actually, a more basic post that explains how the listener works during a connection (which I mentioned shortly in the previous post). It is a bit long and technical, I hope you’ll enjoy it, and I would appreciate any comment. Continue reading

How are Numbers Saved in Oracle?

I’ve been asked quite a few times about the difference between number and character columns in Oracle. So I decided to write this article in order to explain how things work.

Many people, mainly those with programing background, assume that Oracle, like programming languages, contains character columns that work like STRING in programing languages, and number columns that work like INTEGER, DOUBLE and similar in programing languages. If this is the case, working with numbers in Oracle is much more efficient than text, the value has a static size of 4 or 8 bytes, the CPU works with number natively in math calculations, and therefore, number columns are better (when relevant).

However, this is not the case.

Continue reading