| 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
Hi
compute for entire schema
exec dbms_utility.analyze_schema('cabs','compute');
procedure analyze_schema(schema varchar2, method varchar2,
estimate_rows number default null,
estimate_percent number default null, method_opt varchar2 default n=
ull);
or per table:
analyze table <table> compute statistics
estimate statistics [sample n rows/percent]
delete statistics
Siva_Chintalapati <Siva_Chintalapati_at_satyam.com>@fatcity.com on 08/04/2=
000
07:34:30 AM
Please respond to ORACLE-L_at_fatcity.com
Sent by: root_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: RE: Extremely slow querry
Hello ,
Please say me how to compute and estimate stastics.
Regards
Siva
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(ta= bles,
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
>=A0=A0=A0 JOB.EMPLID,
>=A0=A0=A0 JOB.EMPL_RCD#,
>=A0=A0=A0 SEC.OPRCLASS,
>=A0=A0=A0 SEC.ACCESS_CD
>FROM
>=A0=A0=A0 PS_JOB JOB,
>=A0=A0=A0 PS_FAST_SCRTY_2 SEC
>WHERE
>=A0=A0=A0=A0 SEC.ACCESS_CD =3D=A0 'Y'
>=A0=A0=A0 AND=A0=A0=A0=A0 SEC.SETID =3D JOB.SETID_DEPT
>=A0=A0=A0 AND=A0=A0=A0=A0 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=A0 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 toegestaa=
n.
>Ernst & Young staat niet in voor de juiste en volledige overbrengin=
g
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,=A0 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=A0 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
>=A0 INET: nlzanen1_at_ey.nl
>
>Fat City Network Services=A0=A0=A0 -- (858) 538-5051=A0 FAX: (858) =
538-5051
>San Diego, California=A0=A0=A0=A0=A0=A0=A0 -- Public Internet acces=
s / 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)=
.
>
>
--
Author: Gunawan Yuwono
=A0 INET: gunawan.yuwono_at_webbox.com
Fat City Network Services=A0=A0=A0 -- (858) 538-5051=A0 FAX: (858) 5= 38-5051
San Diego, California=A0=A0=A0=A0=A0=A0=A0 -- 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).=A0 You ma= y
also send the HELP command for other information (like subscribing).=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3DDe informatie verzonden met dit E-mail bericht is uitsluitend bestemd v= oor
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3DThe information contained in this communication is confidential and may= be
 |
 |