Increase size of PGA in Oracle 8i

Posted on 2007-10-19
Last Modified: 2013-12-19
SQL> Set autotrace on
SQL>SELECT r.pfier_account_id,
FROM rlup_assigned_account r,
      temp_presc_num_TEST t,
      retail.prescrip_retail partition (PRESC200705) p,
      sherlock.sherlock_product_mapping spm
WHERE spm.product_id like '056%'
      and spm.mds6 = p.product_id
      and t.CLIENT_ID = p.presc_num
      and r.ndc_pyr_id = p.payer_plan
      and t.best_call_state = r.ST
GROUP BY r.pfier_account_id,

1184732 rows selected.

Elapsed: 00:49:06.12

Execution Plan
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=84650 Card=958 Bytes

   1    0   SORT (GROUP BY) (Cost=84650 Card=958 Bytes=75682)
   2    1     HASH JOIN (Cost=26021 Card=9558590 Bytes=755128610)
   3    2       TABLE ACCESS (FULL) OF 'TEMP_PRESC_NUM_TEST' (Cost=2 C
          ard=4908 Bytes=83436)

   4    2       HASH JOIN (Cost=25995 Card=19475529 Bytes=1207482798)
          =26 Card=149501 Bytes=1794012)

   6    4         HASH JOIN (Cost=25942 Card=544269 Bytes=27213450)
          (Cost=1 Card=6 Bytes=66)

   8    6           TABLE ACCESS (FULL) OF 'PRESCRIP_RETAIL' (Cost=259
          28 Card=141147085 Bytes=5504736315)

          0  recursive calls
         33  db block gets
     672144  consistent gets
     676753  physical reads
          0  redo size
   73064006  bytes sent via SQL*Net to client
    8767427  bytes received via SQL*Net from client
      78984  SQL*Net roundtrips to/from client
          5  sorts (memory)
          1  sorts (disk)
    1184732  rows processed

Clearly, with 5 sorts in memory and 1 in disk, PGA memory is adequate.

Q1 However, to tune the query further can we increase the size of the PGA in Oracle8i so that all sorts happen in memory.

Q2 Can we increase the size of PGA in Oracle 10g

Q3. How to increase the performance of this query? This query uses partitions.

Question by:gram77
    LVL 47

    Expert Comment

    To see the memory used by UGA/PGA processes try this query:

       v$session se,
       v$sesstat s,
       v$statname n
       s.statistic# = n.statistic# and
       se.sid = s.sid and
       ( = 'session uga memory max' or = 'session pga memory max' )
    ORDER BY, value;

    Q2. Using OEM

    Q3. Creating partition indexes and regularly running the statistics to keep it fresh


    Author Comment

    1. Is it possible to increase size of PGA in Oracle 8i,
    2. Will increasing size of PGA boost performance of the above query.
    LVL 34

    Accepted Solution

    The biggest problem in this query is *NOT* the sorts!  The biggest problem is:

    The other full table scans may also be problems, but they appear to be small tables/partitions, so they may not be so bad (since they all have low cost values).

    No, changing the size of the PGA will *NOT* help avoid the "TABLE ACCESS (FULL)".

    What can you do to reduce the number of rows retrieved from the 'PRESCRIP_RETAIL' partition?  Or, do you want to include all rows from this partition?

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now