Question:
There is a Z background job for our ISU-Billing which used to take close to 2Hrs however since last week it has started taking more time considerably. Yesterday it took almost 5Hrs and today about 5Hr 46 Minutes. Each day the time has started increasing..
The job is scheduled to run at Odd hours like midnite and at tht time there is almost nil load on the system.
I activated the trace ysterday thru ST05 and saw the execution plan for most of the sql statements and the estimated cost is "1". Also in DB20, I checked the statistics of the tables and which were correct.
SE14, checked for any missing indexes, found none.
In ST03N, for the respective job the info shows as:
# of dialog steps: 112
Response time/dialog step: 14522.8
CPU Time:7918.9
DB Time/dialog step: 8025.9
Wait time 0.2
Roll Wait 0.0
Load + Gen Time: 6.2
KB 201693
I am wondering what else could be the issue as earlier it used to take 2 hours however each day it has started taking almost 45 minutes more than its last run.
Answer:
Hi,
Can you check with your ABAPper what the program is supposed to be doing? Sometimes the program might be deleting/comparing.. before writing/updating anything. From the increase in timing it would seem that this is the behavior of the program.
Dondi
Answer:
if a few tables are growing a lot, it might normal...
check with your ABAPPER to possibly add an index
_________________
SapFans Moderator
NetWeaver ‘04–SAP Web AS for ORACLE certified
Search: /forums/search.php
SAP Notes: http://service.sap.com/notes
SAP Help: http://help.sap.com
Basic Rules: /forums/viewtopic.php?t=222759
Answer:
Hey there Akapu,
We have ISU also. What tables these jobs access? DFKKOP ? Remember these are HUGE. If you can post the offending sqls and/or tables involved I could give you some hints .... Statements with low cost are not necessarily fast-responding. You could have a query that uses index with mandt only, and to optimizer it looks like cost 1 but really the index is non-selective. So it takes a looong time to complete.
Rgds,
Claudio
Answer:
Thanks Mitaboy,
the tables that bill printing reports access are:
FKKVK, DFKKOP, ILOA, V_EGER_H, DBERCHZ4, EVBS, EHAUISU, DBERCHZ3, DBERDU, V_EANL, EANL, ERDK, DBERCHZ1, ERCH, IFLOTX,
DBERDTAX, EGERR, ETTIFN, ERCH, DFKKZP, EASTL, ADRC, BUT000,
EABL, EVBS, DPAYH
In order to get the offending SQLs, I am planning to run ST05 or STAT on that job. The problem is that job runs after midnite and its hard to run STAT on it and keep watching until morning.
Is there any way to look at STAT even after the job has finished, coz each time I try to do it, the history is washed off as soon as the report/transaction is over ??
Answer:
Try using STAD.
Also try increasing the size of Moni table......this will help store more information.
urbane4u
KNOWLEDGE IS THE ANTIDOTE OF FEAR
_________________
Hey we too can be good friends
Mail me at:
urbane4u@rediffmail.com
Answer:
Hi Akapu sorry for getting back late ...
ERCH has expensive joins with ERCH0, same goes for EABL/EABLG.
Watch for these.
Rgds,
Claudio
Answer:
Thanks Mitaboy,
The Function team says that the 3 tables DFKKOP, DFKKZP, and DPAYH are the main ones. Actually this is the IVR & Bill Print Job.
How about considering table buffering on these 3 tables? Will that help?
Btw yesterday that job took 6 hours, and I have no clues whats happening.
Also as you said ERCH has expensive joins with ERCH0, same goes for EABL/EABLG. Can you please let me know how to check this even though the ISU functional says its not imp as they r not hitting that table so much but still I wud like to verify on it.
urbane4u: I used STAD and increased the size of moni too however still whenever the transaction/program is over, the STAD results of that program vanishes off. I guess this is how STAD is designed by SAP.
I may be wrong too? Any clues??
Answer:
Hi Akapu,
You should buffer tables at Oracle level only if these conditions are met:
1) Table fits entirely in the Memory area (SGA)
2) Table is not updated frequently.
With tables DFKK*, ERCH*, etc. both conditions are not met, because they are really fast-growing. I would check statistics for the tables, maybe the access path is off.
Is this a fresh ISU installation ? If so it is a good time to consider partitioning the tables. Unfortunately when I arrived at my company the project was 2 years in and tables were already beyond 30GB , so there's no downtime to partition. But in your case it might still be possible. Also check st04, hit ratio. Maybe your db_cache has not enough memory assigned.
Rgds,
Claudio
Answer:
hi mitaboy,
Yes this is a fresh installation. We went live with ISU just about 2 months ago.
Currently DD Cache Quality is 92%
Shared Pool
Size kb 1,146,880
DD-Cache quality % 92.2
SQL Area getratio % 98.3
pinratio % 99.9
reloads/pins % 0.0206
Also I know that our DB Buffer Quality is running quite lower that what it should however it is not possible for us to tune it at the moment because of tight work schedules on lot of other things like BW/SEM, Solution Manager, SRM Upgrade.
Data buffer
Size kb 1,132,640 Reads 10988,331,590
Quality % 69.7 Physical reads 3,331,415,933
writes 10,019,251
Buffer busy waits 54,686,507
Buffer wait time s 36,469
The buffer quality has been same since last so many months however we never use to face this bill print job problem that we are having right now.
What this job does it that it accesses fkkvkp, fkkop, fkk_sec, but000, erdk,
zisuf001_erdk, dfkkzp, dfkkop, ZDEV_CAT_MTR_SZ, egerh, iflotx, eanlh tables only and then create a output file at the AIX level and then FTP that file to the bank.
I see that FTP is not taking much time however the main job is taking lot of time. In your ISU system also there would be some same kinda job as I believe its a part of ISU functions and as you say those tables are of 30 GB then what is the performance enhancment technique you guys are using?
Currently there are following no. of records in these tables:
FKKVKP - 250,929
FKK_SEC - 88175
BUTOOO - 267,737
ERDK - 285,574
zisuf001_erdk - 283,415
dfkkzp - 241,036
DFKKOP- 1,168,137
ZDEV_CAT_MTR_SZ - 11
egerh- 453,488
iflotx - 457,460
EANLH - 385,718
The biggest is DFKKOP, followed by EGERH & IFLOTX.
Btw yesterday it took 27,000 Secs, as I said its increasing for 45 minutes daily.
To understand table partitioning better, can you please tell me how is it going to work? I mean lets say we have partition A & Partition B so will there be 2 jobs running simultaneously on these tables, instead of currently 1 job running on whole table? Can you please give me a lil bit brief explanation about this concept?
Also as I said earlier how to check expensive joins?
Answer:
Hi Akapu, sorry for answering late..
Well, your tables are still not very big, my DFKKOP has tens of millions of records. What we do is we bill our customers in batches. Say, since these are grouped by district, we bill them in 30 groups, one group daily. So we bill all 700k customers once per month. However this is a functional strategy. (and business process)
Your hit ratio is OK, it still is above 90% which if not optimal, is not tragic yet .. Maybe you should check out disk bottlnecks AND, run this query in sqlplus while the job is running:
SQL > select a.sid,machine,PROCESS,event,p1 pe1,p1text text1,p2,p2text text2,p3,seconds_in_wait sec
from v$session_wait a,v$session b where
event not like 'SQL%' and event !='rdbms ipc message'
and event !='smon timer' and event!='pmon timer' and a.sid=b.sid ;
This will show what bottlenecks Oracle is facing when the background job is running. Post 'em when you have them K?
For partitioning you should review the most expensive SQL's affecting these tables (in select) and decide which tables are good candidates for partitioning. (same as when creating an index, those with good selectivity are best, for example, partition by date)
Rgds,
Claudio
Answer:
Hey, I hadn't seen that hit ratio on the data buffer, 69% ?? Weel that could explain something Akapu.
What do U mean by DD cache quality?
The ones that matter are data buffer and shared pool.
If you had previously only R/3 and then added ISU, beware, you need a sizing to be done on your system, one specifically for ISU that takes into account as # customers, hourly billing rate, etc.
Was this done ?
PD Snowy, got that link to OSS to download the sizing tool ?
Answer:
?
PD Snowy, got that link to OSS to download the sizing tool ?
http://service.sap.com/sizing
_________________
SapFans Moderator
NetWeaver ‘04–SAP Web AS for ORACLE certified
Search: /forums/search.php
SAP Notes: http://service.sap.com/notes
SAP Help: http://help.sap.com
Basic Rules: /forums/viewtopic.php?t=222759
Answer:
Thanks for replying back.
Well I am sure sizing would have ran before installing ISU and yes you are correct R/3 was there since last 3 yrs however ISU Go-live went in may 2006.
We currently have 16GB of RAM and after researching OSS, I found that I need to increase the value of db_block_buffer to increase the buffer quality however as per the recommended value increase, the system failed to start. Paging went up to 100% in just few seconds.
I had to revert back the parameter and have already opened OSS for that.
however i will run the query and will let you know results. Also I will perform a sizing soon.
Btw, today there is a increase in buffer quality. its showing 89%. I know it must be some SQL staments causing that however the worst is the bck job has started taking 6.45 Hrs.
Answer:
Hi Akapu
The first sizing is only for R/3 mods, as SD, CO, FI, inventory ,etc.
For ISU you need a server with enough resources to cope with both R/3 demands and ISU, this one places heavy load on hardware. What't the response time of CIC0 as seen in st03 ? From what you tell me I would guess it's 2 seconds or more. CIC0's the online tool for customer service, and it needs "fast" response time.
If the hardware is not adequate no matter how much you tune the application you'll get slow response times
Regards, take care.
Claudio