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