RE: Slow SQL*Plus connect.
I would also verify that Oracle Trace is turned off and that within $ORACLE_HOME/otrace/admin there are no files. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Wednesday, January 22, 2003 11:49 AM To: Multiple recipients of list ORACLE-L Subject:RE: Slow SQL*Plus connect. How big is the listener log file?? do you truncate/rename it on regular basis?? just a thought. Sunil Nookala Dell Corp. -Original Message- Sent: Wednesday, January 22, 2003 4:09 AM To: Multiple recipients of list ORACLE-L Hi All, We have experienced a *very* slow connect time to a 9.0.1 database via SQL*Plus (and other apps as well) on a Win2K machine, and I was wondering if anybody else had experienced these slow connection times as well? We have also been asked lately by a number of customers about slow connection times, and to this point haven't found a solution for either ourselves or our contacts.. It's not a network issue as connection times take just as long locally. Connections can take up to around a minute (and the odd occasion a couple of minutes). No MTS is in use. OS's that I've heard about this on are Win2K, XP and NT so I'm also wondering if it may be a Win32 issue. Help! Cheers :) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Slow SQL*Plus connect.
Hi Tim, and Steve, Thanks for the comments. I did check whether tracing was enabled, and it wasn't.. Thanks for the thought though. Cheers Mark -Original Message- Stephen Sent: 23 January 2003 13:49 To: Multiple recipients of list ORACLE-L I would also verify that Oracle Trace is turned off and that within $ORACLE_HOME/otrace/admin there are no files. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Wednesday, January 22, 2003 11:49 AM To: Multiple recipients of list ORACLE-L Subject:RE: Slow SQL*Plus connect. How big is the listener log file?? do you truncate/rename it on regular basis?? just a thought. Sunil Nookala Dell Corp. -Original Message- Sent: Wednesday, January 22, 2003 4:09 AM To: Multiple recipients of list ORACLE-L Hi All, We have experienced a *very* slow connect time to a 9.0.1 database via SQL*Plus (and other apps as well) on a Win2K machine, and I was wondering if anybody else had experienced these slow connection times as well? We have also been asked lately by a number of customers about slow connection times, and to this point haven't found a solution for either ourselves or our contacts.. It's not a network issue as connection times take just as long locally. Connections can take up to around a minute (and the odd occasion a couple of minutes). No MTS is in use. OS's that I've heard about this on are Win2K, XP and NT so I'm also wondering if it may be a Win32 issue. Help! Cheers :) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Slow SQL*Plus connect.
I had a similar problem once that was solved by editing my SQLNET.ORA file. I set: SQLNET.AUTHENTICATION_SERVICES= (None) ...instead of: SQLNET.AUTHENTICATION_SERVICES= (NTS) I'm not sure what functionality this change eliminated, but my connection times are sure a lot faster now. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 26-28 London -Original Message- Sent: Thursday, January 23, 2003 9:14 AM To: Multiple recipients of list ORACLE-L Hi Tim, and Steve, Thanks for the comments. I did check whether tracing was enabled, and it wasn't.. Thanks for the thought though. Cheers Mark -Original Message- Stephen Sent: 23 January 2003 13:49 To: Multiple recipients of list ORACLE-L I would also verify that Oracle Trace is turned off and that within $ORACLE_HOME/otrace/admin there are no files. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Wednesday, January 22, 2003 11:49 AM To: Multiple recipients of list ORACLE-L Subject:RE: Slow SQL*Plus connect. How big is the listener log file?? do you truncate/rename it on regular basis?? just a thought. Sunil Nookala Dell Corp. -Original Message- Sent: Wednesday, January 22, 2003 4:09 AM To: Multiple recipients of list ORACLE-L Hi All, We have experienced a *very* slow connect time to a 9.0.1 database via SQL*Plus (and other apps as well) on a Win2K machine, and I was wondering if anybody else had experienced these slow connection times as well? We have also been asked lately by a number of customers about slow connection times, and to this point haven't found a solution for either ourselves or our contacts.. It's not a network issue as connection times take just as long locally. Connections can take up to around a minute (and the odd occasion a couple of minutes). No MTS is in use. OS's that I've heard about this on are Win2K, XP and NT so I'm also wondering if it may be a Win32 issue. Help! Cheers :) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services
RE: Slow SQL*Plus connect.
If the box is swapping memory, connections can be slow since memory has to allocated for the connection. Just one possibility. -Original Message- We have experienced a *very* slow connect time to a 9.0.1 database via SQL*Plus (and other apps as well) on a Win2K machine, -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Slow SQL*Plus connect.
How big is the listener log file?? do you truncate/rename it on regular basis?? just a thought. Sunil Nookala Dell Corp. -Original Message- Sent: Wednesday, January 22, 2003 4:09 AM To: Multiple recipients of list ORACLE-L Hi All, We have experienced a *very* slow connect time to a 9.0.1 database via SQL*Plus (and other apps as well) on a Win2K machine, and I was wondering if anybody else had experienced these slow connection times as well? We have also been asked lately by a number of customers about slow connection times, and to this point haven't found a solution for either ourselves or our contacts.. It's not a network issue as connection times take just as long locally. Connections can take up to around a minute (and the odd occasion a couple of minutes). No MTS is in use. OS's that I've heard about this on are Win2K, XP and NT so I'm also wondering if it may be a Win32 issue. Help! Cheers :) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Slow SQL*Plus connect.
Is otrace enabled? Note: 1020763.6 Note: 45482.1 Tim -Original Message- Sent: Wednesday, January 22, 2003 5:09 AM To: Multiple recipients of list ORACLE-L Hi All, We have experienced a *very* slow connect time to a 9.0.1 database via SQL*Plus (and other apps as well) on a Win2K machine, and I was wondering if anybody else had experienced these slow connection times as well? We have also been asked lately by a number of customers about slow connection times, and to this point haven't found a solution for either ourselves or our contacts.. It's not a network issue as connection times take just as long locally. Connections can take up to around a minute (and the odd occasion a couple of minutes). No MTS is in use. OS's that I've heard about this on are Win2K, XP and NT so I'm also wondering if it may be a Win32 issue. Help! Cheers :) Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: slow SQL query, diagnosis using 10046 trace event
Suhen, Look at what you are doing : Query Plan select orgplvee.org_lvl_parent ,prdplvee.prd_lvl_parent , (NVL(invbalee.on_hand_qty,0)+NVL(to_intrn_qty,0)), (NVL(invbalee.on_hand_retl,0)+NVL(to_intrn_retl,0)), (NVL(invbalee.on_hand_cost,0)+NVL(to_intrn_cost,0)) from invbalee ,orgplvee ,prdplvee where (orgplvee.org_lvl_child= invbalee.org_lvl_child and prdplvee.prd_lvl_child=invbalee.prd_lvl_child) order by orgplvee.org_lvl_parent,prdplvee.prd_lvl_parent Execution Plan Id Par Pos Ins Plan --- 0 SELECT STATEMENT (choose) Cost (48836,5333714,170678848) 101 SORT(order by) Cost (48836,5333714,170678848) 211HASH JOIN Cost (1705,5333714,170678848) 321 INDEX (analyzed) UNIQUE JDAPROD ORGPLVEEP1 (fast full scan) Cost (1,1073,5365) 422 HASH JOIN Cost (1690,1357040,36640080) 541INDEX (analyzed) UNIQUE JDAPROD PRDPLVEEP1 (fast full scan) Cost (16,100070,8005 6421 TABLE ACCESS (analyzed) JDAPROD INVBALEE (full) Cost (746,1257164,23886116) Each fetch call returned an average of 2 rows. Your query generates a full scan of INVBALEE which looks pretty big. However, you 'feed' it no search criteria other than join conditions on ORGPLVEE and PRDPLVEE. Looks to me like a star query. Since you return few rows, the most sensible approach would probably be to use nested loops rather than the hash joins the optimizer jumps for. Summary : a) You cannot do otherwise than scan a table (or possibly an index). Pick the smallest one. b) Force the use of nested loops to get data from the other tables. I am no great fan of hints, but it looks to me like time to play around with ORDERED and USE_NL. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Slow sql
Hi Roland, I would suggest that it pretty much depends on the number of rows that are being returned from your select statement as well as the way the select statement has been put together. Try cutting the select statement from the insert and running it against an explain plan to determine whether the select is optimised fully. Check to see whether the select statement is making use of available indices (if there are any) and consider trying various hint options to try and squeeze some more speed out of it. Regards, Kev. -Original Message- Sent: 16 August 2001 10:46 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Thomas, Kevin INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Slow sql
Hi, it must be doing full table scan.check your column positions of the indexes in table user_ind_columns and change your where condition according to the column positions. rukmini - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 16, 2001 3:15 PM 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Rukmini Devi INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Slow sql
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). * * * * * 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
