HP NonStop SQL/MX query analysis and tuning

Prerequisites

• HP NonStop SQL/MX basics (U4184S)
• HP NonStop SQL/MX database for SQL/MP users (U4185S)

Audience

• Anyone responsible for SQL/MX query analysis, tuning, and programming

Ways to save

Course Objectives

• Understand and use tools to analyze a variety of SQL/MX query plans
• Learn the methods to influence or modify SQL/MX query plans with Control Query Default and Control Query Shape statements
• Understand some of the opportunities for tuning SQL/MX queries in the following areas: query design, application design, and database design
• Become familiar with SQL/MX query plan fundamentals, such as query compilation phases, query tree operators functions, data flow architecture, process boundaries, and parallel execution of a query plan

Benefits to you

• Analyzing a variety of SQL/MX query plans: the operators, data flow, and parallelism
• Utilizing query analysis tools: Visual Query Planner, Explain Function, and Display_explain statement
• Modifying query plans using Control Query Defaults and Control Query Shape

Course outline

• Module 1: SQL/MX Query Fundamentals
• NonStop SQL/MX process architecture
• SQL/MX compilation phases
• Branch and bound optimization
• Query tree data flow model
• SQL/MX executor and its data flow model
• Forms of parallel execution used in SQL/MX query plans
• Module 2: Viewing Database Object Definitions
• SQL/MX database object characteristics
• Primary and clustering keys
• Range and hash partitioning
• Referential Integrity (RI)
• Triggers
• Utilities to display database definitions
• Lab: viewing database object definitions
• Module 3: Viewing SQL/MX Query Plans
• Visual Query Planner
• Explain function and Explain command
• Lab: viewing SQL/MX query plans
• Module 4: File Scan and Exchange Operators
• Basic operators that form a query plan: root operator, file scan operators, and exchange operators
• Statement atomicity
• Exchange operators and process boundaries
• Lab: analyzing basic query plans
• Module 5: Index Scan Operators and MDAM
• Index scan operators and their tokens
• Alternate index access path
• OR optimization
• MultiDimensional Access Method (MDAM)
• Forcing an access path to a table or index
• Lab: analyzing queries that use index scan operators and MDAM
• Module 6: SQL/MX Delete Operators
• Delete operators and their tokens
• Query plans that use delete operators to delete rows in a table with and without indexes
• Lab: analyzing delete queries
• Module 7: Update Operators
• Update operators and their tokens
• Query plans that use update operators to update rows in a table with and without indexes
• Lab: analyzing update queries
• Module 8 - Insert Operators
• Insert operators and their tokens
• Query plans that use insert operators to insert rows in a table with and without indexes
• Lab: analyzing insert queries
• Module 9: Non-parallel joins
• Join operators (nested join, merge join, ordered hash join, and hash join) and their tokens
• Query plans that use join operators
• System defaults for controlling joins
• Control Query Shape statement for joins
• Lab: analyzing nonparallel join queries
• Module 10: Parallel joins
• Type 1 parallel joins; matching partitions, logical partition grouping, logical sub partitioning, range repartitioning, and hash repartitioning
• Type 2 parallel joins: replicate no broadcast and replicate via broadcast
• System defaults for controlling ESP parallelism
• Lab: Analyzing parallel join queries
• Module 11: Subquery Operators
• Semi join and anti semi join operators and their tokens
• Query plans that use the semi join and anti semi join operators
• Lab: analyzing subqueries
• Module 12: Sorting and Grouping Operators
• Sort, sort groupby, hash groupby operators and their tokens
• Sort partial aggr, sort scalar aggr, and shortcut scalar aggr operators and their tokens
• Query plans that use the sort and groupby operators
• Lab: analyzing queries that use sorting and grouping operators
• Module 13: Rowsets and Compound Statements
• Rowset operators and tokens
• Compound statements
• Query plans that use input and output rowset arrays and compound statements
• Labs: rowsets operators and compound statements
• Module 14: Referential Integrity and Triggers
• Referential Integrity (RI)
• Triggers
• Insert, update, and delete queries on tables that have referential integrity constraints and triggers
• Labs: referential integrity and triggers
• Module 15: Forcing Query Plans
• Analyzing query plans
• Changing and modifying query plans
• Forcing query plans
• Lab: forcing query plans
• Module 16: Tuning
• Properties and costs
• TP queries and OLT optimizations
• Basic query, application, and database design for better performance
• Onsite-Delivery Equipment Requirements
• HP NonStop S72000 server or later that supports 1 GB of memory and IEEE floating point
• H06.22 or later operating system with SQL/MX 3.0+ and DDL licensing product T0394
• Student desktop software:
  • Access to NonStop lab server using a terminal emulator such as MrWin6530 or OutsideView
  • Visual Query Planner
  • NonStop ODBC MX 3.0+ Administrator