DBMS_LOCK

The DBMS_LOCK package provides an interface to Oracle Lock Management services. You can request a lock of a specific mode, give it a unique name recognizable in another procedure in the same or another instance, change the lock mode, and release it.

DBMS Locks are allocated via

DBMS_LOCK.allocate_unique(lockname => c_lock_name, lockhandle => tmp_lock_handle); 

You cannot see them in V$lock and they stay in place through a Shutdown and Restart

You can find them in sys.dbms_lock_allocated

SQL> select * from sys.dbms_lock_allocated ;

NAME                   LOCKID EXPIRATIO
------------------ ---------- ---------
EOD_LOCK           1073741824 15-JUL-11

You can clear them by running:
DBMS_LOCK.release(id => 1073741824);

Reason I am posting this is that on the 15th of July 2011 one of our core systems failed during the End of Day process. (hardware error)

When it was eventually brought back up, the Business attempted to resume the End of Day but were told: "EOD already in progress"

This left the DBA on call (Me) scratching my head as there was nothing in v$lock and:

select sesion.sid,

       sql_text,

       sesion.username,

       sesion.OSUSER,

       sesion.process,

       sesion.MACHINE,

       sesion.PROGRAM

  from v$sqltext sqltext, v$session sesion

 where sesion.sql_hash_value = sqltext.hash_value

   and sesion.sql_address    = sqltext.address

 order by sid, sqltext.piece

/

was returning nothing.

Eventually after looking through the EOD package I discovered the DBMS_LOCK.

Pain.....

Using V$SQL

V$SQL lists statistics on shared SQL area. Statistics displayed in V$SQL are normally updated at the end of query execution. 


This Query can help tell you which SQL your Oracle DB has spent the most time parsing, executing and fetching.


SELECT * FROM
(SELECT to_char( to_date( round(ELAPSED_TIME/1000000, 0), 'sssss'), 'hh24:mi:ss') as TIME,
      SQL_ID as "SQL ID", 
      Executions as "Executions",
      DISK_READS as "Disk Reads",
      DIRECT_WRITES as "Disk Writes",
      to_char(LAST_ACTIVE_TIME, 'YYYY-MON-DD-HH24:MI:SS') as "Last Active Time",
      BUFFER_GETS as "Buffer Gets",
      CPU_TIME as "CPU Time",
      floor(round(sum((Executions * ELAPSED_TIME)/1000000), 0)/86400) || 'd ' || 
           to_char(to_date(mod(round(sum((Executions * ELAPSED_TIME)/1000000), 0),86400), 'sssssss'), 'hh24"h" mi"m" ss"s"') as "Total Time: EXECS * TIME"
 from v$sql    
 WHERE ELAPSED_TIME >= 10000000 --this is 10 seconds
 GROUP BY ELAPSED_TIME, SQL_ID, Executions, DISK_READS, DIRECT_WRITES, LAST_ACTIVE_TIME, BUFFER_GETS, CPU_TIME
 ORDER BY elapsed_time DESC)
WHERE ROWNUM < 60
/

You can modify the time by altering the the elapsed_time where clause.

Output looks like this:

TIME     SQL ID        Executions Disk Reads Disk Writes Last Active Time     Buffer Gets   CPU Time Total Time: EXECS * TIME

-------- ------------- ---------- ---------- ----------- -------------------- ----------- ---------- -----------------------------------------------------

00:22:33 5b264bnr9f694          2      42037           0 2012-FEB-23-18:20:39    78613409 1340365913 0d 00h 45m 07s

00:19:28 7xmzgv6w2svbx      48942         26           0 2012-FEB-23-18:32:06    73855656 1051322071 661d 19h 23m 09s

00:11:11 fpd8h590w7wdq          1      17669           0 2012-FEB-23-18:32:06    39302547  665371395 0d 00h 11m 11s

...

...

...

...

7xmzgv6w2svbx has been executed 48942 time at total CPU cost of 1051322071

Note that the Disk reads is very low and the Buffer Gets are quite hight.

If we look at the SQL behind 7xmzgv6w2svbx:

SQL> set long 10000

SQL> select SQL_FULLTEXT from v$sql where SQL_ID='7xmzgv6w2svbx';

SQL_FULLTEXT

---------------------------------------------------------------------------

SELECT SUM(NVL(X.CUR_VAL, 0)) TOTAL_CUR_VAL FROM TABLE_X X WHERE X.ID = :B1 

We can see it's a very simple SQL statement, but statistically the heaviest statement running within this DB

Oracle/Unix Top Process Script

Trying to figure out what process is hogging all the CPU on a *ix environment according to top can be a awkward

Here is a quick little script which can help you quickly determine what the top running  process is doing in your Oracle DB

#!/usr/bin/sh

#copyright © Feb 1st 2012 GRAHAM BOYLE
#This code is free to use, so long as the original author is recognised

if [ $# -eq 1 ]
then
       PARAM1=$1
else
       PARAM1=nothing
fi

SCRIPT_DIR=/home/graham_b/top_ora_proc
TMPFILE1=$SCRIPT_DIR/TOP_ORA_PROC.txt
TMPFILE2=$SCRIPT_DIR/TOP_ORA_PROC2.txt
TMPFILE3=$SCRIPT_DIR/TOP_ORA_PROC3.txt

echo Oracle SID set to: $ORACLE_SID

# Get the top Process ID for your Oracle SID
top -f $TMPFILE1 -d 1 -n 400 -s 1
if [ $PARAM1 = "sleepers" ]
then
       echo Including sleeping processes
       cat $TMPFILE1 | grep -i oracle$ORACLE_SID > $TMPFILE2
else
       echo Excluding sleeping processes
       cat $TMPFILE1 | grep -i oracle$ORACLE_SID | grep -i run > $TMPFILE2
fi

echo Found `wc -l $TMPFILE2 | awk '{print $1}'` processes for $ORACLE_SID

TOP_UNIX_PROCESS=`awk '{print $3}' $TMPFILE2 | head -1`

if [ `wc -l $TMPFILE2 | awk '{print $1}'` -eq 0 ]
then
       echo exiting
else
echo Top Unix process for $ORACLE_SID is $TOP_UNIX_PROCESS
# Now we need to do some fancy SQL to get the SQL to find out what the hell that process is doing.

sqlplus -s / as sysdba >/dev/null 2>&1 <<ENDREAD
set pages 10000
set lines 150
set trimspool on
spool $TMPFILE3

select sesion.sid,
       sql_text as "SQL TEXT",
       substr(sesion.username,1,16) as "USERNAME" ,
       substr(sesion.osuser,1,22) as "OS USER"
  from v\$sqltext sqltext, v\$session sesion, v\$process process
 where process.SPID=$TOP_UNIX_PROCESS
   and sesion.PADDR=process.ADDR
   and sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
 order by sesion.sid, sqltext.piece
/
ENDREAD

cat $TMPFILE3
rm $TMPFILE3
fi

rm $TMPFILE1 $TMPFILE2

Output looks like this:

graham_b $ ./top_oracle_proc.sh

Oracle SID set to: MYDB001
Excluding sleeping processes
Found 1 processes for MYDB001

Top Unix process for  MYDB001  is 6716

       SID SQL TEXT                                                         USERNAME         OS USER

---------- ---------------------------------------------------------------- ---------------- ----------------------

       559 SELECT count(*) from my_tab1                                     SYS              graham_b

       

1 rows selected.

Please note however that this script will only bring back a result if there are running process's

running the script with parameter "sleepers" will include sleeping processes EG:
graham_b $ ./top_oracle_proc.sh sleepers

copyright © Feb 1st 2012 GRAHAM BOYLE

ORAENV for windows

If you have had the misfortune of using Oracle software on a windows environment you will know that getting the Oracle Home, SID and path set up correctly can be a real pain

Example:
Install 11g DB and then install the grid control management agent.
Every time you open a command prompt and try to sqlplus / as sysdba to the DB you will get a network adapter error.

The problem is that the path and Oracle Home will always point towards the Oracle Home of the agent (Or which ever piece of Oracle software was installed last).

This is even more of a problem if you have multiple Oracle Homes installed on a windows server.
I do not understand why 11g DB for windows does not ship with some sort of oraenv utility like it does on Unix/Linux.

To that end I created my own oraenv script

How to use:
Create a batch file called oraenv.bat in some directory (I choose C:\Users\Graham\Desktop\scripts\oraenv)
Create 2 files: cur_sid.ora and oratab.ora in the same directory

In the cur_sid.ora, type db001, save and exit.
In oratab.ora add your Oracle SID and their Oracle Homes separated by a |
EG:

DB001|C:\oracle\dbhome_1
DB002|C:\oracle\dbhome_1
DB003|C:\oracle\dbhome_2
DB0011|C:\oracle\dbhome_2


The contents of oraenv.bat should be as follows:

@echo off

REM copyright © Jan 26th 2012 GRAHAM BOYLE

REM This code is free to use, so long as the original author is recognised

REM Set the environment Variables

REM You will need to set the path to your oraenv directory

SET ORAENV_DIR=C:\Users\Graham\Desktop\scripts\oraenv

SET ORAENV_CURRENT_SID=%ORAENV_DIR%\cur_sid.ora

SET ORAENV_ORATAB=%ORAENV_DIR%\oratab.ora

REM Print out the Current ORACLE_SID and prompt for a new one

echo Please enter Oracle SID:

echo Current SID:

for /f %%a in (%ORAENV_CURRENT_SID%) do (

echo %%a

SET ORA_SID=%%a

)

SET /P ORA_SID="New SID [%ORA_SID%?]: "

REM If the user Enters nothing then keep the Current SID, like UNIX oranev does :)

IF NOT %ORA_SID%=="" echo %ORA_SID% > %ORAENV_CURRENT_SID%

REM Check the ORATAB file to see if this ORACLE_SID is listed

REM If it's not the jump back to the command prompt

FIND /I "%ORA_SID%|" %ORAENV_ORATAB% > nul

IF NOT %ERRORLEVEL%==0 (

echo Oracle SID not found

exit /b

)

REM Set the ORACLE_SID

set ORACLE_SID=%ORA_SID%

REM Now get the Oracle Home from the oratab file

FOR /F "tokens=2 delims=|" %%A IN ('FIND /I "%ORA_SID%|" %ORAENV_ORATAB%') DO SET ORACLE_HOME=%%A

REM Last thing to do is set the Path

SET PATH=%ORACLE_HOME%\bin;%PATH%

REM And as a nice little touch we will Print out some details for the user

ECHO ORACLE_SID has been set to '%ORACLE_SID%' 

ECHO ORACLE_HOME has been set to '%ORACLE_HOME%'

@echo on

To Run:

C:\Users\Graham>cd C:\Users\Graham\Desktop\scripts\oraenv
C:\Users\Graham\Desktop\scripts\oraenv>oraenv
Please enter Oracle SID:
Current SID:
db001
New SID [db001?]: db002
ORACLE_SID has been set to 'db002'
ORACLE_HOME has been set to 'C:\oracle\dbhome_1'
C:\Users\Graham\Desktop\scripts\oraenv>oraenv
Please enter Oracle SID:
Current SID:
db002
New SID [db002?]:
ORACLE_SID has been set to 'db002'
ORACLE_HOME has been set to 'C:\oracle\dbhome_1'
C:\Users\Graham\Desktop\scripts\oraenv>

Please note pressing enter with no value when prompted for a new SID will keep the current Oracle SID.
Happy oraenv'ing
G. 

Checking for locking in Oracle

This is a handy little script I put together to give me details about who is causing locking within your Oracle database


It also give details on what SQL the blocking user is running.


This can be useful when trying to pin down what process is causing excessive locking within your database.


== lock_details.sql == 
set Pages 10000
set lines 150


Prompt Locked Table:
select substr(dba_objects.object_name, 1, 50) as "Locked Object", v$lock.TYPE AS "Lock Type",  v$lock.LMODE AS "Lock Mode"
from dba_objects, v$lock, v$locked_object
where v$lock.sid = v$locked_object.SESSION_ID
and v$locked_object.OBJECT_ID = dba_objects.OBJECT_ID
and v$lock.block=1
/


Prompt Lock Type Key:
Prompt JI (Materialized view)
Prompt MR (Media Recovery)
Prompt ST (Disk Space Transaction)
Prompt TM (DML or Table Lock)
Prompt TX (Transaction)
Prompt


Prompt Lock Mode Key:
Prompt 1 Null
Prompt 2 Row Share            (SS)
Prompt 3 Row Exclusive        (SX)
Prompt 4 Share                (S)
Prompt 5 Share Row Exclusive  (SSX)
Prompt 6 Exclusive            (X)
Prompt


prompt Blocking Session Details
select /*+ RULE */
substr( username, 1, 12)        "USERNAME",
substr( sid, 1, 6)              "SID",
substr( serial#, 1, 7)          "SERIAL#",
substr( command, 1, 6)          "COMMAND",
substr( osuser, 1, 12)          "OSUSER",
substr( process, 1, 8)          "PROCESS",
substr( machine, 1, 6)          "MACHINE",
substr( terminal, 1, 8)         "TERMINAL",
substr( program, 1, 25)         "PROGRAM",
substr( last_call_et, 1, 12)    "LAST CALL ET"
from   v$session
where  sid in (select sid from v$lock where block=1)
/


Prompt Blocking User is Running:


select sesion.sid,
       sql_text
  from v$sqltext sqltext, v$session sesion
 where sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
   and sesion.sid in (select sid from v$lock where block=1)
 order by sesion.sid, sqltext.piece
/