RE: AW: Extremely slow querry

From: Alex Hillman <alex_hillman_at_physia.com>
Date: Sat, 5 Aug 2000 12:36:55 -0400
Message-Id: <10580.113912@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_01BFFEFB.5DDFBE30
Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

You should analyze FOR TABLE FOR ALL INDEXES FOR ALL COLUMNS, not only = FOR
ALL INDEXED COLUMNS. Optimizer gets useful info from analyzing = non-indexed
columns also.

Alex Hillman

-----Original Message-----
From: Vidya Kalyanaraman [mailto:kvidya13_at_hotmail.com] Sent: Friday, August 04, 2000 11:58 AM
To: Multiple recipients of list ORACLE-L Subject: Re: AW: Extremely slow querry

Hi Volker

Normally when u analyze a table, all the associated indexes are=20 automatically analyzed as well.
But from 7.3.4, you have a better way to analyze table/indexes with "FOR" clause of ANALYZE command.
ANALYZE TABLE table_name COMPUTE STATISTICS FOR TABLE FOR ALL INDEXED=20 COLUMNS; You can specify the percentage sampling for ESTIMATE statistics, but = again=20
if you specifiy more than 40% or 50%(I am not really sure about exact=20 value), it is similar to COMPUTE statistics.

Hope it helps
Thanks
Vidya

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Fri, 04 Aug 2000 02:18:43 -0800

analyze table PS_JOB JOB compute statistics; analyze table PS_FAST_SCRTY_2 SEC compute statistics;

do the same for indexes on this tables

analyze index i???? compute statistics;

Volker Sch=F6n
E-Mail: mailto:v.schoen_at_inplan.de <mailto:v.schoen_at_inplan.de> http://www.inplan.de <http://www.inplan.de/>

-----Urspr=FCngliche Nachricht-----
Von: Siva_Chintalapati [mailto:Siva_Chintalapati_at_satyam.com] Gesendet: Freitag, 4. August 2000 08:34
An: Multiple recipients of list ORACLE-L Betreff: RE: Extremely slow querry

Hello ,
Please say me how to compute and estimate stastics.

Regards
Siva

Reply To: ORACLE-L_at_fatcity.com
Sent: Thursday, August 03, 2000 7:16 PM To: Multiple recipients of list ORACLE-L

Just a thought,
Try analyzing the indexes on those tables. Also, if possible, try to use COMPUTE instead of ESTIMATE.

Do the Explain Plan again and compare it with the previous one.

Suggestion: you might want to run a script to analyze the objects(tables,
indexes, etc.) on a regular basis using cron.

HTP. Gunawan Yuwono
Oracle DBA
Kansas City, MO

	>--- Original Message ---

>From: "Jack van Zanen" <nlzanen1_at_ey.nl>
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Date: 8/3/00 11:25:28 AM
>

>
>
>Hi All,
>
>
>Peoplesoap database
>Oracle 8.0.5
>AIX 4.3.2
>
>
>*************
>SELECT
> JOB.EMPLID,
> JOB.EMPL_RCD#,
> SEC.OPRCLASS,
> SEC.ACCESS_CD
>FROM
> PS_JOB JOB,
> PS_FAST_SCRTY_2 SEC
>WHERE
> SEC.ACCESS_CD =3D 'Y'
> AND SEC.SETID =3D JOB.SETID_DEPT
> AND SEC.DEPTID =3D JOB.DEPTID
>
>****************
>
>This is a select that is part of a PS view (stripped version)
and takes 30
>minutes to run
>The explain plan shows just a nested loop with the smaller table
>(ps_fast_scrty_2 5500 records) as the driving table
>and the large table (180.000 records) is not even accessed,
all needed
>information comes out of the index.
>
>This is a small database on a machine that should be capable
of a lot more.
>
>Anybody any idea???
>
>

=
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>De informatie verzonden met dit E-mail bericht is uitsluitend
bestemd voor
>de geadresseerde. Gebruik van deze informatie door anderen dan
de
>geadresseerde is verboden. Openbaarmaking, vermenigvuldiging,
verspreiding
>en/of verstrekking van deze informatie aan derden is niet toegestaan.
>Ernst & Young staat niet in voor de juiste en volledige overbrenging
van de
>inhoud van een verzonden E-mail, noch voor tijdige ontvangst
daarvan.
=
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>The information contained in this communication is confidential
and may be
>legally privileged. It is intended solely for the use of the
individual or
>entity to whom it is addressed and others authorised to receive
it. If you
>are not the intended recipient you are hereby notified that
any disclosure,
>copying, distribution or taking any action in reliance on the
contents of
>this information is strictly prohibited and may be unlawful.
Ernst &
>Young is neither liable for the proper and complete transmission
of the
>information contained in this communication nor for any delay
in its
>receipt.

=
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
>
>
>--
>Author: Jack van Zanen
> INET: nlzanen1_at_ey.nl
>
>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).
>
>

Sent from a WebBox - http://www.webbox.com <http://www.webbox.com> FREE Web based Email, Files, Bookmarks, Calendar, People and Great Ways to Share them with Others!

--
Author: Gunawan Yuwono

INET: gunawan.yuwono_at_webbox.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

--=20
Author: Vidya Kalyanaraman
INET: kvidya13_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

------_=_NextPart_001_01BFFEFB.5DDFBE30
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: AW: Extremely slow querry</TITLE>
</HEAD>
<BODY>

You should analyze FOR TABLE FOR ALL INDEXES FOR ALL = COLUMNS, not only FOR ALL INDEXED COLUMNS. Optimizer gets useful info = from analyzing non-indexed columns also.

Alex Hillman

-----Original Message----- From: Vidya Kalyanaraman [<A = HREF=3D"mailto:kvidya13_at_hotmail.com">mailto:kvidya13_at_hotmail.com</A>]</F=

ONT>
<BR><FONT SIZE=3D2>Sent: Friday, August 04, 2000 11:58 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Re: AW: Extremely slow querry</FONT>
</P>
<BR>

Hi Volker

Normally when u analyze a table, all the associated =

indexes are </FONT>
<BR><FONT SIZE=3D2>automatically analyzed as well.</FONT>
<BR><FONT SIZE=3D2>But from 7.3.4, you have a better way to analyze =

table/indexes with
 "FOR" clause of ANALYZE command. ANALYZE TABLE table_name COMPUTE STATISTICS FOR = TABLE FOR ALL INDEXED 
 COLUMNS;

You can specify the percentage sampling for ESTIMATE = statistics, but again 
 if you specifiy more than 40% or 50%(I am not really = sure about exact 
 value), it is similar to COMPUTE statistics.

<P><FONT SIZE=3D2>Hope it helps</FONT>
<BR><FONT SIZE=3D2>Thanks</FONT>
<BR><FONT SIZE=3D2>Vidya</FONT>
</P>
<BR>

Reply-To: ORACLE-L_at_fatcity.com To: Multiple recipients of list ORACLE-L = <ORACLE-L_at_fatcity.com>
 Date: Fri, 04 Aug 2000 02:18:43 -0800

analyze table PS_JOB JOB compute statistics; analyze table PS_FAST_SCRTY_2 SEC  compute = statistics;

do the same for indexes on this tables

analyze index i???? compute statistics;

Volker Sch=F6n
 E-Mail: <A =
HREF=3D"mailto:v.schoen_at_inplan.de">mailto:v.schoen_at_inplan.de</A> <<A = HREF=3D"mailto:v.schoen_at_inplan.de">mailto:v.schoen_at_inplan.de</A>></FO= NT>
 <A HREF=3D"http://www.inplan.de" = TARGET=3D"_blank">http://www.inplan.de</A> <<A = HREF=3D"http://www.inplan.de/" =
TARGET=3D"_blank">http://www.inplan.de/</A>>

-----Urspr=FCngliche Nachricht----- Von: Siva_Chintalapati [<A = HREF=3D"mailto:Siva_Chintalapati_at_satyam.com">mailto:Siva_Chintalapati_at_sa= tyam.com</A>]

<BR><FONT SIZE=3D2>Gesendet: Freitag, 4. August 2000 08:34</FONT>
<BR><FONT SIZE=3D2>An: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Betreff: RE: Extremely slow querry</FONT>
</P>
<BR>
<BR>

Hello ,
 Please say me how to compute and estimate = stastics.

<P><FONT SIZE=3D2>Regards</FONT>
<BR><FONT SIZE=3D2>Siva</FONT>
</P>

----------
 Reply To:       = ORACLE-L_at_fatcity.com
 Sent:   Thursday, August 03, 2000 7:16 = PM
 To:     Multiple recipients of = list ORACLE-L

Just a = thought,
 Try analyzing the indexes on those tables. Also, if = possible,
 try to use COMPUTE instead of ESTIMATE.

Do the = Explain Plan again and compare it with the previous one.

Suggestion: you might want to run a script to analyze = the

<BR><FONT SIZE=3D2>objects(tables,</FONT>
<BR><FONT SIZE=3D2>indexes, etc.) on a regular basis using cron.</FONT>
</P>

HTP.

Gunawan = Yuwono

<BR><FONT SIZE=3D2>Oracle DBA</FONT>
<BR><FONT SIZE=3D2>Kansas City, MO</FONT>
</P>

>--- = Original Message ---
  >From: "Jack van Zanen" = <nlzanen1_at_ey.nl>
  >To: Multiple recipients of list ORACLE-L = <ORACLE-L_at_fatcity.com>

<BR><FONT SIZE=3D2>&nbsp;&gt;Date: 8/3/00 11:25:28 AM</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
</P>

>

<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;Hi All,</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;Peoplesoap database</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;Oracle 8.0.5</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;AIX 4.3.2</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;*************</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;SELECT</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;&nbsp;&nbsp;&nbsp; JOB.EMPLID,</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;&nbsp;&nbsp;&nbsp; JOB.EMPL_RCD#,</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;&nbsp;&nbsp;&nbsp; SEC.OPRCLASS,</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;&nbsp;&nbsp;&nbsp; SEC.ACCESS_CD</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;FROM</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;&nbsp;&nbsp;&nbsp; PS_JOB JOB,</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;&nbsp;&nbsp;&nbsp; PS_FAST_SCRTY_2 =

SEC
  >WHERE  >     SEC.ACCESS_CD = =3D  'Y'
  >    = AND     SEC.SETID =3D JOB.SETID_DEPT  >    = AND     SEC.DEPTID =3D JOB.DEPTID

<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;****************</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;This is a select that is part of a PS view =

(stripped version)

<BR><FONT SIZE=3D2>and takes 30</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;minutes to run</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;The explain plan shows just a nested loop =

with the smaller table
  >(ps_fast_scrty_2  5500 records) as = the driving table
  >and the large table (180.000 records) is = not even accessed,

<BR><FONT SIZE=3D2>all needed</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;information comes out of the index.</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;This is a small database on a machine that =

should be capable

<BR><FONT SIZE=3D2>of a lot more.</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;Anybody any idea???</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT =

SIZE=3D2> >=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D</FONT>

>De informatie verzonden met dit E-mail = bericht is uitsluitend
 bestemd voor
  >de geadresseerde. Gebruik van deze = informatie door anderen dan
 de
  >geadresseerde is verboden. Openbaarmaking, = vermenigvuldiging,
 verspreiding
  >en/of verstrekking van deze informatie aan = derden is niet toegestaan.
  >Ernst & Young staat niet in voor de = juiste en volledige overbrenging
 van de
  >inhoud van een verzonden E-mail, noch voor = tijdige ontvangst
 daarvan.
  >=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D</FONT>

>The information contained in this = communication is confidential
 and may be
  >legally privileged. It is intended solely = for the use of the
 individual or
  >entity to whom it is addressed and others = authorised to receive
 it. If you
  >are not the intended recipient you are = hereby notified that
 any disclosure,  >copying,  distribution or taking any = action in reliance on the
 contents of
  >this information is strictly prohibited = and may be unlawful.
 Ernst &
  >Young is neither liable  for the = proper and complete transmission
 of the
  >information contained in this = communication nor for any delay

<BR><FONT SIZE=3D2>in its</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;receipt.</FONT>
<BR><FONT =

SIZE=3D2> >=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;--</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;Author: Jack van Zanen</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;&nbsp; INET: nlzanen1_at_ey.nl</FONT>

>
  >Fat City Network = Services    -- (858) 538-5051  FAX: (858) = 538-5051
  >San Diego, = California        -- Public Internet = access / Mailing
 Lists
 <FONT =

SIZE=3D2>&nbsp;&gt;-----------------------------------------------------=

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

<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>----------------</FONT>
<BR><FONT SIZE=3D2>Sent from a WebBox - <A =

HREF=3D"http://www.webbox.com" =
TARGET=3D"_blank">http://www.webbox.com</A> <<A = HREF=3D"http://www.webbox.com" =
TARGET=3D"_blank">http://www.webbox.com</A>> FREE Web based Email, Files, Bookmarks, Calendar, = People and

<BR><FONT SIZE=3D2>Great Ways to Share them with Others!</FONT>
</P>
<BR>

<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>--</FONT>
<BR><FONT SIZE=3D2>Author: Gunawan Yuwono</FONT>

INET: gunawan.yuwono_at_webbox.com

Fat City = Network Services    -- (858) 538-5051  FAX: (858) = 538-5051
 San Diego, =
California        -- Public Internet = access / Mailing Lists
 <FONT =

SIZE=3D2>---------------------------------------------------------------=

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

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

<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Vidya Kalyanaraman</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: kvidya13_at_hotmail.com</FONT>
</P>

Fat City Network Services    -- (858) = 538-5051  FAX: (858) 538-5051 San Diego, =
California        -- Public Internet = access / Mailing Lists
 <FONT =

SIZE=3D2>---------------------------------------------------------------=

Received on Sat Aug 05 2000 - 11:36:55 CDT