| Performance issues after Database Hang during backup [message #336619] |
Mon, 28 July 2008 07:01  |
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 #336697 is a reply to message #336646] |
Mon, 28 July 2008 13:38  |
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 #336721 is a reply to message #336716] |
Mon, 28 July 2008 15:32 |
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 #337542 is a reply to message #336646] |
Thu, 31 July 2008 03:33 |
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 |
 |
Mahesh Rajendran
Messages: 10707
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
