Oracle Truncate Table - Storage Clause Significance

This week I performed a truncate on a table in Oracle database to recover some space in my tablespace. Even after truncate the space was not released back to the tablespace. There I realized the storage clause of the table as well as the truncate command is important.

The storage clause in the truncate command is required to be specified while performing a truncate command. The storage clause determine as to what will happened to the space released by the rows that got removed. When a truncate is issued on a table, Oracle Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter (be aware that the de-allocation depends on the min extents clause and be wary when the table is large and is imported)

The options are

Drop Storage – This means that all space used by the object would be released except for the space allocated by the minextent parameter. This space can be used by any other object or by the same object from which the data is truncated. However, if the minextent (along with the initial value) is large enough, this space is NOT released even after the truncate. For e.g. See the storage parameter for a table that got imported into my database

STORAGE (
INITIAL 2432M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)

If the table with the above definition is truncated, 2432M is not released back to the tablespace for other objects to use. However, if you perform alter table move tablespace X or Drop table, the space would be released back to the tablespace.

Reuse Storage – This means that the space is not released back to the pool instead the space is reserved for the same table. Hence if the table is occupying 50M, a truncate would not release the 50M back to the tablespace instead the space would be reserved for the same table. This option needs to be used in case you are sure that you are going to reload the similar data again into the same table.

Oracle ROWID - its Meaning

ROWID pseudo-column provides the address of a row of a table. Hence it’s the fastest way a row can be accessed in Oracle. When you look at the plan of execution for queries that uses indexes, it shows “Table access by ROWID”. This means that the scan of the indexes has decided to get the row from a set of ROWID’s and the access of the row data will be through the ROWID value for the row.

The ROWID value contains the following information

The data object number of the object –For e.g. if the row belongs to table “Employee”, the data object number can be obtained from the query “select object_id from dba_objects where object_name=’EMPLOYEE’”

The data block number in the data file that holds the row – The block number is relative to the tablespace which holds the block.

Position of the row in the data block – This is the position of the row inside the block. If the row is the first one to get inserted in the block, the position would be zero.

The data file in the database that holds the row – This would point to the data file that holds the block. The number is directly mapped to the File# in the V$DATAFILE table.

The ROWID value, when selected directly using the queries will not display in an understandable format. We need to use dbms_rowid package to retrieve the values.

select rowid,dbms_rowid.ROWID_ROW_NUMBER(rowid) ROWPOSITION,

dbms_rowid.ROWID_OBJECT(rowid) OBJECTNUMBER,

dbms_rowid.ROWID_RELATIVE_FNO(rowid) DATAFILENUM,

dbms_rowid.ROWID_BLOCK_NUMBER(rowid) BLOCKNUMBER,

name from EMPLOYEE

ROWID	ROWPOSITION	OBJECTNUMBER	DATAFILENUM	BLOCKNUMBER	NAME
AAAcv2AAOAAANHIAAA	0	117750	14	53704	Myname

The above result with respect to the row that has the name as “Myname” is

Row is placed in the data file identified with File# as 14

Row is part of the object with the id as 117750

Row holds the first position in the block

Row is placed in the block 53704 in the tablespace.

Oracle KEEP Pool - How Does it help?

When a session requests for the data, Oracle first tries to find the data in the Buffer Pool. If its not present, Oracle fetches the data from the disk (which is called the physical reads) and places in the buffer pool. Once the data is placed in the buffer pool, different sessions can access the data for completing the session requirement. However, entire data from the database cannot be placed in the buffer pool (unless you have abundant memory, still not advisable). So Oracle does a cleanup mechanism to keep the buffer pool usage in a controlled manner and within the set size limits.

If you are sure that the data from specific objects will be always accessed by the application, like a User table. When the first session access this data, the data is moved to the buffer pool. At a later point, for some reason, Oracle decides to evict this data from the buffer pool to give space for some other incoming data, the next access of the User table data will be slower as it incurs a physical read. In such cases, we use a feature called KEEP Pool.

KEEP Pool is also a buffer pool but specifically designated for objects that are defined to be using the KEEP Pool during the object creation itself. While defining the objects, it can be mentioned that the data should be pulled into the KEEP pool rather than the DEFAULT BUFFER POOL. This way, the data will not be evicted from the buffer even if the clean up mechanism of default buffer pool is kicked in. So the KEEP pool gives a higher probability for the data to be in buffer since all objects by default goes to the Default Buffer Pool only and not to the KEEP Pool. The syntax for attaching the object to the keep pool is shown below

ALTER TABLE MYOBJECT storage (buffer_pool keep);

For e.g. if there are only 20 objects(tables, indexes) defined to be in the KEEP pool, only the data from these 20 objects will be present in the KEEP Pool. Even if the default buffer pool evicts data by means of the LRU algorithm because of any reason, the data in the KEEP Pool will not be impacted.

In practice, the small tables in the application are normally the candidate for the KEEP Pool. Oracle generally tends to prefer Full table scans for smaller tables and hence the chances of the data getting evicted from the default pool is high. So these small tables can be attached to the KEEP Pool and hence will not be evicted.

When the AMM (Automatic Memory Management) is turned on for Oracle10g, keep in mind that the size of KEEP pool is not part of the SGA Target. (on a side note, so is for RECYCLE Pool, will cover the RECYCLE Pool in later blogs)

Trip to Mackinac Island

During the weekend, I made a quick trip to Mackinac Island. We spent around 6 hours on the Mackinac Island and a night in the St. Ignace City( City of the Mainland). Well, I'm not quite sure as to how I spent the 6 hours on the Island :-) .

The Mackinac Bridge connects the Lower Peninsula and Upper Peninsula of Michigan State. The bridge is the 12th longest suspension bridge in the world (wiki). The view of the bridge from the Road and from Water (while on the ferry) is spectacular. The ferry took us from the main land to the Mackinac Island. On the island, just like other tourist attractions, the market is impressive. Lot many chocolate fudges shop, restaurants, bike rentals, tour operators were seen.
We first visited the Butterflies museum. The museum had two rooms, one with many butterflies flying around and the other with few glass cases with insects. Then we visited the museum that describes the history of the Mackinac Bridge and the Island. There were other places, which were good but did not visit, which includes the Mackinac Fort (saw it from outside and did not feel that it was worth climbing the stairs), Arch Rock (again had to walk and then climb the stairs).
The impressive part of the journey was the Ferry from the Island and back. The speed jets gives you a view of the Mackinac Bridge and also of the beautiful Lake (one of the 5 Great lakes of Michigan).

High CPU usage of database

In my experience ,Oracle database is capable of taking good amount of load and can utilize the hardware power very effectively for scaling. However in some case , you might find that the CPU usage by the database is considerably high. In my experience , these factors can also cause a high CPU utilization

• Queries performing excessive work to achieve desired results. This could be more data blocks scanning, more nested loops etc.
• Table setups can be cause of concern if there is large amount of row chaining etc.
• Large amount of house keeping work on database like archival sweep, rman processes etc.
• Application keeps opening new physical connections to the database instead of using the connection pool. Even though application servers configure the connection pool, the connection pool might open new physical connections for all transactions due to wrong set up of connection pool.
• XA Transaction has a higher impact on the database as it needs to perform the house keeping activity like managing the transactions stages like prepare, commit etc .
• When less number of CPUs are present and more number of processes are present on the system, the system usage will go high due to context switching.
• As a matter of fact, the CPU utilization will go invalid or the system starts behaving in an awkward manner when the CPU utilization hits above 85%.

In some cases, correcting the above symptoms would fix the high CPU usage. However , in many other cases, it might be that the applications needs that much processing power to support the database operations.

Article on Cloud Computing

My first article on the web got published few weeks back. The article can be found @

http://ajax.sys-con.com/node/938581

The article concentrates on the online software distribution using the cloud.

Hope you enjoy reading it.

Buffer Busy Waits in Oracle

Last week, I was working with a sample program to test the performance of an oracle query under load. The program spawns multiple threads and each thread will execute the same select statement against the database. When I tried the sample program with just one thread, the select statement gave a response of 20 milliseconds. When the number of threads was increased to 60, the response time increased to 1 second per query execution. The reason- Buffer Busy Waits.

When the data is read by the query, the data is read from the physical disk to the buffer pool first. Any subsequent execute of the same query accessing the same data, Oracle will read the data from the buffer pool itself instead of reading it from the physical disk. When 60 instances tried to execute the same query, all the queries were trying to access the same set of blocks at the same time, creating HOT BLOCKS. Whenever Oracle accesses a block in the buffer pool, a latch is obtained by the process on the memory address in the buffer pool pointing to the block. For the next process (session) to access the same block, it needs to acquire a latch on the block first. When the process or session waits to get the access to the block in the buffer, buffer busy waits are caused. When all of the 60 sessions tried to execute the same query, the response time was higher as there were wait event that was happening in the background.

Apart from this, while one session reads the block from the disk to the buffer pool, other sessions trying to access the same block will need to wait till the buffer population activity by the first session is over. This is yet another cause for buffer busy waits.

Removing buffer busy waits is not simple or straightforward. The only option is to remove hot blocks i.e. blocks that are causing the contention. The possible fix is to reduce the amount of wait event by tuning the queries so that the queries execute faster and doesn’t cause noticeable waits. Another option is to avoid executing the query against the same blocks. This can be done by developing application level cache so that data is not requested frequently from the database.