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..

DCH Story

Trichy,In : It was my fifth sem, when one of the most youthful films from Bollywood hit the screen.True, am talking about Farhan Akthar's debut "Dil Chahta Hai". A story of three friends but have a great variety and reality in the movie.

Our sem had come to an end.All the hostelers had carried bag full of books n references home for the study holls. I, Jithu and Sandeep didnt had any notes. Dunno even know where to start for the "Compiler Design" "and the subject made by crazy psychos "Digital Signal Processing"(DSP). Sandeep will still manage some way.Being a lazy bum it was worse for me. I therefore decided to go home a week and return early to hostel to study. I really had fun and bugged every others who are from Kochi.. specially Alson. For me it was like vacation.. movies, outings, junk food. For the name sake every morning I opened a book n by end of the day, the fan would have turned the pages to next.

I rang my guru Devi Sujatha to help me out with DSP. Devi is a sweet person, who always had a mind to help. let me give a better intro of my pal Devi. After the first year, the real IT class was formed. Gals all studious and guys the reciprocal. I started talking to her, seeking her lab observation. Electronics dept. was the worst in the entire coll. With rusted meters, circuit diagrams and CROs, I never got any output. I copied Devi's output and get my record signed. Soon she became my best friend. Whenever we were given assignments, I would ask her to complete it as soon as possible. She made it sure that she complete her work and hand it over to me atleast 2 days before so that I can do mine comfortably.She said that she would come to coll and teach me DSP.

I returned back to college and after 10 days Jithu and Sandeep came to know that I had reached hostel. I had utilised all the days once I was back.Jithu, Sandeep joined back in the hostel. we had gained confidence that we will clear the papers.We learned 4 units from all subjects except DSP and Compiler Design.

It was one friday evening, we discussed about the characters of DCH. I had already seen the movie. Jithu was named Aamir(AKASH) of DCH who dont believe in love or sentiments.He is a kinda rebel. Whatever we said good or bad, he would oppose. Sandeep was like Akshaye Khanna, who liked to make friendship with older females. Sandeep became SIDH. Finally all have to turn to me, and ping me as SAM. We 3 decided to go for the second show. Jithu suggested he wanted to ask Arun L Menon,(a MCA guy who stayed very near to my house at Kochi) if he could join us. Jithu assured that he will be back in 10 minutes with Arun's Yes or No.

Myself and Sandeep waited for 1 hour or more for Jithu to return. It seems Jithu's ten minutes wasn't 10x60 secs.Sandeep decided, to start for DCH. I enjoyed the movie again, so did Sandeep. No news of our dear Jithu. Movie got over and we two were waiting for bus at central by 12:30 AM. We saw two familiar faces walking towards us. There comes Mr. Jithu and Arun. Jithu who went to invite Arun for DCH had gone for some english movie.

Sandeep and myself teased Jithu like anything about his 10 minutes excuse. It became a talk among all of us. Now we three are in different parts of the world, any time DCH is screened on television, we have a memorble stuff to laugh at.