1 / 41

Tuning Oracle SQL

Tuning Oracle SQL. The Basics of Efficient SQL Common Sense Indexing The Optimizer: Making SQL Efficient Finding Problem Queries Oracle Enterprise Manager Wait Event Interface. The Basics of Efficient SQL. SELECT FOR UPDATE Filtering WHERE ORDER BY often ignored query complexity

avent
Download Presentation

Tuning Oracle SQL

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. Tuning Oracle SQL • The Basics of Efficient SQL • Common Sense Indexing • The Optimizer: • Making SQL Efficient • Finding Problem Queries • Oracle Enterprise Manager • Wait Event Interface

  2. The Basics of Efficient SQL • SELECT • FOR UPDATE • Filtering • WHERE • ORDER BY • often ignored • query complexity • GROUP BY SELECT * FROM division; SELECT division_id, name, city, state, country FROM division; SELECT division_id FROM division;

  3. The Basics of Efficient SQL • SELECT • FOR UPDATE • Filtering • WHERE • ORDER BY • often ignored • query complexity • GROUP BY • Avoid unintentional full table scans • SELECT * FROM division WHERE country LIKE '%a%'; • Match indexes • Exact hits (equality) • SELECT * FROM division WHERE division_id = 1; • Range scans / skip scans / full index scans • EXISTS (correlate) faster than IN • Biggest filters first • Full table scans can sometimes be faster

  4. Resorts on result after WHERE and GROUP BY • Don’t repeat sorting (ORDER BY often ignored) • by SELECT • SELECT division_id FROM division ORDER BY division_id; • by WHERE • SELECT * FROM division WHERE division_id < 10 ORDER BY division_id; • GROUP BY • SELECT state, COUNT(state) FROM division GROUP BY state ORDER BY state; • by DISTINCT • SELECT DISTINCT(state) FROM division ORDER BY state; • by indexes • SELECT division_id FROM division ORDER BY division_id; The Basics of Efficient SQL • SELECT • FOR UPDATE • Filtering • WHERE • ORDER BY • often ignored • query complexity • GROUP BY

  5. Resorts on result after WHERE and GROUP BY • Don’t repeat sorting (ORDER BY often ignored) • by SELECT • SELECT division_id FROM division ORDER BY division_id; • by WHERE • SELECT * FROM division WHERE division_id < 10 ORDER BY division_id; • GROUP BY • SELECT state, COUNT(state) FROM division GROUP BY state ORDER BY state; • by DISTINCT • SELECT DISTINCT(state) FROM division ORDER BY state; • by indexes • SELECT division_id FROM division ORDER BY division_id; The Basics of Efficient SQL • SELECT • FOR UPDATE • Filtering • WHERE • ORDER BY • often ignored • query complexity • GROUP BY

  6. The Basics of Efficient SQL • SELECT • FOR UPDATE • Filtering • WHERE • ORDER BY • often ignored • query complexity • GROUP BY • Use WHERE not HAVING GROUP BY SELECT state, COUNT(state) FROM division GROUP BY state ORDER BY state; HAVING (filters aggregate) SELECT state, COUNT(state) FROM division GROUP BY state HAVING COUNT(state) > 1; use WHERE SELECT state, COUNT(state) FROM division WHERE state = 'NY' GROUP BY state; not HAVING SELECT state, COUNT(state) FROM division GROUP BY state HAVING state = 'NY';

  7. The Basics of Efficient SQL • SELECT • FOR UPDATE • Filtering • WHERE • ORDER BY • often ignored • query complexity • GROUP BY • Use WHERE not HAVING GROUP BY SELECT state, COUNT(state) FROM division GROUP BY state ORDER BY state; HAVING (filters aggregate) SELECT state, COUNT(state) FROM division GROUP BY state HAVING COUNT(state) > 1; use WHERE SELECT state, COUNT(state) FROM division WHERE state = 'NY' GROUP BY state; not HAVING SELECT state, COUNT(state) FROM division GROUP BY state HAVING state = 'NY';

  8. The Basics of Efficient SQL • Functions • conversions • miss indexes • counteract with function based indexing • avoid using • DECODE • CASE expressions • set operators (UNION) • Use sequences • Use equality (=) or range scans (>) • avoid negatives (!=, NOT) • avoid LIKE

  9. The Basics of Efficient SQL • Joins • avoid Cartesian Products • avoid anti joins • avoid outer joins • perhaps replace • multiple table complex joins • with subquery semi joins and inline views • Be careful with views

  10. Common Sense Indexing • Don’t always need indexes • table with few columns • static data • small tables • appended tables (SQL*Loader) • How to index • single column surrogate sequences • don’t override PK and FKs • avoid nullable columns

  11. Common Sense Indexing • Read write indexing • BTree • Often read only • Bitmaps • IOTs • Clusters

  12. Common Sense Indexing • Read write indexing • BTree • function based • can help a lot • get out of control • everybody wants one • reverse key • surrogate keys • High insertion rates • not DW • Oracle RAC

  13. Common Sense Indexing • Often read only • Bitmaps • can be much faster than BTrees • twice as fast in my book • at a previous client • 1 year of DML activity • 100s of times slower • problem was nobody knew why • and nobody wanted to change anything

  14. Common Sense Indexing • Often read only • IOTs • small number of columns • small tables • heard good things in Oracle RAC • even highly active DML environments

  15. The Optimizer • Is intelligent • better with simple queries • Is usually correct • Nothing is set in stone • Verify SQL code efficiency • use EXPLAIN PLAN • SET AUTOTRACE ON EXPLAIN • $ORACLE_HOME/rdbms/admin/utlxplan.sql

  16. The Optimizer • Everything cost based • rule based is redundant • Maintain statistics • Dynamic sampling • OPTIMIZER_DYNAMIC_SAMPLING • Set TIMED_STATISTICS • Histograms • maintain as for statistics • use for unevenly distributed indexes

  17. The Optimizer • Tables • full Table scans • small static tables • reading most of the rows • over 10% for the Optimizer • reading deleted rows • parallel table scans • sample table scans • SELECT * FROM generalledger SAMPLE(0.001); • ROWID scans

  18. The Optimizer • Indexes • index unique scan • index range scan • reverse order index range scan • index skip scan • index full scan • fast full index scan • others (very specific)

  19. The Optimizer • Joins • nested loop join • most efficient • row sets both small • one large and one small row set • one sorted • hash join • both large with little difference • temporary hash table generated

  20. The Optimizer • More on joins • sort merge join • inefficient • both rows sets sorted then merge sorted • other join types • semi joins • bitmap joins • star queries • Cartesian joins • outer joins (nested, hash or sort merge)

  21. The Optimizer • Hints can change things • influence the optimizer • CURSOR_SHARING • FIRST or ALL_ROWS • DYNAMIC_SAMPLING • change table scans • FULL

  22. The Optimizer • More on hints • change index scans • INDEX_ASC, INDEX_DESC • INDEX_FFS • INDEX_JOIN (join indexes) • INDEX_SS_ASC or INDEX_SS_DESC • NO_INDEX, NO_INDEX_FFS or NO_INDEX_SS • change joins • can change join type and influence with parameters • parallel SQL

  23. Finding Problem Queries • EXPLAIN PLAN • SET AUTOTRACE ON EXPLAIN • $ORACLE_HOME/rdbms/admin/utlxplan.sql • SQL Trace and TKPROF

  24. Finding Problem Queries • Performance views • V$SQLAREA • executions • parsing • sorting • disk and buffer reads • fetching • V$SQL • optimizer cost • CPU time • elapsed time Wait Event Interface

  25. Wait Event Interface

  26. Wait Event Interface

  27. Wait Event Interface

  28. Wait Event Interface

  29. Wait Event Interface

  30. Wait Event Interface

  31. Wait Event Interface

  32. Performance overview

  33. Database health overview

  34. Drilldown

  35. More drilldown

  36. TopSQL

  37. EXPLAIN PLAN

  38. Wait Event Interface

  39. Wait Event Interface

  40. Wait Event Interface

  41. Use the help files Use the HELP FILES in Oracle Enterprise Manager

More Related