Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Designing a High Performance Database
Oracle Tips by Burleson Consulting

Oracle 11g Grid & Real Application Clusters by Rampant TechPress is written by four of the top Oracle database experts (Steve Karam, Bryan Jones, Mike Ault and Madhu Tumma).  The following is an excerpt from the book.

The next section is an in-depth look at several of the details a database architect may need to consider when designing for high performance.  Some of the details are more relevant when the number of users is greater than one thousand.  However, it is good for any database architect to understand the scores of variables that may affect performance on larger-sized deployments. 

 

As an example to visualize how each software and hardware component needs to be able to continue to function as the number of transactions per minute (TPM) increases, the major components of one of the fastest airplanes in the world, the SR-71 will be briefly analyzed.

 

The major structural parts of an SR-71 (or any fixed-wing plane) consist of:

  • Wing ribs (forms the skeletal shape of the wing)

  • Spars (main structural part of the wing, defines length of the wing)

  • Frame (forms the skeletal shape of the plane body)

  • Longerons (main structural part of the fuselage, front to rear)

  • Skin

As an SR-71’s speed doubles from 100 mph to 200 mph and finally reaches its top speed of Mach 3.2, the strength of the wing ribs, spars, frame, longerons, and skin all need to scale to handle the structural demands that the powerful Pratt Whitney engines require.  In a similar way, as a database’s TPM (transactions per minute) doubles, the CPUs, memory, disks, application and such all need to scale in unison.  If one component becomes the bottleneck, the huge capacity of the other components is irrelevant until the bottleneck is removed.  Hopefully, the bottleneck will be addressed before it crashes!

 

Becoming a subject-matter expert of database performance design takes time and real world experience.  It is also an iterative process.  There is no magic in any database vendor’s source code.  The cost-based optimizers in today’s modern RDBMSs (Relational Database Management Systems) all depend on statistics to make decisions.  Many times, however, the best solution to a performance problem is found outside of the database and in the Application design.  Application design plays a major role in determining user response times.  All the unscalable application design mistakes that have been seen over the years cannot be listed here, but here are a few things to keep in mind.

Unscalable Design Mistakes

These unscalable design mistakes should be avoided.

  • Unused indexes

  • Over-normalization

  • Unnecessary 15-way table joins

  • Unnecessary frequent database connects and disconnects

  • Unnecessary queries against gigantic tables

  • Poor server I/O optimization 

  •  Bad disk I/O configuration

  • Insufficient RAM

  • SGA too small

  • SGA way too big

  • Not using bind variables

  • Developers coding the same SQL using different capitalization

  • Not understanding performance related init parameters

  • Coding features into the application that the database can do better and faster

  • Complex views to reference other complex views

  • Lack of change management procedures

  • Repeatedly reading static data when data could be cached

  • Memory leaks

  • Committing too often / Poor transaction management

  • DBA’s failure to monitor their database

  • Failing to distribute load across multiple RAC instances

  • Failing to segregate load types (DS vs. OLTP)

  • Failing to monitor for heavy interconnect traffic

Hardware Planning

Knowing the hardware details of similar large and scalable installations, e.g. from past experience, is one of the best ways to ensure the new architecture will also scale.  Designing without a similar system to use as a point of reference could mean that one is designing in the dark.  It would be wise to open up a dialog with a capacity-planning expert that works for one’s hardware vendor.  Dell, HP, IBM, SUN and others all have capacity planning experts.  Besides, Oracle recommends that all RAC implementers notify their hardware vendors that the purchase is intended for a RAC system.  Be sure to listen to the technical expertise of the vendor engineers, not the salesman. 

 

Of course, online resources can be very helpful.  One of the best Oracle white papers involving RAC and I/O is Building a Multi-Terabyte Data Warehouse Using Linux and RAC.  This white paper is a must read. 

 

Once the hardware is up and running, be sure to take advantage of the new Oracle 11g CALIBRATE_IO procedure.  This procedure will help determine if the I/O levels are as high as is expected.

 

Below is a list of hardware components that may need to be considered when designing for performance.   The bullet points are not a comprehensive list, but are a starting point for making hardware purchase decisions. 

CPUs

  • Architecture

  • Speed

  • L1, L2, L3 cache

  • Number of cores

  • Supported operating systems

Memory

  • Size

  • Speed

  • Type

  • Mirroring

  • RAID

  • ECC

  • Number of slots on motherboard

  • Motherboard maximum

  • Note swap file size is determined by memory size

System Bus

  • Speed

PCI

  • PCI-X

  • PCI-Express

  • PCI-E x4

  • PCI-E x8

  • PCI-E X16

Disks

  • Size

  • Speed

  • Type

  • Consider sum of I/O of all instances

  • Can the disk architecture easily scale

- Disk testing tools

- dd

- ORION

- IOZone

Raid Configuration

  • Oracle recommends not using RAID -5 for redo logs

  • Separate archive logs from redo logs

  • See Metalink NOTE: 45635.1

Network Adapters

  • Speed

  • Latency

  • NIC bonding

Host Bus Adapters

  • Speed

  • Path Redundancy

Fibre Switches

  • Speed

  • Zoning

  • Number of ports

Storage Area Network

  • Cache size

- SAN cache is good, but salespeople typically exaggerate the benefits 

- SAN cache cannot mask all disk I/O problems

  • Number of disks

  • Speed

  • Many different business continuity features

SCSI Controllers

  • Cache size

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.