AW: Slow sql

From: Schoen Volker <v.schoen_at_inplan.de>
Date: Thu, 16 Aug 2001 02:36:46 -0700
Message-ID: <F001.0036C9B5.20010816022530@fatcity.com>

Hi Roland,

look at the execution plan of your subselect. Start sqlplus then

set autotrace on;

        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;

Look at the results. If there are FULL table scans on large tables you have to create indexes. If you use Optimizer CHOOSE, look if the tables and indexes are analyzed.

Hope this will help.

Regards

Volker Schön
E-Mail: mailto:v.schoen_at_inplan.de
http://www.inplan.de

-----Ursprüngliche Nachricht-----
Von: Roland.Skoldblom_at_ica.se [mailto:Roland.Skoldblom_at_ica.se] Gesendet: Donnerstag, 16. August 2001 11:46 An: Multiple recipients of list ORACLE-L Betreff: Slow sql

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
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: Schoen Volker
  INET: v.schoen_at_inplan.de

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 Thu Aug 16 2001 - 04:36:46 CDT