Being an Oracle database operator or administrator can be a demanding but rewarding career that carries with it a great deal of responsibility as well as authority. This book is intended to help you embark on this exciting path. I hope that within the pages of this book I can convey some of the enthusiasm and excitement I feel when working with state-of-the-art hardware and software such as Oracle8.

I think the best way to grasp a concept is to fully understand why actions are taken and the consequences of those actions. If you understand how Oracle works and its interactions with the operating system and hardware, you can more easily predict and anticipate the result of actions you take. In this book, I attempt to fully explain the workings of Oracle and the supporting software and hardware.

A Brief History of Oracle

In 1977, Larry Ellison, Bob Miner, and Ed Oates formed a company called Relational Software Incorporated (RSI). This company built an RDBMS called Oracle. Ellison, Miner, and Oates made a key decision: to develop their RDBMS using C and the SQL interface. Soon after, they came out with version 1, a prototype. In 1979, RSI delivered its first product to customers. The Oracle RDBMS version 2 worked on the Digital PDP-11 running the RSX-11 operating system and was soon ported to the DEC VAX system.

1983 heralded the release of version 3, which touted changes in the SQL language as well as performance enhancements and other improvements. Unlike earlier versions, version 3 was written almost entirely in C. At this point, RSI changed its name to Oracle Corporation.

Oracle version 4 was released in 1984. This version supported both the VAX system and the IBM VM operating system. Version 4 was the first version to incorporate read consistency. Version 5, introduced in 1985, was a milestone because it introduced client/server computing to the market with the use of SQL*Net. Version 5 was also the first MS-DOS product to break through the 640KB barrier.

In 1988, Oracle presented version 6, which introduced low-level locking as well as a variety of performance improvements and functionality enhancements, including sequence generation and deferred writes. I was introduced to Oracle6 back in the days when we ran the TP1, TPC-A, and TPC-B benchmarks. At this point, Oracle was running on a large variety of different platforms and operating systems. In 1991, Oracle introduced the Oracle Parallel Server option on version 6.1 of the Oracle RDBMS on the DEC VAX platform. Soon the Parallel Server option was available on a variety of platforms.

Oracle7, released in 1992, included many architectural changes in the area of memory, CPU, and I/O utilization. Oracle7 is the full-featured RDBMS to which you are accustomed, the one you've been using for many years. Oracle7 introduced many advances in the area of ease of use, such as the SQL*DBA tools and database roles.

Finally, in 1997 Oracle introduced Oracle8, which added object extensions as well as a host of new features and administrative tools.

For more information about the history of Oracle (specifically about the Oracle server), check out the two-part article by Ken Jacobs in the January/February and March/April 1995 issues of Oracle Magazine.

For more information about the Oracle corporation, its products, and about working with Oracle, check out www.oracle.com. This Web site contains a wealth of information about Oracle parterships and products as well as information about the Oracle Developer Program, which specifically assists developers.

Introduction to Terms

Many different terms and concepts will be used throughout this book. I've introduced them here to make it easier for you to grasp many of the concepts and lessons to follow. If you encounter other terms with which you are unfamiliar, check out Appendix D, "Glossary."

Ad-Hoc Query

This use of the Latin term means an impromptu, simple query.

Block

A block is the smallest unit of storage in an Oracle database. The database block contains header information concerning the block itself as well as the data or PL/SQL code. The Oracle block size is configurable with the minimum size being 2KB and the maximum size being 16KB.

Bottleneck

In computer terms, a bottleneck is a system component that limits the performance of the system.

Buffer

This term refers to an amount of memory used to store data. A buffer stores data that is about to be used or that has just been used. In many cases, buffers are in-memory copies of data that is also on disk. Buffers can be used as a copy of data for quick read access, they can be modified and written to disk, or they can be created in memory as temporary storage.

In Oracle, database buffers of the SGA store the most recently used blocks of database data. The set of database block buffers is known as the database buffer cache. The buffers used to temporarily store redo entries until they can be written to disk are known as redo log buffers.

Cache

A cache is a storage area used to provide fast access to data. In hardware terms, the cache is a small (relative to main RAM) amount of memory that is much faster than main memory. This memory is used to reduce the time it takes to reload frequently used data or instructions into the CPU. CPU chips themselves contain small amounts of memory built in as cache.

In Oracle, the block buffers and shared pool are considered caches because they are used to store data and instructions for quick access. Caching is very effective in reducing the time it takes to retrieve frequently used data.

Caching usually works using a least recently used algorithm. Data that has not been used for a while is eventually released from the cache to make room for new data. If data is requested and is in the cache (a phenomenon called a cache hit), the data is retrieved from the cache, preventing it from having to be retrieved from memory or disk. After the data has been accessed again, it is marked as recently used and put on the top of the cache list.

Checkpoint

A checkpoint is an operation that forces all changed, in-memory data blocks to be written out to disk. This is a key factor in how long the database takes to recover in the event of a failure. This concept is discussed in depth on Day 2, "Exploring the Oracle Architecture."

Clean Buffer

A clean buffer is a buffer that has not been modified. Because this buffer has not been changed, it is not necessary for the DBWR to write this buffer to disk.

Concurrency

This term refers to the capability to perform many functions at the same time. Oracle provides for concurrency by allowing many users to access the database simultaneously.

Database

A database is a set of data, organized for easy access. The database is the actual data. It is the database that you will be accessing when you need to retrieve data.

Data Dictionary

The data dictionary is a set of tables Oracle uses to maintain information about the database. The data dictionary contains information about tables, indexes, clusters, and so on.

DBA (Database Administrator)

The DBA is the person responsible for the operation, configuration, and performance of the database. The DBA is charged with keeping the database operating smoothly, ensuring that backups are done on a regular basis (and that the backups work), and installing new software. Other responsibilities might include planning for future expansion and disk space needs, creating databases and tablespaces, adding users and maintaining security, and monitoring the database and retuning it as necessary. Large installations might have teams of DBAs to keep the system running smoothly; alternatively, the tasks might be segmented among the DBAs.

DBMS or RDBMS

The Database Management System is the software and collection of tools that manages the database. Oracle software is the DBMS. A Relational Database Management System is a DBMS that is relational in nature. This means that the internal workings access data in a relational manner. Oracle is an RDBMS.

DDL (Data Definition Language) Commands

These commands are used in the creation and modification of schema objects. These commands provide the ability to create, alter, and drop objects; grant and revoke privileges and roles; establish auditing options; and add comments to the data dictionary. These commands are related to the management and administration of the Oracle database. Before and after each DDL statement, Oracle implicitly commits the current transaction.

Dirty Buffer

A dirty buffer is a buffer that has been modified. It is the job of the DBWR to eventually write all dirty block buffers out to disk.

DML (Data Manipulation Language) Commands

These commands allow you to query and modify data within existing schema objects. Unlike the DDL commands, a commit is not implicit. DML statements consist of DELETE, INSERT, SELECT, and UPDATE statements; EXPLAIN PLAN statements; and LOCK TABLE statements.

Dynamic Performance Tables

These tables are created at instance startup and used to store information about the performance of the instance. This information includes connection information, I/Os, initialization parameter values, and so on.

Function

A function is a set of SQL or PL/SQL statements used together to execute a particular function. Procedures and functions are identical except that functions always return a value (procedures do not). By processing the SQL code on the database server, you can reduce the number of instructions sent across the network and returned from the SQL statements.

IM (Information Management)

This term is usually used to describe the department that handles your corporate data.

IS (Information Systems)

This term is also used to describe the department that handles your corporate data.

IT (Information Technology)

This term is used to describe the business of managing information.

Network Computing Architecture (NCA)

The Network Computing Architecture is a standard for computing over the network. The NCA was developed in conjunction with Oracle.

Physical Memory

This term refers to the actual hardware RAM (Random Access Memory) available in the computer for use by the operating system and applications.

Procedure

A procedure is a set of SQL or PL/SQL statements used together to execute a particular function. Procedures and functions are identical except that functions always return a value (procedures do not). By processing the SQL code on the database server, you can reduce the number of instructions sent across the network and returned from the SQL statements.

Program Unit

In Oracle, program unit is used to describe a package, a stored procedure, or a sequence.

Query

A query is a read-only transaction against a database. A query is generated using the SELECT statement. Users generally distinguish between queries and other transaction types because a query does not the change data in the database.

Schema

A schema is a collection of objects associated with the database.

Schema Objects

Schema objects are abstractions or logical structures that refer to database objects or structures. Schema objects consist of such things as clusters, indexes, packages, sequences, stored procedures, synonyms, tables, views, and so on.

System Global Area (SGA)

The SGA is a shared-memory region that Oracle uses to store data and control information for one Oracle instance. The SGA is allocated when the Oracle instance starts; it is deallocated when the Oracle instance shuts down. Each Oracle instance that starts has its own SGA. The information in the SGA is made up of the database buffers, the redo log buffer, and the shared pool; each has a fixed size and is created at instance startup.

Transaction

A transaction is a logical unit of work consisting of one or more SQL statements, ending in a commit or a rollback. Performance measurements often use the number of transactions per second or per minute as the performance metric.

Trigger

A trigger is a mechanism that allows you to write procedures that are automatically executed whenever an INSERT, UPDATE, or DELETE statement is executed on a table or view. Triggers can be used to enforce integrity constraints or automate some other custom function.

Virtual Memory

This term refers to the memory that can be used for programs in the operating system. To overcome the limitations associated with insufficient physical memory, virtual memory allows programs to run that are larger than the amount of physical memory in the system. When there is not enough physical memory in the system, these programs are copied from RAM to a disk file called a paging or swap file. This arrangement allows small systems to run many programs. You pay a performance penalty when the computer pages or swaps.

Storage Units

Data is stored in the computer in a binary form. The units used to refer to this binary data are as follows:

Term	Definition	Comment
bit	The smallest unit of data storage	A bit is either a 1 or a 0.
nibble	4 bits	This term is not commonly used.
byte	8 bits	The most commonly used storage unit.
word	This term is architecture	On some systems, a word is 16 bits;
dependent	on others, a word is 32 or 64 bits.
kilobyte (KB)		Even though kilo usually means 1,000, a kilobyte in computer terms is actually 1,024 bytes (because we like powers of 2).
megabyte (MB)		The term megabyte denotes 1,024KB or 1,048,576 bytes.
gigabyte (GB)		A gigabyte is 1,024 megabytes or 1,073,741,824 bytes.
terabyte (TB)		A terabyte is 1,024 gigabytes or 1,099,511,627,776 bytes.

It is not uncommon to hear large data warehousing sites talk in terms of terabytes. In the next few years, you will probably hear of systems using storage in the tens and hundreds of terabytes.

Oracle Configurations

There are many different types of Oracle configurations and uses. Let's look at some of these different types of systems and analyze their usage and characteristics.

OLTP

The Online Transaction Processing (OLTP) system is probably the most common of the RDBMS configurations. OLTP systems have online users that access the system. These systems are typically used for order-entry purposes, such as for retail sales, credit-card validation, ATM transactions, and so on.

Characteristics of OLTP Systems

OLTP systems typically support large numbers of online users simultaneously accessing the RDBMS. Because users are waiting for data to be returned to them, any excessive response time is immediately noticeable. OLTP systems are characteristically read and write intensive. Depending on the specific application, this read/write ratio might vary.

DSS

The Decision Support System (DSS) is used to assist with the decision-making process. These decisions might be based on information such as how sales in a particular region are doing, what cross-section of customers is buying a particular product, or to whom to send a mailing. The DSS system is used to help make decisions by providing good data.

Characteristics of a DSS

The DSS is characterized by long-running queries against a large set of data. Unlike the OLTP system, where users are waiting for data to return to them online, here users expect the queries to take minutes, hours, or days to complete. The data is typically generated from a different source and loaded onto the DSS computer in bulk. Except for during the load, the DSS system is characterized by being read intensive (with very few writes).

Data Warehouse

A data warehouse is typically considered to be a large-scale system that consists of both DSS and OLTP components. These systems are typically hundreds of gigabytes in size and support many users.

Characteristics of a Data Warehouse

Data warehouses have some of the attributes of a DSS system, such as long-running queries and a possible online component. In many cases, this component is the source of the data used in the DSS queries.

Data Mart

A data mart, which is a smaller-scale version of a data warehouse, serves many of the same functions as a data warehouse.

Characteristics of a Data Mart

A data mart is typically 100GB or less in size. As with a data warehouse, a data mart supports many online users as well as a decision-support function.

Video Server

A video server can support large numbers of video data streams. These video streams can be used for purposes such as video on demand for entertainment as well as training functions.

Characteristics of a Video Server

The video server system must support a high network bandwidth in order to support multiple data streams. The video server must also be able to support a high I/O bandwidth. These disk accesses are typically of a very large block size and sequential in nature.

Web Server

The Oracle Web server is designed to support both static and dynamic Web pages. These pages can be simple Web pages or complex database-generated pages. Oracle Web server systems are also typically used in Web commerce applications. These installations can allow the customer to browse online catalogs, which might feature graphics or even video. The customer can then purchase items online.

Characteristics of an Oracle Web Server

The Oracle Web server typically supports many online users. There is typically a large amount of data that has been accessed frequently and other data that is less frequently accessed. A large amount of memory can help improve performance in this type of configuration.

OLAP

The term OLAP (Online Analytical Processing) is usually used in relation with multidimensional data. OLAP users might be financial analysts or marketing personnel looking at global data.

Characteristics of an OLAP System

An OLAP system typically involves a large amount of disk space with heavy I/O and memory requirements. An OLAP system might support only a few or many users. This depends on your type of configuration.

Roles and Responsibilities of an Oracle DBA

If you want to become an Oracle DBA, you should first understand what an Oracle DBA's job is. The basic roles of the DBA are fairly consistent among different companies, but these duties might be expanded based on the size of the company and the experience of the DBA. In fact, the DBA is considered the main resource for DBMS experience and knowledge in many companies.

Let's look at these roles and responsibilities and determine what skills are necessary to fulfill these duties. Here the roles and responsibilities are divided into two categories: basic duties and additional duties. The dividing line between these is not clear; there is significant overlap.

Basic Duties of the DBA

Here are some of the basic roles of the Oracle DBA. This is not an all-inclusive list. Depending on your installation and staff, your duties might not include all of these, or might include many more items. This section is simply intended as a general guide.

• Installation of new software--It is primarily the job of the DBA to install new versions of Oracle software, application software, and other software related to DBMS administration. It is important that the DBA or other IS staff members test this new software before it is moved into a production environment.

• Configuration of hardware and software with the system administrator-- In many cases the system software can only be accessed by the system administrator. In this case, the DBA must work closely with the system administrator to perform software installations, and to configure hardware and software so that it functions optimally with the DBMS.

• Security administration--One of the main duties of the DBA is to monitor and administer DBMS security. This involves adding and removing users, administering quotas, auditing, and checking for security problems.

• Performance tuning and monitoring--The DBA must continually monitor system performance and be prepared to retune the system as necessary. Even a well-tuned system must be constantly monitored and adjusted. Sometimes this involves changing tuning parameters, other times this involves rebuilding an index or restructuring a table.

• Backup and recovery--Perhaps the most important responsibility of the DBA is protecting the data in the system. To effectively do this, you must develop an effective backup and recovery strategy and make sure it is carried out. A DBA's chief responsibility is to maintain the integrity of the database. It is important that the backup and recovery process be periodically tested.

• Routine scheduled maintenance--It is the job of the DBA to schedule routine DBMS maintenance and carry out this maintenance. This maintenance is regularly carried out in the early hours of the morning or on weekends when this maintenance causes the least inconvenience to the user community.

• Troubleshooting:--In the event of a system or DBMS failure, it is the job of the DBA to troubleshoot or assist in the Troubleshooting: of the problem. The DBA might also participate in or lead the effort to find and eliminate problems or potential problems.

• Failure recovery--Because a system failure can mean that the users do not have access to their data, it can be the job of the DBA to lead efforts to recover from system failures. The well-prepared DBA has contingency plans for system outages and can soon have the DBMS running again.

Additional Duties of the DBA

Some of the more advanced duties of the Oracle DBA might include the following:

• Data analysis--The DBA will frequently be called on to analyze the data stored in the database and to make recommendations relating to performance and efficiency of that data storage. This might relate to the more effective use of indexes or the use of some feature such as the Parallel Query option.

• Database design (preliminary)--The DBA is often involved at the preliminary database-design stages. Through the involvement of the DBA, many problems that might occur can be eliminated. The DBA knows the DBMS and system, can point out potential problems, and can help the development team with special performance considerations.

• Data modeling and optimization--By modeling the data, it is possible to optimize the system layout to take the most advantage of your I/O subsystem.

• Assisting developers with SQL and stored procedure development--The DBA should be prepared to be a resource for developers and users. The DBA is often called on to help with SQL problems as well as to design and write stored procedures.

• Enterprise standards and naming conventions--Because many different groups might perform different roles in developing and deploying applications, it is often the DBA who is called on to help define enterprise standards and naming conventions as well as to ensure that new applications are conforming to these standards.

• Development of production migration procedures--Because the DBA is responsible for the availability and reliability of the DBMS and applications using that DBMS, it is up to the DBA to develop and maintain procedures for rolling out new applications and DBMS software. This involves evaluating new software or patches as well as testing them. It is up to the DBA to guarantee the stability and robustness of the system.

• Environmental documentation--The DBA should document every aspect of the DBMS environment, including hardware configuration and maintenance records, software updates, changes to the applications and DBMS, and all other items related to changes made to the system. The DBA should be able to access these records and fully reproduce the current system as necessary.

• Consult with development team and end users--The DBA is often called on to act as a consultant to the development team as well as to the user community. This might involve personally assisting a single user or developing training courses for the user community as a whole.

• Evaluation of new software--The DBA might be called on to evaluate new software and make recommendations based on that evaluation. This might be related to a software purchase or a scheduled rollout of a new version of software. This evaluation must be done in the context of the stability of the system. It is your responsibility to maintain system stability and reliability.

• Evaluation of new hardware and software purchases--There is much consideration involved in purchasing new hardware and software. Much of this consideration involves the functionality and compatibility of the software or hardware as well as the cost of these components. Although the cost of the item is not usually a concern of the DBA, the functionality and compatibility is. The DBA might be asked to make recommendations based on whether these purchases make sense.

• Capacity planning and sizing--Determining whether it is necessary to purchase new hardware or software to meet increased loads is often a job for the DBA. Capacity planning and sizing is important to provide the level of service your users require. By anticipating the future needs of your users, you can provide an excellent level of service with no interruptions.

Summary

This lesson introduces some of the topics you will see in the rest of the book. First you saw a brief history of how Oracle got where it is today. Then you examined number of terms that you will see throughout the book. These terms are important; you will use them every day in your job as a DBA. Finally, you were presented with some of your tasks and responsibilities as a DBA.

What's Next?

Tomorrow's lesson examines the structure and operation of Oracle. By having an understanding of how Oracle works, you can better understand how to administer it. You will look at some of the new features in Oracle8, as well as receive an overview of Oracle performance.

Q&A

Q What units of measurement are typically used in databases?

A With Oracle systems you usually discuss size in terms of megabytes and gigabytes, but some systems are growing into the terabyte range.

Q Are the duties of the DBA the same for all companies?

A No, far from it. No two sites are the same. Although the basic duties and responsibilities might be similar, the extended duties are always different.

Q Why is it important to document?

A If you document the system configuration and logging changes, you will have a much easier time reproducing the system in the event of a failure. By having configuration information in a log book you can save numerous hours of trial and error in reconfiguring the system.

Workshop

The workshop provides quiz questions to help you solidify your understanding of the material covered. For answers to quiz questions, see Appendix A, "Answers."

Quiz

1. How is a DBMS different from a database?

2. What is a DDL statement?

3. What is a DML statement?

4. What are some of the characteristics of an OLTP system?

5. What are some of the characteristics of a DSS system?

6. State five duties of an Oracle DBA.

7. What is the most important duty of an Oracle DBA?

---

© Copyright, Macmillan Computer Publishing. All rights reserved.