Home » Server Options » Streams & AQ » AQ_TM_PROCESSES, which value? (10.2.0.3, win2003, RAC)
AQ_TM_PROCESSES, which value? [message #293195] Fri, 11 January 2008 02:57 Go to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

One of our DB runs slow. So I ran ADDM and found this:


FINDING 4: 43% impact (3644 seconds)
------------------------------------
Wait event "Streams AQ: qmn coordinator waiting for slave to start" in wait
class "Other" was consuming significant database time.

   RECOMMENDATION 1: Application Analysis, 43% benefit (3644 seconds)
      ACTION: Investigate the cause for high "Streams AQ: qmn coordinator
         waiting for slave to start" waits. Refer to Oracle's "Database
         Reference" for the description of this wait event.



Doing som reseach I found out that this has something to do with
AQ_TM_PROCESSES. It is set to 2 in our DB.
Oracle recommends that you dont set this value at all in 10g. But when I try to change it in EM, I must set a value 0-10.

So, what am I doing wrong, how do I 'unset' this value?

Regards
H
Re: AQ_TM_PROCESSES, which value? [message #293197 is a reply to message #293195] Fri, 11 January 2008 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
alter system reset ...

Regards
Michel
Re: AQ_TM_PROCESSES, which value? [message #293223 is a reply to message #293197] Fri, 11 January 2008 04:23 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi and thanks for your help!

I have tried this:

SQL> alter system reset AQ_TM_PROCESSES scope=both sid='*';
alter system reset AQ_TM_PROCESSES scope=both sid='*'
*
ERROR at line 1:
ORA-32009: cannot reset the memory value for instance * from instance db031

SQL> alter system reset AQ_TM_PROCESSES scope=both sid='DB031';
alter system reset AQ_TM_PROCESSES scope=both sid='DB031'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE


So, I can of course change the value in EM, but I want Oracle to take care of this parameter (and I must specify a value between 0-10 if I use EM). I guess I have to use reset? But Im not able to do that, as you can see from the above result.

Any new ideas?

Regards
H
Re: AQ_TM_PROCESSES, which value? [message #293228 is a reply to message #293223] Fri, 11 January 2008 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-32010: cannot find entry to delete in SPFILE
 *Cause:  The SPFILE did not contain the sid.parameter entry

You have to use:
SQL> alter system reset AQ_TM_PROCESSES scope=spfile sid='*';

System altered.

Regards
Michel

Re: AQ_TM_PROCESSES, which value? [message #293234 is a reply to message #293228] Fri, 11 January 2008 04:54 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Thanks for your help.

Regards
H
Re: AQ_TM_PROCESSES, which value? [message #312230 is a reply to message #293195] Tue, 08 April 2008 04:41 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

Instead of creating a new thread I use this one as I have a question about the same parameter (and I created this thread from the beginning).

This is our initfile:
#aq_tm_processes = 2

After starting up the DB and I check this parameter its value is 0. Which is not recommended...

How is this possible? If I do not set this value at all, should not Oracle set a value (and it should not set it to 0, thats for sure!).

Have I missed something here?

Regards
Hristo
Re: AQ_TM_PROCESSES, which value? [message #312242 is a reply to message #312230] Tue, 08 April 2008 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How is this possible? If I do not set this value at all, should not Oracle set a value

It did it, it set it to its default value which is... 0.

Regards
Michel
Re: AQ_TM_PROCESSES, which value? [message #312247 is a reply to message #312242] Tue, 08 April 2008 05:09 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

So you should set this value? As setting this value to 0 can effect the use of streams.

From the Oracle Advanced Queueing Manual:


If you want to disable the Queue Monitor Coordinator, then you must set AQ_TM_PROCESSES = 0 in your pfile or spfile. Oracle strongly recommends that you do NOT set AQ_TM_PROCESSES = 0. If you are using Oracle Streams, then setting this parameter to zero (which Oracle Database respects no matter what) can cause serious problems.

Im not sure that it is this that are messing up our streams (but we did changes this weekend, and AQ_TM_PROCESSES was one of them), but why does Oracle set the value to 0, very strange if you read the above.

Regards
Hristo

[Updated on: Tue, 08 April 2008 05:09]

Report message to a moderator

Re: AQ_TM_PROCESSES, which value? [message #312446 is a reply to message #312247] Tue, 08 April 2008 20:57 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
ORA-32010: cannot find entry to delete in SPFILE
 *Cause:  The SPFILE did not contain the sid.parameter entry

This would lead me to believe that aq_tm_processes isn't explicitly set in your spfile. To ensure that the value isn't set you could create a pfile from your spfile and check the entries.

As you mentioned, with 10.2, it is recommended to unset the aq_tm_processes and let Oracle manage it automatically. To completely unset the value you have to restart the database after you run the reset command Michel recommended.

Re: AQ_TM_PROCESSES, which value? [message #312504 is a reply to message #312446] Wed, 09 April 2008 01:33 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

I # away the AQ parameter in the pfile and started up one DB using its pfile. The value is still 0. We have now set the value two 2.
Re: AQ_TM_PROCESSES, which value? [message #312516 is a reply to message #312504] Wed, 09 April 2008 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does # mean?

Regards
Michel
Re: AQ_TM_PROCESSES, which value? [message #312536 is a reply to message #312516] Wed, 09 April 2008 03:15 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
# = uncomment (comment away, dont really now the expression in english), the value after are not used
Re: AQ_TM_PROCESSES, which value? [message #312562 is a reply to message #312504] Wed, 09 April 2008 04:48 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
hristo wrote on Wed, 09 April 2008 02:33

I # away the AQ parameter in the pfile and started up one DB using its pfile. The value is still 0. We have now set the value two 2.

If you have commented out the parameter it will still show as 0 if you query it's value. That is to be expected. The important part is that you DON'T explicitly have the parameter set in the pfile/spfile. I would NOT recommend setting this value to 2. We saw drastic issues when we had this value set.

I would reset the parameter as Michel indicated and restart the database.

Re: AQ_TM_PROCESSES, which value? [message #312581 is a reply to message #312562] Wed, 09 April 2008 06:04 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Ok! So I have to use reset. But the streams worked fine before, when the valeu was 2. Have to test this more before making a decision.

Regards
H
Re: AQ_TM_PROCESSES, which value? [message #312960 is a reply to message #293195] Thu, 10 April 2008 03:55 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

This is what I did:

1. Took down the DB.
2. Set the aq_tm_processes in the pfile to 2.
3. Startup nomonunt from pfile.
4. Created a new spfile form pfile.
5. Startup DB.
6. alter system reset aq_tm_processes scope=spfile sid='*';
7. Restarted the DB.

Is the aq_tm_processes now reset?

Show parameter shows the value 0.

alter system reset aq_tm_processes scope=spfile sid='*'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

It seems that the value is reset (as the parameter can not be found). But Im still worried. Should the value show 0 if the parameter is reset?

Regards
H

[Updated on: Thu, 10 April 2008 04:22]

Report message to a moderator

Re: AQ_TM_PROCESSES, which value? [message #313142 is a reply to message #312960] Thu, 10 April 2008 18:48 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
This is a RAC database, so you should only have one spfile. Are you certain you are updating ONE spfile?
Re: AQ_TM_PROCESSES, which value? [message #313218 is a reply to message #313142] Fri, 11 April 2008 01:56 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Yes, there are only one spfile. We do have three pfiles, one for each node.

[Updated on: Fri, 11 April 2008 02:26]

Report message to a moderator

Re: AQ_TM_PROCESSES, which value? [message #398761 is a reply to message #293195] Fri, 17 April 2009 08:11 Go to previous messageGo to next message
WillIm
Messages: 13
Registered: June 2008
Location: Kyrgyzstan, Bishkek
Junior Member
Hello! I'm not sure that this thread is still urgent, but I found it to be not finished. There are some issues still need to be clarified.

1) If you have value of aq_tm_processes parameter set in "0" and value of ISDEFAULT = TRUE , then it means that you've reset it to default value and Oracle would automatically tune it.
2) If you reset this parameter delete the string of it from your spfile in order not to reset it the next time when DB is started.

To know whether aq_tm_processes parameter was modified or not run this script:

mycheck number;
begin
select 1 into mycheck from v$parameter where name = 'aq_tm_processes' and value = '0'
and (ismodified <> 'FALSE' OR isdefault='FALSE');
if mycheck = 1 then
dbms_output.put_line('The parameter ''aq_tm_processes'' is explicitly set to 0!');
end if;
exception when no_data_found then
dbms_output.put_line('The parameter ''aq_tm_processes'' is not explicitly set to 0.');
end;
Re: AQ_TM_PROCESSES, which value? [message #406685 is a reply to message #398761] Fri, 05 June 2009 06:32 Go to previous messageGo to next message
lspeedup
Messages: 1
Registered: June 2009
Location: Shanghai
Junior Member
Some information may help
Metalink Note:305662.1
  [Edit MC: Metalink note content removed]

[Updated on: Sat, 06 June 2009 01:12] by Moderator

Report message to a moderator

Re: AQ_TM_PROCESSES, which value? [message #406784 is a reply to message #406685] Fri, 05 June 2009 18:02 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Welcome to OraFaq !!!

We don't have permission to post metalink article to any sites.

Please read the OraFAQ Forum Guide before posting.
http://www.orafaq.com/forum/t/88153/0/

Thanks
Re: AQ_TM_PROCESSES, which value? [message #431423 is a reply to message #293195] Tue, 17 November 2009 05:13 Go to previous messageGo to next message
cheehongjb
Messages: 7
Registered: January 2009
Location: Kuala Lumpur - MALAYSIA
Junior Member

Dear expert,

i would like to know the detail of parameter AQ_TM_PROCESSES = 1, will this dequeue the queue process for oracle?

if AQ_TM_PROCESSES = 2 compare to AQ_TM_PROCESSES = 1, what is the different.

Thanks

best regards
terence
Re: AQ_TM_PROCESSES, which value? [message #431427 is a reply to message #431423] Tue, 17 November 2009 05:20 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
It is irrelevant. Don't worry about it, let Oracle handle it (AQ_TM_PROCESSES Parameter No Longer Needed in init.ora).

Since you don't post a version, I'll assume 10g !
Re: AQ_TM_PROCESSES, which value? [message #431516 is a reply to message #431427] Tue, 17 November 2009 21:19 Go to previous message
cheehongjb
Messages: 7
Registered: January 2009
Location: Kuala Lumpur - MALAYSIA
Junior Member

i also not sure the client version. but i m sure the version is not latest then oracle 9i.

Best Regards
Terence
Previous Topic: Error mail received from production server
Next Topic: process or task in queue is not dequeue
Goto Forum:
  


Current Time: Fri Mar 29 02:26:50 CDT 2024