on 05-05-2015 2:54 PM
Hi experts,
I am new on Sybase ASE with a just a bit of experience.
We have the problem, that on accessing table COEP we have a very very bad performance since a few days.
The days before it was good with the same read-logic.
We have one index including mandt, objnr and TIMESTMP.
We read with mandt and objnr.
If I analyze with st12 I also can see that the index is used.
I've already checked statistics and did rebuild the index.
however performance is still very very bad.
Does anybody know, what I can do next?
COEP is very big, around 95 MIO entries.
I tried index-check in dbacockpit, but got a connection timeout.
Thx for any ideas.
Kind regards
I already got the answer. Performance impact is to big for that table and therefore I will do it on the weekend.
fyi, -> durring indexing also not insert can be done on the table !!!
BTW, So I did create a new index without the timestamp which solves the performance impact.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
And now the jackpot-question
I did see, that the index is having also the timestamp included.
I did create another index without the timestamp included and it is working pretty well.
And now it comes...we are facing the same problem on all ECC systems, also on systems which are having a much smaller COEP table.
May it be, that the timestamp value has now reached a difference in index-system (as it is always calculated from 01.01.1990 to now) which is causing the problem?
Does anybody else have the same problem?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Christian,
You should always make sure regardless of which DB you are running in conjunction with SAP. The DB should always have updated statistics and an update statistics job running. There are various ways to do this of course depending on your OS+DB combination as well as SAP dbacockpit standards.
Reasons the size of the table would not have impact necessarily on the performance is due to statistics.
If table A 30 million rows has updated statistics the system would know best which index to be used and it will run quick.
If table A 30 million rows has outdated statistics the system migh use inefficient index to read data and case table scan of all rows and be very slow.
This is my understanding of the easiest way to explain it hope it is accurate and make sense.
J
I know, but statistic was fine on each system and best index was used.
However, from one day to the other all systems did switch to slow.
And so it seems to be a kind of "date"-depending thing...which brings me to my opinion back, that it might be the timestamp in the index.
However, I am creating now a new index without the timestamp and it works very good (very fast).
As the index will be generated by the transport and this will take around 30 minutes, can I do the transport/index creation in Live system while peak-hours or should this be done at non-business time?
Can the table be used normally while creating an index via transport?
Kind regards
Hi Christian,
There are also some steps like index compression and database consistency check that you can do for overall improvement. At times the actual select statement itself would be the issue; the component CO-OM-IS is dedicated for this.
Regards,
Hemanth
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Christian,
If you check the index/table statistics in DB20 when last was statistics updated?
Johan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.