PGA used for each SQL statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: PGA used for each SQL statement

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258

    PGA used for each SQL statement

    Hi, we have a session that is using 190Mb of PGA.
    SID USERNAME PROGRAM LOGON_TIME MACHINE PGA_ALLOC_MB
    ---------- -------- ------------------------- --------------- ------------------------- ------------
    497 LIMARES ResourcePlannerServer.exe 17-JUN 13:59:40 MAG\MA-APPS-095 187.75

    We have extracted the SQL statments this session has ran over the past 24 hours, but we can't get the PGA used for each statement so we can tune it.

    Question: How do we get the PGA used for each SQL statement?

    Thanks. Rgds. Sheryl

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Try doing a set trace events 10046 and a SET EVENTS 'immediate trace name global_area level 1';

    http://www.psoug.org/reference/trace_tkprof.html

    http://www.psoug.org/reference/dump_ora.html

    If you are using PL/SQL collections with large datasets in a large number of session you might begin to see overhead from the amount f PGA memory that can get used. But its a trade off between doing for loops and recursing through data or grabbing everything you need at once.

    This article might help as well.
    http://www.oracle.com/technology/ora.../o38plsql.html
    this space intentionally left blank

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