Expensive SQL Statements

Share on Facebook12Share on LinkedIn0Tweet about this on TwitterShare on Google+0
Please Share!!

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

a) User point of view: When transactions using these statements are executed, the response time is large.
b) Systems point of view: A large number of Data Blocks are scanned to find the selected records.

Why check Expensive SQL statements?

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

a. High CPU load on database server.
b. Many blocks are moved from database buffer which results into bad cache hit rate for other SQL statement.
c. Data base busy reading large number of blocks.

Expensive SQL Statements

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 with high database request time/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 clause
c. Index used
d. Name of transaction and report containing the statement

[adToAppearHere]

To get the above details, where to go?

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]

ST03

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

Also Read: ‘Efficient ways to select Orders’

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 –cannot be tuned by us
d) Recursive SQL statement – cannot 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.
DBACOCKPIT

If you want to get practical issues and resolutions straight to your inbox, please SUBSCRIBE. We respect your privacy and take protecting it seriously.

If you like our page, please hit the share button.

Thank you very much for your time!!

 

Image source: imgarcade.com

Share on Facebook12Share on LinkedIn0Tweet about this on TwitterShare on Google+0
Please Share!!

About the Author

Vinay Singh
Vinay Singh
Qualification: BE from Bharati Vidyapeeth, India. MBA from Mannheim Business School, Germany. SAP BI/HANA Experience since 2006. Author of books : ‘Real Time Analytics with SAP HANA‘ & ‘Creating and Using Advanced DSOs in SAP BW on SAP HANA‘. Find more about him on LinkedIn.

Be the first to comment on "Expensive SQL Statements"

Leave a comment

Your email address will not be published.


*