Skip to content
Oracle DBA Scripts & Database Utilities

Queue Information (vqueue.sql)

What This Script Does

Section titled “What This Script Does”

This script provides Oracle queue monitoring information by:

  • Displaying all queue statistics from V$QUEUE
  • Showing dispatcher and shared server queue information
  • Monitoring queue wait times and throughput
  • Providing data for shared server configuration analysis

Script

Section titled “Script”
select * from v$queue
/

Usage

Section titled “Usage”
SQL> @vqueue.sql

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$QUEUE

Sample Output

Section titled “Sample Output”
PADDR    TYPE      QUEUED   WAIT     TOTALQ
-------- --------- ------- ------- ----------
12AB34CD COMMON          0       0        125
56EF78GH DISPATCHER      2      15        543
9ABC12DE DISPATCHER      1       8        321
DEF45678 DISPATCHER      0       0        234

Key Output Columns

Section titled “Key Output Columns”
  • PADDR: Process address of the queue owner
  • TYPE: Type of queue (COMMON, DISPATCHER)
  • QUEUED: Current number of items in queue
  • WAIT: Current wait time in centiseconds
  • TOTALQ: Total number of items queued since startup

Understanding Queue Types

Section titled “Understanding Queue Types”

COMMON Queue

Section titled “COMMON Queue”
  • Purpose: Common queue for all dispatchers
  • Usage: Shared among multiple dispatcher processes
  • Monitoring: Watch for high queue depths

DISPATCHER Queue

Section titled “DISPATCHER Queue”
  • Purpose: Individual dispatcher process queues
  • Usage: Specific to each dispatcher process
  • Monitoring: Look for uneven load distribution

Queue Analysis

Section titled “Queue Analysis”

Performance Indicators

Section titled “Performance Indicators”
  • QUEUED = 0: Good performance, no queuing
  • QUEUED > 0: Potential bottleneck or high load
  • WAIT > 0: Active queuing occurring
  • High TOTALQ: Heavy historical usage

Load Distribution

Section titled “Load Distribution”
  • Compare TOTALQ across dispatchers
  • Identify overloaded dispatchers
  • Check for even workload distribution

Common Use Cases

Section titled “Common Use Cases”

  1. Shared Server Monitoring

    • Monitor dispatcher queue performance
    • Identify bottlenecks in shared server configuration
    • Analyze load distribution

  2. Performance Troubleshooting

    • Investigate connection delays
    • Analyze shared server efficiency
    • Check for queue saturation

  3. Capacity Planning

    • Monitor queue utilization trends
    • Plan dispatcher configuration
    • Size shared server pools
Section titled “Related Views and Analysis”

Check Shared Server Configuration

Section titled “Check Shared Server Configuration”
-- Show shared server parameters
SELECT name, value
FROM v$parameter
WHERE name LIKE '%shared_server%'
   OR name LIKE '%dispatcher%'
   OR name = 'max_shared_servers';


-- Current shared server processes
SELECT * FROM v$shared_server;


-- Dispatcher information
SELECT * FROM v$dispatcher;

Queue Performance Analysis

Section titled “Queue Performance Analysis”
-- Average queue wait time
SELECT type, AVG(wait) avg_wait_cs, MAX(wait) max_wait_cs
FROM v$queue
GROUP BY type;


-- Queue utilization by dispatcher
SELECT d.name dispatcher_name, q.queued, q.wait, q.totalq
FROM v$dispatcher d, v$queue q
WHERE d.paddr = q.paddr;

Performance Tuning

Section titled “Performance Tuning”

High Queue Depths

Section titled “High Queue Depths”

If QUEUED values are consistently > 0:

  1. Increase Dispatchers

    ALTER SYSTEM SET dispatchers='(PROTOCOL=TCP)(DISPATCHERS=4)';

  2. Increase Shared Servers

    ALTER SYSTEM SET shared_servers=10;

  3. Check Network Capacity

    • Verify network bandwidth
    • Check for network bottlenecks
    • Monitor connection patterns

Uneven Load Distribution

Section titled “Uneven Load Distribution”

If some dispatchers show much higher TOTALQ:

  1. Review Connection Load Balancing

    • Check TNS configuration
    • Verify client connection patterns
    • Consider connection pooling

  2. Dispatcher Redistribution

    • Restart dispatchers to redistribute load
    • Monitor over time for patterns

Monitoring Scripts

Section titled “Monitoring Scripts”

Continuous Queue Monitoring

Section titled “Continuous Queue Monitoring”
-- Monitor queue activity over time
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') time,
       type, queued, wait, totalq
FROM v$queue
WHERE queued > 0 OR wait > 0;

Queue History Tracking

Section titled “Queue History Tracking”
-- Create monitoring table
CREATE TABLE queue_history (
    check_time DATE,
    queue_type VARCHAR2(20),
    queued NUMBER,
    wait_time NUMBER,
    total_queued NUMBER
);


-- Insert current values
INSERT INTO queue_history
SELECT SYSDATE, type, queued, wait, totalq
FROM v$queue;

Troubleshooting

Section titled “Troubleshooting”

No Queue Data

Section titled “No Queue Data”
  • Shared server not configured
  • All connections using dedicated servers
  • No dispatcher processes running

High Wait Times

Section titled “High Wait Times”
  • Insufficient shared server processes
  • Network bottlenecks
  • Heavy application load

Uneven Queue Distribution

Section titled “Uneven Queue Distribution”
  • Client connection patterns
  • Network routing issues
  • Dispatcher configuration problems

Shared Server Benefits and Considerations

Section titled “Shared Server Benefits and Considerations”

Benefits

Section titled “Benefits”
  • Memory Efficiency: Reduced memory per connection
  • Scalability: Support more concurrent users
  • Resource Sharing: Better resource utilization

Considerations

Section titled “Considerations”
  • Performance: Slight overhead for context switching
  • Complexity: More complex troubleshooting
  • Application Compatibility: Some applications require dedicated servers

Best Practices

Section titled “Best Practices”

  1. Regular Monitoring

    • Check queue depths during peak hours
    • Monitor wait times for user experience
    • Track total queue counts for trends

  2. Configuration Management

    • Size dispatchers based on connection patterns
    • Configure adequate shared servers
    • Balance between memory and performance

  3. Troubleshooting

    • Correlate queue activity with user complaints
    • Monitor during different workload periods
    • Keep baseline measurements
Section titled “Related Scripts”