Diversified Electronics is engaged in e-commerce and sells electronics products over the Web. Their product line ranges from cameras to camcorders to audio-visual equipment and accessories. The company maintains detailed technical information on hundreds of products from hundreds of manufacturers. Customers log on to the web site, pick items to buy, choose a method of delivery, and make payment by providing a credit card number in a secured environment. The database is the backbone of the web-based ordering system. At peak times, between 200 and 400 users access the database. The ordering system processes about 10,000 orders in an average month. The current size of the database is 20 GB.
The customer base of Diversified Electronics is growing at a rapid rate. Because of its rapid growth, the company can’t afford to thrash around and re-architect their existing database platform. Instead, Diversified Electronics wants to maintain its database application on a scalable environment that can grow to match the anticipated growth in load for the next few years.
Diversified Electronics’ database contains business-critical information. In turn, this information is used by several mission-critical applications. Two of the most important requirements for the database involve the following:
- High scalability
The e-commerce business of Diversified Electronics is growing rapidly, and a fourfold increase in sales volume is expected within a year’s time. This growth is being reflected in an increased database size, an increased number of hits at their web site, and an increased number of transactions. One of the primary goals of the information system is to support the business growth without sacrificing performance. The database and the hardware platform must be scalable so they can sustain the increased load associated with the expected business growth.
- Quick response time
The success of Diversified Electronics depends upon not only providing its customers the best prices in a secured environment, but also giving them a pleasant experience that includes a quick response time. Customers have various alternatives available, and if they don’t get a timely response to their queries and transactions, a competitor is just one mouseclick away. To enable a quick response time on the web site, the backend database must provide quick response for online transactions.
Diversified Electronics’ database is hosted on an SMP system. The specific hardware used is a multiprocessor Sun E4500 machine with 2 CPUs and 512 MB of RAM. This hardware is upgradable to a maximum 14 CPUs and a maximum of 14 GB of RAM. The operating system is Sun Solaris 2.6. The database software is Oracle Server Enterprise Edition 8.0.4.
The SMP platform provides good scalability for this application. Any increased load in terms of database size, number of users, or number of transactions can be supported simply by adding processors and memory to the system. As processors and memory are added, these new system resources will improve the inter-query and intra-query parallel capabilities of the system. For this application, the SMP architecture is good enough to meet the scalability requirements, and there’s no need for the additional complexity of clustered or MPP systems.
The applications run by Diversified Electronics use the following of Oracle’s parallel processing features:
- Parallel query
Customers frequently query the system to search for products based on category, price range, and features. To respond to these customer queries, the application executes queries against the database that involve table scans and joins. A customer may run several of these queries before deciding on a specific product to buy and expects quick response to these queries. Oracle’s parallel query feature effectively leverages the processing power of the system’s multiple CPUs, resulting in a satisfactory response time for the customer.
- Parallel DML
To support the business of Diversified Electronics, the ordering database integrates with other databases and applications in the organization. One such application is the pricing system. The pricing system resides on a separate platform and determines pricing for items based on market research, competitor’s prices, supply, demand, and the price and availability of competitive models. The ordering application runs nightly batch jobs to download data from the pricing system. The nightly batch jobs involve large amounts of insert and update activity on various tables in the ordering database and must finish within a specific time window. Several other batch jobs run on a regular basis to purge old transaction records, moving that data to an archival database. Oracle’s parallel DML feature is used to run these batch jobs efficiently.