Script: Display valid values for multioption parameters (including hidden parameters)

August 13, 2008 by 6 Comments

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...]

Filed Under: Oracle Tagged With: ,

Advanced Oracle Troubleshooting Guide, Part 8: Even more detailed latch troubleshooting using LatchProfX

July 23, 2008 by 15 Comments

In my last AOT post I published my LatchProf script which is able to sample detailed latchholder data from V$LATCHHOLDER.

Latchprof allows you to drill down into your latching problems at session level (which V$LATCH, V$LATCH_PARENT and V$LATCH_CHILDREN can’t do). It allows you to get valuable details about individual sessions who are holding a latch the most, therefore likely contributing to the latch contention problem the most.

However after you have discovered the troublemaking session, then what next? One way forward is looking into V$SESSTAT counters using Snapper tool. Depending on what latch is the problematic one, you would look for different stats like various buffer get stats for cache buffers chains latches and parsing/executing stats when looking into library cache latches. However if those stats look “normal”, is there any other way do drill down further?

Yeah, there is and lets look into it!

[Read more...]

Filed Under: Cool stuff, Oracle Tagged With: , , ,

Advanced Oracle Troubleshooting Guide, Part 7: Sampling latch holder statistics using LatchProf

July 9, 2008 by 14 Comments

I have been too busy since getting back from vacation, thus no posts for a while. But I hope the waiting was worthwhile as I present you LatchProf, a tool for digging in to latch contention problems – using plain SQL and sqlplus!

As, I’m still busy, I make it short.

LatchProf is a script similar to WaitProf, only it samples latch holder statistics from V$LATCHHOLDER. As V$LATCHHOLDER contains a SID column (with session ID of a latch holder) it becomes possible to find who is hitting a latch the most (a way to prove that crappy monitoring tools which constantly scan through V$SQL DO cause library cache latch contention themselves).

[Read more...]

Filed Under: Cool stuff, Oracle Tagged With: , ,

Advanced Oracle Troubleshooting Guide, Part 6: Understanding Oracle execution plans with os_explain

June 15, 2008 by 28 Comments

Get ready for some more adventures in Oracle process stack!

Before proceeding though, please read this post about safety of different stack sampling approaches.

I have had few non-trivial Oracle troubleshooting cases, related to query hangs and bad performance, where I’ve wanted to know where exactly in execution plan the current execution is.
Remember, Oracle is just another program executing instructions clustered in functions on your server, so stack sampling can help out here as well.

So, I was looking into the following stack trace taken from an Oracle 10.1 database on Solaris SPARC, running a SQL with this execution plan.

[Read more...]

Filed Under: Cool stuff, Oracle Tagged With: , , , , ,

Advanced Oracle Troubleshooting Guide, Part 5: Sampling V$ stuff with WaitProf. Really fast. Using SQL!

June 6, 2008 by 31 Comments

I bet you thought I’ll be writing about direct SGA access?! ;)

Nope!

Direct SGA access has excellent troubleshooting potential (as long as you know the shared memory data structures), but it has one major drawback – very few companies have such tools already in place in their production systems.

I have occasionally been called in to solve an urgent performance problem, happening right now and it needs solving immediately! And did I mention, these are critical production systems. Where you can’t just install binary executables freshly downloaded off internet. In fact you would want to diagnose the issue with minimal impact and changes required to those production environments (and that leaves sql tracing out the first round troubleshooting tools for me as well!)

So, I’ve developed myself a toolset for such purpose, Snapper and sw.sql and some process stack reading techniques I already have introduced in my blog.

Next in line is waitprof.sql which is a high-frequency V$SESSION_WAIT sampler – implemented in plain SQL (not PL/SQL).

Waitprof is basically a sampling session wait profiler. It’s like running a select against V$SESSION_WAIT in a very tight loop and aggregating results – but I have used a trick to do all this in plain SQL, which gives me performance advantage over PL/SQL based loops. Waitprof is able to sample V$SESSION_WAIT for a session up to 100 000 times per second!

This depends on your hardware of course and Oracle version too, but normally you’ll get 50-70kHz sampling rate with it.

Ok, you want to see an example? ;-)

[Read more...]

Filed Under: Cool stuff, Oracle Tagged With: , , , ,

Performance Tools Quick Reference Guide

June 3, 2008 by 1 Comment

There’s a nice Metalink Note 438452.1 about various less known Oracle performance tuning utilities.

If you haven’t heard about things like StackX, LTOM, HangFG, SQLTXPLAIN, OS_Watcher or OPDG then it’s time to check this note out! :)

Filed Under: Oracle, Uncategorized Tagged With: , , , ,

Snapper shortcut

June 2, 2008 by 2 Comments

I have a (very) small script called sn.sql which I use as a wrapper around snapper (maybe I should’ve called it Snapper Wrapper but it’s too long name for the purpose :)

The idea is to have to type less when running Snapper with default options (take 1 snapshot, output to screen and display Session tats,Wait events and Time model stats).

Whenever there’s a performance issue with a session I first quickly run @sn <seconds> <sid>, for example:

SQL> @sn 10 106

This takes a 10 second snapshot of session 106 and displays output:

-- Session Snapper v1.07 by Tanel Poder ( http://www.tanelpoder.com )

---------------------------------------------------------------------------------------------------------------------------------------------
HEAD,     SID, SNAPSHOT START   ,  SECONDS, TYPE, STATISTIC                               ,         DELTA,  DELTA/SEC,     HDELTA, HDELTA/SEC
---------------------------------------------------------------------------------------------------------------------------------------------
DATA,     106, 20080603 06:31:17,       10, TIME, hard parse elapsed time                 ,      10124018,    1012402,     10.12s,      1.01s
DATA,     106, 20080603 06:31:17,       10, TIME, parse time elapsed                      ,      10124018,    1012402,     10.12s,      1.01s
DATA,     106, 20080603 06:31:17,       10, TIME, DB CPU                                  ,      10113462,    1011346,     10.11s,      1.01s
DATA,     106, 20080603 06:31:17,       10, TIME, DB time                                 ,      10124018,    1012402,     10.12s,      1.01s
DATA,     106, 20080603 06:31:17,       10, WAIT, events in waitclass Other               ,            51,          5,       51us,      5.1us
--  End of snap 1

PL/SQL procedure successfully completed.

This might help you to save few seconds of typing every time you troubleshoot :)

 

Filed Under: Oracle Tagged With: ,

Querying the current tracefile name, using SQL – with tracefile_identifier

May 31, 2008 by 8 Comments

Here’s a code snippet for identifying current tracefile name using SQL. Yep I know there are many such examples online, but I haven’t found any so far which also account for TRACEFILE_IDENTIFIER variable.

Luckily the value of this variable is accessible from V$PROCESS, so we can write a query which constructs us the full tracefile name, including TRACEFILE_IDENTIFIER part, if any:

SQL> alter session set tracefile_identifier = blah;

Session altered.

SQL> select value ||'/'||(select instance_name from v$instance) ||'_ora_'||
  2         (select spid||case when traceid is not null then '_'||traceid else null end
  3               from v$process where addr = (select paddr from v$session
  4                                           where sid = (select sid from v$mystat
  5                                                      where rownum = 1
  6                                                 )
  7                                      )
  8         ) || '.trc' tracefile
  9  from v$parameter where name = 'user_dump_dest'
 10  /

TRACEFILE
---------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/lin11g/LIN11G1/trace/LIN11G1_ora_14639_BLAH.trc

1 row selected.

Let’s see if this actually worked:

LIN11G1$ head /u01/app/oracle/diag/rdbms/lin11g/LIN11G1/trace/LIN11G1_ora_14639_BLAH.trc
Trace file /u01/app/oracle/diag/rdbms/lin11g/LIN11G1/trace/LIN11G1_ora_14639_BLAH.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1
System name:    Linux
Node name:      linux01
Release:        2.6.18-53.el5
Version:        #1 SMP Sat Nov 10 18:24:52 EST 2007
Machine:        i686
LIN11G1$

Yep! :)

Filed Under: Oracle Tagged With: , ,

Expensive calculator…

January 9, 2008 by 6 Comments

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…

Filed Under: Cool stuff Tagged With:

Perl version of Snapper

December 28, 2007 by 1 Comment

Wolfgang Breitling has written a snapper.pl script – Perl version of my original snapper.sql one. Check it out!

As his script doesn’t rely on DBMS_OUTPUT for generating output, you can easily get real time continuous output with his script, without needing to wait until the script finishes or by using somewhat complicated DBMS_SYSTEM.KSDWRT tracefile output.

Also, as his script doesn’t rely on DBMS_LOCK package for sleeping, so you don’t need rights on it.

Obviously you need Perl and Oracle DBI drivers installed in your client machine for using the script. Note that the default Unix/Windows Perl installations do not have Oracle DBI drivers installed, so you might be better off using the perl coming with Oracle server installation itself (or you can download the DBI modules from CPAN if you’re not afraid of little configuration work).

So, I wish you happy snapping for the new year! :)

Filed Under: Cool stuff Tagged With: , ,
«Older Posts
Newer Posts»