After these explanations lets start to explain how to manage shared pool with ASMM. This should show if there are similar literal statements, or multiple versions of a statements which account for a large portion of the memory in the shared pool.
This is sometimes useful to help identify sessions or statements which are continually causing space to be requested. Each column identifies a specific reason why the cursor cannot be shared. First of all find the free memory in shared pool. You can run this query to build trend information on memory usage in the SGA. Dont use the script below when db is under load.
Note says that this query can hang database on HP platforms. If you see lack of large chunks it is possible that you can face with ORA in near future.
The statistics below is based since the start of the instance. You should take interval statistics to interpret these values for performance issues. High invalidations indicates that there is parsing problem with the namespace and high reloads indicates that there is a sizing problem which causes aging out.
If this ratio is higher and every previous control is OK then you should consider to increase the shared pool size. Having objects pinned will reduce fragmentation and changes of encountering the ORA error. In the afternoon, things seems get worse Begin Snap: 94 Dec What is your opinion? December 16, - pm UTC. I'm almost sick of seeing it, no wait - I am sick of seeing. Increasing the shared pool will make things worse, not better.
Alexander the ok, December 16, - pm UTC. Why do vendors never use binds or understand how to write good code? None of ours do either. Then we are stuck with a crap application that we can't change, it's SO frustrating. Dear Tom, How can I solve this error? ORA unable to allocate bytes of shared memory "shared pool", "unknown object", "sga heap 1,0 ", "session param values". December 19, - am UTC. Hi Tom There is a fundamental thing I dont' understand. Why is it bad? Shared pool is where the SQL code and the data dictionary lives.
Why latch? Why do you care to latch? What will be corrupted in the shared pool? Shared Pool is not like the Data Buffer where data lives. In the Data Buffer I understand the reason latching is bad and why it inhibits concurrency, 'Consistent Gets' ACID theory of transactions, locks, transactions trying to concurrently modify same data, phantom reads and dirty reads etc.
Latching Locking in the database buffer I understand it can be chore. Why does it have to be concurrent and use latches serialization devices? If it wasn't for these latches we wouldn't have to use bind variables, is that correct? Many thanks Kubilay. Jonathan Lewis in his blog answered my question as follows, I thought I shared it in this blog as well.
What will be corrupted in the library cache? Why does Oracle have to "protect" it, as you say above? A lot of the work done by Oracle uses linked lists. For example, each cache buffers chain is a linked list, and the reason you grab the cache buffers chains latch is to make sure that the chain linked list that you want to traverse does not get damaged by someone else adding or removing an entry as you walk along it.
So the latching for data blocks has very little to do with updating data, it's essentially about finding out whether the block you want is in memory, or getting it safely into memory if it isn't already there. The latches for the library cache and shared pool are the same. You acquire a library cache latch when you want to walk along a linked list of objects in the library cache to find, or insert, an object. In the case of the library cache, you tend to talk about library cache hash buckets, rather than library cache chains.
The shared pool latch covers the lists of chunks of free memory; if you need a chunk of free memory you acquire the latch so that you can take a piece of memory off a list and use it. If you free up some memory by kicking something out of the library cache you grab the shared pool latch so that you can safely add the free chunk to the correct chain in the shared pool.
The Concepts menu includes a couple of notes that I wrote on this and related topics. Now I understand better bind variables and latching and why latching is bad to scalability and concurrency. Latches are memory structures thus they are finite hardware resources, using them sparingly is wise. As overusing the latches by writing SQL which doesn't use Bind Variables, will starve the system of this resource and will ground it to a halt.
March 07, - am UTC. Ahh, the power of the internet. You asked when I happened to not be around. We have a 3rd party app that sigh! With a large shared pool m we are now getting ORA Clearly the answer is to reduce the shared pool size, not increase it. Are there any other ways to mitigate the problem of using literals rathe rthan bind variables?
March 15, - pm UTC. Please let me know if my interprentation is somewhat correct. May 04, - pm UTC. I have been watching the shared pool free memory more closely and i have spotted that free mb goes up and down constantly. In one minute i have 50MB free and 30 sec later i have 3MB free in the shared pool. More so, the developers have said that collection optimizer statistics have a negative effect on the performace of the application so they have adviced me not to collect stats.
Cant see how that can be the case though.. Since a reload occurs when the sql statement has been aged out and the shared pool free memory is decreasing and then suddenly increasing, then i'm more and more certain this is because of not using binds. Somewhat correct? May 08, - am UTC. Hi Tom I'm a developer and yes, I adhere to using binds as much as I can! May 11, - am UTC. Mat, May 14, - pm UTC.
I believe that if you cache table data you will speed up reads, not the writes. May 14, - pm UTC. If the select component of the update, the bit that retrieves the data, doesn't have to read from disk - it won't, it will benefit from that just as much as a select would.
Mat, May 15, - am UTC. Tom, you are right. When I read "slow DB which predominately is down to the disk raid which we can't improve much for writes" I imagined they are using a raid 5 that is slow in writes. In this case the bottleneck would manifest when the lgw and dbw are writing to disk, if it's true I believe caching would shift the problem, not solve it. It would be nice if "A reader from UK" could add infos on this matter.
Is there a particularly big trade off in storing the value in a table which is then queried each time a debug message is written to determine if debug is on? Thanks Phil. May 21, - pm UTC. I've done the "check a table every time" method and in production - use a 'check a table at startup' or 'use this empty package body that we'll replace with the real package body when we need it' approach.
Tom, I read above , but one thing can't find. June 09, - am UTC. I am not following you at all here. I see nothing unbelievable. What version of Oracle are you on? The large value is actually the result of a bit integer underflow. What is this? How different is it from a latch? Which one takes precedence? Does DBA have any control over mutexes? August 22, - pm UTC. Mutex and latches do not really support orderly queueing and waiting for a resource, more of a mob mentality - enqueues are heavy weight, but orderly used for queueing up for a row that is locked for example.
How does Oracle decides to use latches or mutex? If mutex is faster then can we set or Oracle knows always to use mutex? Assume i cant use ad-hoc in my application. If i know that similar statements will be at max loaded each 1M, then M should be good? August 27, - pm UTC. Tom, help to point me documentations explain about mutexes and the way it could be better than latches.
September 05, - pm UTC. Best Regards Jatin Pal Singh. December 17, - am UTC. You don't give much to work with? Why Not Using Reserved Pool? Jatin, December 17, - pm UTC. Ok, while we agree to increase shared pool in this case, we donot find much help from the advisory posted above as to how much to increase am going in iteration now from to and so on for times.
Am I looking at the right section in statspack - can you please comment as am not sure what it is indicating. I see that this view is consistently showing average free size of over 50 KB, max free size of over KB and so on.. Am I looking at incorrect stats? December 18, - pm UTC. Jatin, December 19, - am UTC. Therefore comparing the number of Lib Cache objects e. December 20, - am UTC. You seem to be using binds pretty much.
Can you explain what was going on during this snapshot, characterize the system at this point in time. Jatin, December 21, - am UTC. You seem to be using binds pretty much.. December 21, - pm UTC. We are receiving the following error while running a snapshot refresh on our data warehouse environment My question is that while I cannot go for shared SQL binds in warehouse , how should I proceed to fix this error?
You are looking at a system that is a small test system, not doing what the real system will be doing, doing not what you said you were doing you are using binds And you say "i beieve this provide enough info on characteristic of this warehouse.
Jatin, December 23, - am UTC. However, I checked and to my understanding confirmed that global temporary table accesses are not there; Can you please elaborate what direction should I take from here? Thanks, Jatin. December 31, - am UTC. Hi Tom, We have a poorly performing production system as most posters do.
I used 4 different sessions to generate unique SQL statements each at the same time. After testing, it barely made a difference to the shared pool and buffer cache sizes 75mb and mb respectively on test env. They still maintained roughly the same size. So, what is going on here? It's really appreciated! January 29, - pm UTC. Hi Tom, Thanks for the reply above! Your answer is spot on.
The DBA reset the memory a few days ago. The users have reported that the system is running much better than before. Oracle started off with a MB buffer cache and an 80MB shared pool. I checked the pool sizes each day and noticed that the shared pool was increasing every day 20MB first day, 30MB the second day, 40MB the third, etc.
This of course is forcing the buffer cache downward by the same amount. For most systems, this value will be sufficient if you have already tuned the shared pool. Here is a query that will display these values. Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum.
Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise.
0コメント