Parallel Degree Analysis (parallel.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script analyzes parallel degree settings for tables and indexes within specified schemas, identifying objects configured for parallel processing. It shows which database objects are set up to use Oracle’s parallel execution capabilities, helping with performance optimization, resource planning, and parallel processing strategy assessment. The script is essential for understanding and managing parallel execution configurations across database schemas.

The Script

rem  parallel.sql
rem
SET PAUSE OFF;
SET LINES 500;
ttitle 'Object level parallel report'
rem
COL degree FORMAT A17 HEADING 'PARALLEL|DEGREE'
col table_name format a26  heading 'TABLE|NAME'
col INDEX_name format a26  heading 'INDEX|NAME'
col INDEX_TYPE format a10  heading 'INDEX|TYPE'
rem
select degree, table_name from dba_tables
where owner like nvl(upper('&owner'), '%')
 AND DEGREE <> 1 ORDER BY DEGREE DESC;

select degree, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME
 from dba_INDEXES
where owner like nvl(upper('&owner'), '%')
 AND DEGREE <> 1 ORDER BY DEGREE DESC;

SET PAUSE ON;

Key Features

• Table Parallel Analysis: Shows tables configured with parallel degree > 1
• Index Parallel Analysis: Displays indexes with parallel processing enabled
• Schema Filtering: Supports filtering by owner or analyzing all schemas
• Degree Ranking: Orders results by parallel degree (highest first)
• Comprehensive Coverage: Includes both tables and indexes in analysis
• Configuration Assessment: Enables review of parallel processing setup

Usage

@parallel.sql

Input Parameters:

• owner: Schema owner name or pattern (use % for all schemas)

Required Privileges

SELECT on DBA_TABLES
SELECT on DBA_INDEXES

Sample Output

                                    Object level parallel report

PARALLEL          TABLE
DEGREE            NAME
----------------- --------------------------
DEFAULT           SALES_FACT_LARGE
8                 CUSTOMER_HISTORY
4                 ORDER_DETAILS_ARCHIVE
2                 PRODUCT_CATALOG

PARALLEL          INDEX                      INDEX      TABLE_OWNER          TABLE
DEGREE            NAME                       TYPE                            NAME
----------------- -------------------------- ---------- -------------------- --------------------------
DEFAULT           SALES_FACT_PK              NORMAL     SALES               SALES_FACT_LARGE
8                 CUST_HIST_DATE_IDX         NORMAL     SALES               CUSTOMER_HISTORY
4                 ORDER_DTL_PROD_IDX         NORMAL     SALES               ORDER_DETAILS_ARCHIVE
2                 PROD_CAT_NAME_IDX          NORMAL     PRODUCT             PRODUCT_CATALOG

Key Output Columns

Tables Section

• PARALLEL DEGREE: Parallel degree setting for the table
• TABLE NAME: Name of the table

Indexes Section

• PARALLEL DEGREE: Parallel degree setting for the index
• INDEX NAME: Name of the index
• INDEX TYPE: Type of index (NORMAL, BITMAP, etc.)
• TABLE_OWNER: Owner of the table the index belongs to
• TABLE NAME: Name of the table the index is on

Understanding Parallel Degree Settings

Parallel Degree Values

Degree Interpretations

-- Parallel degree meanings:
-- 1: No parallel processing (serial execution)
-- 2, 4, 8, etc.: Fixed parallel degree
-- DEFAULT: Uses system default parallel degree
-- Degree > 1: Enables parallel processing

Automatic vs. Manual Settings

-- Degree setting types:
-- Explicit numbers: Manually configured
-- DEFAULT: Automatic degree determination
-- System calculated: Based on CPU count and parameters
-- Workload dependent: May vary by operation type

Performance Implications

Parallel Processing Benefits

-- Parallel execution advantages:
-- Faster processing for large operations
-- Better resource utilization
-- Improved throughput for bulk operations
-- Reduced elapsed time for complex queries

Resource Considerations

-- Resource impact factors:
-- CPU utilization increase
-- Memory consumption (PGA)
-- I/O subsystem load
-- Potential contention with other processes

Common Use Cases

1. Performance Optimization Review

   • Identify objects using parallel processing
   • Assess parallel configuration appropriateness
   • Support performance tuning efforts
   • Validate parallel processing strategy

2. Resource Planning

   • Understand parallel resource requirements
   • Plan system capacity for parallel workloads
   • Assess concurrent parallel operation impact
   • Support infrastructure sizing decisions

3. Configuration Management

   • Standardize parallel degree settings
   • Document parallel processing configuration
   • Plan parallel degree modifications
   • Support environment consistency

4. Troubleshooting

   • Investigate parallel execution issues
   • Identify over-parallelized objects
   • Support performance problem resolution
   • Validate parallel configuration changes

Advanced Analysis

Parallel Configuration Assessment

Optimal Degree Analysis

-- Assess degree appropriateness:
-- Consider object size and access patterns
-- Evaluate system resources (CPU, memory)
-- Analyze concurrent workload impact
-- Review performance improvement vs. cost

Workload Correlation

-- Correlate with workload patterns:
-- OLTP vs. data warehouse workloads
-- Batch processing requirements
-- Interactive query performance
-- Mixed workload considerations

Resource Impact Analysis

System Resource Assessment

-- Evaluate resource implications:
-- Total parallel degree across all objects
-- Peak concurrent parallel operations
-- Memory requirements (PGA_AGGREGATE_TARGET)
-- CPU core utilization patterns

Contention Analysis

-- Identify potential contention:
-- Over-subscription of CPU resources
-- Memory pressure from parallel operations
-- I/O subsystem saturation
-- Inter-process coordination overhead

Optimization Strategies

Parallel Degree Optimization

Right-Sizing Parallel Degrees

-- Optimization guidelines:
-- Large tables/indexes: Higher degrees (4-8)
-- Medium objects: Moderate degrees (2-4)
-- Small objects: Serial execution (degree 1)
-- Consider system capacity and concurrent load

Workload-Specific Tuning

-- Workload optimization:
-- OLTP: Minimal parallel usage
-- Data warehouse: Aggressive parallel settings
-- ETL processes: High parallel degrees
-- Reporting: Moderate parallel usage

System-Level Configuration

Parameter Tuning

-- Related parameters to optimize:
-- PARALLEL_MAX_SERVERS
-- PARALLEL_ADAPTIVE_MULTI_USER
-- PGA_AGGREGATE_TARGET
-- PARALLEL_DEGREE_POLICY

Resource Allocation

-- Resource optimization:
-- CPU core allocation
-- Memory distribution
-- I/O bandwidth allocation
-- Process pool sizing

Filtering Examples

Specific Schema Analysis

-- Analyze specific schema:
Enter value for owner: SALES

All Schemas Review

-- Review all schemas:
Enter value for owner: %

Pattern-Based Analysis

-- Analyze schema patterns:
Enter value for owner: %DW%
Enter value for owner: PROD_%

Integration with Performance Monitoring

Parallel Execution Monitoring

Active Parallel Sessions

-- Monitor active parallel execution:
SELECT degree, count(*) sessions
FROM v$px_session
GROUP BY degree
ORDER BY degree DESC;

Parallel Execution Statistics

-- Analyze parallel execution performance:
SELECT name, value
FROM v$sysstat
WHERE name LIKE '%parallel%'
ORDER BY name;

Resource Utilization Tracking

Memory Usage Analysis

-- Monitor PGA usage for parallel operations:
SELECT name, value/1024/1024 mb
FROM v$pgastat
WHERE name LIKE '%parallel%'
ORDER BY name;

Best Practices

Configuration Guidelines

Degree Setting Strategy

-- Best practice guidelines:
-- Start with conservative degrees
-- Test and validate performance improvements
-- Consider system capacity and concurrent load
-- Monitor resource utilization impact

Object-Specific Configuration

-- Object type considerations:
-- Fact tables: Higher degrees appropriate
-- Dimension tables: Lower degrees typically
-- Large indexes: Parallel beneficial
-- Small objects: Avoid parallel overhead

Monitoring and Maintenance

Regular Assessment

-- Assessment schedule:
-- Monthly parallel configuration review
-- Quarterly performance impact analysis
-- Semi-annual optimization assessment
-- Change impact evaluation

Performance Validation

-- Validation process:
-- Measure performance before/after changes
-- Monitor resource utilization impact
-- Assess concurrent workload effects
-- Document optimization results

Troubleshooting Applications

Performance Issues

Over-Parallelization Problems

-- Signs of over-parallelization:
-- Increased resource contention
-- Longer elapsed times than serial
-- High context switching overhead
-- Memory pressure from parallel processes

Under-Utilization Issues

-- Signs of under-utilization:
-- Poor performance on large operations
-- Unused CPU and I/O capacity
-- Long-running batch processes
-- Inefficient resource usage

Configuration Problems

Inappropriate Degree Settings

-- Common configuration issues:
-- Small objects with high degrees
-- Large objects without parallel settings
-- Inconsistent degree settings across related objects
-- Failure to consider workload patterns

Resource Conflicts

-- Resource conflict indicators:
-- Parallel server pool exhaustion
-- Memory allocation failures
-- CPU saturation during parallel operations
-- I/O bottlenecks from parallel processing

Related Scripts

• vprocess.sql - Process analysis including parallel workers
• dtable.sql - Comprehensive table analysis
• dindex.sql - Index analysis
• gvpga.sql - PGA memory analysis

Enhanced Analysis

Comprehensive Parallel Review

Extended Object Analysis

-- Include additional object information:
SELECT t.owner, t.table_name, t.degree,
       ROUND(s.num_rows/1000000,1) as million_rows,
       ROUND(s.blocks*8/1024,1) as size_mb
FROM dba_tables t, dba_tab_statistics s
WHERE t.owner = s.owner
  AND t.table_name = s.table_name
  AND t.degree != '1'
  AND t.owner LIKE nvl(upper('&owner'), '%')
ORDER BY t.degree DESC, s.num_rows DESC;

Parallel Configuration Summary

-- Summarize parallel configuration:
SELECT degree,
       COUNT(*) as object_count,
       SUM(CASE WHEN object_type = 'TABLE' THEN 1 ELSE 0 END) as tables,
       SUM(CASE WHEN object_type = 'INDEX' THEN 1 ELSE 0 END) as indexes
FROM (
  SELECT degree, 'TABLE' as object_type FROM dba_tables WHERE degree != '1'
  UNION ALL
  SELECT degree, 'INDEX' as object_type FROM dba_indexes WHERE degree != '1'
)
GROUP BY degree
ORDER BY degree DESC;

Summary

This script is essential for:

1. Parallel Configuration Analysis - Understanding current parallel processing configuration across database objects
2. Performance Optimization - Supporting parallel execution strategy development and optimization
3. Resource Planning - Planning system resources for parallel workloads and capacity requirements
4. Configuration Management - Managing and standardizing parallel degree settings across environments
5. Troubleshooting - Diagnosing parallel execution issues and performance problems