SPPID slowness problem

[somnath.thakare]

Good day.
We are currently executing a project with a standalone SPPID.

We are experiencing all sorts of problems such as the following:

1. out of synch,

2. cannot load items from database, etc..

3. Opening the drawing and querying the OPC takes 5 mins.

4. and some drawings can't open

I've attached a logfile and what is highlighted here is
ORA-01003: no statement parsed

We then proceeded to increase the auto-extend value for the tablespace. That did not work.

Most recently we got the following:

ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 28 bytes of shared memory

We got PDBA to increase the shared_pool_size" from "84 M to 147 M. This still did not work.

We are still experiencing slowness and worst it just hang for some or all users.

Questions:

1. Are there best practices for the configuration /set-up of parameters Oracle for SPPID?

2. We are using only one oracle instance for SPI, SPEL AND SPPID, is this okey?

Thanks

Somnath

[Patrick_Aps]

My answers:
2) It was never recommended by Ingr to have different producs in one Instance. But if you have only one Plant in each application, Oracle should be capable of handeling this amout of data
1) With SPPID 4.3 and SPI 7.n I listed all the Oracle recommondations I found in Ingr setup manuals and came with this set of parameters:
DB_BLOCK_SIZE 8k
DISTRIBUTED_TRANSACTIONS is not needed with Oracle 9i
LOG_ARCHIVE_START True
LOG_CHECKPOINT_INTERVAL 10000
LOG_CHECKPOINT_TIMEOUT 1800
OPEN_CURSORS 300
optimizer_index_caching 0
optimizer_index_cost_adj 100
OPTIMIZER_MODE CHOOSE
PROCESSES minimum 300
SHARED_POOL_SIZE 48 MB
TRANSACTIONS 368
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16

USER_TABLESPACE 600mb
TEMP_TABLESPACE 200mb
Auto Extend TRUE

Oracle Client settings:
Same as server:
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET AL32UTF8

[smartman]

Instead of having specific SGA and PGA , I prefer to specify 40-50% of RAM.
Also if you have all apps sharing same instance it can cause problems. Ask DBA to check "Using Oracle Analyze Scripts
" in SPEM.

Try to delete temp files, also delete temp files on desktop. The one which you have attached has entries since 2010 :)

[mandy4u]

Your problem is due to improper Oracle database tuning. Change oracle SGA/PGA Max size....increase SGA size = 250MB , PGA= 120MB & other parameters as suggested by Patrick.

It's necessary to delete all temp files on regular basis from your workstation for better performance.