Choose one: High throughput or fast response time

Many people assume that the defaults for the Oracle parameters are just-right for their systems, not realizing the incompatible goals of optimizing for throughput vs. optimizing for fast response time.

The default in Oracle 11g for the optimizer_mode is all_rows, an optimizer mode that has the goal of maximizing total throughout by choosing SQL access plans that minimize server resources.  While this is a worthy goal, optimizing for maximum system throughput is not the same as optimizing for the fastest response time, a goal set with optimizer_mode=first_rows.

- Response time – This is a “selfish” goal; minimizing response time for individual queries, regardless of the expense to other tasks on the system.  Fast response time is often associated with index access.

- Throughput – This is a holistic system-wide optimizer goal that is concerned with optimizing your entire workload to maximize total throughout as a whole.  High throughput is often associated with parallelized large-table full-table scans.

To get the fastest response time we often need to use the first_rows optimizer_mode, a setting that directs the SQL optimizer to choose plans that start returning rows back to the calling application as quickly as possible.  Optimizing for the fastest response time often involves using indexes, an access technique that returns rows quickly but incurs more I/O operations.

The optimizer_mode is a “silver bullet” parameter, case where a single change will have a profound impact on the entire database. 

Follow the link to read more about optimizing for fast response time vs. optimizing for high system throughput:

http://www.dba-oracle.com/t_tuning_high_throughput_vs_fast_response_time.htm


*****************************************
Try Ion for Oracle database trends!

Using the principles of decision support systems technology, BC has developed an important new Oracle tuning tool, Ion for Oracle.  Ion quickly isolate the reports on the important optimization issues, separating the wheat from the chaff like no other performance tuning software.

After several years of hard work, Ion for Oracle is finally available for general release.  Ion is unlike any other Oracle tool on the market, a tool that encapsulates expert techniques to allow a DBA to quickly visualize important trends and signatures.

Predictive analytics if the key to repairing Oracle problems before they cripple your database, and I created Ion for Oracle to allow any DBA to quickly identify changing workloads and unobtrusive patterns of data access.  To try Ion for free, see here:

www.ion-dba.com

*****************************************

Need a health Check?

Oracle is the world's most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf