How to obtain the variable value bound to an OracleSQL statement

Source: Internet
Author: User
When diagnosing SQL Performance problems, we sometimes need to obtain the actual value of the variable bound to it, and then bring the actual value into the SQL statement, using the original SQL to form the select statement (

When diagnosing SQL Performance problems, we sometimes need to obtain the actual value of the variable bound to it, and then bring the actual value into the SQL statement, using the original SQL to form the select statement (

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 and 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 400

Col SQL _Id format a20

Col name format a20

Col datatype_string format a14

Col value_string format a20

-- This SQL statement reads the bound variable value information from the memory. If it is not in the memory, the next SQL statement is used.

Select SQL _id, name, datatype_string, last_captured, value_string from v $ SQL _bind_capture where SQL _id = 'dxfcacn4t4ppw' order by LAST_CAPTURED, POSITION;

-- This SQL statement reads the bound 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;

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.