1 / 17

Performance Tuning Methods

Performance Tuning Methods. Reactive Tuning at its Best. Author: Vladimir Andreev Semantec GmbH Lector: Stoyan Ivanov Semantec Bulgaria OOD. Semantec GmbH Benzstr. 32 D-71083 Herrenberg, Germany www.semantec.de Semantec Bulgaria OOD Hubcha Str. 8 1618 Sofia, Bulgaria. About us.

feoras
Download Presentation

Performance Tuning Methods

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. Performance Tuning Methods Reactive Tuning at its Best Author: Vladimir Andreev Semantec GmbH Lector: Stoyan Ivanov Semantec Bulgaria OOD Semantec GmbH Benzstr. 32 D-71083 Herrenberg, Germany www.semantec.de Semantec Bulgaria OOD Hubcha Str. 8 1618 Sofia, Bulgaria

  2. About us • More than ten years of Oracle-related experience • Application design and development • Database administration • Performance tuning • Performance analysis

  3. Developers and DBAs • Developers: • Control the design and implementation • Work in a sandbox • No danger • Low data volumes • Low concurrency • Different platform • Incomplete system architecture • Work on a schedule

  4. Developers and DBAs • DBAs: • Have no control over the design and implementation • Deal with production systems • Controlled changes • High availability requirements • Real user and data loads • Have tasks with higher priority than performance • Availability • Maintenance • End user support

  5. Proactive Is planned Low time pressure Any methods and tools are allowed No scope or target Reactive Cannot be planned High time pressure Methods constrained by live data and packaged application Scope limited to a single (group of) problems A target always exists Tuning Modes

  6. The Plan for Success • Must be simple and straightforward • Must lead to fast solutions • Must cover all possible cases

  7. The Plan for Success • Reproduce the problem • Define the target • Find the bottleneck component • If the bottleneck happens to be in the database, use response time analysis to find the reason

  8. Things to do first • Reproduce the problem • Trust the users for • Effects of the problem • Do not trust the users for • Reasons for the problem • Candidate solutions • Scale down, but carefully • Define the target • Keep baselines

  9. Find the bottleneck component • Tier Walk • Start from the end-user perspective • Try to decompose the Application Response Time into time spent at all individual tiers • Concentrate on the tier where most time is spent • Inside Out • Server_Idle_Time = User_Think_Time + Client_Processing_Time • User Think Time can be ignored: • When tuning batch jobs without user interaction • By limiting the observation period to exclude user interaction

  10. Tier Walk

  11. Inside Out • Benefits • Client applications are generally not instrumented • The Oracle Database Server has an excellent set of statistics: • Complete • Standardised • Fairly well documented • Tools • V$ Views (and StatsPack) • V$Session (Logon_Time) • V$Session_Event (Event, Time_Waited) • V$Session_Wait (Seconds_In_Wait) • V$SesStat (‘CPU used by this session’) • STATS$Idle_Event (Event) • SQL_Trace • Event 10046, Level 12 • tkprof, version 9.0 and above

  12. Response Time Analysis • Basics • Response Time = Service Time + Wait Time • Service Time = CPU time • Wait Time = Idle Time + Non-Idle Time • Non-Idle Time = I/O Waits + Other Waits • System-Level • Aggregated statistics prevent accurate analysis in multi-session environment • Maybe useful for overall performance analysis, but not for reactive tuning • Session-Level • Reflect a linear (non-parallel) process • Allow the tuner to ignore the other processing taking place in the server

  13. OK, it is the database:CPU • CPU-Bound • Logical reads • Inefficient SQL • Inefficient Application Design • (Re-)Parsing • SQL with literals • Shared Pool Size • Invalidation • PL/SQL • Deterministic functions • Tools: DBMS_Profiler

  14. OK, it is the database:Disk I/O • I/O-Bound • Inefficient SQL + inadequate buffer sizes • Table and index scans • Disk sorts • Hash Joins • Inefficient Application Design: heavy client (or PL/SQL) processing • Hot disks

  15. Network I/O

  16. OK, it is the database:Contention • Contention • Latches and locks • Shared pool • Library cache • Cache buffer chains • Hot blocks • Segment headers (freelists) • Rollback segment headers • Leading block of an index

  17. Want to know more? Company: Names: Address: Semantec GmbH., Semantec Bulgaria OOD Vladimir Andreev, Stoyan Ivanov Hubcha Str. 8 1618 Sofia, Bulgaria Telephone: Telephone: Fax: E-Mail: Internet: +359(2)9603911 +359(2)9603921 +359(2)9603946 andreev@semantec.de Stoyan.Ivanov@semantec.bg www.semantec.de

More Related