High Database server load from expensive SQL statements

[Former Member]

Dear all,

I am facing problem in the production sever there is high Database server load from expensive SQL statements as per EVA report

Buffer Load [%] Disk Load [%] CPU Load [%]

55 69 0

Analysis of DB SQL CACHE

EXPENSIVE SQL STATEMENTS OVERVIEW

Object Name CPU Load [%] I/O Load [%] Elapsed Time [%] Executions Records Processed

BSIS 1 9 0 22 90462

CDEF$ 6 6 0 2131113 2575694

BSAD 1 3 0 21 408576

MKPF 1 32 0 180 3899

ICOL$ 9 1 0 2575694 8703798

OBJ$ 6 1 0 3405254 3400023

COL$ 12 0 0 2138793 22919657

MKPF 1 13 0 75 396

MCHB 7 0 0 366543 41708

Please suggest the step by step to reduce the expensive SQL from these table.

Regards

[Former Member]

Mohan,

Please check following two things

1. Analyse table buffering using ST10 (table call statistics)

2. Analyse sql statements using ST05 (SQL Trace)

Check if these expensive sql statements are coming from Z* programs and get abaper to have a look at them.

Hope this helps.

Manoj

[Former Member]

Hi,

In tx code ST05 i have find the following deatails.

Duration |Obj. name |Op. |Recs.|RC |Statement

4 TSP03A REOPEN 0 SELECT WHERE "NAME" = 'LOCL' AND "P" = 'S_CLIENTS' 344 TSP03A FETCH 1 0 42 ZRIN DECLARE 0 SELECT WHERE "MANDT" = :A0 AND "BCQ" = :A1 442 ZRIN PREPARE 0 SELECT WHERE "MANDT" = :A0 AND "BCQ" = :A1 3 ZRIN OPEN 0 SELECT WHERE "MANDT" = '600' AND "BCQ" = 'BCQ'

2,181,565 ZRIN FETCH 4 1403

706,267 DBA_SEGME FETCH 99 0 8,248 DBA_SEGME FETCH 99 0 44,994 DBA_SEGME FETCH 99 0 67,713 DBA_SEGME FETCH 99 0

1,367,923 DBA_SEGME FETCH 99 0

16,735|TADIR |FETCH | 1| 1403| |

5	TADIR	REOPEN		0	SELECT WHERE "PGMID" = 'R3TR' AND "OBJECT" = 'TABL' AND "OBJ_NAME" = 'CME__TEXT'
22,415	TADIR	FETCH	1	1403
4	TADIR	REOPEN		0	SELECT WHERE "PGMID" = 'R3TR' AND "OBJECT" = 'TABL' AND "OBJ_NAME" = 'CME_PATTERN_SIGN'
232	TADIR	FETCH	1	1403
4	TADIR	REOPEN		0	SELECT WHERE "PGMID" = 'R3TR' AND "OBJECT" = 'TABL' AND "OBJ_NAME" = 'CML_ARC_DEADLINE'
19,189	TADIR	FETCH	1	1403
4	TADIR	REOPEN	706,267	DBA_SEGME	FETCH	99	0

|

44,994	DBA_SEGME	FETCH	99	0
67,713	DBA_SEGME	FETCH	99	0

Please suggest how to reduce it.

Regards,

[stefan_koehler]

Hello Mohan,

go to ST04 -> Detailed analysis menu -> SQL Request

..or..

use STATSPACK for Oracle 9i

..or..

use AWR for Oracle 10g.

If you have identified the expensive SQL statements on the tables you have to check why they are expensive (CPU time, I/O load, etc.) and tune them.

But if you ask these basic questions i think it will be hard to tune the statements. I would suggest to buy some external support, if you have performance problems.

Regards

Stefan