Re: What are ORA$AT_OS_OPT_SY_665 like 11g jobs?

On Jul 3, 3:50 am, jstuglik <jakub.stug..._at_gmail.com> wrote:
> On 1 Lip, 23:40, Mladen Gogala <mgog..._at_yahoo.com> wrote:
>
> > On Tue, 01 Jul 2008 03:43:57 -0700, jstuglik wrote:
> > > I'm not even from americe so it would be hard for me, wouldn't it? I got
> > > it from oracle's webpage. But you're right - I mistyped - should be: 11g
> > > Release 11.1.0.6.0 - 64bit Production
>
> > Why don't you turn on the trace and see what are those jobs doing.
> > DBMS_MONITOR could probably help you with that.
>
> > --http://mgogala.freehostia.com
>
> Just one more question: do you know how big impact on performance
> turning on tracing could have? It is a production environment and it's
> used intensly so I don't want to slow things down too much by doing
> this.
> Thanks in advance.
>
> Kuba

See
http://blog.tanelpoder.com/2008/06/06/advanced-oracle-troubleshooting-guide-part-5-sampling-v-stuff-with-waitprof-really-fast-using-sql/ and http://www.jlcomp.demon.co.uk/kiddy_scripts.html

There was also a bug long ago where system wide tracing of some sort was left on in the delivered Oracle, which didn't make too much of a problem until the trace file got large, and it seemed the call used to write it would scan the entire thing to figure out the end where it could write.

Obviously, if you are tracing something that has severe performance problems, it could make the problem worse. But if not tracing doesn't solve the problem, you may have no choice. System-wide tracing will have a severe impact, even the docs say that. Or do they? From the 11.1 perf tuning docs: "Although it is possible to enable the SQL Trace facility for a session or for an instance, it is recommended that you use the DBMS_SESSION or DBMS_MONITOR packages instead. When the SQL Trace facility is enabled for a session or for an instance, performance statistics for all SQL statements executed in a user session or in the instance are placed into trace files. Using the SQL Trace facility can have a severe performance impact and may result in increased system overhead, excessive CPU usage, and inadequate disk space."

jg

--
@home.com is bogus.
“It is an 'I told you so' moment.”  http://www.signonsandiego.com/uniontrib/20080704/news_1b4youtube.html