RE: Index question

From: Johnson, Michael <Michael.Johnson_at_oln-afmc.af.mil>
Date: Fri, 27 Jun 2003 14:13:29 -0700
Message-ID: <F001.005BB6A8.20030627134453@fatcity.com>

Terrista,

Did you run the UTLXPLAN.sql
file so you can get a explain plan output ?

Check Metalink for an
explanation of all of this .... here is one link ....

<A
href="http://metalink.oracle.com/metalink/plsql/ml2_gui.startup">http://metalink.oracle.com/metalink/plsql/ml2_gui.startup

Then .....

SQL> Set Autotrace
on

Then run those queries
and report back the outputs.

Everything else is set up fine
for now although we will probaly change
a few more optimzer
parameters so stay tuned for that.

buenos días a usted.
Miquel.

-----Original
 Message-----From: Teresita Castro
 [mailto:[EMAIL PROTECTED]Sent: Friday, June 27, 2003 10:30 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Index question
 About the time test, this are some of the results:

 SQL> set time on10:22:59 SQL> select * from ictrans where item='0010096'10:23:30 2 10:23:39 SQL> 10:23:53 SQL> select * from ictrans where location='TJU01'10:24:20 2 10:24:21 SQL> update itemloc set average_cost=6.3 where company='2000' and item='0010041'10:27:45 2 10:27:49 SQL> >>> [EMAIL PROTECTED] 06/26/03 03:09PM >>>
 Teresita,

 Usted tiene un nombre hermoso. Yo nunca he estado a Guadalajara, pero oigo es muy agradable. La mayor parte de mi tiempo en México ha sido de Tiajuana completamente al sur a Zijuantinajo. El País hermoso y muy entibiar a gente Italia mucho más apreciando. Mi próximo viaje a México estará a Cabo San Lucas probablemente en agosto. Espero llegar a Guadalajara algún día.
 As for your current problem
 with the index......

First run a
 script called utlxplan.sql (spelling) from the $ORACLE_HOME/rdbms/admin directory under the schema you wish to execute this query out of then
 

 The optimizer_mode = Choose , timed_statistics = true
 parameters can be set in your
 init*.ora file, but your can see the current setting of these values through ...
 
 SQL> select value from v$parameter where name like 'optimizer%'
 or name = 'timed_statistics';
 
 The query will also return several optimizer values which may be needed for assessment

later
 on.
 
 If you cannot bounce ( restart) the instance then consider setting these parameters using the command ...
 
 SQL> Alter
 session set .....
 
 Then
 ....
 
 SQL> set
 autotrace traceonly
 also
 consider
 SQL> set
 timing on
 for a relative cost on how much time the query takes
 
 and then
 Execute
 your SQL statement which will output an explain plan for your viewing pleasure.
 
 Lots of information on metalink on how to use and interpret the explain plan.
 
 Lets stop here and report back what your output is on the explain plan.
 
 CAUTION: I never use 3rd party products. I like to go straight to the data dictionary to find out whats going on with the database. Not to say 3rd party products are bad or anything, its just my style thats all.
 
 
 Espero que esto lo ayude fuera.
 
 Miquel.

-----Original Message-----From: Teresita Castro [mailto:[EMAIL PROTECTED]Sent: Thursday, June 26, 2003 11:25 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Index question
 Where I can see the explain Plan?... I have TOAD but is a try version and I don't have this option activated, can I see it in another program?

 And where I have to define this
 option:
 optimizer_mode = Choose
 timed_statistics = true
 
 Michael:
 Sobre tus vacaciones como estaras cerca de Guadalaja, te recomiendo visitar estar ciudad, y tomar el tur del tren Tequita express. Yo no he ido a puerto Vallarte pero la gente de por alla es muy amigable y servicial, buena suerte !! >>>
 [EMAIL PROTECTED] 06/25/03 09:59PM >>>
 First, Your english is
 excellent or "Usted habla inglés muy bien. "
 Assuming you are running
 version 8i or better ....

 Have you analyzed the
 tables you are querying against ? You may not need to force a rule as the CBO
 will try to find the quickest way. It looks like
 you are using the RBO by
 default.

 Do you have optimizer_mode

Choose ? Do you have timed_statistics = true ? Have you run your queries through an explain plan ? If not this will show you the execution path and a relative cost of each statement. You can manipulate your SQL to see different costs as you change the statement. Also, Your queries could have been run while competing for resources in one case and maybe not in another case. Run both queries in the same environment.

    I have found that small
    tables don't need indexes for the most part although this

    is not a hard and fast
    rule.   You must go through the process.
     Espero que eso lo
    ayude y la buena suerte a usted.   Espero verlo en

Puerto Vallarta Alguna Vez
pronto en la playa con un margarita. Hasta Luego.

    Miquel.

      <FONT 
      size=2>-----Original Message-----From: Teresita Castro 
      [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 
      2003 7:04 PMTo: Multiple recipients of list 
      ORACLE-LSubject: Re: Index question
      Hi!!
       
      Let explain more about my 
      situation.
      The company that I work for is a 
      chain of stores around some city's on Mexico, they bought Lawson a system 
      that uses Oracle to manage the data bases, at first they use SQL Server 
      2000, but I wasn't enough to manager all the information.
      The structure of the table is all ready 
      done and I have to learn it to do some reports that Lawson don't have, 
      change or delete information and export some information to dbf 
      files. Because we was using SQL Server I used Store procedures to 
      return  the select result to VB recordset and the I pass the 
      select result to Crystal Report or to a DBF file.
      Well  I see that in Oracle the store 
      procedure do not returns the result set has easy has SQL Server so I use 
      and statement that after execute it returns me the result in a record 
      set.
       
      sQuery = "SELECT COMPANY,LOCATION, 
      R_NAME FROM ICLOCATION " & 
      _         "WHERE COMPANY=2000 
      OR COMPANY=2001 OR COMPANY=2002 order by COMPANY,LOCATION"Set recRS = 
      New ADODB.RecordsetrecRS.Open sQuery, gcnOracle, adOpenForwardOnly, 
      adLockReadOnly, adCmdText
       
      or execute a delete or update 
      statement
       
      sSQL = "update  /*+ 
      INDEX(itemloc ITLSET2) */ itemloc set average_cost =" & costo & " 
      where (company='2000') and item= '" & Arti & 
      "'"gcnOracle.Execute sSQL
      gcnOracle.Execute "Commit", 
      dbSQLPassThrough   
      Back to my problem:
      In this case the update of the average 
      cost has to be done on the table Item location ( ITEMLOC)  that have 
      all the item that each location( store) have. The locations have a 
      company, when we changes the average cost is per company ( each company 
      represent a different city) 
       
      So my boss  execute the update 
      statement 
       
      
      sSQL = "update  itemloc set 
      average_cost =" & costo & " where (company='2000') and item= '" 
      & Arti & "'"
      and she told me that per 
      item  it takes like 10 seconds.
      After read the article that I mention 
      she changes the statement to this :
       sSQL = "update  /*+ 
      INDEX(itemloc ITLSET2) */ itemloc set average_cost =" & costo & " 
      where (company='2000') and item= '" & Arti & 
      "'"
       
      ITLSET2 is a index that have company(1), 
      location(2) and Item(3) and it takes 2 
      seconds per item, so that is way she is convinced that we have to uses the 
      /*+ INDEX(itemloc ITLSET2) */ in all of our select, 
      update or delete statement.
       
      I hope you undestant my English and my 
      problem too, because I have to do some really complicated 
      queries that have like 3 or 5 tables in them, and using this method 
      will give me some serious complications, maybe in this case (change of the 
      average cost) is not too dangerous. 
      But I have to give her strong 
      statements to change her mind.
       
      Thanks for everything 
      friends!!
      >>> [EMAIL PROTECTED] 06/25/03 06:10PM 
      >>>Teresita,I don't fully understand whether 
      adding or removing a hint caused theproblem but like Stephane said - 
      you should probably stay away from themfor now.  If adding a hint 
      decreased performance then you have proved thisfor yourself.A 
      couple of important points:* Using an index isn't always faster 
      than scanning the table* If a database is correctly analyzed then 
      the optimisor can determine whento use indexes or not 
      automatically* Hints can force the optimisor to choose a 
      non-optimal execution plan.  Ifyou are smarter than the optimisor 
      this may be fine but in most cases theoptimisor will make the right 
      decision when all tables are analyzed* Hints have very specific 
      formatting and object name rules.  If you renamean index the hint 
      will become invalid and be blissfully ignored - you won'teven 
      know.* There are some hints which can safely be used but it takes 
      a goodunderstanding of Oracle first.  My advice would be don't 
      use them.  If youhave a specific problem then post lot's of 
      details here and someone mightsuggest trying a hint.* Until 
      you really understand the implications of using a specific hint itcan 
      be dangerous in terms of performance - Stephane is not lying when 
      hesays you can do more harm than good.  I have many cases where I 
      can "tune"a query simply by removing the hints which someone included 
      and lettingOracle do what it does 
      best.Regards,      
      Mark.                                                                                                                                        
                            
      "Teresita 
      Castro"                                                                                                 
                            
      <[EMAIL PROTECTED]        
      To:       Multiple recipients of list 
      ORACLE-L 
      <[EMAIL PROTECTED]>                  
                            
      martmx.com>                
      cc:                                                                                    
                            
      Sent 
      by:                   
      Subject:  Re: Index 
      question                                                           
                            
      [EMAIL PROTECTED]                                                                                               
                            
      om                                                                                                                
                                                                                                                                              
                                                                                                                                              
                            
      26/06/2003 
      09:29                                                                                                  
                            
      Please respond 
      to                                                                                                 
                            
      ORACLE-L                                                                                                          
                                                                                                                                              
                                                                                                                                              
      Ophss...I have a terrible problem, I am using VB with 
      Oracle and since weput that instruccion on the execute instruction our 
      execution timedecrease.The problem is that my boss was the one 
      that found that instruction and Ihave to told her not to uses it, but 
      with her I have to show some evidencethat show why now to uses 
      hits.Do you have any information that I can show to 
      her.Thanks!!!>>> [EMAIL PROTECTED] 06/25/03 
      02:34PM >>>Teresita,   Since you advertise 
      yourself as a newbie, listen to an old-timer :for the next two years, 
      forget about hints. You risk doing more harmthan 
      good.--Regards,Stephane FaroultOriole 
      Software--Please see the official ORACLE-L FAQ: <A 
      href="http://www.orafaq.net">http://www.orafaq.net--Author: 
      Stephane Faroult  INET: [EMAIL PROTECTED]Fat City 
      Network Services    -- 858-538-5051 <A 
      href="http://www.fatcity.com">http://www.fatcity.comSan Diego, 
      California        -- Mailing list and 
      web hosting 
      services---------------------------------------------------------------------To 
      REMOVE yourself from this mailing list, send an E-Mail messageto: 
      [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe 
      message BODY, include a line containing: UNSUB ORACLE-L(or the name of 
      mailing list you want to be removed from).  You mayalso send the 
      HELP command for other information (like 
      subscribing).<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>   
      Privileged/Confidential information may be contained in this 
      message.          If you 
      are not the addressee indicated in this 
      message       (or responsible for 
      delivery of the message to such 
      person),            
      you may not copy or deliver this message to anyone.In such case, you 
      should destroy this message and kindly notify the 
      sender           by 
      reply e-mail or by telephone on (61 3) 9612-6999.   Please 
      advise immediately if you or your employer does not consent 
      to                
      Internet e-mail for messages of this 
      kind.        Opinions, conclusions 
      and other information in this 
      message              
      that do not relate to the official business 
      of                         
      Transurban City Link 
      Ltd         shall be 
      understood as neither given nor endorsed by 
      it.<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>-- 
      Please see the official ORACLE-L FAQ: <A 
      href="http://www.orafaq.net">http://www.orafaq.net-- Author: 
      Mark Richard  INET: [EMAIL PROTECTED]Fat City 
      Network Services    -- 858-538-5051 <A 
      href="http://www.fatcity.com">http://www.fatcity.comSan Diego, 
      California        -- Mailing list and 
      web hosting 
      services---------------------------------------------------------------------To 
      REMOVE yourself from this mailing list, send an E-Mail messageto: 
      [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe 
      message BODY, include a line containing: UNSUB ORACLE-L(or the name of 
      mailing list you want to be removed from).  You mayalso send the 
      HELP command for other information (like

subscribing). Received on Fri Jun 27 2003 - 16:13:29 CDT