Update staistics Running Way Slow

Hello All, I am running Update Stats on ne fragmented table in 40 dbspaces and looks like it's running way slow. Please look at my follwing script, even after setting PDQPRIORITY, I am not able to see anything in onstat -g mgm. Because of this I am guessing that I am getting default allocation which is 128K. I will highly appreciate any attention and some tips to improvethe timing of this. I am getting 1hr 15min. Please give a look on following { TABLE "scorpion".seg_detail row size = 221 number of columns = 70 index size = 61 } create table "scorpion".seg_detail ( prc_date date, batch_no smallint, record_num integer, prd_cd char(2), bkg_date date, bkg_time datetime hour to minute, pcc char(5), seg_sts_cd char(2), prv_seg_sts_cd char(2), cos_ind char(2), nbr_in_pty smallint, con_asc_cd char(3), con_lvl_cd char(2), nx_bs_lnk_ind char(1), pcc_scr_t_ind char(1), seg_aty_cd char(1), upd_pcc char(5), scr_t_ind char(1), frc_seg_ind char(1), bsg_mst_ind char(1), asc_bll_mth_cd char(5), sub_bll_mth_cd char(5), seg_sts_typ_cd char(1), asc_bll_ind char(1), sub_bll_ind char(1), chg_seg_sts_ind char(1), clm_ind char(1), scr_tfm_pcc char(5), bll_con_lvl_cd char(2), div_pnr_ind char(1), cvt_tna_dtime datetime year to minute, hm_ofc_dtime datetime year to minute, reduce_npty_ind char(1), dep_dtime datetime year to minute, bkg_ind char(1), cnl_ind char(1), pnr_lct_id char(6), orig_seg_cre_date date, psg_nm varchar(30), asc_cd char(3), flt_nr smallint, brd_on_cty char(3), brd_on_cry char(2), brd_off_cty char(3), brd_off_cry char(2), agt_sin_id char(3), hm_sta_cd char(5), agt_dty_cd char(1), ln_ia_ta_id char(6), tjr_arc_iata integer, cre_pcc char(5), int_dom_itr_ind char(1), scr_rmv_zro_ind char(1), pnr_typ char(1), oa_lct_id char(6), int_dom_seg_ind char(1), eqt_cd char(3), cht_ind char(1), epr_pnr_cd char(5), epr_seg_cd char(5), rep_chnl_cd char(2), det_chnl_cd char(2), frag_id smallint, ine_cd char(3), prc_lvl_cd char(2), npc_ind char(1), pnr_cre_dtime datetime year to minute, midt_ind char(1), tkt_cry_ind char(1), qk_cpy_ind char(1) ) fragment by expression (frag_id = 7 ) in sopp1sp1d_seg_07 , (frag_id = 8 ) in sopp1sp1d_seg_08 , (frag_id = 9 ) in sopp1sp1d_seg_09 , (frag_id = 10 ) in sopp1sp1d_seg_10 , (frag_id = 11 ) in sopp1sp1d_seg_11 , (frag_id = 12 ) in sopp1sp1d_seg_12 , (frag_id = 13 ) in sopp1sp1d_seg_13 , (frag_id = 14 ) in sopp1sp1d_seg_14 , (frag_id = 1 ) in sopp1sp1d_seg_01 , (frag_id = 2 ) in sopp1sp1d_seg_02 , (frag_id = 3 ) in sopp1sp1d_seg_03 , (frag_id = 4 ) in sopp1sp1d_seg_04 , (frag_id = 5 ) in sopp1sp1d_seg_05 , (frag_id = 6 ) in sopp1sp1d_seg_06 , (frag_id = 15 ) in sopp1sp1d_seg_15 , (frag_id = 16 ) in sopp1sp1d_seg_16 , (frag_id = 17 ) in sopp1sp1d_seg_17 , (frag_id = 18 ) in sopp1sp1d_seg_18 , (frag_id = 19 ) in sopp1sp1d_seg_19 , (frag_id = 20 ) in sopp1sp1d_seg_20 , (frag_id = 21 ) in sopp1sp1d_seg_21 , (frag_id = 22 ) in sopp1sp1d_seg_22 , (frag_id = 23 ) in sopp1sp1d_seg_23 , (frag_id = 24 ) in sopp1sp1d_seg_24 , (frag_id = 25 ) in sopp1sp1d_seg_25 , (frag_id = 26 ) in sopp1sp1d_seg_26 , (frag_id = 27 ) in sopp1sp1d_seg_27 , (frag_id = 28 ) in sopp1sp1d_seg_28 , (frag_id = 29 ) in sopp1sp1d_seg_29 , (frag_id = 32 ) in sopp1sp1d_seg_32 , (frag_id = 33 ) in sopp1sp1d_seg_33 , (frag_id = 34 ) in sopp1sp1d_seg_34 , (frag_id = 35 ) in sopp1sp1d_seg_35 , (frag_id = 36 ) in sopp1sp1d_seg_36 , (frag_id = 37 ) in sopp1sp1d_seg_37 , (frag_id = 38 ) in sopp1sp1d_seg_38 , (frag_id = 39 ) in sopp1sp1d_seg_39 , (frag_id = 40 ) in sopp1sp1d_seg_40 , (frag_id = 30 ) in sopp1sp1d_seg_30 , (frag_id = 31 ) in sopp1sp1d_seg_31 extent size 512 next size 43840 lock mode page; revoke all on "scorpion".seg_detail from "public"; create unique index "scorpion".seg_detail_x1 on "scorpion".seg_detail (prc_date,batch_no,record_num,frag_id); create index "scorpion".seg_detail_x2 on "scorpion".seg_detail (pcc); create index "scorpion".seg_detail_x3 on "scorpion".seg_detail (asc_cd); create index "scorpion".seg_detail_x4 on "scorpion".seg_detail (bkg_date,asc_bll_ind); System Configuration: Sun Microsystems sun4u Memory size: 3072 Megabytes System Peripherals (Software Nodes): 14 CPU Machine /opt/ifmx > cat /tmp/test.sh #!/bin/ksh PSORT_NPROCS=28 PSORT_DBTEMP="/dbsopp1_01/p1/buildtemp /dbsopp1_02/p1/buildtemp" PDQPRIORITY=80 export PDQPRIORITY PSORT_DBTEMP PSORT_NPROCS dbaccess sp1bill -<<EOF update statistics medium for table seg_detail(prc_date, pcc, asc_cd, bkg_date)resolution 1.00 .99; EOF >onstat -c [only main parameters] SERVERNUM 1 # Unique idcorresponding to a OnLine instance DBSERVERNAME sopp1 # Name of defaultdatabase server DBSERVERALIASES sopp1_tcp # List of alternatedbservernames DEADLOCK_TIMEOUT 60 # Max time to wait oflock in distributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 forsingle-processor, 1 for multi-processor #NUMCPUVPS 8 # Number of user (cpu) vps NUMCPUVPS 14 # Number of user (cpu)vps SINGLE_CPU_VP 0 # If non-zero, limitnumber of cpu vps to one NOAGE 1 # Process aging AFF_SPROC 0 # Affinity startprocessor AFF_NPROCS 13 # Affinity number ofprocessors LOCKS 40002 # BUFFERS 401408 NUMAIOVPS 32 # Number of IO vps PHYSBUFF 32 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes) LOGSMAX 100 # Maximum number oflogical log files #CLEANERS 32 # Number of buffer cleaner processes (1/drive) CLEANERS 128 # Number of buffer cleaner processes (1/drive), 2nd run SHMBASE 0xa000000 # Shared memory baseaddress SHMVIRTSIZE 838860 # 2nd Run SHMADD 83440 # Size of new sharedmemory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 128 # Number of LRUqueues, Need to change for 2nd run to reduce buffw

One problem is the setting of PSORT_DBTEMP is incorrect. It should be: PSORT_DBTEMP=/dbsopp1_01/p1/buildtemp:/dbsopp1_02/p1/buildtemp Also for best performance you should be using at least three filesystems. Four FSs seems to improve things just a bit more but more than that do not help unless you need the disk volume to hold the temp files. Art S. Kagel Sanjeev sagar wrote: > > Hello All, > > I am running Update Stats on ne fragmented table in 40 > dbspaces and looks like it's running way slow. Please > look at my follwing script, even after setting > PDQPRIORITY, I am not able to see anything in onstat -g > mgm. Because of this I am guessing that I am getting > default allocation which is 128K. I will highly > appreciate any attention and some tips to improvethe > timing of this. I am getting 1hr 15min. Please give a > look on following > > { TABLE "scorpion".seg_detail row size = 221 number of > columns = 70 index size = > 61 } > create table "scorpion".seg_detail > ( > prc_date date, > batch_no smallint, > record_num integer, > prd_cd char(2), > bkg_date date, > bkg_time datetime hour to minute, > pcc char(5), > seg_sts_cd char(2), > prv_seg_sts_cd char(2), > cos_ind char(2), > nbr_in_pty smallint, > con_asc_cd char(3), > con_lvl_cd char(2), > nx_bs_lnk_ind char(1), > pcc_scr_t_ind char(1), > seg_aty_cd char(1), > upd_pcc char(5), > scr_t_ind char(1), > frc_seg_ind char(1), > bsg_mst_ind char(1), > asc_bll_mth_cd char(5), > sub_bll_mth_cd char(5), > seg_sts_typ_cd char(1), > asc_bll_ind char(1), > sub_bll_ind char(1), > chg_seg_sts_ind char(1), > clm_ind char(1), > scr_tfm_pcc char(5), > bll_con_lvl_cd char(2), > div_pnr_ind char(1), > cvt_tna_dtime datetime year to minute, > hm_ofc_dtime datetime year to minute, > reduce_npty_ind char(1), > dep_dtime datetime year to minute, > bkg_ind char(1), > cnl_ind char(1), > pnr_lct_id char(6), > orig_seg_cre_date date, > psg_nm varchar(30), > asc_cd char(3), > flt_nr smallint, > brd_on_cty char(3), > brd_on_cry char(2), > brd_off_cty char(3), > brd_off_cry char(2), > agt_sin_id char(3), > hm_sta_cd char(5), > agt_dty_cd char(1), > ln_ia_ta_id char(6), > tjr_arc_iata integer, > cre_pcc char(5), > int_dom_itr_ind char(1), > scr_rmv_zro_ind char(1), > pnr_typ char(1), > oa_lct_id char(6), > int_dom_seg_ind char(1), > eqt_cd char(3), > cht_ind char(1), > epr_pnr_cd char(5), > epr_seg_cd char(5), > rep_chnl_cd char(2), > det_chnl_cd char(2), > frag_id smallint, > ine_cd char(3), > prc_lvl_cd char(2), > npc_ind char(1), > pnr_cre_dtime datetime year to minute, > midt_ind char(1), > tkt_cry_ind char(1), > qk_cpy_ind char(1) > ) > fragment by expression > (frag_id = 7 ) in sopp1sp1d_seg_07 , > (frag_id = 8 ) in sopp1sp1d_seg_08 , > (frag_id = 9 ) in sopp1sp1d_seg_09 , > (frag_id = 10 ) in sopp1sp1d_seg_10 , > (frag_id = 11 ) in sopp1sp1d_seg_11 , > (frag_id = 12 ) in sopp1sp1d_seg_12 , > (frag_id = 13 ) in sopp1sp1d_seg_13 , > (frag_id = 14 ) in sopp1sp1d_seg_14 , > (frag_id = 1 ) in sopp1sp1d_seg_01 , > (frag_id = 2 ) in sopp1sp1d_seg_02 , > (frag_id = 3 ) in sopp1sp1d_seg_03 , > (frag_id = 4 ) in sopp1sp1d_seg_04 , > (frag_id = 5 ) in sopp1sp1d_seg_05 , > (frag_id = 6 ) in sopp1sp1d_seg_06 , > (frag_id = 15 ) in sopp1sp1d_seg_15 , > (frag_id = 16 ) in sopp1sp1d_seg_16 , > (frag_id = 17 ) in sopp1sp1d_seg_17 , > (frag_id = 18 ) in sopp1sp1d_seg_18 , > (frag_id = 19 ) in sopp1sp1d_seg_19 , > (frag_id = 20 ) in sopp1sp1d_seg_20 , > (frag_id = 21 ) in sopp1sp1d_seg_21 , > (frag_id = 22 ) in sopp1sp1d_seg_22 , > (frag_id = 23 ) in sopp1sp1d_seg_23 , > (frag_id = 24 ) in sopp1sp1d_seg_24 , > (frag_id = 25 ) in sopp1sp1d_seg_25 , > (frag_id = 26 ) in sopp1sp1d_seg_26 , > (frag_id = 27 ) in sopp1sp1d_seg_27 , > (frag_id = 28 ) in sopp1sp1d_seg_28 , > (frag_id = 29 ) in sopp1sp1d_seg_29 , > (frag_id = 32 ) in sopp1sp1d_seg_32 , > (frag_id = 33 ) in sopp1sp1d_seg_33 , > (frag_id = 34 ) in sopp1sp1d_seg_34 , > (frag_id = 35 ) in sopp1sp1d_seg_35 , > (frag_id = 36 ) in sopp1sp1d_seg_36 , > (frag_id = 37 ) in sopp1sp1d_seg_37 , > (frag_id = 38 ) in sopp1sp1d_seg_38 , > (frag_id = 39 ) in sopp1sp1d_seg_39 , > (frag_id = 40 ) in sopp1sp1d_seg_40 , > (frag_id = 30 ) in sopp1sp1d_seg_30 , > (frag_id = 31 ) in sopp1sp1d_seg_31 > extent size 512 next size 43840 lock mode page; > revoke all on "scorpion".seg_detail from "public";> > create unique index "scorpion".seg_detail_x1 on > "scorpion".seg_detail > (prc_date,batch_no,record_num,frag_id); > create index "scorpion".seg_detail_x2 on > "scorpion".seg_detail > (pcc); > create index "scorpion".seg_detail_x3 on > "scorpion".seg_detail > (asc_cd); > create index "scorpion".seg_detail_x4 on > "scorpion".seg_detail > (bkg_date,asc_bll_ind); > > System Configuration: Sun Microsystems sun4u > Memory size: 3072 Megabytes > System Peripherals (Software Nodes): > > 14 CPU Machine > > /opt/ifmx > cat /tmp/test.sh > #!/bin/ksh > PSORT_NPROCS=28 > PSORT_DBTEMP="/dbsopp1_01/p1/buildtemp > /dbsopp1_02/p1/buildtemp" > PDQPRIORITY=80 > export PDQPRIORITY PSORT_DBTEMP PSORT_NPROCS > dbaccess sp1bill -<<EOF > update statistics medium for table seg_detail(prc_date, > pcc, asc_cd, bkg_date)> resolution 1.00 .99; > EOF > > >onstat -c [only main parameters]> > SERVERNUM 1 # Unique id> corresponding to a OnLine instance > DBSERVERNAME sopp1 # Name of default> database server > DBSERVERALIASES sopp1_tcp # List of alternate> dbservernames > DEADLOCK_TIMEOUT 60 # Max time to wait of> lock in distributed env. > RESIDENT 1 # Forced residency flag > (Yes = 1, No = 0) > MULTIPROCESSOR 1 # 0 for> single-processor, 1 for multi-processor > #NUMCPUVPS 8 # Number of user (cpu) > vps > NUMCPUVPS 14 # Number of user (cpu)> vps > SINGLE_CPU_VP 0 # If non-zero, limit> number of cpu vps to one > NOAGE 1 # Process aging > AFF_SPROC 0 # Affinity start> processor > AFF_NPROCS 13 # Affinity number of> processors > > LOCKS 40002 # > BUFFERS 401408 > NUMAIOVPS 32 # Number of IO vps > PHYSBUFF 32 # Physical log buffer> size (Kbytes