REPOST: Re: tuning sql statement with bind value

From: Kevin Brand <kevin.brandx_at_tel.gte.com>
Date: Fri, 25 Jan 2002 11:57:00 -0600
Message-ID: <6$--$%%%_$%_-$$%$$@news.noc.cabal.int>

AUTOTRACE=true doesn't work unless you bind the variables and execute the statement, however the following does work:

SQL> desc mytab;

 Name                            Null?    Type
 ------------------------------- -------- ----
 COL1                                     VARCHAR2(10)
 COL2                                     NUMBER
 COL3                                     CHAR(1)

SQL> explain plan for select * from mytab where col1 = :1   2 ;

Explained.

SQL> @/opt/app/oracle/admin/util/getplan

Query Plan

---

---

SELECT STATEMENT Cost = 1
  TABLE ACCESS FULL MYTAB SQL> ... getplan.sql is simply a formatting SQL to get rows from PLAN_TABLE. If you don't have PLAN_TABLE, execute
$ORACLE_HOME/rdbms/admin/utlxplan.sql to build it.

-Kevin

"Mike F" <u518615722_at_spawnkill.ip-mobilphone.net> wrote in message news:l.1011976519.1981536865@[64.94.198.252]...
> I am learning the most difficult part of performance tuning.
> Almost all of our sql statement use bind values. With binding
> variables in your sql statement, oracle does not allow you to explain
> plan, should i just change the sql, hard code it and explain it and
> believe that oracle will use the same execution path as the binding one?

>

> How about histograms?
> Thanks for you help.

>
>
>
>
>
>

> --
> Sent by dbadba62 from hotmail subpart of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new

• WAS CANCELLED BY =======: From: "Kevin Brand" <kevin.brandx_at_tel.gte.com> Control: cancel <a2s6bn$ksp$1_at_news.gte.com> Subject: cmsg cancel <a2s6bn$ksp$1_at_news.gte.com> Date: Mon, 28 Jan 2002 01:53:43 GMT Message-ID: <cancel.a2s6bn$ksp$1_at_news.gte.com> X-No-Archive: yes Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.misc NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88 Lines: 1 Path: news.uni-stuttgart.de!news.fh-hannover.de!feed.news.nacamar.de!newsfeeds.belnet.be!news.belnet.be!newsfeed00.sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!fr.clara.net!heighliner.fr.clara.net!news.mailgate.org!news2.euro.net!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp03!u&n&a&c&anceller Xref: news.uni-stuttgart.de control:40716350

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Fri Jan 25 2002 - 11:57:00 CST