Re: Performance problem with a PeopleSoft Database

Trying again, It must have been too big.

> I am experiencing a problem with one of my databases that I can't explain.
> Supposidly, there is not real difference between
> development/qa/test/production databases, but for some reason the
 Production
> is running slower for long running jobs than is Dev and QA. I have
> considered the additional access of the on-line users, but I don't feel
 that
> is the issue.
>
> Here are some excerpts of the report.txt (bstat/estat report), that
> concerned me. This is only a 10 minute snapshot, but longer reports don't
> really look much different.
>
>
> This is a link to the whole file.
> http://24.8.218.197/problem/report.txt
>
>
> LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS
> INVALIDATI
> ------------ ---------- ---------- ---------- ---------- ---------- ------
 --
> --
> BODY 12 .833 12 .75 1
> 0
> CLUSTER 17 .941 25 .92 0
 0
> INDEX 3 .333 3 .333 0
> 0
> OBJECT 0 1 0 1 0
> 0
> PIPE 0 1 0 1 0
> 0
> SQL AREA 14047 .917 36630 .94 8
> 0
> TABLE/PROCED 3936 .945 7960 .949 4
 0
> TRIGGER 20 1 20 1 0
> 0
>
> The above looked fine to me, the area's that didn't look good, where small
> cases. (index 3)
>
> consistent gets 5870256 57551.53 90311.63
> 9576.27
>
> This concerned me, remember this is only a 10 minute scan.
>
> session logical reads 5869211 57541.28 90295.55
> 9574.57
> session pga memory 22543532 221015.02 346823.57
> 36775.75
> session pga memory max 46852708 459340.27 720810.89
> 76431.82
> session uga memory 103079434128 1010582687.5 1585837448.1
> 168155683.73
> session uga memory max 26167636 256545.45 402579.02
> 42687.82
>
> This is more concerning (session uga memory)
>
> cache buffers chai 8987049 29 1 0
> 0
>
> The processes that were having problems seemed to get hung (1-3 seconds)
 on
> this latch, and also
> db file sequential read's
>
> I am really stumped.
>
> This is one statement that is an issue.
>
>
> UPDATE PS_BNK_RCN_DISBRSE SET PYMNT_RECONCILE_DT = TO_DATE('2000-06-05',
> 'YYYY-MM-DD') ,RECON_STATUS = 'REC' ,RECON_TYPE = 'A' ,RECONCILE_OPRID =
> 'AUTO' ,RECON_CYCLE_NBR = 0000000003 ,STTLMNT_DT_ACTUAL =
> TO_DATE('2099-08-17','YYYY-MM-DD')
> WHERE
> RECON_TYPE = 'U' AND BNK_ID_NBR = '950054313' AND BANK_ACCOUNT_NUM =
> '612969840' AND (RECON_CYCLE_NBR <> 0000000003 OR RECON_CYCLE_NBR = 0)
> AND PYMNT_STATUS IN ('P', 'V', 'S') AND PYMNT_ID_REF = ( SELECT
> PS_BANK_STMT_TBL.RECON_REF_ID FROM PS_BANK_STMT_TBL WHERE
> PS_BNK_RCN_DISBRSE.PYMNT_ID_REF = PS_BANK_STMT_TBL.RECON_REF_ID AND
> PS_BNK_RCN_DISBRSE.PYMNT_AMT = PS_BANK_STMT_TBL.RECON_TRAN_AMT AND
> PS_BNK_RCN_DISBRSE.PYMNT_DT <= PS_BANK_STMT_TBL.RECON_BANK_DT AND
> PS_BANK_STMT_TBL.RECON_STATUS IN ('UNR') AND
 PS_BNK_RCN_DISBRSE.BNK_ID_NBR
> =
> PS_BANK_STMT_TBL.BNK_ID_NBR AND PS_BNK_RCN_DISBRSE.BANK_ACCOUNT_NUM =
> PS_BANK_STMT_TBL.BANK_ACCOUNT_NUM AND PS_BANK_STMT_TBL.RECON_CYCLE_NBR =
> 0000000003 AND PS_BANK_STMT_TBL.RECON_BANK_DT = TO_DATE('2099-08-17',
> 'YYYY-MM-DD'))
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
 --
> ----
> Parse 1 0.06 0.06 0 0 0
> 0
> Execute 1 4.30 4.30 0 33511 0
> 0
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
 --
> ----
> total 2 4.36 4.36 0 33511 0
> 0
>
> The 4 seconds per update is the issue for me, considering I have over
 3,000
> to do each day. (5+ hours, counting the other issues )
>
> Link to the tkprof file.
> http://24.8.218.197/problem/problem_tkprof.out
>
> And a little more info.
>
> See the waits on one block.
>
> *** 2000.06.06.14.46.09.000
> WAIT #1: nam='db file sequential read' ela= 2 p1=64 p2=6164 p3=1
> WAIT #1: nam='db file sequential read' ela= 2 p1=64 p2=7350 p3=1
> WAIT #1: nam='db file sequential read' ela= 0 p1=64 p2=7345 p3=1
> WAIT #1: nam='db file sequential read' ela= 2 p1=64 p2=32334 p3=1
> WAIT #1: nam='db file sequential read' ela= 0 p1=64 p2=32332 p3=1
> WAIT #1: nam='db file sequential read' ela= 1 p1=64 p2=32331 p3=1
> WAIT #1: nam='db file sequential read' ela= 2 p1=64 p2=2359 p3=1
> WAIT #1: nam='db file sequential read' ela= 2 p1=64 p2=7349 p3=1
> WAIT #1: nam='db file sequential read' ela= 1 p1=64 p2=7356 p3=1
> WAIT #1: nam='db file sequential read' ela= 1 p1=64 p2=7355 p3=1
> WAIT #1: nam='db file sequential read' ela= 2 p1=64 p2=2362 p3=1
> =====================
> PARSING IN CURSOR #1 len=3291 dep=0 uid=208 oct=2 lid=208 tim=16101617
> hv=2161587976 ad='95dffb24'
> INSERT INTO PS_EDT_ACCTLN_WRK ( BUSINESS_UNIT, VOUCHER_ID,
VOUCHER_LINE_NUM,
...
> END OF STMT
> EXEC #1:c=0,e=0,p=56,cr=34707290,cu=68,mis=1,r=90,dep=0,og=4,tim=16101617
> WAIT #1: nam='SQL*Net message to client' ela= 0 p1=675562835 p2=1 p3=0
> WAIT #1: nam='SQL*Net message from client' ela= 0 p1=675562835 p2=1 p3=0
> XCTEND rlbk=0, rd_only=0
> WAIT #0: nam='log file sync' ela= 3 p1=40 p2=0 p3=0
> WAIT #0: nam='SQL*Net message to client' ela= 0 p1=675562835 p2=1 p3=0
> WAIT #0: nam='SQL*Net message from client' ela= 5 p1=675562835 p2=1 p3=0
> =====================
> PARSING IN CURSOR #2 len=128 dep=1 uid=0 oct=3 lid=0 tim=16101629
> hv=2554239735 ad='972832ac'
> select u.name,o.name from obj$ o,user$ u,trigger$ t where t.baseobject=:1
> and t.obj#=o.obj# and o.owner#=u.user# order by o.obj#
> END OF STMT
> PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=16101629
> BINDS #2:
> bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08
> bfp=01094a90 bln=22 avl=04 flg=05
> value=122841
> EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=16101629
> FETCH #2:c=1,e=1,p=0,cr=22,cu=3,mis=0,r=0,dep=1,og=4,tim=16101630
> STAT #2 id=1 cnt=0 pid=0 pos=0 obj=0 op='SORT ORDER BY '
> STAT #2 id=2 cnt=0 pid=1 pos=1 obj=0 op='HASH JOIN '
> STAT #2 id=3 cnt=364 pid=2 pos=1 obj=22 op='TABLE ACCESS FULL USER$ '
> STAT #2 id=4 cnt=0 pid=2 pos=2 obj=0 op='NESTED LOOPS '
> STAT #2 id=5 cnt=0 pid=4 pos=1 obj=69 op='TABLE ACCESS BY INDEX ROWID
> TRIGGER$ '
> STAT #2 id=6 cnt=1 pid=5 pos=1 obj=109 op='INDEX RANGE SCAN '
> STAT #2 id=7 cnt=0 pid=4 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$
 '
> STAT #2 id=8 cnt=0 pid=7 pos=1 obj=31 op='INDEX UNIQUE SCAN '
>
>
>
>
> --
> Robert Fazio, Oracle DBA
> rfazio_at_home.com
> remove nospam from reply address
> http://24.8.218.197/
>
>
Received on Wed Jun 07 2000 - 00:00:00 CDT