Re: Gather_system

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 19 Mar 2003 11:26:12 -0700
Message-Id: <24715.322411@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_01C2EE45.3E1EF3D8
Content-Type: text/plain

DO understand the implications of gathering system statistics. Once you have system statistics, the cbo adds a cpu cost element to the plan costs. In my experience it is largely marginal, especially if you have fast cpus. The bulk cost is still coming from the IO cost. But with system statistics gethered, the cbo also starts distinguishing between single block IO (sequential_reads) and multiblock IO (scattered_reads) costs. Pay attention the the MBRC statistic and the ratio of mreadtim:sreadtim. Those two together now determine the cost of scans (FTS and FFS) and that can have quite an effect on the access path choice. I am preparing a presentation for IOUG Live 2003 on CBO related changes in 9i.

At 07:03 AM 3/19/2003 -0800, you wrote:

>Hi everyone
>
>I was trying to migrate one of our applications to 9.2 and put in cost at 
>the same time
>
>I came across Gather_system_stats and was wondering if anyone had used it?
>Did it improve the executions plan choice at all?
>
>Thanks
>David Hill
>DBA

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier electronique est une communication privee a l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'etes pas le destinataire prevu, vous etes avise, par les presentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'etes pas specifiquement autorise a recevoir ce courriel ou si vous croyez l'avoir recu par erreur, veuillez en aviser l'expediteur original immediatement. Nous respectons les demandes similaires qui touchent la confidentialite des communications par courrier electronique.

------_=_NextPart_001_01C2EE45.3E1EF3D8
Content-Type: text/html
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=3DUS-ASCII">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2655.35">
<TITLE>Re: Gather_system_stats</TITLE>
</HEAD>
<BODY>

DO understand the implications of gathering system =
statistics. Once you 
 have system statistics, the cbo adds a cpu cost =
element to the plan costs. 
 In my experience it is largely marginal, especially =
if you have fast cpus. 
 The bulk cost is still coming from the IO cost. But =
with system statistics 
 gethered, the cbo also starts distinguishing between =
single block IO 
 (sequential_reads) and multiblock IO =
(scattered_reads) costs. Pay attention 
 the the MBRC statistic and the ratio of =
mreadtim:sreadtim. Those two 
 together now determine the cost of scans (FTS and =
FFS) and that can have 
 quite an effect on the access path choice.
 I am preparing a presentation for IOUG Live 2003 on =
CBO related changes in 9i.


At 07:03 AM 3/19/2003 -0800, you wrote:


>Hi everyone
 >
 >I was trying to migrate one of our applications =
to 9.2 and put in cost at 
 >the same time
 >
 >I came across Gather_system_stats and was =
wondering if anyone had used it?
 >Did it improve the executions plan choice at =

all?</FONT>


<BR><FONT SIZE=3D2>&gt;</FONT>

<BR><FONT SIZE=3D2>&gt;Thanks</FONT>

<BR><FONT SIZE=3D2>&gt;David Hill</FONT>

 >DBA


Wolfgang Breitling
 Centrex Consulting Corporation
 <A HREF=3D"http://www.centrexcc.com" =
TARGET=3D"_blank">http://www.centrexcc.com</A>

 

          &nb= sp;           &nb=

sp;&nbsp; ********************</FONT>

This email communication is intended as a private =
communication for the sole use of the primary addressee and those =

individuals listed for copies in the original message. The information =
contained in this email is private and confidential and if you are not =
an intended recipient you are hereby notified that copying, forwarding =

or other dissemination or distribution of this communication by any = means is prohibited.  If you are not specifically authorized to = receive this email and if you believe that you received it in error = please notify the original sender immediately.  We honour similar = requests relating to the privacy of email communications.

Cette communication par courrier électronique =
est une communication privée à l'usage exclusif du = destinataire principal ainsi que des personnes dont les noms figurent = en copie.  Les renseignements contenus dans ce courriel sont = confidentiels et si vous n'êtes pas le destinataire prévu, = vous êtes avisé, par les présentes que toute = reproduction, tout transfert ou toute autre forme de diffusion de cette = communication par quelque moyen que ce soit est interdit.  Si vous = n'êtes pas spécifiquement autorisé à = recevoir ce courriel ou si vous croyez l'avoir reçu par erreur, = veuillez en aviser l'expéditeur original = immédiatement.  Nous respectons les demandes similaires qui = touchent la confidentialité des communications par courrier = Received on Wed Mar 19 2003 - 12:26:12 CST