系統模擬程式碼

第五章實驗結果與分析

第五節查詢回應時間

B. 系統模擬程式碼

圖A-1資料庫效能監控系統架構圖

/usr/local/bin/php 7/horizontal_model.php &

/usr/local/bin/php 8/horizontal_model.php &

/usr/local/bin/php 9/horizontal_model.php &

/usr/local/bin/php 10/horizontal_model.php &

/usr/local/bin/php 11/horizontal_model.php &

/usr/local/bin/php 12/horizontal_model.php &

/usr/local/bin/php 13/horizontal_model.php &

/usr/local/bin/php 14/horizontal_model.php &

/usr/local/bin/php 15/horizontal_model.php &

/usr/local/bin/php 16/horizontal_model.php &

/usr/local/bin/php 17/horizontal_model.php &

/usr/local/bin/php 18/horizontal_model.php &

/usr/local/bin/php 19/horizontal_model.php &

/usr/local/bin/php 20/horizontal_model.php &

/usr/local/bin/php 21/horizontal_model.php &

/usr/local/bin/php 22/horizontal_model.php &

/usr/local/bin/php 23/horizontal_model.php &

/usr/local/bin/php 24/horizontal_model.php &

表 b-2 h/ makestarttime.php

程式名稱作用觸發時機與次數

h/ makestarttime.php 決定統一一起應用的時間一次

程式碼 <?php

$File = "starttime";

$Handle = fopen($File, 'w');

$Data=date("Y-m-d H:i:s",time()+ 10*60);

fwrite($Handle, $Data);

fclose($Handle);

表 b-3 h/ oracle_delete.php

程式名稱作用觸發時機與次數

h/ oracle_delete.php 正式刪除 2008/06/26 testresult 的資料一次

程式碼 <?php

$pconn=oci_connect("model1","**********","oracleserver");

$pstid=oci_parse($pconn,"delete from testresult where to_char(cdt,'yyyy/mm/dd')='2008/06/26'");

exec("rm -f elapsed_delete.txt");

$time_start = microtime(true);

oci_execute($pstid);

$time_end = microtime(true);

$time = $time_end - $time_start;

$time=sprintf("%9.2f",$time)."\n";

$File = "elapsed_delete.txt";

$Handle = fopen($File, 'a+');

fwrite($Handle, $time);

fclose($Handle);

表 b-4 h/insert/horizontal_model.php

程式名稱作用觸發時機與次數

h/insert/

horizontal_model.php

控制新增 2008/6/26 資料時間控制檔一次

程式碼 <?php

echo __FILE__." parameters random makeing...\n";

$starttime = file_exists('starttime') ? exec("/bin/cat starttime") : date("Y-m-d H:i:s",time()+ 3*60) ;

$pqdoendtime=date("Y-m-d H:i:s");

echo __FILE__." run query start at $starttime\n";

while (date("Y-m-d H:i:s") < $starttime) { }

file_exists('exectime.php') ? include ("exectime.php") :

$endtime=date("Y-m-d H:i:s",time()+ 2*60) ; echo "Run from $starttime to $endtime \n";

exec("rm -f elapsed_insert.txt");

$query_count=0;

$sleep=0;

while ($sleep < $endtime) {

while(date("Y-m-d H:i:s")<$sleep){}

$sleep=date("Y-m-d H:i:s",time()+60);

include "oracle_insert.php";

$query_count++;

}

$qdoendtime=date("Y-m-d H:i:s");

$File = basename(__FILE__)."3.result";

$Handle = fopen($File, 'w');

$Data=__FILE__." PreQuery end $pqdoendtime, from $starttime to $endtime but stop $qdoendtime had $query_count query\n";

fwrite($Handle, $Data);

fclose($Handle);

表 b-5 h/insert/oracle_insert.php

程式名稱作用觸發時機與次數

h/insert/

oracle_insert.php

新增 2008/06/26 的資料到資料庫每分鐘

程式碼 <?php

$insert_date="'".date('Y/m/d H:i',mktime(0,$query_count,0,6,26,2008))."'";

$conn=oci_connect("modle-1","**********","remoteoracle");

$stid=oci_parse($conn,"

Select uid_, pcbano, wono, teststationid, line, testprgid, testcount, tester, DURATION, result,

to_char(cdt,'yyyy/mm/dd hh24:mi:ss') as date_result from TestResult where to_char(cdt,'yyyy/mm/dd hh24:mi')=$insert_date

");

oci_execute($stid);

echo "Insert $insert_date record \n";

while (($results = oci_fetch_array($stid, OCI_BOTH))) {

$conn_2=oci_connect("model1","oracle0211","oracleserver");

$stid_2=oci_parse($conn_2,"

insert into testresult (UID_, PCBANO, WONO, TESTSTATIONID, LINE, TESTPRGID, TESTCOUNT, TESTER, DURATION, RESULT,CDT) values

(:query_0,:query_1,:query_2,:query_3,:query_4,:query_5,:query_6,:query_7,:q uery_8,:query_9,

to_date(:query_10,'yyyy/mm/dd hh24:mi:ss'))

");

oci_bind_by_name($stid_2, ":query_0", $results[0]);

oci_bind_by_name($stid_2, ":query_1", $results[1]);

oci_bind_by_name($stid_2, ":query_2", $results[2]);

oci_bind_by_name($stid_2, ":query_3", $results[3]);

oci_bind_by_name($stid_2, ":query_4", $results[4]);

oci_bind_by_name($stid_2, ":query_5", $results[5]);

oci_bind_by_name($stid_2, ":query_6", $results[6]);

oci_bind_by_name($stid_2, ":query_7", $results[7]);

oci_bind_by_name($stid_2, ":query_8", $results[8]);

oci_bind_by_name($stid_2, ":query_9", $results[9]);

oci_bind_by_name($stid_2, ":query_10", $results[10]);

$time_start = microtime(true);

oci_execute($stid_2);

$time_end = microtime(true);

$time = $time_end - $time_start;

$time=sprintf("%9.2f",$time)."\n";

$File = "elapsed_insert.txt";

$Handle = fopen($File, 'a+');

fwrite($Handle, $time);

fclose($Handle);

}

表 b-6 h/1/random_parameters.php

程式名稱作用觸發時機與次數

h/1/random_parameters.php 正式應用前的 where 條件查詢一次

程式碼 <?php

$pconn=oci_connect("modle-1","**********","remoteoracle");

$pstid=oci_parse($pconn,"

select wono from (

Select a.wono, (DefectQty*1.0/InputQty*1.0) from (

Select WoNo,count(distinct PcbaNo) as InputQty from v_TestRec_AS

group by WoNo

)a join (

Select WoNo,count(distinct PcbaNo) as DefectQty from v_TestRec_AS

where Result=0 group by WoNo )b on a.WoNo=b.WoNo

ORDER BY dbms_random.value ) WHERE rownum < 4096

");

oci_execute($pstid);

$pnrows=oci_fetch_all($pstid,$presults);

表 b-7 h/1/horizontal_model.php

程式名稱作用觸發時機與次數

h/1/horizontal_model.php 正式應用的時間控制 28800 秒/次

程式碼 <?php

echo __FILE__." parameters random makeing...\n";

$starttime = file_exists('starttime') ? exec("/bin/cat starttime") : date("Y-m-d H:i:s",time()+ 3*60) ;

include "random_parameters.php";

$pqdoendtime=date("Y-m-d H:i:s");

echo __FILE__." run query start at $starttime\n";

while (date("Y-m-d H:i:s") < $starttime) { }

file_exists('exectime.php') ? include ("exectime.php") :

$endtime=date("Y-m-d H:i:s",time()+ 2*60) ; echo "Run from $starttime to $endtime \n";

$query_count=0;

$sleep=date("Y-m-d H:i:s",time()+28800);

exec("rm -f elapsed1.txt");

$conn=oci_connect("model1","oracle0211","oracleserver");

while (date("Y-m-d H:i:s") < $endtime) {

while(date("Y-m-d H:i:s")<$sleep){}

$sleep=date("Y-m-d H:i:s",time()+28800);

include "oracle_query.php";

echo

"---\n";

$query_count++;

}

$qdoendtime=date("Y-m-d H:i:s");

$File = basename(__FILE__)."1.result";

$Handle = fopen($File, 'w');

$Data=__FILE__." PreQuery end $pqdoendtime, from $starttime to

$endtime but stop $qdoendtime had $query_count query\n";

fwrite($Handle, $Data);

fclose($Handle);

表 b-8 h/1/exponential.php

程式名稱作用觸發時機與次數

h/1/exponential.php 離散函數隨機

程式碼 <?php

$x=-7*log((rand(1,1000)/1000));

sleep($x); ?>

表 b-9 h/4/horizontal_model.php

程式名稱作用觸發時機與次數

h/4/horizontal_model.php 新增 2008/06/26 的資料隨機

程式碼 <?php

echo __FILE__." parameters random makeing...\n";

$starttime = file_exists('starttime') ? exec("/bin/cat starttime") : date("Y-m-d H:i:s",time()+ 3*60) ;

include "random_parameters.php";

$pqdoendtime=date("Y-m-d H:i:s");

echo __FILE__." run query start at $starttime\n";

while (date("Y-m-d H:i:s") < $starttime) { }

file_exists('exectime.php') ? include ("exectime.php") :

$endtime=date("Y-m-d H:i:s",time()+ 2*60) ; echo "Run from $starttime to $endtime \n";

$query_count=0;

exec("rm -f elapsed4.txt");

$conn=oci_connect("model1","oracle0211","oracleserver");

while (date("Y-m-d H:i:s") < $endtime) {

include "exponential.php";

include "oracle_query.php";

echo

"---\n";

$query_count++;

}

$qdoendtime=date("Y-m-d H:i:s");

$File = basename(__FILE__)."4.result";

$Handle = fopen($File, 'w');

$Data=__FILE__." PreQuery end $pqdoendtime, from $starttime to $endtime but stop $qdoendtime had $query_count query\n";

fwrite($Handle, $Data);

fclose($Handle);

表 b-10 h/1/oracle_query.php

程式名稱作用觸發時機與次數

h/1/oracle_query.php 對資料庫正式的應用每分鐘

程式碼 <?php

$rdata=rand(0,sizeof($presults["WONO"])-1);

$query_p=$presults["WONO"][$rdata];

$stid=oci_parse($conn,"

Select (DefectQty*1.0/InputQty*1.0) as result from (

Select WoNo,count(distinct PcbaNo) as InputQty from v_TestRec_AS

where WoNo=:query_p group by WoNo )a join (

Select WoNo,count(distinct PcbaNo) as DefectQty

from v_TestRec_AS where WoNo=:query_p and Result=0

group by WoNo )b on a.WoNo=b.WoNo

");

oci_bind_by_name($stid, ":query_p", $query_p);

$time_start = microtime(true);

oci_execute($stid);

$time_end = microtime(true);

$time = $time_end - $time_start;

$time=sprintf("%9.2f",$time)."\n";

$File = "elapsed1.txt";

$Handle = fopen($File, 'a+');

fwrite($Handle, $time);

fclose($Handle);

$nrows=oci_fetch_all($stid,$results);

for ($i=0 ; $i < $nrows ; $i++) {

echo $results["RESULT"][$i]."\n";

}

在文檔中中華大學 (頁 66-74)

第五章 實驗結果與分析

第五節 查詢回應時間

B. 系統模擬程式碼

第五章實驗結果與分析

第五節查詢回應時間