1 / 149

Informix SQL Performance Tuning

Phone: 1-888-UCI FOR U 1-888-824-3678. Fax: 1-609-654-0957 e-mail: mwalker@uci-consulting.com. Informix SQL Performance Tuning. Mike Walker. Overview:. Discuss steps for optimizing Discuss the output of the Set Explain command Finding Slow Running SQL Discuss Indexing Schemes

kevlyn
Download Presentation

Informix SQL Performance Tuning

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. Phone: 1-888-UCI FOR U 1-888-824-3678 Fax: 1-609-654-0957 e-mail: mwalker@uci-consulting.com Informix SQLPerformance Tuning Mike Walker

  2. Overview: • Discuss steps for optimizing • Discuss the output of the Set Explain command • Finding Slow Running SQL • Discuss Indexing Schemes • Data Access Methods • Optimizer Directives • Discuss optimization techniques and examples • XTREE command • Correlated Sub-Queries

  3. What will not be covered: • Engine & Database Tuning: • Onconfig settings • Disk/Table Layouts • Fragmentation, etc

  4. Steps for Optimizing

  5. Optimization Goal:Increase Performance • Reduce I/O • reduce I/O performed by the engine • reduce I/O between the back-end and the front-end (reduce number of database operations) • Reduce processing time

  6. Setting up a Test Environment • Identify Problem Queries • Easier to spot • Easier to trace • Simplify Queries • Test on a machine with minimal system activity • Use database that reflects production data • Number of rows & similar distributions • Want same query plan • Want similar timings • Turn Set Explain on • Change configuration parameters • Turn PDQ on • Bounce engine to clear LRUs

  7. Optimizing the Query:Understand the Requirements • What is the object of the query? • What is the information required? • What is the order criteria?

  8. Optimizing the Query:Examine the Schema • Identify the the data types and indexes on the columns being: • selected • used as filters • used in joins • used for sorting • Be aware of constraints on the data ( e.g. primary, check, etc. ) • Some constraints are enforced with indexes • Primary and Unique constraints may help identify when expect single row to be returned • Check constraints may hint at data distributions

  9. Optimizing the Query:Examine the Data • Consider the number of rows examined vs. the number of rows returned • Determine the distribution of filter columns • dbschema -hd <tablename> -d <database> (if have stats on that column) • Select count with group • Look at the relationship of joined tables: • one-to-one • one-to-many • many-to-many

  10. UPDATE STATISTICS ON TABLE query_table; SET EXPLAIN ON; SELECT . . . Optimizing the Query:Run, Examine and Modify • Run the Query: query.sql $ timex dbaccess db query.sql > try1.out 2>&1 • Examine the Set Explain output • Modify the query and/or schema (use directives to test various paths) • Run the query again

  11. Optimizing the Query:Explain Output • The query plan is written to the file: sqexplain.out • File is created in the current directory (UNIX) • If use SQLEditor – file will be in home directory of the user that SQL was executed as • File will be appended to each time more SQL is executed in the same session • For NT, look for a file called “username.out” in %INFORMIXDIR%\sqexpln on the server

  12. Optimizing the Query:Explain Output • Sometimes sqexplain.out will not be written to, even though “SET EXPLAIN ON” statement has been executed • Turn off the EXPLAIN and turn it back on again: SET EXPLAIN OFF; SET EXPLAIN ON; SELECT …

  13. Optimizing the Query:Explain Output • In IDS 9.4… onmode –Y <sid>[0|1] Set or unset dynamic explain Creates file called: sqexplain.out.sid May have “issues”…

  14. Set Explain Output

  15. Set Explain: Example 1 QUERY: select * from stock order by description Estimated Cost: 6 Estimated # of Rows Returned: 15 Temporary Files Required For: Order By 1) informix.stock: SEQUENTIAL SCAN

  16. Set Explain: Example 2 QUERY: select * from stock where unit_price>20 order by stock_num Estimated Cost: 3 Estimated # of Rows Returned: 5 1) informix.stock: INDEX PATH Filters: informix.stock.unit_price > 20 (1) Index Keys: stock_num manu_code

  17. Set Explain: Example 3 QUERY: select manu_code from stock Estimated Cost: 2 Estimated # of Rows Returned: 15 1) informix.stock: INDEX PATH (1) Index Keys: stock_num manu_code (Key-Only)

  18. Set Explain: Example 4 QUERY: select * from stock where stock_num>10 and stock_num<14 Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.stock: INDEX PATH (1) Index Keys: stock_num manu_code Lower Index Filter: informix.stock.stock_num > 10 Upper Index Filter: informix.stock.stock_num < 14

  19. Set Explain: Example 5 QUERY: select * from stock, items where stock.stock_num = items.stock_num and items.quantity>1 Estimated Cost: 9 Estimated # of Rows Returned: 22 1) informix.stock: SEQUENTIAL SCAN 2) informix.items: INDEX PATH Filters: informix.items.quantity > 1 (1) Index Keys: stock_num manu_code Lower Index Filter: informix.items.stock_num = informix.stock.stock_num

  20. Set Explain: Example 6 QUERY: ------ select * from items, stock where items.total_price = stock.unit_price Estimated Cost: 35 Estimated # of Rows Returned: 496 1) informix.items: SEQUENTIAL SCAN 2) informix.stock: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: informix.items.total_price = informix.stock.unit_price

  21. Set Explain: Example 7 Table ps_ledger has the following index: create index psaledger on ps_ledger ( account, fiscal_year, accounting_period, business_unit, ledger, currency_cd, statistics_code, deptid, product, posted_total_amt ) fragment by expression ( fiscal_year = 2003 ) in dbspace1, ( fiscal_year = 2004 ) in dbspace2, remainder in dbspace3

  22. Set Explain: Example 7 cont. QUERY: ------ select fiscal_year, account, posted_total_amt from ps_ledger where fiscal_year = 2003 and accounting_period = 10 and account between '1234' and '9999' 1) sysadm.ps_ledger: INDEX PATH Filters: (ps_ledger.fiscal_year = 2003 AND ps_ledger.accounting_period = 10 ) (1) Index Keys: account fiscal_year accounting_period business_unit ledger currency_cd statistics_code deptid product posted_total_amt (Key-Only) (Serial, fragments: 0) Lower Index Filter: ps_ledger.account >= '1234' Upper Index Filter: ps_ledger.account <= '9999'

  23. Any Questions?

  24. Finding Slow SQL

  25. Finding Slow SQL • onstat –u address  flags   sessid   user     tty      wait     tout locks nreads nwrites 1b062064 Y--P--- 44948    cbsread  -        1c5f3cc8 0    1     0      0 1b06662c ---PX-- 44961    cbsdba   -        0        0    0     2022 118008 1b067520 Y--P--- 39611    cbsuser  -        1ecf6f00 0    1     5308 61240 address  flags   sessid   user     tty      wait     tout locks nreads nwrites 1b062064 Y--P--- 44948    cbsread  -        1c5f3cc8 0    1     0      0 1b06662c ---P--- 44961    cbsdba   -        0        0    1     2372 135200 1b067520 Y--P--- 39611    cbsuser  -        1ecf6f00 0    1     5308 61240 address  flags   sessid   user     tty      wait     tout locks nreads nwrites 1b062064 Y--P--- 44948    cbsread  -        1c5f3cc8 0    1     0      0 1b06662c ---P--- 44961    cbsdba   -        0        0    1     31294 6803308 1b067520 Y--P--- 39611    cbsuser  -        1ecf6f00 0    1     5308 61240

  26. Finding Slow SQL • onstat –g ntt Individual thread network information (times): netscb thread name    sid     open     read    write address 1d380f00 sqlexec      44961 16:46:29 16:46:29 16:46:29 >date Wed Apr  7 16:49:49 MDT 2004 Query has been executing for 3 mins 20 secs

  27. Finding Slow SQL • onstat –g sql 44961 or onstat –g ses 44961 Sess  SQL            Current            Iso Lock       SQL  ISAM F.E. Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers 44961 SELECT         cbstraining        CR  Not Wait   0    0    7.31 Current statement name : slctcur Current SQL statement :   select * from tab1, tab2 where tab1.a = tab2.b order by tab2.c Last parsed SQL statement :   select * from tab1, tab2 where tab1.a = tab2.b order by tab2.c

  28. Create a “suite” of performance monitoring scripts Finding Slow SQL These Informix “onstat” commands are easily “scriptable”!!

  29. Indexing Schemes

  30. 1 0 0 > 1 0 0 > 5 0 0 > 1 3 2 1 9 0 4 0 0 5 0 0 5 0 1 6 9 9 8 5 0 9 9 9 1 5 2 5 9 9 1 0 0 Indexing Schemes: B+ Trees Level 2 (Root Node) Level 1 Level 0 D A T A

  31. Indexing Schemes:Types of Indexes • Unique • Duplicate • Composite • Clustered • Attached • Detached In 9.x, all indexes are detached - index pages and data pages are not interleaved

  32. Indexing Schemes:Leading Portion of an Index Index is used for: SELECT * FROM XYZ WHERE a = 1 AND b = 2 AND c = 3 SELECT * FROM XYZ WHERE a = 1 AND b = 2 SELECT * FROM XYZ WHERE a = 1 ORDER BY a, b, c Consider an index on columns a, b and c on table xyz. Index is not used for: SELECT * FROM XYZ WHERE b = 2 AND c = 3 SELECT * FROM XYZ WHERE b = 2 SELECT * FROM XYZ WHERE c = 3 ORDER BY b, c

  33. Indexing Schemes: Guidelines • Evaluate Indexes on the following: • Columns used in joining tables • Columns used as filters • Columns used in ORDER BY’s and GROUP BY’s • Avoid highly duplicate columns • Keep key size small • Limit indexes on highly volatile tables • Use the FILLFACTOR option

  34. Indexing Schemes:Benefits vs. Cost Benefits • Speed up Queries • Guarantee Uniqueness • Cost • Maintenance of indexes on Inserts, Updates & Deletes • Extra Disk Space

  35. Any Questions?

  36. How Data is Accessed

  37. Data Access Methods • Sequential Scan • Index • Auto Index

  38. Index Scans:Upper and Lower Index Filters QUERY: select * from stock where stock_num>=99 and stock_num<=190 Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.stock: INDEX PATH (1) Index Keys: stock_num manu_code Lower Index Filter: informix.stock.stock_num >= 99 Upper Index Filter: informix.stock.stock_num <= 190

  39. 1 0 0 > 1 0 0 > 5 0 0 > 1 3 2 1 9 0 4 0 0 5 0 0 5 0 1 6 9 9 8 5 0 9 9 9 1 5 2 5 9 9 1 0 0 Index Scans:Upper and Lower Index Filters

  40. Index Scans:Upper and Lower Index Filters Create indexes on columns that are the most selective. For example: SELECT * FROM CUSTOMER WHERE ACCOUNT BETWEEN 100 and 1000 AND STATUS = “A” AND STATE = “MD” Which column is the most selective? Account, status or state?

  41. Index Scans:Key-Only QUERY: select manu_code from stock where stock_num = 190 Estimated Cost: 2 Estimated # of Rows Returned: 15 1) informix.stock: INDEX PATH (1) Index Keys: stock_num manu_code (Key-Only) Lower Index Filter: informix.stock.stock_num = 190

  42. Index Pages stock_num Data Pages stock_num, manu_code, qty Index Pages stock_num, manu_code Data Pages stock_num, manu_code, qty Index Scans: Key-Only select manu_code from stock where stock_num = 190 Index Read (not Key Only) Index Read (Key Only)

  43. Index Pages stock_num, manu_code Data Pages stock_num, manu_code, qty Index Scans: Key-Only select manu_code from stock where stock_num = 190 Table “stock” : 100,000 rows stock_num = 190 : 10,000 rows Key Only has saved 10,000 “jumps” to the Data Pages

  44. Index Scans: Key-First QUERY: select count(e) from mytable where a=1 and b=1 and d="Y" Estimated Cost: 4 Estimated # of Rows Returned: 1 1) informix.mytable: INDEX PATH Filters: informix.mytable.d = 'Y' (1) Index Keys: a b c d (Key-First) (Serial, fragments: ALL) Lower Index Filter: (informix.mytable.a = 1 AND informix.mytable.b = 1 )

  45. Index Scans: Key-First • May not see much advantage with Key-First Indexes. They may help some – especially for large wide tables • Can gain some benefit from adding additional columns to the end of the index to reduce the jumps from the index pages to the data pages • Evaluate adding a new index or changing the index to include the key-first column earlier in the index

  46. Any Questions?

  47. Table Joins

  48. Joining Tables: Join Methods • Nested Loop Join • Dynamic Hash Join • Sort Merge Join

  49. Joining Tables Consider the following query: select * from stock, items where stock.stock_num = items.stock_num and items.quantity>10 What we’re looking for is: All of the items records with a quantity greater than 10 and their associated stock records.

  50. Join Methods: Nested Loop Join QUERY: select * from stock, items where stock.stock_num = items.stock_num and items.quantity>10 Estimated Cost: 9 Estimated # of Rows Returned: 22 1) informix.stock: SEQUENTIAL SCAN 2) informix.items: INDEX PATH Filters: informix.items.quantity > 10 (1) Index Keys: stock_num manu_code Lower Index Filter:items.stock_num = stock.stock_num NESTED LOOP JOIN Notice the index on the joined column

More Related