[20180625]函数与标量子查询13(补充).txt

--//最近一段时间一直在测试标量子查询视buckets的数量,我前面的测试方法纯粹蛮力测试.

--//参考链接:

1.环境:

SYS@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ---------------------------------------------------------------- x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

grant execute on sys.dbms_lock to scott;

CREATE OR REPLACE FUNCTION sleep1 (seconds IN NUMBER)

RETURN NUMBER is d_date date; BEGIN   select sysdate into d_date from dual;   sys.dbms_lock.sleep(seconds/10);   RETURN seconds; END; /

CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)

RETURN NUMBER is d_date date; BEGIN   select sysdate into d_date from dual; --//sys.dbms_lock.sleep(0.01);   RETURN seconds; END; /

create table t as select rownum id1,mod(rownum-1,10000)+1 id2 from dual connect by level<=20000;

--//分析表略. 前面测试level<=40000;实际上level<=20000足够了.

2.测试:

--//我前面测试生成的脚本太不好了. set term off exec :x := 1; select t.*,(select sleep(id2) from dual) s from t where id2<=:x; set term on --//我开始以为这样spool不会输出执行结果,实际上还是写入spool指定的文件导致生成的文件很大.

--//我后来修改如下.

--//建立脚本by.txt: set term off alter session set statistics_level=all; variable x number; exec :x := &&1; select t.*,(select sleep(id2) from dual) s from t where id2<=:x; set term on @ &r/dpc '' '' quit

--//建立shell脚本by.sh:

#! /bin/bash # rm -f ez.txt for i in $(seq 10000) do     echo $i     sqlplus -s -l scott/book @by.txt $i >> ez.txt done

--//这样执行脚本就ok了.其他步骤略.

--//实际上这样不断调用sqlplus也就是要不断的登陆与退出,我的测试还是有点慢. --//再修改如下:

--//建立脚本by1.txt:

set term off exec :x := &&1; select t.*,(select sleep(id2) from dual) s from t where id2<=:x; set term on @ &r/dpc '' ''

--//建立by2.txt脚本

--//$ seq 10000 | xargs -I{} echo @by1.txt {}  > by2.txt --//这步可以省略.

--//建立shell脚本by1.sh:

#! /bin/bash sqlplus -s -l scott/book <<EOF >> ez.txt alter session set statistics_level=all; variable x number; $(seq 10000 | xargs -I{} echo @by1.txt {}) quit EOF

--//其中`cat by2.txt`也可以写成$(cat by2.txt).

3.顺便补充一下生成文本可以跳过中间文件生成直接执行如下:

#$ egrep 'FAST DUAL' ez.txt  | cut -f5 -d"|" > e2.txt #$ egrep 'SELECT STATEMENT' ez.txt | cut -f10 -d"|"  > e3.txt #$ paste e2.txt e3.txt -d"," > e4.txt

$ paste <(egrep 'FAST DUAL' ez.txt  | cut -f5 -d"|") <(egrep 'SELECT STATEMENT' ez.txt | cut -f10 -d"|" ) -d","