Monitoring Oracle Parallel Queries!!

Page: 1 2

Oracle Parallel Query enables us to break-up a single SQL statement so that its parts can run simultaneously on different processors in a multi-processor machine. Parallel Query can drastically improve performance of certain types of operations. Typical operations that can run in parallel include full table scans, sorts, sub-queries, data loading etc. Oracle Parallel Query is commonly used in Decision Support Systems and Data Warehousing applications. In this article I will discuss some great tips about monitoring your parallel jobs.

Large Parallel Batch Jobs:

Almost all database administrators have to perform large parallel batch jobs almost every weekend. They have to sit and keep watching until normal jobs such as table reorganizations and cloning and refreshing test database run for hours.  

High-Availability of Data:

Database administrators have sole responsibility of keeping the validity and integrity of data and maximizing high-availability by using every spare resource to manage the I/O sub-system. 

Optimization:

Oracle parallel query is already highly optimized, and OPQ will divide a table into well-sized chunks to allow the query to complete in an optimal fashion.  However, the trend towards super-large disks can defeat the benefits of OPQ on SMP servers.

Degree of Parallelism :

Changing the degree of parallelism is helpful but as the data may span many physical disk platters so it can make the parallelism confused. Over-estimating the degree of parallelism can result in hot spots as some disks will enqueue I/O requests while other spindles spin idle.

Histograms:

You can tune Oracle parallel queries by changing the histograms. This is especially helpful when data skew is an issue. 

Oracle Dynamic Sampling:

You can use Oracle dynamic sampling for ensuring optimal execution plans for large parallel job with highly-variability in data skew.

Real-time monitoring of Parallel Jobs:

Monitoring the parallel session in real-time using a v$pq_tqstat script is not helpful in making the executing job run any faster because there are few real-time tuning options. UNIX/Linux nice command can be used to increase the dispatching priority of the parallel query slave processes by giving them LIFO access to the queuing of most CPU dispatchers. This can be beneficial for databases that have a CPU bottleneck where r column in vmstat is higher than the number of processors on the server. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle10g Wait Event Tuning
   Effective Tuning of Oracle 11g Fully Automated SQL!
   Beware of Worse Oracle performance after Migration!!
   Beware of over-creating Indexes!!