What do you mean by "going hung"?Quote:
Originally Posted by muzammil.dba
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?
Printable View
What do you mean by "going hung"?Quote:
Originally Posted by muzammil.dba
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?
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"
-------------------------------------------------------------------
Regards,
dba
AWR Report:
DB cpu (seconds) 0.00 0.00 0.00
DB time (seconds) 10793.85 2.99 0.63
db block changes 306559.00 84.87 17.94
execute count 99084.00 27.43 5.80
global cache cr block receive time (seconds) 0.00 0.00 0.00
global cache cr blocks received 0.00 0.00 0.00
global cache current block receive time (seconds) 0.00 0.00 0.00
global cache current blocks received 0.00 0.00 0.00
global cache get time (seconds) 0.00 0.00 0.00
global cache gets 0.00 0.00 0.00
opened cursors cumulative 15820.00 4.38 0.93
parse count (total) 16720.00 4.63 0.98
parse time cpu (seconds) 0.09 0.00 0.00
parse time elapsed (seconds) 0.26 0.00 0.00
physical reads 14.00 0.00 0.00
physical writes 6446.00 1.78 0.38
redo size (KB) 50233.90 13.91 2.94
session cursor cache hits 8794.00 2.43 0.51
session logical reads 1077166.00 298.22 63.03
sql execute cpu time (seconds) 0.00 0.00 0.00
sql execute elapsed time (seconds) 0.00 0.00 0.00
user calls 20412.00 5.65 1.19
user commits 13039.00 3.61 0.76
user rollbacks 4051.00 1.12 0.24
workarea executions - multipass 0.00 0.00 0.00
workarea executions - onepass 0.00 0.00 0.00
workarea executions - optimal 2697.00 0.75 0.16
It seems you have lot of Writing activities going on, can you uploadthe enture AWR report?
How I will get AWR report?
Please find attached zip file of SP Report.
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
Dear Malay,
Thanks for your help.
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
Regards,
dba
Yeah Buffer cache size is in statspack report. You can't execute that SQL just like that, you need to provide the bind varibales.