Oracle RDBMS: Pinning of Database Objects

This is one way to improve performance for a program that uses large packages. To understand the concept, Lets ask ourselves why pin is used in our daily life? To have a small note of important points like phone numbers or syntaxes to be recollected often or to get noticed soon. The same applies here in database. The process of placing the database packages in the Shared Global Area (SGA) is called Pinning.

SGA consists of shared pool,buffer cache, large pool, java pool, log buffer, streams pool etc. We are concerned only about shared pool now. In 10g, we can use sga_target init parameter instead of specifying size of each and every SGA components in the init.ora. This parameter will set automatic shared pool management. shared pool works on continuous memory chunks called granules.Shared pool works on LRU algorithm.

Using multiple large PL/SQL objects in the database can be hazardous job. The database has to search for free space for the object. If it cannot get enough contiguous space, it will free many small objects to satisfy the request. To search for small objects causes high CPU resources. Because of this we pin the large objects to shared pool during start up.Objects are 'kept' in the shared pool using the dbms_shared_pool package that is defined in the dbmspool.sql file.

Syntax:

execute dbms_shared_pool.keep('owner.object');

To view a list of all objects that are kept in the shared pool, one can query the v$db_object_cache :

select owner,name,type,sharable_mem from v$db_object_cache where kept='YES';

In Oracle Applications :

The $AD_TOP/sql/ADXGNPIN.sql script is provided to pin the packages in Oracle Applications. This script pins all Oracle Applications objects, which is generally not completely necessary or advisable.

This method is very useful in data warehousing environment, where we can pin the most used objects with the memory.

Oracle RDBMS: Locks, Latches

Performance is a pure dictionary word. This word has a great value in today's world.The word is added to any stuff you see around. It has different units with various objects. Well, I am concerned about computers.A computerized organization completes with hardware,software and network. There are hundreds of vendors in the market competing each other.

Let me ask you a question, What is Data and What is information? In simple words, Data is a collection of facts from which conclusions may be drawn. Information is processed data. We make many software to tackle the data and to extract information. Database is the concept of storing data.There are many vendors with various products; Oracle is the most popular one, IBM DB2, PostgreSQL, SQL Server, MySQL.Well I work in Oracle so I would like to talk about it.

How much does performance affect with database?
We require proper and complete information when we need. We don't want to loose a customer or business by late data reports. Well let me begin with some terms:

Locks - This is a mechanism to regulate concurrent access to a shared resource, at the same time providing data integrity and consistency.It is not necessary unless multiple users tries to change same line of data.

Blocking - As the literal meaning refers to, when a session holds a lock on a resource that another session is requesting. The session will be kind of hang state until lock on the resource is released. DMLs that can block are INSERT, UPDATE, DELETE, MERGE and SELECT FOR UPDATE.

Deadlocks - It is like two sessions, each of them holding a resource that other wants.

Types of Locks:
1. DML Locks
2. DDL Locks
3. Internal locks and latches

TX (Transaction Locks) : When a transaction initiates its first change and is held until the change performs a COMMIT or ROLLBACK. The session requesting the lock will be queued up waiting for that transaction to complete.

TM (DML Enqueue locks): This ensure the structure of the able remains the while modification os its contents occurs. we get as many TM locks as the objects are modified.But the total number of TM locks allowed in system is configurable.

DDL Locks - These are automactic events on objects during a DDL operation, to prevent chanes by any other sessions.The different types of DDL locks are Exclusive DDL locks, Share DDL locks, and Breakable parse locks.

Data Dictionary Views:
1. v$lock
2. v$transaction
3. v$session
4. dba_ddl_locks

v$lock has the following common modes:
0 - None
2 - Row shared (DML locks)
4 - For TX locks
6 - Exclusive for row level

If there are enqueue, you can see from
select * from v$lock where request > 0;

To view the current status of current system, query v$lock, v$session, v$latchholder, v$open_cursor and v$session_wait.

Latches - These are locks degined to hold resources for extremely short period of time.Latches are lightweight serialization devices used to coordinate multiuser access to shared data structures,objects and files. Latches are used to protect certain memory structures like db block buffer cache,library cache in shared pool.

I hope we have a notion of all these oracle terms. Lets discuss more in next posts. Performance analysis of today is big zero without wait interfaces. lets rush to modern terminologies..