Performance Tuning  «Prev  Next»

Database Schema

You are the DBA
You are the DBA

Today you received an email from the operations manager at House-O-Pets, a Web-based vendor of pet products in San Francisco. House-O-Pets uses Oracle as a back-end database to manage its e-commerce site and keep track of inventory, customers, and customer purchases. The company is looking for some ways to get better performance from its application systems.

House-O-Pets email
House-O-Pets email

For confidential information ranging from company history to the database system requirements, review the FactFile that Mr. Schwartz provided to you at the close of your meeting.
The information in this folder is essential for solving problems throughout your tenure as DBA for House-O-Pets:

View Data table
Click the View Table link above to see the contents of the tables in the House-O-Pets database.
The course project will enable you to demonstrate your knowledge of how to repair or improve the database through a variety of exercises. You may be asked to analyze and report on existing code, submit pieces of code, or transfer what you have learned to a new situation. Some of the exercises will consist of evaluative simulations where you will be asked to problem solve within a simulated Oracle environment. Other exercises require an analysis of the information in the FactFile to solve the problem.

In the exercises, you will:
  1. Create an index-organized table to improve performance
  2. Create a materialized view to speed the performance of analysis queries without having to redesign any of the SQL forthe queries
  3. Create a descending index and a function-based index to improve the performance of some queries
  4. Learn about Advanced Queueing, which you could use to offload blocking processes like the credit check.
We will provide prompts for you to try exercises on your own if you do have access to Oracle .
We are about to play a major part in administering the House-O-Pets Oracle database.In the next module, you will get started on your way toward improving database performance, starting with optimizer and query improvements.