1 / 95

Informix Performance Optimization

Phone: 1-888-UCI FOR U 1-888-824-3678. Fax: 1-609-654-0957 e-mail: kfenn@uci-consulting.com. Informix Performance Optimization. by Kevin Fennimore. Overview:. Discuss steps for optimizing Discuss the output of the Set Explain command Discuss Indexing Strategies

howard
Download Presentation

Informix Performance Optimization

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: kfenn@uci-consulting.com InformixPerformance Optimization by Kevin Fennimore

  2. Overview: • Discuss steps for optimizing • Discuss the output of the Set Explain command • Discuss Indexing Strategies • Table Scans & Table Joins • Optimizer Directives • Discuss optimization techniques and examples • XTREE command

  3. Steps for Optimizing

  4. 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 processing time

  5. Setting up a Test Environment • Identify Problem Queries • Simplify Queries • Test on a machine with minimal system activity • Use database that reflects production data • Turn Set Explain on

  6. Optimizing the Query Understand the Requirements: • What is the object of the query? • What is the information required? • What is the order criteria? Examine the Schema: • Identify 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. ) Examine the Data : • Consider the number of rows examined vs. the number of rows returned • Determine the distribution of filter columns • Look at the relationship of joined tables: • one-to-one • one-to-many • many-to-many

  7. 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

  8. Any Questions?

  9. Set Explain Output

  10. 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

  11. 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

  12. 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)

  13. 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

  14. 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

  15. Set Explain: Example 6 QUERY: select * from items,stock where items.total_price=stock.unit_price Estimated Cost: 19 Estimated # of Rows Returned: 59 1) informix.items: SEQUENTIAL SCAN SORT SCAN: informix.items.total_price 2) informix.stock: SEQUENTIAL SCAN SORT SCAN: informix.stock.unit_price MERGE JOIN Merge Filters: informix.stock.unit_price = informix.items.total_price Prior to version 5.0 this would have caused an auto-index on the second table.

  16. Set Explain: Example 7 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

  17. Set Explain: Example 8 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 = 1999 ) in dbspace1, ( fiscal_year = 2000 ) in dbspace2, remainder in dbspace3

  18. Set Explain: Example 8 cont. QUERY: ------ select fiscal_year, account, posted_total_amt from ps_ledger where fiscal_year = 1999 and accounting_period = 10 and account between '1234' and '9999' 1) sysadm.ps_ledger: INDEX PATH Filters: (ps_ledger.fiscal_year = 1999 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'

  19. Any Questions?

  20. Indexing Strategies

  21. 1 0 0 > Level 2 (Root Node) 1 0 0 > 5 0 0 > Level 1 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 Level 0 D A T A Indexing Strategies: B+ Trees

  22. Indexing Strategies:Types of Indexes • Unique • Duplicate • Composite • Clustered • Attached • Detached

  23. Indexing Strategies:Leading Portion of an Index 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 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.

  24. Indexing Strategies: Guidelines • 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 FILL FACTOR option (version 7)

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

  26. Any Questions?

  27. Table Scans

  28. Types of Table Scans • Sequential • Auto Index • Index

  29. 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

  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 Index Scans:Upper and Lower Index Filters

  31. 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?

  32. 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

  33. 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 )

  34. Any Questions?

  35. Table Joins

  36. 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.

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

  38. 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 New Output in version 7.3

  39. Joining Tables: Table Order Consider the select: Select * from A, B where A.join_col = B.join_col How can the database satisfy this join? • Read from A then find matching rows in B • Read from B then find matching rows in A

  40. A then B 1,000 reads from A For each A row do an index scan into B (4 reads) Total reads: 5,000 (1,000 for A + 1,000*4 for B) B then A 50,000 reads from B For each B row do an index scan into A (3 reads) Total reads: 200,000 (50,000 for B + 50,000*3 for A) Joining Tables: Table OrderWho Cares? Table A - 1000 rows Table B - 50,000 rows This is a difference of 195,000 reads!!!

  41. A then B 1,000 reads from A For each A row do an index scan into B (4 reads) Total reads: 5,000 (1,000 for A + 1,000*4 for B) Total Rows Returned: 10 B then A Index scan of B (3 reads), then the data (10 reads) for a toal of 13 For each B row do an index scan into A (3 reads) Total reads: 43 (13 for B+10*3 for A) Total Rows Returned: 10 Joining Tables: Table OrderWhat is the best order? Assume 10 rows meet this condition • Table A - 1,000 rows Table B - 50,000 rows • Select * from A, B • where A.join_col = B.join_col • and B.filter_col = 1 General Rule: The table which returns the fewest rows, either through a filter or the row count, should be first.

  42. Joining Tables: Table OrderWhat affects the join order? • Number of rows in the tables • Indexes available for: • Filters • Join Columns • Data Distribution • UPDATE STATISTICS is very important

  43. Any Questions?

  44. Optimizer Directives

  45. Optimizer Directives • Changes the generated query plan by removing paths from consideration • Similar to Oracle’s HINTs • Better than HINTs • More options • Cannot be ignored • Negative directives • Set Explain output

  46. A then B Seq A, Seq B Cost:100 Seq A, Idx B Cost:50 Idx A, Idx B Cost:20 etc. B then A Seq B, Seq A Cost:100 Seq B, Idx A Cost:50 Idx B, Idx A Cost:10 etc. Optimizer Directives Select --+ORDERED * from A, B where A.join_col = B.join_col With the directive, ORDERED, the optimizer only considers paths that read from A then B. The lowest cost is then chosen from those paths. With the directive, this path would be chosen Normally, this path would be chosen

  47. Optimizer Directives:Syntax • SELECT --+ directive text • SELECT {+ directive text } • UPDATE --+ directive text • UPDATE {+ directive text } • DELETE --+ directive text • DELETE {+ directive text } C-style comments are also valid as in: SELECT /*+directive*/

  48. Types of Directives • Access Methods • Join Methods • Join Order • Optimization Goal

  49. Types of Directives:Access Methods • index - forces use of a subset of specified indexes • index_one - forces use of one of the specified indexes • index_all - forces use of all of the specified indexes • avoid_index - avoids use of specified indexes • full - forces sequential scan of specified table • avoid_full - avoids sequential scan of specified table

  50. Types of Directives:Join Order • ordered - forces table order to follow the from clause

More Related