Thanks for the question, Reader.

Asked: March 15, 2011 - 7:58 am UTC

Last updated: November 01, 2017 - 2:47 am UTC

Version: 10.2.0.4

Viewed 10K+ times! This question is

You Asked

Dear Tom

How can I find out if a SQL used bind variable or not? I know you can check v$sql and get a list of distinct SQLs after translating the constants in the predicates. But if it's using bind variable, shouldn't V$SQL_BIND_CAPTURE have that SQL_ID and also the values really used for the variables?

I used your function to identify the SQLs that are NOT using bind variables after reducing the total count to 20. Then I picked up a query that had bound the variables for sure to analyze it and went to V$SQL_BIND_CAPTURE view but I can't see even a single instance of that SQL_ID. What can be the reason?

Regards

and Connor said...

https://docs.oracle.com/database/122/REFRN/V-SQL_BIND_CAPTURE.htm#REFRN30310

V$SQL_BIND_CAPTURE is not always populated, it can easily be disabled. Further, it is somewhat specific to a session. Many sessions can be simultaneously executing a given sql statement - and their binds are not all visible to other sessions. Also, if someone isn't executing the SQL statement, there wouldn't be any binds to look at.

You can determine if a statement used binds by visual inspection (you'll SEE the :bind placeholders).

if we "peeked" at the binds, you can see that in dbms_xplan output (comes from the other_xml information in v$sql_plan)

ops$tkyte%ORA11GR2> begin
  2      for x in (select sql_id, child_number from v$sql
  3                 where sql_text = 'select count(object_type) from t n_is_1_first where id = :n')
  4      loop
  5          for y in (select PLAN_TABLE_OUTPUT
  6                      from table(dbms_xplan.display_cursor(x.sql_id,x.child_number,
  7                                                   'typical +peeked_binds')) )
  8          loop
  9              dbms_output.put_line( '.' || y.plan_table_output );
 10          end loop;
 11          for i in 1 .. 5
 12          loop
 13              dbms_output.put_line( '.' );
 14          end loop;
 15      end loop;
 16  end;
 17  /
.SQL_ID  2x454rqkvtpbc, child number 0
.-------------------------------------
.select count(object_type) from t n_is_1_first where id = :n
.
.Plan hash value: 1789076273
.
.--------------------------------------------------------------------------------------
.| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
.--------------------------------------------------------------------------------------
.|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |
.|   1 |  SORT AGGREGATE              |       |     1 |    14 |            |          |
.|   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    14 |     2   (0)| 00:00:01 |
.|*  3 |    INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
.--------------------------------------------------------------------------------------
.
.Peeked Binds (identified by position):
.--------------------------------------
.
.   1 - :N (NUMBER): 1
.
.Predicate Information (identified by operation id):
.---------------------------------------------------
.
.   3 - access("ID"=:N)
.
.
.
.
.
.
.SQL_ID  2x454rqkvtpbc, child number 1
.-------------------------------------
.select count(object_type) from t n_is_1_first where id = :n
.
.Plan hash value: 2966233522
.
.---------------------------------------------------------------------------
.| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
.---------------------------------------------------------------------------
.|   0 | SELECT STATEMENT   |      |       |       |    82 (100)|          |
.|   1 |  SORT AGGREGATE    |      |     1 |    14 |            |          |
.|*  2 |   TABLE ACCESS FULL| T    | 19999 |   273K|    82   (0)| 00:00:01 |
.---------------------------------------------------------------------------
.
.Peeked Binds (identified by position):
.--------------------------------------
.
.   1 - :N (NUMBER): 99
.
.Predicate Information (identified by operation id):
.---------------------------------------------------
.
.   2 - filter("ID"=:N)
.
.
.
.
.
.

PL/SQL procedure successfully completed.

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good explanation

Reader, March 15, 2011 - 10:39 am UTC

Hi Tom

Thanks, I never knew that. Whenever I had checked v$Sql_bind_capture before to look at the values, it was always there. So it never occured to me that it was kind of specific to session. Now it makes sense.

Now, I am not sure if this question is related to the original subject but wanted to ask this. Please feel free to ignore it and I'll ask it when you accept questions again.

Say, I have an application with 800 to 1000 concurrent users and let's assume that all of them run the SAME query SELECT blah from table a where a.col1=:x and a.col2 =:y;

And my developer has bound the variable and there's no problem as such with the query. But my question is, if all of those 800 sessions run the same parsed SQL at the same time, will there be any contention for the single parsed SQL? Or will it be better to create few versions of that SQL as follows:

SELECT blah from table b where b.col1=x and b.col2 =y;
SELECT blah from table c where c.col1=x and c.col2 =y;
SELECT blah from table d where d.col1=x and d.col2 =y;

Regards

March 15, 2011 - 10:48 am UTC

if you have 800 to 1000 concurrent sessions and they are all active at the same instant, I hope you have at least 100 cpus or more. Think about that for a minute...

If we hit too much traffic to a single sql statement - the opportunity to create child cursors already exists in the database. You do not need to do anything.

HOWEVER - you should be thinking about getting your connection pools under control. There is no way on todays conventional hardware that you should have anywhere NEAR 800/1000 concurrent connections, let alone active sessions!!! On say an 8 cpu machine - maybe 30/60 connections at most - but not hundreds, definitely not thousands. Maybe when we have 128 cores "standard" it'll be a common practice.

Do your connection concentration in the middle tier, tighten up those connection pool sizes - they should be SMALL and definitely not in the hundreds.

Small clarification

Reader, March 15, 2011 - 10:55 am UTC

Hi Tom

Thanks for your quick response. OK, it may not be 800 to 1000 but I just made it up to give importance to the context of the question. So, say even if I have 100 sessions all running the same query at the same time, do you say that there won't be a need for explicitly creating additional versions of that SQL? Won't there be contention for the parsed SQL as each session will have to wait until the previous one has finished as it will be a serialized operation?

Also, can you please explain this bit little more clearly, if you don't mind?

<quote>If we hit too much traffic to a single sql statement - the opportunity to create child cursors already exists in the database. You do not need to do anything. </quote>

Would the database create additional cursors automatically in such a case?

Thanks (and I won't bother you again today--I believe :)

March 15, 2011 - 11:02 am UTC

... do you say that there
won't be a need for explicitly creating additional versions of that SQL? ..

correct.

.. Won't
there be contention for the parsed SQL as each session will have to wait until
the previous one has finished as it will be a serialized operation? ..

not anymore contention than for different sql in most cases. Parsing - be it soft or hard - requires latching (locking, serializing on) large segments of the shared pool/SGA. Parsing of ANY statement is a more or less serial process. Yes, there are a few (very few) subpools in the shared pool that allow for limited parse concurrency (you need a large shared pool, really large and lots of cpus for this to be true). But even then, parsing is a pretty serial process.

You would be best served by making it so you REDUCE the number of parse calls. The easiest way I know to do that is to move as much sql into plsql as possible. plsql caches cursors open in a fantastic way - reducing the parse calls in a HUGE way.

SQL vs PL/SQL

Reader, March 15, 2011 - 11:21 am UTC

Hi Tom

Thanks once again for your explanation and time. I agree that PL/SQL caches cursors nicely and may help us in this context. This brings another doubt for me.

Say, I have a procedure that accepts a parameter and updates or inserts a table (like a MERGE). If the parameter value is available in table, the procedure will update the record or else insert as a new record. And the inserts and update SQL statements inside the PL/SQL have no issues with external binding.

However, say, if I have a workflow (using Sqlplus interface to call the procedure) that has to call this procedure for every record in real time, will that not add additional burden to the Oracle memory? That is, would not Oracle have to parse the calling statement every time as literals?

Calling WORKFLOW:

Declare
v_id number;
v_date date;
Begin
v_id := 1;
v_date := '15-MAR-2011';
call_insert_update_procedure(v_id, v_date);
End;
/

Will this PL/SQL block itself not have to undergo hard parsing every time because of literals like 1 and '15-MAR-2011'? I see this happening in my database where a process automatically assigns the value to the variables (3rd party product) and then call my procedure. And in V$SQL, I see the PL/SQL calling block gets a new SQL_ID every time. How can this be avoided?

Thanks

March 15, 2011 - 1:10 pm UTC

do not use SQLPlus as a development tool - use a programming language so you have things like error handling, binding, debugging, etc.

cursor sharing force doesn't hit blocks of plsql like that - so it won't help. There is nothing other than sound programming that will correct that bit.

v$sql_bind_capture Value_String

S, March 17, 2011 - 3:38 pm UTC

Tom
i was doing a similar research trying to grab the values actually getting passed in to the bind variables, but i see Nulls in Value_String, i do see that the max length has values in it..this is 11gR2..Now my optimizer bind peek is set to FALSE, is that the reason that the Value_String does not show any values ?
My problem scneario is a Java App, with very high concurrency levels, doing primarily inserts into couple of tables..Now in sql bind capture i am seeing max lengths of up to 2000 , for some bind variables, but not always..
Now, the table column max is actually 80 or something, so i am trying to find out what exactly is the app passing in, so i can give an example to the app team..so that sql bind capture is creating a new version for it..
They are using the 11.2.0.1 JDBC driver and they are saying not doing any different between different sql calls from Java..
To prevent new sql child cursors with different bind variable lengths i am researching this..in sql_bind_capture, the versions are reporting difference primarily due to

PURGED_CURSOR :196
BIND_LENGTH_UPGRADEABLE :197

Thanks for your help as always

March 17, 2011 - 3:44 pm UTC

can you enable some sql tracing via dbms_monitor with binds=>true, that'll let you see the sql and the binds in a trace file easily. All of the bind information (lengths and so on) will be there as well.

Just a clarification

Reader, March 20, 2011 - 5:48 am UTC

Hi Tom

I tried my best to create such a situation to see if this is happening.

<quote>if we hit too much traffic to a single sql statement - the opportunity to create child cursors already exists in the database. You do not need to do anything.</quote>

Still I can't see any new instances of SQLs for the same SQL when I run it from multiple sessions.

Can you please give an example for this by a test case when you get a chance?

Thanks as always

March 20, 2011 - 9:29 am UTC

Nope, I've seen it only happen under real duress, some would and do consider it happening at all a bug, creating multiple child cursors. It would be an indication you are doing it all wrong by the way if it happen, it would be an indication you have far more connections than you should from your middle tier. Unless you have hundreds of CPUs there is no way you should have hundreds of connections - which is what it would take.

Remember - it isn't just the SQL statement that causes some serialization during parsing - it is the fact you are PARSING in the first place. Having lots of people trying to parse the same text does lead to marginally increased contention - but the basic problem is lots of people parsing at the same time - not just lots of people parsing the same statement.

Out of Date

Chris Ruel, September 07, 2017 - 6:22 pm UTC

I don't really want to give this a bad review but the link to the documentation is out of date. I get a 404 page not found error.

September 09, 2017 - 1:01 am UTC

No - *thank you* for bringing it to our attention. This is why we have the "Is this out of date" option in AskTOM - so we can continuously improve the content on the site.

The link has been updated.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Good explanation

Small clarification

SQL vs PL/SQL

v$sql_bind_capture Value_String

Just a clarification

Out of Date

More to Explore

PL/SQL demos

PL/SQL docs

Bind capturing

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Good explanation

Small clarification

SQL vs PL/SQL

v$sql_bind_capture Value_String

Just a clarification

Out of Date

More to Explore

PL/SQL demos

PL/SQL docs