OraFAQ Forum: Performance Tuning » Avoiding bind variable from a sql in java code

Home » RDBMS Server » Performance Tuning » Avoiding bind variable from a sql in java code (oracle 10.2.0.4 on Solaris 10)

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

Avoiding bind variable from a sql in java code [message #460854]

Tue, 15 June 2010 04:35

orausern
Messages: 826
Registered: December 2005

Senior Member

Hi,

We are on oracle 10.2.0.4 on Solaris 10 and have a perf. issue with a bind variable using query. The query is in java application. I want to test its performance when the query doesn't use bind variable and instead uses the passed value as literal. How can it be done?

As example lets say the query is:


SQL> variable vn varchar2(20);
SQL>  EXEC :vn :='ADAMS';

PL/SQL procedure successfully completed.

SQL> select * from emp where ename=:vn;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20

1 row selected.

SQL> EXEC :vn :='KING';

PL/SQL procedure successfully completed.

SQL> select * from emp where ename=:vn;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

1 row selected.

But the statements will be taken as similar statements by oracle (due to :vn). Now I want oracle to take it as literal and the change for this has to be done in java code in my actual scenario which has a different query (but conceptually it uses bind variable and I want it to use passed value as literal). How can it be done?

Thanks a lot!

Report message to a moderator

Re: Avoiding bind variable from a sql in java code [message #460856 is a reply to message #460854]

Tue, 15 June 2010 04:45

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

That's java question, once the db has a bind you can't make it turn it back into a literal.
Are you sure binds are the problem?

Report message to a moderator

Re: Avoiding bind variable from a sql in java code [message #460860 is a reply to message #460856]

Tue, 15 June 2010 04:52

orausern
Messages: 826
Registered: December 2005

Senior Member

Thanks for your reply. I am trying out different options. With one value of parameters the query is very fast...with other set of values it goes slow. I want to check if using cursor_sharing_exact in teh sql query as a hint can help but this query has those bind variables like:

select * from emp where ename=:vn;

This means that even if I put the hint it wont help right? I mean even if I put the query like it wont have turn off the bind variables right?

select /*+ CURSOR_SHARING_EXACT */ * from emp where ename=:vn;

Thanks

Report message to a moderator

Re: Avoiding bind variable from a sql in java code [message #460862 is a reply to message #460860]

Tue, 15 June 2010 04:56

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Sounds like you've got skewed data - might what to look at collecting histograms.
Cursor sharing isn't going to help and if the java is using binds it should be set to exact anyway - isn't it?
Plus you can't use cursor sharing as a hint, lowest level you can set that is session.

Report message to a moderator

Re: Avoiding bind variable from a sql in java code [message #460863 is a reply to message #460862]

Tue, 15 June 2010 04:58

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Also go to asktom.oracle.com and have a search for bind variable peeking on there - that's probably what the problem is.

Report message to a moderator

Re: Avoiding bind variable from a sql in java code [message #460865 is a reply to message #460862]

Tue, 15 June 2010 05:02

orausern
Messages: 826
Registered: December 2005

Senior Member

cursor_sharing_exact is a hint documented in oracle so it can be used right? You are saying cursor sharing can be done only at session level so I am not clear, I think it can be used at query level too right?

[Updated on: Tue, 15 June 2010 05:03]

Report message to a moderator

Re: Avoiding bind variable from a sql in java code [message #460868 is a reply to message #460865]

Tue, 15 June 2010 05:24

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

I stand corrected, you can use it as a hint.
It stops oracle replacing literals with binds, but if the java is passing a bind it won't help.
What's cursor_sharing set to at system level?

Report message to a moderator

Re: Avoiding bind variable from a sql in java code [message #460871 is a reply to message #460868]

Tue, 15 June 2010 05:27

orausern
Messages: 826
Registered: December 2005

Senior Member

at system level it is set to similar. Thanks a lot for helping out. I just don't know how the java should change so that it doesn't get passed as bind variable...can someone help on that?

Report message to a moderator

Re: Avoiding bind variable from a sql in java code [message #460873 is a reply to message #460871]

Tue, 15 June 2010 05:35

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

There might be someone who knows but most of use here aren't java programmers. I'm certainly not.
If the java is set using binds through out then cursor sharing should be set to exact. Unless you've got some other app that accesses the same db that doesn't use binds.
Cursor_sharing set to similar or force is a work around for applications that don't use binds.

I also suggest again that you read up on histograms and bind variable peeking as it's entirely possible you can fix this without changing the java - asktom would be the best place to start.

Report message to a moderator

Re: Avoiding bind variable from a sql in java code [message #460954 is a reply to message #460873]

Tue, 15 June 2010 13:21

orausern
Messages: 826
Registered: December 2005

Senior Member

I am confused due to my lack of expertise...but to understand how to tackle this i will create an example and ask for help in a new thread.

Thanks,

Report message to a moderator

Previous Topic:	SGA "usage"
Next Topic:	TKPROF Output Understanding

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 13:03:09 CST 2024