Streams AQ wait event on Oracle 10g

[nicholas_bourgon]

Hello,

I have ECC 6.0 on W2k3 with Oracle. I have some wait event about Streams AQ :

Streams AQ: waiting for messages in the queue

Streams AQ: qmn slave idle wait

Streams AQ: qmn coordinator waiting for slave to start

What does it mean ? What can I do to fix that?

From what I read, it's seems to have something to do with parameter : aq_tm_processes

What this parameter whould be set to? It seems to be set to O now.

Thank you for any help,

Nicholas

[markus_doehr2 · Accepted solution]

A good start is

Note 619188 - FAQ: Oracle wait events

and the Oracle documentation at

http://download-west.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams008.htm

which recommends setting it to 0.

Markus

[nicholas_bourgon · Accepted solution]

Thank you for the answer but it said this opposite :

"Oracle strongly recommends that you do NOT set AQ_TM_PROCESSES to 0."

I did not set it to 0. It was the default when I installed ECC. SO I'm not sure what to do with it

Any other help?

Nicholas

[markus_doehr2 · Accepted solution]

> "Oracle strongly recommends that you do NOT set AQ_TM_PROCESSES to 0."

> I did not set it to 0. It was the default when I installed ECC. SO I'm not sure what to do with it

You're right, I misread that.

Do you use Oracle Streams? According to

Note 105047 - Support for Oracle functions in the SAP environment

it cannot be used.

Markus

[nicholas_bourgon · Accepted solution]

No I don't.

But when I go to ST04 -> Wait Event Analysis -> System Event -> Wait Event details. I have big wait event with streams AQ. Some with 4 seconds average time which seems very long to me.

So I try to figure out why and what can I do to fix it?

Also, since Oracle strongly suggest not to set the parameter aq_tm_processes to 0 and by default it is to 0, I try to figure out what should I do with this parameter.

Thank you,

Nicholas

[former_member524429 · Accepted solution]

Hi,

Does your alert<sid>.log file is telling something related to these events or AQ_TM_PROCESSES parameter ?

Does your spfile contains the entry for AQ_TM_PROCESSES parameter ?

As per this SAP Note 669902,

AQ_TM_PROCESSES should not be set in SPFILE (see Note 830576), therefore SCOPE=MEMORY.
If they were inadvertently set in SPFILE, you can delete them from SPFILE again using:
..
SQL> ALTER SYSTEM RESET AQ_TM_PROCESSES SCOPE=SPFILE SID='*';

Regards,

Bhavik G. Shroff

[markus_doehr2 · Accepted solution]

Since Streams is not used that parameter is not really relevant.

Metalink "Queue Monitor Process: Architecture and Known Issues [ID 305662.1]" describes that the parameter should be "reset" as opposed to be set to 0.

I just checked, we have the same wait event(s) in our database but they don't consume a significant time if you compare them with other events.

Markus

[nicholas_bourgon · Accepted solution]

Hello,

I don't have any errors in my alert_sim.log concerning parameter aq_tm_processes.

The parameter aq_tm_processes is not set in spfile but if I do a show parameter I do see that it is set to 0 because it's the default value for that parameter.

That funny that Oracle strongly recommends that you do NOT set AQ_TM_PROCESSES to 0 and it's the default value.

Am I missing something ?

Thank you,

Nicholas

Edited by: Nicholas Bourgon on Mar 17, 2010 11:49 AM

[fidel_vales · Accepted solution]

Hi,

The events you mention are "idle" events, nothing to be "fixed"

When you take a look at the DB time, you "eliminate" the idle events, you should not count them.

[former_member524429 · Accepted solution]

Hi,

What is the Patch Level of Oracle 10g which is in use ?

Please refer Oracle Meta link 428441.1 to get more information. It will tell you the reason and the possible alternatives to deal with it. You can refer SAP Note 758563 to get Oracle Meta link access.

Unless you use Oracle Streams Advanced Queuing , there's no need to set this parameter.

If AQ_TM_PROCESSES is not specified or is set to 0, then the queue monitor is not created.

In 10gR2 parameter AQ_TM_PROCESSES shouldn't be set explicitly in pfile/spfile, because Oracle autotunes it.

Also refer the [this link|; to get more info.

Regards,

Bhavik G. Shroff

[nicholas_bourgon · Accepted solution]

Thank you for your answer.

Fidel : I have been told by an sap trainer that Wait Events that were their : ST04 -> Wait Event Analysis -> System Event -> Wait Event details. Were not Idlle Events. Maybe she was wrong.

Nicholas

[fidel_vales · Accepted solution]

Hi,

You are correct, I was mistaken with the SAP version

There you "should" see only non idle events.

BUT, the idle events are defined on the "local" table ora_idle_Events

And, in order to have them current you must apply the support packages mentioned on the note 1176846.

On that table the following events are defined as "idle":

Streams AQ: RAC qmn coordinator idle wait

Streams AQ: deallocate messages from Streams Pool

Streams AQ: delete acknowledged messages

Streams AQ: qmn coordinator idle wait

Streams AQ: qmn slave idle wait

Streams AQ: waiting for messages in the queue

Streams AQ: waiting for time management or cleanup tasks

Streams fetch slave: waiting for txns

Review the mentioned note.

If you do not use "streams" then you do not need to worry about them, they are idle.