some time is going hung & I have to restart the db.
What do you mean by "going hung"?
Can users connect to the database?
Can you see active sessions on v$session?
What do you see in alert log?
What do you see on the box?... are pmon/smon in good shape?
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Why do not you analyze the AWR report when the performance issue was there? IF you have some difficulty with that you can share the report. We'll help you.
Every day I am facing deadlock issue. this is the problem problem for the same??
-------------------------------------------------------------------
Oct 6, 2008 1:18:02 PM Oct 6, 2008 1:19:05 PM 1.05 Metrics "Database Time Spent Waiting (%)" is at 32.7185 for event class "Other"
Oct 6, 2008 1:16:02 PM Oct 6, 2008 1:17:02 PM 1 Metrics "Database Time Spent Waiting (%)" is at 39.59369 for event class "Concurrency"
Oct 6, 2008 1:16:02 PM Oct 6, 2008 1:17:02 PM 1 Metrics "Database Time Spent Waiting (%)" is at 76.37071 for event class "Other"
Oct 6, 2008 1:13:02 PM Oct 6, 2008 1:14:02 PM 1 Metrics "Database Time Spent Waiting (%)" is at 40.04823 for event class "Other"
Oct 6, 2008 1:09:59 PM Oct 6, 2008 1:10:59 PM 1 Metrics "Database Time Spent Waiting (%)" is at 68.52271 for event class "Other"
Oct 6, 2008 1:08:59 PM Oct 6, 2008 1:09:59 PM 1 Metrics "Database Time Spent Waiting (%)" is at 50.2031 for event class "Concurrency"
Oct 6, 2008 1:04:50 PM Oct 6, 2008 1:06:53 PM 2.05 Metrics "Database Time Spent Waiting (%)" is at 46.4171 for event class "Other"
Oct 6, 2008 12:00:23 PM Oct 6, 2008 12:01:23 PM 1 Metrics "Database Time Spent Waiting (%)" is at 36.67834 for event class "Other"
Oct 6, 2008 11:55:17 AM Oct 6, 2008 11:57:20 AM 2.05 Metrics "Database Time Spent Waiting (%)" is at 42.73943 for event class "Other"
Oct 6, 2008 11:50:08 AM Oct 6, 2008 11:51:08 AM 1 Metrics "Database Time Spent Waiting (%)" is at 83.0844 for event class "Other"
Oct 6, 2008 10:14:28 AM Oct 6, 2008 10:15:28 AM 1 Metrics "Database Time Spent Waiting (%)" is at 32.30657 for event class "Other"
Oct 6, 2008 9:29:49 AM Oct 6, 2008 9:30:49 AM 1 Metrics "Database Time Spent Waiting (%)" is at 33.01647 for event class "Concurrency"
Oct 6, 2008 9:29:49 AM Oct 6, 2008 9:30:49 AM 1 Metrics "Database Time Spent Waiting (%)" is at 75.57205 for event class "Other"
Oct 6, 2008 9:16:19 AM Oct 6, 2008 9:17:19 AM 1 Metrics "Database Time Spent Waiting (%)" is at 79.5333 for event class "Commit"
Oct 6, 2008 9:16:19 AM Oct 6, 2008 9:17:19 AM 1 Metrics "Database Time Spent Waiting (%)" is at 82.38806 for event class "Other"
Oct 6, 2008 8:50:25 AM Oct 6, 2008 8:51:25 AM 1 Metrics "Database Time Spent Waiting (%)" is at 57.47044 for event class "Other"
Oct 6, 2008 8:20:19 AM Oct 6, 2008 8:22:19 AM 2 Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other"
Oct 6, 2008 8:20:19 AM Oct 6, 2008 8:21:19 AM 1 Metrics "Database Time Spent Waiting (%)" is at 45.7133 for event class "Concurrency"
Oct 6, 2008 7:39:46 AM Oct 6, 2008 7:40:49 AM 1.05 Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other"
Oct 6, 2008 7:34:37 AM Oct 6, 2008 7:35:37 AM 1 Metrics "Database Time Spent Waiting (%)" is at 52.15656 for event class "Concurrency"
Oct 6, 2008 7:34:37 AM Oct 6, 2008 7:35:37 AM 1 Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other"
Oct 6, 2008 6:36:27 AM Oct 6, 2008 6:37:27 AM 1 Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other"
Oct 6, 2008 5:04:48 AM Oct 6, 2008 5:06:48 AM 2 Metrics "Database Time Spent Waiting (%)" is at 39.85522 for event class "Other"
Oct 6, 2008 4:50:12 AM Oct 6, 2008 4:51:15 AM 1.05 Metrics "Database Time Spent Waiting (%)" is at 39.58819 for event class "Other"
Oct 6, 2008 4:46:06 AM Oct 6, 2008 4:47:06 AM 1 Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other"
1st question is this the statspack report during problematic period, as there are only 25 sessions during that 48 min period.
Anyway Your Buffer Cache is not big enough and this is the query which needs to be tuned. This is in both top Buffer gets and Disk read.
You check explain plan of this query and try to tune it and if that's not suffiecient you may need to increase your Buffer cache.
select count(alertvo0_.ALERT_ID) as col_0_0_ from ALERT alertvo0
_ where alertvo0_.EH_CODE=:1 and alertvo0_.DELETE_FLAG=:2 and al
ertvo0_.ALERT_TO_USER=:3 and alertvo0_.STATUS=:4
When I am trying to run ur SQL I got following message :
SQL> select count(alertvo0_.ALERT_ID) as col_0_0_ from ALERT alertvo0 where aler
tvo0_.EH_CODE=:1 and alertvo0_.DELETE_FLAG=:2 and alertvo0_.ALERT_TO_USER=:3 and
alertvo0_.STATUS=:4;
SP2-0552: Bind variable "4" not declared.
=
AND MY DB BUFFER size is 1gb
SQL> show sga
Total System Global Area 2298478592 bytes
Fixed Size 2074512 bytes
Variable Size 1207961712 bytes
Database Buffers 1073741824 bytes
Redo Buffers 14700544 bytes