Panorama: Evaluate SGA memory usage and resize operations for Oracle DBs

Starting with release 10.1 Oracle introduced automatic shared memory management.

Setting only SGA_TARGET=x without defining limits for the SGA-components (buffer cache, shared pool, large pool, java pool) allows your database to redistribute SGA memory between components due to current needs.

This will work properly in many cases but sometimes there are some pitfalls:

A very common problem on Oracle databases are applications without usage of bind variables in their SQL statements.

Not using bind variables leads to excessive SGA memory consumption in shared pool and parse effort if you have a large number of SQLs executed with different values in filter conditions stored as literals.
For example:

SELECT a, b FROM Item WHERE ItemNo = 453612;
SELECT a, b FROM Item WHERE ItemNo = 876886;

etc. instead of
SELECT a, b FROM Item WHERE ItemNo = :ItemNo;

In such a situation the DB may resize the SGA. The size of the shared pool will grow thus reducing the size of the buffer cache.

In worst case this may lead to a SQL-area in shared pool which is much greater than the remaining buffer cache. Result in this case is poor SQL performance due to poor cache hit ratio.

With alternating load scenarios between heavy db-cache and SQL-area utilization there is also a risk of getting "ORA-04031 unable to allocate x bytes of shared memory" if SGA resize operations are not satisfied fast enough.

Solution for such a problematic skew position is to define minimum values for SGA components according to your needs thus preventing SGA components from falling below these values and reducing resize operations.
The minimum values set for e.g. DB_CACHE_SIZE and SHARED_POOL_SIZE may by smaller in sum than SGA_TARGET, so you may leave some remaining space for Oracle's own decision who gets it.

The Oracle performance analysis tool "Panorama" allows the evaluation of current and historic distribution of SGA memory between the components and also a view inside the objects and their memory consumption for some components.

Panorama is available free of charge at http://rammpeter.github.io/panorama.html.

View SGA components and their sizes (Menu "SGA/PGA details" / "SGA memory" / "SGA components")


















View last resize operations (Click "Resize ops." in previous table) 





View content of SQL-area (Click on name "SQLA" in previous table)




Historic SGA resize operations





Current buffer cache content (Menu:  "SGA/PGA details" / "DB-cache" / "DB-cache usage current")



Panorama also has it's own sampling of historic information so consideration of DB-cache contents is also possible back in history even if this is not part of AWR-recordings. See http://rammpeter.github.io/panorama_sampler.html if interested.

Comments

Post a Comment

Popular posts from this blog

Common pitfalls using SQL*Net via Firewalls

It's very common that firewalls are terminating idle TCP sessions after a limited time, often after approximately one hour. Possible problem If you have a long running SQL or PL/SQL program and the firewall terminates your apparently idle TCP session this may lead to the following scenario: the database is not able to send result to client. After reaching the TCP-timeout the database server terminates the DB-Session the client program stays in socket read forever waiting for the database response Oracle's full solution: SQLNET.EXPIRE_TIME=x in sqlnet.ora Oracle's solution for this problem is setting parameter SQLNET.EXPIRE_TIME=x in sqlnet.ora where x are the minutes between keep alive packets on idle sessions that inform the firewall that this session is always alive. SQLNET.EXPIRE_TIME hast to be set in the sqlnet.ora of your RDBMS' ORACLE_HOME. Normally $ORACLE_HOME/network/admin. If you have a grid infrastructure setting SQLNET.EXPIRE_TIME in sqlnet
Read more

Oracle-DB: How to check for appropriate sequence caching

Image
A common pitfall while using sequences in Oracle-DB is forgetting to define sequence caching. The default for sequences is cache size = 0. That means, every sequence.nextval operation triggers a single write operation on dictionary table sys.SEQ$. Especially if frequently calling sequence.nextval uncached sequences may cause significant performance degradation. Frequently calling sequence.nextval in parallel sessions may lead to ramdom lock scenarios in library cache. Setting a cache size for a sequence may heavy increase performance of nextval-operations. There's a minimal memory overhead for caching a sequence because all that's needed is a counter to increase in SGA memory and a upper limit. If this limit is reached with the counter, the upper limit is increased by cache size and the new upper limit is written down once to SEQ$. The possible drawback for sequence caching is that you loose all your values between the current value and the highest cached value if yo
Read more

Oracle-DB: Link between audit trail and active session history

Unfortunately the audit trail of the Oracle-DB uses a different session identifier (AudSid) than the Active Session History (SID + Serial#). Both identifiers are available in v$Session (AudSid, SID, Serial#). So during lifetime of a session it is possible to link between the session info from v$Session an audit trail. But neither the AudSID is stored in ASH (v$Active_Session_History) nor the SID + Serial# is stored in audit trail. This prevents from combining session info of both sources after the session is closed. There is a possible way to link audit trail with ASH by establishing a logon trigger for that. The LOGOFF records in audit trail (if AUDIT SESSION is active) record also the Client_Identifier from v$Session. So supplying v$Session.Client_Identifier with the needed info allows to retrieve it from DBA_Audit_Trail.Client_ID.  This logon trigger does it: CREATE OR REPLACE TRIGGER Client_ID AFTER LOGON ON DATABASE -- Put unique session identifier into client-id to have it a
Read more