Performance Problem SQL on RESB

[Private_Member_19084]

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

[stefan_koehler · Accepted solution]

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