Re: MMON_SLAVE seems high CPU with "select longname from javasnm$ where short = :1"

From: Stefan Koehler <contact_at_soocs.de>
Date: Tue, 1 Dec 2015 10:53:45 +0100 (CET)
Message-ID: <788065197.941205.1448963625666.JavaMail.open-xchange_at_app02.ox.hosteurope.de>

Hi Kim,
i have no idea about all that JVM dev stuff, but can possibly answer some of your questions.

> I have tried to enable trace for the session in order to examine bind variable content to find out which Java identifiers are being looked up. But
> no trace file is created in the usual place? (The session also has AudSid=0 and AudSid usually is part of trace file name as far as I know.)

If the SQL is still running and burning up CPU only, you won't see any(thing) in trace file until another db-/syscall is executed. However you can also dump the bind variables of an existing cursor / running SQL without SQL trace. Just dump an errorstack and search for "Session Cursor Dump":   SQL> oradebug setospid <PID_OF_MMON_SLAVE>   SQL> oradebug dump errorstack 3

> Q1: Are trace files from background processes placed in other directory? If so, where can I find which directory?
No. DIAG directory in folder trace.

> Q2: Is it possible the 20% of a core CPU expenditure shown in Cloud Control is not actually CPU expenditure but wrongly reported?
If you have 4 CPUs and one process is running on CPU only - 20% could be reasonable. However i would always use OS tools to verify this.  

> Q3: Any suggestions for how to dig into what actually is happening with that statement?
Check execution plan, capture and check session statistics, capture stack traces. CPU issues could be more difficult to troubleshoot, but maybe my latest DOAG talk about this may help and point you in the right direction: http://www.soocs.de/public/talk/

Best Regards
Stefan Koehler

Freelance Oracle performance consultant and researcher Homepage: http://www.soocs.de
Twitter: _at_OracleSK

> Kim Berg Hansen <kibeha_at_gmail.com> hat am 1. Dezember 2015 um 09:53 geschrieben:
>
> Hi, List
>
> We have a database Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production on Oracle Linux Server release 6.5 with 4 cores.
>
> One statement seems to practically continously use 20% of a core when I look at top activity in Cloud Control (or Enterprise Manager or whatever it
> is called):
>
> select longname
> from javasnm$
> where short = :1
>
> All of it executed by a single session with Module=MMON_SLAVE, Action=JAVAVM JIT slave action.
>
> We do use some Java and XML in the database, but I do not believe it to be huge amounts.
> Besides, I would think then it would be executed in user processes and not this single background process?
>
> I have tried to enable trace for the session in order to examine bind variable content to find out which Java identifiers are being looked up.
> But no trace file is created in the usual place? (The session also has AudSid=0 and AudSid usually is part of trace file name as far as I know.)
>
> Q1: Are trace files from background processes placed in other directory? If so, where can I find which directory?
>
>
> The wait event for the session seem to be JOX Jit Process Sleep.
> As far as I can tell it is an idle event.
> MOS note 1075283.1 describes it might show up in top list as a bug in 11.1 that should be fixed in 11.2.
>
> Q2: Is it possible the 20% of a core CPU expenditure shown in Cloud Control is not actually CPU expenditure but wrongly reported?
>
>
> Q3: Any suggestions for how to dig into what actually is happening with that statement?
> (Keeping in mind I am primarily a developer ;-)
>
>
> Thanks in advance for any hints I might use for further digging.
>
>
> Regards
>
>
> Kim Berg Hansen
>
> http://www.kibeha.dk
> kibeha_at_kibeha.dk <mailto:kibeha_at_kibeha.dk>
> _at_kibeha <http://twitter.com/kibeha>

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

Received on Tue Dec 01 2015 - 10:53:45 CET