| AQ_TM_PROCESSES, which value? [message #293195] |
Fri, 11 January 2008 02:57  |
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 #293223 is a reply to message #293197] |
Fri, 11 January 2008 04:23  |
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 #312230 is a reply to message #293195] |
Tue, 08 April 2008 04:41 |
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 #312247 is a reply to message #312242] |
Tue, 08 April 2008 05:09 |
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 #312960 is a reply to message #293195] |
Thu, 10 April 2008 03:55 |
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 #398761 is a reply to message #293195] |
Fri, 17 April 2009 08:11 |
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;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
