Hello

We face the problem thet the booking of material receipts for project items is very slow. for standard items the booking performance is o.k.

We checked with the ORAPROF options and get for always the following result for project items:

------ Profiling value exceeded -----
<fcper><tdpur4120m000>:2005-03-23[12:50:56.265]:
Time (parse) : 0.000000 seconds
SQL statement:
SELECT /*+ FIRST_ROWS index(a ttfgld418100$idx1) */ MAX(a.t$sern) FROM baandb.ttfgld418100 a WHERE a.t$ocom = :1 AND a.t$tror = :2 AND a.t$fitr = :3 AND a.t$trdt = :4 AND a.t$trtm = :5
-------------------------------------
------ Profiling value exceeded -----
<fcper><tdpur4120m000>:2005-03-23[12:50:56.265]:
Time (multi_exec) : 0.000000 seconds
SQL statement:
SELECT /*+ FIRST_ROWS index(a ttfgld418100$idx1) */ MAX(a.t$sern) FROM baandb.ttfgld418100 a WHERE a.t$ocom = :1 AND a.t$tror = :2 AND a.t$fitr = :3 AND a.t$trdt = :4 AND a.t$trtm = :5
-------------------------------------
------ Profiling value exceeded -----
<fcper><tdpur4120m000>:2005-03-23[12:52:21.280]:
Time (multi_fetch) : 85.015000 seconds
SQL statement:
SELECT /*+ FIRST_ROWS index(a ttfgld418100$idx1) */ MAX(a.t$sern) FROM baandb.ttfgld418100 a WHERE a.t$ocom = :1 AND a.t$tror = :2 AND a.t$fitr = :3 AND a.t$trdt = :4 AND a.t$trtm = :5
-------------------------------------
As the table has more then 10 million records we did not reorganize it yet.

Do you now a similar problem, and if, how did you solve it?

Thanks for your help

/lakoon

It's a simple query using key fields in the table. You may want to run oracle's statspack at the same time to see what oracle is waiting on. I think you'll find
that its I/O. You can alleviate that somewhat by doing a reorg of the table as well as generating statistics on the table (using dbms_stats if you're 9i or higher)

Dave

Hello Dave

We will do the reorg. The oracle version we are running is 7.3.4....
How can we check in oracle, which tools for this version?

Thanks for your help

/lakoon

Hi Lakoon,

for SQL you can use utlbstat/utlestat (located in $ORACLE_HOME/rdbms/admin) I believe then you can upload the results
to www.oraperf.com to get a readable report.

for the statistics do
analyze table <table name> estimate statistics sample 35 percent;

Dave

Hello Dave

Thank you, we'll have a try on it. I will update with the resutls.

/lakoon

Something else that could go wrong:
- index 1 is not available/corrupt. I don't think this is the problem as 85 seconds is than probably too less
- indexes are fragmented. Because 418 can have a lot of inserts at the end and deletes at the beginning, so indexes specialy in older versions can get imbalanced.

I think that a recreation of index 1 followed by an generating of statistics will help. As you use 7.3.4 I doubt if a reorg can be done when users try to access the table, so I advise to do this during a weekend.

If this doesn't help (sufficient) there are other things that can be investigated. These are the steps that can be done at that moment
- checking the excecution plan of the query
- checking the cost in Oracle
- change application query so that performance becomes better.

Hope this helps,
Dick

Hello Dick

We have run the program serveral times with ORAPROF. Sometimes we got up to 120 seconds for the execution.

I have figured out that the table tfgld410 and tfgld417 are also out of balance to tfgld418 (269 records). We started to run otfcor0232 but have not found the difference yet.

How can we only recreate index 1?

/lakoon

drop index 1 of the table and recreate it by SQL*Plus.
As I don't have access at this moment to the Oracle docs nor the Baan DD of tfgld418 you have to wait for another or till tomorrow.

Kind regards,
Dick

We are the company with the problem described by lakoon.
Last weekend we reorganized tfgld418. This solve our performance problem.
I think the problem was the corrupt index1.

Thomas