sql_Retrieve_Cluster_Queries

RETRIEVE ACTUAL SQL QUERIES FROM SPECIFIC CLUSTERS FOR PATTERN ANALYSIS

This tool extracts the actual SQL query text and performance metrics from selected clusters, enabling detailed pattern analysis and specific optimization recommendations. Essential for moving from cluster-level analysis to actual query optimization.

DETAILED ANALYSIS CAPABILITIES:

• SQL Pattern Recognition: Analyze actual query structures, joins, predicates, and functions

• Performance Correlation: Connect query patterns to specific performance characteristics

• Optimization Identification: Identify common anti-patterns, missing indexes, inefficient joins

• Code Quality Assessment: Evaluate query construction, complexity, and best practices

• Workload Understanding: See actual business logic and data access patterns

QUERY SELECTION STRATEGIES:

• By CPU Impact: Sort by 'ampcputime' to focus on highest CPU consumers

• By I/O Volume: Sort by 'logicalio' to find scan-intensive queries

• By Skew Problems: Sort by 'cpuskw' or 'ioskw' for distribution issues

• By Complexity: Sort by 'numsteps' for complex execution plans

• By Response Time: Sort by 'response_secs' for user experience impact

AVAILABLE METRICS FOR SORTING:

• ampcputime: Total CPU seconds (primary optimization target)

• logicalio: Total logical I/O operations (scan indicator)

• cpuskw: CPU skew ratio (distribution problems)

• ioskw: I/O skew ratio (hot spot indicators)

• pji: Physical-to-Logical I/O ratio (compute intensity)

• uii: Unit I/O Intensity (I/O efficiency)

• numsteps: Query execution plan steps (complexity)

• response_secs: Wall-clock execution time (user impact)

• delaytime: Time spent in queue (concurrency issues)

AUTOMATIC PERFORMANCE CATEGORIZATION: Each query is categorized using configurable thresholds (from sql_opt_config.yml):

• CPU Categories: VERY_HIGH_CPU (>config.very_high), HIGH_CPU (>config.high), MEDIUM_CPU (>10s), LOW_CPU

• CPU Skew: SEVERE_CPU_SKEW (>config.severe), HIGH_CPU_SKEW (>config.high), MODERATE_CPU_SKEW (>config.moderate), NORMAL

• I/O Skew: SEVERE_IO_SKEW (>config.severe), HIGH_IO_SKEW (>config.high), MODERATE_IO_SKEW (>config.moderate), NORMAL

Use thresholds set in config file for, CPU - high, very_high, Skew moderate, high, severe

TYPICAL OPTIMIZATION WORKFLOW:

1. Start with clusters identified from sql_Analyze_Cluster_Stats

2. Retrieve top queries by impact metric (usually 'ampcputime')

3. Analyze SQL patterns for common issues:

   • Missing WHERE clauses or inefficient predicates

   • Cartesian products or missing JOIN conditions

   • Inefficient GROUP BY or ORDER BY operations

   • Suboptimal table access patterns

   • Missing or outdated statistics

4. Develop specific optimization recommendations

QUERY LIMIT STRATEGY:

• Use the query limit set in config file for pattern recognition and analysis, unless user specifies a different limit

OUTPUT INCLUDES:

• Complete SQL query text for each query

• All performance metrics, user, application, and workload context, cluster membership and rankings

• Performance categories for quick filtering