How to find the most pga consumer sql statement?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How to find the most pga consumer sql statement?

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    How to find the most pga consumer sql statement?

    Hi folks,

    We suffered this early morning from significant virtual memory paging on our main production server during the ETL processes .
    Eventually, we were forced to reboot the server.
    I need to investigate who were the most pga memory consumers sessions before the server's crashing and how many bytes the session used from the pga?
    Is there any historical session table which store this information?

    DB version : 10.2.0.4
    OS : Sun Solaris 10 64 bit

    Thanks in advance,
    Nir

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Cool stats pack report

    You could generate statspack report for the snapshots from that period of time.

    If you are using EM dbconsole or Grid Control, you can generate awr report.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi LKBrwn_DBA,

    Thanks.
    Unfortunately,statspack or even AWR report does not contain this information.
    Any more ideas?

    Thanks,
    Nir

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Are you certain it was an Oracle related process the one that went spinning?
    Is your ETL tool running on the same server?
    I would start by asking systems to identify offending process - that would at least tell who owns it.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi Pablo,

    Yes, I know it's an oracle process and I have a major suspect
    We set to a specific etl session a huge manual pga , meaning , We set in session level:
    workarea_size_policy=manual
    sore_area_size=1Gb
    hash_area_size=1Gb
    After we canceled those manual setting and changed back the parameter workarea_size_policy=auto , the ssupected etl process run well.
    Therefore , I wanted to check if Oracle keep pga consuming statistics per session?

    Regards,
    Nir

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by nir_s View Post
    I wanted to check if Oracle keep pga consuming statistics per session?
    Yes... at least as long as the session is in the system - check v$sesstat

    Feel free to customize query below to show the data you are looking for as well as the particular session/s you are interested in. Found this query some time ago, find it usefull.

    Code:
    set pages 500 lines 100 trims on
    clear col
    col name     format a30 
    col username format a40
    break on username nodup skip 1
    
    select   vses.username||':'||vsst.sid||','||vses.serial# username, 
             vstt.name, 
             max(vsst.value) value 
    from     v$sesstat  vsst, 
             v$statname vstt, 
             v$session  vses
    where    vstt.statistic# = vsst.statistic# 
         and vsst.sid = vses.sid 
         and vstt.name in ('session pga memory','session pga memory max','session uga memory',
                           'session uga memory max','session cursor cache count',
                           'session cursor cache hits','session stored procedure space',
                           'opened cursors current','opened cursors cumulative') 
         and vses.username is not null
    group by vses.username, vsst.sid, vses.serial#, vstt.name 
    order by vses.username, vsst.sid, vses.serial#, vstt.name
    ;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi Pablo,

    Thanks a lot!!
    It looks very nice
    This query is good to catch current sessions.
    What about historical sessions?
    For example, from AWR report I have only sql_id.
    How can I find the information from your query by sql_id instead of sid number?

    Thanks in advance,
    Nir

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width