Oracle on Dell Poweredge Servers

Oracle8 on Dell PowerEdge Servers

Introduction:

Selecting an appropriate hardware platform, Server Operating System and a Database Server to fulfill the needs of any organization requires tremendous amount of investment in terms of time, research, analysis of data, applications, and user demands. Dell Computer Corporation provides a solution by delivering one of the best combination of hardware and database – Oracle8 on PowerEdge Servers. The next phase in the assessment process is to select the most appropriate PowerEdge server in terms of user workload, type of application, and availability requirements from the line of available server products. The next decision that has to be made is regarding the most appropriate operating system and the database server product options. After the decision about all of the above issues has been made, the implementation and maintenance phase requires hardware and database tuning and optimization according to the needs of the application and the user requirements. This paper is intended for systems engineers, technical sales personnel, and technical support analysts. It investigates various Oracle8 server tuning and optimization issues. It can also be used as a guideline on choosing an appropriate Windows NT (Windows NT Server and Windows NT Server Enterprise Edition) and Oracle8 (Oracle8 Server and Oracle8 Enterprise Edition) combination to run on a Dell PowerEdge Server.

We start with a brief comparison between Windows NT Server 4.0 and Windows NT/SE. Then we will compare Oracle8 Server with Oracle8 Enterprise Edition followed by some features of Windows NT specific Oracle architecture. The rest of this document will be dedicated to the performance tuning and optimization issues for running Oracle8 databases on Dell’s PowerEdge Servers on Windows NT.

Windows NT Server 4.0 and Windows NT Server Enterprise Edition:

Windows NT Server Enterprise Edition is the latest edition to the Microsoft Windows NT Operating system family of products. It builds on existing features of Windows NT Server to provide extended scalability, interoperability, availability and manageability features. Windows NT Server with its 32-bit architecture allows a maximum of 4 GB of addressable memory. Out of this 4 GB of RAM, 2GB is dedicated to the kernel and only 2GB is available for applications. Windows NT/SE with its 4 GB Memory Tuning (4GT) feature, reduces the kernel memory to 1GB, making up to 3GB of memory available for applications. Also, Windows NT/SE is licensed for use on SMP servers with up to 8 processors as compared to standard Windows NT server, which is licensed for only 4 processors on SMP machines. Below is a chart, which compares various features of Microsoft Windows NT server and Windows NT Server Enterprise Editions:

Feature

NT Server

NT/SE

Multipurpose OS w/ file, print, internet/intranet, & application support / Yes / Yes
Multi-threaded micro-kernel architecture; 4GB of total addressable memory /

Yes

/ Yes
Maximum number of processors licensed for an SMP server / 4 / 8
Maximum amount of application memory (i.e., not assigned to kernel) / 2 GB / 3 GB
High-availability Microsoft Cluster Server (2-server cluster with automatic failover and single-system image management) / No / Yes
Standard cross-platform Win32 APIs, support for COM/DCOM object standards / Yes / Yes
Microsoft Transaction Server (component based transaction monitor) /

Yes

/ Yes (including high-availability on Microsoft Cluster Server cluster)
Microsoft Message Queue Server (high-performance store-and-forward queuing for reliable communication between distributed components) / Standard version / Enhanced version

Comparison between Oracle8 Server and Oracle8 EE:

Oracle8 Server: Oracle8 Server is equivalent to Oracle workgroup Server in the previous releases of Oracle. It is intended for smaller and cost effective implementations with easy to use functionality. Still Oracle8 Server and Oracle8 EE are based on the same code, so it is easy to migrate from Oracle8 to Oracle8 EE in case the business needs expand in the future. Oracle8 is focused at fulfilling workgroup and lower end departmental needs of small businesses. Even for large operations, Oracle8 and oracle8 EE can coexist and transparently communicate in a distributed environment.

Oracle8 EE: Oracle8 EE contains many high-end features, which makes it suitable for large, enterprise level systems. It can support tens of thousands of users with virtually no limits on the type or amount of data stored. Its high availability features can be used to support 72452 operations. It also supports applications based on both the relational and the object-relational model. Some features of Oracle8 EE are only available as separately licensed options. Following is a list of options, which are only available for Oracle8 EE.

Option

Description

Objects Option / The Objects option allows data to be represented, accessed, manipulated, and stored as business objects. No CREATE TYPE, ALTER TYPE, or CREATE OR REPLACE TYPE statements are possible without this option.
Partitioning Option / The partitioning option allows the definition of partitions of tables and indexes. This option is required for parallel index scans and parallel DML. No CREATE PARTITION statements are possible without this option
Advanced Networking Option / This option provides client/server, server/server network security using encryption and data integrity checking as well as enhanced user authentication services.
Enterprise Manager Performance Packs / The performance packs, which include Diagnostic pack, Tuning Pack, and Change Management Pack provide advanced set of tools for managing database environments.
Parallel Server Option / It allows multiple nodes of a loosely coupled (Shared Disk) system to share access to a single database for increased scalability and security.

To find out what options are installed and available, run the following query:

Svmgr30> select * from V$OPTION;

The query will respond with a value of TRUE for installed option and a value for FALSE for options not available at your database server.

The following chart elaborates various features of Oracle8 EE as compared to Oracle8 Server.Feature

Oracle8

/ Oracle8 EE /

Comments

Enterprise Manager

Yes

/ Yes /

Included both with Oracle8 EE and Oracle8 server.

Performance Packs

No

/ Yes /

Only the Diagnostic pack comes free with the purchase of Oracle8 EE.

Advanced Backup and recovery features

Yes

/ Yes /

This includes server-managed backup and recovery, recovery backup for online backup, online recovery, and Legato Storage Manager.

Incremental backup and recovery

No

/ Yes

Failsafe for Oracle8

Yes

/ Yes /

Comes as a separate installable product.

Bit-mapped indexes

No

/ Yes

Parallel Operations

No

/ Yes /

These features include Parallel Execution, Parallel load, Parallel Query, Parallel DML, Parallel Index Scans, Parallel Bitmap Star Joins, Parallel Index Build, and Parallel Analyze. These features require the Partitioning Option, which is only available with Oracle8 EE.

Programming Interfaces

Yes

Including Oracle Call Interface, Objects for OLE, ODBC driver, Pro*C/C++

Object Features

Yes

Including Object references (REFs), object collections, nested tables, variable arrays, and object views

Distributed Features

Yes

These include distributed queries, distributed transactions, two phase commit, heterogeneous services, basic replication, read only snapshots, subquery subsetting, primary key based snapshots, internal triggers, and replicated LOBs.

Advanced Replication

Yes

Advanced replication features include updateable snapshots, multimaster replication, conflict detection and resolution, replication manager, parallel propagation, and minimized communication.

Oracle Names

Yes

Connection Manager

Yes

Connection Pooling

Yes

Connection Multiplexing

Yes

Mutiprotocol Connectivity

Yes

Security Server

Yes

Advanced Queuing

Yes

Reverse key Indexes

Yes

Password management

Yes

Index-Organized Tables

Yes

Stored procedures and triggers

Yes

INSTEAD OF triggers

Yes

External Procedures

Yes

National Language Support

Yes

LOB support

Yes

ConText Cartridge

Yes

This Cartridge offers full text retrieval

Video Cartridge

Yes

Image Cartridge

Yes

Provides Image storage, retrieval and image format conversion capabilities through Object Types

VIR Cartridge

Yes

Visual Information Retrieval

Time Series Cartridge

Yes

Provides storage and retrieval of time-stamped data through Object Types.

Spatial Data Cartridge

Yes

This Cartridge is designed to store, retrieve and spatial data easily for Geographic Information System Users.

Oracle8 Database Features on NT:

Maximum Database Block Size: 16384 bytes

Maximum Database Blocks per Oracle datafile = 4 million

Maximum Oracle datafile size = 64 GB

Maximum number of Oracle datafiles per database = Depends on the database block size

Database Block Size

Maximum number of datafiles

Block Size = 2KB / Max. datafiles per database = 20,000
Block Size = 4 KB / Max. datafiles per database = 40,000
Block Size = 8 KB and above / Max. datafiles per database = 65,536

Maximum Database Size = 41015 = 4 Peta Bytes

Oracle8 Architecture Overview:

Oracle8 for Windows NT is a 32-bit implementation written on Microsoft 32 API. Oracle on Windows NT runs as a single process, multithreaded architecture and it fully conforms to Windows NT memory model. Fig. 1 shows a model of Oracle8 instance.

The Oracle8 Instance on Windows NT consists of the System Global Area (SGA) and background threads. All Oracle8 threads share the single Oracle8 process address space. Following is the description of memory areas of which SGA consists of.

Major memory structures in SGA:

Database Buffer Cache: The database buffer cache holds copies of data blocks read from datafiles. All users concurrently connected to the system share access to the database buffer cache. An optimal number of database buffers reduce disk I/O and improve performance. The initSID.ora parameter DB_BLOCK_BUFFERS determines the size of the database buffer cache.

Redo Log Buffer: Is a circular buffer in SGA, containing information about changes made to the database. The contents are flushed to the redo log files by the LGWR background thread. The initSID.ora parameter LOG_BUFFERS sets the size of redo log buffer in the SGA.

Shared Pool: Is the area in the SGA that contains memory constructs such as the data dictionary cache, library cache and shared SQL area. This area contains a parsed form of SQL statements so, the similar statements can be re-executed without reparsing. The parameter SHARED_POOL_SIZE determines the shared area size in bytes.

Oracle8 Background Threads on NT: Following is a description of some important background Oracle threads on Windows NT:

Database Writer (DBWR) thread: The database writer is responsible for writing the modified data block from the buffer cache to the SGA. Under Oracle Performance Monitor utility, it is represented as thread number 3.

Log Writer (LGWR) thread: The log writer writes the redo log entries from the redo buffer cache to the online redo log files on the disk. It is represented as thread number 4 under performance monitor.

Checkpoint (CKPT) thread: Optionally started to update the System Change Number in the data files and the control files. If this thread is not started then the LGWR thread assumes the responsibility of CKPT. For performance enhancement this thread should be enabled via the initSID.ora parameter CHECKPOINT_PROCESS = TRUE.

System Monitor (SMON) thread: This thread performs instance recovery at instance startup. It also reclaims temporary segment space and coalesces contiguous areas of free space in data files. It is thread number 5 under process monitor.

Processor Monitor (PMON) thread: Process monitor cleans up abnormally terminated connections, rolls back uncommitted transactions, frees SGA resources and releases locks held by terminated processes. It is shown as thread number 2 under process monitor.

Recovery (RECO) thread: This thread resolves distributed transactions in case there is a network or system failure. This one is thread number 6 in performance monitor utility.

Archiver (ARCH) thread: It copies the online redo log files to another location on disk specified in the initSID.ora file from where they can be written to a tape device.

Oracle Storage Architecture: Fig. 2 depicts how Oracle stores data.

Oracle Database:

An Oracle database is a collection of data that is treated as a unit. The general purpose of a database is to store and retrieve related information efficiently. An Oracle database has logical structures and physical structures.

Logical Database Structures:

The logical database structure includes tablespaces, schema objects, segments, extents and data blocks. Following is a description of each of these logical database structures:

Tablespaces:

The logical storage units of an Oracle database are called tablespaces. An Oracle database consists of one or more tablespaces. A tablespaces belonging to a database in turn can consist of one or more datafiles on the disk. The combined size of a tablespace’s datafiles is the total storage capacity of the tablespace.

Schema and Schema Objects:

Schema is a collection of objects in a database like tables, views, sequences, stored procedures, synonyms, indexes, clusters and database links. Schema objects can belong to different tablespaces.

Data Blocks:

This is the basic unit of data storage in an Oracle database. One data block consists of a specific number of bytes of physical database space on disk. One database Block should be a multiple of operating system block size. A data block size is specified at the creation of an Oracle database and cannot be change unless the database is recreated.

Extents:

The next level of logical database space is called an extent. An extent is a specified in number of contiguous data blocks.

Segments:

The logical database storage level above an extent is called a segment. A segment is a collection of extents allocated for a certain database object. For example table data, index data, rollback data and temporary data are stored in data segments, index segments, rollback segment and temporary segments respectively.

Physical Database Structures:

The physical structures of an Oracle database consist of datafiles, redo log files, and control files.

Datafiles:

Every Oracle database has one or more physical datafiles. The datafiles contain all the database’s data. The data of the logical database structures such as tables and indexes is physically stored in datafiles belonging to an Oracle database. One or more datafiles form a logical unit of database storage called a tablespace. A datafile can only belong to one tablespace in a database.

Redo log files:

Every Oracle database has a set of two or more redo log files. The primary function of redo log files is to store all changes made to the data. In case of a system or media failure, the changes made to the data can be applied from the redo log files and the work is never lost.

Control Files:

Every Oracle database has a control file. The control file contains entries that specify the physical structures of the database. It contains information such as, database name, names and locations of database’s datafiles and redo log files, and time stamp of the database creation.

Major Contributors to performance Bottlenecks on NT:

Following is a description of four major resources on an NT system that need tuning in order to enhance system performance:

1) System Memory tuning:

In any production system, memory is limited. Processes contend for available memory which in turn forces the operating system to perform paging and swapping. The main memory area that needs tuning on any Oracle system is the System global Area or SGA.

Tuning the System Global Area:

The SGA should always be contained in the main memory. Otherwise the database would not start. To view the total memory allocated to the SGA, at the server manager prompt, type:

Svrmgr30> show SGA;

The query returns the sizes in bytes of the following values:

1. Total system Global Area

2. Fixed Size

3. Variable Size

4. Database Buffers

5. Redo Buffers

Where:

Total System Global Area = Fixed Size + Variable Size + Database Buffers + Redo buffers.

The ‘Fixed Size’ is determined by the installed Oracle products and options and does not change as long you do not add or remove some of the installed products. The ‘Variable Size’ is determined from the initSID.ora file parameters such as the values for SHARED_POOL_SIZE, PROCESSES, SESSIONS, and TRANSACTIONS at startup. The value of ‘Total System Global Area’ should always be less than the total physical memory available on your machine. The major contributors to the size of SGA are the following initSID.ora parameters:

DB_BLOCK_SIZE

DB_BLOCK_BUFFERS

LOG_BUFFERS

SHARED_POOL_SIZE

On Windows NT/E, the total address space available to applications is 3GB. Any physical memory more than this value is a waste on windows NT. The address space available for Win32 applications (including Oracle) is only 2GB. The other 1GB of memory is used for system DLL’s. In any Oracle on NT implementation, the SGA, memory taken by all client connections and any other win32 applications should fit into this 2GB virtual address space. The next major release of Windows NT might increase this limit to 4GB for win32 applications.

Oracle SGA exists as virtual memory. Because, NT does not allocate any physical memory to any process unless it is actually used, Oracle does not have its SGA in the real memory when it is started. This causes slower performance at startup because page faults (reading from the page file in 4KB page sizes to the real memory) occur in order to allocate pages of SGA to the physical RAM. To avoid that set initSID.ora parameter:

PRE_PAGE_SGA=TRUE

This would force Oracle to touch all the pages of the SGA so that they all get committed in the physical memory. This would result in slow startup but better performance for regular Oracle activity.

Tuning the Database Buffer cache:

The database buffer cache size is determined by the initSID.ora parameter DB_BLOCK_BUFFERS. This value accompanied by the value of DB_BLOCK_SIZE determines the size of the database buffer cache.

Database Buffer Cache (Bytes) = DB_BLOCK_BUFFERS * DB_BLOCK_SIZE

This buffer holds copies of blocks of tables, indexes, and rollback segments. The hit ratio indicates what percentage of data Oracle can find in memory. The hit ratio is calculated by: