changing sql area size without decreasing shared_pool_size 2

[sem]

Hello,

Does anybody know how to limit sql area size without decreasing shared_pool_size?
I'm sure, that shared_pool_size is oversized and sql area uses virtual, not physical memory. I have no access to unix and only select any table privilege. I can not prove that 550M shared pool is more then sufficient for our needs. All hit rates are >99%, but our DBA wants them to be 99.99 and also is afraid of the lack of memory for 300-500 sessions. He claims that the larger shared_pool the better performance is. As sql area size grows to 100M+ we get a great loss of productivity. Finally it stops on 3000M. 10000 from 20000 statements are executed just 1 time, so I need to set some "personal" limit to sql area or some utility to unpin those statements, for they are replaced only after all 300M are full.

Please, help me.

 

[karluk]

Oracle likes to recommend large shared pools as a solution to performance problems, but there are certain situations where they actually make performance worse. We ran into this type of problem last year, leading to extremely poor performance and intermittent periods when Oracle seemed to come to almost a complete halt.

We eventually diagnosed the problem because of the huge number of library cache and shared pool latch waits. You can check v$latch to see if you have similar symptoms.

If you are on Oracle 8.1.6, or higher, setting the initialization parameter "cursor_sharing=force" can usually fix this problem. On earlier versions of Oracle, flushing the shared pool periodically can help. The flush clears out the shared pool, which usually hurts performance, but it also prevents the shared pool from getting badly fragmented, which is the main cause of the latch waits.

I encourage you to work with your dba on this problem. Dba's like hard numbers and, if you can demonstrate problems with latch waits, he or she will probably be willing to look at potential fixes.

 

[sem]

Thank you a lot, karluk, for implicit support of my point of view also. We're on 8.1.6. He saw the great nuber of misses in v$latch, but interprets it conversely: as I see, he've just added to shared pool 100M more. Do you have some "authoritative" link with explicit advise? I don't have access to metalink, but he does.
Thank you.

 

[karluk]

Have your DBA take a look at Metalink document 62143.1 () A key excerpt is

"If there is heavy use of literal SQL and the shared pool is too large then over time a lot of small chunks of memory can build up on the internal memory freelists causing the shared pool latch to be held for longer which in-turn can impact performance. In this situation a smaller shared pool may perform better than a larger one."

It also describes the cursor_sharing=force parameter, which is designed for use when it's not possible to rewrite the application to replace literals in SQL with bind variables.

It might also be illuminating to search the Metalink forums. I found documents 176285.999 and 85019.996 discussed this problem. The solutions they implemented were as I described.

I attended two or three seminars at Oracle Open World last year where the cursor_sharing=force option was discussed. They all said it works spectacularly well on systems that use literals instead of bind variables. If this is your problem, maybe you can convince your DBA to give it a try. I'm sure he's not too happy with a situation where repeatedly increasing the shared pool size either doesn't work at all, or actually makes the situation worse.

 

[THoey]

Karluk,

I think this may be a reason for some problems we are having too. I have seen you mention Metalink in this thread and several others. How does one get access to it? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[karluk]

The address for Metalink is

http://metalink.oracle.com

Registration is required.

 

[carp]

If I might add my $.02, I would also recommend Metalink as an excellent source. You can find documentation and white papers as well as gain access to TARs that have been logged. You can also log a TAR here if you don't want to wade through Oracle Support's phone queue.
Oracle has put a lot of work into this site; it's a splendid tool for helping solve your problems!

 

[sem]

Thank you all, especially Karluk. We significantly redused the number of proccesses by using MTS, getting a lot of free memory (we had some problems with Forms5 on win98 + shared servers, but they've dissapeared in some miraculous manner), so the system now works, though still not perfectly. I'm sure the next step will be reducing of shared pool.
As for cursor_sharing, this is the problem, but we have some third party applications, written with BDE and using constructs like (smth = null), which doesn't work with force. Fixing them will take some time, so we still use cursor_sharing=exact.