I read this post by Laurent Schneider yesterday. In the comment section Tom Kyte already explained what the issue was about, but I’ll expand this explanation a little.

The question was why should the apparently invalid statement below work? I mean there is no such column nor number as “1.x”), yet the statement works ok:

SQL> select 1.x from dual;

         X
----------
         1

The column header gives a good hint what happened above. Oracle has treated the X as the column alias.

Let’s remove the “x” and see:

SQL> select 1. from dual;

        1.
----------
         1

Now all works as expected, “1.” is treated as number ( 1. = 1.0 )

But the question remains, how come Oracle splits up the string “1.x” and decides that the “x” is the column and “1.” is the number part – considering that there’s no whitespace between the 1. and x?

The answer is that apparently the string tokenizer used by Oracle SQL parser is smart enough that it doesn’t rely only on whitespace for recognizing token delimiters. It is also able to use character class analysis for understanding where a literal ends and the next token (like column alias) starts.

So, it is possible to write valid SQL statements without using any whitespace at all. For example:

Read more…

Tanel Poder Cool stuff, Oracle, SQL

Oracle has evolved over time to much more than just a plain relational database. One option is to use Oracle as an expensive calculator.

When researching or demoing Oracle, it’s quite convenient to do number calculations directly on sqlplus prompt, especially if dealing with internals where lots of stuff is about addresses and offsets shown in hex.

Here’s the script what I use for such purposes: http://www.tanelpoder.com/files/scripts/calc.sql.

It usually saves me couple of seconds every calculation as I don’t have to reopen the calc.exe on my Windows box (I immediately stopped using it after I wrote the script).

But the main benefit is that your calculation outputs remain in the sqlplus output history, so you don’t have to redo them again and you’ll have somewhat better documentation of the output of research steps you’ve done.

Here’s a quick demo:

Add a decimal value to decimal value:

SQL> @calc 10 + 10

                 DEC                  HEX
-------------------- --------------------
                  20                   14

Add a hex value to a decimal:

SQL> @calc 10 + 0x10

                 DEC                  HEX
-------------------- --------------------
                  26                   1A

Divide a hex value with a decimal one:

SQL> @calc 0xFFFF / 64

                 DEC                  HEX
-------------------- --------------------
                1024                  400

Show a value in both hex and dec:

SQL> @calc 65536 + 0

                 DEC                  HEX
-------------------- --------------------
               65536                10000

Note that you do not need to use “0x” for indicating a hex value, instead just an “x” will do:

SQL> @calc xB0B * x142A2D

                 DEC                  HEX
-------------------- --------------------
          3735928559             DEADBEEF

So, you can use Oracle as a very expensive calculator – but on the other hand it can save some of your time…

Tanel Poder Cool stuff, Tools

Few days ago I wrote the post about systematic Unix troubleshooting, I mentioned my sw.sql script for quick session wait monitoring from sqlplus.
I had forgot to upload the latest version of my script, which is able to decode the enqueue and latch names from P1 values of V$SESSION_WAIT.

I have uploaded it now and you can get it from here: http://www.tanelpoder.com/files/scripts/sw.sql

This script allows you to easily see the lock or latch session is waiting on even on before Oracle 10g.

Also, not all latches and enqueues are externalized into separate wait events in 10g+, but only the most usual ones.

The below output is from Oracle 11.1.0.6 where only 27 latch types of total 496 do have their own wait event:

SQL> select count(*) from v$latch;

  COUNT(*)
----------
       496

SQL> select name from v$event_name where name like 'latch:%';

NAME
----------------------------------------------------------------
latch: cache buffers chains
latch: redo writing
latch: redo copy
latch: Undo Hint Latch
latch: In memory undo latch
latch: MQL Tracking Latch
latch: row cache objects
latch: shared pool
latch: session allocation
latch: messages
latch: enqueue hash chains
latch: ges resource hash list
latch: gcs resource hash
latch: cache buffers lru chain
latch: checkpoint queue latch
latch: cache buffer handles
latch: object queue header operation
latch: redo allocation
latch: gc element
latch: undo global data
latch: Change Notification Hash table latch
latch: change notification client cache latch
latch: lob segment hash table latch
latch: lob segment query latch
latch: lob segment dispenser latch
latch: virtual circuit queues
latch: parallel query alloc buffer

27 rows selected.

Not all enqueue types have their own wait either:

SQL> select count(*) from v$lock_type;

  COUNT(*)
----------
       187

SQL> select count(distinct(substr(name,6,2))) locks from v$event_name where name like 'enq:%';

     LOCKS
----------
       161

The SUBSTR trick was needed because some enqueues show different wait events depending for what purpose the enqueue is required.

Anyway, here is the output from my script. Note the syntax how I pass a SQL statement as a parameter (enclosed in double quotes) to dynamically generate a list of SIDs of interest (you may need to make your browsing window wider to see all output. Also note that the blogging engine keeps replacing normal quotes & double-quotes with “nicer” looking ones, so directly pasting these commands to sqlplus may not work).

SQL> @sw "select sid from v$session where type != 'BACKGROUND'"

    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT         P1         P2         P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ---------- ---------- ---------- ------------------------------------------
    144 WAITING SQL*Net message from client                   27328           3 1650815232          1          0
    127 WAITING SQL*Net message from client                    7008           4 1650815232          1          0
    170 WAITING SQL*Net message from client                    1279         221 1650815232          1          0
    118 WAITING enq: TX - row lock contention                    35        2053 1415053318     458777       1454 0x54580006: TX mode 6
    116 WAITING jobq slave wait                                   3           2          0          0          0
    114 WAITING kksfbc child completion                        2694           0          0          0          0
    111 WAITING latch: cache buffers chains                   10673           0 1586048564        141          0 0x5E892E34: cache buffers chains[c3482]
    119 WORKING On CPU / runqueue                              5703           0 1413697536          1          0
    117 WORKING On CPU / runqueue                             51328           0 1586048564        141          0
    132 WORKING On CPU / runqueue                              7457           0 2314369474    7667712    6263293

10 rows selected.

SQL> @sw "select sid from v$session where type != 'BACKGROUND'"

    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT         P1         P2         P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ---------- ---------- ---------- ------------------------------------------
    144 WAITING SQL*Net message from client                   27328           3 1650815232          1          0
    127 WAITING SQL*Net message from client                    7008           4 1650815232          1          0
    170 WAITING SQL*Net message from client                    1279         222 1650815232          1          0
    114 WAITING cursor: pin S wait on X                        2769           0 3157682392    8650752     327681
    118 WAITING enq: TX - row lock contention                    35        2054 1415053318     458777       1454 0x54580006: TX mode 6
    116 WAITING jobq slave wait                                   3           2          0          0          0
    111 WAITING latch: shared pool                            10765           0  537529180        277          0 0x200A0B5C: shared pool[c1]
    117 WORKING On CPU / runqueue                             51423           0  537529180        277          0
    119 WORKING On CPU / runqueue                              5709           0 1413697536          1          0
    132 WORKING On CPU / runqueue                              7577           0 1586048564        141          0

10 rows selected.

In addition to latch name, this script also shows the latch address in SGA and the child latch number, which allow identifying if several sessions are waiting on the same actual latch or just another child latch with same name.

Enjoy ;)

Tanel Poder Administration, Performance, Troubleshooting

I was once asked a question, why does Oracle change its parameter count during session lifetime?

The question arose from the following observation that v$parameter shows more parameters after you adjust some hidden parameter value:

SQL>
SQL> select count(*) from v$parameter;

  COUNT(*)
----------
       288

SQL>
SQL> alter session set "_complex_view_merging"=false;

Session altered.

SQL> select count(*) from v$parameter;

  COUNT(*)
----------
       289

Looks like the parameter count was just increased by one!

“It sure seems like the hidden parameter don’t exist before they are actually modified”:

SQL> show parameter _unnest_subquery   (no rows returned)
SQL>
SQL>
SQL> alter session set "_unnest_subquery"=false;

Session altered.

SQL>
SQL> show parameter _unnest_subquery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_unnest_subquery                     boolean     FALSE
SQL>

So it seems like Oracle was “creating” the hidden parameter when it was modified.

This is not the reality though. All parameters for session are created during session startup and stored in shared pool.

The answer lies in the view text of GV$PARAMETER view. This example is from an 11g database, older versions like 9.2 do have less checks in the where clause. Note that the output is manually formatted for better readability:

Read more…

Tanel Poder Internals, Oracle, Troubleshooting

How many times have you seen a following case, where a user or developer complains that their Oracle session is stuck or running very slowly and the person who starts investigating the issue does following:

1. Checks the database for locks
2. Checks free disk space
3. Checks alert log
4. Goes back to the client saying “we did a healthcheck and everything looks ok” and closes the case or asks the user/developer to contact application support team or tune their SQL

The point here is that what the heck do the database locks, alert log or disk space have to do with first round session troubleshooting, when Oracle provides just about everything you need in one simple view?

Yes, I am talking about sampling V$SESSION_WAIT here. Database locks, free space and potential errors in alert log may have something to do with your users problems, but not necessarily. As there are many more causes, like network issues etc which could affect your user (and the whole database), it doesn’t make sense to go through all those random “healthchecks” every time you receive a user phone call. Moreover, even if you identify that there is shortage of disk space or there are many database locks – so what? They may not have anything to do with the users problem.

The issue here is that still many people do not know about V$SESSION_WAIT which in most cases shows your problem immediately or at least points you to right direction (e.g. there’s no need to check for locks if your session is waiting on “log file switch (archiving needed)” wait – and vice versa). Even if “these people” have heard of V$SESSION_WAIT and may be able to drop this in during their job interview, they may not know how to use it in systematic troubleshooting context. Many hours of service downtime and user frustration would be saved if all DBAs knew this extremely simple concept of looking at V$SESSION_WAIT.

This blog entry is not about Oracle though, so I will leave this rant for a future blog post.

This post is about a similar problem in Unix world. Having been involved with resolving some serious production issues lately I have been surprised quite many times by the corporate Unix support people who seem to do behave in similar manner. For example, there is a user calling in saying that their scheduled Unix job, which normally takes 5 minutes, has been running for hours now. The “senior unix support analyst” will do following:

Read more…

Tanel Poder Troubleshooting, Unix/Linux