Tanel Poder’s blog: Core IT for geeks and pros

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 0×54580006: 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 0×5E892E34: 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 0×54580006: TX mode 6
    116 WAITING jobq slave wait                                   3           2          0          0          0
    111 WAITING latch: shared pool                            10765           0  537529180        277          0 0×200A0B5C: 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 ;)

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:

(more…)

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:

(more…)

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! :)

If you need to run and manage loads of sqlplus scripts which call other scripts, which call other scripts etc, then you are probably interested in the sqlplus APPINFO parameter shown below.

When you issue SET APPINFO ON in sqlplus, this makes sqlplus to automatically call DBMS_APPLICATION_INFO.SET_MODULE and set the MODULE value to sql script name which is currently being executed.

This allows you to easily pass the current script name info to Oracle, without the need to have a manual call to SET_MODULE in beginning and end of every script (along with some mechanism for storing the previous module).

A simple example is below. I used two scripts blah.sql and blah2.sql for my test:

C:\tmp>type c:\tmp\blah.sql   

select sys_context(’USERENV’, ‘MODULE’) from dual;   

@@blah2   

select sys_context(’USERENV’, ‘MODULE’) from dual;   

 C:\tmp>type c:\tmp\blah2.sql   

select sys_context(’USERENV’, ‘MODULE’) from dual;   

So, blah.sql reports the current module, then calls blah2.sql which reports current module and then returns back to blah.sql which returns the current module again.

SQL> set appinfo on
SQL>
SQL> @blah   

SYS_CONTEXT('USERENV','MODULE')
------------------------------------------------
01@ blah.sql   

SYS_CONTEXT(’USERENV’,'MODULE’)
————————————————
02@ blah2.sql   

SYS_CONTEXT(’USERENV’,'MODULE’)
————————————————
01@ blah.sql   

SQL>   

Looks cool!

From output above we can see the following things:

  (more…)

I have released the version 1.06 of Oracle Session Snapper.

If you don’t know what Snapper is, check out those blog entries first:

• http://blog.tanelpoder.com/2007/08/20/oracle-session-snapper-real-time-session-level-performance-stats-for-dbas/
• http://blog.tanelpoder.com/2007/08/26/oracle-session-snapper-part-2-getting-most-out-of-snapper/

Improvements:

• Ability to report instancewide Latch and Enqueue get deltas (useful for testing code efficiency in dev environments where there’s no other activity going on)
• Header format and header options have changed slightly - for making it simpler to quickly paste & graph the data in Excel
• Some bugs have been fixed (and probably new ones introduced)

How to read Snapper output?

Here are the column definitions (sorry for crappy formatting, I need to do some CSS tweaking once I get a chance):

Examples:

(more…)

Couple of years ago an interesting fact floated up in Oracle-L - a regular user with only SELECT privilege on a table can successfully execute a SELECT FOR UPDATE against it, locking all rows and even lock the whole table using LOCK TABLE command. Locking a table in exclusive mode would stall all changes and selects against that table - effectively hanging all applications using that table. Pete Finnigan wrote a review of the issue in this blog entry.

This means that many of the “read only” accounts used by support or reporting users aren’t really that read only - these accounts could be used for a (hopefully) short denial of service attack and leaves another opportunity for human error to cause trouble in production environments. This issue applies both for direct user sessions and dblinks.

This behaviour came as news to me back then and no real solution for this issue has been proposed so far. So, while I do not provide any new information regarding the problem itself, I do propose a solution for it.

First lets start from reproducing the problem case. Note that I use two users, SYSTEM as the table owner and TEST as the “read only” user.

  (more…)

I was doing some low-level security research on Oracle 11g and realized that combining couple little known Oracle’s features can allow anyone with DBA or IMP_FULL_DATABASE rights run any OS command under the same privileges the Oracle processes are running. This allows an attacker to erase files from audit_file_dest or patch the Oracle binary (after setting _disable_image_check to true) or make a dedicated server process a SYSDBA one using a debugger.

I don’t rank this security issue a too critical one as exploiting it requires the attacker to already have high privileges - the BECOME USER privilege in addition to execute rights on KUPP$PROC package used by DataPump. These privileges are included in DBA and IMP_FULL_DATABASE roles by default. So in order to exploit the security flaw you would already have pretty destructive rights ( IMP_FULL_DATABASE has DROP ANY TABLE and such privs in it already ).

However there are few assumptions on what a regular, non-SYSDBA DBA can’t do on modern Oracle versions ( where o7_dictionary_accessibility = false ).

Few of those are:

1. can not alter SYS objects
2. can not shut down & restart database ( for changing audit_trail or remote_login_passwordfile settings )
3. can not grant SYSDBA & SYSOPER privileges
4. can not grant rights on some SYS objects ( which haven’t been granted to DBA role with grant option )
5. can not use oradebug
6. can not run OS commands using Oracle executable rights

Those assumptions do not hold true for now for users with DBA or IMP_FULL_DATABASE role, until the issue has been fixed - or the BECOME USER privilege is revoked from the roles ( which may make full imports fail ).

Here is the test case for Solaris ( but this can be made to work on other Unixes and Windows as well ):

(more…)

I’m having some busy times, so can’t blog anything more serious than another sqlplus trick (which likely has value only to some hardcore sqlplus geeks though).

Ever wanted to load a sqlplus parameter (like linesize, pagesize or arraysize) into a sqlplus define variable?

This can sometimes be helpful for customizing your everyday DBA scripts to output (or not output) some columns based on linesize. Or you may want to use the SQL error code somewhere in your script.

We’ll it’s doable with the getplusparm.sql script:

SQL> def myvar
SP2-0135: symbol myvar is UNDEFINED
SQL>
SQL> @getplusparm linesize myvar
SQL>
SQL> def myvar
DEFINE MYVAR           = “80″ (CHAR)
SQL>  

The above example reads sqlplus parameter “linesize” to a define “myvar”.

The example below uses the sqlcode sqlplus parameter to capture the error code of last command sent to database ( 0 if success ):

SQL> drop table xyz;
drop table xyz
           *
ERROR at line 1:
ORA-00942: table or view does not exist  

SQL>
SQL> @getplusparm sqlcode err
SQL>
SQL> prompt Error code &err
Error code 942
SQL>  

Note that the script creates few temporary files into local directory, you may want to modify the file locations from default for your use (and include an instance name or some variable to avoid race conditions when running multiple sqlplus’es concurrently).

And this is the script text… if you find a way for doing it simpler in sqlplus, let me know :)

set termout off  

def _tmpfile=getplusparm  

spool &_tmpfile..tmp
show &1
spool off  

spool &_tmpfile..chg
prompt c/&1/def &2/
prompt c/&2 /&2=/
spool off  

get &_tmpfile..tmp nolist
@&_tmpfile..chg
save file &_tmpfile..set replace  

@&_tmpfile..set  

set termout on  

While this is probably my most useless post written during my short blogging career, I was satisfied just to find out that such thing is doable. So, all sqlplus geeks out there, enjoy! ;)

Question: How to check instance parameter values in Oracle?

Answer: show parameter xyz

WRONG!

Answer: select value from v$parameter where name = ‘xyz’

WRONG!

These commands show the session level parameter values, which are separate from instance level parameters:

SQL> show parameter session_cached_cursors 

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
session_cached_cursors               integer     20 

SQL> select value from v$parameter where name = 'session_cached_cursors'; 

VALUE
---------------------------------------------------------------------------------
20 

V$SYSTEM_PARAMETER is the view which shows instance level parameters (and these are what all new sessions inherit)

 

SQL> select value from v$system_parameter where name = 'session_cached_cursors'; 

VALUE
--------------------------------------------------------------------------------
0 

In this case the difference was due a logon trigger whcih did set session_cached_cursors based on v$session program name.

Note that there is another not widely known gotcha with parameters - multivalue parameters:

There are few parameters which can accept and hold multiple values at a time. For example, utl_file_dir and service_names.

From the following it seems that in my instance there’s a single parameter with multiple comma separated values:

SQL> select value from v$system_parameter where name = 'service_names'; 

VALUE
------------------------------------------------------------------------------------
WEB, HR, OE, MFG, ADMIN 

Actually these values are treated as separate parameter values (of the same parameter name) in V$PARAMETER2 and V$SYSTEM_PARAMETER2:

SQL> select value from v$system_parameter2 where name = 'service_names'; 

VALUE
------------------------------------------------------------------------------------
WEB
HR
OE
MFG
ADMIN 

So, in addition to well known V$PARAMETER, there’s V$PARAMETER2, V$SYSTEM_PARAMETER and V$SYSTEM_PARAMETER2 - and only V$SYSTEM_PARAMETER(2) show you the instance parameters.

And there’s more - V$SPPARAMETER, V$OBSOLETE_PARAMETER and V$PARAMETER_VALID_VALUES (introduced in 10.2) which all can be handy for diagnosing problems and for grilling people at interviews (*evil grin*).

Unlike all my other marvellous posts, this one doesn’t actually have a point - but hey it’s Friday evening in Singapore (or Saturday in Australia) already, so I wish you a happy, parameter-free weekend ;-)