cancel
Showing results for 
Search instead for 
Did you mean: 

Performance Problem SQL on RESB

Private_Member_19084
Active Contributor
0 Kudos

Hi experts,

since a few days we have a big performance impact by sql-statements on tabel RESB.

e.g. we have select-statements, which did take around 10 minutes and now over 3 hours.

We have already checked indizes and table consistent. Also table checks in DB02.

If I make the same sql on our testing system it is still fast, so only in the productive sytem we are facing this problem.

The table size in both systems is similar.

At the moment selecting around 1000 rows in PROD systems takes more than 10 minutes.

In Q System it takes just a few minutes for more than 100 000.

Can someone help to analyze the problem?

Thank you

Accepted Solutions (1)

Accepted Solutions (1)

stefan_koehler
Active Contributor
0 Kudos

Hi Christian,

> If I make the same sql on our testing system it is still fast, so only in the productive sytem we are facing this problem.

Please provide the execution plan of the SQL statement from both systems.

> If I select in SE16 for 500 rows, it is really fast (around 2 seconds). But if I select for 1000 rows (same critery) it takes more minutes.

The SQL is different (keyword ROWNUM <= VALUE) if you limit the amount of rows via SE16. This change can easily lead to a different execution plan.

> Only for the last block it takes very long (marked line)

It is not a block, it is a FETCH call and the amount of (database) work can vary tremendously with(in) each FETCH all.

You can also provide the real time SQL monitoring report (for that slow SQL), if you are running on Oracle 11g R2. This report includes the most important information in first place.


SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SPOOL /tmp/report_sql_monitor.htm

SELECT DBMS_SQLTUNE.report_sql_monitor(

  sql_id => '<SQL_ID>',

  type => 'HTML',

  report_level => 'ALL') AS report

FROM dual;

SPOOL OFF



Regards

Stefan

Answers (2)

Answers (2)

0 Kudos

Hi Cristian.

You can check the SQL Statments with high cost to the system.

Check this in ST04 or dbacockpit, > Performance > SQL Statment Analysis > Shared Cursor Cache.

On right hand screen you can filter the SQL Statments and show the cost through EXPLAIN buton and check the Source Code in 'ABAP Source" Buton.

In this point you have conditions to analyse whether you need to create a new Index or send the Program Code to be revised by Abap team.

Check the table statistics in DB20 tcode.

regards,

Mauricio Montoya

Private_Member_19084
Active Contributor
0 Kudos

Now I did also findout.

If I select in SE16 for  500 rows, it is really fast (around 2 seconds).

But if I select for 1000 rows (same critery) it takes more minutes.

former_member235680
Active Participant
0 Kudos

Hi Christian,

Do you have any secondary index(assuming you are using ORA DB) on table RESB?

Cheers

Abhi

Private_Member_19084
Active Contributor
0 Kudos

Hello Abhi,

first thx for help.

The select is done with the same index (I did analyze with ST12).

But one thing is different in ST12.
For 700 entries the ST12 displays 148 "DB Interface Cursor".

And for 800 entires only 9 cursor.

May this be the answer?

Now I also try to analyze with st05

former_member235680
Active Participant
0 Kudos

No Problem Christian. Also, have a look on below thread,

Private_Member_19084
Active Contributor
0 Kudos

I've now done also an ST05 analyse and found out something very interesting (see graphic).

Only for the last block it takes very long (marked line)=.

Any idea for this?

former_member235680
Active Participant
0 Kudos

Try running tx-code SAT. It gives you very detailed and perfect picture, where exactly it is taking the time.

Private_Member_19084
Active Contributor
0 Kudos

I think that SAT is not good for this analyse, because it is ABAP trace and would show which selects takes how long.

But in ST05 I did already see, that it is an SQL/DB problem.

Do you know other transactions for this?

former_member235680
Active Participant
0 Kudos

I would say pls have a look on your secondary indices in use. I have shared a thread already, request you to please go through that, on more info regarding index.