Re: parallel query

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 11 Jul 2006 04:34:57 -0700
Message-ID: <1152617697.368767.33880@m79g2000cwm.googlegroups.com>

Prasath wrote:
> Hi Brian,
>
> How to minimize the physical reads? This query joins three tables and
> each table has almost 7 million rows?
>
> Thanks,
> Prasath.
>
> Brian Peasland wrote:
>
> > You have 331,539 physical reads which is most likely contributing more
> > to performance problems than 2 sorts to disk. Are you sure that you are
> > looking at the correct location for your performance problem?
> >
> > If you have WORKAREA_SIZE_POLICY set to auto and your
> > PGA_AGGREGATE_TARGET defined, you can use the and V$PGA_TARGET_ADVICE
> > views to tune your PGA_AGGREGATE_TARGET parameter.
> >
> > HTH,
> > Brian
> >
> > --
> > ===================================================================
> >
> > Brian Peasland
> > oracle_dba_at_nospam.peasland.net
> > http://www.peasland.net

You should expect to see some amount of physical reads when the database is first started, and the rate of additional physical reads should decrease as blocks are loaded into the buffer cache. You have some control over how large the buffer cache is, and what remains in the buffer cache.

There is no right answer for your question, especially given the limited information about the database. Oracle's performance tuning manual, along with tuning books by Cary Millsap and Gaja Krishna Vaidyanatha, along with "Cost-Based Oracle Fundamentals" by Jonathan Lewis will help you find the best solution.

Initial testing:
1. Fire up SQLPLus or some other query tool (Excel will work well for this).
2. Determine your current statistics:
SELECT

  S.NAME,
  S.VALUE SYSTEM_VALUE,
  M.VALUE SESSION_VALUE

FROM
  V$SYSSTAT S,
  V$MYSTAT M
WHERE
  S.STATISTIC#=M.STATISTIC#
  AND S.NAME NOT LIKE '%SQL*Net%'
ORDER BY
  S.NAME;
3. Execute your query.
4. Execute the SQL statement in step 2 again, and compare the results of the two runs.
5. Execute your query.
6. Execute the SQL statement in step 2 again, and compare the results of the three runs.

This should provide a very rough idea of the source of the problem. If the SQL statement is performing full table scans, or if the buffer cache is very small, you will likely see the same increase in values in step 6 as you did in step 4. The statistics may also help identify other problems - investigate any changed values.

More thorough investigation:
Pick up a copy of Cary Millsap's "Optimizing Oracle Performance" and have a go at 10046 traces at level 8 or 12. It could be that the statistics that you are seeing amount to little or no performance penalty. Note: these steps are written in a generic way, so that the same steps may be used to trace your session or any other. 1. Find your SID and SERIAL# (you can determine the SID by querying V$MYSTAT, but you will still need to determine the SERIAL#): SELECT
  SID,
  SERIAL#,
  USERNAME,
  OSUSER,
  MACHINE,
  PROGRAM
FROM
  V$SESSION; 2. In SQLPlus, execute the following, substituting the SID and SERIAL#: EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,8,'');

3. Execute your query.
4. Perform a simple select, such as SELECT SYSDATE FROM DUAL;
5. Execute your query.
6. In SQLPlus, execute the following, substituting the SID and SERIAL#:

EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,0,''); 7. Pick up the trace file, likely stored in the user dump location on the server.
8. Examine the trace file, send the trace file through tkprof, or process it using some other trace file processor that does not lose the detail as tkprof will.

Jonathan Lewis' "Cost-Based Oracle Fundamentals" book will provide an understanding of why the database acts a certain way, how to prove that it is acting that way, and in many cases how to adjust Oracle so that it works effectively for your environment. Gaja Krishna Vaidyanatha's book helps tune the entire instance, while Cary Millsap's book addresses how to tune specific processes that cause performance problems.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Jul 11 2006 - 06:34:57 CDT