看了白鳝的一片关于v$sql 中bind_data的测试,俺也学习了一下
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> alter system flush shared_pool;
System altered.
SQL> var v1 varchar2(2);
SQL> exec :v1:='a';
PL/SQL procedure successfully completed.
SQL> select * from test11 where id=:v1;
ID ID1
-- ----------
ID3
---------------------------------------------------------------------------
a 1
11-APR-12 06.34.53.000000 PM
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9g1kvnud8041t, child number 0
-------------------------------------
select * from test11 where id=:v1
Plan hash value: 1550834917
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| TEST11 | 1 | 29 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST11@SEL$1
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST11"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------
1 - :V1 (VARCHAR2(30), CSID=852): 'a'--------初次绑定的值
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:V1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - "ID"[VARCHAR2,2], "TEST11"."ID1"[NUMBER,22],
"TEST11"."ID3"[TIMESTAMP,11]
Note
-----
- dynamic sampling used for this statement (level=2)
52 rows selected.
SQL> select dbms_sqltune.extract_binds(bind_data) bind from v$sql WHERE SQL_TEXT LIKE '%FROM TEST11%';
BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRE
--------------------------------------------------------------------------------
SQL_BIND_SET()
SQL> select dbms_sqltune.extract_binds(bind_data) bind from v$sql WHERE SQL_TEXT LIKE '%from test11%';
BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRE
--------------------------------------------------------------------------------
SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 852, NULL, NULL, 32, '11
-APR-12', 'a', ANYDATA()))
SQL_BIND_SET()
SQL_BIND_SET()
SQL> exec :v1:='aa';
PL/SQL procedure successfully completed.
SQL> select * from test11 where id=:v1;
ID ID1
-- ----------
ID3
---------------------------------------------------------------------------
aa 12
12-APR-01 12.00.00.000000 AM
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID gq117a08wfuy0, child number 0
-------------------------------------
select * from test11 where id=:v1
Plan hash value: 1550834917
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| TEST11 | 1 | 29 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST11@SEL$1
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST11"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------
1 - :V1 (VARCHAR2(30), CSID=852): 'aa'------------------最近一次绑定的值
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:V1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - "ID"[VARCHAR2,2], "TEST11"."ID1"[NUMBER,22],
"TEST11"."ID3"[TIMESTAMP,11]
Note
-----
- dynamic sampling used for this statement (level=2)
52 rows selected.
SQL> select dbms_sqltune.extract_binds(bind_data) bind from v$sql WHERE SQL_TEXT LIKE '%from test11%';
BIND(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRE
--------------------------------------------------------------------------------
SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 852, NULL, NULL, 32, '11
-APR-12', 'a', ANYDATA()))
SQL_BIND_SET()
SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 1, 'VARCHAR2(32)', 852, NULL, NULL, 32, '11
-APR-12', 'aa', ANYDATA()))
SQL_BIND_SET()
把最近几次的值全列出来
结果是跟白鳝的是不一样的,可能是版本不通造成的
分享到:
相关推荐
作用:DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息. DBMS_STATS.GATHER_TABLE_STATS的语法如下: DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_...
DBMS_XPLAN是Oracle提供的一个用于查看SQL计划,包括执行计划和解释计划的包;在以前查看SQL执行计划的时候,我都是使用set autotrace命令,不过现在看来,DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。 这5...
NULL 博文链接:https://duqiangcise.iteye.com/blog/648491
Oracle 18c bug 执行 DBMS_PDB.CHECK_PLUG_COMPATIBILITY报错_ITPUB博客.mhtml
oracle dbms_lob
NULL 博文链接:https://huanyue.iteye.com/blog/2095594
--dbms_sql.bind_variable(l_cursor,':ename',l_ename); --5.执行 l_retval := dbms_sql.execute(l_cursor); --6.取数 --6.1取列名 for i in 1..l_colcnt loop dbms_output.put(l_col_tab(i).col_name); dbms_...
这篇文档整合了热门的oracle DBA面试问题 一. SQL tuning 类 1:列举几种表连接方式 hash join/merge join/nest loop(cluster ... SQL>SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
支持ArcGIS10.2版本的PostgreSQL_DBMS_for_windows_922,ESRI官方原版资源。
dbms_comp_advisor.getratio 预估压缩比例的存储过程脚本 11gR2以前使用,11gR2后可以使用系统自带的存储。
以下是对Oracle中dbms_metadata.get_ddl的用法进行了详细的分析介绍,需要的朋友参考下
Oracle SQL执行计划分析器...(View Create Script v1.0.sql) 如果出现没权限访问GV$SQL_PLAN_MONITOR这些对象,请先用sys用户赋值。 3 最后,编译XYG_ALD_SESS_PKG的包体。完工! (XYG_ALD_SESS_PKG_BODY.sql)
亲测有效 通过此版本可以把oracle时区版本调整到为最新版本,一般配合时区补丁使用 可以参考...脚本里有说明 先运行upg_tzv_check.sql再运行upg_tzv_apply.sql
Oracle统计分析-dbms_stats.pdf
FROM v$logmnr_contents where SQL_REDO like '%T1%'; 分析的结果其它会话无法访问 用以下方法转储: create table logmnr3 as select * from GV$LOGMNR_CONTENTS; 结束分析 EXECUTE DBMS_LOGMNR.END_...
oracle dbms_sql的使用方法,非常使用
简单的dbms_stats操作,简单的dbms_stats操作简单的dbms_stats操作
使用C语言实现的数据库管理系统。 支持简单类 SQL语言。
How to Move from ANALYZE (using DBMS_UTILITY) to DBMS_STATS Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY SYS)?