DB 3

Slide1: 

POP/FED: Progressive Query Optimization for Federated Queries in DB2 Wook-Shin Han, Volker Markl, Stephan Ewen Vijayshankar Raman, Holger Kache Goal: Add Robustness to Complex Federated Queries Environment: Complex federated SQL queries, e.g. in decision support applications Problem: Sub-optimal query performance due to erroneous cardinality estimates used during federated query compilation/optimization Results: Queries much more robust. Improvement in execution time by orders of magnitude. Solution: Monitor federated plan during runtime and trigger reoptimization, reusing intermediate results. SELECT YEAR AS YEAR, SUM(CASE WHEN NATION = 'BRAZIL' THEN VOLUME ELSE 0 END) / SUM(VOLUME) AS MKT_SHARE FROM (SELECT YEAR(O_ORDERDATE) AS YEAR, L_EXTENDEDPRICE*(1-L_DISCOUNT) AS VOLUME, N2.N_NAME AS NATION FROM ORA.SUPPLIER, ORA.CUSTOMER, ORA.NATION N1, ORA.NATION N2, ORA.REGION, DB2.LINEITEM, DB2.ORDERS, WHERE S_SUPPKEY = L_SUPPKEY AND L_ORDERKEY = O_ORDERKEY AND O_CUSTKEY = C_CUSTKEY AND C_NATIONKEY = N1.N_NATIONKEY AND N1.N_REGIONKEY = R_REGIONKEY AND R_NAME = 'AMERICA' AND S_NATIONKEY = N2.N_NATIONKEY AND O_ORDERDATE BETWEEN DATE('1995-01-01') AND DATE('1996-12-31') ) AS ALL_NATIONS GROUP BY YEAR ORDER BY YEAR; Example Query Without POP Total execution time: 57 sec. Plan with POP CHECK: Operator 3 RANGE [47287, 1000000]: CARDINALITY: 17916 OUT OF BOUNDS! Reoptimizing! Reoptimize 3x Total execution time: 107 sec. Query Checked & Rematchable Code Parser Semantics Rewrite Code Generator 5) Add Matching Information 4) Generate CHECK Code Thread: 28F: CHK (0010) jf=2A0:0 op=13 lb=a ub=b 2A0: TA (001F) jf=2C0:0 jb=0:0 evlo=0:0 taob=158:0 2C0: JNF (0008) jf=2D8:0 jump=308:0 Optimizer 3) Place CHECKs 2) Place dams for eager materialization 1) Compute Validity Ranges Checked Plan [lb..ub] [lb..ub] Check [lb..ub] Temp Code with CHECK Matchable TAOBs Plans with judicious materialization Plans with optimality criterion [lb2..ub2] [lb1..ub1] [lb3..ub3] Cheaper More Expensive Query Compiler Runtime 8) Save Intermediate Results Section Closure Query Result Relational Data Services Compiler to Runtime Transition Section Execution 6) Drop Redundant Interm. results Subsumes Cardinality ??? 7) Execute Checks Intermediate Results / Additional knowledge Interm. Result Interm. Result Interm. Result RDS Monitor Counters Err a++ b++ c++ d++ e++ f++ lb < actual card < ub ? Reoptimize ! N OK Y 9) Match Previus Results VMV Interm. Res. VMV Legend Est: 182K VR: [47K, 10M] Act: 17.9K Est: 50 VR: [25, 1M] Act: 5 Est: 120M VR: [10M, INF] Act: 1.2M SHIP DB2. Lineitem Orders Sort Scan NLJN SHIP ORA. Supplier MGJN Filter Sort ORA. Nation Region Sort GrpBy Ret. HSJN SHIP ORA. Customer SHIP NLJN ORA. Nation SHIP Temp Scan Temp Scan Scan Scan Check Temp Check Scan Check HSJN Sort GrpBy Ret. MGJN Filter VMV NLJN ORA. Supplier SHIP SHIP ORA. Nation VMV Scan TAOB Table Object Section Executable Code and Objects [lb..ub] Validity Range [lower .. upper bound] Virtual Materialized View Remote Statement Object Oracle Nickname Data / Knowledge Control Flow VMV SHIP ORA POP/FED Monitor