How many of you have enabled Array Interface in db_resource (ora_init)? I have tried several times, but result has baan always bad performance in Baan. Processes are not getting time enough.
I have tried several ora_max_array_fetch values (5, 50, 500,5000 with bdbpre test) but nothing is better than without Array interface.
Is this normal?

Problem:
We have serious problems with database going to coffee break for few minutes from time to time. Even sqlplus login will take very long time. When this happens it seems like processes are not getting processor time.
statspack report has nothing wrong. Maybe enabling array interface would help...with worse performance, who knows.

HP/UX L3000 and Oracle 8.0.5 3-tier setup in one box at the moment.
6.1c.06.02 Porting set

db_resource:
dbsinit:01
ora_init:0011000
lock_retry:0
ora_timeout:{0,60,60,60,0}
retained_cursors:50
max_free_cursors:50
bdb_max_session_schedule:4
rds_full:5
#ora_max_array_fetch:50

nls_lang:american_america.we8iso8859p1
nls_sort:binary
ora_default_tablespace:datbaan
ora_temporary_tablespace:temp
ora_column_format:7
oracle_client_home:/u4/oracle/product/8.0.5
oracle_service_name:baan.world
two_task:baan.world
oracle_home:/u4/oracle/product/8.0.5

I absolutely recommend the use of the max_array_fetch. If you are running pre-IVc4, not many of the sessions will take advantage of this. bdbpre and post do however.

But, when you increase the size of the array fetch, you should also increase the size of the rds_full. Otherwise, you are returning more rows at a time to the driver process but taking more time to send the rows to the bshell (assuming that level 2 driver is in use, otherwise will operations being single row, the array interface makes no difference).

I have never seen worse performance when turning on the array interface. At worst, it is nuetral unless is causes some level of Oracle contention or IO bottleneck due to the ability of the driver to move more rows faster to/from Oracle. If you are experiencing a CPU bottleneck, having all drivers use more CPU could lead to increased contention. If you can check this when nothing else is running on the system, you can find out if the performance is better when CPU is not constrained.

When you say that the system is going out to lunch and you cannot tell what is happening in STATSPACK, if you query the system wait table when the slowdown occurs you can see what all the running sessions are waiting upon. A wait_time=0 states that the wait is not complete, in other words what the session is waiting for right now. This can tell you what Oracle resource is constrained:

SQL> select event, count(*) from v$session_wait
2 where wait_time = 0
3 group by event order by 2 desc;

If you would like to post the output from this query in this thread, we can help diagnose your problem.

Hope this helps,


-- Jim

With an setting for the ora_init to 0011000 your are not able to use the array settings for oracle. Set the ora_init to 0101000.


Martin

My assumption was that orainit was changed to disable the array interface from 0111000 to 0011000, but good point Martin.

Did you only set the size of max_array_fetch without changing orainit?

-- Jim

Hi !
Baan version is BaanIVc4. I was running array interface with value 5 in ora_max_array_fetch and also rds_full. Users reported slowness in for example integration runs. Normally 20 min. and now 1 hour. Also table export by bdbpre was very, very slow during nighttime. Then I tested bdbpre with 50, 500 and 5000, but I forgot to change rds_full!

There is over 250 concurrent users at daytime and I/O is at least one bottleneck, but can this cause Oracle to go to lunch?

SAR:
7:00:00 5 3 14 77
07:20:00 9 5 20 66
07:40:00 8 5 21 66
08:00:00 8 4 21 67
08:20:00 14 6 24 56
08:40:00 12 5 24 60
09:00:00 13 6 27 54
09:20:00 18 8 41 33
09:40:00 26 9 37 29
10:00:00 16 6 31 47
10:20:00 16 7 47 30
10:40:00 20 7 46 27
11:00:00 27 12 28 32
11:20:00 32 14 27 26
11:40:00 26 14 22 38
12:00:00 27 14 23 36
12:20:00 26 13 27 34
12:40:00 19 8 27 45
13:00:00 27 10 33 30
13:20:00 19 9 26 46
13:40:00 19 9 26 46
14:00:00 33 10 23 34
14:20:00 28 11 37 24
14:40:00 40 11 25 25
15:00:00 22 8 28 42
15:20:00 23 9 31 38
15:40:00 26 9 37 28
16:00:00 19 8 34 39

Average 20 8 24 47

Now I'm thinking of changing 3-tier to 2-tier setup from TCP to BEQ. What do you think how it will be with performance?
This is clustered setup with two boxes, but I think I can test this...


This is taken from a time with not so big load...

SQL> select event, count(*) from v$session_wait
2 where wait_time = 0
3 group by event order by 2 desc;

SQL*Net message from client 158
rdbms ipc message 7
db file sequential read 1
log file parallel write 1
smon timer 1
log file sync 1
pmon timer 1

7 rows selected.

Clearly you are having some IO bottleneck on the system (from the sar report). I would run an iostat to further refine what type of problem you are having on the system.

The Oracle wait information from that particular sample does not show a IO wait. We would need a longer sample using a delta from Oracle system statistics (see the V$SYSSTAT table).

The SQL*NET Message from Client indicates either normal waiting for the Baan application and driver OR communication delays between the Oracle driver and the Oracle database. This could be improved by switching to the BEQ interface.

You have idle CPU time which (in combination with the above) clearly points to either a configuration error (including the need for an Oracle patch) or a major IO bottleneck. I would go after the IO subsystem. The long delays could be the result of a log sync, RBS constraint or other related IO type delay.

-- Jim