cancel
Showing results for 
Search instead for 
Did you mean: 

FETCH in ST05 is huge; what is the FETCH and how do I tune it

Former Member
0 Kudos

We have been running queries and loading PLM material for aircraft parts and the system is really slow. I captured the sql statement via ST05 and found the highest time periods were not inserts but rather the highest was the FETCH operation.

The explain statement shows execution plan shows 1 and the proper index. The optimizer is working properly.

What is the FETCH and how can we tune the fetch process to reduce the time to respond.

Thanks

Mikie

Accepted Solutions (0)

Answers (7)

Answers (7)

Former Member
0 Kudos

Here is the info (hopefully this is what you wanted correct)

Fields of MAKT

Fld name Position Data Type Length Decimals Not null Default

MANDT 1 VARCHAR2 9 X '000'

MATNR 2 VARCHAR2 54 X ' '

SPRAS 3 VARCHAR2 3 X ' '

MAKTX 4 VARCHAR2 120 X ' '

MAKTG 5 VARCHAR2 120 X ' '

Indexes for MAKT

Unique index MAKT~0

MANDT

MATNR

SPRAS

Index MAKT~M

MANDT

SPRAS

MAKTG

Plus checking DB20 shows for MAKT:

Statistics are current (|Changes| < 50 %)

Thanks

Mikie

Former Member
0 Kudos

This is the view which is a join of mara and makt:

MANDT CLNT 3 Client

MTART CHAR 4 Material Type

YMAT_LIN CHAR 6 Line Item Number

MATNR CHAR 18 Material Number

SPRAS LANG 1 Language Key

MAKTX CHAR 40 Material Description (Short Text)

Thank

Mikie

Former Member
0 Kudos

Hi,

Sorry, I didn't explain properly. With definition I mean the SQL definition.

You can get it on SE11 -> Utilities -> DB object -> display.

Can you also post here the statistics of the makt indexes?

Former Member
0 Kudos

This is my select.....so why doesn't the select use the table access by Index Rowid MAKT?

SELECT

/*+

first_rows

*/

*

FROM

"YMAT_VEN"

WHERE

"MANDT" = :A0 AND "SPRAS" = :A1 AND "YMAT_LINE" LIKE :A2

And then the execution plan is:

SELECT STATEMENT ( Estimated Costs = 1,367,619 , Estimated #Rows = 368,588 )

6 FILTER

Filter Predicates

5 NESTED LOOPS

( Estim. Costs = 1,967,619 , Estim. #Rows = 368,588 )

Estim. CPU-Costs = 48,732,198,765 Estim. IO-Costs = 1,363,641

2 TABLE ACCESS BY INDEX ROWID MARA

( Estim. Costs = 560,888 , Estim. #Rows = 368,588 )

Estim. CPU-Costs = 30,830,111,252 Estim. IO-Costs = 557,877

Filter Predicates

1 INDEX RANGE SCAN MARA~T

( Estim. Costs = 18,126 , Estim. #Rows = 5,408,050 )

Search Columns: 2

Estim. CPU-Costs = 1,187,191,534 Estim. IO-Costs = 18,010

Access Predicates

4 TABLE ACCESS BY INDEX ROWID MAKT

TABLE ACCESS BY INDEX ROWID MAKT

( Estim. Costs = 3 , Estim. #Rows = 1 )

Estim. CPU-Costs = 36,867 Estim. IO-Costs = 3

3 INDEX UNIQUE SCAN MAKT~0

Thanks

Mikie

Former Member
0 Kudos

Hi,

YMAT_VEN seems to be a view created by you.

You should provide also the view definition.

It looks like the CBO is going for index MARA~T.

But the conditions is on SPRAS ( belongs to MAKT ) and YMAT_LINE. This one seems also a non standard SAP field, I cannot say to wich table it belongs.

a "quick and dirty" look make me think that index MAKT~M could be interesting. But it would be necessary to know why is not taken by the CBO. For that it is necessary to look at the statistics of the tables/indexes involved to figure out the alternative cost.

Another option could be ( if YMAT_LINE is a field from MAKT ) to create an index on this fields.

More ideas is to tune the query.

1) you are usin first_rows hint, but you are not indicating the rows you want to be returned. This is done automatically by the DBI interface, so I assume that this is not an ABAP query.

2) you are using oracle 10g ( I assume all paramerters are properly set ), perhaps it is better to use the hint first_rows(n) instead.

Former Member
0 Kudos

Hi,

The explain plan shows you the cost that is calculated by the oracle CBO.

a value of 1 does not mean it is the best. it is a number that is compared with the cost of other options that oracle also calculated.

A bug, wrong statistics, parameters ... could cause the CBO to choose the "wrong" access path.

For that reason it is necessary to analyze the query that is running slow.

Usually I check not only the SQL but also ALL the indexes that the table(s) involved have.

If the query only has one table and no "customer" indexes are involved you can put here the query and the table/indexes statistics ( you can see them if you click on the table on the explain plan. )

former_member204746
Active Contributor
0 Kudos

would you share the SQL command it is trying to run?

as for issues with listener, forget about this, it will not explain your problem.

Former Member
0 Kudos

hello

please check the envirnment variables

like $ORACLE_HOME , $ORACLE_SID

And are able to login to oracle

  1. sqlplus " / as sysdba"

Check the Oracle listener

  1. lsnrctl status >> this should display you the status of listener

Former Member
0 Kudos

Fetch is dataset which created based on the query passed.

If the fetch is taking longer time. check the select statement

do note use select * from xyz.

in select statement give the required column name , and

in filter condition see that is done on the key columns.