1 / 35

Oracle9 i Performance Tuning

Oracle9 i Performance Tuning. Chapter 1 Performance Tuning Overview. Chapter Objectives. Learn general concepts of database tuning Review the System Development Life Cycle Identify tuning goals and measurements Describe and understand performance problems. Chapter Objectives (continued).

patrick-boyer
Download Presentation

Oracle9 i Performance Tuning

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. Oracle9iPerformance Tuning Chapter 1 Performance Tuning Overview

  2. Chapter Objectives • Learn general concepts of database tuning • Review the System Development Life Cycle • Identify tuning goals and measurements • Describe and understand performance problems Chapter 1: Performance Tuning Overview

  3. Chapter Objectives (continued) • Learn the steps of the tuning process • Use a database tuning checklist • Understand the roles and responsibilities of the database administrator (DBA) • Learn about Oracle tuning tools Chapter 1: Performance Tuning Overview

  4. Database Tuning Overview • Database tuning is the ongoing process of optimizing the operation of a database configuration based on attainable tuning goals • There are no quick formulas or specific methods for tuning a database • It depends on the “art” of developing good designs and creativity in troubleshooting and analyzing problems as well as the “science” of using technical tools to detect problems before they occur Chapter 1: Performance Tuning Overview

  5. System Development Methodologies • System Development Life Cycle • Structured analysis and design • Object-oriented analysis and design • Rapid application development • Prototyping development • Joint application development Chapter 1: Performance Tuning Overview

  6. System Development Life Cycle • Referred to as the SDLC • Purpose of the SDLC: • Establishes a consistent framework for development • Provides a solution to solve a problem • Delivers the application design and code that has been reviewed and signed off by technical leads Chapter 1: Performance Tuning Overview

  7. System Development Life Cycle (continued) • Short-cutting the SDLC leads to: • Inferior system design • Poor system architecture • Inefficient application code • Performance problems • Lower user productivity Chapter 1: Performance Tuning Overview

  8. System Development Life Cycle (continued) Chapter 1: Performance Tuning Overview

  9. DBA Tasks • A DBA must be involved in all phases of the SDLC • DBA tasks for each of the database phases: • Database analysis phase: • Identify new resources and capacities that are required for the new system • Analyze the business functions of the new system to determine database features and functions • Identify database performance goals and objectives Chapter 1: Performance Tuning Overview

  10. DBA Tasks (continued) • Logical design phase: • Identify the type of database application • Gather data from existing system and business requirements • Analyze data and identify all entities • Identify relationships between entities • Create a conceptual data model • Identify degrees of relationships and cardinalities Chapter 1: Performance Tuning Overview

  11. DBA Tasks (continued) • Logical design phase: • Identify all attributes for each entity • Create a logical data model • Identify domains for each attribute • Create a data dictionary • Identify data constraints and validation techniques • Normalize data model Chapter 1: Performance Tuning Overview

  12. DBA Tasks (continued) • Physical design phase: • Decide which database management system will be employed • Examine the features of the database management system to be used • Create a storage capacity plan • Analyze initial data and growth • Design database procedures to implement business rules • Identify data migration procedures • Identify and create backup and recovery strategies Chapter 1: Performance Tuning Overview

  13. DBA Tasks (continued) • Database implementation phase: • Create the database by using a template configuration • Create application users and roles and grant privileges • Create application schema • Create and code all database procedures and scripts • Create data migration procedures and scripts • Create necessary data feeds and/or loads • Document database configuration • Implement a backup strategy for testing Chapter 1: Performance Tuning Overview

  14. DBA Tasks (continued) • Database testing phase: • Test database connections • Identify problematic queries • Gather database statistics and analyze results to determine whether performance goals are met • Test all database procedures and scripts • Identify database configuration problems • Document database maintenance procedures and administration tasks • Test backup and recovery strategies Chapter 1: Performance Tuning Overview

  15. DBA Tasks (continued) • Database maintenance phase: • Create a production database • Migrate data to the production database • Use tools to monitor the database and install scripts • Tune and improve database performance as required • Verify backups and perform random recovery testing Chapter 1: Performance Tuning Overview

  16. Tuning Goals • Performance-tuning goals must be measured by the following criteria: • Database response time • Database availability • Database statistics and ratios • CPU utilization • Memory utilization • Disk thrashing or paging • Excessive I/O (caused by reads and writes) Chapter 1: Performance Tuning Overview

  17. Tuning Goals (continued) Chapter 1: Performance Tuning Overview

  18. Performance Problems • Performance problems are classified as follows: • Contention problems • Resource consumption problems • Scalability problems • Architectural problems • Application problems • Network problems • Sorting problems Chapter 1: Performance Tuning Overview

  19. Performance Problems (continued) • Performance problems are classified as follows: • Connection problems • Allocation problems • Full Table Scan (FTS) problems • Parsing problems • Index problems • Storage parameters problems Chapter 1: Performance Tuning Overview

  20. Tuning Processes and Steps Chapter 1: Performance Tuning Overview

  21. Degree of Tuning Effort Chapter 1: Performance Tuning Overview

  22. Database Tuning Checklist Chapter 1: Performance Tuning Overview

  23. DBA Roles and Responsibilities Chapter 1: Performance Tuning Overview

  24. DBA Roles and Responsibilities (continued) • The DBA should be focused on the following activities: • Be involved in all SDLC phases • Recommend best practices in data modeling and database designs • Educate developers on how to optimize their code (queries) • Work with designers and developers on transaction controls Chapter 1: Performance Tuning Overview

  25. DBA Roles and Responsibilities (continued) • The DBA should be focused on the following activities: • Recommend to developers SQL tuning tools • Monitor database performance • Collect and analyze database statistics • Educate users on best practices for working with database applications Chapter 1: Performance Tuning Overview

  26. DBA Roles and Responsibilities (continued) Chapter 1: Performance Tuning Overview

  27. DBA Roles and Responsibilities (continued) Chapter 1: Performance Tuning Overview

  28. DBA Roles and Responsibilities (continued) Chapter 1: Performance Tuning Overview

  29. DBA Roles and Responsibilities (continued) Chapter 1: Performance Tuning Overview

  30. Oracle Tuning Tools and Utilities • Performance Views (V$ Views) • Data dictionary views • Oracle Optimizer • Oracle Optimizer Hints • Explain Plan Statement • SQL Trace Chapter 1: Performance Tuning Overview

  31. Oracle Tuning Tools and Utilities (continued) • Autotrace settings • Tkprof • Outlines • UTLBSTAT.SQL and UTLESTAT.SQL • Statspack package Chapter 1: Performance Tuning Overview

  32. Oracle Manager Tools • Oracle Enterprise Management Console • Diagnostic Pack: • Lock Monitor • Performance Manager • Performance Overview • Top Sessions • Top SQL • Trace Data Viewer Chapter 1: Performance Tuning Overview

  33. Oracle Manager Tools (continued) • Tuning Pack • Oracle Expert • Outline Management • SQL Analyze • Tablespace Map Chapter 1: Performance Tuning Overview

  34. Summary • The System Development Life Cycle provides a consistent methodology for solving business problems • A DBA should be involved in most SDLC phases • Performance tuning is one of the most challenging tasks of a DBA • DBAs should focus their efforts on database design and code • Most performance problems stem from bad design and inefficient code Chapter 1: Performance Tuning Overview

  35. Summary (continued) • DBAs should implement a mechanism to collect statistics and analyze these statistics to determine performance trends • The Oracle9i product provides DBAs with a number of tools that assist with performance tuning • Knowing who should perform tuning tasks and what should be tuned is essential for allocating resources to performance problems Chapter 1: Performance Tuning Overview

More Related