Slow query with 7.31UC2
Hi everyone,
First, the system:
-Running 7.31UC2 on an HPUX 10/20 K410, no KAIO or PDQ
-The application is PeopleSoft HR/Benefits 7.02 (client side SQR)
-Updating statistics nightly w/ Art Kagel's dostats program
-Logical and phys logs are on their own controllers; same w/ root and
mirrored root.
We had this program that ran in about 30-40 minutes on 7.30UC5. Now in
7.31UC2 it takes HOURS UPON HOURS. I'm hoping someone here can give me
some constructive advice as to what I should do to tune this query, or
some other advice (i.e. I'm fully prepared for someone to shred my
onconfig! :)
The two tables involved are ps_pay_deduction (828823 rows) and
ps_pay_check (93956).
Here is the sqexplain.out for the 7.30UC5 optimizer:
QUERY:
------
select sum(ee.ded_cur)
FROM ps_pay_check pc, ps_pay_deduction ee
WHERE
pc.company = ee.company
AND pc.paygroup = ee.paygroup
AND pc.pay_end_dt = ee.pay_end_dt
AND pc.off_cycle = ee.off_cycle
AND pc.pagen = ee.pagen
and pc.linen = ee.linen
AND pc.sepchk = ee.sepchk
AND pc.company = '100'
and pc.paygroup = '01'
AND pc.emplid = '558476318'
--and pc.check_dt = '12-17-1999'
and pc.check_dt BETWEEN '01-01-1999' AND '12-17-1999'
and ee.dedcd = '253'
and ee.ded_class = 'B'
and ee.ded_class <= 'K'
Estimated Cost: 24
Estimated # of Rows Returned: 1
1) informix.pc: INDEX PATH
Filters: (informix.pc.check_dt >= 01/01/1999
AND informix.pc.check_dt <= 12/17/1999 )
(1) Index Keys: emplid company paygroup pay_end_dt
off_cycle pagen linen sepchk form_id checkn name
(Serial, fragments: ALL)
Lower Index Filter: (informix.pc.company = '100'
AND (informix.pc.paygroup = '01' AND
informix.pc.emplid = '558476318' ) )
2) informix.ee: INDEX PATH
(1) Index Keys: company paygroup pay_end_dt off_cycle
pagen linen sepchk plan_type benefit_plan dedcd ded_class
(Key-First) (Serial, fragments: ALL)
Lower Index Filter: (informix.ee.sepchk = informix.pc.sepchk
AND (informix.ee.linen = informix.pc.linen
AND (informix.ee.pagen = informix.pc.pagen
AND (informix.ee.off_cycle = informix.pc.off_cycle
AND (informix.ee.pay_end_dt = informix.pc.pay_end_dt
AND (informix.ee.paygroup = informix.pc.paygroup
AND informix.ee.company = informix.pc.company ) ) ) ) ) )
Key-First Filters: (informix.ee.ded_class <= 'K' ) AND
(informix.ee.ded_class = 'B' ) AND
(informix.ee.dedcd = '253' )
NESTED LOOP JOIN
...and here is the sqexplain for 7.31UC2:
QUERY:
------
select sum(ee.ded_cur)
FROM ps_pay_check pc, ps_pay_deduction ee
WHERE
pc.company = ee.company
AND pc.paygroup = ee.paygroup
AND pc.pay_end_dt = ee.pay_end_dt
AND pc.off_cycle = ee.off_cycle
AND pc.pagen = ee.pagen
and pc.linen = ee.linen
AND pc.sepchk = ee.sepchk
AND pc.company = '100'
and pc.paygroup = '01'
AND pc.emplid = '558476318'
--and pc.check_dt = '12-17-1999'
and pc.check_dt BETWEEN '01-01-1999' AND '12-17-1999'
and ee.dedcd = '253'
and ee.ded_class = 'B'
and ee.ded_class <= 'K'
Estimated Cost: 57
Estimated # of Rows Returned: 1
1) informix.pc: INDEX PATH
Filters: (informix.pc.check_dt >= 01/01/1999
AND informix.pc.check_dt <= 12/17/1999 )
(1) Index Keys: emplid company paygroup
pay_end_dt off_cycle pagen linen sepchk
form_id checkn name (Serial, fragments: ALL)
Lower Index Filter: (informix.pc.company = '100'
AND (informix.pc.paygroup = '01'
AND informix.pc.emplid = '558476318' ) )
2) informix.ee: INDEX PATH
(1) Index Keys: company paygroup pay_end_dt
off_cycle pagen linen sepchk plan_type benefit_plan
dedcd ded_class (Key-First) (Serial, fragments: ALL)
Lower Index Filter: (informix.ee.sepchk = informix.pc.sepchk
AND (informix.ee.linen = informix.pc.linen
AND (informix.ee.pagen = informix.pc.pagen
AND (informix.ee.off_cycle = informix.pc.off_cycle
AND (informix.ee.pay_end_dt = informix.pc.pay_end_dt
AND (informix.ee.paygroup = informix.pc.paygroup
AND informix.ee.company = informix.pc.company ) ) ) ) ) )
Key-First Filters: (informix.ee.ded_class <= 'K' ) AND
(informix.ee.ded_class = 'B' ) AND
(informix.ee.dedcd = '253' )
NESTED LOOP JOIN
...and here is our onconfig for 7.31UC2 (some irrelevant stuff snipped):
ROOTNAME hr72root
ROOTPATH /link_hr72/hr72root
ROOTOFFSET 0
ROOTSIZE 50000MIRROR 1
MIRRORPATH /link_hr72/hr72rootm
MIRROROFFSET 0PHYSDBS hr72_phydbs
PHYSFILE 30000
LOGFILES 64
LOGSIZE 10000
TAPEDEV /dev/rmt/0m
TAPEBLK 128
TAPESIZE 8000000
LTAPEDEV /dev/rmt/1m
LTAPEBLK 16
LTAPESIZE 4000000STAGEBLOB
SERVERNUM 3
DBSERVERNAME hrprod72
DBSERVERALIASES hrprod72_tcp
NETTYPE ipcshm,1,12,CPU
NETTYPE soctcp,2,50,NET
DEADLOCK_TIMEOUT 60
RESIDENT 1
MULTIPROCESSOR 1
NUMCPUVPS 2
SINGLE_CPU_VP 0
NOAGE 1
AFF_SPROC 0
AFF_NPROCS 2
LOCKS 500000
BUFFERS 50000
NUMAIOVPS 5
PHYSBUFF 60
LOGBUFF 8LOGSMAX 160
CLEANERS 127
SHMBASE 0x0
SHMVIRTSIZE 40000
SHMADD 8000
SHMTOTAL 0
CKPTINTVL 400
LRUS 127
LRU_MAX_DIRTY 2
LRU_MIN_DIRTY 1
LTXHWM 50
LTXEHWM 60
TXTIMEOUT 0x12c
STACKSIZE 128
OFF_RECVRY_THREADS 20
ON_RECVRY_THREADS 20
DRAUTO 0
DRINTERVAL 30
DRTIMEOUT 30
DRLOSTFOUND /logs_fsprod/dr_fsprod.lostfoundCDR_LOGBUFFERS 2048
CDR_EVALTHREADS 1,2
CDR_DSLOCKWAIT 5
CDR_QUEUEMEM 4096
BAR_ACT_LOG /tmp/bar_act.log
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 10
BAR_XFER_BUF_SIZE 31
RA_PAGES 10
RA_THRESHOLD 5
DBSPACETEMP hr72_temp
DUMPDIR /dev/null
DUMPSHMEM 0
DUMPGCORE 0
DUMPCORE 0
DUMPCNT 0
FILLFACTOR 90
USEOSTIME 0
MAX_PDQPRIORITY 100
DS_MAX_QUERIES 256
DS_TOTAL_MEMORY 131072
DS_MAX_SCANS 1048576
DATASKIP off
OPTCOMPIND 0
ONDBSPACEDOWN 0LBU_PRESERVE 1
OPCACHEMAX 0
HETERO_COMMIT 0SYSALARMPROGRAM /opt/informix731UC2/etc/evidence.sh
TBLSPACE_STATS 1ISM_DATA_POOL ISMData
ISM_LOG_POOL ISMLogs
OPT_GOAL -1
DIRECTIVES 1
RESTARTABLE_RESTORE off
Many, many, many thanks for your time!
Ben Guerard
Napa County
--
to reply: drumzspace (AT) yahoo (DOT) com
Sent via Deja.com http://www.deja.com/
Before you buy.
A couple of new indexes may be worth trying. The following indexes would
cover the query on both tables and give maximum selectivity:
create index psxpay_check on ps_pay_check
(emplid, company, paygroup, check_dt,
pay_end_dt, off_cycle, pagen, linen, sepchk);
create index psxpay_deduction on ps_pay_deduction
(company, paygroup, pay_end_dt, off_cycle,
pagen, linen, sepchk, dedcd, ded_class, ded_cur)
It may also be worthwhile to investigate the order in which this query is
executed.
For example, if a cursor on employee ordered by company, paygroup, emplid is
driving the process, it may be useful to make emplid the third column in the
ps_pay_check index so that the indexes are read sequentially.
Jay Buckler
drumzspace <drumzspace@my-deja.com> wrote in message
news:83lnn4$5hu$1@nnrp1.deja.com...
> Hi everyone,
>
> First, the system:
> -Running 7.31UC2 on an HPUX 10/20 K410, no KAIO or PDQ
> -The application is PeopleSoft HR/Benefits 7.02 (client side SQR)
> -Updating statistics nightly w/ Art Kagel's dostats program
> -Logical and phys logs are on their own controllers; same w/ root and
> mirrored root.
>
> We had this program that ran in about 30-40 minutes on 7.30UC5. Now in
> 7.31UC2 it takes HOURS UPON HOURS. I'm hoping someone here can give me
> some constructive advice as to what I should do to tune this query, or
> some other advice (i.e. I'm fully prepared for someone to shred my
> onconfig! :)
>
> The two tables involved are ps_pay_deduction (828823 rows) and
> ps_pay_check (93956).
>
> Here is the sqexplain.out for the 7.30UC5 optimizer:
> QUERY:
> ------
> select sum(ee.ded_cur)
> FROM ps_pay_check pc, ps_pay_deduction ee
> WHERE
> pc.company = ee.company
> AND pc.paygroup = ee.paygroup
> AND pc.pay_end_dt = ee.pay_end_dt
> AND pc.off_cycle = ee.off_cycle
> AND pc.pagen = ee.pagen
> and pc.linen = ee.linen
> AND pc.sepchk = ee.sepchk
> AND pc.company = '100'
> and pc.paygroup = '01'
> AND pc.emplid = '558476318'
> --and pc.check_dt = '12-17-1999'
> and pc.check_dt BETWEEN '01-01-1999' AND '12-17-1999'
> and ee.dedcd = '253'
> and ee.ded_class = 'B'
> and ee.ded_class <= 'K'
>
> Estimated Cost: 24
> Estimated # of Rows Returned: 1
>
> 1) informix.pc: INDEX PATH
>
> Filters: (informix.pc.check_dt >= 01/01/1999
> AND informix.pc.check_dt <= 12/17/1999 )
>
> (1) Index Keys: emplid company paygroup pay_end_dt
> off_cycle pagen linen sepchk form_id checkn name
> (Serial, fragments: ALL)
> Lower Index Filter: (informix.pc.company = '100'
> AND (informix.pc.paygroup = '01' AND
> informix.pc.emplid = '558476318' ) )
>
> 2) informix.ee: INDEX PATH
>
> (1) Index Keys: company paygroup pay_end_dt off_cycle
> pagen linen sepchk plan_type benefit_plan dedcd ded_class
> (Key-First) (Serial, fragments: ALL)
> Lower Index Filter: (informix.ee.sepchk = informix.pc.sepchk
> AND (informix.ee.linen = informix.pc.linen
> AND (informix.ee.pagen = informix.pc.pagen
> AND (informix.ee.off_cycle = informix.pc.off_cycle
> AND (informix.ee.pay_end_dt = informix.pc.pay_end_dt
> AND (informix.ee.paygroup = informix.pc.paygroup
> AND informix.ee.company = informix.pc.company ) ) ) ) ) )
> Key-First Filters: (informix.ee.ded_class <= 'K' ) AND
> (informix.ee.ded_class = 'B' ) AND
> (informix.ee.dedcd = '253' )
> NESTED LOOP JOIN
>
> ...and here is the sqexplain for 7.31UC2:
> QUERY:
> ------
> select sum(ee.ded_cur)
> FROM ps_pay_check pc, ps_pay_deduction ee
> WHERE
> pc.company = ee.company
> AND pc.paygroup = ee.paygroup
> AND pc.pay_end_dt = ee.pay_end_dt
> AND pc.off_cycle = ee.off_cycle
> AND pc.pagen = ee.pagen
> and pc.linen = ee.linen
> AND pc.sepchk = ee.sepchk
> AND pc.company = '100'
> and pc.paygroup = '01'
> AND pc.emplid = '558476318'
> --and pc.check_dt = '12-17-1999'
> and pc.check_dt BETWEEN '01-01-1999' AND '12-17-1999'
> and ee.dedcd = '253'
> and ee.ded_class = 'B'
> and ee.ded_class <= 'K'
>
> Estimated Cost: 57
> Estimated # of Rows Returned: 1
>
> 1) informix.pc: INDEX PATH
>
> Filters: (informix.pc.check_dt >= 01/01/1999
> AND informix.pc.check_dt <= 12/17/1999 )
>
> (1) Index Keys: emplid company paygroup
> pay_end_dt off_cycle pagen linen sepchk
> form_id checkn name (Serial, fragments: ALL)
> Lower Index Filter: (informix.pc.company = '100'
> AND (informix.pc.paygroup = '01'
> AND informix.pc.emplid = '558476318' ) )
>
> 2) informix.ee: INDEX PATH
>
> (1) Index Keys: company paygroup pay_end_dt
> off_cycle pagen linen sepchk plan_type benefit_plan
> dedcd ded_class (Key-First) (Serial, fragments: ALL)
> Lower Index Filter: (informix.ee.sepchk = informix.pc.sepchk
> AND (informix.ee.linen = informix.pc.linen
> AND (informix.ee.pagen = informix.pc.pagen
> AND (informix.ee.off_cycle = informix.pc.off_cycle
> AND (informix.ee.pay_end_dt = informix.pc.pay_end_dt
> AND (informix.ee.paygroup = informix.pc.paygroup
> AND informix.ee.company = informix.pc.company ) ) ) ) ) )
> Key-First Filters: (informix.ee.ded_class <= 'K' ) AND
> (informix.ee.ded_class = 'B' ) AND
> (informix.ee.dedcd = '253' )
> NESTED LOOP JOIN
>
> ...and here is our onconfig for 7.31UC2 (some irrelevant stuff snipped):
> ROOTNAME hr72root
> ROOTPATH /link_hr72/hr72root
> ROOTOFFSET 0
> ROOTSIZE 50000> MIRROR 1
> MIRRORPATH /link_hr72/hr72rootm
> MIRROROFFSET 0> PHYSDBS hr72_phydbs
> PHYSFILE 30000
> LOGFILES 64
> LOGSIZE 10000
> TAPEDEV /dev/rmt/0m
> TAPEBLK 128
> TAPESIZE 8000000
> LTAPEDEV /dev/rmt/1m
> LTAPEBLK 16
> LTAPESIZE 4000000> STAGEBLOB
> SERVERNUM 3
> DBSERVERNAME hrprod72
> DBSERVERALIASES hrprod72_tcp
> NETTYPE ipcshm,1,12,CPU
> NETTYPE soctcp,2,50,NET
> DEADLOCK_TIMEOUT 60
> RESIDENT 1
> MULTIPROCESSOR 1
> NUMCPUVPS 2
> SINGLE_CPU_VP 0
> NOAGE 1
> AFF_SPROC 0
> AFF_NPROCS 2
> LOCKS 500000
> BUFFERS 50000
> NUMAIOVPS 5
> PHYSBUFF 60
> LOGBUFF 8> LOGSMAX 160
> CLEANERS 127
> SHMBASE 0x0
> SHMVIRTSIZE 40000
> SHMADD 8000
> SHMTOTAL 0
> CKPTINTVL 400
> LRUS 127
> LRU_MAX_DIRTY 2
> LRU_MIN_DIRTY 1
> LTXHWM 50
> LTXEHWM 60
> TXTIMEOUT 0x12c
> STACKSIZE 128
> OFF_RECVRY_THREADS 20
> ON_RECVRY_THREADS 20
> DRAUTO 0
> DRINTERVAL 30
> DRTIMEOUT 30
> DRLOSTFOUND /logs_fsprod/dr_fsprod.lostfound> CDR_LOGBUFFERS 2048
> CDR_EVALTHREADS 1,2
> CDR_DSLOCKWAIT 5>