Oracle 10g: Use database optimized for storage system

[Maximiliano2]

I am here to get some idea about using an Oracle 10g as database. There is simple logic about databases which are the heart of many business applications or websites. The success depends processed with the speed in the database queries. If the answer to a web request too late, the visitor might be gone again and order elsewhere. The physical disk and file system on which is the database has significant influence on the performance of a database. I have dealt with methods that can be directly controlled via the Oracle database to improve performance. Also I want to know about the underlying disk and how to best prepare it to use as a database storage? I hope that you members will provide some useful notes about this topic.

[Osman84]

The files of the database consists of data blocks. The blocks contain the data of the individual segments. The server processes the user can read the blocks in the buffer cache and the background process Database Writer (DBW n) writes it back to the hard disk. You can use the I / O performance with the views V$FILESTAT and V$DATAFILE to the Performance Manager or measure. The query in the following listing provides the most important I / O statistics of the database files:

SQL> SELECT name,
FROM v $ filestat a, v $ datafile b 
WHERE a.file b.file # = #; 
system01.dbf 3140 149 0 0 2 8 
undotbs01.dbf 23 84 0 0 2 4  
sysaux01.dbf 574 666 0 0 4 8  
users01.dbf 5 2 0 0 1 0 
example01.dbf 7 2 0 0 1 2 
big_ts.dbf 5 2 0 0 1 1

[Anirvinya]

The easiest way to achieve a balance of I / O activity is to follow the guidelines of the Optimal Flexible Architecture (OFA) and distribute data and indexes on different tablespaces. In the following list is for a simple distribution strategy:

• SYSTEM - Database catalog
• USERS - Default tablespace for users
• TOOLS - Tablespace for other tools and repositories
• UNDO/RBS - UNDO - or rollback segments
• TEMP - Tablespace for temporary segments
• DATA - Tables of the application
• INDEX - Indices of the application

This is a very simple form of sharing. When multiple applications run on the database, each application should have its own data and index tablespace. In VLDBs and in the data warehouse environment, the division is even stronger, up to a separate tablespace for each partitioned table.

[Christina 80]

The idea of distributing the files on different disks can be obtained by expanding striping. Striping means that a tablespace is spread across multiple hard drives. This increases performance, since multiple read heads are employed simultaneously with reading or writing data. In Oracle 10g feature Automatic Storage Management (ASM) was introduced. ASM organized a dynamic allocation of I / O load depending on the current workload. ASM requires a separate database for the administration. Whether it's worth it to use ASM depends largely on the nature of the workload of a database. Manual striping is important if you are not using ASM. Today, often disk subsystems are used. The allocation of the disks is via Logical Volume Manager. Also, since you can make a striped and distribute the tablespace files.

[Alondra]

Today, often disk subsystems are used. The allocation of the disks is via Logical Volume Manager. Also, since you can make a striped and distribute the tablespace files.

Another problem is that the capacity of the hard drives get larger, which restricts the possibilities for distribution. Locally managed tablespaces use to manage the extents of the database catalog rather than a bitmap in the header of each file. This can be quickly released extents and acquired without the SYSTEM tablespace must be accessed. Locally managed tablespaces are able to manage a large number of extents effective and to reduce the fragmentation. This initialization parameter determines the maximum size of database blocks that the server will read the user process, when a full table scan (FTS) is running. The larger the parameter is, the more data can be read with a single access.