OraFAQ Forum: Performance Tuning » Difficulty in using bind variable to check explain plan

Home » RDBMS Server » Performance Tuning » Difficulty in using bind variable to check explain plan (Oracle 10.2.0.4 on Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difficulty in using bind variable to check explain plan [message #535048]

Mon, 12 December 2011 00:55

orapratap
Messages: 134
Registered: November 2011
Location: Canada

Senior Member

Hi

since the optimizer (during explain plan) assumes all bind variable to be of varchar type, while checking plan for SQL statment using bind variable of numeric and date type shall we convert (typecast) it as following?

variable n_sal number
variable dt_joining date
exec n_sal:= 1000
exec dt_joining := '12-dec-2005'
select first_name from emp_data where sal=to_number(n_sal) and joining=to_date(dt_joining);

Regards,
Pratap

Report message to a moderator

Re: Difficulty in using bind variable to check explain plan [message #535052 is a reply to message #535048]

Mon, 12 December 2011 01:12

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Try..

 SET AUTOTRACE ON ;
select ename from emp where sal=to_number(1000) and hiredate =to_date('9/14/2011','mm/dd/yyyy');

Report message to a moderator

Re: Difficulty in using bind variable to check explain plan [message #535057 is a reply to message #535052]

Mon, 12 December 2011 01:19

orapratap
Messages: 134
Registered: November 2011
Location: Canada

Senior Member

Hello Murali

Thanks for your reply

However in the case you described I will be using hard coded values and in case of bind variable peeking I wont get the exact simulation

One second!!

I think anyway such SQL executed from SQL+ will cause hard parsing so is there any advantage testing plan of a query using bind variable?

Sorry to deviate little from the original question but if I am facing a perforamce issue because of a query in pl/sql and I want to check its plan on sql+ what I can do when I don't want to write a pl/sql block?

Regards
OraPratap

Report message to a moderator

Re: Difficulty in using bind variable to check explain plan [message #535058 is a reply to message #535057]

Mon, 12 December 2011 01:22

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Post the entire query?

Report message to a moderator

Re: Difficulty in using bind variable to check explain plan [message #535059 is a reply to message #535058]

Mon, 12 December 2011 01:23

orapratap
Messages: 134
Registered: November 2011
Location: Canada

Senior Member

Hello Murali

This is my general query not about any specific sql query

Regards
OraPratap

Report message to a moderator

Re: Difficulty in using bind variable to check explain plan [message #535061 is a reply to message #535059]

Mon, 12 December 2011 01:26

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

select ename from emp where sal=to_number(&n_sal) and hiredate=to_date('dd-mon-yyyy',&dt_joining);

Report message to a moderator

Re: Difficulty in using bind variable to check explain plan [message #535067 is a reply to message #535048]

Mon, 12 December 2011 01:38

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Maybe, you should firstly post valid statements.
Bind variable in sqlplus cannot have DATE data type (at least in 10gR1, but it is not documented even in 11gR2).
When referencing bind variable from SQL or PL/SQL, it must be preceded with colon (:).

Oracle knows data types of the variables, so it is useless (and counterproductive) to cast them to the same type.
By the way, TO_DATE function has two arguments. You should use the second too or you may be surprised when NLS_DATE_FORMAT will be set differently than you expect.

[Edit: smilie disabled]

[Updated on: Mon, 12 December 2011 01:39]

Report message to a moderator

Re: Difficulty in using bind variable to check explain plan [message #535069 is a reply to message #535067]

Mon, 12 December 2011 01:51

orapratap
Messages: 134
Registered: November 2011
Location: Canada

Senior Member

Hello Flyboy

Thanks for your reply

I agree mistakes in my post (I do not have db system while writing this post)

So the missing colon and and argument to to_date are incorrect

you said
Quote:

Oracle knows data types of the variables, so it is useless (and counterproductive) to cast them to the same type

I do not agree with that
If this is the case why Oracle assumes bind variables to be only varchar type!

BTW my original query is unresolved

1) if I am using bind variable and want to use numeric value then shall I use following

variable n_sal number
exec n_sal:= 1000
select first_name from emp_data where sal=to_number(:n_sal);

Reason I want to convert datatype is that if oracle assumes it to be varchar datatype it may not be correct simulation and may change the plan

2) Similar doubts are regarding date values

Regards
OraPratap

Report message to a moderator

Re: Difficulty in using bind variable to check explain plan [message #535073 is a reply to message #535069]

Mon, 12 December 2011 02:06

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

If this is the case why Oracle assumes bind variables to be only varchar type!

This is wrong. Oracle knows the datatype of the variable because YOU (in your client application) tell it.

SQL> variable n_sal number
SQL> exec :n_sal:= 1000
SQL> @traceon

Session altered.

SQL> select * from emp where sal > :n_sal;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 02/04/1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 19/04/1987 00:00:00       3000                    20
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 23/05/1987 00:00:00       1100                    20
      7902 FORD       ANALYST         7566 03/12/1981 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 23/01/1982 00:00:00       1300                    10

12 rows selected.

SQL> @traceoff

Session altered.

Content of trace file:

...
PARSING IN CURSOR #7 len=36 dep=0 uid=57 oct=3 lid=57 tim=927600340250 hv=1782574640 ad='6fa8fbb8'
select * from emp where sal > :n_sal
END OF STMT
PARSE #7:c=0,e=16587,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=927600340242
BINDS #7:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=080a9b08  bln=22  avl=02  flg=05
  value=1000
EXEC #7:c=15625,e=117869,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=927600580673
WAIT #7: nam='SQL*Net message to client' ela= 18 driver id=1111838976 #bytes=1 p3=0 obj#=50293 tim=927600648738
FETCH #7:c=0,e=163,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=927600666951
...

Note "oacdty=02" which means datatype NUMBER.

SQL> set serveroutput off
SQL> var s_sal varchar2(10);
SQL> exec :S_SAL := '1000'

PL/SQL procedure successfully completed.

SQL> @traceon

Session altered.

SQL> select * from emp where sal > :s_sal;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 02/04/1981 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 19/04/1987 00:00:00       3000                    20
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 23/05/1987 00:00:00       1100                    20
      7902 FORD       ANALYST         7566 03/12/1981 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 23/01/1982 00:00:00       1300                    10

12 rows selected.

SQL> @traceoff

Session altered.

Content of trace:

...
select * from emp where sal > :s_sal
END OF STMT
PARSE #6:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=928062645755
BINDS #6:
kkscoacd
 Bind#0
  oacdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=178 siz=32 off=0
  kxsbbbfp=07f6a3bc  bln=32  avl=04  flg=05
  value="1000"
EXEC #6:c=0,e=194664,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=928062946413
WAIT #6: nam='SQL*Net message to client' ela= 14 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=928062958604
FETCH #6:c=0,e=164,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=928062994912
...

Note "oacdty=01" which means datatype VARCHAR2.

Regards
Michel

[Updated on: Mon, 12 December 2011 02:08]

Report message to a moderator

Re: Difficulty in using bind variable to check explain plan [message #535075 is a reply to message #535073]

Mon, 12 December 2011 02:20

orapratap
Messages: 134
Registered: November 2011
Location: Canada

Senior Member

Thanks Michel

You have proved it now

I am unable to search the conent which misled me earlier

Meanwhile it seems explain plan may be incorrect in this and diplay_cursor is the way to go as following link by another expert suggests :

http://blog.tanelpoder.com/2009/11/17/explain-plan-for-command-may-show-you-the-wrong-execution-plan-part-1/

Regards
OraPratap

Report message to a moderator