Download the Oracle 9i client for Windows, you'll need
an Oracle Technet account but you can get one for free
immediately, then choose the scratchpad tool, it
generates the explain plan and also you can get a
complete report.. and other tools for oracle dba..
Gabriel
- Teresita Castro <[EMAIL PROTECTED]>
wrote:
> 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"
>
>
>
=== message truncated ===
"Any dream worth having is a dream worth fighting for"(Cualquier sueño que valga la
pena tener, es un sueño por el que vale la pena luchar)Charles Xavier
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gabriel Aragon
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).
Received on Thu Jun 26 2003 - 15:55:51 CDT
