Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Extremely slow querry

RE: Extremely slow querry

From: Siva_Chintalapati <Siva_Chintalapati_at_satyam.com>
Date: Fri, 4 Aug 2000 10:51:11 +0530
Message-Id: <10578.113815@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_01BFFDD3.C97C2C70
Content-Type: text/plain;

        charset="iso-8859-1"

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

Regards
Siva

> ----------
> From: Gunawan Yuwono[SMTP:gunawan.yuwono_at_webbox.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Thursday, August 03, 2000 7:16 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Extremely slow querry
>
>
> 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 = 'Y'
> > AND SEC.SETID = JOB.SETID_DEPT
> > AND SEC.DEPTID = 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???
> >
> >
> >===================================================================
> >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.
> >===================================================================
> >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.
> >===================================================================
> >
> >
> >
> >--
> >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
> 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).
>

------_=_NextPart_001_01BFFDD3.C97C2C70
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=3Dus-ascii">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12">
<TITLE>RE: Extremely slow querry</TITLE>
</HEAD>
<BODY>

<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Hello ,</FONT> <BR><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Please say me how = to compute and estimate stastics.</FONT> </P>

<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Regards</FONT>
<BR><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Siva</FONT>
</P>
<UL>
<P><FONT SIZE=3D1 FACE=3D"MS Sans Serif">----------</FONT>
<BR><B><FONT SIZE=3D1 FACE=3D"MS Sans Serif">From:</FONT></B> &nbsp; =
<FONT SIZE=3D1 FACE=3D"MS Sans Serif">Gunawan =
Yuwono[SMTP:gunawan.yuwono_at_webbox.com]</FONT> <BR><B><FONT SIZE=3D1 FACE=3D"MS Sans Serif">Reply To:</FONT></B> = &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D1 FACE=3D"MS Sans = Serif">ORACLE-L_at_fatcity.com</FONT>
<BR><B><FONT SIZE=3D1 FACE=3D"MS Sans Serif">Sent:</FONT></B> &nbsp; = <FONT SIZE=3D1 FACE=3D"MS Sans Serif">Thursday, August 03, 2000 7:16 = PM</FONT>
<BR><B><FONT SIZE=3D1 FACE=3D"MS Sans Serif">To:</FONT></B> = &nbsp;&nbsp;&nbsp; <FONT SIZE=3D1 FACE=3D"MS Sans Serif">Multiple = recipients of list ORACLE-L</FONT>
<BR><B><FONT SIZE=3D1 FACE=3D"MS Sans Serif">Subject:</FONT></B> = &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D1 FACE=3D"MS Sans = Serif">RE: Extremely slow querry</FONT>
</P>
<BR>

<P><FONT SIZE=3D2 FACE=3D"Arial">Just a thought,</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Try analyzing the indexes on those = tables. Also, if possible,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">try to use COMPUTE instead of = ESTIMATE.</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">Do the Explain Plan again and compare = it with the previous one.</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">Suggestion: you might want to run a = script to analyze the objects(tables,</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">indexes, etc.) on a regular basis = using cron.</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">HTP.</FONT> </P>

<P><FONT SIZE=3D2 FACE=3D"Arial">Gunawan Yuwono</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Oracle DBA</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Kansas City, MO</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">&gt;--- Original Message ---</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;From: &quot;Jack van Zanen&quot; = &lt;nlzanen1_at_ey.nl&gt;</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;To: Multiple recipients of list = ORACLE-L &lt;ORACLE-L_at_fatcity.com&gt;</FONT>

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

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

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

FACE=3D"Arial">&gt;=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=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 FACE=3D"Arial">&gt;</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;-- </FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;Author: Jack van Zanen</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;&nbsp; INET: = nlzanen1_at_ey.nl</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;Fat City Network = Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp; FAX: (858) = 538-5051</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;San Diego, = California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet = access / Mailing</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Lists</FONT> <BR><FONT SIZE=3D2 =
FACE=3D"Arial">&gt;-----------------------------------------------------=
---------------</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;To REMOVE yourself from this = mailing list, send an E-Mail message</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;to: ListGuru_at_fatcity.com (note = EXACT spelling of 'ListGuru')</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">and in</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;the message BODY, include a line = containing: UNSUB ORACLE-L</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;(or the name of mailing list you = want to be removed from). </FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">You may</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;also send the HELP command for = other information (like subscribing).</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&gt;</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">----------------</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Sent from a WebBox -</FONT><U> <FONT =
COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial"><A = HREF=3D"http://www.webbox.com" =
TARGET=3D"_blank">http://www.webbox.com</A></FONT></U> <BR><FONT SIZE=3D2 FACE=3D"Arial">FREE Web based Email, Files, = Bookmarks, Calendar, People and</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Great Ways to Share them with = Others!</FONT>
</P>
<BR>
<P><FONT SIZE=3D2 FACE=3D"Arial">-- </FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Author: Gunawan Yuwono</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp; INET: =
gunawan.yuwono_at_webbox.com</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">Fat City Network = Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp; FAX: (858) = 538-5051</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">San Diego, = California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet = access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2 =

FACE=3D"Arial">---------------------------------------------------------=
-----------</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">To REMOVE yourself from this mailing = list, send an E-Mail message</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">to: ListGuru_at_fatcity.com (note EXACT = spelling of 'ListGuru') and in</FONT> Received on Fri Aug 04 2000 - 00:21:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US