[20180625]函数与标量子查询13(补充).txt
--//最近一段时间一直在测试标量子查询视buckets的数量,我前面的测试方法纯粹蛮力测试.
--//参考链接:http://blog.itpub.net/267265/viewspace-2156082/
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","
