Solved

Hang problem in oracle database

Posted on 2014-03-10
11
922 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 37

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 37

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
 
LVL 37

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 37

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

726 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