首页 Oracle 9i性能调整与优化培训_D34255-9i-PerformanceTuning-PPT

Oracle 9i性能调整与优化培训_D34255-9i-PerformanceTuning-PPT

举报
开通vip

Oracle 9i性能调整与优化培训_D34255-9i-PerformanceTuning-PPT Tuning Electronic Presentation D11299GC11 Production 1.1 December 2001 D34225 Oracle9i Database Performance Tuning Electronic Presentation Copyright © Oracle Corporation, 2001. All rights reserved. This documentation contains proprietary information ...

Oracle 9i性能调整与优化培训_D34255-9i-PerformanceTuning-PPT
Tuning Electronic Presentation D11299GC11 Production 1.1 December 2001 D34225 Oracle9i Database Performance Tuning Electronic Presentation Copyright © Oracle Corporation, 2001. All rights reserved. This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable: Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). This material or any portion of it may not be copied in any formor by any means without the express prior written permission of the Education Products group of Orac le Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties. If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in Data- General, including Alternate III (June 1987). The information in this document is subject to change without notice. If you find any problems in the documentation, please report them in writing to Worldwide Education Services, Oracle Corporation, 500Oracle Parkway, Box SB-6, Redwood Shores, CA 94065. Oracle Corporation does not warrant that this document is error-free. Oracle and all references to Oracle Products are trademarks or registered trademarks of Oracle Corporation. All other products or company names are used for identification purposes only, and may be trademarks of their respective owners. Authors Peter Kilpatrick Shankar Raman Jim Womack Technical Contributors and Reviewers Mirza Ahmad Harald Van Breederode Howard Bradley Howard Ostrow Alexander Hunold Joel Goodman John Watson Michele Cyran Pietro Colombo Ranbir Singh Ruth Baylis Sander Rekveld Tracy Stollberg Connie Dialeris Wayne Stokes Scott Gossett Sushil Kumar Benoit Dagerville David Austin Howard Bradley Howard Ostrow Janet Stern Lilian Hobbs Maria Senise Roderick Manalac Sander Rekveld Scott Gossett 1 Copyright © Oracle Corporation, 2001. All rights reserved. Overview of Oracle 9i Performance Tuning 1-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • List the roles associated with the database tuning process • Describe the dependencies between tuning in different development phases • Describe service level agreements • List the tuning goals • List the most common tuning problems • Describe tuning during development and production • Describe performance and safety tradeoffs 1-3 Copyright © Oracle Corporation, 2001. All rights reserved. Tuning Questions • Who tunes? – Application designers – Application developers – Database administrators – System administrators • Why tune? • How much tuning? 1-5 Copyright © Oracle Corporation, 2001. All rights reserved. Tuning Phases Tuning can be divided into different phases: • Application design and programming • Database configuration • Adding a new application • Ongoing Tuning 1-6 Copyright © Oracle Corporation, 2001. All rights reserved. Tuning Goals • Reducing or eliminating waits • Accessing the least number of blocks • Caching blocks in memory • Response time • Throughput • Load • Recovery time 1-7 Copyright © Oracle Corporation, 2001. All rights reserved. Examples of Measurable Tuning Goals • Fewer waits • Improved response time • Improved database availability • Improved memory utilization • Improved instance hit percentages 1-8 Copyright © Oracle Corporation, 2001. All rights reserved. Common Tuning Problems • Bad session management (usually related to middleware) • Bad cursor management (usually resulting from programmer error) • Bad relational designs (usually resulting from over normalization) 1-9 Copyright © Oracle Corporation, 2001. All rights reserved. Results of Common Tuning Problems • Bad session management: – Limits scalability to a point you cannot exceed – Makes the system one or two orders of magnitude slower than it should be • Bad cursor management makes scalability more limited • Bad relational design – Unnecessary table joins performed – Usually a result of trying to build an object interface of relational storage 1-10 Copyright © Oracle Corporation, 2001. All rights reserved. Proactive Tuning Considerations During Development • Tune the design. • Tune the application. • Tune memory. • Tune I/O. • Tune contention. • Tune the operating system. 1-11 Copyright © Oracle Corporation, 2001. All rights reserved. Tuning Steps During Production • Locate the bottleneck by using tools. • Determine the reason for the bottleneck. • Resolve the cause. • Check that the bottleneck has been resolved. 1-12 Copyright © Oracle Corporation, 2001. All rights reserved. Performance Versus Safety Trade-Offs Factors that affect performance: • Multiple control files • Multiple redo log members in a group • Frequent checkpointing • Backing up datafiles • Performing archiving • Block check numbers • Number of concurrent users and transactions 1-13 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned how to: • Create a good initial design • Define a tuning methodology • Perform production tuning • Establish quantifiable goals • List tuning problems • Decide between performance and safety 2 Copyright © Oracle Corporation, 2001. All rights reserved. Diagnostic and Tuning Tools 2-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Describe how the alert.log file is used • Describe how background trace files are used • Describe how user trace files are used • Describe the statistics kept in the dynamic performance views • Collect statistics using STATSPACK • Describe how STATSPACK collects statistics • Collect statistics using Enterprise Manager • Describe other tools used for tuning 2-3 Copyright © Oracle Corporation, 2001. All rights reserved. Maintenance of the alert.log File • The alert.log file consists of a chronological log of messages and errors. • Check the alert log file regularly to: – Detect internal errors (ORA-600) and block corruption errors – Monitor database operations – View the nondefault initialization parameters • Remove or trim the file regularly after checking. 2-4 Copyright © Oracle Corporation, 2001. All rights reserved. Tuning Using the alert.log File The alert.log file contains the following information which can be used in tuning the database: • Checkpoint start and end times • Incomplete checkpoints • Time to perform archiving • Instance recovery start and complete times • Deadlock, and timeout errors 2-5 Copyright © Oracle Corporation, 2001. All rights reserved. Background Processes Trace Files • The Oracle server dumps information about errors detected by any background process into trace files. • Oracle Support uses these trace files to diagnose and troubleshoot. • These files do not usually contain tuning information. 2-6 Copyright © Oracle Corporation, 2001. All rights reserved. User Trace Files • Server process tracing can be enabled or disabled at the session or instance level. • A user trace file contains statistics for traced SQL statements in that session. • User trace files are created on a per server process basis. • User trace files can also be created by: – Backup control file to trace – Database SET EVENTs 2-7 Copyright © Oracle Corporation, 2001. All rights reserved. Views, Utilities, and Tools Tools, and views, that are available to the DBA for determining performance: • V$xxx dynamic troubleshooting and performance views • DBA_xxx dictionary views • STATSPACK • utlbstat.sql and utlestat.sql scripts • Enterprise Manager • Oracle wait events • Oracle diagnostics and tuning packs 2-10 Copyright © Oracle Corporation, 2001. All rights reserved. Dictionary and Special Views The following dictionary and special views provide useful statistics after using the DBMS_STATS package: • DBA_TABLES, DBA_TAB_COLUMNS • DBA_CLUSTERS • DBA_INDEXES, INDEX_STATS • INDEX_HISTOGRAM, DBA_TAB_HISTOGRAMS This statistical information is static until you reexecute DBMS_STATS. 2-11 Copyright © Oracle Corporation, 2001. All rights reserved. Dynamic Troubleshooting and Performance Views V$ views: • Based on X$ tables • Listed in V$FIXED_TABLE X$ tables: • Not usually queried directly • Dynamic and constantly changing • Names abbreviated and obscure Populated at startup and cleared at shutdown 2-12 Copyright © Oracle Corporation, 2001. All rights reserved. Memory V$BUFFER_POOL_STATISTICS T/P V$DB_OBJECT_CACHE T V$LIBRARYCACHE P V$ROWCACHE P V$SYSSTAT T/P V$SGASTAT P Disk V$DATAFILE T/P V$FILESTAT T/P V$LOG T V$LOG_HISTORY T V$DBFILE T/P V$TEMPFILE P V$TEMPSTAT P Instance/Database V$DATABASE T V$INSTANCE T V$OPTION T V$PARAMETER T/P V$BACKUP T V$PX_PROCESS_SYSSTAT T/P V$PROCESS T V$WAITSTAT T/P V$SYSTEM_EVENT T/P Contention V$LOCK T/P V$ROLLNAME T/P V$ROLLSTAT T/P V$WAITSTAT T/P V$LATCH T/P User/Session V$LOCK P V$OPEN_CURSOR T V$PROCESS T V$SORT_USAGE T/P V$SESSION T/P V$SESSTAT T/P V$TRANSACTION T V$SESSION_EVENT T/P V$SESSION_WAIT T/P V$PX_SESSTAT P V$PX_SESSION P V$SESSION_OBJECT_CACHE P Topics for Troubleshooting and Tuning Systemwide Statistics Session-Related Statistics T: Troubleshooting T/P: Troubleshooting/Performance P: Performance 2-14 Copyright © Oracle Corporation, 2001. All rights reserved. Collecting Systemwide Statistics V$SYSSTAT • Statistic number • Name • Class • Value V$SGASTAT • Pool • Name • Bytes V$EVENT_NAME • Event number • Name • Parameter 1 • Parameter 2 • Parameter 3 V$SYSTEM_EVENT • Event • Total waits • Total timeouts • Time waited • Average wait 2-17 Copyright © Oracle Corporation, 2001. All rights reserved. Collecting Session-Related Statistics V$STATNAME • Statistic number • Name • Class V$SESSTAT • SID • Statistic number • Value V$SESSION_WAIT • SID • Sequence number • Event • Parameter 1/2/3 text • Wait time • Seconds in wait • State V$SESSION • SID • Serial number • Username • Os user V$SESSION_EVENT • SID • Event • Total waits • Total timeouts • Time waited • Average wait • Maximum wait V$EVENT_NAME • Event number • Name • Parameter 1 • Parameter 2 • Parameter 3 2-19 Copyright © Oracle Corporation, 2001. All rights reserved. Oracle Wait Events • A collection of wait events provides information on the sessions that had to wait or must wait for different reasons. • These events are listed in the V$EVENT_NAME view, which has the following columns: – EVENT# – NAME – PARAMETER1 – PARAMETER2 – PARAMETER3 2-20 Copyright © Oracle Corporation, 2001. All rights reserved. The V$EVENT_NAME View NAME PARAMETER1 PARAMETER2 PARAMETER3 ------------------------------- ---------- ---------- ---------- PL/SQL lock timer duration alter system set mts_dispatcher waited buffer busy waits file# block# id library cache pin handle addr pin address 0*mode+name log buffer space log file switch (checkpoint incomplete) transaction undo seg# wrap# count ... 286 rows selected. SQL> SELECT name, parameter1, parameter2, parameter3 2 FROM v$event_name; 2-21 Copyright © Oracle Corporation, 2001. All rights reserved. Statistics Event Views • V$SYSTEM_EVENT: Total waits for an event, all sessions together • V$SESSION_EVENT: Waits for an event for each session that had to wait • V$SESSION_WAIT: Waits for an event for current active sessions that are waiting 2-22 Copyright © Oracle Corporation, 2001. All rights reserved. The V$SYSTEM_EVENT View EVENT TOTAL_ TOTAL_ TIME_ AVERAGE_ WAITS TIMEOUTS WAITED WAIT ----------------- ------ -------- ------ ---------- latch free 5 5 5 1 pmon timer 932 535 254430 272.993562 process startup 3 8 2.66666667 buffer busy waits 12 0 5 5 ... 34 rows selected. SQL> SELECT event, total_waits, total_timeouts, 2 time_waited, average_wait 3 FROM v$system_event; 2-23 Copyright © Oracle Corporation, 2001. All rights reserved. The V$SESSION_EVENT View SID EVENT TOTAL_WAITS AVERAGE_WAIT ---- ------------------------------ ----------- ------------- 10 buffer busy waits 12 5 10 db file sequential read 129 0 10 file open 1 0 10 SQL*Net message to client 77 0 10 SQL*Net more data to client 2 0 10 SQL*Net message from client 76 0 SQL> select sid, event, total_waits,average_wait 2> from v$session_event where sid=10; 2-24 Copyright © Oracle Corporation, 2001. All rights reserved. The V$SESSION_WAIT View SID SEQ# EVENT WAIT STATE TIME ---- ------ --------------------------- ----- ------- 1 1284 pmon timer 0 WAITING 2 1697 rdbms ipc message 0 WAITING 3 183 rdbms ipc message 0 WAITING 4 4688 rdbms ipc message 0 WAITING 5 114 smon timer 0 WAITING 6 14 SQL*Net message from client -1 WAITED SHORT TIME SQL> SELECT sid, seq#, event, wait_time, state 2 FROM v$session_wait; 2-26 Copyright © Oracle Corporation, 2001. All rights reserved. STATSPACK • Installation of STATSPACK – $ORACLE_HOME/rdbms/admin/spcreate.sql • Collection of statistics – execute STATSPACK.snap • Automatic collection of statistics – $ORACLE_HOME/rdbms/admin/spauto.sql • Produce a report – $ORACLE_HOME/rdbms/admin/spreport.sql • To collect timing information, set TIMED_STATISTICS = true 2-28 Copyright © Oracle Corporation, 2001. All rights reserved. STATSPACK Output Information found on the first page: • Database and instance name • Time at which the snapshots were taken • Current sizes of the caches • Load profile • Efficiency percentages of the instance • Top five wait events 2-29 Copyright © Oracle Corporation, 2001. All rights reserved. STATSPACK Output Information found in the remainder of the document: • Complete list of wait events • Information on SQL statements currently in the pool • Instance activity statistics • Tablespace and file I/O • Buffer pool statistics 2-30 Copyright © Oracle Corporation, 2001. All rights reserved. STATSPACK Output Information found in the remainder of the document: • Rollback or undo segment statistics • Latch activity • Dictionary cache statistics • Library cache statistics • SGA statistics • Startup values for init.ora parameters 2-31 Copyright © Oracle Corporation, 2001. All rights reserved. UTLBSTAT and UTLESTAT Utilities These utilities: • Gather performance figures over a defined period • Produce a hard-copy report • To fully use these, set TIMED_STATISTICS to TRUE • Use the utlbstat.sql and utlestat.sql scripts • Run the scripts from SQL*Plus connected as SYSDBA • STATSPACK provides clearer statistics. 2-32 Copyright © Oracle Corporation, 2001. All rights reserved. Enterprise Manager Console 2-33 Copyright © Oracle Corporation, 2001. All rights reserved. Performance Manager 2-35 Copyright © Oracle Corporation, 2001. All rights reserved. Overview of Oracle Expert Tuning Methodology Specify tuning scope Collect data View and edit data and rules Analyze data Review recommendations Implement recommendations 2-36 Copyright © Oracle Corporation, 2001. All rights reserved. Tuning Using Oracle Expert 2-37 Copyright © Oracle Corporation, 2001. All rights reserved. Tuning Using Oracle Expert 2-38 Copyright © Oracle Corporation, 2001. All rights reserved. DBA-Developed Tools • Develop your own scripts. • Use the supplied packages for tuning. • Schedule periodic performance checking. • Take advantage of the EM Job service to automate the regular execution of administrative tasks. • Take advantage of the EM Event service to track specific situations. • Take advantage of the EM Job service to apply tasks that automatically solve problems detected by EM event service. 2-39 Copyright © Oracle Corporation, 2001. All rights reserved. Summary In this lesson, you should have learned how to: • Use the alert log file • Get information from background processes trace files • Trace user SQL statements • Collect statistics from dictionary and dynamic performance troubleshooting views • Use the STATSPACK utility to collect performance data • Retrieve wait events information 3 Copyright © Oracle Corporation, 2001. All rights reserved. Sizing the Shared Pool 3-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • Measure and tune the library cache hit ratio • Measure and tune the dictionary cache hit ratio • Size and pin objects in the shared pool • Tune the shared pool reserved space • Describe the user global area (UGA) and session memory considerations • List other tuning issues related to the shared pool • Set the large pool 3-3 Copyright © Oracle Corporation, 2001. All rights reserved. The System Global Area Major components of the shared pool are: • Library cache • Data dictionary cache • User global area (UGA) for shared server connections Database buffer cache Redo log buffer Shared pool Library cache Data dictionary cache User global area Large pool UGA 3-4 Copyright © Oracle Corporation, 2001. All rights reserved. The Shared Pool • Size is defined by SHARED_POOL_SIZE. • Library cache contains statement text, parsed code, and execution plan. • Data dictionary cache contains definitions for tables, columns, and privileges from the data dictionary tables. • UGA contains session information for Oracle Shared Server users when large pool is not configured. Shared pool Library cache Data dictionary cache UGA Shared pool Shared pool 3-5 Copyright © Oracle Corporation, 2001. All rights reserved. The Library Cache • Used to store SQL statements and PL/SQL blocks to be shared by users • Managed by a least recently used (LRU) algorithm • Used to prevent statements reparsing 3-6 Copyright © Oracle Corporation, 2001. All rights reserved. Shared SQL, PL/SQL areas The Library Cache Context area for SELECT statement 2 Context area for SELECT statement 1 SELECT statement 2 SELECT statement 1 SELECT statement 1 3-7 Copyright © Oracle Corporation, 2001. All rights reserved. Tuning the Library Cache Reduce misses by keeping parsing to a minimum: • Make sure that users can share statements • Prevent statements from being aged out by allocating enough space • Avoid invalidations that induce reparsing
本文档为【Oracle 9i性能调整与优化培训_D34255-9i-PerformanceTuning-PPT】,请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑, 图片更改请在作品中右键图片并更换,文字修改请直接点击文字进行修改,也可以新增和删除文档中的内容。
该文档来自用户分享,如有侵权行为请发邮件ishare@vip.sina.com联系网站客服,我们会及时删除。
[版权声明] 本站所有资料为用户分享产生,若发现您的权利被侵害,请联系客服邮件isharekefu@iask.cn,我们尽快处理。
本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用。
网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
下载需要: 免费 已有0 人下载
最新资料
资料动态
专题动态
is_752690
暂无简介~
格式:pdf
大小:8MB
软件:PDF阅读器
页数:0
分类:互联网
上传时间:2012-10-16
浏览量:13