1. 首页
  2. IT资讯

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

[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 😡 := 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 😡 := &&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 😡 := &&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","

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2156594/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/182842.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code