[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Increase size of PGA in Oracle 8i

Posted on 2007-10-19
4
Medium Priority
?
2,294 Views
Last Modified: 2013-12-19
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
Comment
Question by:gram77
3 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 20109206
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
 

Author Comment

by:gram77
ID: 20109556
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
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 2000 total points
ID: 20110640
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month19 days, 7 hours left to enroll

872 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