Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

Free Oracle Tips

HTML Text

 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle News
 Oracle Forum
 Class Catalog


 Our Staff
 Our Prices
 Help Wanted!

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security
 UNIX
 Oracle UNIX
 Linux
 Oracle Linux
 Monitoring
 Remote help
 Remote plans
 Remote services
 Oracle C++
 Oracle Java
 Apache
 JDeveloper
 App Server
 Applications
 Oracle Forms
 Oracle Portal
 11i Upgrades
 SQL Server
 Oracle Concepts
 HTML-DB Tips
 Software Help
 Remote Help  
 Development  
 Implementation


 Financials Training
 Oracle 11i
 Oracle Apps 11i
 Oracle Workflow
 Oracle AR 11i Class
 Oracle AP 11i class
 Oracle GL 11i class
 Oracle HR 11i class
 Oracle FA 11i class
 11i Project Mgt
 11i procurement
 11i collections


  Oracle Posters
 Oracle Books
 Oracle Tuning Book
 Oracle RAC Book
 Oracle Security
  Easy Oracle Books
  Oracle Scripts
 SQL Server DBA
 SQL Design Patterns
 Ion
 Excel-DB   


 BC Oracle News


 Rednecks!
 Dress code
 Arabian Stallion
 Burleson Arabians
 Guide Horses
 Don Burleson Blog
 Golf & Travel


 Privacy Policy
 

 
Oracle Answers
 

Optimizing database performance with parallel Oracle
Feb 15, 2001
Donald Burleson

 

In "Using parallel Oracle for high-speed e-commerce systems," I explained how Oracle's parallel query and Parallel Server features can help you build a better e-commerce system. Now it's time to turn our attention to some additional approaches for achieving that speedy e-commerce system.

Oracle parallel query is very important as Oracle database move onto SMP servers with 8, 16, 32 and 64 CPU processors.  On these servers, the speed of full-table scans and index fast-full scans can be greatly improved:

Invoking Parallelism

There are several ways to invoke Oracle parallel query, and some of them are very dangerous because they influence the costs assigned to full-table scans.

  • System-level parallelism - Setting the parallel_automatic_tuning parameter may cause the cost-based optimizer to perceive full-table scans as cheaper.
     
  • Session-level parallelism - Using the alter session force parallel query syntax.
     
  • SQL-level parallel query - This parallel hint is the preferred method for invoking parallel query, selectively, based on the operation:

     select /*+ parallel (c, 31) (e, 31) */ . . . .


Parallel DBA operations

With 24/7 e-commerce databases on the Web, the Oracle DBA is challenged to perform maintenance duties as fast as possible. Oracle has several tools that can implement the parallelization approach to improve manageability and speed up the processing performance of Oracle database administration.

There are several areas where the DBA can use parallel operations to speed Oracle maintenance.

  • Parallelize the Oracle backup
    The time required for backups can be both multiplexed (through Oracle's Enterprise Backup Utility [EBU] or Recovery Manager [RMAN]) and parallelized (with such third-party tools as Legato, ADSM, or Veritas) to speed the elapsed time for the backup. Creating a separate backup thread for each available tape drive will accomplish this. Hence, a backup that takes eight hours with one tape drive will only take 30 minutes with 16 tape drives.
     
  • Parallelize the index rebuilds
    Oracle indexes become out-of-balance and inefficient in highly active e-commerce databases. It is possible to submit many index rebuild tasks concurrently by performing parallel ALTER INDEX REBUILD commands. This can also be done within an OPS environment without excessive pinging, so long as the Web servers are disconnected from the database.
     
  • Parallelize the table reorganizations
    Oracle tables will fragment and become less efficient over time, and the DBA sometimes needs to reorganize the tables to improve performance. Before Oracle9i, if you are reorganizing the Oracle tables to re-sequence the rows, you can't use parallelism with the CREATE TABLE AS SELECT (CTAS) statement because you need to use an index hint to force the sequence of the rows. You can't use an index hint and a parallel hint in the same statement. However, you can submit concurrent CTAS tasks, one for each table, such that the elapsed time for the reorganization becomes the maximum time required for the largest table.

 

  
  
 

 Oracle cruise
 
 
 
Oracle performance tuning software
 
 

Oracle performance tuning book
 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

Parallelization promises to become even more popular with the increased use of Oracle parallel server (OPS). Oracle8i's new parallel IDML features are also expected to improve the parallel capabilities of database administration. These features are the PARALLEL CREATE TABLE AS SELECT (PCTAS) statement and parallel index rebuilding, which I'll describe briefly in the following sections.

PARALLEL CREATE TABLE AS SELECT (PCTAS)

The PARALLEL CREATE TABLE AS SELECT (PCTAS) statement can be useful in an Oracle environment where tables are reorganized by copying them to another tablespace. For example, you could specify the number of parallel processes to compute the monthly summary values from your fact table. In the following example, we assign five processes to simultaneously read the blocks from a fact table in a warehouse that was populated from operational data:

CREATE TABLE
   SALESPERSON_SUMMARY_03_00
PARALLEL (degree 5)
AS
SELECT
   region,
   salesperson,
   sum(sale_amount)
FROM
   FACT
WHERE
   month = 3
AND
   year = 2000
GROUP BY
   region,
   salesperson;

Here, we dedicate five query servers to extract the data from the fact table and five query servers to populate the new summary table.

Parallel index builds

Parallel index builds are often useful to the Oracle administrator who needs to rebuild indexes that have either spawned too many levels or that contain too many deleted leaf rows. Parallel index creation is also useful when importing large tables. Because so many tables are large, Oracle exports never capture the indexes and only export the row values. If a recovery of a table becomes necessary, the Oracle administrator can use a parallel CREATE to speed up the index re-creation. Parallel index creation takes place by allowing the degree of parallelism to be specified in the CREATE INDEX statement. For example:
ALTER INDEX customer_name_idx REBUILD PARALLEL 10;

Because this type of index creation always involves reading the old index structure and performing a large sort operation, Oracle is able to dedicate numerous independent processes to simultaneously read the base index and collect the keys for the new index structure.

Now, we have saved the best for last. The final approach—parallelized Oracle queries—can be used by all e-commerce databases to improve performance.

Parallelized Oracle queries

The parallelized query technique allows a single program to make multiple requests to the Oracle database in parallel. It is not a feature of the Oracle database, but a method that is very popular with programmers who need to perform many simultaneous queries against a database.

To illustrate how parallelized queries work, consider the following example. Assume that we have an Oracle database that contains electronic books. In addition to the book text, we also have another table that contains notes about the text.

At page retrieval time, we need the following information:

  • The page text
  • The illustrations and figures for the page
  • Any notes that were placed into the text

As we see in Figure A, instead of waiting for each query to complete independently for a total elapsed time of six seconds, each query is submitted to Oracle at the same instant for a total elapsed time of three seconds.

Figure A
Parallelized Oracle queries



While the C or Java program is waiting for the Oracle queries to complete, it is free to do other work. For example, since the page text is returned in only one second, the program can map out the text to the end user while waiting for the illustration and notes queries to complete.

Basic Web server multithreading of parallelized Oracle queries

Within the Web server code, multithreading can be implemented by using POSIX extensions that allow for multithreading within the same program address space. Internally to the C++ program, the multithreading is done with a function call, where multiple copies of the function may simultaneously exist within the address space of the C++ program.

To make concurrent database requests from C++ programs, some programmers choose to set a semaphore or use an interrupt to manage the simultaneous external requests. However, this approach requires that the programmer create a listener thread to determine when the simultaneous requests have completed.

A more popular solution to multiple simultaneous database requests is to use the POSIX library. POSIX offers the pthread_create function, which can be used to create a new thread within the program. POSIX also offers a pthread_join function that will signal the program when the external function has completed. Using these methods, the programmer can write Web server code that will submit and manage multiple simultaneous requests to Oracle.

Summary

The four components of Oracle parallelism discussed here and in the previous article can be used together to create an e-commerce database that runs very fast and can be quickly maintained. To recap, here are the basic features:

  • Oracle parallel query—Useful for Web data warehouses where full-table scans exist
  • Oracle Parallel Server—Useful when running SMP of massively parallel processors
  • Parallel DBA tools—Help minimize downtime for database maintenance
  • Manual parallelization—The most common way to speed Web-based queries, by breaking a single large query into many small queries and executing them in parallel

  		•

     

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.
 
 


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

 

Remote DBA Services


 

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle © is the registered trademark of Oracle Corporation.