RSS Feed

Method R Discussion Group

Hello,

I am in the performance team for a big banking application.
We have Oracle 10.2.0.3 and a dedicated server configuration.

As the number of users grows we have issues with overall memory consumption because each PGA allocates at least 20 MB.

A lot of caching in PL/SQL package variables is used by the application, and we (the performance team) want to know what is cached at all.

Do you know a method or a product that can profile the usage of memory of a session and tell which objects consume which memory at a given snapshot time?

I tried oradebug dump heapdump 1, but was not able to interpret the results as there is no connection from memory address to the respective object.

Oradebug seems to be the tool of choice, but I found nothing I would call a documentation.

With kind regards,
Dieter

If you really are certain that the application is caching too much data, then I would recommend looking at the cache based on two variables: size and access count.

Essentially, any tool that helps you reduce memory-for-cache consumption will have to show you data based on size and access count.

It's ok to cache a few bytes that are never again reference. But's it's not efficient to do it for significantly large objects or for numerous small ones.

HTH

I don't know an easy way to find details about PL/SQL variables, etc in memory during execution.  The only way I know of to get close would be to instrument your code to attempt to collect the info you seek.  At the very least, you could capture 'session pga memory' statistic values (and the other pga stats as well) from v$sesstat before a procedure starts and then capture the same stats afterward and diff the two to get how much total memory was used for the entire execution.  But, to be able to truly dump memory isn't anything I've seen a product, or even handwritten scripts, to do.

I'll assume you've already done all the usual PGA checks using the various V$ views to verify that PGA is overallocated and that sessions can't get the PGA they need etc. and that is definitely your problem.  But, have you actually traced a session's execution during the time window when you have lots of users and are experiencing the problems that typically occur?  If everything is effected during this time, tracing any session should produce a trace file that would be able to tell you exactly which code is being executed that runs into trouble and consumes the most response time.  You could then focus your attention on that portion of your code to either instrument it more thoroughly to give you more detailed information or perhaps you'd even find a particularly bad single piece of code that, if remedied, could gain you back significant amounts of response time.

If you can get a trace, we'd be happy to help you interpret it and perhaps be able to point you to other actions to take to focus in on the precise root cause of your problems.

There's one more thing I'll add.  You can execute the script (sorry for the formatting) that follows to get a list by SID and USERNAME of PGA memory usage at any given time.  Perhaps if you execute this during a time when you're experiencing the problems, you could identify one or more sessions that are primarily responsible for using large amounts of memory and dive into what code they are executing at that time to see what it is doing in the way of memory usage.

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid                    FORMAT 999            HEADING 'SID'
COLUMN oracle_username        FORMAT a12            HEADING 'Oracle User'    JUSTIFY right
COLUMN os_username            FORMAT a9            HEADING 'O/S User'        JUSTIFY right
COLUMN session_program        FORMAT a18            HEADING 'Session Program' TRUNC
COLUMN session_machine        FORMAT a8            HEADING 'Machine'        JUSTIFY right TRUNC
COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory'
COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA Memory Max'
COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING 'UGA Memory'
COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING 'UGA Memory MAX'

SELECT
    s.sid                sid
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)    os_username
  , s.program            session_program
  , lpad(s.machine,8)    session_machine
  , (select ss.value from v$sesstat ss, v$statname sn
    where ss.sid = s.sid and
          sn.statistic# = ss.statistic# and
          sn.name = 'session pga memory')        session_pga_memory
  , (select ss.value from v$sesstat ss, v$statname sn
    where ss.sid = s.sid and
          sn.statistic# = ss.statistic# and
          sn.name = 'session pga memory max')    session_pga_memory_max
  , (select ss.value from v$sesstat ss, v$statname sn
    where ss.sid = s.sid and
          sn.statistic# = ss.statistic# and
          sn.name = 'session uga memory')        session_uga_memory
  , (select ss.value from v$sesstat ss, v$statname sn
    where ss.sid = s.sid and
          sn.statistic# = ss.statistic# and
          sn.name = 'session uga memory max')    session_uga_memory_max
FROM
    v$session  s
ORDER BY session_pga_memory DESC
/