ANALYZE INDEX pk_mytable ESTIMATE STATISTICS SAMPLE 5 PERCENT
This was good enough to change performance. It took 3 seconds
Thursday, July 7, 2011
analyze 5 percent
ANALYZE INDEX pk_mytable ESTIMATE STATISTICS SAMPLE 5 PERCENT
This was good enough to change performance.
This was good enough to change performance.
Wednesday, June 22, 2011
Documentation. - Program Spec: My ugly way and why do I make it.
Non of programmer likes documentation over actual programming. I haven't seen one yet. But he/she should have ability to create one in easy to read format. When things so complicated, or need detailed detailed confirmation from power user group. You need to some way to share current status of system. Like 10 year old program got some requirement from user. MS visio is a good tool, but often times, user and developer himself doesn't have a copy of it. So I prefer use Powerpointer. Specs one goad is sharing, sometimes you have to show in projector.
Below is very little part of program and very part of drawing of spec.
Text format documentation is good way. Some time drawing reduces words.
This Pro*C program sample : Of cause , I had to change wording.
As you can see, above long ugly coding can be shown user now. Even though it's still ugly, it became readable.
This much detailed discussion doesn't happen often. But when I have logic critical process and user never / won't keep their documentation, I do keep some written like this get sign off.
Below is very little part of program and very part of drawing of spec.
Text format documentation is good way. Some time drawing reduces words.
This Pro*C program sample : Of cause , I had to change wording.
/* 3/29 OOW : Oh Oo Wow you don't need know what is this. It just trying to show part of source code*/
if (0 == strcmp((char *)o_iwow ,"OOW")){
MemSet( ls_tmp );
EXEC SQL
SELECT 'Y' into :ls_tmp
FROM T_MODEL_CHECK_TBL
WHERE model = :i_model
and chk_type = 'OWRJT';
if (SQLCODE != NOTFOUND && SQLCODE != SQLOK ) {
snprintf((char *)o_Msg , 100, "ERR:OWRJT check [%s][%s]", (char *)i_serial_no , i_model );
ret_code = 2;
goto LB_CUSTERROR;
}
if (0 == strcmp((char *)ls_tmp ,"Y")){
snprintf((char *)o_Msg , 100, "[OWRJT]OW Receive as reject phone[%s]", (char *)i_model );
ret_code = 2;
goto LB_CUSTERROR;
}
}
/* Certain Customer */
if (0 == strcmp((char *)i_customer , "xxxx")) {
MemSet(o_err ); MemSet(o_err1 );
EXEC SQL
SELECT CASE WHEN (TRUNC(sysdate) - TRUNC( repr_ship_date ) <= 90) THEN 'Y' ELSE 'N' END in_90, CASE WHEN repr_ship_date is null THEN 'NEW' WHEN (TRUNC(sysdate) - TRUNC( repr_ship_date ) >= 16)
THEN 'Y' ELSE 'N' END ov_30
INTO :o_err , :o_err1
FROM T_TRANSACTION_MASTER
WHERE serial_key =:i_serial_no ;
if ( SQLOK != SQLCODE ) {
faile_log(CRITICAL, "[%s]: NO 3rd Strike failed!:[%d][%s]\n",SERVICE_NAME,SQLCODE,SQLMSG);
ret_code = -1;
goto LB_DMLERROR;}
/* 30 day block: Not receive for */
if ( (0 == strcmp((char *)o_err1 , "N" ))&&
( 0 != strcmp((char *)i_reason , "973")) &&
( 0 != strcmp((char *)i_reason , "975")) ){
snprintf((char *)o_Msg , 100, "Returned in 30 days. Would you receive as reject phone?");
ret_code = 2;
goto LB_CUSTERROR;
}
}
LB_CUSTERROR :
SENDVALUE( ret_code , 0);
SENDVALUE( o_Msg , 0);
EXEC SQL ROLLBACK WORK;
faile_log(NORMAL, "[%s]: The SERVICE is terminated with Erro message--\n",SRC_NAME);
exit_system( );
As you can see, above long ugly coding can be shown user now. Even though it's still ugly, it became readable.
This much detailed discussion doesn't happen often. But when I have logic critical process and user never / won't keep their documentation, I do keep some written like this get sign off.
Tuesday, June 21, 2011
Big one SQL to broken down small sqls by PL/SQL - Using global temp table
This program creates new data called T_REPAIR_REPEAT
it containes pretty challanging logic behind.
It has company , model, date and each days
comp_cd, mat , product_gi_dt, total repair count, total REPEAT count : REPEAT means repeated repair in 90 days.
When each phone determined as REPEAT, next fields cound added.
Same fail means if same fail code exists it determined as same fault.
Fail 2 fail : you have to know what's fail or Ntf first.
below some master table (tb_df_repr) contains a column "repeat_count_yn"
if this marked 'Y' and one ESN's repair record includes this type of fail code, ESN considered as FAIL, if not contains it considered as 'NTF'.
So program has to judge this times NTF, fail and last repairs NTF, fail.
SAME_FAIL here means it just checks if any fail code marked repeat_count_yn 'Y' exists prior repair and this time also.
Those pretty complicate logic check can create huge amount of data inquery for each decision.
Here I chose to use global temporary table. TT_DUM_TEMP is global temporary table, means the data will be cleared when commit.
So I don't have to worry insert and delete for later use.
Whenever ESN judges as REPEAT, program saves very simple dataset for PGI date and repeat_count_yn 'Y' marked fail code.
Then program has countable amount of data, user might enter ten twenty of fail code for each ESN, but that's super minimal amount of data.
And also it screens only repeat_count_yn 'Y' marked fail code marked data, so there will be half or lesser faild code entered.
This program got huge benefit of using global temp table. Of couse these can be done by using struture or whever.
But I'm old school, I can use table very comfortably then structures. And do they have automated clearing feature?
And as you can see, judging REPEAT logic only have dedicated REPEAT or not checking.
Imagine whole this logic into one SQL statement, I know it can be done, but that will be readable and will be able to debug and fix easily.
That's one of largest benefit using PL/SQL over one giant SQL statement, I believe as developer.
PROCEDURE PR_DAILY_REPEAT (
i_product_gi_dt IN VARCHAR2,
o_msg OUT VARCHAR2
) IS
/*
2011 3 15 Billy Hong New development :
*/
l_p_product_gi_dt T_BILL_MST.product_gi_dt%type;
l_p_ship_delvy_no T_BILL_MST.ship_delvy_no%type;
l_p_cust_cd T_BILL_MST.cust_cd%type;
l_REPEAT_yn varchar2(1) ;
l_prev_fail varchar2(1) ;
l_cur_fail varchar2(1) ;
l_same_fault_exists varchar2(1) ;
l_loc varchar2 (100);
BEGIN
o_msg := 'OK';
delete T_REPAIR_REPEAT
where product_gi_dt = i_product_gi_dt ;
commit;
FOR c_m IN(
Select A.comp_cd, a.cust_cd, A.product_gi_dt, A.ship_delvy_no,
A.recv_date, A.ESN , a.mat
From T_BILL_MST A
Where a.product_gi_dt = i_product_gi_dt
)
LOOP
BEGIN
l_loc := '000';
/* 3. REPEAT or not : GR base -90 if GI exist REPEAT !
find previous GR base 90 days get last only one
Total returns: REPEAT ! */
l_p_product_gi_dt := NULL;
l_REPEAT_yn := NULL;
l_p_ship_delvy_no := NULL;
l_loc := '111';
-- Possible Major delay here
begin
select product_gi_dt, 'Y', ship_delvy_no, a.cust_cd
into l_p_product_gi_dt, l_REPEAT_yn, l_p_ship_delvy_no, l_p_cust_cd
from T_BILL_MST a
where a.product_gi_dt >= to_char(c_m.recv_date -90, 'yyyymmdd')
and a.product_gi_dt < to_char(c_m.recv_date , 'yyyymmdd')
and a.esn = c_m.esn
and product_gi_dt = (select max(product_gi_dt) from T_BILL_MST
where esn = a.esn
and product_gi_dt >= to_char(c_m.recv_date -90, 'yyyymmdd')
and product_gi_dt < to_char(c_m.recv_date , 'yyyymmdd')) ;
exception when NO_DATA_FOUND THEN
-- set 0 for all values
l_REPEAT_yn :='N' ;
l_prev_fail :='N' ;
l_cur_fail :='N' ;
l_same_fault_exists :='N' ;
goto update_or_insert;
end;
l_loc := '222 TT_DUM_TEMP';
/* save current fault info */
/* global temp table delete on commit */
insert into TT_DUM_TEMP
(c_col1, c_col2 )
select c_m.product_gi_dt, df_cd
from T_BILL_REPR_TMP a, tb_df_repr b
where a.bill_date = c_m.product_gi_dt
and a.sold_to = c_m.cust_cd
and a.delvy_no = c_m.ship_delvy_no
and a.esn_no = c_m.esn
and a.df_cd = b.cd
and b.repeat_count_yn = 'Y'
and b.df_repr_flag = 'F' ;
l_loc := '333 ge DUM_TEMP';
/* get DF
Fail code repeat_count Y fail , else N */
select decode(count(1), 0, 'N','Y')
into l_cur_fail
from TT_DUM_TEMP
where c_col1 = c_m.product_gi_dt;
l_loc := '444 ins DUM_TEMP';
/* save previous fault info */
insert into TT_DUM_TEMP
(c_col1, c_col2 )
select l_p_product_gi_dt, df_cd
from T_BILL_REPR_TMP a, tb_df_repr b
where a.bill_date = l_p_product_gi_dt
and a.sold_to = l_p_cust_cd
and a.delvy_no = l_p_ship_delvy_no
and a.esn_no = c_m.esn
and a.df_cd = b.cd
and b.repeat_count_yn = 'Y'
and b.df_repr_flag = 'F' ;
l_loc := '444 get pre';
/* get DF
Fail code repeat_count Y fail N */
select decode(count(1), 0, 'N','Y')
into l_prev_fail
from TT_DUM_TEMP
where c_col1 = l_p_product_gi_dt;
l_loc := '444 get fault_ex';
/* 4. Same repeat_count Y exists */
select decode(count(1), 0, 'N','Y')
into l_same_fault_exists
from TT_DUM_TEMP a, TT_DUM_TEMP b
where a.c_col1 = l_p_product_gi_dt
and b.c_col1 = c_m.product_gi_dt
and a.c_col2 = b.c_col2;
<< update_or_insert >>
/* 5. Total repair : billing count => just plus one here */
/* update or insert */
l_loc := 'upd T_REPAIR_REPEAT';
update T_REPAIR_REPEAT
set
tot_repair = tot_repair +1,
tot_REPEAT = tot_REPEAT+ decode(l_REPEAT_yn, 'Y', 1, 0),
same_fail = same_fail + decode(l_REPEAT_yn, 'N', 0, decode(l_same_fault_exists,'Y', 1, 0)),
fail2fail = fail2fail + decode(l_REPEAT_yn, 'N', 0, case when l_prev_fail ='Y' and l_cur_fail ='Y' then 1 else 0 end) ,
ntf2ntf = ntf2ntf + decode(l_REPEAT_yn, 'N', 0, case when l_prev_fail ='N' and l_cur_fail ='N' then 1 else 0 end) ,
fail2ntf = fail2ntf + decode(l_REPEAT_yn, 'N', 0, case when l_prev_fail ='Y' and l_cur_fail ='N' then 1 else 0 end) ,
ntf2fail = ntf2fail + decode(l_REPEAT_yn, 'N', 0, case when l_prev_fail ='N' and l_cur_fail ='Y' then 1 else 0 end)
where comp_cd = c_m.comp_cd
and mat = c_m.mat
and product_gi_dt = c_m.product_gi_dt;
if sql%rowcount = 0 then
l_loc := 'ins bill_REPEAT';
insert into T_REPAIR_REPEAT
( comp_cd, mat , product_gi_dt,
tot_repair, tot_REPEAT,
same_fail , fail2fail , ntf2ntf , fail2ntf ,ntf2fail )
values
( c_m.comp_cd, c_m.mat , c_m.product_gi_dt,
1, decode(l_REPEAT_yn, 'Y', 1, 0),
decode(l_REPEAT_yn,'N',0,decode(l_same_fault_exists,'Y', 1, 0)),
decode(l_REPEAT_yn,'N',0,case when l_prev_fail ='Y' and l_cur_fail ='Y' then 1 else 0 end) ,
decode(l_REPEAT_yn,'N',0,case when l_prev_fail ='N' and l_cur_fail ='N' then 1 else 0 end) ,
decode(l_REPEAT_yn,'N',0,case when l_prev_fail ='Y' and l_cur_fail ='N' then 1 else 0 end) ,
decode(l_REPEAT_yn,'N',0,case when l_prev_fail ='N' and l_cur_fail ='Y' then 1 else 0 end) ) ;
end if;
/* commit. also release temp data */
COMMIT;
EXCEPTION WHEN OTHERS THEN
o_msg := 'Error '|| c_m.esn ||' at ['|| l_loc || ' ' || SQLERRM ;
rollback ;
return;
END;
END LOOP;
EXCEPTION WHEN OTHERS THEN
o_msg := 'Error PR_DAILY_REPEAT '|| l_loc || SQLERRM ;
END PR_DAILY_REPEAT;
it containes pretty challanging logic behind.
It has company , model, date and each days
comp_cd, mat , product_gi_dt, total repair count, total REPEAT count : REPEAT means repeated repair in 90 days.
When each phone determined as REPEAT, next fields cound added.
Same fail means if same fail code exists it determined as same fault.
Fail 2 fail : you have to know what's fail or Ntf first.
below some master table (tb_df_repr) contains a column "repeat_count_yn"
if this marked 'Y' and one ESN's repair record includes this type of fail code, ESN considered as FAIL, if not contains it considered as 'NTF'.
So program has to judge this times NTF, fail and last repairs NTF, fail.
SAME_FAIL here means it just checks if any fail code marked repeat_count_yn 'Y' exists prior repair and this time also.
Those pretty complicate logic check can create huge amount of data inquery for each decision.
Here I chose to use global temporary table. TT_DUM_TEMP is global temporary table, means the data will be cleared when commit.
So I don't have to worry insert and delete for later use.
Whenever ESN judges as REPEAT, program saves very simple dataset for PGI date and repeat_count_yn 'Y' marked fail code.
Then program has countable amount of data, user might enter ten twenty of fail code for each ESN, but that's super minimal amount of data.
And also it screens only repeat_count_yn 'Y' marked fail code marked data, so there will be half or lesser faild code entered.
This program got huge benefit of using global temp table. Of couse these can be done by using struture or whever.
But I'm old school, I can use table very comfortably then structures. And do they have automated clearing feature?
And as you can see, judging REPEAT logic only have dedicated REPEAT or not checking.
Imagine whole this logic into one SQL statement, I know it can be done, but that will be readable and will be able to debug and fix easily.
That's one of largest benefit using PL/SQL over one giant SQL statement, I believe as developer.
PROCEDURE PR_DAILY_REPEAT (
i_product_gi_dt IN VARCHAR2,
o_msg OUT VARCHAR2
) IS
/*
2011 3 15 Billy Hong New development :
*/
l_p_product_gi_dt T_BILL_MST.product_gi_dt%type;
l_p_ship_delvy_no T_BILL_MST.ship_delvy_no%type;
l_p_cust_cd T_BILL_MST.cust_cd%type;
l_REPEAT_yn varchar2(1) ;
l_prev_fail varchar2(1) ;
l_cur_fail varchar2(1) ;
l_same_fault_exists varchar2(1) ;
l_loc varchar2 (100);
BEGIN
o_msg := 'OK';
delete T_REPAIR_REPEAT
where product_gi_dt = i_product_gi_dt ;
commit;
FOR c_m IN(
Select A.comp_cd, a.cust_cd, A.product_gi_dt, A.ship_delvy_no,
A.recv_date, A.ESN , a.mat
From T_BILL_MST A
Where a.product_gi_dt = i_product_gi_dt
)
LOOP
BEGIN
l_loc := '000';
/* 3. REPEAT or not : GR base -90 if GI exist REPEAT !
find previous GR base 90 days get last only one
Total returns: REPEAT ! */
l_p_product_gi_dt := NULL;
l_REPEAT_yn := NULL;
l_p_ship_delvy_no := NULL;
l_loc := '111';
-- Possible Major delay here
begin
select product_gi_dt, 'Y', ship_delvy_no, a.cust_cd
into l_p_product_gi_dt, l_REPEAT_yn, l_p_ship_delvy_no, l_p_cust_cd
from T_BILL_MST a
where a.product_gi_dt >= to_char(c_m.recv_date -90, 'yyyymmdd')
and a.product_gi_dt < to_char(c_m.recv_date , 'yyyymmdd')
and a.esn = c_m.esn
and product_gi_dt = (select max(product_gi_dt) from T_BILL_MST
where esn = a.esn
and product_gi_dt >= to_char(c_m.recv_date -90, 'yyyymmdd')
and product_gi_dt < to_char(c_m.recv_date , 'yyyymmdd')) ;
exception when NO_DATA_FOUND THEN
-- set 0 for all values
l_REPEAT_yn :='N' ;
l_prev_fail :='N' ;
l_cur_fail :='N' ;
l_same_fault_exists :='N' ;
goto update_or_insert;
end;
l_loc := '222 TT_DUM_TEMP';
/* save current fault info */
/* global temp table delete on commit */
insert into TT_DUM_TEMP
(c_col1, c_col2 )
select c_m.product_gi_dt, df_cd
from T_BILL_REPR_TMP a, tb_df_repr b
where a.bill_date = c_m.product_gi_dt
and a.sold_to = c_m.cust_cd
and a.delvy_no = c_m.ship_delvy_no
and a.esn_no = c_m.esn
and a.df_cd = b.cd
and b.repeat_count_yn = 'Y'
and b.df_repr_flag = 'F' ;
l_loc := '333 ge DUM_TEMP';
/* get DF
Fail code repeat_count Y fail , else N */
select decode(count(1), 0, 'N','Y')
into l_cur_fail
from TT_DUM_TEMP
where c_col1 = c_m.product_gi_dt;
l_loc := '444 ins DUM_TEMP';
/* save previous fault info */
insert into TT_DUM_TEMP
(c_col1, c_col2 )
select l_p_product_gi_dt, df_cd
from T_BILL_REPR_TMP a, tb_df_repr b
where a.bill_date = l_p_product_gi_dt
and a.sold_to = l_p_cust_cd
and a.delvy_no = l_p_ship_delvy_no
and a.esn_no = c_m.esn
and a.df_cd = b.cd
and b.repeat_count_yn = 'Y'
and b.df_repr_flag = 'F' ;
l_loc := '444 get pre';
/* get DF
Fail code repeat_count Y fail N */
select decode(count(1), 0, 'N','Y')
into l_prev_fail
from TT_DUM_TEMP
where c_col1 = l_p_product_gi_dt;
l_loc := '444 get fault_ex';
/* 4. Same repeat_count Y exists */
select decode(count(1), 0, 'N','Y')
into l_same_fault_exists
from TT_DUM_TEMP a, TT_DUM_TEMP b
where a.c_col1 = l_p_product_gi_dt
and b.c_col1 = c_m.product_gi_dt
and a.c_col2 = b.c_col2;
<< update_or_insert >>
/* 5. Total repair : billing count => just plus one here */
/* update or insert */
l_loc := 'upd T_REPAIR_REPEAT';
update T_REPAIR_REPEAT
set
tot_repair = tot_repair +1,
tot_REPEAT = tot_REPEAT+ decode(l_REPEAT_yn, 'Y', 1, 0),
same_fail = same_fail + decode(l_REPEAT_yn, 'N', 0, decode(l_same_fault_exists,'Y', 1, 0)),
fail2fail = fail2fail + decode(l_REPEAT_yn, 'N', 0, case when l_prev_fail ='Y' and l_cur_fail ='Y' then 1 else 0 end) ,
ntf2ntf = ntf2ntf + decode(l_REPEAT_yn, 'N', 0, case when l_prev_fail ='N' and l_cur_fail ='N' then 1 else 0 end) ,
fail2ntf = fail2ntf + decode(l_REPEAT_yn, 'N', 0, case when l_prev_fail ='Y' and l_cur_fail ='N' then 1 else 0 end) ,
ntf2fail = ntf2fail + decode(l_REPEAT_yn, 'N', 0, case when l_prev_fail ='N' and l_cur_fail ='Y' then 1 else 0 end)
where comp_cd = c_m.comp_cd
and mat = c_m.mat
and product_gi_dt = c_m.product_gi_dt;
if sql%rowcount = 0 then
l_loc := 'ins bill_REPEAT';
insert into T_REPAIR_REPEAT
( comp_cd, mat , product_gi_dt,
tot_repair, tot_REPEAT,
same_fail , fail2fail , ntf2ntf , fail2ntf ,ntf2fail )
values
( c_m.comp_cd, c_m.mat , c_m.product_gi_dt,
1, decode(l_REPEAT_yn, 'Y', 1, 0),
decode(l_REPEAT_yn,'N',0,decode(l_same_fault_exists,'Y', 1, 0)),
decode(l_REPEAT_yn,'N',0,case when l_prev_fail ='Y' and l_cur_fail ='Y' then 1 else 0 end) ,
decode(l_REPEAT_yn,'N',0,case when l_prev_fail ='N' and l_cur_fail ='N' then 1 else 0 end) ,
decode(l_REPEAT_yn,'N',0,case when l_prev_fail ='Y' and l_cur_fail ='N' then 1 else 0 end) ,
decode(l_REPEAT_yn,'N',0,case when l_prev_fail ='N' and l_cur_fail ='Y' then 1 else 0 end) ) ;
end if;
/* commit. also release temp data */
COMMIT;
EXCEPTION WHEN OTHERS THEN
o_msg := 'Error '|| c_m.esn ||' at ['|| l_loc || ' ' || SQLERRM ;
rollback ;
return;
END;
END LOOP;
EXCEPTION WHEN OTHERS THEN
o_msg := 'Error PR_DAILY_REPEAT '|| l_loc || SQLERRM ;
END PR_DAILY_REPEAT;
Subscribe to:
Posts (Atom)