In this case I am talking about a custom session. We recently upgraded to a new SP and Oracle 10g. This had horrible performance - an Oracle profile was added and the session runs fine. Well, because of other issues we set all recommended parameters, bounce the database. When it comes backup - we test this session just fine. The next day the users hit the session and we are back to horrible performance. The DBA's remove the profile and then the session works fine. Now two weeks later(yesterday) the session starts performing bad again - DBA's add the Oracle profile and the session is back to normal. So far none of the other session problems have come back since we set the suggest parameters - again,so far just this one session cropped back up as a problem. Any suggestions on what might cause something like this?

I included one of the main queries on the session ---please note that I did not write either of these queries --- I want to rework the query and break it up, but so far we have agreed to wait and see if the problem comes back again. Is there something in 1 of these queries which might cause problems - maybe something like UNREF CLEAR or refers to???? The two queries are called based on input parameters - query 1 is below:

select tipgc520.*,
tppdm065.desc, | Project Group description
tccom020.clan, | Supplier Language
tccom020.nama, | Supplier Name
tcmcs002.crnd, | Currency rounding factor
tcmcs046.lang, | Language
tbuyr.nama:buyr.nama, | Buyers name
tcplb.nama:cplb.nama, | Planners name
tiitm001.citg, | Item Group
tiitm001.cuni | Conversion
,tiitm001.dsca, | Descritpion |#40b2a&d2.n
tiitm001.csig | Signal Code
from tipgc520, tppdm065, tccom020,
tcmcs002, tccom001 tbuyr, tccom001 tcplb,
tiitm001
where tipgc520._index1 inrange {:ccot.f, :orno.f}
and {:ccot.t, :orno.t}
and tipgc520.item between :item.f and :item.t
and tipgc520.suno between :suno.f and :suno.t
and tipgc520.podt between :podt.f and :podt.t
and tipgc520.osta between :osta.f and :osta.t
and tipgc520.buyr between :buyr.f and :buyr.t
and tipgc520.cplb between :cplb.f and :cplb.t
and tiitm001.citg between :citg.f and :citg.t
and tipgc520.osta <> tcorsa.actualized | Transfered
and tipgc520.item refers to tiitm001
and tipgc520.ccot refers to tppdm065
and tipgc520.suno refers to tccom020 UNREF CLEAR
and tccom020.clan refers to tcmcs046 UNREF CLEAR
and tipgc520.ccur refers to tcmcs002
and tipgc520.buyr refers to tbuyr UNREF CLEAR
and tipgc520.cplb refers to tcplb UNREF CLEAR
order by tipgc520._index1
selectdo


Query 2 is run when the user includes the project and CCN

domain tcamnt discount.amount | to calculate discount |552.sn
domain tcamnt net.amount | ,, ,, ,, |552.en

select tipgc820.cprj, tipgc820.cspa, tppdm600.cprj, tppdm600.ccot,
tipgc521.*, tipgc520.*, tppdm065.desc, tccom020.clan,
tccom020.nama, tcmcs002.crnd, tcmcs046.lang, tiitm001.citg,
tiitm001.cuni, tiitm001.dsca, tiitm001.csig,
tbuyr.nama:buyr.nama, tcplb.nama:cplb.nama
from tipgc820, tppdm600, tipgc521, tipgc520, tppdm065, tccom020,
tcmcs002, tcmcs046, tiitm001, tccom001 tbuyr, tccom001 tcplb
where tipgc820._index2 inrange {:cprj.f, :cspa.f} and
{:cprj.t, :cspa.t} and
tipgc820.cprj refers to tppdm600 and
tppdm600.ccot inrange :ccot.f and :ccot.t and
tipgc521.cprj = tipgc820.cprj and
tipgc521.butm = tipgc820.butm and
tipgc521.sbtm = tipgc820.sbtm and
tipgc521.eser = tipgc820.eser and
tipgc521.eseq inrange tipgc820.unfr and tipgc820.unto and
tipgc520._index5 = {tipgc521.orno} and
tipgc520.item inrange :item.f and :item.t and
tipgc520.suno inrange :suno.f and :suno.t and
tipgc520.podt inrange :podt.f and :podt.t and
tipgc520.osta inrange :osta.f and :osta.t and
tipgc520.buyr inrange :buyr.f and :buyr.t and
tipgc520.cplb inrange :cplb.f and :cplb.t and
tiitm001.citg inrange :citg.f and :citg.t and
tipgc520.osta <> tcorsa.actualized and
tipgc520.item refers to tiitm001 and
tipgc520.ccot refers to tppdm065 and
tipgc520.suno refers to tccom020 UNREF CLEAR and
tccom020.clan refers to tcmcs046 UNREF CLEAR and
tipgc520.ccur refers to tcmcs002 and
tipgc520.buyr refers to tbuyr UNREF CLEAR and
tipgc520.cplb refers to tcplb UNREF CLEAR
order by tipgc520.ccot, tipgc521._index1
selectdo

Did you run this with -profile flag on for some small sub-set of data?

I am not at my computer right now, but the first select looks familiar - must be from the Print Planned GRP Purchase Orders. It seems o.k. because nobody here has reported any performance issues with it. To locate the problem table, the only suggestion I can make is to start by striping out all the tables except tipgc520 and tipgc521 and then progressively add tables till performance breaks down - not very efficient.

The second select on the other hand, I would rewrite as follows:
- Outer select loop of tipgc520 and tipgc521
- within that select tipgc820, etc. and print
The reason for this is that tipgc820 will in general have several times more records than will be present in tipgc521, and breaking it down like this will help.

If you have to keep the second select as is, then try removing the order by. If this is a report session, you can always order by in the report fields. Doing a where clause on tipgc821._index2 and then ordering by a completely unrelated field make the select slow. I am not at my computer right now, and I do not remember the index for tipgc820, so I cannot recommend sorting by tipgc820._index2, but if that is a valid sort, that WILL speed up the select.

Mark,

Has your database been setup in that it automaticly performs a 'analyze tables' which is turned on by default if you install Oracle?
Check the field 'last_analyzed' in dba_tables for the tables that you are trying to read to check if that could cause the problem.

Regards,
Han

I am not at my computer right now, but the first select looks familiar - must be from the Print Planned GRP Purchase Orders. It seems o.k. because nobody here has reported any performance issues with it. To locate the problem table, the only suggestion I can make is to start by striping out all the tables except tipgc520 and tipgc521 and then progressively add tables till performance breaks down - not very efficient.

The second select on the other hand, I would rewrite as follows:
- Outer select loop of tipgc520 and tipgc521
- within that select tipgc820, etc. and print
The reason for this is that tipgc820 will in general have several times more records than will be present in tipgc521, and breaking it down like this will help.

If you have to keep the second select as is, then try removing the order by. If this is a report session, you can always order by in the report fields. Doing a where clause on tipgc821._index2 and then ordering by a completely unrelated field make the select slow. I am not at my computer right now, and I do not remember the index for tipgc820, so I cannot recommend sorting by tipgc820._index2, but if that is a valid sort, that WILL speed up the select.
Yep - you nailed the session and what I want to do. In both of the selects I want to strip out all the tables except the main tables. The problem is that it was decided to wait to see if the problem will reappear. I also want to nail down which query is causing the problem(still fix both), but it seems like it is the first query. I guess one of our fears is that other sessions will display the same type symptoms - but so far no problems. I will keep your suggestions in mind - I am sure they will eventually let me redesign it. :)

Han - I will check on that, but I thought it was mentioned that this was turned on. Will have to verify that.

It appears the session acted up again - removed the profile and it takes off. Now it appears another session - Print Pegging Data by Group Item tipgc0410m00b is acting up. So today it was profile off on the tables. :)

According to DBA update statistics is done nightly. One of the things I got:

execute dbms_stats.gather_schema_stats('BAAN',DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE =>TRUE)

Oracle 10g automatically gathers statistics on its tables when it feels that the statistics are not current enough. However, prior to going to production, I put this gather stats in place to run every night so that Baan will always have the most up to date statistics.

Mark,
execute dbms_stats.gather_schema_stats('BAAN',DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE =>TRUE)
We use -
exec dbms_stats.gather_table_stats ('BAAN','TZTTNT152707',ESTIMATE_PERCENT=>30,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254',DEGREE=>4,CASCADE=> TRUE);

Also, the newer porting sets generate different Oracle execution plan and we found bad performance when the code had 'refers to' in query extension, once its replaced by ' = ' operator and properly joined with tables it improved.
Also refer to the note from Design Principles:
Table references
Problem

Table references can be costly in performance. The Restricted (with counter) reference mode is very time consuming in situations where child records are added, updated, or deleted frequently.
Solution

The Lookup (restricted) reference mode must be used instead of the restricted (with counter) reference mode.
The only exceptions are if:
No index on the foreign key exists and
Parent records are almost never updated or deleted.
Example Currency code or country code.
You are on Baan-4, I am not sure how much is applicable for that version.

It appears the session acted up again - removed the profile and it takes off. Now it appears another session - Print Pegging Data by Group Item tipgc0410m00b is acting up. So today it was profile off on the tables. :)

According to DBA update statistics is done nightly. One of the things I got:

execute dbms_stats.gather_schema_stats('BAAN',DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE =>TRUE)

Oracle 10g automatically gathers statistics on its tables when it feels that the statistics are not current enough. However, prior to going to production, I put this gather stats in place to run every night so that Baan will always have the most up to date statistics.

I assume you applied all the parameters to the DB from the Baan important 10g parameters solution.

If that didn't fix it, then you may want to lock the statistics on some of the tables. Oracle 10g has some bugs with regard to statistics that can sometimes generate crazy execution plans.

dave

We have checked and double checked the parameters than baan sent us. All matched between test and production. Production had problem test did not. What we did discover is that some of the init parameters are different and are now trying to identify which one of those might be causing the problem.

I've been following Mark's threads on this because we are considering similar upgrades.

But I'm curious: Where can this "Design Principles" document be found.

Thanks.
Rob

Rob,

I am not sure of the Baan-4 versions, but the programmer's manual and design principles help files are included in DFE installation and located in - C:\Program Files\Baan\Baan Windows\help

You might have to check with Baan support if they have a different one for Baan-4.

The documents they recommend are LN documents, but if you read closely somewhere in there it mentions it works for baan 4 also.

There is a difference between the command you give to the database to analyze the tables and the command that Oracle uses to refresh that data. If this is the/ a reason, make sure that this job is disabled.

I have no idea if any of the tables fluctuate in data e.g. during the weekend these are empty due to a process that is just run before the weekend and on Monday this table is really filled. This can explain why a certain query can change in performance behavior.

Hope this helps,
Dick

did you try playing around with shared memory, sounds drastic but may help.

Here is an update and I am not sure if I can explain it right. We have a server which sits in Minneapolis and sync's up with a server in Louisville. During the week the two servers could not stay in sync - blackberry exchange servers. As the week progressed more and more data was going across the wan. Someone found that this sync'ing was using up 90% of the band width between the sites. They shut this down and we no longer have any issues. Everything we tried before would not work(parameters, cache, memory sizing, etc.) - and it would usually get worse as the week went on. This syncronization was stopped last friday and we have not had any issues since then.

I have not been able to convince myself on how this could have been the problem - but I think it was it. Will have to wait another week. The DBA's mentioned that once a query locked up it looked like the client was not getting a response back from the application server. On the database server they would see that the query would lock up, get a brief burst of CPU, then it would start recycling. Maybe kind of like a re-transmit.

On a side note once this sync was turned off other sessions which were slower are now running back they way they were. Something else that makes me think this was the issue. I wish I had some data to back me up so I can prevent them from turning it back on.

Gah - I knew it. it's always the network. ALWAYS! =)

Dave

Gah - I knew it. it's always the network. ALWAYS! =)
Thanks to the social network we know it is always the network.:rolleyes:

Best regards,
Dick

Gah - I knew it. it's always the network. ALWAYS! =)

Dave

Yeah - none of the infrastructure team wants to believe it either. But the fact remains - now going on two weeks and no issues. :)