BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










Parallel Queries and Distributed Tables

Oracle Tips by Burleson Consulting

There is an alternative to parallelism for Oracle distributed queries. In a distributed environment, Oracle parallel query can be simulated when using Net8 to perform simultaneous remote queries on each remote Net8 server. Interestingly, double parallelism can be achieved if the remote server invokes parallel full-table scans. While each query is executing simultaneously on each remote server, each query can be using Oracle parallel query.

These types of parallel queries are most useful in distributed databases where a single logical table has been partitioned into smaller tables at each remote node. This approach is very common in ERP applications where each remote Net8 server requires autonomy, while the corporate office requires a method to query each cloned table as if it were local. For example, a customer table that is ordered by customer name may be partitioned into a customer table at each remote database, such that we have a new_york_employee table, a california_employee table, and so on. This vertical table partitioning approach is very common with distributed databases where local autonomy of processing is important.

With the tables partitioned onto different databases at different geographical locations, how can we meet the needs of the corporate headquarters where a complete view is required? How can they query all of these remote tables as a single unit and treat the logical customer table as a single entity? For large queries that may span many logical tables, the isolated tables can then easily be reassembled to use Oracle's parallel query facility:

create view
   select *
   select *
   select *

We can now query the all_employee view as if it were a single database table, and Oracle will automatically recognize the union all clause and fire off simultaneous queries against each of the three base tables. It is important to note that the distributed database manager will direct that each query be processed at the remote location, while the query manager waits until each remote node has returned its result set. For example, the following query will assemble the requested data from the three tables in parallel, with each query being separately optimized. The result set from each subquery is then merged by the query manager process and delivered to the front-end application.

   salary > 500000;

In a query like this, a large distributed transaction will invoke parallel queries at each remote database. Note that these parallel queries will not necessarily perform full-table scans, but the remote data requests will be issued and managed by the Oracle Distributed Recovery Manager (RECO) process (Figure 10-4).

Figure 4: A distributed parallel query

Next, let’s wrap up this chapter and cover the major points to consider when evaluating and tuning full-table scans.


This chapter has been concerned with identifying full-table scans, evaluating the legitimacy of the full-table scan, tuning full-table scans for optimal performance, and monitoring full-table scan activity in your database. The main points of this chapter include these:

  • Oracle will often perform a full-table scan even if a faster way exists to access the table.

  • All full-table scans should be evaluated to see if the full-table scan can be replaced by index access. Always question the legitimacy of a full-table scan.

  • Large-table full-table scans will run far faster with Oracle parallel query.

  • Small-table full-table scans will run faster by placing the table blocks in the KEEP pool.

  • Turning on parallelism with the alter table command is dangerous because it can change the behavior of the execution plans for all queries that involve the table.

  • The degree of parallelism depends on the number of CPUs and the distribution of a table across disks.

  • You can monitor the parallel query slave behavior by using the v$pq_tqstat view. You should always make sure that the parallel query slaves are accessing an equivalent number of rows.

  • You can monitor the historical instance-wide behavior of queries by using STATSPACK. When a period of high parallel query activity is identified, you can go to the STATSPACK stats$sql_summary table and extract and evaluate the individual SQL statements.

  • Parallelism can be used to improve the speed of table joins, including nested loop joins, hash joins, and sort merge joins.

  • Legitimate sort merge joins should always use parallel query because a sort merge join requires full-table scans against both tables.

Next, let’s move on and take a look at optimizing sorting operations for Oracle SQL statements.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter