I haven’t written a non-Oracle post into my blog yet, so here’s one for you :)

I recently developed a little web service with a friend. Shortly, check out http://n.otepad.com and any feedback is appreciated (especially about the parts which suck, so we could improve those :)

The longer story is that for years I used to have a notes.txt file on my Windows desktop (or Linux desktop, whatever I happened to use at that time) for writing down my notes, addresses, code snippets, URLs etc etc. I created a keyboard shortcut CTRL+ALT+N for my notes file, so I could easily open up the file without needing to navigate around with mouse or switching between applications. I could open the file and search its contents in matter of 2-3 seconds.

Then this notes file got too big, Windows XP’s notepad.exe started getting slow when I had more than 10000 lines of text in the file. So I split my notes up to notes.txt, oracle.txt, unix.txt, etc. Each had a different keyboard shortcut, like CTRL+ALT+O for Oracle stuff (damn, I just realized this post is loosely related to Oracle :)

Anyway, the obvious problem which came from that split was that often I didn’t remember into which note file I had put a particular note (e.g. some Unix script for Oracle could have been in Unix file or Oracle file). So I ended up looking through multiple files, getting frustrated and sometimes giving up.

Oh, did I mention that I also used to send emails with notes to myself, just to keep them (or maybe deal with them later). And then I went to client’s office and realized I couldn’t access any webmail through their proxies, so had to rewrite couple of scripts from scratch.

I guess you get the picture. Finding my old notes got ineffective, time consuming (and lame!). I wanted to find my notes in matter of couple seconds, not give up after minutes.

So we decided to write a solution for ourselves with a friend.

Read more…

Tanel Poder Cool stuff, Productivity, Tools

In recent past I’ve blogged few scripts which use specially crafted ordered nested loop for sampling contents of V$ and X$ views fast, with plain SQL.

If you haven’t read them yet, here are the links:

• WaitProf
• LatchProf
• LatchProfX

I wrote the above scripts having special purposes in mind (e.g. profile session waits or latching activity).

Now I introduce a simple but powerful sqlplus script for ad-hoc sampling of any V$ view. It’s called…. (drumroll) …. sample.sql :)

When you look into it, the script is actually very simple. It’s just using power of sqlplus substitution variables, I can pass the sampled column and table names and sampling conditions in to the script dynamically.

The basic syntax is:

@sample column_name table_name filter_condition num_samples

For example, let say I have a session which executes lots of SQL statements in a loop and I want to have a quick overview of what’s the TOP SQL statement for a session right now.

Read more…

Tanel Poder Internals, Oracle, Performance, Troubleshooting

I will speak at four more conferences this year, so if you like what I’m offering at this blog, you can get more at these conferences. Also it’s a chance to meet up and match the names with faces :)

21-25 September, Oracle OpenWorld 2008 in San Francisco

• I’ll be presenting my 1-hour Advanced Oracle Troubleshooting guide

22-24 October, Miracle Oracle Open World 2008 in Denmark

• I’ll be delivering my 1-hour Advanced Oracle Troubleshooting session and potentially some more

1-5 December, UKOUG Conference 2008 in Birmingham

• I’ll be delivering a 2-hour masterclass on Advanced Oracle Troubleshooting

7-12 December, CMG ’08 International Conference, Las Vegas

• I’ll be presenting a joint paper “Multidimensional Visualization of ORACLE Performance Using Barry007″ with Dr. Neil Gunther

See you there!

Tanel Poder Oracle, Oracle 11g, Performance, Training, Troubleshooting

I got a question regarding Metalink note 296235.1 about a describe bug which causes objects to “disappear” when they are described when database is not open.

It was an interesting case involving a bug, so I wrote a quite long analysis with test cases today. However when posting the entry to wordpress, it managed to completely mess up the formatting. After wasting half an hour trying to get the formatting correct I gave up and saved the article into a PDF instead.

You can access it here: http://www.tanelpoder.com/files/oracle_rowcache.pdf

By the way, I welcome more questions about Oracle internals or performance tuning.
Often when starting to explain something, I realize that I don’t really know the answer well enough, which is a valuable experience as it makes me figure it out and learn!

So, if you have a question, write it into a comment here or drop me an email to tanel@tanelpoder.com

Tanel Poder Administration, Internals, Oracle, Troubleshooting

I just replied to an Oracle Forum Thread about why does even a small difference in SQL statement text cause it to be hard parsed and loaded as a different cursor. The reason is actually very simple – and I’m posting it into my blog too:

–

The reason why a statement with even a minor difference in text is parsed as a separate cursor is due how Oracle looks up statements from library cache.

First, a little background:

Tanel Poder Administration, Internals, Oracle, Performance, SQL, Troubleshooting

I wrote a little script pvalid.sql for listing valid values for multioption parameters (the ones which are not string, number or boolean type, but accept a parameter from predetermined list, like optimizer_mode which can have values of ALL_ROWS, FIRST_ROWS, CHOOSE, FIRST_ROWS_1, etc).

The script accepts a (part of) Oracle parameter name as first argument, for example the following output is from Oracle 10.2.0.3 database:

SQL> @pvalid lock

  PAR# PARAMETER                                          VALUE                          DEFAULT
------ -------------------------------------------------- ------------------------------ -------
   374 _db_block_cache_protect                            FALSE                          DEFAULT
       _db_block_cache_protect                            LOW
       _db_block_cache_protect                            MEDIUM
       _db_block_cache_protect                            TRUE

   376 db_block_checksum                                  TRUE                           DEFAULT
       db_block_checksum                                  FALSE
       db_block_checksum                                  FULL
       db_block_checksum                                  OFF
       db_block_checksum                                  TYPICAL

   696 _row_locking                                       ALWAYS
       _row_locking                                       ALWAYS
       _row_locking                                       DEFAULT
       _row_locking                                       DEFAULT
       _row_locking                                       INTENT
       _row_locking                                       INTENT

   756 db_block_checking                                  FALSE                          DEFAULT
       db_block_checking                                  FULL
       db_block_checking                                  LOW
       db_block_checking                                  MEDIUM
       db_block_checking                                  OFF
       db_block_checking                                  TRUE

   851 _plsql_anon_block_code_type                        INTERPRETED                    DEFAULT
       _plsql_anon_block_code_type                        NATIVE

23 rows selected.

SQL>

Or:

Read more…

Tanel Poder Administration, Oracle, Tools

When someone asks you to take a quick look into database performance and for whatever reason you can’t run your usual scripts or performance tools on there, ), then what query would you run first?
Yeah sometimes I’ve been not allowed to run custom scripts nor even touch the keyboard due security policies in effect.

Whenever you’re in such situation you want to be the command both short and effective for showing the database state.

The simplest query for determining database state performance wise would be this:

SQL> select event, state, count(*) from v$session_wait group by event, state order by 3 desc;

EVENT                                                            STATE                 COUNT(*)
---------------------------------------------------------------- ------------------- ----------
rdbms ipc message                                                WAITING                      9
SQL*Net message from client                                      WAITING                      8
log file sync                                                    WAITING                      6
gcs remote message                                               WAITING                      2
PL/SQL lock timer                                                WAITING                      2
PL/SQL lock timer                                                WAITED KNOWN TIME            2
Streams AQ: qmn coordinator idle wait                            WAITING                      1
smon timer                                                       WAITING                      1
log file parallel write                                          WAITING                      1
ges remote message                                               WAITING                      1
SQL*Net message to client                                        WAITED SHORT TIME            1
DIAG idle wait                                                   WAITING                      1
pmon timer                                                       WAITING                      1
db file sequential read                                          WAITING                      1
Streams AQ: waiting for messages in the queue                    WAITING                      1
rdbms ipc message                                                WAITED KNOWN TIME            1
jobq slave wait                                                  WAITING                      1
Streams AQ: qmn slave idle wait                                  WAITING                      1
Streams AQ: waiting for time management or cleanup tasks         WAITING                      1

19 rows selected.

It uses the Oracle wait interface to report what all database sessions are currently doing wait/CPU usage wise. Whenever there’s a systemic issue (like extremely slow log file writes) this query will give good hint towards the cause of problem. Of course just running couple of queries against wait interface doesn’t give you the full picture (as these kinds of database wide “healthchecks” can be misleading as we should be really measuring end user response time breakdown at session level and asking questions like what throughput/response time do you normally get) but nevertheless, if you want to see an instance sessions state overview, this is the simplest query I know.

Interpreting this query output should be combined with reading some OS performance tool output (like vmstat or perfmon), in order to determine whether the problem is induced by CPU overload. For example, if someone is running a parallel backup compression job on the server which is eating all CPU time, some of these waits may be just a side-effect of CPU overload).

Below is a cosmetically enhanced version of this command, as one thing I decode the “WAITED FOR xyz TIME” wait states to “WORKING” and “On CPU / runqueue” as event name as otherwise it’s easy to miss by accident that some sessions are not actually waiting on previous event anymore:

Read more…

Tanel Poder Administration, Oracle, Performance, Troubleshooting

If you like the stuff in my blog or my conference sessions, you will sure like my seminar :)

I’m happy to announce the dates of my first public Advanced Oracle Troubleshooting seminars:

I will be posting  the missing registration links for above seminars in coming days. Also you can follow the (growing) list of seminar locations from http://blog.tanelpoder.com/seminar/

I may do few additional seminars this year, I will keep you posted when details are sorted out. Next year I will be doing my seminar in quite a few cities, in Europe, US and Asia.

If you are interested in my seminar in your city/company, please drop me an email to tanel@tanelpoder.com and I’ll see what I can do (you can also contact your local user group directly as that’s probably what I will do when planning next seminars).

Tanel Poder Oracle, Oracle 11g, Performance, Troubleshooting

In Oracle 11g even more library cache operations have been changed to use KGX mutexes instead of latches.

In Oracle 10.2.0.2+ the library cache pin latch usage was replaced with mutexes whenever _kks_use_mutex_pin was true, also few other things like V$SQLSTATS arrays and parent cursor examination were protected by mutexes. However the traversing of library cache hash chains (the right child cursor lookup using kksfbc()) was still protected by library cache latches which could become a problem with frequent soft parsing combined with too little cursor cache and long library cache hash chains (remember, the library cache latches were always taken exclusively even for plain hash chain scanning).

In 11g all library cache related latches except “library cache load lock” are gone and corresponding operations are protected by mutexes instead. The “library cache” latches have been replaced by “Library Cache” mutexes for example.

Here are couple queries which illustrate the change.

Executed on 10.2.0.3:

SQL> select name from v$latch where lower(name) like '%library%';

NAME
--------------------------------------------------
library cache pin allocation
library cache lock allocation
library cache hash chains
library cache lock
library cache
library cache pin
library cache load lock

7 rows selected.

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

NAME
----------------------------------------------------------------
latch: library cache
latch: library cache lock
latch: library cache pin
library cache pin
library cache lock
library cache load lock
library cache revalidation
library cache shutdown

8 rows selected.

Same queries executed on 11.1.0.6 and the bold lines above are gone:

Read more…

Tanel Poder Internals, Oracle, Oracle 11g