Re: RE: Index question

From: <rgaffuri_at_cox.net>
Date: Thu, 26 Jun 2003 11:25:59 -0700
Message-ID: <F001.005BA947.20030626110948@fatcity.com>

go to the rdbms/admin directory in your oracle home(i forget the full path so you will have to do a search).

look for the script utlxplan.sql

run it in a directory where you can create a table. this will create the plan table.

when you want to see the result of a query plan type

set autotrace on

run the query. results will follow.

if you just want the results type

set autotrace traceonly.

to interpret go to otn.oracle.com its in the performance guide, there is also a really good explain plan doc on metalink.
>
> From: "Teresita Castro" <[EMAIL PROTECTED]>
> Date: 2003/06/26 Thu PM 02:24:46 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: 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.
>
> -----Original Message-----
> Sent: Wednesday, June 25, 2003 7:04 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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.Recordset
> recRS.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 the
> problem but like Stephane said - you should probably stay away from them
> for now. If adding a hint decreased performance then you have proved this
> for 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 when
> to use indexes or not automatically
>
> * Hints can force the optimisor to choose a non-optimal execution plan. If
> you are smarter than the optimisor this may be fine but in most cases the
> optimisor will make the right decision when all tables are analyzed
>
> * Hints have very specific formatting and object name rules. If you rename
> an index the hint will become invalid and be blissfully ignored - you won't
> even know.
>
> * There are some hints which can safely be used but it takes a good
> understanding of Oracle first. My advice would be don't use them. If you
> have a specific problem then post lot's of details here and someone might
> suggest trying a hint.
>
> * Until you really understand the implications of using a specific hint it
> can be dangerous in terms of performance - Stephane is not lying when he
> says 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 letting
> Oracle 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 we
> put that instruccion on the execute instruction our execution time
> decrease.
>
> The problem is that my boss was the one that found that instruction and I
> have to told her not to uses it, but with her I have to show some evidence
> that 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 harm
> than good.
>
> --
> Regards,
>
> Stephane Faroult
> Oriole Software
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Stephane Faroult
> 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).
>
>
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> 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: http://www.orafaq.net
> --
> Author: Mark Richard
> 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).
>
>

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:
<FONT color=#000080
size=2> optimizer_mode = Choose
<FONT
size=2> timed_statistics = true
<FONT color=#000080
size=1>
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 <FONT
size=1>"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.

  -----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).<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>&ggt;>>>>>>>

  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.<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>&ggt;>>>>>>--

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