RE: Tune for 'db file sequential read'

From: Alex Hillman <alex_hillman_at_physia.com>
Date: Thu, 4 May 2000 16:29:59 -0400
Message-Id: <10487.104978@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01BFB607.846A9670
Content-Type: text/plain;

charset="iso-8859-1"

Try this: analyze table <table-name> for table for all indexes for all columns compute statistic. You can increase speed if you need analyzing in parallel, also you can increase sort_area_size for this session. Analyzing for all columns allow optimizer to have more inf when creating execution plan.

Alex Hillman

-----Original Message-----
From: Rajagopal Venkataramany [mailto:rajagopalvr_at_hotmail.com] Sent: Thursday, May 04, 2000 3:48 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Tune for 'db file sequential read'

Hi Deepak,

Try to create histograms whenever you analyze. This can improve CBO plan in a long way.

If the application you are using has been specifically tuned for RULE then you can attempt the following :

The optimizer Mode in Init.ora can be changed to RULE if most of your application(s) are tuned for RULE. Else this suggestion would not help you.
You can set a session to work on RULE based with the Init.ora being set as COST or CHOOSE.
If the default mode is COST or CHOOSE then use RULE hint to force RBO wherever needed.
- If it is possible for you to provide a specific SQL with the background details about the indexes available, table volume, growth etc, I think we can come to a conclusion better.

Regards
Rajagopal Venkataramany

----Original Message Follows----
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Thu, 04 May 2000 10:56:24 -0800

I found out that the explain plan showed the instance on which the query was running faster was RULE based. Adding a RULE hint to the SQL on the slower instance overcame the performance issue. We then tried Analyzing ALL the tables in that query (under CHOOSE). The query didn't improve. So, the only solution is to use RULE hint. It worked in this case, but is there any other solution. It is a peoplesoft environment, where I believe application such as Crystal Report, work better under RULE. The problem, however, is our production is running under CHOOSE, so does it mean we'll have to apply RULE hint at lots of places ???

Deepak
- "Khedr, Waleed" <Waleed.Khedr_at_FMR.COM> wrote:
  > I think the problem is doing thousands of index
  > scans (unique or range
  > scan).
  > Doing thousands of random single block I/O is very
  > sensitive to the
  > performance of the disk system, the memory cache on
  > the top of the disk
  > system, the structure of the index and the size of
  > buffer cache.
  >
  > Increasing the buffer cache could help but will be
  > very limited.
  > Check the performance of the disks that have
  > indexes, percentage busy, queue
  > length, etc.
  > Separate heavily used indexes on separate disks.
  >
  > Also your process could be using index scan on the
  > slow system while it is
  > using FTS on the fast one.
  >
  >
  > Regards,
  >
  > Waleed
  >
  > -----Original Message-----
  > Sent: Wednesday, May 03, 2000 2:07 PM
  > To: Multiple recipients of list ORACLE-L
  >
  >
  > On querying the v$session_event, I could see one
  > process taking a very large wait time. The process
  > takes about 4 Hrs to complete on *this* instance
  > (A),
  > whereas it takes a few minutes on another
  > instance(B)
  > with almost same amount of data. The Metalink pages
  > suggest to test by increasing DB_BLOCK_BUFFERS, but
  > the interesting thing is that instance A's SGA is
  > 128M, whereas instance B's is 44M. Any suggestions ?
  >
  > __________________________________________________
  > Do You Yahoo!?
  > Send instant messages & get email alerts with Yahoo!
  > Messenger.
  > http://im.yahoo.com/
  > --
  > Author: Deepak Sharma
  > INET: sharmakdeep_at_yahoo.com
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  >
  
  > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > (or the name of mailing list you want to be removed
  > from). You may
  > also send the HELP command for other information
  > (like subscribing).
  > --
  > Author: Khedr, Waleed
  > INET: Waleed.Khedr_at_FMR.COM
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  >
  
  > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > (or the name of mailing list you want to be removed
  > from). You may
  > also send the HELP command for other information
  > (like subscribing).
  >

Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger. http://im.yahoo.com/

--
Author: Deepak Sharma
   INET: sharmakdeep_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

-- 
Author: Rajagopal Venkataramany
  INET: rajagopalvr_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

------_=_NextPart_001_01BFB607.846A9670
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: Tune for 'db file sequential read'</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>Try this:&nbsp; analyze table &lt;table-name&gt; for =
table for all indexes for all columns compute statistic. You can =
increase speed if you need analyzing in parallel, also you can increase =
sort_area_size for this session. Analyzing for all columns allow =
optimizer to have more inf when creating execution plan.</FONT></P>

<P><FONT SIZE=3D2>Alex Hillman</FONT>
</P>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Rajagopal Venkataramany [<A =
HREF=3D"mailto:rajagopalvr_at_hotmail.com">mailto:rajagopalvr_at_hotmail.com</=
A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, May 04, 2000 3:48 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: RE: Tune for 'db file sequential =
read'</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>Hi Deepak,</FONT>
</P>

<P><FONT SIZE=3D2>&nbsp; Try to create histograms whenever you analyze. =
This can improve</FONT>
<BR><FONT SIZE=3D2>&nbsp; CBO plan in a long way.</FONT>
</P>

<P><FONT SIZE=3D2>&nbsp; If the application you are using has been =
specifically tuned for RULE</FONT>
<BR><FONT SIZE=3D2>&nbsp; then you can attempt the following :</FONT>
</P>

<P><FONT SIZE=3D2>&nbsp; 1. The optimizer Mode in Init.ora can be =
changed to RULE if most of</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp; your application(s) are =
tuned for RULE. Else this suggestion would</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp; not help you.</FONT>
</P>

<P><FONT SIZE=3D2>&nbsp; 2. You can set a session to work on RULE based =
with the Init.ora</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp; being set as COST or =
CHOOSE.</FONT>
</P>

<P><FONT SIZE=3D2>&nbsp; 3. If the default mode is COST or CHOOSE then =
use RULE hint to force</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp; RBO wherever needed.</FONT>
</P>

<P><FONT SIZE=3D2>&nbsp; ** If it is possible for you to provide a =
specific SQL with the</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp; background details about =
the indexes available, table volume,</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp; growth etc, I think we can =
come to a conclusion better.</FONT>
</P>

<P><FONT SIZE=3D2>Regards</FONT>
<BR><FONT SIZE=3D2>Rajagopal Venkataramany</FONT>
</P>

<P><FONT SIZE=3D2>----Original Message Follows----</FONT>
<BR><FONT SIZE=3D2>Reply-To: ORACLE-L_at_fatcity.com</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L =
&lt;ORACLE-L_at_fatcity.com&gt;</FONT>
<BR><FONT SIZE=3D2>Date: Thu, 04 May 2000 10:56:24 -0800</FONT>
</P>

<P><FONT SIZE=3D2>I found out that the explain plan showed the =
instance</FONT>
<BR><FONT SIZE=3D2>on which the query was running faster was RULE =
based.</FONT>
<BR><FONT SIZE=3D2>Adding a RULE hint to the SQL on the slower =
instance</FONT>
<BR><FONT SIZE=3D2>overcame the performance issue. We then tried</FONT>
<BR><FONT SIZE=3D2>Analyzing ALL the tables in that query (under =
CHOOSE).</FONT>
<BR><FONT SIZE=3D2>The query didn't improve. So, the only solution is =
to</FONT>
<BR><FONT SIZE=3D2>use RULE hint. It worked in this case, but is =
there</FONT>
<BR><FONT SIZE=3D2>any other solution. It is a peoplesoft =
environment,</FONT>
<BR><FONT SIZE=3D2>where I believe application such as Crystal =
Report,</FONT>
<BR><FONT SIZE=3D2>work better under RULE. The problem, however, is =
our</FONT>
<BR><FONT SIZE=3D2>production is running under CHOOSE, so does it =
mean</FONT>
<BR><FONT SIZE=3D2>we'll have to apply RULE hint at lots of places =
???</FONT>
</P>

<P><FONT SIZE=3D2>-- Deepak</FONT>
</P>

<P><FONT SIZE=3D2>--- &quot;Khedr, Waleed&quot; =
&lt;Waleed.Khedr_at_FMR.COM&gt; wrote:</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; I think the problem is doing thousands of =
index</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; scans (unique or range</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; scan).</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Doing thousands of random single block =
I/O is very</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; sensitive to the</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; performance of the disk system, the =
memory cache on</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; the top of the disk</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; system, the structure of the index and =
the size of</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; buffer cache.</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Increasing the buffer cache could help =
but will be</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; very limited.</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Check the performance of the disks that =
have</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; indexes, percentage busy, queue</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; length, etc.</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Separate heavily used indexes on separate =
disks.</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Also your process could be using index =
scan on the</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; slow system while it is</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; using FTS on the fast one.</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Regards,</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Waleed</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; -----Original Message-----</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Sent: Wednesday, May 03, 2000 2:07 =
PM</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; To: Multiple recipients of list =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; On querying the v$session_event, I could =
see one</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; process taking a very large wait time. =
The process</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; takes about 4 Hrs to complete on *this* =
instance</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; (A),</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; whereas it takes a few minutes on =
another</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; instance(B)</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; with almost same amount of data. The =
Metalink pages</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; suggest to test by increasing =
DB_BLOCK_BUFFERS, but</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; the interesting thing is that instance =
A's SGA is</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; 128M, whereas instance B's is 44M. Any =
suggestions ?</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; =
__________________________________________________</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Do You Yahoo!?</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Send instant messages &amp; get email =
alerts with Yahoo!</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Messenger.</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; <A HREF=3D"http://im.yahoo.com/" =
TARGET=3D"_blank">http://im.yahoo.com/</A></FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; --</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Author: Deepak Sharma</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;&nbsp;&nbsp; INET: =
sharmakdeep_at_yahoo.com</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Fat City Network =
Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp; FAX:</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public =
Internet</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; To REMOVE yourself from this mailing =
list, send an</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; E-Mail message</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; to: ListGuru_at_fatcity.com (note EXACT =
spelling of</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; 'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; the message BODY, include a line containin=
g: UNSUB</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; (or the name of mailing list you want to =
be removed</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; from).&nbsp; You may</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; also send the HELP command for other =
information</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; (like subscribing).</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; --</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Author: Khedr, Waleed</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;&nbsp;&nbsp; INET: =
Waleed.Khedr_at_FMR.COM</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Fat City Network =
Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp; FAX:</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public =
Internet</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; To REMOVE yourself from this mailing =
list, send an</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; E-Mail message</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; to: ListGuru_at_fatcity.com (note EXACT =
spelling of</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; 'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; the message BODY, include a line =
containing: UNSUB</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; (or the name of mailing list you want to =
be removed</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; from).&nbsp; You may</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; also send the HELP command for other =
information</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; (like subscribing).</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
</P>

<P><FONT =
SIZE=3D2>__________________________________________________</FONT>
<BR><FONT SIZE=3D2>Do You Yahoo!?</FONT>
<BR><FONT SIZE=3D2>Send instant messages &amp; get email alerts with =
Yahoo! Messenger.</FONT>
<BR><FONT SIZE=3D2><A HREF=3D"http://im.yahoo.com/" =
TARGET=3D"_blank">http://im.yahoo.com/</A></FONT>
<BR><FONT SIZE=3D2>--</FONT>
<BR><FONT SIZE=3D2>Author: Deepak Sharma</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; INET: sharmakdeep_at_yahoo.com</FONT>
</P>

<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) =
538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =
from).&nbsp; You may</FONT>
<BR><FONT SIZE=3D2>also send the HELP command for other information =
(like subscribing).</FONT>
</P>

<P><FONT =
SIZE=3D2>_______________________________________________________________=
_________</FONT>
<BR><FONT SIZE=3D2>Get Your Private, Free E-mail from MSN Hotmail at <A =
HREF=3D"http://www.hotmail.com" =
TARGET=3D"_blank">http://www.hotmail.com</A></FONT>
</P>

<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Rajagopal Venkataramany</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: rajagopalvr_at_hotmail.com</FONT>
</P>

<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) =
538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =

Received on Thu May 04 2000 - 15:29:59 CDT