• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2330
  • Last Modified:

Increase size of PGA in Oracle 8i

SQL> Set autotrace on
SQL>SELECT r.pfier_account_id,
      p.presc_num,
      spm.product_id,
      p.month,
      t.best_call_state,
      sum(p.trx_count)
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,
      p.presc_num,
      spm.product_id,
      p.month,
      t.best_call_state;

1184732 rows selected.

Elapsed: 00:49:06.12

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

   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)
   5    4         TABLE ACCESS (FULL) OF 'RLUP_ASSIGNED_ACCOUNT' (Cost
          =26 Card=149501 Bytes=1794012)

   6    4         HASH JOIN (Cost=25942 Card=544269 Bytes=27213450)
   7    6           TABLE ACCESS (FULL) OF 'SHERLOCK_PRODUCT_MAPPING'
          (Cost=1 Card=6 Bytes=66)

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


Statistics
----------------------------------------------------------
          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.


0
gram77
Asked:
gram77
1 Solution
 
schwertnerCommented:
Q1.
http://www.ss64.com/orasyntax/initorav8.html
To see the memory used by UGA/PGA processes try this query:

SELECT
   se.SID,
   value,
   se.username,
   se.osuser,
   n.name
FROM
   v$session se,
   v$sesstat s,
   v$statname n
WHERE
   s.statistic# = n.statistic# and
   se.sid = s.sid and
   (n.name = 'session uga memory max' or n.name = 'session pga memory max' )
ORDER BY
   n.name, value;


Q2. Using OEM
http://www.oracle.com/technology/obe/obe10gdb/manage/memmgmt/memmgmt.htm

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

0
 
gram77Author Commented:
1. Is it possible to increase size of PGA in Oracle 8i,
2. Will increasing size of PGA boost performance of the above query.
0
 
Mark GeerlingsDatabase AdministratorCommented:
The biggest problem in this query is *NOT* the sorts!  The biggest problem is:
TABLE ACCESS (FULL) OF 'PRESCRIP_RETAIL' (Cost=25928

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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now