cancel
Showing results for 
Search instead for 
Did you mean: 

Bad performance on table access - how to analyze?

Private_Member_19084
Active Contributor
0 Kudos

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

Private_Member_19084
Active Contributor
0 Kudos

do I have to turn off the showplan/statistics also after that?

Thank you

Private_Member_19084
Active Contributor
0 Kudos

ok, I have to turn it off also...got it

1st OUTPUT

The sp_help says incorrect syntax near '.', how do I have to execute it?

I did sp_help SAPSR3.COEP...?


==================== Lava Operator Tree ====================


            Emit
            (VA = 1)
            r:10 er:5887
            cpu: 31300


/
IndexScan
COEP~4
(VA = 0)
r:10 er:5887
l:139103 el:2622
p:137678 ep:2615

============================================================
Table: SAPSR3.COEP scan count 1, logical reads: (regular=139103 apf=1269 total=140372), physical reads: (regular=103136 apf=34542 total=137678), apf IOs used=34330
Total writes for this command: 283

Execution Time 313.
Adaptive Server cpu time: 6180 ms.  Adaptive Server elapsed time: 131388 ms.

(10 rows affected)

crisnormand
Active Participant
0 Kudos

Hello,

You wrote "on accessing table COEP we have a very very bad performance":

Which is the exact query that is executed with bad performance, is it the sp_help?

If not, were you able to identify it (for example with ST05), could you clarify what means exactly "on accessing table": delete, select, update, all of them? ?

Also, could you please specify the ASE and ODBC version, as well as the DBSL version?

Thanks!

Regards,

Cris

Private_Member_19084
Active Contributor
0 Kudos

We have one abap select on the COEP.

And at the moment I am testing with Se16.

Entering just one objnr and get 10 rows as result.

We have one index including mandt, objnr and TIMESTMP.

We read with mandt and objnr.

I have done an ST12, where I can see that index is used which is including mandt, objnr and timestmp.

After that I rebuild the index.

Sybase ASE 15.7.0.1300

DBSL Version is 721.02

Where can I find ODBC Version?

crisnormand
Active Participant
0 Kudos

Hello,

> Where can I find ODBC Version?

In any worker process log file (dev_w*.log) or executing RSSYBDBVERSION from SE38 (this is a complete report that will show you the versions and patches of all components of your SAP system).

If you are testing from SE16 I guess you are using  option "Explain Access Plan", and you can see there the index is used.

Another idea to identify the problem would be to get an sp_sysmon output (from an isql session logged as sapsa) while the query is running (how long it takes now btw and how long it used to take?), or from the DBA Cockpit analyzing the Data Collectors during the time frame the query is running (if you configured the DCF).

Thanks

Regards,

Cris

Private_Member_19084
Active Contributor
0 Kudos

Hi Cris,

I did the analyze with st12 -> explain.
I try again with dbacockpit.

Accepted Solutions (1)

Accepted Solutions (1)

Private_Member_19084
Active Contributor
0 Kudos

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.

Answers (4)

Answers (4)

Private_Member_19084
Active Contributor
0 Kudos

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?

Johan_sapbasis
Active Contributor
0 Kudos

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

Private_Member_19084
Active Contributor
0 Kudos

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

Johan_sapbasis
Active Contributor
0 Kudos

Hi,

Run update statistics on the index and table on the system you are looking at and you will see the date stamp will change so in that sense yes the newer the statistics the better.

J

Private_Member_19084
Active Contributor
0 Kudos

I think you missunderstood.

In the index we had the 3 fields of the table COEP.

MANDT, OBJNR and TIMESTMP of the table COEP

hemanth2
Product and Topic Expert
Product and Topic Expert
0 Kudos

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

Johan_sapbasis
Active Contributor
0 Kudos

Hi,

For statistisc update refer to helpful post from

I would rather update the index statistics first, update table statistics will run a long while for COEP being such a large table.

Kind Regards,

Johan

Johan_sapbasis
Active Contributor
0 Kudos

Hi Christian,

If you check the index/table statistics in DB20 when last was statistics updated?

Johan

Private_Member_19084
Active Contributor
0 Kudos

I did one today manually