Solved

Hang problem in oracle database

Posted on 2014-03-10
11
867 Views
Last Modified: 2014-03-12
Dear Experts ,

I have a problem in my oracle database the clients in some departments calling me always and they said the system hanging when they try to save the data in the application and the hang happen in some clients PC and the other clients using same application and same window but in another departments they don't have this hang error.
I am using oracle 10G database and the application developed by developer 6I
How can I solve this issue and find out the cause for this hang?

Kind Regards
0
Comment
Question by:abuabdallah
11 Comments
 
LVL 14

Expert Comment

by:leoahmad
ID: 39919827
Information you provided is not sufficient, please elaborate your question
0
 

Author Comment

by:abuabdallah
ID: 39919842
Ok

I have oracle developer application applied in medical Laboratory in 7 departments 2 departments when they want to receive the blood samples in the system it takes long time to commit the data and some time its not commit the transaction in the database tables the tables names LAB_SEROLOGY_SAMPLES & LAB_HORMONES_SAMPLES.
The others 5 departments working fine and can commit there transactions successfully and no hangs at the commit time and the other 5 departments have another tables in the database
Can you advice how to find the error and is the indexes may affect also?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39919845
the hanging in oracle will be reflected in wait events found in the system tables

an oracle tuning dba would be your best help in tackling this
look for books on the oracle wait interface for complete description of this

what i use at the moment of the problem is a session wait query
it displays all the queries and their wait events which are "waiting" on something

select s.sid, s.sql_hash_value, sw.event, sw.seq#, s.last_call_et, substr(sql.sql_text, 1, 80)
  from v$sql     	 sql,
       v$session  s,
       v$session_wait sw
 where sql.hash_value(+) = s.sql_hash_value
   and sw.sid = s.sid
   and sw.event <> 'SQL*Net message from client'    -- this is  
   and sw.event <> 'rdbms ipc message'              -- to exclude
   and sw.event <> 'pmon timer'                     -- the 
   and sw.event <> 'smon timer'                     -- unimportant 
   and sw.event <> 'pipe get'                       -- ones
   and sw.event <> 'PL/SQL lock timer'
   and sw.event <> 'jobq slave wait'
   and sw.event not like 'Streams AQ%'
 group by s.sid, s.sql_hash_value, sw.event, sw.seq#, s.last_call_et, substr(sql.sql_text, 1, 80)
 order by sid

Open in new window


another item to look out for is locking
to see if you have an exclusive table locking problem
select * from v$sql
where upper(sql_text) like '%LOCK TABLE%'
  and not sql_text like '%v$sql';

Open in new window


if you see multiple statements with LOCK table then it's normal that users will have to wait on each other
> in this case, it's usually a design problem of the application
0
 

Author Comment

by:abuabdallah
ID: 39920006
the output of the select statement  attached
what you can see from this output
and what is your advice?
No result for the second query.
query-result.xls
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39920068
repeat this updated query a few times shortly after each other
usually the sid + query which keeps being displayed after several repetitions is the one causing the problem

select s.sid, s.sql_hash_value, sw.event, sw.seq#, s.last_call_et, substr(trim(sql.sql_text), 1, 80)
  from v$sql     	 sql,
       v$session  s,
       v$session_wait sw
 where sql.hash_value(+) = s.sql_hash_value
   and sw.sid = s.sid
   and sw.event <> 'SQL*Net message from client'    -- this is  
   and sw.event <> 'rdbms ipc message'              -- to exclude
   and sw.event <> 'pmon timer'                     -- the 
   and sw.event <> 'smon timer'                     -- unimportant 
   and sw.event <> 'pipe get'                       -- ones
   and sw.event <> 'PL/SQL lock timer'
   and sw.event <> 'jobq slave wait'
   and sw.event not like 'Streams AQ%'
   and sw.event not like 'gcs remote message'
   and sw.event not like 'DIAG idle wait'
   and sw.event not like 'ASM background timer'
 group by s.sid, s.sql_hash_value, sw.event, sw.seq#, s.last_call_et, substr(trim(sql.sql_text), 1, 80)
 order by sid

Open in new window



i see event PX ...
are you using shared server setup ?
do you have enough shared server processes for your number of concurrent users ?

there is a backup running  Backup: sbtbackup
is performance slow when the backup is running
> i'm guessing it's not because i see only 2 processes for the backup ...

these queries  are waiting
SELECT FACILITY_STOP_FLAG,NVL(COPY_VERSION,1)   FROM SYS_FUNCTIONS  WHERE FACILI
SELECT REPORT_ID   FROM SYS_HOSPITAL_REPORTS  WHERE HOSPITAL_NO = :b1  AND FACIL
SELECT MAIN_NAME   FROM MAIN.MAIN_DESCRIPTIONS  WHERE MAIN_TYPE = 1  AND MAIN_MA

Open in new window


it's possible the table hold row locks if they end with for update
select hash_value, sql_text, sql_fulltext from v$sql
where hash_value in (3959280555, 2955847235, 1964035381)

Open in new window


you'll have to check if the statements end with FOR UPDATE.
> it's possible they hold a lock too
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 39920073
check LAST_CALL_ET column

this is the one with longest time
8726      SELECT REPORT_ID   FROM SYS_HOSPITAL_REPORTS  WHERE HOSPITAL_NO = :b1  AND FACIL

this is the second one
8264      SELECT REPORT_ID   FROM SYS_HOSPITAL_REPORTS  WHERE HOSPITAL_NO = :b1  AND FACIL
0
 

Author Comment

by:abuabdallah
ID: 39920199
Thank you for your fast responding ,

- We are not using shared server setup
- We are using RAC and ASM technology
- also we are using HL7 protocol with another systems sending and receiving data between the systems
- for the backup its scheduled daily full backup at 12:00 AM
- Also there is no SELECT FOR UPDATE statement.

But there is one more thing in some departments we are using TPlink hub is the TPlink will affect the connection and hang the database ?
0
 
LVL 23

Expert Comment

by:David
ID: 39920502
Hello abuabdallah.  Just to supplement Geert's answer: a "hang" such as your people report can be anywhere in the client to server round-trip.  This can include issues with the client, the network carriers, the host, any middle-tier (webserver), switches, etc.  Or, even a TPlink hub.  These events can cause "wait" without, in your words, hanging the database.

I agree that we DBAs tend to see a resource contention most commonly -- where someone is using a poorly designed UPDATE form, which keeps a lock on the rows.  Obviously, this can lead to an intermittent conflict that's difficult to track down.

Follow Geert's advice, but remember that SELECT statements rarely cause contention:  they are not modifying data.  INSERT, DELETE and UPDATE will generate redo logs for rollback, wait on conflicting locks, and require constraint checks.  By default, every INSERT with indexes requires a small wait while each row's indexes are created.

So my questions to you:  are the two tables mentioned indexed on the columns used by the queries?  And, what are you doing to keep the data and indexes analyzed for the cost based optimizer (CBO)?
0
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 39920600
you could also contact the support of oracle clinical to get their feedback on this
http://www.oracle.com/us/products/applications/health-sciences/e-clinical/clinical/index.html

it's a 3 tier app, and there really isn't a lot of activity on the database which i see describing the problems you have
i'm guessing it's either the end user frontend or the middle tier (tplink-hub) which causes the slowing down
0
 

Author Comment

by:abuabdallah
ID: 39922620
" So my questions to you:  are the two tables mentioned indexed on the columns used by the queries?  And, what are you doing to keep the data and indexes analyzed for the cost based optimizer (CBO)?"

Actually I am using update commands on that tables and its indexed also , and for data and indexes I am doing rebuilt for the indexes from time to time.

What is the best way to keep the data and indexes analyzed for the CBO?
0
 

Author Closing Comment

by:abuabdallah
ID: 39923258
thank you for your help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now