RE: (oracle q000) Streams AQ: waiting for time management or cleanup tasks fall out.

From: <Joel.Patterson_at_crowley.com>
Date: Fri, 22 Jun 2012 10:26:23 -0400
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1C729DA912_at_JAXMSG01.crowley.com>

Found it with the help of Kyle Hailey, as he pointed out some features of DBOptimizer -- which I am learning by osmosis (as opposed to a more sane approach like reading the fine manual -- which BTW might not help when it comes to using your brain to diagnose the issue -- I'm just saying that drilling just a little further was the key to solving the riddle).

Ultimately it comes down to is that the program the database was built for was accessing the sys.aud$ tablespace with about 17 million rows. Without going to deep into the architecture; (ie. zfs, compellent tiered storage -- which this table was on the slowest disks, additional checksum overhead of having sys.aud$ in SYSTEM tablespace, etc.), suffice it to say that by truncating the tablespace, I was able to clear up the problem.

Finding out what the program is wanting to do might not be possible, but it might; time will tell.

I'll look into the ML notes anyway. I appreciate everyones support and ideas.

Best Regards,

Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: Tim Gorman [mailto:tim_at_evdbt.com] Sent: Thursday, June 21, 2012 4:35 PM
To: Patterson, Joel
Cc: ORACLE-L
Subject: Re: (oracle q000) Streams AQ: waiting for time management or cleanup tasks fall out.

If AQ_TM_PROCESSES is set to 10 or above in 10g, please take a look at Oracle Support note #393781.1 and bug #5069930 to see if that matches your symptoms?

On 6/21/2012 11:30 AM, Joel.Patterson_at_crowley.com wrote:
> Yes set to 10.
>
> Usually I see DBSNMP as a known username in v$session. But in any event, I'd like to know what it is doing and why. Maybe I could let it run all weekend -- except that after an hour or two it begins affecting every other database that shares the SAN.
>
> Joel Patterson
> Database Administrator
> 904 727-2546
>
> -----Original Message-----
> From: Michael Dinh [mailto:mdinh_at_XIFIN.Com]
> Sent: Thursday, June 21, 2012 1:24 PM
> To: Patterson, Joel; oratune_at_yahoo.com; oracle-l_at_freelists.org
> Subject: RE: (oracle q000) Streams AQ: waiting for time management or cleanup tasks fall out.
>
> Is your job_queue_processes > 0?
>
> DBSNMP uses AQ as well.
>
> Michael Dinh
> Disparity Breaks Automation (DBA)
>
> Confidence comes not from always being right but from not fearing to
> be wrong - Peter T Mcintyre Great minds discuss ideas; average minds
> discuss events; small minds discuss people - Eleanor Roosevelt When
> any rule or formula becomes a substitute for thought rather than an
> aid to thinking, it is dangerous and should be discarded -Thomas
> William Phelps
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
> Joel.Patterson_at_crowley.com
> Sent: Thursday, June 21, 2012 10:20 AM
> To: oratune_at_yahoo.com; oracle-l_at_freelists.org
> Subject: RE: (oracle q000) Streams AQ: waiting for time management or cleanup tasks fall out.
>
> I'm not using data pump at the moment, especially when first starting the database. I could at any time if that is what you mean by having data pump configured.
> I do not believe I have Streams configured either.
>
> Joel Patterson
> Database Administrator
> 904 727-2546
> From: David Fitzjarrell [mailto:oratune_at_yahoo.com]
> Sent: Thursday, June 21, 2012 10:14 AM
> To: Patterson, Joel; oracle-l_at_freelists.org
> Subject: Re: (oracle q000) Streams AQ: waiting for time management or cleanup tasks fall out.
>
> If you have Data Pump configured then you're using AQ. Remember that AQ is reading from and possibly writing to a queue table and if you also have Streams configured and running the I/O could get heavy.
>
>
> David Fitzjarrell
>
>
> From: "Joel.Patterson_at_crowley.com<mailto:Joel.Patterson_at_crowley.com>"
> <Joel.Patterson_at_crowley.com<mailto:Joel.Patterson_at_crowley.com>>
> To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
> Sent: Thursday, June 21, 2012 6:54 AM
> Subject: (oracle q000) Streams AQ: waiting for time management or cleanup tasks fall out.
>
> When first starting a database I get very high USER IO: I used db optimizer to see what stands out. user 'Unknown', is shown to be consuming IO - it is connected to Non-SQL Activity with an SQL_ID of 0, and a SQL_ID which is connected to the Advanced queuing wait event in the subject line.
> I am unsure how this wait event or session is connected to the IO. Has anyone come across this before? Don't use AQ purposely in this DB or others.
>
>
> Joel Patterson
> Database Administrator
> 904 727-2546

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 22 2012 - 09:26:23 CDT