RE: Slow sql

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Thu, 16 Aug 2001 07:07:28 -0700
Message-ID: <F001.0036D04F.20010816071612@fatcity.com>

Statistics can in fact hurt. If your queries are tuned for rule based optimizer, it is very possible that cost base will perform suboptimal, perhaps a little, perhaps a lot. You may need to tune the database to be more efficient for CBO by changing parameters.

Although adding statistics and removing them if it doesn't work won't hurt a thing, is a good thing to try. Just noting that statistics can degrade performance of a queries/query.

Just something to keep in mind.

"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863

-----Original Message-----

Sent: Thursday, August 16, 2001 10:54 AM To: Multiple recipients of list ORACLE-L

Yesterday I had a developer complain about a SQL select statment with subquery that was taking 10 minutes to run. I fixed it by generating new statistics:

ANALYZE TABLE table_name COMPUTE STATISTICS;

It took about an hour and a half to run on this particular table but now the query takes 30 seconds. Give it a try on all the tables in your SQL if you're using cost-based optimizer. It can't hurt.

HTH
Ed

-----Original Message-----

Sent: Thursday, August 16, 2001 5:46 AM
To: Multiple recipients of list ORACLE-L

Hallo you DBA'¨s

Can anyonetell me why this insert statement takes 30-40 minutes to run? What can I do to make it go faster?

insert into varukorgtemp ( varukorgid, ean, anvandarid, lagstapris, varutyp, varunamn, strl, leverantor, varumarke, sortiment,vgrp,vare_snr,varenr,levnr )

        SELECT PBK.VARUKORGEANREL.varukorgid, rik2.vare.ean_nr, 'rsm' ,
           PBK.VARUKORGEANREL.lagstapris, 0, rik2.vare.varenavn,
rik2.vare.str,
           rik2.lev.navn, rik2.vare.hylletxt2,

rik2.vare.sortiment,rik2.art_hierarki.vgrp,

pbk.varukorgeanrel.vare_snr,pbk.varukorgeanrel.varenr,pbk.varukorgeanrel.lev nr

         FROM PBK.VARUKORGEANREL,rik2.vare,rik2.lev,rik2.art_hierarki
         WHERE PBK.VARUKORGEANREL.varukorgid= 39
         AND PBK.VARUKORGEANREL.ean=rik2.vare.ean_nr
         AND RIK2.VARE.ARTNR=RIK2.ART_HIERARKI.ARTNR
         AND rik2.vare.levnr=rik2.lev.levnr
         AND rik2.vare.selskap='11'
         AND rik2.vare.vare_snr=pbk.varukorgeanrel.vare_snr
         AND pbk.varukorgeanrel.varenr=rik2.vare.varenr
         AND pbk.varukorgeanrel.levnr=rik2.vare.levnr
         AND rik2.art_hierarki.sett_id=2

Sincerely

Roland Sköldblom

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Roland.Skoldblom_at_ica.se

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

* * * * Freedom of Information Act Notice * * * * * The information in this email is subject to the record protection mandated by 5 United States Code 552(b)(4) and relevant judicial opinions.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sherman, Edward INET: shermanej_at_eccic.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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Christopher Spence
INET: cspence_at_FuelSpot.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