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
From: Schoen Volker <v.schoen_at_inplan.de>
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Subject: AW: Extremely slow querry
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ön
E-Mail: mailto:v.schoen_at_inplan.de <mailto:v.schoen_at_inplan.de>
http://www.inplan.de <http://www.inplan.de/>
-----Ursprüngliche 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 = '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 <
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).
Received on Fri Aug 04 2000 - 09:46:37 CDT
