Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

What's holding up the system?

What's slowing down your Oracle database? Here are some possibilities.

A common question that developers ask me is "Why is my SQL just sitting there?" There are several things that an Oracle DBA can easily look at to see what is wrong. The top three are: archive destination full, poorly written SQL, and table locks.

When I am hit with the question, I first take a look at the archive destination, and make sure that it has free space. There can be a lot of volatility in the development environment, particularly when loading data into test databases and it's often difficult for the DBA to gauge the amount of archive filesystem space to allocate. Run the command

 df -Pk /arch
...where /arch is your archive log destination, and make sure you have some free space.

Poorly written SQL is another big problem. Use the following SQL to determine the UNIX pid:

  column sid format 999999
  column serial# format 99999
  column osuser format a10
  column machine format a10
  column spid format 099999
    s.sid, s.serial#, s.machine,s.osuser,  p.spid, t.sql_text
    v$session s,
    v$sqltext t,
    v$process p
    s.sql_address = t.address and
    s.paddr = p.addr and
    s.sql_hash_value = t.hash_value and
    s.sid > 7 and
    s.audsid != userenv ('SESSIONID') and
  Order By s.sid, s.osuser, s.process, t.piece ;

Once you determine the pid, use top or glance to see if this process is still consuming CPU. If it is consuming CPU, then you know that the SQL is still running, and that is entirely other issue.

The final problem is a actual lock in Oracle. Oracle typically does a good job at locking, generally at the row level, but this SQL will identified if the SQL is stopped because of a lock condition. Note: before running this script make sure that you run $ORACLE_HOME/rdbms/admin/catblock.sql.

ttitle on
set pagesize 9999 feedback off echo off
col waiting_session head 'Waiting|Session' format 999999
col holding_session head 'Holding|Session' format 999999
col lock_type head 'Lock Type' format a15
col mode_held head 'Mode|Held' format a10
col mode_requested head 'Mode|Request' format a10
col lock_id1 head 'Lock ID1' format a10
col lock_id2 head 'Lock ID2' format a10
select  waiting_session
,       holding_session
,       lock_type
,       mode_held
,       mode_requested
,       lock_id1
,       lock_id2
from    sys.dba_waiters
order by 2, 1 ;

Reader Feedback

Bruce T. writes: In the Unix PID script, there is an excess AND which will cause an invalid SQL error.

About the Author

James Giordano is an Oracle database administrator. He has been working with Oracle for about seven years, and also has experience with UNIX and PeopleSoft/Oracle financials.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.