How to obtain the variable value bound to an oracle SQL statement, oraclesql

How to obtain the variable value bound to an oracle SQL statement, oraclesql

When diagnosing SQL Performance problems, we sometimes need to obtain the actual value of the variable it binds, and then bring the actual value into the SQL statement, use the original SQL statement to form a select statement (with the where condition). Execute the statement to check the selectivity.

This document describes how to obtain the bound variable value. Thanks for the help of elders who have prepared this article.

This article applies to databases with oracle 10 Gb or later.

 

 

Alter session set nls_date_format = 'yyyy-mm-dd, hh24: mi: ss '; set linesize 400col SQL _Id format a20col name format a20col datatype_string format a14col value_string format a20 -- This SQL statement reads the bound variable value information from the memory. If it is not in the memory, use the next sqlselect SQL _id, name, datatype_string, last_captured, value_string from v $ SQL _bind_capture where SQL _id = 'hangzhou' order by LAST_CAPTURED, POSITION; -- This SQL statement reads binding variable value information from awr select instance_number, SQL _id, name, datatype_string, last_captured, value_string from dba_hist_sqlbind where SQL _id = 'fahv8x6ngrb50 'order by LAST_CAPTURED, POSITION;

How Does oracle use variable binding?

Variable binding
2009/08/09 by maclean liu 5 COMMENTS
Oracle generally performs the following steps when executing SQL statements:
When an SQL statement is executed for the first time, Oracle will confirm whether the Syntax of the statement is correct (Syntax parse is parsed) and further confirm the existence of table and column related to the statement (semantic parse) and the optimizer determines the execution plan. Hard parsing consumes a lot of CPU time and system resources. Too many hard parsing will effectively reduce system performance.
If the parsed analysis tree and execution plan are still in the shared pool, the same SQL statement only needs soft resolution. Soft resolution converts the entered SQL statement into a hash code, compares it with existing records on the hash linked list in the Shared Pool, finds the corresponding cursor information, and uses the existing execution plan for execution.
Bind the variable to the SQL statement.
Execute the SQL statement. The query statement returns the result set.
Oracle cannot treat these SQL statements as the same without binding variables, for example, the following two statements:
Select * from emp where empno = 1234

Select * from emp where empno = 5678

Due to the differences in free variables, Oracle considers the preceding two statements as different. When the first SQL statement is hard parsed, hard parsing cannot be avoided when the second SQL statement is executed. In the above cases, as long as the free variable changes, hard Parsing is required. This is the main reason why it is strongly recommended to bind a variable. the actual value of the statement variable bound to the variable is substituted only at the final stage of SQL Execution. The statement is as follows:
Select * from emp where empno =: x
This statement uses the BIND value: x to replace the free variable. In the application, the statement may exist in pre-compilation or normal compilation mode, and only the variable value is substituted in the execution phase, multiple executions only require one hard resolution, which greatly improves performance when no variable is bound.

How does an oracle SQL statement reference a variable?

If you connect the table to an alias where your global variables come from, won't you be able to reference it?
In addition, it is best to use datediff to get the date in where.