I am newbie to Baan. We have a Baan V with an Oracle 10g R2 installed by contractor on one AIX 5.3L system. There is not much explaination by them after finished. When I check the Oracle, I did not see any of package, procedure, function.. except the data and index associated with Baan application. I want to know how actually Baan connects to the Oralce and performs the select, insert, update .. , So that as a DBA of oracle, we can tune the oracle optimized for the Baan. Secondaryly, is there a benchmark process to verify the Baan V/Oracle. This is very basic question. Hope anyone can help or point our any fundamental doc to read. Thanks.


C Chang

Hi Ciatemx,

I will try my best to answer based on my limited knowledge of both products. Baan (application) communicates with Oracle 10g (database) through Porting Set. Baan itself has its own 4gl programming language called Baan SQL where it does its own DML (data manipulation) like insert, update and delete. The commands are "ported and interpreted" in order for Oracle to understand it.

In Oracle db, you won't find much of these statements that relate to Baan because they are inherent at the application. As a dba, although not recommended, it doesn't stop you in creating your own package (means different in Baan), procedures and functions (similar to Baan function) in Oracle. I need to warn you against creating triggers because Baan does not like it. You also need to be careful about table referential integrity because the table definitions are stored in the application

I suggest you read the Baan document on optimization because it might ask you to optimize from the application as well as database level.

I'm not to clear about your "benchmark" question. Maybe somebody else can chip in.

Thanks,
Benito

Thanks for your guideness. I will check the doc of Baan optimization. For the benchmark question, I meant that becuase Baan uses its own SQL and DML, how do we know, as users, that the Baan SQL scripts are in the best interests to Oracle, ie allowing the best performance by the Oracle. The target source of data is still the oracle setting, is it?

C Chang

Benito already explained you some parts, but let me try to elaborate on it:
Baan is written in 4 GL, the queries in this 4 GL are translated into database specific queries by a Baan database driver. These queries including DML and DDL operations are in your case sent to the OCI layer of Oracle. Baan doesn’t use triggers etc etc, but has its own referential integrity.

About the benchmarking: every customer uses its application different. Take care of solution 166049 as basic tuning and measure when the customer starts working. You can’t expect that a benchmark will give you a good performing system for the online usage, but indeed it will help you. So the recommendations of the solution will do.

Hope this helps,
Dick

Hi Chang,

your approach is very Oracle centric and I fear you have to get a bit away from this. It is good to know Oracle, when analyzing baan performance, but the problem and the solution is 90% of the time in Baan and not in Oracle, assuming the basic settings and paramters are correct.

Baan is using Oracle only as a basic data storage. The only concepts used are tables, indexes and a single role. It does not use triggers, stored procedures, functions etc. And there is a good reason for this, namely to be database independent.

Baan offers several means to trace the SQL statements produced and you can use the standard oracle techniques to trace sql statements and look at the execution plan. But if you then discover a problem, the solution might be to change/add an index or to change the SQL to access oracle. Both has to be done in Baan, so you will have to learn how to do this, if you are really concerned about Baan performance.

Apart from this you will find here on the board, several threads discussing both Baan variables and Oracle parameters, which influence performance. Youo should also take a good look at these.

Regards

Markus

Thanks all for yours input. I will try to learn those issues in BAAN ASAP. Actually, currently there seems to be a problem caused by the BAAN, as you descripted, that Oracle indicates a "TCP Socket (KGAS)" in wait class "Network" was consuming significant database time, which is caused by a BAAN updating SQL. And I tried to trace it. It is good case to learn.

Chi

If it is a network delay, then I would assume the specific SQL server statement does not matter.

Is your Baan and oracle installed on the same host? If so, is it still configured to use SQL-Net to access the database?

Good luck,

Markus

Waiting for the network is mostly waiting for other processes like:
- baan
- user input.

So it is normal that this part is most of the time.

Hope this helps,
Dick

Markus:
Yes. My BAAN is on the same AIX 5.3L box with Oracle 10g? Does it affect anything? we can not affort another box right now?

Chisoon

Quite the opposite. Don't worry. Considering todays scalibility of servers from IBM and HP-Ux you are better off running on one server, even with several hundreds of users.

Having the applicaton and the database on the same server has the advantage of eleminating network delays. Many customers went from a two-server (so called client server configuration) to a one server (so called host mode configuration) setup and experienced a drastic performance improvement.

Just make sure, that you have nowhere the TWO_TASK set, so that ou do not go via SQL-Net by mistake anyway, but access the database directly.

Execue me for the lack of knowledge, what is the TWO_TASK?.

Chi

Markus:
Thanks for your suggestion again. Forgot to mention in last post, I knew my BAAN system is definetely not using the SQL-net because the listener once dysfunction,but the BAAN still talked to Oracle.

Chi