AW: Extremely slow querry

From: Schoen Volker <v.schoen_at_inplan.de>
Date: Fri, 4 Aug 2000 11:05:42 +0200
Message-Id: <10579.113828@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_01BFFDF3.2AB8CAF0
Content-Type: text/plain;

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

analyze table PS_JOB JOB compute statistics; analyze table PS_FAST_SCRTY_2 SEC compute statistics; =20
do the same for indexes on this tables
=20
analyze index i???? compute statistics;

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

-----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 ,=20
Please say me how to compute and estimate stastics.=20

Regards=20
Siva=20

----------=20
From: Gunawan Yuwono[SMTP:gunawan.yuwono_at_webbox.com]=20 Reply To: ORACLE-L_at_fatcity.com=20
Sent: Thursday, August 03, 2000 7:16 PM=20

To:     Multiple recipients of list ORACLE-L=20
Subject:        RE: Extremely slow querry=20


	Just a thought,=20

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

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

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

HTP.=20 Gunawan Yuwono=20
Oracle DBA=20
Kansas City, MO=20

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

>=20
>=20
>=20
>Hi All,=20
>=20
>=20
>Peoplesoap database=20
>Oracle 8.0.5=20
>AIX 4.3.2=20
>=20
>=20
>*************=20
>SELECT=20
> JOB.EMPLID,=20
> JOB.EMPL_RCD#,=20
> SEC.OPRCLASS,=20
> SEC.ACCESS_CD=20
>FROM=20
> PS_JOB JOB,=20
> PS_FAST_SCRTY_2 SEC=20
>WHERE=20
> SEC.ACCESS_CD =3D 'Y'=20
> AND SEC.SETID =3D JOB.SETID_DEPT=20
> AND SEC.DEPTID =3D JOB.DEPTID=20
>=20
>****************=20
>=20
>This is a select that is part of a PS view (stripped version)=20
and takes 30=20
>minutes to run=20
>The explain plan shows just a nested loop with the smaller table=20
>(ps_fast_scrty_2 5500 records) as the driving table=20
>and the large table (180.000 records) is not even accessed,=20
all needed=20
>information comes out of the index.=20
>=20
>This is a small database on a machine that should be capable=20
of a lot more.=20
>=20
>Anybody any idea???=20
>=20
>=20
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
>De informatie verzonden met dit E-mail bericht is uitsluitend=20
bestemd voor=20
>de geadresseerde. Gebruik van deze informatie door anderen dan=20
de=20
>geadresseerde is verboden. Openbaarmaking, vermenigvuldiging,=20
verspreiding=20
>en/of verstrekking van deze informatie aan derden is niet toegestaan.=20
>Ernst & Young staat niet in voor de juiste en volledige overbrenging=20
van de=20
>inhoud van een verzonden E-mail, noch voor tijdige ontvangst=20
daarvan.=20
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
>The information contained in this communication is confidential=20
and may be=20
>legally privileged. It is intended solely for the use of the=20
individual or=20
>entity to whom it is addressed and others authorised to receive=20
it. If you=20
>are not the intended recipient you are hereby notified that=20
any disclosure,=20
>copying, distribution or taking any action in reliance on the=20
contents of=20
>this information is strictly prohibited and may be unlawful.=20
Ernst &=20
>Young is neither liable for the proper and complete transmission=20
of the=20
>information contained in this communication nor for any delay=20
in its=20
>receipt.=20
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
>=20
>=20
>=20
>--=20
>Author: Jack van Zanen=20
> INET: nlzanen1_at_ey.nl=20
>=20
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051=20
>San Diego, California -- Public Internet access / Mailing=20
Lists=20
>--------------------------------------------------------------------=20
>To REMOVE yourself from this mailing list, send an E-Mail message=20
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')=20
and in=20
>the message BODY, include a line containing: UNSUB ORACLE-L=20
>(or the name of mailing list you want to be removed from).=20
You may=20
>also send the HELP command for other information (like subscribing).=20
>=20
>=20

----------------=20

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

--=20
Author: Gunawan Yuwono=20
INET: gunawan.yuwono_at_webbox.com=20

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

To REMOVE yourself from this mailing list, send an E-Mail message=20 to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in=20 the message BODY, include a line containing: UNSUB ORACLE-L=20 (or the name of mailing list you want to be removed from). You may=20 also send the HELP command for other information (like subscribing).=20

------_=_NextPart_001_01BFFDF3.2AB8CAF0
Content-Type: text/html;

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">
<TITLE>RE: Extremely slow querry</TITLE>

<META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D844350409-04082000></SPAN><FONT color=3D#0000ff =

face=3DArial=20
size=3D2>analyze table PS_JOB = JOB<SPAN=20
class=3D844350409-04082000> compute statistics; analyze = table<SPAN=20
class=3D844350409-04082000> PS_FAST_SCRTY_2 SEC  compute=20 statistics;</DIV>
<DIV> </DIV> <DIV><FONT color=3D#0000ff =

face=3DArial=20
size=3D2>d<SPAN class=3D844350409-04082000>o the same for indexes on =
this=20

tables</DIV>
<DIV><SPAN=20 class=3D844350409-04082000> </DIV> <DIV><SPAN=20 class=3D844350409-04082000>a<SPAN=20
class=3D844350409-04082000>nalyze index i???? compute=20 statistics;</DIV>
Volker Sch=F6n = <FONT=20
color=3D#000000 face=3DArial size=3D2>E-Mail: <A=20
href=3D"mailto:v.schoen_at_inplan.de">mailto:v.schoen_at_inplan.de</A><= /U>=20
 <A = href=3D"http://www.inplan.de/"=20
target=3D_blank>http://www.inplan.de</A> <BLOCKQUOTE>
 <DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr>-----Urspr=FCngliche Nachricht----- Von: = Siva_Chintalapati=20
 [mailto:Siva_Chintalapati_at_satyam.com] Gesendet: Freitag, 4. = August=20
 2000 08:34 An: Multiple recipients of list=20 ORACLE-L Betreff: RE: Extremely slow = querry </DIV>
 Hello , = <FONT=20
 color=3D#0000ff face=3DArial size=3D2>Please say me how to compute = and estimate=20
 stastics. 
 Regards = <FONT=20
 color=3D#0000ff face=3DArial size=3D2>Siva <UL>
 ---------- = <FONT=20

face=3D"MS Sans Serif" size=3D1>From:   <FONT=20 face=3D"MS Sans Serif" size=3D1>Gunawan=20 Yuwono[SMTP:gunawan.yuwono_at_webbox.com] <FONT=20 face=3D"MS Sans Serif" size=3D1>Reply To:=20       ORACLE-L_at_fatcity.com <FONT face=3D"MS Sans = Serif"=20

size=3D1>Sent:   Thursday,=20

August 03, 2000 7:16 PM To:     <FONT face=3D"MS Sans = Serif"=20

size=3D1>Multiple recipients of list ORACLE-L <FONT=20 face=3D"MS Sans Serif" size=3D1>Subject:=20        RE:=20

Extremely slow querry Just a thought, <FONT = face=3DArial=20

size=3D2>Try analyzing the indexes on those tables. Also, if = possible,=20

try to use COMPUTE instead of = ESTIMATE.=20

Do the Explain Plan again and = compare it with the=20

previous one. 
Suggestion: you might want to run a = script to=20

analyze the objects(tables, indexes,=20

etc.) on a regular basis using cron. HTP. Gunawan Yuwono <FONT = face=3DArial=20

size=3D2>Oracle DBA Kansas = City, MO=20

    </P>
    <P><FONT face=3DArial size=3D2>&gt;--- Original Message ---</FONT> =
<BR><FONT=20

face=3DArial size=3D2>>From: "Jack van Zanen" = <nlzanen1_at_ey.nl>=20

>To: Multiple recipients of list = ORACLE-L=20
<ORACLE-L_at_fatcity.com> >Date:=20

8/3/00 11:25:28 AM >

> > > = <FONT=20

face=3DArial size=3D2>>Hi All, >=20

> >Peoplesoap database >Oracle=20

8.0.5 >AIX 4.3.2 = <FONT=20

face=3DArial size=3D2>> >=20

>************* <FONT = face=3DArial=20

size=3D2>>SELECT >   =20

JOB.EMPLID, >   =20

JOB.EMPL_RCD#, >   =20

SEC.OPRCLASS, >   =20

SEC.ACCESS_CD >FROM <FONT=20

face=3DArial size=3D2>>    PS_JOB JOB, = <FONT=20

face=3DArial size=3D2>>    PS_FAST_SCRTY_2 = SEC=20

>WHERE <FONT = face=3DArial=20

size=3D2>>     SEC.ACCESS_CD =3D  = 'Y'=20

>   =20 AND     SEC.SETID =3D JOB.SETID_DEPT = <FONT=20

face=3DArial size=3D2>>    = AND    =20

SEC.DEPTID =3D JOB.DEPTID >=20

    <BR><FONT face=3DArial size=3D2>&gt;****************</FONT> =

<FONT face=3DArial=20

size=3D2>> >This is a = select that is=20

part of a PS view (stripped version) and=20

takes 30 >minutes to = run=20

>The explain plan shows just a = nested loop=20

with the smaller table >(ps_fast_scrty_2  5500 records) as the driving = table=20

>and the large table (180.000 = records) is not=20

even accessed, all = needed=20

>information comes out of the = index.=20

> >This=20

is a small database on a machine that should be capable = <FONT=20

face=3DArial size=3D2>of a lot more. > >Anybody = any=20

idea??? > = <FONT face=3DArial=20

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</F= ONT>=20
 >De informatie verzonden met dit = E-mail=20

bericht is uitsluitend bestemd=20

voor >de geadresseerde. = Gebruik van=20

deze informatie door anderen dan de >geadresseerde is verboden.=20

Openbaarmaking, vermenigvuldiging, verspreiding >en/of=20

verstrekking van deze informatie aan derden is niet = toegestaan.=20

>Ernst & Young staat niet in = voor de=20

juiste en volledige overbrenging van=20

de >inhoud van een = verzonden E-mail,=20

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</F= ONT>=20
 >The information contained in = this=20

communication is confidential and may=20

be >legally privileged. = It is intended=20

solely for the use of the individual=20

or >entity to whom it is = addressed and=20

others authorised to receive it. If=20

you >are not the intended = recipient=20

you are hereby notified that any=20

disclosure, >copying, =20

distribution or taking any action in reliance on the = <FONT=20

face=3DArial size=3D2>contents of >this=20

information is strictly prohibited and may be unlawful. = <FONT=20

face=3DArial size=3D2>Ernst & >Young=20

is neither liable  for the proper and complete = transmission=20

of the <FONT = face=3DArial=20

size=3D2>>information contained in this communication nor for = any=20

delay in its = <FONT face=3DArial=20

size=3D2>>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</F= ONT>=20
 > > > = <FONT=20

face=3DArial size=3D2>>-- >Author: Jack=20

van Zanen >  INET:=20 nlzanen1_at_ey.nl > = <FONT=20

face=3DArial size=3D2>>Fat City Network = Services    -- (858)=20

538-5051  FAX: (858) 538-5051 >San Diego, =
California       =20

Public Internet access / Mailing Lists >-----------------------------------------------------------= ---------=20 >To REMOVE yourself from this = mailing list,=20 send an E-Mail message >to:=20 ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') = <FONT=20 face=3DArial size=3D2>and in >the message=20 BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed = from).=20 You may >also send the HELP command for other information (like =

subscribing). > = <FONT=20

face=3DArial size=3D2>> <FONT face=3DArial=20

    size=3D2>----------------</FONT> <BR><FONT face=3DArial =

size=3D2>Sent from a=20

WebBox - <A=20 href=3D"http://www.webbox.com"=20
target=3D_blank>http://www.webbox.com</A> <FONT = face=3DArial=20

size=3D2>FREE Web based Email, Files, Bookmarks, Calendar, People = and=20

Great Ways to Share them with = Others!=20

-- Author:=20

Gunawan Yuwono   INET:=20 gunawan.yuwono_at_webbox.com Fat City Network = Services    --=20

(858) 538-5051  FAX: (858) 538-5051 <FONT = face=3DArial=20

size=3D2>San Diego, =
California        --=20

Public Internet access / Mailing Lists <FONT =

face=3DArial=20
    =
size=3D2>---------------------------------------------------------------=

-----=20

To REMOVE yourself from this = mailing list, send=20

an E-Mail message to:=20 ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and = Received on Fri Aug 04 2000 - 04:05:42 CDT