Home » RDBMS Server » Performance Tuning » Performance issues after Database Hang during backup (Oracle 10g)
Performance issues after Database Hang during backup [message #336619] Mon, 28 July 2008 07:01 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
We are facing database performance issues as we have a web based application where Oracle is used at backend. Everything was fine until Friday. On saturday backup was being taken and database got hanged and on Monday DBAs killed the session and again restarted the server.
From then everything seems slow despite things are rebooted and kiiled.

Any suggestions on how to increase the performance.

I am attching the AWR in textmode.


Thanks in advance,
Mahi
Re: Performance issues after Database Hang during backup [message #336646 is a reply to message #336619] Mon, 28 July 2008 09:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Could you afford to bounce the database?
Try it and see if you can reproduce the issue.
Re: Performance issues after Database Hang during backup [message #336697 is a reply to message #336646] Mon, 28 July 2008 13:38 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
As per AWR report This query was taking time

select count (opp.OPPORTUNITY_ID) logins from TS_OPPORTUNITY_DETAILS opp,ts_bpt_
leads bpt where bpt.BPT_ID=opp.BPT_ID and bpt.anhfsc_id=:1 and bpt.docto
r_id =:2 and opp.STATUS_CODE_STAGE_3=:3 and opp.REASON_CODE_STAGE_3=:4
and opp.DATE_STAGE_3 >= to_date(to_char(add_months(sysdate,to_numb

Kindly note :- It is using sysdate so taking date from system.

Sorting is done... so you have to set he big SORT_AREA_SIZE


e.g.
SQL> ALTER SESSION SET SORT_AREA_SIZE = 10000000;

Your problem lies here db file sequential read & enq: TX - row lock contention
Re: Performance issues after Database Hang during backup [message #336716 is a reply to message #336697] Mon, 28 July 2008 15:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> so you have to set he big SORT_AREA_SIZE
Probably right, if the OP is using Oracle 8i or below.
But the OP is in 10g and is already using pga_aggregate_target.
Re: Performance issues after Database Hang during backup [message #336721 is a reply to message #336716] Mon, 28 July 2008 15:32 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO.

Examine the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET.
Re: Performance issues after Database Hang during backup [message #336748 is a reply to message #336721] Mon, 28 July 2008 17:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Again,
Why don't you just say, REFER DOCS?
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams157.htm#REFRN10165
Also,
What does all this have to do with above mentioned issues?
Re: Performance issues after Database Hang during backup [message #337542 is a reply to message #336646] Thu, 31 July 2008 03:33 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Mahesh,
The database was bounced but to no help. Sometimes it goes well and other times even though the number of logins are not enough, the performance is low. Do you have any suggestion.

The entire server was restarted for the first time after one year when the system got hanged. Is it possible that there is some configuration issue.

Please advice.

Thanks,
Mahi
Re: Performance issues after Database Hang during backup [message #337592 is a reply to message #337542] Thu, 31 July 2008 06:28 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Nothing right away. It could be a wild goose chase.
Certain OS may get happy after the reboot.
Post your OS,backup type (if RMAN, post configs and scripts).
Did the backups go well?
Next ,
Isolate one module/sql that is having trouble. Look into the plan.
Are the stats on table and indexes upto date? Else collect stats again and try.
Previous Topic: ora-04030
Next Topic: Report hangs
Goto Forum:
  


Current Time: Sun Dec 04 02:37:39 CST 2016

Total time taken to generate the page: 0.04887 seconds