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

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


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


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

Optimizing performance with Oracle parallel query

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 - Up to 11g release 1, setting the parallel automatic tuning parameter (In 11g release 2 and beyond, the parallel_degree_policy parameter)  may cause the cost-based optimizer to perceive full-table scans as cheaper.  System-level parallelism is best for data warehouse and DSS systems and should be avoided for OLTP systems.
     
  • Session-level parallelism - Using the alter session force parallel query syntax.
  • Object level parallelism - You can say "alter table customer parallel degree 15", but beware, this influences the SQL optimizer into thinking that full-scans are "cheaper" then index access. I recommend invoking OPQ with hints, on a statement-by-statement basis.
     
  • 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.
  • 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 below, 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.

    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 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

 

 
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

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 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.