Message-Id: <10579.113818@fatcity.com> From: "Jack van Zanen" Date: Fri, 4 Aug 2000 09:18:49 +0100 Subject: 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 compute statistics estimate statistics [sample n rows/percent] delete statistics Siva_Chintalapati @fatcity.com on 08/04/2= 000 07:34:30 AM Please respond to ORACLE-L@fatcity.com Sent by: root@fatcity.com To: Multiple recipients of list ORACLE-L cc: Subject: RE: Extremely slow querry Hello , Please say me how to compute and estimate stastics. Regards Siva ---------- From: =A0 Gunawan Yuwono[SMTP:gunawan.yuwono@webbox.com] Reply To: =A0=A0=A0=A0=A0 ORACLE-L@fatcity.com Sent: =A0 Thursday, August 03, 2000 7:16 PM To: =A0=A0=A0 Multiple recipients of list ORACLE-L Subject: =A0=A0=A0=A0=A0=A0 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(ta= bles, 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 >=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@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@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 =A0 INET: gunawan.yuwono@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@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=3D De informatie verzonden met dit E-mail bericht is uitsluitend bestemd v= oor de geadresseerde. Gebruik van deze informatie door anderen dan de geadresseerde is verboden. Openbaarmaking, vermenigvuldiging, verspreid= ing en/of verstrekking van deze informatie aan derden is niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging va= n 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 disclos= ure, 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 th= e information contained in this communication nor for any delay in its