[20130622]Creating Test Scripts With Bind Variables.txt

[20130622]Creating Test Scripts With Bind Variables.txt

我们程序需要大量优化,而程序没有存在大量使用非绑定变量,我修改了回话参数cursor_sharing=force,
这样要优化这些语句非常麻烦,一些参数要通过视图获取v$sql_bind_capture.但是每次带入非常麻烦,特别是第一次.

我很早就知道有这么一个脚本可以生成sql语句的执行脚本,链接如下:

http://kerryosborne.oracle-guy.com/2009/07/creating-test-scripts-with-bind-variables/

http://kerryosborne.oracle-guy.com/scripts/build_bind_vars2.sql
http://kerryosborne.oracle-guy.com/scripts/build_bind_vars.sql

--首先我感觉build_bind_vars.sql不是很好用.而且还需要建立display_raw函数.而使用build_bind_vars2.sql不需要display_raw函数.
--但是在具体使用中我遇到1个问题date类型,sqlplus不支持如下:

SQL> var a1 date
Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
                    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]

--要使用脚本正确执行,必须带入字符串.而且测试发现如果是 日期='2013/06/13 10:00:00',不会出现隐式转换,只要定义好有关日期的
--环境变量:我一般定义如下,这样像日期='2013/06/13 10:00:00'基本没有问题.

--export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
--export NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF'
--export NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF'

我以build_bind_vars2.sql为蓝本,修改如下:

-------------------------------------------------------------------------------------------------------
--
-- 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/
-- add date convert varchar2(32)
-------------------------------------------------------------------------------------------------------
--
set verify off
set sqlblanklines on
set trimspool on
set trimout on
set feedback off;
set linesize 255;
set pagesize 50000;
set timing off;
set head off
--
accept sql_id char prompt "Enter SQL ID ==> "
accept child_no char prompt "Enter Child Number ==> " default 0
var isdigits number
--
--
col sql_fulltext for a140 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 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)','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 value_string 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 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');
--
spool off;
undef sql_id
undef child_no
set feedback on;
set head on

具体使用如下,执行它@build_bind_vars3,输入sql_id,chile_number不输入默认为0:

SQL> @build_bind_vars3
Enter SQL ID ==> 5qbknrugyjyhc
Enter Child Number ==>

--在屏幕输出的同时,在执行的目录下生成一个5qbknrugyjyhc.sql文件,一般多数情况下可以执行的.
--我的建议最好看看生成的sql文件,避免执行的语句是DML类的语句.有些时候可能要做小量修改.
--比如在cursor_sharing=force情况下,如果执行select 1 ,a,b from t where a=:x;会转换为select  :"SYS_B_0" ,a,b from t where a=:x;
--而:"SYS_B_0"的不在where里面,无法抓取,修改一下就可以了.