1 / 77

DB-04 Tuning OpenEdge™ SQL: Boosting Your SQL Application Performance

DB-04 Tuning OpenEdge™ SQL: Boosting Your SQL Application Performance. Steve Pittman Principle Software Engineer, SQL Team. Agenda. OpenEdge SQL Server architecture Configuration and Security Applications and SQL SQL Execution thru the SQL engine Tuning and best practices Questions.

dwayne
Download Presentation

DB-04 Tuning OpenEdge™ SQL: Boosting Your SQL Application Performance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB-04Tuning OpenEdge™ SQL: Boosting Your SQL Application Performance Steve Pittman Principle Software Engineer, SQL Team

  2. Agenda • OpenEdge SQL Server architecture • Configuration and Security • Applications and SQL • SQL Execution thru the SQL engine • Tuning and best practices • Questions DB-04 Tuning OpenEdge SQL

  3. Under Development D I S C L A I M E R D I S C L A I M E R • This talk includes information about potential future products and/or product enhancements. • What I am going to say reflects our current thinking, but the information contained herein is preliminary and subject to change. Any future products we ultimately deliver may be materially different from what is described here. DB-04 Tuning OpenEdge SQL

  4. OpenEdge SQL System Architecture SQL Servers Shared Memory SQL client SQL client SQL & 4GL Broker Database 4GL client 4GL client 4GL Servers DB-04 Tuning OpenEdge SQL

  5. SQL Architecture Components • SQL engine • Statement planning • Statement execution - scan, join, sort, etc. • Communications • Database storage manager • Persistent data storage and indexes • Transactions • Locking • Etc. DB-04 Tuning OpenEdge SQL

  6. Agenda • OpenEdge SQL Server architecture • Configuration and Security • Applications and SQL • SQL Execution thru the SQL engine • Tuning and best practices DB-04 Tuning OpenEdge SQL

  7. OpenEdge SQL Server Configuration Tuning the server: Threads vs. Processes • Threads are better than processes • Less resource consumption • More sharing - pages, caches • Better load balancing • Faster execution • Startup parameters • -Mi - minimum threads (clients) per server • -Ma - maximum threads (clients) per server • -Mn - maximum server processes • Default settings not the best for SQL! • Better example: -Mi 4 -Ma 8 -Mn 4 DB-04 Tuning OpenEdge SQL

  8. OpenEdge SQL - Startup Parameters SQL Servers Shared Memory SQL client SQL client -Mi 5 5 threads min -Ma 1010 threads max 4GL Servers DB-04 Tuning OpenEdge SQL

  9. OpenEdge SQL Server Configuration Separating 4GL and SQL brokers/servers … examples • Example: Start a 4GL Primary broker • Example: Start a Secondary SQL broker proserve Sports2000 -S 6000 -H localhost -n 48 -Mn 8 -Mpb 4 -ServerType4GL -minport 6100 -maxport 6300 -Mi 1 -Ma 5 proserve Sports2000 -S 5000 -H localhost -m3 –Mpb 3 -ServerType SQL -minport 5100 -maxport 5300 -Mi 4 -Ma 8 DB-04 Tuning OpenEdge SQL

  10. OpenEdge SQL - Configured SQL Servers Shared Memory SQL Broker SQL client SQL client Database 4GL client 4GL client 4GL Servers 4GL Broker DB-04 Tuning OpenEdge SQL

  11. OpenEdge SQL Security Model SQL Server User List SQL client Database Making your data safe and accessible • Authentication • Who am I? • aaa • jones • smith DB-04 Tuning OpenEdge SQL

  12. OpenEdge SQL Security Model Enabling safe data access • Authorization • What can I do? • Closed model (SQL) vs. open model (4GL) • Every action possible must be authorized • Privileges • DBA - can do everything • Table (also column) privileges • Sequence, stored procedure privileges • Common error • “Access Denied (Authorization failed) (7512)” DB-04 Tuning OpenEdge SQL

  13. OpenEdge SQL Security Model Best practices • Require user authentication • Define two, limited-use DBA users • Do not use predefined system DBA userid, please!! • Grant table privileges to all or selected users • GRANT is online DB-04 Tuning OpenEdge SQL

  14. Agenda • OpenEdge SQL Server architecture • Configuration and Security • Applications and SQL • SQL Execution thru the SQL engine • Tuning and best practices DB-04 Tuning OpenEdge SQL

  15. Applications and OpenEdge SQL SQL Server Database SQL client Client and server interaction A look at the relationship • Representative applications • Crystal Reports • Java, WebSphere, and JDBC • Delphi, ODBC, Web server DB-04 Tuning OpenEdge SQL

  16. Statement-oriented Select SQL statements • Prepare, execute, fetch cycle • Result set • ODBC: fetch array size • Statement may not be visible (Crystal, etc.) Select onum, name, cnum …from pub.customer c, pub.orders owhere c.custnum = o.custnum SQL Server Database SQL client 8765 General Motors 1235143 Toyota Mfg, Inc. 4228123 Chrysler Motors 274… … … DB-04 Tuning OpenEdge SQL

  17. Statement-oriented Update sql statements • Prepare, execute “cycle” • Simple execute message exchange • No table data flow between client and server Update pub.ordersset delivery_date = ‘06-30-2005’where delivery_date = ‘06-15-2005’ SQL Server Database SQL client 2 rows updated DB-04 Tuning OpenEdge SQL

  18. Statement-oriented Patterns of statements • Similar statement are executed by common prepared statement • Select * from sales where date =‘06/15/05’; • Select * from sales where date =‘01/08/04’; • Automatic optimization for statements on a connection Select *from pub.sales where date = ‘06/15/05’ Select *from pub.sales wheredate = ‘01/08/04’ SQL Server Database SQL client 8765 General Motors 1235143 Toyota Mfg, Inc. 4228123 Chrysler Motors 274… … … 3205 Laval Motors 689 DB-04 Tuning OpenEdge SQL

  19. Transactions Isolation levels • Read Committed - default • Repeatable Read • Change via ODBC/JDBC API • ODBC DSN Advanced option SQL Server SQL client 1 Database 4GL client 2 DB-04 Tuning OpenEdge SQL

  20. Schemas Sets of tables with common owner • PUB schema • Schema for inter-operability with 4GL • Default schema • Changing the default • Set schema ‘pub’ ; SQL Server PUB SMITH SQL client JONES DB-04 Tuning OpenEdge SQL

  21. Agenda • OpenEdge SQL Server architecture • Configuration and Security • Applications and SQL • SQL Execution thru the SQL engine • Tuning and best practices DB-04 Tuning OpenEdge SQL

  22. Executing SQL Statements What the server does • Build query plan for SQL statement • Execute query plan to build result set • Stream result set back to client • How to choose best query plan? OpenEdge SQL Server Query Plan Select * from T1,T2 where T1.f=T2.g • Join • Table Scan T1 • Index Scan T2 Database DB-04 Tuning OpenEdge SQL

  23. What’s in a Query Plan Building blocks for execution • Query plan elements • Table scan • Index scan • Join • Restrict • Project • Sort • Organization and form • What’s useful to know DB-04 Tuning OpenEdge SQL

  24. Building the Query Plan schema sql statistics sql statement SQL Optimizer U s e r SQL Runtime Query plan DB-04 Tuning OpenEdge SQL

  25. Cost-based Optimization What does this mean? • Optimization model • Figure out all feasible ways to do a step • Figure out the costs of each way • Choose way with smallest cost • Optimize from the inside out • Optimize table access • Optimize joins • Optimize result set • What cost is DB-04 Tuning OpenEdge SQL

  26. How the Optimizer Knows Cost Rule-based mode • When no statistics exist • Table and index metadata • number of key components used • unique and non-unique indexes • Default column selectivity per operator • “=” is .04, “between” is .1, etc. • Heuristics • Assume all tables have n K rows • Cost = • cardinality * selectivity * row-cost * k DB-04 Tuning OpenEdge SQL

  27. How Optimizer Knows Cost Statistics-based mode - basic statistics • Table statistics - cardinality • Column statistics - data distribution++ • Individual column selectivity per operator • % of table’s data returned by predicate • Combining multiple columns selectivities • Best for range operators(“between”, etc.), especially in Version 9 • Cost = • cardinality * selectivity * row-cost * k DB-04 Tuning OpenEdge SQL

  28. How Optimizer Knows Cost Default statistics select * from pub.customer c, pub.order o where c.custnum = o.custnum and o.orderdata between ‘05/01/05’ and ‘05/30/05’ What percentage of data - column statistics How many rows - table statistics DB-04 Tuning OpenEdge SQL

  29. More on Column Statistics • Based on sampling table’s data • “Histogram” derived from sampled data • OpenEdge 10 vs. Version 9 • New: estimated number of distinct values per histogram bucket - an explanation… • New: extensibility in statistics format in schema Example - Histogram of 2000 random integers 1..1150 Example - number distinct values for Histogram DB-04 Tuning OpenEdge SQL

  30. How the Optimizer Knows Cost Statistics-based mode - index statistics • Counts number of values for components of index • Prefixes of an index key - leading sequence of key components • Gives most precise estimate of number of rows satisfying “=” and “in” operators • Accounts for correlation between components of an index key • Can accurately model very, very low selectivity and very high selectivity DB-04 Tuning OpenEdge SQL

  31. How Optimizer Knows Cost Index statistics select * from pub.customer c, pub.order o where c.custnum = o.custnum and o.orderdata between ‘05/01/05’ and ‘05/30/05’ How many matching rows - index statistics DB-04 Tuning OpenEdge SQL

  32. More on Index Statistics • Count of number of unique values for each prefix • OpenEdge 10 vs. Version 9 • OE 10: counts for all prefixes • Prefix - key components 1 to n • V9: counts for first key component, and last 3 prefixes • V9 “interpolation” for prefixes without counts • Estimate via “straight line” between first 2 counts DB-04 Tuning OpenEdge SQL

  33. Example - OpenEdge 10 Index Statistics • Sample single table query select … from Sales_History where terr_id = ‘abc’ and subt_id = 1 and yr = 2004 and zip = ‘05601’ and demo_cat = ‘xyz’ and cust_stat = ‘M’ and regn = ‘NE’ and countycd = 5 ; DB-04 Tuning OpenEdge SQL

  34. Example - OpenEdge 10 Index Statistics abc 2004 1 50K 100K 90 10K abc 056 xyz M 1 NE 5 5 75 Indexes and key components: Xsales_terr index statistics 300K 800K 1M 90 10K 20K Xconsumer_id Assume cardinality (total number of rows) = 1,000,000 Xmarket_seg DB-04 Tuning OpenEdge SQL

  35. Example - Cost via Index Statistics 5 components specified index statistics Cost = (1M/ 800K )rows * IO cost per row select … from Sales_History where terr_id = ‘abc’ and subt_id = 1 and zip = ‘05601’ and demo_cat = ‘xyz’ and cust_stat = ‘M’; DB-04 Tuning OpenEdge SQL

  36. Optimizing Join Execution Or, What gets optimized • Join order • Join methods • index join (= augmented nested loop) • nested loop • dynamic index (looks like index join) • Hash join when low data volume • Index join when larger data volume DB-04 Tuning OpenEdge SQL

  37. What Optimizer Does for Join Order • Consider many possible join orders • choose least cost order • Use join cardinality as cost metric • Joining small amount of data to larger amount of data is usually least cost • Cost estimation drivers • Table statistics • Index statistics • OpenEdge 10 and V9.1E employ much more powerful join order exploration DB-04 Tuning OpenEdge SQL

  38. Planning Join Method - Index Join indexk-OrdL Sales scan bracket rows get 1 row SalesHist Or, augmented nested loop Joined data Select … From Sales s, SalesHist hWhere s.city = ‘MyTown’ And s.acct = h.acct ; Join DB-04 Tuning OpenEdge SQL

  39. Example - OpenEdge10 Index Statistics F1 F3 F2 350K 700K 90 10K F1 F4 F5 F6 F2 F10 F11 5 75 Indexes and key components: index statistics Xsales_terr select … from Sales s, Sales_History h where s.terr = h.F1 and s.acct = h.F2 and s.city = h.F3 and s.col01 = h.F4 and s.col02 = h.F5 and s.regn = h.F10 and s.segid = h.F11 100K 800K 1M 90 10k 20k Xconsumer_id Xmarket_seg DB-04 Tuning OpenEdge SQL

  40. Agenda • OpenEdge SQL Server architecture • Configuration and Security • Applications and SQL • SQL Execution thru the SQL engine • Tuning and best practices DB-04 Tuning OpenEdge SQL

  41. What to Tune • Tune your SQL server • SQL Statistics • Releases • Tune your SQL statements • Possible problems • Finding problems • Special situations OpenEdge SQL Server SQL client Database Tune here DB-04 Tuning OpenEdge SQL

  42. Tuning Your SQL Server • Create, or update, sql statistics • Move to newer release • OpenEdge 10.0B is better than 10.0A • OpenEdge 10.0A is better than Version 9.1E • Version 9.1E is better than 9.1D • Latest Service Pack • OpenEdge 10 service packs • Version 9.1 service packs • Possibly consider adding indexes DB-04 Tuning OpenEdge SQL

  43. Updating SQL Statistics • Default statistics • “update statistics [for <table name>];” • Best statistics • “update table statistics and index statistics and column statistics [for <table name>];” • reads all of each index for all tables, or for one table. • May be resource intensive • Example - 4.5G customer db, 600 tables 4500 indexes • Index stats runtime = 25 cpu minutes • Index statistics drive best join optimizations • Must be DBA • When to do • relationships between tables or indexes change DB-04 Tuning OpenEdge SQL

  44. Tuning Your SQL Statements Possible problems and remedies • Join relationships not completely expressed in predicates • Remedy - more, better join predicates on sql statements • Every pair of tables with a relationship should have a predicate giving that relation • “select … from pub.orders O, pub.orderlines L where O.onum = L.onum” DB-04 Tuning OpenEdge SQL

  45. Tuning Your SQL Statements Possible problems and remedies - more • Leading keys of indexes not specified • Remedy - give predicates on leading keys • Predicates best for index use not used • Remedy - best are “=”, IN • Almost best - BETWEEN • Good - >, >=, <, <= • Note - OR can disable optimizations • Several similar indexes not distinguished as expected • Remedy: index statistics DB-04 Tuning OpenEdge SQL

  46. Tuning Your SQL Statements F10 F11 Possible problems and remedies - example F1 F1 F3 F2 F4 Indexes and key components: Xsales_terr select … from Sales s, Sales_History h where s.accno = h.F2 and s.city = h.F3 and s.col01 = h.F4 and s.col02 = h.F5 and s.regn = h.F10 and s.segid = h.F11 F1 F7 F1 F4 F5 F6 F2 Xconsumer_id s.terr = h.F1 and Xmarket_seg DB-04 Tuning OpenEdge SQL

  47. Tuning Your SQL Statements Finding problems • Time: do simple timing of data access requests • Inspect: SQL statement executed • Investigate: SQL virtual system table for query plan • Access query plan for sql statement executed • Query plan data will show: • tables • indexes • joins • predicates • order • Note: only your query plans available (currently) • Must be DBA or have DBA grant privileges DB-04 Tuning OpenEdge SQL

  48. Getting the Query Plan • Basic form select substring("_Description",1,80) from pub."_Sql_Qplan“where "_Pnumber" = (select max("_Pnumber") from pub."_Sql_Qplan" where "_Ptype" > 0 ); • Simplify with views select * from my_Qplan; DB-04 Tuning OpenEdge SQL

  49. Getting the Query Plan • Get your SQL statement • Crystal Reports: • Database menu • “Show SQL query …” • Copy into a SQL query tool • SQL Explorer, WinSQL, DB Visualizer • Run your statement • Run the SQL statement to get query plan DB-04 Tuning OpenEdge SQL

  50. Query Plan - Operations What you need to find • Order of operations • Top to bottom • Tables • Indexes • Indexkeys • Joins • Join predicates DB-04 Tuning OpenEdge SQL

More Related