Database Time Spent Waiting (%): Wait Class Other

Printable View

09-25-2008, 01:36 AM
muzammil.dba

Database Time Spent Waiting (%): Wait Class Other

Dear Friends,

Please help me out to find in my prod db EM below issues.

Sep 25, 2008 9:07:59 AM Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other" -
Sep 25, 2008 9:02:50 AM Metrics "Database Time Spent Waiting (%)" is at 22.76449 for event class "Other" -
Sep 25, 2008 9:01:50 AM Metrics "Database Time Spent Waiting (%)" is at 33.72828 for event class "Other" -
Sep 25, 2008 8:58:44 AM Metrics "Database Time Spent Waiting (%)" is at 20.37493 for event class "Other" -
Sep 25, 2008 8:57:41 AM Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other" -
Sep 25, 2008 8:46:17 AM Metrics "Database Time Spent Waiting (%)" is at 25.87486 for event class "Other" -
Sep 25, 2008 8:45:17 AM Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other" -
Sep 25, 2008 8:33:50 AM Metrics "Database Time Spent Waiting (%)" is at 21.14679 for event class "Other" -
Sep 25, 2008 8:32:47 AM Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other" -
Sep 25, 2008 8:17:14 AM Metrics "Database Time Spent Waiting (%)" is at 25.62567 for event class "Other"

Kindly advice/suggestion.

Thanks in advance.

Best Regards,
DBA
09-25-2008, 03:23 AM
PAVB

It looks to me your alerts are a mess, you can find the truth running this...

Code:

select * from v$system_event where event like '%wait%' order by time_waited_micro DESC ;

... at least, the big picture.
09-25-2008, 03:57 AM
muzammil.dba

please find attached file of output system events.

Please give your valuable suggestion.

Regards,
DBA
09-25-2008, 04:15 AM
malay_biswal

where is the attached file?
09-25-2008, 04:36 AM
muzammil.dba

1 Attachment(s)

Please check now.

Regards,
DBA
09-25-2008, 05:23 AM
PAVB

If some of my guys comes to me with that report I would say... Can't read it, would you mind in formatting the output properly?

... at least do
set linesize 180
set pagesize 999
09-25-2008, 06:10 AM
muzammil.dba

find attached file in HTML format.

waiting for your valuable suggestion.

Regards,
DBA
09-25-2008, 06:16 AM
muzammil.dba

1 Attachment(s)

Please find Zip file for the same.

Sorry for inconvenient.

Best Regards,
DBA
09-26-2008, 03:03 AM
malay_biswal

I do not see any issue from wait events. BTW what's your problem in D/B exactly you are facing?
09-28-2008, 01:29 AM
muzammil.dba

some time is going hung & I have to restart the db.

some time its working fine.
09-28-2008, 05:15 AM
PAVB

Quote:

Originally Posted by muzammil.dba

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?
09-29-2008, 02:46 AM
malay_biswal

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.
10-06-2008, 05:35 AM
muzammil.dba

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
10-06-2008, 05:44 AM
muzammil.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
10-06-2008, 06:09 AM
malay_biswal

It seems you have lot of Writing activities going on, can you uploadthe enture AWR report?
10-06-2008, 07:00 AM
muzammil.dba

How I will get AWR report?
10-06-2008, 08:30 AM
muzammil.dba

1 Attachment(s)

Please find attached zip file of SP Report.
10-07-2008, 01:29 AM
malay_biswal

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
10-07-2008, 03:31 AM
muzammil.dba

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
10-07-2008, 04:25 AM
malay_biswal

Yeah Buffer cache size is in statspack report. You can't execute that SQL just like that, you need to provide the bind varibales.
10-07-2008, 04:44 AM
muzammil.dba

Dear Malay,

Please tel me, hw to provide bind variable and where.

Regards,
10-07-2008, 05:00 AM
malay_biswal

You can do a simple google search and find out what a bind variable is. I'm now confused what your role is and who is supposed to Tune this DB?
10-07-2008, 05:15 AM
muzammil.dba

Dear Malay,

Sorry, I know abt Bind Variable but I confuse u had send that script, I thought it will show some output.

any hw ur suggestion is have to increase db buffer cache size & i will checkit out which quiery taking more time then i will let you now.

Thanks & Regards,
DBA
10-07-2008, 05:33 AM
malay_biswal

Fine. Yes that's the query you need to consider. Only concentrate on tuning that query and after tuning if does not help then you can incraese the buffer acache and verify. Why i'm not strongly recommending to increase buffer cache is the advisory report in statspack report says, it'll not help much.