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 Select s.sid, s.serial#, s.machine,s.osuser, p.spid, t.sql_text From v$session s, v$sqltext t, v$process p Where 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 ;
Bruce T. writes: In the Unix PID script, there is an excess AND which will cause an invalid SQL error.
About the AuthorJames 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.