[20150114]Creating Test Scripts With Bind Variables.txt
--昨天在优化一条sql语句,我发现我的测试与实际的存在很大的差异.我使用链接的脚本生成sql执行脚本,但是我发现我执行计划的信息很奇怪.
http://blog.itpub.net/267265/viewspace-764543/
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 250K(100)| 0 |00:00:03.21 | 832K| | | |
|* 1 | TABLE ACCESS BY INDEX ROWID | YS_MZ_JBZD | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 | | | |
|* 2 | INDEX UNIQUE SCAN | PK_YS_MZ_JBZD | 0 | 1 | 2 (0)| 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 3 | SORT ORDER BY | | 1 | 1 | 250K (1)| 0 |00:00:03.21 | 832K| 1024 | 1024 | |
|* 4 | HASH JOIN | | 1 | 1 | 250K (1)| 0 |00:00:03.21 | 832K| 16M| 4385K| 15M (0)|
| 5 | JOIN FILTER CREATE | :BF0000 | 1 | 115K| 215K (1)| 61274 |00:00:02.34 | 755K| | | |
|* 6 | HASH JOIN | | 1 | 115K| 215K (1)| 61274 |00:00:02.32 | 755K| 14M| 4512K| 13M (0)|
| 7 | JOIN FILTER CREATE | :BF0001 | 1 | 115K| 188K (1)| 63207 |00:00:01.55 | 695K| | | |
|* 8 | TABLE ACCESS STORAGE FULL| MS_CF01 | 1 | 115K| 188K (1)| 63207 |00:00:01.54 | 695K| 1025K| 1025K| 3085K (0)|
| 9 | JOIN FILTER USE | :BF0001 | 1 | 3561K| 17505 (1)| 215K|00:00:00.63 | 60201 | | | |
|* 10 | TABLE ACCESS STORAGE FULL| MS_MZXX | 1 | 3561K| 17505 (1)| 215K|00:00:00.61 | 60201 | 1025K| 1025K| |
| 11 | JOIN FILTER USE | :BF0000 | 1 | 3922K| 21988 (1)| 168K|00:00:00.73 | 77353 | | | |
|* 12 | TABLE ACCESS STORAGE FULL | MS_BRDA | 1 | 3922K| 21988 (1)| 168K|00:00:00.72 | 77353 | 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------------------------------
--实际的A-Rows都是0.而实际我直接带入参数执行是有结果的.花了一点时间研究,才发现我以前写的脚本存在问题,从V$SQL_BIND_CAPTURE里面的char类型的参数存在
--问题.
--它里面有:as_mzhm = :"SYS_B_06" 条件,而as_mzhm的类型是char(32),而SYS_B_06的类型varchar2(本来在是字符常量,因为修改cursor_sharing=force),很奇怪
--sql能正常执行.改成varchar2类型后,可以发现执行正确了.A-Rows=397
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 250K(100)| 397 |00:00:03.34 | 832K| | | |
|* 1 | TABLE ACCESS BY INDEX ROWID | YS_MZ_JBZD | 328 | 1 | 3 (0)| 0 |00:00:00.01 | 14 | | | |
|* 2 | INDEX UNIQUE SCAN | PK_YS_MZ_JBZD | 328 | 1 | 2 (0)| 0 |00:00:00.01 | 14 | 1025K| 1025K| |
| 3 | SORT ORDER BY | | 1 | 1 | 250K (1)| 397 |00:00:03.34 | 832K| 214K| 214K| 190K (0)|
|* 4 | HASH JOIN | | 1 | 1 | 250K (1)| 397 |00:00:03.34 | 832K| 16M| 4382K| 16M (0)|
| 5 | JOIN FILTER CREATE | :BF0000 | 1 | 115K| 215K (1)| 63062 |00:00:02.38 | 755K| | | |
|* 6 | HASH JOIN | | 1 | 115K| 215K (1)| 63062 |00:00:02.36 | 755K| 14M| 4512K| 13M (0)|
| 7 | JOIN FILTER CREATE | :BF0001 | 1 | 115K| 188K (1)| 63409 |00:00:01.52 | 695K| | | |
|* 8 | TABLE ACCESS STORAGE FULL| MS_CF01 | 1 | 115K| 188K (1)| 63409 |00:00:01.50 | 695K| 1025K| 1025K| |
| 9 | JOIN FILTER USE | :BF0001 | 1 | 3561K| 17505 (1)| 218K|00:00:00.67 | 60200 | | | |
|* 10 | TABLE ACCESS STORAGE FULL| MS_MZXX | 1 | 3561K| 17505 (1)| 218K|00:00:00.65 | 60200 | 1025K| 1025K| |
| 11 | JOIN FILTER USE | :BF0000 | 1 | 3922K| 21988 (1)| 173K|00:00:00.77 | 77352 | | | |
|* 12 | TABLE ACCESS STORAGE FULL | MS_BRDA | 1 | 3922K| 21988 (1)| 173K|00:00:00.75 | 77352 | 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------------------------------
--做一个例子来说明:
SCOTT@test> variable x CHAR(32)
SCOTT@test> exec :x := 'a';
PL/SQL procedure successfully completed.
SCOTT@test> alter session set cursor_sharing=force ;
Session altered.
SCOTT@test> select * from Emp where :x = 'a' ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
...
14 rows selected.
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
---------------------------------------
SQL_ID 8zgbkfksu4nfp, child number 0
-------------------------------------
select * from Emp where :x = :"SYS_B_0"
SCOTT@test> @bind_cap 8zgbkfksu4nfp
C200
---------------------------------------
select * from Emp where :x = :"SYS_B_0"
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STR VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- ------------ ---------------
8zgbkfksu4nfp 0 YES :X 1 32 2015-01-14 11:32:59 CHAR(32) a
YES :SYS_B_0 2 32 2015-01-14 11:32:59 VARCHAR2(32) a
SCOTT@test> @build_bind_vars3.sql 8zgbkfksu4nfp
Enter SQL ID ==>
Enter Child Number ==>
variable X CHAR(32)
variable SYS_B_0 VARCHAR2(32)
begin
:X := 'a ';
:SYS_B_0 := 'a';
end;
/
set termout off
alter session set current_schema=SCOTT;
alter session set statistics_level=all;
select /* test 8zgbkfksu4nfp */ /*+ gather_plan_statistics */ * from Emp where :x = :"SYS_B_0" ;
set termout on
@dpc '' ''
rollback;
--这样生成的脚本sql语句没有输出.修改如下:
variable X VARCHAR2(32)
variable SYS_B_0 VARCHAR2(32)
begin
:X := 'a ';
:SYS_B_0 := 'a';
end;
/
set termout off
alter session set current_schema=SCOTT;
alter session set statistics_level=all;
select /* test 8zgbkfksu4nfp */ /*+ gather_plan_statistics */ * from Emp where :x = :"SYS_B_0" ;
set termout on
@dpc '' ''
rollback;
--一样没有输出.除非把:X := 'a ';改成:X := 'a';
> SELECT name, datatype_string, value_string || 'a' c20 FROM V$SQL_BIND_CAPTURE WHERE sql_id = 'abwrcfvwk3g18' AND child_number = 0 AND datatype_string LIKE 'CHAR%';
NAME DATATYPE_STRING C20
-------------------- ------------------------------ --------------------
:AS_MZHM CHAR(32) -1a
:AS_MZHM CHAR(32) -1a
SCOTT@test> SELECT name, datatype_string, value_string || 'a' c50 FROM V$SQL_BIND_CAPTURE WHERE sql_id = '8zgbkfksu4nfp' AND child_number = 0 ;
NAME DATATYPE_STRING C50
-------------------- ------------------------------ --------------------------------------------------
:X CHAR(32) a a
:SYS_B_0 VARCHAR2(32) aa
--奇怪没有空格.不理这些.修改脚本如下:
-------------------------------------------------------------------------------------------------------
--
-- File name: build_bind_vars3.sql
--
-- Purpose: Build SQL*Plus test script with variable definitions
--
-- Author: Jack Augustin and Kerry Osborne
--
-- Description: This script creates a file which can be executed in SQL*Plus. It creates bind variables,
-- sets the bind variables to the values stored in V$SQL_BIND_CAPTURE, and then executes
-- the statement. The sql_id is used for the file name and is also placed in the statement
-- as a comment. Note that numeric bind variable names are not permited in SQL*Plus, so if
-- the statement has numberic bind variable names, they have an 'N' prepended to them. Also
-- note that CHAR variables are converted to VARCHAR2.
--
-- Usage: This scripts prompts for two values.
--
-- sql_id: this is the sql_id of the statement you want to duplicate
--
-- child_no: this is the child cursor number from v$sql
-- (the default is 0 second)
--
-- http://kerryosborne.oracle-guy.com/2009/07/creating-test-scripts-with-bind-variables/
-- modify : add date convert varchar2(32) 20130622
-- modify : bug replace char to varchar2(32) 20150114
-- modify : replace sql_fulltext chr(13) to '' 20150114
-------------------------------------------------------------------------------------------------------
--
set verify off
set sqlblanklines on
set trimspool on
set trimout on
set feedback off;
set linesize 4000;
set pagesize 50000;
set timing off;
set head off
--
accept sql_id char prompt "Enter SQL ID ==> " default &1
accept child_no char prompt "Enter Child Number ==> " default 0
var isdigits number
var v_sql_fulltext clob
--
--
col sql_fulltext for a4000 word_wrap
spool &&sql_id\.sql
--
--Check for numeric bind variable names
--
begin
select case regexp_substr(replace(name,':',''),'[[:digit:]]') when replace(name,':','') then 1 end into :isdigits
from
V$SQL_BIND_CAPTURE
where
sql_id='&&sql_id'
and child_number = &&child_no
and rownum < 2;
end;
/
--
-- Create variable statements
--
select
'variable ' ||
case :isdigits when 1 then replace(name,':','N') else substr(name,2,30) end || ' ' ||
decode(datatype_string,'DATE','VARCHAR2(32)',replace(datatype_string,'CHAR(','VARCHAR2(')) txt
--decode(datatype_string, 'DATE','VARCHAR2(32)','CHAR(', 'VARCHAR2(',datatype_string) txt
--replace(datatype_string,'CHAR(','VARCHAR2(') txt
from
V$SQL_BIND_CAPTURE
where
sql_id='&&sql_id'
and child_number = &&child_no;
--
-- Set variable values from V$SQL_BIND_CAPTURE
--
select 'begin' txt from dual;
select
case :isdigits when 1 then replace(name,':',':N') else name end ||
' := ' ||
case datatype_string when 'NUMBER' then null else '''' end ||
case datatype_string when 'DATE' then to_char(to_date(value_string,'mm/dd/yy hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss') else nvl(value_string,'00') end ||
case datatype_string when 'NUMBER' then null else '''' end ||
';' txt
from
V$SQL_BIND_CAPTURE
where
sql_id='&&sql_id'
and child_number = &&child_no;
select 'end;' txt from dual;
select '/' txt from dual;
--
-- Generate statement
--
select 'set termout off' txt from dual;
select 'alter session set current_schema='||PARSING_SCHEMA_NAME||';' txt from v$sqlarea where sql_id = '&&sql_id';
select 'alter session set statistics_level=all;' from dual;
select ' ' from dual;
select replace(sql_fulltext,chr(13),'') from (
select regexp_replace(sql_fulltext,'(select |SELECT )','select /* test &&sql_id */ /*+ gather_plan_statistics */ ',1,1) sql_fulltext from (
select case :isdigits when 1 then replace(sql_fulltext,':',':N') else sql_fulltext end ||';' sql_fulltext
from v$sqlarea
where sql_id = '&&sql_id'));
--
select 'set termout on' txt from dual;
select '@dpc '''' ''''' txt from dual;
select 'rollback;' txt from dual;
spool off;
undef sql_id
undef child_no
set feedback on;
set head on
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1401633/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-1401633/
扫一扫
1080
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
