Go Back > Forum > Baan Quick Support: Functional & Technical > Operating Systems & Databases

User login

Frontpage Sponsor


Google search

How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
200 - 500 GB
500 - 800 GB
800 - 1200 GB
1200 - 1500 GB
1500 - 2000 GB
> 2000 GB
Total votes: 35

Baanboard at LinkedIn

Reference Content

Thread Tools Display Modes
Old 8th March 2018, 17:46
rduncan10's Avatar
rduncan10 rduncan10 is offline
Senior Member
Join Date: Feb 2007
Location: Cambridge, ON Canada
Posts: 117
rduncan10 is on a distinguished road
Baan: IVc4 - DB: Oracle 10g - OS: SUSE Linux
ORA-00942 and Oracle shared_pool
Baan: Baan IVc4
DB: Oracle 12cR2
C/S: None/Unknown

I'm troubleshooting a persistent problem we have with reports that will periodically lock up or take a very long time to run. Most of the time, the report works fine, but sometimes it locks.

Sometimes, recompiling the session code will make the report work faster, or the problem will go away if you wait awhile and try again.

There is no error in any Baan log and I've been searching various Oracle tables and logs.

I tried to output the v$sessions view in Oracle ("select * from v$sessions"). I have little idea how to read this output, but I notice a lot of ORA-00942 (table or view not found) errors, some associated with users running these reports.

There is no corresponding error in the Baan logs or the Oracle alert logs.

I have read that this error can sometimes be associated with a problem in the Oracle cache and running something like "ALTER SYSTEM FLUSH SHARED_POOL;" can fix it.
Has anyone had any experience with something like this in Baan?

Reply With Quote
Old 8th March 2018, 22:48
NPRao's Avatar
NPRao NPRao is offline
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,073
NPRao will become famous soon enough
Baan: Baan 4-5,5.2(Reger),LN-6.1,Infor LN-10.x - DB: Oracle,MS-SQL - OS: HPUX, Linux, Windows
I think you have to tune your Oracle 12g Database or work with your DBA. From some of the online articles -
The purpose of this shared area is to maximize the sharing and the reuse of the information.
Periodically flushing a shared pool might be something you do in an emergency.
o negatively impacts performance
o is totally unnecessary
o negatively impacts performance
o could be killing your performance
o causes a large CPU spike every half hour
o causes a slow down ever half hour

When attempting to get shared buffers, Oracle does the following:

If LARGE_POOL_SIZE is set, then Oracle attempts to get memory from the large pool. If this value is not large enough, then Oracle does not try to get buffers from the shared pool.

If LARGE_POOL_SIZE is not set, then Oracle attempts to get memory from the shared pool. Fragmentation of the shared pool can lead to memory not being allocated. From the screenshot of the behavior in your environment, it does appear that memory could not be allocated from the Shared pool. This could be because the Shared Pool size is not optimal. This will depend on the Production environment Transactional requirement and should be carefully set after consultation from the DBA. A DBA should adjust this value upward based on the available physical memory of the hardware and performance, whether connections are dedicated, or run Multi-Threaded Server (MTS) and the application type. You may want to increase the Shared pool size (for example to 300000000; please note that as mentioned this will depend on your specific environment requirements)

SQL> select * from v$sgastat;
SQL> select nvl(pool,'<unnamed pool>') pool, name, bytes from v$sgastat order by 1, 3 desc
SQL> show sga
SQL> show parameter pool

The primary uses of -
shared pool - code
buffer cache - data
Also, refer to - Infor Enterprise Server Technical Reference Guide for Oracle Database Driver manual and you can use the following variable and run the program to get the cursor statistics and work with your DBA if your database cursor settings are proper or need to be adjusted.
orastat / ORASTAT
Driver resource orastat
Environment variable ORASTAT
Client/Server resource Set for server only
Type Integer
Default Not set
This variable allows you to report database driver statistics. If set to
a value n greater than 0, statistics are logged every n seconds while
the driver is active. If set to 0, a statistics report is generated when
the driver terminates.
If this is your custom code which uses the dynamic SQL make sure there is sql.close() for every sql.parse()
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
Sponsored Links

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

All times are GMT +2. The time now is 13:55.

©2001-2018 - -