1 / 40

Advanced SQL Application Tuning: Find the Proverbial Needle in the Haystack

Advanced SQL Application Tuning: Find the Proverbial Needle in the Haystack. Bert Scalzo, Ph.D. Bert.Scalzo@Quest.com. About the Author. Oracle DBA from 4 through 10g Worked for Oracle Education Worked for Oracle Consulting Holds several Oracle Masters

sera
Download Presentation

Advanced SQL Application Tuning: Find the Proverbial Needle in the Haystack

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. Advanced SQL Application Tuning: Find the Proverbial Needle in the Haystack Bert Scalzo, Ph.D. Bert.Scalzo@Quest.com

  2. About the Author • Oracle DBA from 4 through 10g • Worked for Oracle Education • Worked for Oracle Consulting • Holds several Oracle Masters • BS, MS and PhD in Computer Science • MBA and insurance industry designations • Articles in • Oracle Magazine • Oracle Informant • PC Week (now E-Magazine)

  3. About Quest Software

  4. Where Can We Look • What is Tunable: • Hardware • Operating System • Database • Network • Application Too often people attempt to improve database application performance by focusing on just the first three: hardware, OS and RDBMS …

  5. Performance Pyramid Wrong Focus Network Hardware OS DBMS Application

  6. Where Should We Look Most application performance problems result from an application’s poor general design or grossly inefficient SQL • Do your developers know: • Sub-queries (correlated and not) • Outer Joins • Minus • Union and Union-All Golden Rule #1:It’s the application stupid!

  7. Performance Pyramid Best Tuning Network Hardware OS DBMS Application

  8. Hardware Won’t Compensate Often people have unrealistic expectation that using expensive hardware is only way to obtain optimal application performance • CPU • SMP • MPP • Disk IO • 15,000 RPM • RAID (EMC) • OS • UNIX • 64-bit • Oracle • OPS / PQO • 64-bit

  9. Hardware Tuning Example • Problem: Data load runtime 4+ hours • 8 400-MHz 64-bit CPU’s • 4 Gigabytes UNIX RAM • 2 Gigabytes EMC Cache • RAID 5 (slower on writes) • Attempt #1: Bought more hardware • 16 400-MHz 64-bit CPU’s • 8 Gigabytes UNIX RAM • 4 Gigabytes EMC Cache • RAID 1 (faster on writes) • Runtime still 4+ hours !!!

  10. Application Tuning Example • Attempt #2: Redesigned application • Convert PL/SQL to Pro-C • Run 16 streams in parallel • Better utilize UNIX capabilities • Run time = 20 minutes !!! • Attempt #3: Tuned the SQL code • Tune individual SQL statements • Use Dynamic SQL method # 2 • Prepare SQL outside loop • Execute Prep SQL in loop • Run time = 15 minutes !!!

  11. Lesson Learned • Hardware: • Cost approximately $1,000,000 • System downtime for upgrades • Zero runtime improvement • Loss of credibility with customer • Redesign: • 4 hours DBA $150/hour = $600 • 20 hours Developer $100/hour = $2000 • Total cost = $2600 or 385 times less Golden Rule #2:Application redesign much cheaper than hardware!

  12. What Exactly is Tuning How you define tuning implicates how you will approach it. And the approach directly effects the tuning ROI that you can expect • Tuning is an art requiring creativity • Tuning is a science requiring regimen Thus,we must use both intuition and rules …

  13. How to Approach Tuning • Tuning Approaches: • Reactively (after the fact) - intuition • Proactively (as you go) - rules • Hybrid (combination) - both Tuning as you go may be impractical, and after you’re done it may be too late. Often it’s best to apply general tuning guidelines as you go, and then identify problem areas for improvement. Golden Rule #3:Best to tune before, during, and after!

  14. Who will find the needle first?

  15. Carpenter Needs Tools Too many people try to tune applications the hard way, by visual inspection based upon their own limited SQL knowledge But SQL as a language is far too complex, and Oracle’s optimizers too dynamic to rely solely on the intuition of developers and DBA’s … Golden Rule #4:Utilize tools to tune SQL!

  16. Commercial Tuning Tools • Reactive • Quest’s Spotlight on Oracle • Oracle’s Performance Overview & Manager (OEM) • Embarcadero’s DBArtisan & Performance Center • BMC’s SmartDBA Cockpit & DBXray • Proactive • Quest’s SQL Navigator & TOAD with SQLab Xpert option • Oracle’s UTLPLP.SQL script (parallel explain plan script) • Oracle’s SQL Analyze (OEM) • Embarcadero’s Rapid SQL • BMC’s SQL Programmer • CA’s SQL Station (Plan Analyzer) • Hybrid • Quest’s SQLab Xpert • Oracle’s trace files and TKPROF utility • Oracle’s UTLBSTAT and UTLESTAT scripts • Oracle’s Stats Pack (only for versions 8i and newer) • Quest’s SQL Expert (formerly LECCO Technology)

  17. Reactive: Spotlight

  18. High Logical & Physical IO’s

  19. High Read & Write Waits

  20. Examine Top SQL Consumers

  21. Explain Plan for Top SQL

  22. Reactive Summary • After the fact tuning dumped on the DBA. • Often too little, too late ... • Pros: • DBA’s tend to know SQL very well • Cons: • DBA’s often focus more on OS and RDBMS • DBA’s often don’t know the application code • DBA’s often don’t know the data’s nature • DBA’s often don’t know the business rules • DBA’s generally have way too much to do

  23. Proactive: TOAD Proc Editor

  24. Proactive: TOAD SQL Editor

  25. Proactive: TOAD SQL Tuning

  26. Proactive Summary • Tuning performed during coding by Developer. • Often too much, too soon ... • Pros: • Developers focus is application code & data • Developers know the relevant business rules • Cons: • Developers must take time to identify problem code • Developers often don’t tune SQL as well as DBA • Developers often measured by lines of code • Developers often not part of production support

  27. Hybrid: SQLab

  28. Explain Plan for Top SQL

  29. SQLab can Advise & Fix!!!

  30. Hybrid Summary Best of both worlds for DBA’s and Developers.

  31. SQL Guidelines Rule #1: Watch Indexed WHERE Conditions • Assume index on address (city, state) • non-leading index column references cannot use indexes • where state = 'TX' [Index Not used] • where city = 'DALLAS' [Index Used] • where state = 'TX' and city = 'DALLAS' [Index Used] • NOT, != and <> disable index use • where state not in ('TX', 'FL','OH') [Index Not used] • where state != 'TX' [Index Not used] • NULL value references can never use indexes • where state IS NULL [Index Not used] • where state IS NOT NULL [Index Not used] • expression references can never use indexes • where substr(city,1,3) = 'DAL' [Index Not used] • where city like 'DAL%' [Index Used] • where city || state = 'DALLASTX' [Index Not used] • where city = 'DALLAS' and state = 'TX‘ [Index Used] • where salary * 12 >= 24000 [Index Not used] • where salary >= 2000 [Index Used]

  32. SQL Guidelines Rule #2:Watch Non-Indexed WHERE Conditions • Oracle evaluates Non-Indexed conditions linked by AND bottom up • Bad: select * from address where • areacode = 972 and • type_nr = (select seq_nr from code_table where type = ‘HOME’) • Good: select * from address where • type_nr = (select seq_nr from code_table where type = ‘HOME’) and • areacode = 972 • Oracle evaluates Non-Indexed conditions linked by OR top down • Bad: select * from address where • type_nr = (select seq_nr from code_table where type = ‘HOME’) or • areacode = 972 • Good: select * from address where • areacode = 972 or • type_nr = (select seq_nr from code_table where type = ‘HOME’)

  33. SQL Guidelines Rule #3:Order Table in the FROM Clause • important under rule based optimizer, and won't hurt under cost based optimizer • order FROM clauses in descending order of table sizes based upon row counts • for example • select * from larger table, smaller table • select * from larger table, smaller table, smallest table • select * from larger table, smaller table, associative table Note – rule based optimizer only

  34. SQL Guidelines Rule #4:Consider IN or UNION in place of OR • if columns are not indexed, stick with OR • if columns are indexed, use IN or UNION in place of OR • IN example • Bad: select * from address where • state = 'TX‘ or • state = 'FL‘ or • state = 'OH‘ • Good: select * from address where • state in ('TX','FL','OH') • UNION example • Bad:select * from address where • state = ‘TX’ or • areacode = 972 • Good:select * from address where • state = ‘TX’ • union • select * from address where • areacode = 972

  35. SQL Guidelines Rule #5:Weigh JOIN versus EXISTS Sub-Query • use table JOIN instead of EXISTS sub-query • when the percentage of rows returned from the outer sub-query is high • select e.name, e.phone, e.mailstop • from employee e, department d • where e.deptno = d.deptno • and d.status = ‘ACTIVE’ • use EXISTS sub-query instead of table JOIN • when the percentage of rows returned from the outer sub-query is low • select e.name, e.phone, e.mailstop • from employee e • where e.deptno in (select d.deptno • from department d • where d.status != ‘ACTIVE’)

  36. SQL Guidelines Rule #6:Consider EXISTS in place of DISTINCT • avoid joins that use DISTINCT, use EXISTS sub-query instead • Bad: select distinct deptno, deptname from emp, dept where • emp.deptno = dept.deptno • Good:select deptno, deptname from dept where • exists (select ‘X’ from emp where • emp.deptno = dept.deptno) Note – only has to find one match

  37. SQL Guidelines Rule #7:Consider NOT EXISTS in place of NOT IN • avoid sub-queries that use NOT IN, use NOT EXISTS instead • Bad: select * from emp where • deptno not in (select deptno from dept where • deptstatus = ‘A’) • Good:select * from emp where • not exists (select ‘X’ from dept where • deptstatus = ‘A’ and • dept.deptno = emp.deptno) Note – only has to find one non-match

  38. SQL Guidelines Rule #8:COUNT Using Indexed Column or Asterisk • when counting rows, use COUNT on indexed column or asterisk • select count(indexed_column) from table [Most Efficient] • select count(*) from table • Select count(non_indexed_column) from table • select count(1) from table Note – rule based optimizer only

  39. SQL Guidelines Rule #9:Ordering Via the WHERE Clause • a dummy WHERE clause referencing an indexed column will • retrieve all records in ascending order (descending for 8i descending index) • not perform a costly sort operation • Bad:select * from address • order by city • Good:select * from address where • city > ‘’

  40. SQL Guidelines Rule #10:Use PL/SQL to reduce network traffic • Utilize PL/SQL to group related SQL commands and thereby reduce network traffic • Bad: • select city_name, state_code • into :v_city, :v_sate • from zip_codes where zip_code = ‘75022’; • insert into customer (‘Bert Scalzo’,’75022’, :v_city, v_state); • Good: • begin • select city_name, state_code • into :v_city, :v_sate • from zip_codes where zip_code = ‘75022’; • insert into customer (‘Bert Scalzo’,’75022’, :v_city, v_state); • end; • /

More Related