Section 9.6. Exercises | Optimizing Oracle Performance

9.6 Exercises

Use the M/M/ m queueing model to verify all of the results of our worked example.
The designers of the new international terminal at the Millsap International Airport (MQP) in Millsap, Texas have solicited your help in optimizing customer response time. Each new state-of-the-art ticket counter will host six ticket agents. The airport designers need your help determining whether they should set up one long snaking queue to feed customers to all six ticket agents (an M/M/6 configuration), or six shorter straight queues that each feeds one agent (a configuration of six independent M/M/1 systems). Use the M/M/ m queueing model to determine which configuration would provide faster customer response times.
A customer has a choice between purchasing two different types of computers. Model H has one CPU that's capable of executing 40,000 Oracle logical reads (LIOs) per second. Model L has four CPUs arranged in a symmetric multiprocessing (SMP) organization, but each CPU is capable of executing only 15,000 Oracle LIOs per second. Which computer should the customer buy?
Write a program to retrieve interarrival times from your batch queue manager. Are the numbers exponentially distributed? Can you find exponentially distributed subsets of your data by restricting the programs for which you collect interarrival times? Can you find exponentially distributed subsets of your data by restricting the times of the day for which you collect interarrival times?
Does your batch queue manager allow you to measure true service times for your jobs? For example, for a given job, can you determine how much CPU time the job has consumed? If it is possible, write a program to retrieve service times from your batch queue manager. Are the numbers exponentially distributed? Can you find subsets of the data that are exponentially distributed? If your batch queue manager does not reveal true service times, how might you go about collecting them?

An investments brokerage firm has purchased an Oracle Server license and plans to design and build a custom application to manipulate investment trade transactions. No code has yet been written, but the business requirements dictate that an end- user receive a query result or transaction confirmation within three seconds of pressing a key to execute the action. The firm expects to process 30,000 transactions per continental USA business day, with a peak processing rate of 650 transactions in a five-minute period. A business day in the continental USA spans from 8:00 a.m. Eastern time through 5:00 p.m. Pacific time (8:00 a.m. Pacific time is 11:00 a.m. Eastern time). There are an estimated 2,000 brokers who will have online access to the new system.

All users are connected to the database server via a complicated system involving LAN links, terminal servers, and a transaction processing monitor. After deducting time for client-side presentation management, network response time, and TP-monitor processing, the system designers estimate that out of the three-second response time tolerance, the time left over for Oracle Server operations for each transaction is about a second and a half, as shown in Table 9-7.

Table 9-7. Response time requirement for an investments brokerage firm

Max. allowable response time (sec)	Execution phase
1.500	Oracle Server response time
	Parse, bind, execute, fetch, etc.
1.500	Total non-Oracle response time
	Client-side presentation management
	Network response time
	Transaction processing monitor
3.000	Total response time

The client needs advice about hardware and application architecture. Specify a hardware architecture and a corresponding transaction performance target that will guide the design and development of application SQL that will meet this brokerage firm's performance requirements.
Use M/M/ m to model response times of an important business function in your system. Manipulate the model parameters until the model reliably forecasts measurable present-day performance behavior. For example, if you have a 10-CPU system with over 100 distinct business functions, experiment with the model as if you had only a part of one CPU dedicated to a single specific business function. What happens when you double the arrival rate of that function? What if you could improve the service rate for that function by a factor of 10%? What would happen to the performance of that function if you could double the amount of CPU capacity you could dedicate to it? Aside from hardware upgrades, what are some ways that you can increase the amount of CPU that could be dedicated to a given business function?

Top