Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> REPOST: Re: tuning sql statement with bind value

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

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US