Oracle Performance Tuning 04_Monitoring an Application
oracle数据库性能调优
oracle数据库性能调优⼀：注意WHERE⼦句中的连接顺序：ORACLE采⽤⾃下⽽上的顺序解析WHERE⼦句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最⼤数量记录的条件必须写在WHERE⼦句的末尾.尤其是“主键ID=？”这样的条件。
⼆： SELECT⼦句中避免使⽤ ‘ * ‘：ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个⼯作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
简单地讲，语句执⾏的时间越短越好（尤其对于系统的终端⽤户来说）。
⽽对于查询语句，由于全表扫描读取的数据多，尤其是对于⼤型表不仅查询速度慢，⽽且对磁盘IO造成⼤的压⼒，通常都要避免，⽽避免的⽅式通常是使⽤索引Index。
三：使⽤索引的优势与代价。
优势：1）索引是表的⼀个概念部分,⽤来提⾼检索数据的效率，ORACLE使⽤了⼀个复杂的⾃平衡B-tree结构. 通常,通过索引查询数据⽐全表扫描要快. 当ORACLE找出执⾏查询和Update语句的最佳路径时, ORACLE优化器将使⽤索引. 同样在联结多个表时使⽤索引也可以提⾼效率. 2）另⼀个使⽤索引的好处是,它提供了主键(primary key)的唯⼀性验证.。
那些LONG或LONG RAW数据类型, 你可以索引⼏乎所有的列. 通常, 在⼤型表中使⽤索引特别有效. 当然,你也会发现, 在扫描⼩表时,使⽤索引同样能提⾼效率.代价：虽然使⽤索引能得到查询效率的提⾼,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本⾝也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反⽽会使查询反应时间变慢.。
⽽且表越⼤，影响越严重。
使⽤索引需要注意的地⽅：1、避免在索引列上使⽤NOT ， 我们要避免在索引列上使⽤NOT, NOT会产⽣在和在索引列上使⽤函数相同的影响. 当ORACLE”遇到”NOT,他就会停⽌使⽤索引转⽽执⾏全表扫描.2、避免在索引列上使⽤计算．WHERE⼦句中，如果索引列是函数的⼀部分．优化器将不使⽤索引⽽使⽤全表扫描．举例:代码如下:低效:SELECT … FROM DEPT WHERE SAL * 12 > 25000;⾼效:SELECT … FROM DEPT WHERE SAL > 25000/12;3、避免在索引列上使⽤IS NULL和IS NOT NULL避免在索引中使⽤任何可以为空的列，ORACLE性能上将⽆法使⽤该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录. 如果⾄少有⼀个列不为空，则记录存在于索引中．举例: 如果唯⼀性索引建⽴在表的A列和B列上, 并且表中存在⼀条记录的A,B值为(123,null) , ORACLE将不接受下⼀条具有相同A,B值（123,null）的记录(插⼊). 然⽽如果所有的索引列都为空，ORACLE将认为整个键值为空⽽空不等于空. 因此你可以插⼊1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE⼦句中对索引列进⾏空值⽐较将使ORACLE停⽤该索引.代码如下:低效:(索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;⾼效:(索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;4、注意通配符%的影响使⽤通配符的情况下Oracle可能会停⽤该索引。
Oracle Database Workload Performance Measurement and Tuning Toolkit
Issues in Informing Science and Information Technology Volume 3, 2006Oracle Database Workload PerformanceMeasurement and Tuning ToolkitSai Peck Lee and Džemal Zildži ćFaculty of Computer Science and Information TechnologyUniversity of Malaya, Kuala Lumpur, Malaysia saipeck@.myAbstractDatabase tuning practice is mainly conducted as a consequence of user’s complaints on the per-formance. There is a need for a reactive monitoring and tuning tool enabling a real-time overview of the main resource consumers in order to detect and solve performance bottlenecks. With an assessment of the Oracle’s high-availability database, in terms of the main architectural compo-nents and their impact on performance, we have developed a Java tool for the efficient and re-source-effective tuning of Oracle databases. Our tool, called Workload Performance Monitoring and Tuning (WPMT), enables proactive and reactive database tuning. By combining today’s best monitoring and tuning practices with our metrics management, we have designed a unique ap-proach to illustrate the efficiency of the Oracle database memory segments responsible for han-dling the workload. This approach consists of developing database memory delta charts, illustrat-ing the efficiency of memory initialization parameters versus component’s workload perform-ance.Keywords : database management systems, tuning, workload performanceIntroductionRegardless of IT environment-specific requirements for running mission-critical database-driven applications, the two basic requirements are always requested: firstly, high availability, in terms of database accessibility and resource availability; and secondly, a high performance, in terms of access and code efficiency (Silberschatz, Korth & Sudarshan, 2002). These database management systems, i.e. the most demanding systems with 24x7 availability (24 hours a day, 7 days a week), require regular or proactive maintenance practice in order to achieve and maintain the optimal database workload performance. Although Oracle, IBM, and Microsoft provide a solution for a high availability (or multi-node) computing environment, availability, scalability, and manage-ability among them differ significantly (Buch & Cheevers, 2002; Chandrasekaran, & Kehoe, 2003; MSDN, 2004).Ultimately responsible for database tun-ing, the most inconvenient situation for a database administrator (DBA) is when users submit complaints about the data-base performance. Forced to act imme-diately (reactive tuning), for the DBA it is a critical task due to the fact that it often happens during the peak database activity. A common solution would be to optimize the SQL (Structured Query Material published as part of this publication, either on-line orin print, is copyrighted by the Informing Science Institute.Permission to make digital or paper copy of part or all of theseworks for personal or classroom use is granted without feeprovided that the copies are not made or distributed for profitor commercial advantage AND that copies 1) bear this noticein full and 2) give the full citation on the first page. It is per-missible to abstract these works so long as credit is given. Tocopy in all other cases or to republish or to post on a server orto redistribute to lists requires specific permission and paymentof a fee. Contact Publisher@ to requestredistribution permission.Oracle Database Workload Performance Measurement and Tuning ToolkitLanguage) statement’s Execution Plan that improves the execution performance. However, per-forming the object optimization during the peak database activity shall decrease the overall per-formance, since the optimization process consumes resources (CPU and RAM), and may even bring some objects temporary unavailable. Identifying the cause of performance overhead is not an easy task. For example, knowing that excessive disk I/O operations may bring down the data-base performance up to 2500 times (Burleson, 2003), a DBA must have a real-time overview of major I/O users, as well as the most expensive user’s SQL statements causing the slowdown. The root cause can be inaccurate SQL’s Execution Plan, or unbalanced memory allocation due to in-appropriate initialization of database, or because the database server has no more resources to handle the workload and requires the upgrade. In any case, the DBA must be able to assess the current database resource consumption and to proceed with investigation to discover the root of the problem and to solve the problem.Based on conducted on-line survey and an interview with Oracle Support Manager (Zildžić, 2005), we may conclude that database memory and index tuning practice needs an improvement. Since it is mainly conducted as a consequence of database performance overhead, i.e. when the user submits a complaint, a proactive monitoring of database performance should be implemented to anticipate performance leakage and ultimately avoid severe performance degradation.The above considerations led to a custom developed monitoring and tuning tool which delivers a unique solution for the efficient and resource-effective Oracle database workload performance measurement and tuning. This tool, called Workload Performance Monitoring and Tuning (WPMT), is developed in Java. It can proactively and reactively be used during Oracle database tuning in order to measure the current workload performance of the main database components, anticipate resource shortage, and optimize database objects for the achievement of the optimal database performance (Zildžić, 2005).Oracle’s Monitoring and Tuning ToolsEffective data collection and analysis is essential for identifying and correcting system perform-ance problems. Oracle provides tools, such as Statspack and Oracle Enterprise Manager (OEM) packs allowing a DBA to gather information on database performance (Metalink A87503-02, n.d.). These tuning tools, recommended by Oracle corporation and currently the most often used, use data dictionary views to capture database activity metrics. In this section, a part from Oracle Company designed tools, we shall assess the third-party tuning tool, i.e. the MGA’s EagleEye, developed by a recognized Oracle database expert Mark Gurry.Statspack UtilityStatspack utility consists of a set of Programming Language/Structured Query Language(PL/SQL) scripts, executed against the database, in order to gather, store data and metrics, and generates reports of the database activity. These metrics (Table 1), collected from dynamic per-formance tables, are automatically recorded in utility-created tables within a database with the DBA’s choice of collection and threshold level to be used (Metalink 228913.1, n.d.). Bundled with the Oracle Server, it is also known as Utlbstat/Utlestat and as of database release version 8.1.6, it is renamed as Statspack.372Lee & ZildžićTable 1: Statspack utility collection levelsCollection level* Description0 to 4 General performance metrics on all memory areas, latches, pools, andevents.5 to 9 Same metrics from the lower levels, plus the most resource-intensiveSQL statements.10 + Same metrics from the lower levels, plus parent/child latch data.*By default and recommended value of the collection level is 5, but it depends on the scopeof the tuning.Statspack tool has been designed by the Oracle Server architects, and it generates a set of reports based on user-defined collection level. With a higher collection level, metrics capturing consist-ing of almost all database performance activity related segments. Executing this utility has high costs in terms of space usage and query performance. For example, just for STATS$SYSSTAT table, Oracle will generate 255 rows for each snapshot, and there are 35 tables more. Whether required or not, the thousands of rows will be generated anyway with this utility. Managing his-torical metrics (dozen of snapshots) requires significant space allocation. The reports are gener-ated in text format only, and it is difficult to establish “chain” conclusion-analysis with dependent parameters. Report analysis is a time consuming task due to non self-intuitive and complex report presentation. Statspack is not supported with database releases earlier than 8.1.6, and storing data from multiple databases in one PERFSTAT user account is currently not supported.Oracle Enterprise Manager (OEM) toolsOracle Enterprise Manager (OEM) is Oracle's single, integrated solution for managing, adminis-tering and monitoring global enterprises. OEM management framework consisting of three tiers: The Console (client tier) and its integrated tools provide a graphical interface for administrators to manage the complete Oracle environment. Management Servers and a database repository pro-vide a scalable middle tier for processing system management tasks. Intelligent Agents are in-stalled on each network node (database) to monitor the services that node provides, and to exe-cute tasks from the Management Server (Metalink A96674-01, n.d.).Oracle offers several tools to administer the complete Oracle environment, including: databases, Internet Application Servers (iAS), applications, and services. For this research we have assessed two types of tools that help the DBA to tune and monitor Oracle database: Performance Tuning pack (Metalink A86647-01. n.d.) with Oracle Expert tool for automated database tuning, and Di-agnostics pack (Metalink A88748-02, n.d.) with a tool called Performance Manager that provides a set of graphical charts to monitor the performance of the database.Oracle Expert provides a methodology that is used to collect, evaluate, verify, and implement da-tabase tuning changes (Metalink 93490.1, n.d.). Tuning areas cover instance parameter tuning, database structure and placement, index tuning, and SQL statement reuse evaluation. During the tuning session, Oracle Expert collects metrics from dynamic performance views, processes this input through the various rules and algorithms, and creates recommendations, tuning scripts, and reports.Before creating a tuning session, Oracle establishes a current workload baseline (application SQL data and statistics or SQL History) of the database environment. Tuning session consists of five steps: defining the scope (SGA tuning, SQL reuse, storage management, data access – I/O), met-rics collection, metrics review, recommendations based on built-in rules, and generating the SQL scripts based on the recommendations.373Oracle Database Workload Performance Measurement and Tuning ToolkitObservation. Oracle Expert tool, with 900 tuning rules built-in, sub-applications SQL Analyze and Index Wizard, represents Oracle’s most enhanced graphical tool, especially with the release 9i. To host and manage collected metrics, this tool creates near 700 objects: 249 tables, 270 in-dexes, and 63 views. Algorithms for processing recommendations based on the collected metrics are high memory and CPU consumers. Using this tool with a repository installed on a production (main) database would not be an advantage due to its negative impact on the overall database per-formance.Performance Manager, available as a part of Oracle Diagnostic pack, Performance Management and Overview, provides a set of charts on the current state of the database. This tool is designed to give an immediate feedback on the state of all tuning areas. With an Overview option selected, this tool displays several charts simultaneously. Dynamic monitoring of the database state is en-hanced with user defined snapshot time interval, and various type of chart formats (Metalink 169551.1, n.d.).Observation. This tool provides a complete overview of Oracle database state, in the form of multiple-choice charts in HTML format. Also it can monitor Microsoft SQL server database. It is available for UNIX, and Windows platforms only. For the database release prior to 9i, tool is available only with OMS repository connection. With 9i release, without OMS only the current activity can be monitored. Collection and review of historical data is available with OMS reposi-tory connection exclusively. With a frequent refresh rate (snapshot time interval), the database has drawbacks in the overall performance due to increased activities (CPU and RAM consump-tion) necessary to calculate all performance metrics.MGA’s EagleEyeMGA EagleEye is a third-party software tool developed by Mark Gurry & Associates (MGA) that monitors the performance of Oracle databases and provides real-time diagnostic information to allow for immediate rectification of performance problems (/eagleeye.htm) (Gurry, 2001; Gurry & Corrigan, 1996). This tuning tool is reviewed by ‘SELECT’ magazine, Australian Oracle Users Group, and recommended for users experiencing extensive problems with performance and robustness on their mission critical database.Observation. The tool runs a set of SQL statements and records tuning information when ex-pected response times are exceeded, including details of logged on users, the applications and specific actions they are running, and where the contention is occurring. It also produces graphi-cal information on:•Shared Pool Problems•Analysis (and not rebuilt) of tables and indexes•Waits in tables and indexes•Sort efficiency•Disk I/O analysisEagle Eye is a product that has been developed to help DBAs to quickly identify where the per-formance problems are occurring and the cause of the problems. When used with the Mark Gurry’s books Oracle Performance Tuning and Oracle SQL Tuning, the tool enables the DBA to get performance problems under control. This tool does not provide a solution for recording his-torical metrics. It is designed to give an instant overview of database performance, and therefore is not suitable for a long-term proactive database maintenance. Also, it does not enable object optimization nor cache hit-ratio analysis.374Lee & ZildžićWorkload Performance Measurement and Tuning Workload Performance Measurement and Tuning (WPMT) is a custom developed monitoring and tuning tool enabling a DBA to perform reactive and proactive Oracle database tuning, with an efficient and resource-effective solution for managing the memory related historical resource and performance metrics. With an assessment of the high-availability Oracle database architectural components and their impact on workload performance, we have designed a tool with following features:•Database connections assessment. A feature required for the successful implementation of developed tuning strategy for administrating efficiently mission-critical Oracle data-base-driven applications.•Database workload assessment. Capturing and displaying in real-time the database users with the most demanding resource allocations, in terms of top memory and CPU consum-ers, as well as the most expensive SQL statements in terms of I/O executions.•Instance memory allocation assessment. In order to determine the efficiency of allocated memory to manage the workload, the current memory allocation breakdown, calculatedcache hit ratios using a ratio-methodology, memory sort ratio, and a redo log space re-quests, are calculated and displayed in real-time as well.•Bottleneck or Wait Event assessment. With wait-methodology, we have determined total instance session’s wait time and corresponding event description, required to identify the root causes of session’s hang-up.•Storage or space allocation assessment. In order to anticipate space shortage and elimi-nate unnecessary database performance overhead or downtime, we have developed a fea-ture to provide an overview of the currently free/used space. Furthermore, with calculated fragmentation index, the efficiency of the overall datafile free space has been determined and displayed.•Disk or I/O activities assessment. A real-time assessment feature detecting inappropriate or unbalanced throughput of physical storage allocation has been developed in terms ofhighest physical reads/writes per datafile.•RAC inter-node performance assessment. An overview of top wait events related to in-stance cache has been determined using RAC’s dictionary views. The efficiency of inter-instance intercommunication component has been assessed using average current blocktime metric.•Memory metrics management. Instead of recording metrics in database tables, and as a consequence additionally increase the database workload; we have developed a solutionthat uses a flat file for recording and reviewing current/historical metrics. Recordedmemory related performance metrics keep track of database performance achieved withhistorical initialization parameters, and enable a DBA to continuously measure the effi-ciency of allocated memory compared to the component’s workload performance (deltametrics).•Automated object optimization feature updates the database data dictionary with the cur-rent table and index statistics required by the Oracle’s Cost Based Optimizer to calculate the best SQL Execution Plan. With accurate table and index statistics, a database work-load performance will be improved.375Oracle Database Workload Performance Measurement and Tuning Toolkit376These features successfully integrated in a user-friendly Java application (WPMT tool), enable a DBA to continuously measure, resource-effectively and efficiently, the workload performance excellence of high-availability Oracle databases.WPMT Tool's TestingOur tool has been tested with three workload setting criteria: Low, Medium, and High, as de-scribed in Table 2. Transactions have been executed by each user independently and simultane-ously against a single CPU P-IV 1.8GHz/512 MB of RAM, on Personal edition Oracle 9i data-base. These transactions, containing a mixture of SQL commands such as INSERT, UPDATE, SELECT, and DELETE, are executed via sql scripts from Oracle SQL Plus editor.Table 2: Workload criteriaLow Medium HighUsers 2 5 13Transactions 12000 19500 43000Exec. Time 1h00 1h15 2h00A Low workload criteria is defined as two (2) users executing 12 000 transactions in one hour. A Medium workload criteria is defined as five (5) users, executing 19 500 transactions within an hour and fifteen minutes. A High workload criteria is defined as thirteen (13) users, executing 43 000 transactions within two (2) hours.Hit ratios at the beginning (start) and at the end (end) of the simulation, as well as adjusted ini-tialization parameters SHARED_POOL_AREA (SPA) and DB_CACHE_SIZE (CACHE), are illustrated in Tables 3, 4, 5 for Low, Medium, and High workload settings respectively.Low Workload results, as illustrated in Table 3, show that for the achievement of an optimal da-tabase performance in case of dictionary hit (95.8%) and a peak performance in case of library hit (100%), a size of shared pool area initialization parameter (SPA) had to be increased from 8 to 12 MB. Furthermore, there was no need to increase a buffer cache parameter size (CACHE) since almost a peak database performance (99.5%) has been achieved with an initial size of 4 MB (Fig-ure 1).Lee & Zildži ć377Medium Workload results, as illustrated in Table 4, show that bigger values for both initializa-tion parameters: shared pool area size (SPA) and buffer cache (CACHE), were required to achieve optimal/peak database workload performance. In case of buffer cache ratio a CACHE size was increased from 4 to 8 MB achieving 99.4 %; dictionary hit ratio achieving 95.8% with16 MB, and a peak performance for library hit ratio (99.9%) with 16 MB (Figure 2).High Workload results, as illustrated in Table 5, describe requirements, in terms of parameters size, for an achievement of optimal database workload performance. A 20 MB of memory allo-cated to shared pool area (SPA) were needed to achieve 95.7% and 99.6% hit ratios for dictionary and library respectively. A buffer cache size (CACHE) was increased from 4 to 8 MB in order to achieve 99% buffer hit ratio.As illustrated in Tables 3,4, and 5, and as demonstrated in Figure 1 and 2, we may conclude that our WPMT tool, can be used efficiently and resource-effectively to tune database initialization parameters in order to achieve optimal/peak workload performance regardless the current data-base workload.Figure 1: Buffer Hit Ratio – Low workloadFigure 2: Library/Dictionary Hit Ratio – Medium WorkloadOracle Database Workload Performance Measurement and Tuning ToolkitWPMT Tool's EvaluationComparison between Oracle tuning tools and custom developed WPMT tool has been described in Table 6.Table 6: Oracle database monitoring and tuning tools evaluationscripts(4.1)Enhanced with the Explain plan feature (trace the execution of the SQL in order to discover all objectsinvolved in the execution)(4.2)Limited to Shared Pool Area, and no cache-hit ratio analysis(5.1)Cache hit-ratio metrics and top initialization parametersOracle Statspack utility is an effective tool measuring the performance of each database com-ponent. To have an overview of metrics generated by this tool, the database must pay high costs in terms of resources, since to host the collected metrics, the utility will create additional schema in the production database. This utility maintains only the cumulative metrics, since all collected and recorded metrics are automatically deleted when the instance is restarted.The main disadvantage of this Oracle’s recommended tool for proactive maintenance, in our opinion, is an additional performance overhead created with an each utility’s snapshot that col-lects metrics from almost all database performance views (default and recommended is level 5). For example, if we want to apply Oracle’s recommended tuning methodology, i.e. one-by-one parameter, and to evaluate the effect of change on one component, Statspack’s snapshot will cap-ture metrics for other components anyway, thus increasing unnecessary database workload. Fur-thermore, the generated text file (report) is very complex to analyze and understand. For example, to evaluate the efficiency of hit ratios compared to dependent initialization parameters, the DBA must browse txt report from the beginning until the end since the information is provided on sepa-rate locations within a same report file.378Lee & ZildžićOracle Enterprise Manager (OEM) tools, i.e. tuning and diagnostic packs are effective tools but should be used jointly with a repository on a remote database (separately from production database) due to high cost in resources. Using these tools independently would not be an advan-tage since tuning pack provides only recommendations and not an overview of performance met-rics. Algorithms (rules) are hidden, and the DBA has no insight of how and why did Oracle server draw the conclusions and which parameter should be changed.A tuning pack in combination with diagnostic pack provides a complete monitoring and tuning mechanisms to measure overall and independently database component’s workload. For example, when Oracle Expert (tuning pack) recommends altering SHARED_POOL_SIZE parameters with optimal value, it will generate the script, and the DBA, after executing the script, may monitor the library and dictionary hit ratio through performance manager (diagnostic pack). Maintaining the OEM repository and tools high financial costs (new database on new server) are the main disad-vantages in our opinion.MGA’s EagleEye is an efficient tool to optimize SQL statements for the best Execution Plan. The Author’s book (Gurry, 2001) and EagleEye tool focus on database component that mainly measures SQL execution efficiency, i.e. Shared Pool Area. Although it is enhanced with storage and I/O metrics, the tool is quite limited with features for measuring overall database workload performance. User-friendly GUI, low cost in resources, and a quick overview of the performance is an advantage of this tool. In our opinion, it is suitable only for proactive database maintenance. With WPMT tool, we have combined the best practices to monitor and measure the overall data-base workload performance.These best practices are the recommendations from Oracle Support Services(/), combined with our 5 years expertise in maintaining the Oracle da-tabase. With a lean and portable Java user-friendly GUI, the tool has the lowest possible require-ments in terms of resources. It does not create any additional table in production or remote in-stance, and metrics are saved in RandomAccessed flat file on the local or remote machine. Fur-thermore, with the most important instance related initialization parameters, recorded together with related performance metrics, we enabled historical overview of delta metrics.Summary and ConclusionA custom developed Java application (WPMT v1.0) combines the best practices of the most common Oracle tuning tools. WPMT’s user-friendly GUI enables a DBA to efficiently perform proactive and reactive database tuning regardless of the current database workload.Our tool combines two approaches for gauging database workload performance hit-ratio and wait (or bottleneck) analysis. Measuring a workload performance with wait analysis enables the DBA to have an insight where a database is spending time, and with hit-ratio, the efficiency of instance memory allocation is instantly detected.Overcoming the limitations of measuring only the current (cumulative) metrics is achieved with a cost-effective and efficient solution, i.e. the flat-file metrics management. With this solution the burden of performance overhead created with a historical metrics is completely removed from a production database. Measurement with delta metrics and monitoring of memory allocation for each SGA component enables the DBA to efficiently readjust the memory for optimal database workload performance. Effective combination of the memory allocation with initialization pa-rameter and overall efficiency of the related component responsible to manage the current work-load is an advantage compared to all Oracle tools. In case of reported performance overhead (for example, low hit-ratios), based on available SGA memory, a DBA can reallocate additional mem-ory to area(s) with low performance (SPA or Buffer Cache).379Oracle Database Workload Performance Measurement and Tuning Toolkit380 AcknowledgementsWe thank Mr. Bazran Bin Md Bahe, Oracle Support Manager Malaysia, for providing the infor-mation regarding Oracle database implementation and maintenance.ReferencesBuch, V. & Cheevers, S. (2002). Database architecture: Federated vs. clustered. (Oracle white paper.) Online library: Burleson, K. D. (2003). Creating a self-tuning Oracle database. Kittrell, North Carolina, USA: Rampant TechPress.Chandrasekaran, S. & Kehoe, B. (2003). Technical comparison of Oracle real application clusters vs. IBM DB2 UDB ESE. (Oracle white paper.) Online library: Gurry, M. (2001). Oracle SQL tuning. O'Reilly & Associates.Gurry, M. & Corrigan, P. (1996). Oracle performance tuning, Database management systems (2nd ed.).O'Reilly & Associates.Metalink A87503-02. (n.d.).Oracle9i database performance guide and reference, release 1 (9.0.1). On-line: /docs/cd /A91202_01/ 901_doc/server.901/a87503/toc.htm Metalink 228913.1. (n.d.). Systemwide tuning using StatsPack reports. Oracle Support Services. On-line library: Metalink A96674-01. (n.d.). Oracle enterprise manager concepts guide release 9.2.0. On-line: /docs/html/A96674_01/toc.htmMetalink A86647-01. (n.d.). Oracle enterprise manager database tuning with the Oracle tuning pack re-lease 9.0.1. On-line library: http://download-/docs/cd/A91202_01/901_doc/em.901/a86647/tun_ovw.htm#1001025Metalink A88748-02. (n.d.). Oracle enterprise manager getting started with the Oracle diagnostics pack release 9.0.1. On-line: http://download-/docs/cd/A91202_01/901_doc/em.901/a88748/toc.htmMetalink 93490.1. (n.d.). How to use Oracle Expert. Oracle Support Services. On-line library: Metalink 169551.1. (n.d.). FAQ Oracle diagnostics pack 9.0.1. Oracle Support Services. On-line library: MSDN. (2004). Federated SQL Server 2000 Servers, MSDN. Microsoft library. On-line: /library/default.asp?url=/library/en-us/architec/8_ar_cs_4fw3.asp Silberschatz, A., Korth, H. F. & Sudarshan S. (2002). Database system concepts (4th ed.). McGraw – Hill. Zildžić, Dž. (2005). Workload performance measurement and tuning of high-availability Oracle databases.Masters Dissertation. University Malaya, Malaysia.。
Oracle 9i 数据库性能调优技术-les02
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:
2-21
Copyright © Oracle Corporation, 2002. All rights reserved.
Tuning Using Oracle Expert
2-14
Copyright © Oracle Corporation, 2002. All rights reserved.
Statspack
• • • • •
Installation of Statspack using the spcreate.sql script Collection of statistics execute statspack.snap Automatic collection of statistics using the spauto.sql script Produce a report using the spreport.sql script To collect timing information, set TIMED_STATISTICS = True
2-7
Copyright © ll rights reserved.
Oracle Enterprise Manager Console
2-9
红帽JBoss企业应用平台7.1性能优化指南说明书
Red Hat JBoss Enterprise ApplicationPlatform 7.1Performance Tuning GuideFor Use with Red Hat JBoss Enterprise Application Platform 7.1Last Updated: 2018-10-11Red Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideFor Use with Red Hat JBoss Enterprise Application Platform 7.1Legal NoticeCopyright © 2018 Red Hat, Inc.The text of and illustrations in this document are licensed by Red Hat under a Creative Commons Attribution–Share Alike 3.0 Unported license ("CC-BY-SA"). An explanation of CC-BY-SA is available at/licenses/by-sa/3.0/. In accordance with CC-BY-SA, if you distribute this document or an adaptation of it, you must provide the URL for the original version.Red Hat, as the licensor of this document, waives the right to enforce, and agrees not to assert, Section 4d of CC-BY-SA to the fullest extent permitted by applicable law.Red Hat, Red Hat Enterprise Linux, the Shadowman logo, JBoss, OpenShift, Fedora, the Infinity logo, and RHCE are trademarks of Red Hat, Inc., registered in the United States and other countries.Linux ® is the registered trademark of Linus Torvalds in the United States and other countries. Java ® is a registered trademark of Oracle and/or its affiliates.XFS ® is a trademark of Silicon Graphics International Corp. or its subsidiaries in the United States and/or other countries.MySQL ® is a registered trademark of MySQL AB in the United States, the European Union and other countries.Node.js ® is an official trademark of Joyent. Red Hat Software Collections is not formally related to or endorsed by the official Joyent Node.js open source or commercial project.The OpenStack ® Word Mark and OpenStack logo are either registered trademarks/service marks or trademarks/service marks of the OpenStack Foundation, in the United States and other countries and are used with the OpenStack Foundation's permission. We are not affiliated with, endorsed or sponsored by the OpenStack Foundation, or the OpenStack community.All other trademarks are the property of their respective owners.AbstractThis book is a guide of performance tuning for Red Hat JBoss Enterprise Application Platform 7.1.. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Table of Contents CHAPTER 1. INTRODUCTION 1.1. ABOUT THE USE OF EAP_HOME IN THIS DOCUMENT CHAPTER 2. MONITORING PERFORMANCE 2.1. CONFIGURING JBOSS EAP FOR REMOTE MONITORING CONNECTIONS2.2. JCONSOLE2.2.1. Connecting to a Local JBoss EAP JVM Using JConsole2.2.2. Connecting to a Remote JBoss EAP JVM Using JConsole2.3. JAVA VISUALVM2.3.1. Connecting to a Local JBoss EAP JVM Using VisualVM2.3.2. Connecting to a Remote JBoss EAP JVM Using VisualVM CHAPTER 3. DIAGNOSING PERFORMANCE ISSUES 3.1. ENABLING GARBAGE COLLECTION LOGGING 3.2. JAVA HEAP DUMPS 3.2.1. Creating a Heap Dump 3.2.1.1. OpenJDK and Oracle JDK 3.2.1.2. IBM JDK 3.2.2. Analyzing a Heap Dump 3.3. IDENTIFYING HIGH CPU UTILIZATION BY JAVA THREADS CHAPTER 4. JVM TUNING 4.1. SETTING A FIXED HEAP SIZE 4.2. CONFIGURING THE GARBAGE COLLECTORGarbage Collection Logging Options4.3. ENABLING LARGE PAGES4.4. ENABLING AGGRESSIVE OPTIMIZATIONS4.5. SETTING ULIMITS4.6. HOST CONTROLLER AND PROCESS CONTROLLER JVM TUNING CHAPTER5. EJB SUBSYSTEM TUNING 5.1. BEAN INSTANCE POOLS 5.1.1. Creating a Bean Instance Pool 5.1.2. Specifying the Instance Pool a Bean Should Use 5.1.3. Disabling the Default Bean Instance Pool 5.2. BEAN THREAD POOLS 5.2.1. Creating a Bean Thread Pool 5.2.2. Configuring EJB Services to Use a Specific Bean Thread Pool 5.3. EXCEPTIONS THAT INDICATE EJB SUBSYSTEM TUNING MIGHT BE REQUIRED CHAPTER6. DATASOURCE AND RESOURCE ADAPTER TUNING 6.1. MONITORING POOL STATISTICS 6.1.1. Datasource Statistics 6.1.1.1. Enabling Datasource Statistics Enable Datasource Statistics Using the Management CLI Enable Datasource Statistics Using the Management Console 6.1.1.2. Viewing Datasource StatisticsView Datasource Statistics Using the Management CLIView Datasource Statistics Using the Management Console6.1.2. Resource Adapter StatisticsEnable Resource Adapter StatisticsView Resource Adapter Statistics6.2. POOL ATTRIBUTES 4455778910111313131313141415161616161618181920202021212122222224242424242425252626262627Table of Contents1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6.3. CONFIGURING POOL ATTRIBUTES 6.3.1. Configuring Datasource Pool Attributes 6.3.2. Configuring Resource Adapter Pool Attributes CHAPTER 7. MESSAGING SUBSYSTEM TUNING CHAPTER 8. LOGGING SUBSYSTEM TUNING 8.1. DISABLING LOGGING TO THE CONSOLE8.2. CONFIGURING LOGGING LEVELS8.3. CONFIGURING THE LOCATION OF LOG FILES CHAPTER 9. UNDERTOW SUBSYSTEM TUNING 9.1. BUFFER CACHES 9.2. JSP CONFIGURATION9.3. LISTENERS CHAPTER 10. IO SUBSYSTEM TUNING 10.1. CONFIGURING WORKERS 10.1.1. Monitoring Worker Statistics10.2. CONFIGURING BUFFER POOLS CHAPTER 11. JGROUPS SUBSYSTEM TUNING 11.1. MONITORING JGROUPS STATISTICS11.2. NETWORKING AND JUMBO FRAMES11.3. MESSAGE BUNDLING11.4. JGROUPS THREAD POOLS11.5. JGROUPS SEND AND RECEIVE BUFFERS CHAPTER 12. TRANSACTIONS SUBSYSTEM TUNING APPENDIX A. REFERENCE MATERIAL A.1. DATASOURCE STATISTICS A.2. RESOURCE ADAPTER STATISTICS A.3. IO SUBSYSTEM ATTRIBUTES 282829303131313132323233353535353636373738383940404343Red Hat JBoss Enterprise Application Platform 7.1 Performance Tuning Guide2Table of Contents3Red Hat JBoss Enterprise Application Platform 7.1 Performance Tuning Guide4CHAPTER 2. MONITORING PERFORMANCEYou can monitor JBoss EAP performance using any tool that can examine JVMs running on your machine. Red Hat recommends that you use either JConsole, for which JBoss EAP includes a preconfigured wrapper script, or Java VisualVM. Both these tools provide basic monitoring of JVM processes, including memory usage, thread utilization, loaded classes, and other JVM metrics.If you will be running one of these tools locally on the same machine that JBoss EAP is running on, then no configuration is necessary. However, if you will be running one of these tools to monitor JBoss EAP running on a remote machine, then some configuration is required for JBoss EAP to accept remote JMX connections .2.1. CONFIGURING JBOSS EAP FOR REMOTE MONITORING CONNECTIONSFor a Standalone Server1. Ensure that you have created a management user. You might want to create a separatemanagement user to monitor your JBoss EAP server. See the JBoss EAP Configuration Guide for details.2. When starting JBoss EAP, bind the management interface to the IP address that you will use to remotely monitor the server:$ EAP_HOME /bin/standalone.sh -bmanagement=IP_ADDRESSWARNINGThis exposes all the JBoss EAP management interfaces, including themanagement console and management CLI, to the specified network.Ensure that you only bind the management interface to a private network.3. Use the following URI with your management user name and password in your JVM monitoring tool to connect to the JBoss EAP server. The URI below uses the default management port(9990).service:jmx:remote+http://IP_ADDRESS :9990For a Managed Domain HostUsing the above procedure of binding the management interface on a managed domain host will only expose the host controller JVM for remote monitoring, and not the individual JBoss EAP servers running on that host.To configure JBoss EAP to remotely monitor individual servers on a managed domain host, follow the procedure below.1. Create a new user in the ApplicationRealm that you will use to connect to the JBoss EAP servers for remote monitoring. See the JBoss EAP Configuration Guide for details. CHAPTER2. MONITORING PERFORMANCE5Red Hat JBoss Enterprise Application Platform 7.1 Performance Tuning Guide6CHAPTER 2. MONITORING PERFORMANCERed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 2. MONITORING PERFORMANCERed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 2. MONITORING PERFORMANCERed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 3. DIAGNOSING PERFORMANCE ISSUESRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 3. DIAGNOSING PERFORMANCE ISSUESRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 4. JVM TUNINGRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 4. JVM TUNINGRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 5. EJB SUBSYSTEM TUNINGRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 5. EJB SUBSYSTEM TUNINGRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 6. DATASOURCE AND RESOURCE ADAPTER TUNINGRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 6. DATASOURCE AND RESOURCE ADAPTER TUNINGRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 7. MESSAGING SUBSYSTEM TUNING Performance tuning advice for the messaging-activemq subsystem is covered in the Performance Tuning part of the JBoss EAP Configuring Messaging guide.CHAPTER 8. LOGGING SUBSYSTEM TUNINGCHAPTER 8. LOGGING SUBSYSTEM TUNINGYou can further improve upon JBoss EAP logging subsystem performance in production environmentsby disabling logging to console, configuring appropriate logging levels, and specifying the best location to store log files.For more information on configuring the logging subsystem, see the logging chapter in the JBoss EAP Configuration Guide.8.1. DISABLING LOGGING TO THE CONSOLEDisabling console logging can improve JBoss EAP performance. Although outputting logs to the console can be useful in development and testing environments, for production environments it is not necessaryin most cases. The JBoss EAP root logger includes a console log handler for all default standalone server profiles except standalone-full-ha. The default managed domain profiles do not include a console handler.To remove the default console handler from the root logger, use the following management CLI command./subsystem=logging/root-logger=ROOT:remove-handler(name=CONSOLE)8.2. CONFIGURING LOGGING LEVELSFor ideal performance, you must configure the logging levels for your production environment appropriately. For example, although INFO or DEBUG levels might be appropriate for development or testing environments, in most cases you should set your production environment logging level to something higher, such as WARN or ERROR.For details on setting log levels for different logging handlers, see Configuring Log Handlers in the JBoss EAP Configuration Guide.8.3. CONFIGURING THE LOCATION OF LOG FILESYou should consider the storage location of log files as a potential performance issue. If you save logs to a file system or disk configuration that has poor I/O throughput, it has the potential to affect the whole platform’s performance.To prevent logging from impacting JBoss EAP performance, it is recommended that you set log locations to high-performance dedicated disks that have a lot of space.For details on configuring log file locations for different logging handlers, see Configuring Log Handlers in the JBoss EAP Configuration Guide.Red Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 9. UNDERTOW SUBSYSTEM TUNINGThe non-blocking I/O undertow subsystem introduced in JBoss EAP 7 has greatly improved performance compared to the previous web subsystem in JBoss EAP 6. Opportunities for tuning the undertow subsystem for your environment include configuring buffer caches, JSP settings, and listeners.9.1. BUFFER CACHESA buffer cache is used to cache static files handled by the undertow subsystem. This includes images, static HTML, CSS, and JavaScript files. You can specify a default buffer cache for each Undertow servlet container. Having an optimized buffer cache for your servlet container can improve Undertow performance for serving static files.Buffers in a buffer cache are allocated in regions, and are of a fixed size. There are three configurable attributes for each buffer cache:buffer-sizeThe size of an individual buffer, in bytes. The default is 1024 bytes. Red Hat recommends that you set the buffer size to entirely store your largest static file.buffers-per-regionThe number of buffers per region. The default is 1024.max-regionsThe maximum number of regions, which sets a maximum amount of memory allocated to the buffer cache. The default is 10 regions.You can calculate the maximum amount memory used by a buffer cache by multiplying the buffer size, the number of buffers per region, and the maximum number of regions. For example, the default buffer cache is 1024 bytes * 1024 buffers per region * 10 regions = 10MB.Configure your buffer caches based on the size of your static files, and the results from testing expected loads in a development environment. When determining the effect on performance, consider the balance of the buffer cache performance benefit versus the memory used.For instructions on using the management CLI to configure buffer caches, see Configuring Buffer Caches in the JBoss EAP Configuration Guide.9.2. JSP CONFIGURATIONThere are JSP configuration options for Undertow servlet containers that provide optimizations for how JSP pages are compiled into Java bytecode.generate-strings-as-char-arraysIf your JSPs contain a lot of String constants, enabling this option optimizes scriplets by converting the String constants to char arrays.optimize-scriptletsIf your JSPs contain many String concatenations, enabling this option optimizes scriplets byremoving String concatenation for every JSP request.trim-spacesIf your JSPs contain a lot of white space, enabling this option trims the white space from HTTPrequests and reduces HTTP request payload.CHAPTER 9. UNDERTOW SUBSYSTEM TUNINGRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 10. IO SUBSYSTEM TUNINGRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 11. JGROUPS SUBSYSTEM TUNINGRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideCHAPTER 12. TRANSACTIONS SUBSYSTEM TUNINGRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideAPPENDIX A. REFERENCE MATERIALA.1. DATASOURCE STATISTICSTable A.1. Core Pool StatisticsActiveCount The number of active connections. Each of the connections iseither in use by an application or available in the pool.AvailableCount The number of available connections in the pool.AverageBlockingTime The average time spent blocking on obtaining an exclusive lockon the pool. This value is in milliseconds.AverageCreationTime The average time spent creating a connection. This value is inmilliseconds.AverageGetTime The average time spent obtaining a connection.AveragePoolTime The average time that a connection spent in the pool.AverageUsageTime The average time spent using a connection.BlockingFailureCount The number of failures trying to obtain a connection.CreatedCount The number of connections created.DestroyedCount The number of connections destroyed.IdleCount The number of connections that are currently idle.InUseCount The number of connections currently in use.MaxCreationTime The maximum time it took to create a connection. This value isin milliseconds.MaxGetTime The maximum time for obtaining a connection.MaxPoolTime The maximum time for a connection in the pool.MaxUsageTime The maximum time using a connection.MaxUsedCount The maximum number of connections used.MaxWaitCount The maximum number of requests waiting for a connection at thesame time.APPENDIX A. REFERENCE MATERIAL MaxWaitTime The maximum time spent waiting for an exclusive lock on thepool.TimedOut The number of timed out connections.TotalBlockingTime The total time spent waiting for an exclusive lock on the pool.This value is in milliseconds.TotalCreationTime The total time spent creating connections. This value is inmilliseconds.TotalGetTime The total time spent obtaining connections.TotalPoolTime The total time spent by connections in the pool. TotalUsageTime The total time spent using connections.WaitCount The number of requests that had to wait to obtain a connection. XACommitAverageTime The average time for an XAResource commit invocation. XACommitCount The number of XAResource commit invocations. XACommitMaxTime The maximum time for an XAResource commit invocation. XACommitTotalTime The total time for all XAResource commit invocations. XAEndAverageTime The average time for an XAResource end invocation. XAEndCount The number of XAResource end invocations.XAEndMaxTime The maximum time for an XAResource end invocation. XAEndTotalTime The total time for all XAResource end invocations. XAForgetAverageTime The average time for an XAResource forget invocation. XAForgetCount The number of XAResource forget invocations. XAForgetMaxTime The maximum time for an XAResource forget invocation. XAForgetTotalTime The total time for all XAResource forget invocations. XAPrepareAverageTime The average time for an XAResource prepare invocation.Red Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideXAPrepareCount The number of XAResource prepare invocations.XAPrepareMaxTime The maximum time for an XAResource prepare invocation.XAPrepareTotalTime The total time for all XAResource prepare invocations.XARecoverAverageTime The average time for an XAResource recover invocation.XARecoverCount The number of XAResource recover invocations.XARecoverMaxTime The maximum time for an XAResource recover invocation.XARecoverTotalTime The total time for all XAResource recover invocations.XARollbackAverageTime The average time for an XAResource rollback invocation.XARollbackCount The number of XAResource rollback invocations.XARollbackMaxTime The maximum time for an XAResource rollback invocation.XARollbackTotalTime The total time for all XAResource rollback invocations.XAStartAverageTime The average time for an XAResource start invocation.XAStartCount The number of XAResource start invocations.XAStartMaxTime The maximum time for an XAResource start invocation.XAStartTotalTime The total time for all XAResource start invocations.Table A.2. JDBC StatisticsPreparedStatementCacheAccessCount The number of times that the statement cache was accessed.PreparedStatementCacheAddCount The number of statements added to the statement cache.PreparedStatementCacheCurrentSize The number of prepared and callable statements currentlycached in the statement cache.PreparedStatementCacheDeleteCount The number of statements discarded from the cache.PreparedStatementCacheHitCount The number of times that statements from the cache were used.APPENDIX A. REFERENCE MATERIAL PreparedStatementCacheMissCount The number of times that a statement request could not besatisfied with a statement from the cache.A.2. RESOURCE ADAPTER STATISTICSTable A.3. Resource Adapter StatisticsActiveCount The number of active connections. Each of the connections is either in use by anapplication or available in the poolAvailableCount The number of available connections in the pool.AverageBlockingTime The average time spent blocking on obtaining an exclusive lock on the pool. Thevalue is in milliseconds.AverageCreationTime The average time spent creating a connection. The value is in milliseconds. CreatedCount The number of connections created.DestroyedCount The number of connections destroyed.InUseCount The number of connections currently in use.MaxCreationTime The maximum time it took to create a connection. The value is in milliseconds. MaxUsedCount The maximum number of connections used.MaxWaitCount The maximum number of requests waiting for a connection at the same time. MaxWaitTime The maximum time spent waiting for an exclusive lock on the pool.TimedOut The number of timed out connections.TotalBlockingTime The total time spent waiting for an exclusive lock on the pool. The value is inmilliseconds.TotalCreationTime The total time spent creating connections. The value is in milliseconds.WaitCount The number of requests that had to wait for a connection.A.3. IO SUBSYSTEM ATTRIBUTESRed Hat JBoss Enterprise Application Platform 7.1 Performance Tuning GuideAPPENDIX A. REFERENCE MATERIAL。
Oracle Support Services 教程说明书
CON8293
Best Practices for Supporting Oracle Exadata
CON8253
Best Practices for Troubleshooting System and Performance Issues with PeopleSoft
Best Practices for Supporting and Maintaining Oracle Database
You chose Oracle Business Analytics to help your organization deliver superior results. Learn how to take advantage of your software with the great tools, resources, and product updates you’re entitled to through Oracle Support. In this session, Oracle product experts provide proven best practices to help you work more efficiently, plan and prepare for upgrades more effectively, and manage risk. Topics include My Oracle Support best practices, remote diagnostic tools, the My Oracle Support Community, and Lifecycle Advisors. New users and experts alike will leave with fresh ideas and practical, easy-to-implement next steps for successfully supporting Oracle Hyperion enterprise performance management and business intelligence solutions. [Add session to your conference agenda]
oracle性能调优汇总
性能调优---------------------方法：一、检查日志是否有错误二、检查参数是否设置正确三、检查系统IO、CPU、内存等利用率，查看哪些占用较高四、检查哪些SQL语句大量占用IO或CPU权衡性能与安全的方面：一、多个控制文件二、一个日志文件组中有多个日志文件三、频繁的执行检查点四、备份数据文件五、频繁的归档六、块检查七、同时执行操作与统计性能调优工具：查看ALTER.LOG中的信息：1、用/ORA- 能查找文件中的错误信息2、要想让ALTER.LOG文件中产生检查点开始与结束信息，得修改参数LOG_CHECKPOINTS_TO_ALERT，将之修改为TRUE。
默认为FALSE。
SQL>ALTER SYSTEM SET LOG_CHECKPOINTS_TO_ALERT = TRUE SCOPE=BOTH;在用户UDUMP目录下的文件中产生监控用户执行信息：方法1、监控自己的操作。
在自己的session中设置参数，这样就能在用户udump目录下生成执行的SQL语句信息SQL>ALTER SESSION SET SQL_TRACE=TRUE;在pl/sql或者sqlplus中，打开一个sql_window。
（1）先运行：alter session set sql_trace=true;（2）再运行你那个返回结果不正确的SQL（3）再运行：alter session set sql_trace=false;（4）马上登陆到机器上，到$ORACLE_BASE/admin/sid/udump目录下。
（5）找到刚生成的.trc文件(假设文件名是 xxx.trc)，执行命令转储跟踪文件：tkprof xxx.trc aa.txt。
查看aa.txt文件。
这个文件里面有执行计划。
看看执行计划每一步返回的结果集记录数是不是正确。
方法2、SYS用户监控别的用户执行情况方法3、对所有SESSION进行监控SQL>ALTER SESSION SET SQL_TRACE=TURE;安装STATSPACK一、创建表空间，最少100M二、安装在安装时默认创建了一个用户PERFSTAT，所以密码也可以输入这个选择要将快照存放于哪个表空间使用STATSPACK手动执行生成一次快照，间隔一般为10几分钟为益，中间不要有停机。
Oracleg数据库性能调优方法
减少存储过程的调用次数 通过减少存储过程的调用次数， 可以减少数据库的负载。
优化存储过程的逻辑 通过优化存储过程的逻辑，可以 提高存储过程的执行效率。
调优技术：分区表与分区索引
分区表
将一个大表分成多个小表，可以提高查询效 率。
分区索引
为分区表创建分区索引，可以加快查询速度 。
合理分区
根据实际情况，将数据按照时间、地域等因 素进行分区，可以提高查询效率。
• 考虑使用分布式文件系统和集群解决方案，提高存储性能和可靠性。
优化策略：硬件配置与软件设置
根据业务需求和数据量，选择合适的数据库管理系统和 版本。
考虑使用多实例和分片解决方案，提高系统可用性和扩 展性。
软件设置 调整数据库参数和配置，优化内存使用和磁盘IO。
优化策略：数据库结构与数据设计
数据库结Байду номын сангаас 设计合理的表结构，减少数据冗余和重复。
oracleg数据库性能调优方 法
2023-11-09
目录
• 数据库性能问题与影响 • 数据库性能优化目标与策略 • 数据库性能调优技术与方法 • 数据库性能监控与分析工具 • 数据库性能优化实践与案例
01
数据库性能问题与影响
性能问题的定义与表现
性能问题定义
数据库性能问题是指系统无法在规定时间内完成用户请求， 导致响应延迟、系统卡顿、甚至崩溃。
在OEM中，DBA可以通过图形界面查看数 据库性能数据、执行SQL语句、管理数据库
对象等操作。同时，OEM还提供了告警功 能，能够及时发现数据库性能问题并发送警
报。
05
数据库性能优化实践与案 例
某银行数据库优化案例
硬件配置优化
01
DBA笔记 tun
Chapter1Overview of Oracle9i Database Performance TuningTuning goalsminimizing response timeincreasing throughputincreasing load capabilitiesdecreasing recovery timeTuning steps during development1design2application3memory4input/output(I/O)5contention6opersting systemChapter2Diagnostic and Tuning Toolsv$sysstat(statistic#,name,class,value)v$sgastat(pool,name,bytes)v$event_name(event number,name,parameter1,parameter2,parameter3)v$system_event(event,total_waits,total_timeouts,time_waited,average_wait)SQL>select name,class,value from v$sysstat;SQL>select*from v$sgastat;Displaying session-related statisticsv$statname(statistic#,name,class)v$sesstat(sid,statistic#,value)v$session(sid,serial#,username,osuser)v$session_event(sid,event,total_waits,total_timeouts,time_waited,average_wait,max_wait)v$event_name(event#,name,parameter1,parameter2,parameter3)v$session_wait(sid,seq#,event,ps/2/3,p1/2/3text,p1/2/3raw,wait time,seconds_in_wait,state)SQL>select sid,username,type,server from v$session;SQL>select username,name,value from v$statname n,v$session s,v$sesstat t where s.sid=t.sid and n.statistic#=t.statistic#and s.type='user' and ername is not null and ='session pga memory'and t.value>30000;SQL>select sid,event from v$session_wait where wait_time=0;SQL>select name,parameter1,parameter2,parameter3from v$event_name;SQL>select sid,event,total_Waits,average_wait from v$session_event where sid=10;SQL>select sid,seq#,event,wait_time,state from v$session_wait;wait_time{>0(the session's last wait time);=0(the session is currently waiting)=-1(the value was less than1/100of a second)=-2(the system cannot provide timing information)}SQL>select event,total_waits,total_timeouts,time_Waited,average_wait from v$system_event order by time_waited desc; Troubleshooting and tuning viewsInstance/Databasev$databasev$instancev$optionv$parameterv$backupv$px_process_sysstat:parallel query system statisticsv$process:information about currently active processv$waitstat:connection statisticsv$system_event:total waits for particular eventsDiskv$datafilev$filestat:data file read/write statisticsv$logv$log_historyv$dbfilev$tempfilev$tempstat:information about file read/write statistics for temporary tablespace data filesv$segment_statistics:offers statistics on I/O segmentConnectionv$lockv$rollnamev$rollstat:statistics for all online rollback segmentsv$waitstat:block contention statistics(the timed_statistics parameter should be set to true)v$latch:statistics for each types of latchMemoryv$buffer_pool_statistics:buffer pools allocation on the instancev$db_object_cache:database objects cached in the library cachev$librarycache:library cache performance and activity statisticsv$rowcache:data dictionary hits and misses activityv$sysstat:basic instance statisticsv$sgastatUser/Sessionv$lock:locks currently held by the server and outstanding requests for a lock or latchv$open_cursor:cursors currently opened and parsed by each sessionv$processv$transactionv$px_sesstat:information about the session executing SQL statements in parallelv$px_sessionv$sesstat:user session statisticsv$session_event:information on waits for an event by a sessionv$sort_usage:seze of temporary segment and sessions creating them;identification of process doing disk sortsv$session_waitv$sessionv$session_object_cachetestSQL>show parameter timed_statisticsSQL>altere system set timed_statistics=true;SQL>alter session set SQL_TRACE=true;SQL>select count(*)from dba_tables;SQL>alter session set SQL_TRACE=false;$cd$HOME/admin/udump$ls-l$sqlplus hr/hr$sqlplus sys/oracle as sysdbaSQL>select username,sid,serial#from v$session where username='hr';SQL>begindbms_system.set_sql_trace_in_session(&SID,&SERIALNUM,true);end;/SQL>select*from employees;SQL>begindbms_system.set_sql_trace_in_session(&SID,&SERIALNUM,false);end;/$cd$HOME/admin/udump$ls-lSQL>connect sys/oracle as sysdbaSQL>create tablespace tools datafile'$HOME/oradata/u05/tools.dbf'size100M extent management dictionary;SQL>select tablespace_name,sum(types)from dba_free_space where tablespace_name='tools'group by tablespace_name; SQL>@$HOME/student/labs/spcreate.sqlSQL>@$HOME/student/labs/snap.sqlSQL>@$HOME/student/labs/spauto.sqlSQL>select job,next_date,next_sec,last_sec from user_jobs;SQL>select sanp_id,to_char(startup_time,'dd Mon"at"HH24:mi:ss')instart_fm,to_char(snap_time,'dd Mon YYYY HH24:mi') snapdat,snap_level"level"from stats$snapshot order by snap_id;SQL>@$HOME/student/labs/spreport.sql$vi sp_x_y.1stwhere x is the starting snapshot,and y is the ending snapshot(this is true if the default report filename was used)SQL>connect sys/oracle as sysdbaSQL>select event,total_waits,time_waited from v$system_event;SQL>select sid,event,p1text,wait_time,state from v$session_wait;SQL>connect perfstat/perfstatSQL>select job,log_user from user_jobs;SQL>execute dbms_job.remove($job);SQL>alter session set sql_trace=true;SQL>select sid,serial#,username from v$session;SQL>execute dbms_system.set_sql_trace_insession(16.167.true);statspack install先阅读/u010g/oracle/rdbms/admin/spdoc.txt这文档SQL>create tablespace perfstat datafile'/u01/oradata/pitts/perfstat_01.dbf'size512M;SQL>@?/rdbms/admin/spcreate.sqlSQL>execute statspack.snap;SQL>execute statspack.snap;SQL>@?/rdbms/admin/spreport********************************************************************************德哥statspack讲解SQL>drop user perfstat cascade;SQL>drop tablespace perfstat including contents and datafiles;SQL>create tablespace perfstat datafile'/oradata/clonedb/perfsata01.dbf'size500M autoextend on;SQL>@?/rdbms/admin/spcreate.sql如过运行出错SQL>@?/rdbms/admin/spdrop.sql然后在安装SQL>@?/rdbms/admin/spcreate.sql查看相关文档cat spcusr.listSQL>connect perfstat/perfstatSQL>execute statspack.snap;SQL>select*from stats$statspack_parameter;SQL>select*from stats$snapshot;然后在执行一次SQL>execute statspack.snap;vi spauto.sqlvariable jobno number;variable instno number;beginselect instance_number into:instno from v$instance;dbms_job.submit(:jobno,'statspack.snap;',trunc(sysdate+1/24,'HH'),'trunc(SYSDATE+1/24,''HH'')',TRUE,:instno);commit;end;/SQL>execute dbms_job.interval(62,'sysdate+1/(24*60)');SQL>select*from user_jobs;report1、instance reportspreport.sqlsprepins.sql(multi-instance)2、sql reportsprepsql.sqlsprsqins.sql(multi-instance)vi$HOME/rdbms/admin/sprepcon.sqldefine num_days='';SQL>@?/rdbms/admin/spreport.sql(生成报告)SQL>@?/rdbms/admin/sprepins.sqlSQL>@?/rdbms/admin/sprepsql.sqlConfigure1database parameters2snapshot parameters3instance report parameters4sql report parametersSQL>desc stats$statspack_parameter;SQL>select SNAP_LEVEL from stats$statspack_parameter;SQL>execute statspack.snap<i_snap_level=>7>;SQL>execute statspack.snap<i_snap_level=>7,i_modify_parameter=>'true'>;使用包更改SQL>execute statspack.modify_statspack_parameter<i_snap_level=>6>;SQL>select SNAP_LEVEL from stats$statspack_parameter;停止脚步执行SQL>execute dbms_job.remove(62);SQL>select*from user_jobs;maintenanceMake_baseline&clear_baselinePurge snapshot except baselineTruncate all tables with perfstat schemaDrop perfstat schema;SQL>define i_begin_snap=1SQL>define i_end_snap=16SQL>execute statspacl.make_baseline(i_begin_snap=>1,i_end_snap=>16);SQL>execute statspack.purge(i_begin_snap=>1,i_end_snap=>73);SQL>execute statspace.clear_baseline(i_begin_snap=>1,i_end_snap=>16,i_snap_range=>false);SQL>execute statspack.purge(i_begin_snap=>1,i_end_snap=>16);SQL>select snap_id from stats$snapshot;********************************************************************************Chapter3Database Configuration and I/O IssuesUsing the v$filestat viewSQL>select phyrds,phywrts, from v$datafile d,v$filestat f where d.file#=f.file#order by ;I/O statisticsSQL>select d.tablespace_name tablespace,d.file_name,f.phyrds,f.phywrts from v$filestat f,dba_data_files d where f.file#=d.file_id; Table Scan statisticsSQL>select name,value from v$sysstat where name like'table scan%';Determine the progress of long operations usingSQL>select sid,serial#,opname,to_char(start_time,'HH24:MI:SS')as"start",(sofar/totalwork)*100as percent_complete from v$session_longops;Use set_session_longops to populate v$session_longopsdbms_application_info.set_session_longops(rindex,slno,"Operation X",obj,0,sofar,totalwork,"table","tables");Monitoring full table scan operationsdeclarerindex binary_integer;slno binary_integer;totalwork number;sofar number;obj binary_integer;beginrindex:=dbms_application_info.set_session_longops_nohint;sofar:=0;totalwork:=10;while sofar<10loop--update obj based on sofar and perform task on object targetsofar:=sofar+1;dbms_application_info.set_session_longops(rindex,slno,"Operation X",obj,0,sofar,totalwork,"table","tables");end loop;end;DB_FILE_MULTINLOCK_READ_COUNT:是指oracle一次IO读出的最大块数，同时也受操作系统限制如：oracle一次IO读64K，但是，操作系统只能读32K，这样就受到限制。
使用Oracle Enterprise Manager 诊断、优化数据库
© 2008 Oracle 版权所有第0页<Insert Picture Here>使用Oracle Enterprise Manager 诊 使用 断、优化数据库阮蓉，高级咨询顾问 2008、2© 2008 Oracle 版权所有第1页Agenda• OEM Overwiew • Database Diagnostic Pack • Database Tuning Pack • Demonstration • Q&A© 2008 Oracle 版权所有第2页<Insert Picture Here>Oracle Enterprise Manager 10gOverview© 2008 Oracle 版权所有第3页企业网格管理器(OEM) 企业网格管理器主机与硬件 数据库 Oracle AS网络与负载均衡器管理 监控 诊断 配置企业管理器应用程序存储器© 2008 Oracle 版权所有第4页Oracle Enterprise Manager全面的管理功能 全面的管理功能Oracle Grid Control Custom and Packaged Apps Middleware and App Servers Reports Database Oracle Database Oracle Apps - Fusion, OCSBusiness Flows Custom AppsOracle Application ServerWebLogic TuxedoManagement Console CMDB RepositoryHostHostStorage, Network, Hardware© 2008 Oracle 版权所有第5页Oracle Enterprise Manager 10g Release 4• BEAApplications• IBM • Juniper • Microsoft • Nortel • Onaro • Pillar Data • Radware • Jboss •Websphere - MQ Series• Configuration Management • Service Level ManagementMiddleware• BEZ • Blue Lane • Citrix • Dell • Egenera• Check Point • NetApp• Application Performance Mgmt • Lifecycle Management • DashboardsDatabase• EMC • F5Enterprise Linux• Data Masking •Application Server Diagnostics • Non-Oracle + AD4J Middleware + AD4J •Tomcat •Business Intelligence •VMWareNew•PeopleSoft HelpDesk Connector •Siebel HelpDesk Connector •Microsoft Exchange© 2008 Oracle 版权所有第6页OEM Grid Control 拓扑TargetsHTTP/SManage from AnywhereE-Biz Host AS …….Oracle Management ServicesHTML ConsoleServers Database Storage AS Network OCS Software E-BizDBAgentHTTP/SEnterprise Config Management Precision System MonitoringApplication Perf Management AdministrationE-Biz Host AS DB …….Mobile deviceInfrastructure (Jobs, Alerts, and so on)AgentJDBC HTTP/S HTTP/SE-Biz Host AS DB …….AgentPortalsFirewallOracle Management Repository© 2008 Oracle 版权所有 第7页Oracle10g Enterprise ManagerGrid Control Console HTTP(S) Grid Control Management ServerFirewallThin JDBCManagement RepositoryFirewallManaged Targets Agent HTTP(S) Agent HTTP(S) Agent HTTP(S) AgentHTTP(S) 10g Database Oracle 10g Control Console Database ServerHTTP(S) Oracle 8i/9i OS/ Third-Party Oracle 10g Database Application Application Server Server© 2008 Oracle 版权所有10g Application Server Control Console第8页Enterprise Manager 10g 产品家族Enterprise Manager 10g Grid Control• Oracle’s grid-ready framework, allowing IT professionals to manage the entire Oracle eco-system through one integrated management console.Enterprise Manager 10g Database Control• Database Control is the front-end tool for managing a single instance of the 10g database or single cluster instance.Enterprise Manager 10g Application Server Control• Oracle’s application server management interface built directly into the Oracle 10g application server infrastructureEnterprise Manager 10g Management Packs Enterprise Manager 10g Management Plug-ins Enterprise Manager 10g Management Connectors© 2008 Oracle 版权所有 第9页Database ManagementGrid Control vs. Database ControlOne per 10g database home One per host in separate home Enterprise Manager agent In local 10g database CentralizedEnterprise Manager repository Yes Yes EM job systemDatabase 10g Separate InstallationLocal system Enterprise-wide Configuration management NoYesMobile access (EM2Go)NoYesStandby database (Data Guard)Yes Yes Cluster database (RAC)One Many # of targets Single 10g database Many Target types Database Control Grid Control FeatureOracle Grid Control 的功能•日常管理日常管理：：如数据库的备份管理如数据库的备份管理、、安全管理安全管理、、对象管理对象管理、、空间管理空间管理、、灾难备份等•日常监控日常监控：：对数据库对数据库、、应用服务器应用服务器、、存储和IO 、空间使用、应用性能应用性能、、网络设备网络设备、、主机的运行状态进行监控主机的运行状态进行监控，，及时发现故障和异常等发现故障和异常等，，并通知系统管理人员•数据库性能优化数据库性能优化：：捕获需要优化的SQL 语句语句，，进行自动优化•软件分发软件分发：：包括克隆软件包括克隆软件、、安装补丁等•报表报表：：对系统的运行状况提供自定义报表•其它OEM Management Packs•Oracle OEM不仅给数据库管理员提供了友好的，容易操作的图形化管理界面，而且通过一系列不同功能的管理包(Packs)简化了系统管理、降低了管理成本，将IT人员从日常繁琐、复杂、重复的管理任务中解放出来，把更多的时间和精力投入到和业务相关的企业数据管理中去，同时在业务系统出现异常出现之前主动进行故障隔离并处理，提高业务用户对IT服务的满意度。
703. TIPTOP数据库管理(Oracle) - OraclePerformanceTuning_cant_touch_code
Buffer Cache Advisory V$DB_CACHE_ADVICE
ALTER SYSTEM SET DB_CACHE_ADVICE = ON ;
( OFF | READY | ON )
Cache Size (MB) 30 60 91 ….. 273 304 334 364 ….. 547 577 608
363 wait events in Oracle 9iR2 buffer busy waits db file scattered read db file sequential read enqueue ...
Hit Ratio statistics
V$SYSSTAT V$WAITSTAT ...
OPT-3 IVAN Information Technology Company
Many Application Vendors forbid customers to touch code
• • • • • • •
SAP R/3 Oracle EBS Seibel PeopleSoft Enterprise JD Edward EnterpriseOne Baen ….
SELECT name, block_size, 100*(1 - (physical_reads / (db_block_gets+consistent_gets))) buffhit FROM v$buffer_pool_statistics;
•
– > 0.90 & < 0.97 Library cache hit ratio
IVAN Information Technology Company
•
•