RE: (oracle q000) Streams AQ: waiting for time management or cleanup tasks fall out.
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <tim@xxxxxxxxx>
- Date: Fri, 22 Jun 2012 10:26:23 -0400
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@xxxxxxxxx]
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@xxxxxxxxxxx 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@xxxxxxxxx]
> Sent: Thursday, June 21, 2012 1:24 PM
> To: Patterson, Joel; oratune@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
> Joel.Patterson@xxxxxxxxxxx
> Sent: Thursday, June 21, 2012 10:20 AM
> To: oratune@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxx]
> Sent: Thursday, June 21, 2012 10:14 AM
> To: Patterson, Joel; oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxx<mailto:Joel.Patterson@xxxxxxxxxxx>"
> <Joel.Patterson@xxxxxxxxxxx<mailto:Joel.Patterson@xxxxxxxxxxx>>
> To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
