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.
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.
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."
This use of the Latin term means an impromptu, simple query.
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.
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.
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.
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.
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.
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).
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.
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.
A data mart, which is a smaller-scale version of a data warehouse, serves many of the same functions as a data warehouse.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Some of the more advanced duties of the Oracle DBA might include the following:
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.
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.
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.
The workshop provides quiz questions to help you solidify your understanding of the material covered. For answers to quiz questions, see Appendix A, "Answers."
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.