Hi,
What settings are you using for cursor parameters with 10.2 and LN?


session_cached_cursors = 200
open_cursors = 5000
cursor_sharing = exact
#cursor_sharing=similar

Hard to achieve good parse to execute ratio...

Hard to achieve good parse to execute ratio...

In Oracle you can't influence the parse to execute ratio. This is determined by the application usage. The only thing you can influence is the ratio between hard parses ((re)new parses in Oracle) and soft parses (Plan is available in SGA).

For a higher ratio soft parse to hard parse you can use session_cached_cursors and cursor_space_for_time. The higher these the better the ratio and the more memory it will use. For Baan you don't have to change cursor_sharing. For a load of a company you require something like 500 open_cursors.

best regards,
Dick

With session_cached_cursors set to 400, I have achieved under 10% parse to execute ratio. Shared pool 3,6GB and db_cache for 8k 3,6GB. This db is almost 1T because of pure Unicode character set. Hit ratios are good, but LN/fp3 performance is not good.

Can you elaborate on what you mean by LN performance is not OK?

Best regards,
Dick

Basically, for me, it looks like silly amount of queries in some of these problem sessions. Nothing takes time in db-level. In some of the cases 500000 queries in run...some are customized. Also problems with MRP, of course:)
Multi-bshellling not working with this at the moment. No error messages...looking good with two bshell's, but then stops...ticks stops growing and job stays running. No message queue problems.

Some of the problem-sessions:

Update prod orders tisfc0101m100
Update of operations tibom1110m000
Transfer orders cppat1250m000 timing approx 5-8 minutes
Release orders tisfc0204m000 timing approx 5 minutes
Initiate inventory issue tisfc0207m000 timing approx 5 minutes

Some deadlock issues also...

Played a lot with different db_resource -values and those listed in Infor case for Oracle parameters.
Porting set:
8.4a.02 on AIX 3-tier
fp3

***************

db_resource:
dbsinit:01
ora_init:0101000
ora_max_array_fetch:5
ora_max_array_insert:2
#baan_oracle_prefetch:2
rds_full:2
nls_lang:american_america.al32utf8
nls_sort:infor_generic_m
nls_comp:ansi
oracle_home:/app/erpln6a/ora_client2
ora_temporary_tablespace:TEMP
ora_default_tablespace:DAT
ora_alter_session: SET SESSION_CACHED_CURSORS = 400
#ora_alter_session: SET _complex_view_merging = false
#first_rows_hint:1
#ansi_outer_join:0
lock_retry:0
ora_timeout:{120,120,120,120,120}
#lock_retry:5*100;5*500;10*1000

*********
keep_cache used for mcs050 and similar tables...
Of course one big reason for slowness is pure Unicode, not TSS. Much more data to process everywhere.

Now I can image that you kept it to a high level. You have a complex environment with tons of things that can go wrong:
- System setup
- Oracle
- Unicode
- ERP LN
- customization
- application usage

I believe these all are involved. Although I would like to help you, I think this would take a very long thread and I think you better contact support or your account manager for a session or system investigation if it is hampering your business.

Best regards,
Dick

Of course these support actions has been taken, but overall performance is just quite poor....or should these be quicker with lot's of data, I don't know.
Is there anybody else running real Unicode here?

What would you say if you see tkprof or CGP with half a million of same quories, which takes 30-40 secs total time in db and rest of the time (500 secs) is spent in bshell...bad code I would say:)

I would not necessary say bad code, but it could be. Oracle is not telling the real truth when you see only 30 secs there. But you run 3-tier and thus network can consume sme time etc etc. Can you provide a CGP and tkprof output on any of these sessions? And what is the system telling after the run? How much time is spent on bshell, driver and database server process.

BTW. Did you know you can run in combo on your portingset? Combo has been introduced on UNIX since 8.4a.01. Since 8.4b combo has been improved even further by the removal of some internal layers. More than 10% improvement has been measured in most situations.

Best regards,
Dick

Yes, I have tested combo mode with MRP. It was 5-10% faster with it, but it has at least one problem with one session, causing application lock issue with ****** as header. It's solved by tools-fix, but we can't implement that at this time.
Network is not a big issue. We have snapshot env. of prod, which has same issues. Packet sizes (SDU and TDU) are fixed in prod.

Of course it would be faster with 2-tier and BEQ. We could save Oracle licenses this way. At least one reason for 3-tier.
AIX with LPAR's in use. Not The Best option this either...:)

Has anybody heard that AIX is slower than others with TWO_TASK?
We have now solved many problems by switching to beq/hostmode.

makiju,

The reason could me many things but in general I am not aware that AIX is better or worse than others.

In general I see more and more a difference between host mode and C/S than in the past due to much faster CPU's compared to almost the same network speed.

Hope this helps,
Dick