ORACLE ARCHITECTURE

ORACLE ARCHITECTURE

[accordian class="" id=""]

[toggle title="Raid" open="no"]

Bookmark Fixed font Go to End

Doc ID: Note:38281.1
Subject: RAID and Oracle - 20 Common Questions and Answers
Type: FAQ
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 26-FEB-1998
Last Revision Date: 26-MAR-2000

RAID and Oracle - 20 Common Questions and Answers
==========================================

1. What is RAID?

RAID is an acronym for Redundant Array of Independent Disks. A RAID system consists of an enclosure containing a number of disk volumes, connected to each other and to one or more computers by a fast interconnect. Six levels of RAID are defined: RAID-0 simply consists
of several disks, and RAID-1 is a mirrored set of two or more disks.The only other widely-used level is RAID-5, which is the subject of this article. Other RAID levels exist, but tend to be vendor-specific, and there is no generally accepted standard for features included.

2. What platforms is RAID available for?

Third-party vendors supply RAID systems for most of the popular UNIX platforms and for Windows NT. Hardware vendors often provide their own RAID option.

3. What does RAID do?

The main feature of RAID-5 is prevention of data loss. If a disk is lost because of a head crash, for example, the contents of that disk can be reconstituted using the information stored on other disks in
the array. In RAID-5, redundancy is provided by error-correcting codes (ECCs) with parity information (to check on data integrity) stored with the data, thus striped across several physical disks. (The intervening RAID levels between 1 and 5 work in a similar way,
but with differences in the way the ECCs are stored.)

4. What are the performance implications of using RAID-5?

Depending on the application, performance may be better or worse. The basic principle of RAID-5 is that files are not stored on a single disk, but are divided into sections, which are stored on a number of different disk drives. This means that the effective disk spindle speed is increased, which makes reads faster. However, the involvment of more disks and the more complex nature of a write operation means that writes will be slower. So applications where the majority of transactions are reads are likely to give better response times, whereas write-intensive applications may show worse
performance.

Only hardware-based striping should be used on Windows NT. Software striping, from Disk Administrator, gives very poor performance.

5. How does RAID-5 differ from RAID-1?

RAID-1 (mirroring) is a strategy that aims to prevent downtime due to loss of a disk, whereas RAID-5 in effect divides a file into chunks and places each on a separate disk. RAID-1 maintains a copy of the contents of a disk on another disk, referred to a mirrored disk. Writes to a mirrored disk may be a little slower as more than one physical disk is involved, but reads should be faster as there is a choice of disks (and hence head positions) to seek the required location.

6. How do I decide between RAID-5 and RAID-1?

RAID-1 is indicated for systems where complete redundancy of data is considered essential and disk space is not an issue. RAID-1 may not be practical if disk space is not plentiful. On a system where uptime must be maximized, Oracle recommends mirroring at least the control files, and preferably the redo log files.

RAID-5 is indicated in situations where avoiding downtime due to disk problems is important or when better read performance is needed and mirroring is not in use.

6a. Do all drives used for RAID-5 have to be identical?

Most UNIX systems allow a failed disk to be replaced with one of the same size or larger. This is highly implementation-specific, so the vendor should be consulted.

7. Is RAID-5 enough to provide full fault-tolerance?

No. A truly fault-tolerant system will need to have a separate power supply for each disk to allow for swapping of one disk without having to power down the others in the array. A fully fault-tolerant system has to be purpose-designed.

8. What is hot swapping?

This refers to the ability to replace a failed drive without having to power down the whole disk array, and is now considered an essential feature of RAID-5. An extension of this is to have a hot standby disk that eliminates the time taken to swap a replacement disk in - it is already present in the disk array, but not used unless there is a problem.

9. What is a logical drive, and how does it relate to a physical drive?

A logical drive is a virtual disk constructed from one or (usually) more than one physical disks. It is the RAID-5 equivalent of a UNIX logical volume; the latter is a software device, whereas RAID-5 uses additional hardware.

10. What are the disadvantages of RAID-5?

The need to tune an application via placement of 'hot' (i.e. heavily accessed) files on different disks is reduced by using RAID-5. However, if this is still desired, it is less easy to accomplish as the file has already been divided up and distributed across disk drives. Some vendors, for example EMC, allow striping in their RAID systems, but this generally has to be set up by the vendor. There is an additional consideration for Oracle, in that if a database file needs recovery several physical disks may be involved in the case of a striped file, whereas only one would be involved in the case of a normal file. This is a side-effect of the capability of RAID-5 to withstand the loss of a single disk.

11. What variables can affect the performance of a RAID-5 device?

The major ones are:
- Access speed of constituent disks
- Capacity of internal and external buses
- Number of buses
- Size of caches
- Number of caches
- The Nature of the algorithms used for determining how reads and writes
are done.

12. What types of files are suitable for placement on RAID-5 devices?

Placement of data files on RAID-5 devices is likely to give the best performance benefits, as these are usually accessed randomly. More benefits will be seen in situations where reads predominate over writes. Rollback segments and redo logs are accessed sequentially (usually for writes) and therefore are not suitable candidates for being placed on a RAID-5 device. Also, datafiles belonging to temporary tablespaces are not suitable for placement on a RAID-5 device.

Another reason redo logs should not be placed on RAID-5 devices is related to the type of caching (if any) being done by the RAID system. Given the critical nature of the contents of the redo logs, catastrophic loss of data could ensue if the contents of the cache were not written to disk, e.g. because of a power failure, when Oracle was notified they had been written. This is particularly true of write-back caching, where the write is regarded as having been written to disk when it has only been written to the cache. Write-through caching, where the write is only regarded as having completed when it has reached the disk, is much safer, but still not recommended for redo logs for the reason mentioned earlier.

13. What about using multiple DBWRs as an alternative to RAID-5?

Using at least as many Database Writer processes (DBWR) as you have database disks will maximize synchronous write capability, by avoiding one disk having to wait for a DBWR process, which is busy writing to another disk. However, this is not an alternative to RAID-5, because it improves write efficiency. And RAID-5 usually results in writes being slower.

14. What about other strategies?

Two strategies that can be used as alternatives to RAID-5, or in addition to it, are Asynchronous I/O (aio) and List I/O (listio).

15. What is Asynchronous I/O?

Asynchronous I/O (aio) is a means by which a process can proceed with the next operation without having to wait for a write to complete. For example, after starting a write operation, the DBWR process blocks (waits) until the write has been completed. If aio is used, DBWR can continue almost straight away. aio is activated by the relevant "init.ora" parameter, which will either be ASYNC_WRITE or USE_ASYNC_IO, depending on the platform. If aio is used, there is no need to have multiple DBWRs.

Asynchronous I/O is optional on many UNIX platforms. It is used by default on Windows NT.

16. What are the advantages and disadvantages of aio?

In the above DBWR example, the idle time is eliminated, resulting in more efficient DBWR operation. However, aio availability and configuration is very platform-dependent; while many UNIX versions support it, some do not. Raw devices must be used to store the files so the use of aio adds some complexity to the system administrator's job. Also, the applications must be able to utilize aio.

17. What is List I/O?

List I/O is a feature found on many SVR4 UNIX variants. As the name implies, it allows a number of I/O requests to be batched into a "list", which is then read or written in a single operation. It does not exist on Windows NT.

18. What are its advantages and disadvantages?

I/O should be much more efficient when done in this manner. You also get the benefits of aio, so this is not needed if listio is available. However, listio is only available on some UNIX systems, and as in the case of aio, the system administrator needs to set it up and make sure key applications are configured to use it.

19. How do Logical Volume Managers (LVMs) affect use of RAID-5?

Many UNIX vendors now include support for an LVM in their standard product. Under AIX, all filesystems must reside on logical volumes. Performance of a UNIX system using logical volumes can be very good compared with standard UNIX filesystems, particularly if the stripe size (size the chunks files are divided into) is small. Performance will not be as good as RAID-5 given that the latter uses dedicated hardware with fast interconnects. In practice, many small and medium-sized systems will find that the use of logical volumes (with a suitable stripe size for the type of application) performs just as good as RAID-5. This particularly applies to systems where there is no I/O problem. Larger systems, though, are more likely to need the extra performance benefits of RAID-5.

20. How can I tell if my strategy to improve I/O performance is working?

At the UNIX level, there are several commands that can tell you if a disk device is contributing to I/O problems. On SVR4, use the 'sar' command with the appropriate flag, usually '-d'. On BSD, use the 'iostat' command. You are looking for disks whose request queue average length is short, ideally zero. Disks with more than a few entries in the queue may need attention. Also check the percent busy value, as a disk might have a short average queue length yet be very active.
On Windows NT, the Performance Monitor allows I/O statistics to be monitored easily and in a graphical manner.
It is essential to obtain baseline figures for normal system operation, so you will know when a performance problem develops and when your corrective action has restored (or improved upon) the

[/toggle]
[toggle title="Norms" open="no"]

Normalization and De-normalization

Normalization means decomposition of a relational schema that has many attributes into several schemas with fewer attributes. Careless decomposition leads to bad design and high cost access of data.
Normalization is the process of putting things right, making them normal. In Latin the word 'norma' means the square used by the carpenter, used to assure a right angle. In geometry, when a line is in at a right angle to another line, it is said "normal" to it. In RDBMS it has a specific mathematical meaning having to do with separating elements of data into affinity groups and defining the normal or right relationship between them.

A key is an attribute in the entity (Table), which identifies the entire row. Keys are different types. They are intelligent keys and non-intelligent keys. An intelligent key is based on data values such as date, a last name or a combination of values. A non-intelligent key is completely arbitrary, having no function or meaning other than identifying the row uniquely. The relations are established between the entities between / among these keys.

Relationships

The relations can be four types:
(1) One to One

In Table A one row is associated with one row in Table B and one row in Table B is associated with one row in Table A. This relationship is addressed to as one-to-one relationship.

If the two tables are related to at one to one level and the values in either table or entity are repetitive and redundant, these tables they are called in one-to-one relationship. These relations are very rare for the simple reason such relation between the entities can be reduced to a single entity instead of having two entities. This one-to-one relationship can be expressed simply as one-to-many relationship using primary and foreign key relations as seen in Oracle Demo Tables DEPT and EMP tables where DEPTNO in the DEPT table is the parent of deptno column in EMP table.

(2) One to Many

If in table A one row is associated with more than one row in Table B the relationship between these two tables is called one-to-many relationship.

This relationship can be seen between DEPT and EMP tables where one DEPT row is related to many rows in EMP table.

(3) Many to One

If in Table A more than one row is associated with one row in Table B, this relationship is called one to many relationship.

This relationship can be seen between DEPT and EMP tables given in Oracle demo Scott schema. Many employees of department number 10 in Table EMP are related to one row in DEPT Table, i.e., department number 10.

(4) Many to Many

If one row in Table A is related to many rows in Table B and a row in Table B is related to many rows in Table A the relation ship between these two tables is many-to-many.

Say that we have two tables, Customer and Account. One customer may have more than one account, say one checking account, one savings account, one credit card account and one loan account. This is one-to-many relationship. Now, more than one customer can own an account. Those customers that have joint accounts and individual accounts are also seen in the customer table as well as account table. In such circumstances the relation ship between the Customer table and Account table is many-to-many. In such circumstances neither account number nor the customer name can be unique in these two tables. Hence we need another table like, Account Type and Customer and Account Tables have 'account type' attribute in them these two tables can be related to basing on a composite key like customer name and account type and account number and account type. Essentially it is to be understood that we need three tables or entities to establish many-to-many relationship.

First Normal Form

Every table should have a primary key, and a set of repeating groups should appear in its own table. If this is satisfied, the first normal form is satisfied.

Second Normal Form

When a column value is dependent on the value of one column in another table, the tables are in parent and child relationship eliminating redundancy and this is the second normal form.


Boyce-Codd Normal Form

(This is also treated as 2nd Normal Form)


A design is BCNF if each member of the set of relational schemas that constitute the design is BCNF.

The following example taken from Database System Concepts explains the BCNF very clearly.

Customer-schema = (customer-name, customer-street, customer-city)
Customer-name -> customer-street customer-city
Brach-schema = (branch-name, assets, branch-city)
Branch-name -> assets branch-city
Loan-info-schema = (branch-name, customer-name, loan-number, amount)
Loan-number -> amount branch-name

Candidate key for customer-schema is customer-name, for branch-schema is branch-name and for loan-info-schema is loan-number. The functional dependency of the schema attributes is non-trivial in the case of customer-schema and branch-schema. But it is not so in the case of loan-info-schema. It is not in BCNF. Loan number is not a super key or a candidate key as the names may be repeated and if the data is accessed basing on the name of the loan account there cannot be loss-less join. it is due to the fact that a single loan is made to two people. But the functional dependency is nontrivial. Hence loan-info-schema is to be decomposed into two schemas.

Loan-schema = (branch-name, loan-number, amount)
Barrower-schema = (customer-name, loan-number)

This decomposition of the loan-info-schema results in loss-less join of the data and now can be said in BCNF.

Third Normal Form

Third Normal Forms Requires no non-key column depend on another non-key column.
When all the columns in a table describe and depend upon the primary key, the table is said to have satisfied third normal form.

Fourth Normal Form

Fourth Normal form forbids independent one-to-may relationships between primary key columns and non-key columns.

Fifth Normal Form

Fifth normal form breaks tables into the smallest possible pieces in order to eliminate all redundancy within a table.
If the design is in 3rd Normal Form the design achieves many of the set goals of normalization and functional advantages of relational database systems (RDBMS)

De-normalization:

It is the opposite of Normalization of entities or tables or schemas. Normalization is helps normalize the data with relational designs. This results in joining many tables together to access data as required by an application. As the number of tables increase in generating the data the usage of resource goes up and it impacts the performance of the application. Hence while designing the database, the purpose of the database is also to be understood and the designs are to be made to meet such requirements. For instance, the application is to collect data from many sources and the data so collected is to be processed, made available to the end users of data who have different purposes. In such circumstances we cannot stick to a particular design. A single database does not serve the purpose and we need multiple databases. A database that is writes intensive means a collecting database or a data-staging database (OLTP database), a database that is that processes the data (OLAP- online analytical processing, database). This database has more updates and deletes and inserts and contains all the business logic requirements and processes. This is nothing but a stage for processing the data collected from various sources. Then comes the database that hosts the processed data that is used by end users. The End-Users access the database through reports. This type of databases may be called data marts, data warehouses and decision supporting systems, depending upon the requirements.

The data warehouses, data marts and decision supporting systems hold large amounts of data and the tables which are relationally joined may takes all the time on earth to access the data required by the users. Hence the design of the databases cannot be insisted upon to be in 2nd, 3rd normal forms and hold up the performance of the database. Then there is requirement of de-normalization of the design. The tables may hold redundant data in them. Still the performance of the database is very high as we need not to join multiple tables.

The design of the databases is dependent on

(1) Establishing relations,
(2) Data accuracy and integrity,
(3) Easy accessibility and,
(4) Performance

Say an application is to collect the data from various sources and then process the data to meet the business logics and make it available to for access by end users of various types.

In this case, multiple database designs are necessary. A staging database to collect the data from various data sources. A processing database to process the data and a warehouse or mart or decision support system database for the end users.

De-normalization is to be considered where performance is important. If the normalization is followed where staging the data, the same design with no constraints (referential) and additional summary tables may be used for the OLAP and then the same design for DWH, DSS and DM.

[/toggle]
[toggle title="Database Structural ER Diagram" open="no"]

(defines relations between database, instance, schemas and other structures of database)
Srinivasacharyulu Maddali

The relations can be defined as under:

[/toggle]
[/accordian]