| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Extremely slow querry
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">
<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> = <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> = <FONT SIZE=3D1 FACE=3D"MS Sans = Serif">ORACLE-L_at_fatcity.com</FONT>
<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">>--- Original Message ---</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">>From: "Jack van Zanen" =
<nlzanen1_at_ey.nl></FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">>To: Multiple recipients of list =
ORACLE-L <ORACLE-L_at_fatcity.com></FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">>Date: 8/3/00 11:25:28 AM</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> </P> <P><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">>Hi All,</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">>Peoplesoap database</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">>Oracle 8.0.5</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">>AIX 4.3.2</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">>*************</FONT><BR><FONT SIZE=3D2 FACE=3D"Arial">>SELECT</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">> = JOB.EMPLID,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">>FROM</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">> PS_JOB = JOB,</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">> =PS_FAST_SCRTY_2 SEC</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">>WHERE</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">> = SEC.ACCESS_CD =3D 'Y'</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">> =AND SEC.SETID =3D JOB.SETID_DEPT</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">> = AND SEC.DEPTID =3D JOB.DEPTID</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">>****************</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">>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">>minutes to run</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">>The explain plan shows just a =nested loop with the smaller table</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">>(ps_fast_scrty_2 5500 = records) as the driving table</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">of a lot more.</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">>Anybody any idea???</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></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</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">van de</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">>inhoud van een verzonden E-mail, = noch voor tijdige ontvangst</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">daarvan.</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</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">in its</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">>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</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">>-- </FONT><BR><FONT SIZE=3D2 FACE=3D"Arial">>Author: Jack van Zanen</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">> INET: = nlzanen1_at_ey.nl</FONT>
FACE=3D"Arial">>-----------------------------------------------------=---------------</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">></FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">></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" =
<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"> INET: =gunawan.yuwono_at_webbox.com</FONT>
<P><FONT SIZE=3D2 FACE=3D"Arial">Fat City Network =
Services -- (858) 538-5051 FAX: (858) =
538-5051</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">San Diego, =
California -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2 =
FACE=3D"Arial">---------------------------------------------------------=-----------</FONT>
 |
 |