1 / 37

Oracle Tuning Accelerator

Oracle Tuning Accelerator. David Scott Intec Billing Georgia Oracle Users’ Group president@gouser.org. Tuning Metaphors. Body / Doctor Auto / Mechanic. The Right Solution. Fixes the immediate problem Prevents a recurring problem Fixes the underlying problem

Lucy
Download Presentation

Oracle Tuning Accelerator

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. Oracle Tuning Accelerator David ScottIntec Billing Georgia Oracle Users’ Group president@gouser.org

  2. Tuning Metaphors • Body / Doctor • Auto / Mechanic

  3. The Right Solution • Fixes the immediate problem • Prevents a recurring problem • Fixes the underlying problem • Is identified and implemented quickly • Is cost-effective • Is properly documented • Has measurable ROI

  4. The Main ThingIs To KeepThe Main ThingThe Main Thing

  5. “It’s Slow!” - Triage • Compared to what? What is “Normal”? • Do you have benchmarks? • What has changed in the environment? • And when? • Are you processing the right data? • Which part of the system is slow? • most expensive / most used queries • Concentrate effort on items with most business impact.

  6. Basic Approach • Define the business problem • Measure the pain • Identify the problem component • Find the root cause • Investigate and weigh solutions • Fix the problem • Measure the results

  7. Case Study #1 p1 • Define the BUSINESS problem • The GUI is too slow; a loss in productivity $$$ • Measure the pain • Screen XYZ in the GUI takes 47 seconds • 3000 users use this screen at each login • Most other screens are fine • Identify the problem component • Stopwatch timing of XYZ response is 47 seconds • Same SQL query takes 47 seconds • Query identified as culprit

  8. Case Study #1 p2 • Find the root cause • Inefficient query • Investigate and weigh solutions • Multiple versions of query compared • Results must match original • Fix the problem • Replace query in Screen XYZ with tuned version • Measure the results • Query returns in 2 seconds • ROI: 3000 users * 45 seconds saved * Avg salary @ $10/hour * 260 business days/year = • $97,500

  9. Case Study #2 p1 • Define the BUSINESS problem • Processing 40M rows exceeds operational window • Measure the pain • Users cannot begin sessions until 10-11 AM • Cost = 2-3 hours overtime for 2 people per day • Identify the problem component • Main processing routine takes > 4 hours/day

  10. Case Study #2 p2 • Find the root cause • UPDATE of records in main table is not appropriate for increased workload (maximum 1200 rows/second) • Investigate and weigh solutions • Used tracing, tkprof, Explain Plan • Fix the problem • Rewrite using partitioned staging tables and INSERT /*+APPEND */ • Measure the results • Average run is < 2 hours (average 3600 rows/second) • Processing completes before users arrive

  11. Where’s the Wait? Database Processing Network ClientApplication CPU Disk

  12. Finding Out “What’s Wrong” • Guessing… (from experience, of course) • Old wive’s tales and “Rules of Thumb” • Trial and Error • Statspack (AWR in 10g) • Using the V$ Tables • Cache Hit and other ratios • Top SQL (cost, # executions, logical/physical I/O) • Explain Plan • Are indexes always “good”? Are they used? • Profiling and Tracing • Autotrace, Event 10046 • Wait Events

  13. Investigative Arsenal • SQL*Plus • Docs • Performance Tuning Guide and Reference • Wait event reference • Database Reference • Init.ora parameters, V$ view listings • Tkprof • TOAD, OEM, etc (optional) • OS tools: vi, iostat, sar, etc.

  14. Fixing “What’s Wrong” • Change the database configuration: init.ora • Statistics • Make better choices, I/O speed is unchanged • “Lock down” plans via Stored Outlines • Rewrite • Parallel operations • Hints • More efficient plans • Re-architect / Rebuild • You must know what will perform! – Study it.

  15. Pitfalls • Risk: Wasting time chasing a hunch • Problem: fixing before finding out! • jumping to conclusions • Problem: “It can’t hurt…” • Yes, it can. • Problem: Multiple changes at once • Which one ‘fixed’ it? • Side effects (What else did you break?)

  16. Which Approach, When? • Massive problems, entire computer • OS tools, Configuration parameters • Entire database is slow, no clues • Statspack, V$ views • This query or batch is slow • Autotrace, V$, Tracing, Wait Events

  17. OS Tools • Basic observation of resources and processes • top • sar • iostat • ps –ef • ipcs

  18. Configuration Parameters • Use AUTO parameters where possible • Override only when needed • Have a good reason • Remove deprecated parameters • Check COMPATIBLE

  19. Parameter Resources • Oracle Docs • Database Reference Guide • The usual websites… • otn.oracle.com • asktom.oracle.com • Metalink • Oracle Initialization Parameters Pocket Reference by David C. Kreines, O’Reilly Press

  20. Statspack • What is it? • How to install it? • How to gather data? • What is reported? • How to report on the data?

  21. Statspack: What is it? • System-wide stats and ratios • Based on bstat / estat • Point-in-time snapshots • Automated via DBMS_JOBS • Many options and settings

  22. Statspack: Installation • Login as a sysdba • @?/rdbms/admin/spcreate.sql • Follow prompts for user, pw, tablespaces • Set TIMED_STATISTICS=TRUE • Set JOB_QUEUE_PROCESSES=1 or more • Total setup time: about 2 minutes! • AWR installed by default in 10g

  23. Statspack: Gather Data • Manual • EXEC STATSPACK.SNAP; • Better:SQL> var snap number;SQL> exec :snap := statspack.snap;SQL> print snap • Automated (every hour on the hour!) • 9i and before: @?/rdbms/admin/spauto.sql • Automatic is on by default in 10g • Schedule to capture your workload!

  24. Statspack Content • Level >= 0 • General Performance Statistics • Level >= 5 • SQL Statement Stats • Level >= 6 • SQL Plans and Plan Usage • Level >= 7 • Segment Level Stats • Level >= 10 • Parent and Child Latches

  25. Statspack: Reports • Full Report: spreport.sql • STATS$SNAPSHOT for snapshot info • SQL Report: sprepsql.sql • STATS$SQL_SUMMARY for hash info • 10g • Full Report: awrrpt.sql • SQL Report: awrsqrpt.sql • Use OEM – very nice!

  26. Autotrace • Quick plan explanation & basic statistics • To setup, see http://osi.oracle.com/~tkyte/ article1/autotrace.html • Settings • SET AUTOTRACE ON EXPLAIN • SET AUTOTRACE ON STATISTICS • SET AUTOTRACE ON • SET AUTOTRACE TRACEONLY

  27. Explain Plan • explain plan set statement_id = ‘whatever’for <your SQL here>; • select * from table(dbms_xplan.display); • Are the plans what you expect? • Full table scans are not always ‘bad’ • See Database Performance Tuning Guide and Reference

  28. SQL Trace • Started in Oracle7 • Cryptic but useful information • Files written to UDUMP directory • Level 1 = Equal to SQL_TRACE=TRUE. • Level 4 = SQL_TRACE & bind variables. • Level 8 = SQL_TRACE & wait events. • Level 12 = Combines levels 4 & 8.

  29. Wait Events • Identify where time is spent waiting for work • Viewable in V$SESSION_WAIT • join to V$SESSION • Written to trace file via event 10046 • alter session set events '10046 trace name context forever, level 12'; • alter session set events '10046 trace name context off' ; • exec sys.dbms_system.set_sql_trace_in_session (sid, serial#,TRUE | FALSE);

  30. How to Use Wait Events • Modify the process to set the event • Run the process (or part of it) • Find the trace file • Use tkprof to summarize the trace file • Find the significant statements • Identify the waits • Modify the code and/or environment

  31. Wait Event Resources • Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning by Shee, Deshpande, and Gopalkrishnan • Buy it NOW! • Docs: Performance Tuning Guide • The usual web sites…

  32. Using Tkprof • tkprof inputfile.trc outputfile • Many sorting options • I don’t bother… some folks do. • To get help, type ‘tkprof’ w/o parameters • Avoid tkprof explain=user/pw • Gives current explain plan, not actual plan • Then just vi outputfile

  33. Tkprof Resources • Expert Oracle One-On-One by Tom Kyte • Database Performance Tuning Guide and Reference, Chapter 10 • Every performance book ever written…

  34. 10g Changes Everything (Almost) • ADDM – Automatic Database Diagnostic Monitor • Statspack, V$, and ‘OEM’ rolled into 1 place • AWR is the version 10 Statspace equivalent • OEM has been expanded • Easy to see while in use OR historical! • Stored Snapshot Sets are great

  35. Are You Finished? • CTD – Compulsive Tuning Disorder • Document the solution • Calculate the ROI

  36. Questions?

  37. ORA-03113

More Related