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