VBFA Time out dump

[Former Member]

Hi Experts,

We encountered several TIME_OUT dumps due to a select statement in VBFA. All those programs with VBFA select statements are working fine for 2 years already. Recently another index has been created named ZVB03 using the fields MANDT, VBELN, VBELV and VBTYP_V. Aside from the newly created index, an index ZVB01 (VBELN and VBTYP_N) and ZVB02 (VBELN and VBTYP_V) is already in the system.

The ZVB03 index was already in production system last June 9 and did not cause any problems until August 17. An Oracle Patch was applied the weekend before (August 15 if Iu2019m not mistaken) and after this, all sorts of performance issues came out. The patch may have also caused this problem but this weu2019re not exactly sure of.

Are there any possible causes that this patch impact the table VBFA indexes?

Thanks for your response.

Best Regards,

Kurrt

[Former Member]

Hello,

I don't know if this is your problem but make sure all recommended parameters are ok.

[1171650 Automated Oracle DB parameter check|https://websmp202.sap-ag.de/~form/handler?_APP=01100107900000000342&_EVENT=REDIR&_NNUM=1171650&_NLANG=E]

[fidel_vales]

>

> Hi Experts,
> 
> All those programs with VBFA select statements are working fine for 2 years already. Recently another index has been created named ZVB03 using the fields MANDT, VBELN, VBELV and VBTYP_V. Aside from the newly created index, an index ZVB01 (VBELN and VBTYP_N) and ZVB02 (VBELN and VBTYP_V) is already in the system.

As usual, a question with missing information

> An Oracle Patch was applied the weekend before

so do you want us to "guess" the patch that you have installed? I'm afraid that I left my magic ball and the TAROT cards at home.

> We encountered several TIME_OUT dumps due to a select statement in VBFA.

And you do not indicate what is the statement what index was using before and what index is using now (here i think I can guess it is ZVB03)

a little advice:

if you had the following indexes:

ZVB01 - VBELN and VBTYP_N

ZVB02 - VBELN and VBTYP_V

instead of creating this one

ZVB03 - MANDT, VBELN, VBELV, VBTYP_V

perhaps you could have adapted ZVB02.

Of course, to evaluate that we would need to know what kind of queries were using those two indexes and why you have created the new one (for what kind of query)

As we do not have ANY information, we can only guess. Perhaps you are lucky and some people here is able to read the stars and give you a correct answer. Or, perhpas, you began to receive a lot of lucky guesses and one could be helpful.

[Former Member]

Hi Fidel,

Thanks for your response and my apologies that I need to gather other information. The database patch made is to 10G from 9i.

The select statement is:

SELECT SINGLE VBELV

FROM VBFA

INTO v_vbelv

WHERE VBELN EQ nast-objky

AND VBTYP_N EQ 'J'.

Before when there was no ZVB03 and no patch made, it make used of the index ZVB01. However after the patch, the selection of index to be used seems became dynamic. It sometimes used ZVB01, ZVB02 and sometimes ZVB03.

Hope to hear from you soon.

Best Regards,

Kurtt

[Former Member]

Still missing relevant information.

There are various patch levels within Oracle 10g.

SAP only supports 10.2.0.2 and 10.2.0.4.

And for both of them a lot of so called interim patches have to be applied, as described in SAP notes. I haven't their number at hand now, but they may be searched easily.

Anyway, have those interim patches been applied?

regards

PS:

Applying interim patches is not just for fun. They will fix various problems, like performance....

Edited by: Joe Bo. on Sep 25, 2009 9:00 AM

[fidel_vales]

Hello Kurtt,

> The database patch made is to 10G from 9i.

Good, then you have upgraded your database, which is a much bigger thing than only installing one patch.

Upgrading from 9i to 10g is a mayor thing and there are several "post upgrade" steps that must be done. If those steps are missing the probability of situations like the one you mention are "bigger"

Please, check the "new" upgrade guide and bew sure that you have followed ALL steps mentioned there. At the beginning of this forum you find a post "stcik" mentioned where to find the new upgrade uides.

Specially pay attention to:

1) installing a correct patch set (are you in 10.2.0.2 or 10.2.0.4?)

2) installing ALL required patches, specially the optimizer merge fix. Check the following notes:

1137346 Oracle 10.2.0: Patches/patch collections for Oracle 10.2.0.4

1165319 Optimizer merge fix for Oracle 10.2.0.4

871096 Oracle 10.2.0: Patches/patch collections for Oracle 10.2.0.2

981875 Optimizer merge fix for Oracle 10.2.0.2

3) set properly the parameters. You can use the script from note 1171650 already mentioned. The notre with the SAP parameter recommendation is 830576

4) calculate statistics for the oracle data dictionary, fixed tables and system statistics

5) calculate statisitcs for the complete schema.

if the previous steps are not done, not proper analysis can be done.

Note, I'm not saying that those steps will solve the issue (but I have seen lot os performance issues after upgrading to 10g desappearing after they have been properly done) but that no good analysis can be done if one or some are "missing"

If you still have the problem afterwards, it could be ideal that you execute the scrip from SAP note 1257075 (SQL_ID Data Collector (Oracle 10.2.0.2 or higher)) and collect the output.

Please, do not paste the output in a post, because it would be tooooo big, and the format would be "crap" and imposible to read. I think you can attach a file somewhere in the SDN, or may be uploaded to "rapidshare" or any similar place where you can put a txt file.

Edited for format by: Fidel Vales on Sep 25, 2009 10:02 AM

[Former Member]

Hi Kurt,

We had similar issues with VBFA index after we upgraded our DB to 10G from 9i. It was a Custom ABAP Code that was causing the issue. Can you please provide more information on what DB version you are on before/after the patch was applied?

Since you already know it is a Query on VBFA table, try running a trace on the query to see what execution path/index is being used. If it is a Custom Code then you may have to make some changes to the code.