Message-Id: <10580.113912@fatcity.com> From: Alex Hillman Date: Sat, 5 Aug 2000 12:36:55 -0400 Subject: RE: AW: 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_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@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@fatcity.com To: Multiple recipients of list ORACLE-L 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@inplan.de http://www.inplan.de -----Urspr=FCngliche Nachricht----- Von: Siva_Chintalapati [mailto:Siva_Chintalapati@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@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" >To: Multiple recipients of list ORACLE-L >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@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@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@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@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@hotmail.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@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_01BFFEFB.5DDFBE30 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: AW: Extremely slow querry

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

Hope it helps
Thanks
Vidya


Reply-To: ORACLE-L@fatcity.com
To: Multiple recipients of list ORACLE-L = <ORACLE-L@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@inplan.de <mailto:v.schoen@inplan.de>
http://www.inplan.de <http://www.inplan.de/>

-----Urspr=FCngliche Nachricht-----
Von: Siva_Chintalapati [mailto:Siva_Chintalapati@sa= tyam.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@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@ey.nl>
 >To: Multiple recipients of list ORACLE-L = <ORACLE-L@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@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@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@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@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

--
Author: Vidya Kalyanaraman
  INET: kvidya13@hotmail.com

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