Expensive SQL statements:-

These are defined as sql statements that cause database to read many blocks from disk or buffer.

User point of view: when transactions using these statements are executed, the response time is large.

Systems point of view: A large number of Data Blocks are scanned to find the selected records.

Why checking Expensive SQL statements:

a. Work processes are blocked by reports, thereby increasing the wait time for other processes.

b. a.High CPU load on database server.

c. b.Many blocks are moved from database buffer which results into bad

cache hit rate for other SQL statement.

d. c.Data base busy reading large number of blocks.

so an Expensive SQL statement reduces the performance of SAP system

Finding out the culprits:-

1. SQL statements with higher number of buffer gets.

2. reports/transaction where t he database request time

of response time.

Once we are able to find out the statements, we need to find out following for each statement:-

a.Table name.

b.WHERE cluase

c.Index used

d. Name of transaction and report containing the statement.

To get the above details:-

• Goto DBACOCKPIT ->performance->SQL statement Analysis->shared cursor cache [sap net weaver]
• Goto ST04 -> Detailed Analysis Menu -> SQl Request -> Sort by disk reads/ buffer gets / executions.[for others]

SM 50/66, ST05 and ST03/STAD can also be used to find expensive SQL statement.

Expensive SQL statements can be categories under following heads:-

a) SQL statements which are used by ABAP programs. - These statements can be tuned.

b) SQL Statements used by database – cannot be tuned by us.

c) SQL statements selected from SAP Basis tables –can’ t be tuned by us

d) Recursive SQL statement - can’ t be tuned by us

Tuning the tunable statements …

It is done under two heads, depending on the scenarios:-

a) Case 1:- you see many buffer gets * but only few records per execution.

We can speed up the execution of SQL statements in such case by:-

- Updating the optimizer statistics.

- Creating/extending/dropping existing indexes.

- Optimizing the user input.

a) Case 2:- you see many buffer gets* and many records per execution.

In such cases, we can speed up the execution by:-

- Adapting the ABAP code, replacing “* “from the statements [“SELECT* FROM…..”]

With list of fields that are actually used by the program.

- Optimizing the user input.

- Tuning the business process.

· * you can see the buffer get @ DBACOCKPIT->performance->SQL statement Analysis->shared cursor cache (double click here)->new screen for selection criteria, put your value in buffer gets field.