ST04-SQL Request

[Former Member]

Hi Friends,

In my production system, I've executed the ST04 transaction, Detailed Analysis Menu....SQL Request....Sorted by Buffer gets... and I checked the SQL statement....and then clicked on display execution plan for SQL statement...

There it shows the following information

SELECT STATEMENT ( Estimated Costs = 17,039 , Estimated #Rows = 0 )

1 TABLE ACCESS FULL LIPS

( Estim. Costs = 17,038 , Estim. #Rows = 1 )

Estim. CPU-Costs = 885,646,245 Estim. IO-Costs = 16,963

Filter Predicates

What are the parameters that will indicate that there is an issue with the SQL statement

Regards

Aliila

[former_member251078 · Accepted solution]

Hello Aliila,

As indicated below, the statement is expensive as the explain plan goes for full table scan. As LIPS table is one of the line item table, it has to read all the blocks of this table to satisfy the user query. So if there is no WHERE clause in the corresponding ABAP code, then you have to discuss with your ABAP developer to input the selective fields in the WHERE clasue so that appropriate indexes will be choosen in the explain plan. From this i can easily conclude that this statment is expensive because of either of these reasons.

1. Missing Where clause in the ABAP code ( I believe this is coming from Z* program/transaction) or

2. User is not inputting any criteria for selecting this ( Here you to need to train the user).

Back to your question: if you are doing sql cache analysis through ST04 normally you have to find out the statement based on the following criteria.

1. Greater than 5% of the total buffer gets ( This will cause memory load)

and/or

2. Greater than 2% of the physcial reads ( This cause I/O Load).

you can find that total buffers gets and physical read from ST04 initial screen.

Hope above information will help you.

Yours Sincerely

Dileep

[pavan_kumargali]

Hi Aliila,

As informed by Dileep, 'TABLE ACCESS FULL LIPS' is one of the indicator for expensive SQL. And again it depends on the size of the table. If the size is less then some times Full table scan would be better for the statement.

I want to include one more expensive SQL indicator "Estimated Costs = 17,039". Generally Estimated Costs should be in single or double digits, which means SQL statement is performing well.

If you want more information, please let me know.

Thanks and Regards,

Pavan Kumar Gali.

[Former Member]

Hi

Please do also post the sql statement as well, executions, buffer gets, number of rows would help as well.

Regards Michael