1304-1306

Previous Table of Contents Next

Page 1304

• What will your current and future applications require in RAM, disk storage, and processor I/O?
• What are your concurrency requirements in RAM, disk storage, and processor I/O per user ?
• Transaction type and percentages of: online, batch, select, insert, update, or delete?
• Is the ability to double throughput by adding another processor important?

When your data answering these questions is collected, it should illustrate which file servers you should consider. This should strategically allow for the inclusion of one or more additional Parallel Processors (PP), Symmetrical Multi-Processors (SMP), or Massively Parallel Processors (MPP) as an optional upgrade path for performance improvements if needed.

The logical design should be used to guide the platform design process by providing maximum throughput, ease of use, and ease of maintenance.

The platform and physical design information is then used to distribute tablespaces on opposite disks of the high volume and heavy load queries so as to minimize disk trashing of tablespace files and elimination of I/O contention .

The high-volume queries should access table and index data, spread across different disks so as to minimize disk contention between users and queries, referencing the proposed physical design.

Remember when calculating index size space estimates, indexes can be as large as tables, and frequently total 150 percent larger than actual table sizes, in megabytes.

Parallel Index Platform Design

Disk access is a major performance bottleneck. Server performance degrades as disk I/O occurs. An index on selective columns can greatly improve server performance as the index reduces disk I/O. All database servers support B-tree indexes. B-trees store key values and their physical storage addresses in a hierarchical tree of index pages.

Calculating Legacy Tablespace and Data File Sizes for the Parallel Platform

Tablespaces are composed of the physical data files on the disks where the data actually resides.

Select * from sys.DB_FILES is the maximum number of Oracle data files that can be opened per database on that operating system. Select * from sys.DBA_DATA_FILES lists the current data files, sizes, current status and paths used by that Oracle database.

Indexspaces are identical to a tablespace except for the index objects which should exclusively occupy this space. Because of the physical disk separation of tables and indexes, queries using the indexes and retrieving table data from non-indexed columns use two different disks, cutting in half the read/write time.

When a table is "striped," it uses multiple data files. These are optimally spread across multiple disk drives for the fastest table and index access possible.

Page 1305

Table Sizing and Types for Parallel Processing Platform Disk Design

When tables are created through the execution of Data Definition Language (DDL), storage parameters are given for the initial sizing and data file. This is the specific disk and full path of the objects creation and for the subsequent or future space allocation extension, when additional storage space will be required.

The initial parameter is the size in kilobytes or megabytes of the first contiguous extent. This is composed of Oracle blocks, in which the data is actually stored internally to the database. The normal block size is 2K minus about 50_100 bytes, which Oracle uses internally.

The available contiguous disk space must be as large as these "initial" values. A buffer of between 33 percent to 50 percent, additional RDBMS reserved disk space, is reasonable from a platform design standard. This can be queried from the Oracle view by using the following command:

 Select * from sys.DBA_FREE_SPACE 

When an existing table has two or more extents, it should be reorganized for performance considerations and to avoid the famous ORA-1547 Failed to allocate extent of size (number of extents needed) in tablespace (tablespace name ) error message. This indicates that your object cannot grow without the addition of a new data file for the tablespace and table or tablespace import with the compress files=Y export parameter option.

Before redesigning your tablespaces, be sure that your tablespaces having data files greater than one can be unified on your proposed disk layout, unless they are striped tablespaces. This will ensure that your largest objects will fit into the new single extents. Also verify that percent free is zero, or you may be surprised that your contiguous free space is rapidly used up when new data requires additional extents.

Temporary Segment Parallel Processing Platform Design

A temporary segment is opened for each active user in the Oracle RDBMS. Optimally the temporary tablespace is a "striped" tablespace, or one in which the multiple data files associated with this tablespace are spread across multiple disks. The temporary tablespace and default tablespace are both ALTER USER modifiable parameters.

Parallel Processing Platform Backup Device Design

If archive mode is enabled, the archive destination path should point to a fast, removable media such as a WORM-CD, Cybernetics CY-8505 8mm tape device, or Digital Linear Tape device (DLT) featuring compression capable of storing up to 24GB per tape or 5GB uncompressed. A standard 2.2_5GB Exabyte 8mm tape drive may also be used, offering less capacity for the same price.

These three tape devices offer the capability to store from 8GB up to 40GB compressed onto a single tape or 5GB uncompressed per tape. This eliminates the need for attended backup tape changers and automated tape-changing hardware, which can fail. The Cybernetics read/write rates vary up to 85MB per minute when used with an optimum SCSI II interface.

Page 1306

The fastest backup device available is the Digital Linear Tape device (DLT). The data backup or "transfer" rate is a speedy 1GB per 3 minutes! Cartridges are independently rated for over 40,000 cycles of tape writes . The DLT has a storage capacity of 20 to 40GB per cartridge. The DLT is also priced at less than $4,500, which is in the same price range as the other two tape devices.

Parallel Processing Platform Database Creation Requirements

The Oracle installer utility for Oracle7 now avoids the previous limit of 32 maximum data files and other limitations encountered when creating databases from the Oracle version 6 installation utility.

The utility is accessed initially by copying it from the installation media into a temporary location. Do this only after you have read the installation guide specifically written for your hardware platform and operating system version release. About 200MB may be required for the temp directory until you remove it, after your installation.

After loading the system variables and specifying directory locations for product locations, give special attention to not accepting the default locations and tablespace sizes for the system, rollback, and control file locations.

These database files should be spread across several different physical disk drives for performance optimization. If the online help facilities of many products are installed, the sizing of the tablespaces must increase to hold this data. Specifically, the Oracle case tools and SQl*TextRetrieval will require at least a 25MB tablespace for their help examples when loaded.

New Datatypes That Enhance Parallel Processing Platforms

The Blob and VARCHAR2 column datatypes should be used accordingly .

Oracle's Media Server uses the Blob to store video and audio text for real-time playback, fast forward, and rewind directly from an MP, SMP or MPP server as delivered to between 30,000 to 150,000 concurrent users over cellular, twisted pair, co-ax, and fiber- optic cabling.

Blob, or Binary Large Object, may be up to a 4GB-long RAW field which should be used in a "striped" disk tablespace configuration. This will enable the maximum disk I/O performance.

Blob datatypes are used in the storage of large data objects, where searching and indexing would otherwise not be possible.

Oracle Callable Interfaces (OCI) can be required to read this datatype as a bitstream using Oracle's Pro*C tool, from the file server.

VARCHAR2 datatypes are a variable-length character field of up to 1,000 characters and are used to avoid wasted column space for missing data in the storage of your records. VARCHAR2 should be used wherever possible, unless math is or will be done (requiring data validation) with the data being stored in this particular column. A 2-byte end-of-file marker is the cost of this feature, while any space savings below this are saved and available for other data storage uses.

Previous Table of Contents Next