Use of performance analysis tools

Use of the Performance planner

The Performance Planner (Capacity Planner) is a tool that is integrated in Oracle Enterprise Management Packs (Oracle Enterprise Management packs) to collect parameters that reflect system performance, specifying the data to be collected, The frequency at which data is collected and the time that data is loaded into the Oracle Capacity Planner history database. This facilitates the administrator to compare and analyze the system performance parameters in a certain time range.
1. Settings for the Performance planner
(1) Select "Start"/"Oracle-orahome90"/"Enterprise Management Packs"/"diagnostics"/"Capacity Planner" option on the server's desktop. The Performance Planner login interface, as shown in Figure 13.1, is logged on with the user name and password of the login Management Server.

(2) The collection Network navigation tree of the performance planner shown in Figure 13.2 appears, selecting the database myoracle.mynet to collect performance data, and the Collection Options tab interface appears.
The settings for "collection scope" include two options.
"Oralce Recommended Collection": Historical performance data can be analyzed to support performance diagnostics and reporting.
User-defined collection: Historical performance data can be analyzed, but performance diagnostics and reporting are not supported.
There are two options for collecting sampling frequency.
"Sampling per Interval": The default is to sample 4 times per hour.
Sample interval: Sets the time interval for the sampling.
(3) as shown in Figure 13.3, the collection/Storage Options tab interface of the Performance planner, the collected performance data is formed into a set of database tables that can be stored in any Oracle database. You can choose to store the data tables that were created when you installed Oracle Management Server (to hold state information for the managed environment) or in a database that was made by your administrator. The parameters that are set include.
Choose to store the history in the data archive or in the specified database.
Sets the interval at which data is transferred to the database at historical times.

(4) as shown in Figure 13.4, the collection/Purge Options tab interface for the Performance planner, which specifies the time interval for saving data and the data before a specific time is automatically cleared at the next sampling.
(5) as shown in Figure 13.5, the Analysis tab of the Performance planner, because the database of the storage performance parameters selected in the interface shown in Figure 13.3 is "myoracle.mynet" in the specified database, so this is the connection to the data

(6) After a successful connection to the history database, in the Manage Target navigator, select the history data Base/Database/myoracle.mynet option, and the statistics about the database that are stored in the history database include the following categories.
Response Time: Statistical data for routine response time.
Number of wait events: statistics for the number of sessions that include routines waiting for events and waiting for events.
I/O: Includes file statistics and routine I/O statistics.
Database routines: Includes routine efficiency and initialization parameter statistics.
Background process: Includes statistics for redo statistics, fallback segments, and DBWR processes.
Memory: Statistical information about database dictionaries and shared pools.
User statistics: Statistics on the session action count.
Load: Includes routine statistics per second and statistics information for each transaction.
The analysis and judgment of historical data can be done by selecting the specific statistics below each category.
2. The historical data analysis of the Performance planner
The following is an example of historical statistics for response time/routine response time, which describes how to use a performance planner to analyze historical data and use the tool to predict future performance.
(1) Click the response time/routine response time option to select the database "Myoracle.mynet" in the Data Source list box that appears, and in the Data Items list box, the parameters for the routine response time in the history data are displayed.
The percentage of the wait time.
The percentage of CPU time.
Response time for each transaction.
Commit count.
The fallback count.
Response time for each execution.
Execution count.
Click the Show new Chart button after clicking to select the data item you want to view, as shown in Figure 13.6.
(2) A performance chart showing the sampled data shown in Figure 13.7, click the button shown in the picture to provide a forecast of future performance according to the sampled performance data.

(3) The "Welcome" interface of the Trend Analysis Wizard, shown in Figure 13.8, is available, click the button.
(4) The date range interface for the Trend Analysis Wizard, shown in Figure 13.9, Capacity Planner will estimate how the data varies over time based on this date range. The selected date range has a significant impact on the results of the trend analysis. If the estimate is based on a very busy period of time the system will get a result. However, if the estimates are based on data collected within a long period of time that contains the system is not busy, the results will be very different. Includes two options.
If you select the Use the date range selected for the chart radio button, capacity planner analyzes all the data currently displayed in the Analysis window and then generates an estimate based on that data.
If you want to analyze a specific range of data instead of analyzing the data that is displayed in the chart, select Select New chart date range radio buttons. You can then select one of two options: Generate estimates based on the most recent period and generate estimates based on a specific date range that you specify.
Click the button when the settings are complete.

(5) The type interface of the Trend Analysis Wizard appears as shown in Figure 13.10, which uses the Trend Analysis Wizard to perform two trend analyses for the selected data item.
"Point Trend Analysis": Provides target time for data items, and Oracle Capacity Planner uses the values in the history database to speculate on the value of the data item at this target point in time.
Value Trend Analysis: Provides a target value for a data item, and Oracle Capacity Planner uses the values in the history database to infer when the data item will reach its target value.
Select the "estimate values to be reached in the following date items" radio buttons for value trend analysis, click the button.

(6) The "description" interface of the Trend Analysis Wizard, shown in Figure 13.11, shows the results of the system's calculated trend analysis. To change the trend analysis before the data is displayed on the chart, click the button, update the chart, and click the button.
(7) The results of the analysis of the response time parameters of the routines, as shown in Figure 13.12, can be saved or generated from Web browsers that can be viewed.


Use of top-level sessions

The top-level session (Topsessions) is a tool that is integrated in Oracle Enterprise Management Packs (Oracle Enterprise Management packs) to monitor and analyze session performance in the system. Because each session is a connection request from a server or client, the performance of the system's processing session can be observed by performing a statistical analysis of the information on these connection requests.
1. Login for top-level session
(1) Select "Start"/"Oracle-orahome90"/"Enterprise Management Packs"/"diagnostics"/"topsessions" option on the server's desktop The login interface appears as shown in Figure 13.13. Note that you must click the Connection Details button to set the way to connect to the database, otherwise it will produce garbled characters.

(2) appear as shown in Figure 13.14 consistent connection details interface, select the "Connect through the Intelligent agent" radio button, click the "OK" button to complete.
(3) The top-level session interface, shown in Figure 13.15, shows the current database system background session and user session information in tabular form. This includes the SID, session name, State, source machine, the physical parameters used by the session, and memory parameters.

2. Information analysis of top-level conversation
(1) by double-clicking a session in the interface shown in Figure 13.15, the Details tab for the session shown in Figure 13.16 appears, with the main information included.
The session identity.
The current state.
CPU activity for the session.
Memory usage for the session.
I/O use of the session.
(2) as shown in Figure 13.17, the "Statistics" tab of Session information, which shows the performance parameters of the session in tabular form.

Because session information is sampled by the system at timed intervals, administrators can collect session information by setting the sampling frequency by default of 15 seconds. The top-level session tool can also be nested as a child tool for performance administrators.

Use of Oracle Experts

Oracle Expert is a tool that is integrated in the tuning Pack (tuning) to optimize and tune system performance.
1. Create an optimization session
(1) Select "Start"/"Oracle-orahome90"/"Enterprise Management Packs"/"Tuning"/"Expert" option on the server's desktop The expert login interface appears as shown in Figure 13.18.

There are two ways of logging in.
"Log on to Oracle Management server": Managed through a Management server.
"Log in to a separate data archive": Choose from an environment where there is no management server.
Select the "Log on to Oracle Management Server" radio button and log in as Administrator.
(2) The Welcome interface for the Create Tuning Session Wizard, as shown in Figure 13.19, is the basic configuration of Oracle Expert to collect profiling data in an optimized database environment, and generate optimized proposal scenarios, reports, and implementation scripts. The first time you use Oracle Expert, you need to create a new tuning session.
Select the "Create a new tuning session" radio button and click the button.
(3) The Create interface for the Tuning Session Wizard appears as shown in Figure 13.20, select "Myoracle.mynet" in the "to optimize which database" Drop-down list box, enter "New tuning session" in the "How to name the new tuning session" text box, and click the button.

(4) The Scope tab of the new tuning session, as shown in Figure 13.21, is used to set the scope of the optimization and to optimize the characteristics of the session.
The main settings for the optimization range include.

Check routine optimization: Use this optimization scope to determine whether the correct tuning parameters are set and whether the database routines can efficiently utilize system resources.
"Check SQL Reuse Possibilities": Use this optimization scope to determine if the tuning session workload contains the same nature and slightly different syntax for SQL statements. Such SQL statements must be parsed and cached separately. If the grammatical differences have been excluded, then Oracle expert will cache a single version of the statement, allowing the application to reuse the cached SQL statement.
"Check for appropriate space management": Use this optimization scope to evaluate database space management issues, such as table spatial structure, schema object sizing and layout, and table space allocation for database users.
"Check for Best data access": Use this tuning session to optimize the index of the specified table and examine the indexes that need to be rebuilt. There are 3 different options. Select "Perform a comprehensive index evaluation on the table referenced by the worst performing SQL statement", Oracle Expert will automatically centralize data access optimization in the tables referenced by performing the worst SQL statements (identified in the tuning session effort), optimizing the session's SQL The statement divides the rank according to the physical read rate per execution of each statement, and Oracle Expert automatically checks for index fragmentation on the existing indexes in the target table. Select the comprehensive index evaluation for specified tables radio button, Oracle Expert will centralize data access optimizations in specific scenarios or tables, and Oracle Expert will automatically check for index fragmentation on existing indexes in the target table. If you want to perform an index fragmentation check only, select index fragmentation evaluation for specified tables radio buttons. Oracle Expert will identify only the indexes in the target table, the target table must have encountered index hysteresis and need to be rebuilt to improve performance.
The attribute parameter settings for tuning sessions include.
Application Type: The Oracle expert indicates the type of work that is used in the database environment. This allows Oracle Expert to optimize the database based on the workload type. Possible values are: OLTP (OLTP workloads typically use simple queries that require fast response times for tables that contain mixed read and write requests), data warehouses (data warehouse workloads typically use consolidated queries for large, usually read-only database tables) and multi-purpose (multipurpose workloads typically have very wide response time constraints, It is typically characterized by a large number of write-intensive transactions by one or a few users.
Shutdown tolerance: You can determine whether the system's recommendations will tend to optimize recovery or optimize performance. If the tolerance is large, Oracle Expert will optimize performance. If the tolerance is small, Oracle expert will optimize recovery time.
Peak Logical Write Rate: Indicates the maximum write transaction volume to Oracle Expert, which is used to evaluate whether the server is configured to support the expected write transaction rate.
"Used table Application": tells Oracle Expert whether to use a tabular application in a database environment. Oracle Expert contains rules dedicated to tabular applications, such as setting the minimum number of open cursors for routines.
"Comprehensive analysis": tells Oracle expert that there is a complete workload in the current database.
Optimizer Validation: Notifies Oracle Expert to verify the proposed case before implementing the proposal to ensure that performance is fully improved. Oracle Expert recommends implementing only those recommendations that do improve performance.
(5) Figure 13.22 shows the Collection tab for the new tuning session, specifying the types of data to be collected for tuning sessions, including systems, databases, routines, scenarios, and workload 5 classes. If a collection class is enabled, it indicates that the information is required for the current optimization scope. If the entire row is disabled, this information is not required for the current tuning range. If the collection Class option is enabled, however, the Collect class check box is not selected, and the last collection time cell and the option set cell are disabled, indicating that the class information will not be collected, but can be collected by selecting the Collect class check box.
The last collection time cell displays the date and time that each type of data was last collected.
The SET options cell indicates whether a class is ready to be collected.
(6) Figure 13.23 shows the Review tab interface for the new tuning session, which is used to view the collected data in a hierarchical view of the tuning session and SQL History collection.

(7) Figure 13.24 shows the Proposal tab interface for the new tuning session, which is used to review the recommendations generated by Oracle Expert as part of the analysis tuning session data. Click the Build button the system will automatically generate the proposed scheme.
(8) Figure 13.25 shows the Script tab interface for the new tuning session, showing a description of the files and scripts that Oracle Expert can create that can help implement the current recommendations. Also shows where the Oracle Expert creates each file.

2. Analysis Tuning session
An analysis tuning session is used to automate the analysis of the created tuning session by the system and to obtain an evaluation report.
(1) Select the Report/analyze option on the toolbar, as shown in Figure 13.26.
(2) The appearance of the interface shown in Figure 13.27 prompts you to save the analysis report in a Web page for open viewing and click OK.

(3) Open the generated Web page, the system has automatically generated the analysis report, the main aspects include.
Summary of the proposal case.
Routine analysis (including database routine analysis, compatible parameters, parallel query parameter evaluation, ranking parameter evaluation, SGA parameter evaluation, operating system specific parameter evaluation, contention problem assessment, shared server Option).
Recommend the space management proposal for the fallback section.
3. Automatic optimization of system performance
Oracle Expert provides automatic optimization of system performance.

Click to select Database Myoracle.mynet, and select the Auto optimize/start option on the toolbar to perform automatic optimizations for Oracle 9i database system performance, as shown in Figure 13.28.

Index Tuning Wizard

Index Tuning Wizard is integrated in Enterprise Manager to find and optimize indexing problems in a database. The purpose of using indexes is to increase the speed of accessing data in a database table. Index Tuning Wizard checks the SQL statements that access and updates some tables to determine whether the index of the table is properly indexed. For some tables that might benefit from index Tuning, the wizard will guide the administrator through the evaluation steps and make the changes that are required to implement the index recommendation scheme.
(1) To select the database "Myoracle.mynet" to index profiling in the Manage Target navigator of Enterprise Manager after you log on to the Management server, and in the toolbar, select Tools/tuning Pack/Index Tuning Wizard "option, as shown in Figure 13.29.

(2) The "Welcome" interface of the Index Tuning Wizard, shown in Figure 13.30, is displayed, click the button.
(3) The application type interface of the Index Tuning Wizard, shown in Figure 13.31, is used to select the application type of the target database, and understanding the type of database application helps index tuning Wizard determine the type and number of indexes to suggest. This information also helps determine whether some database functionality is being used. There are 3 different options.
Online transaction processing (OLTP): Applications must provide fast end-user response times. A typical example of an OLTP application is the banking system, where online customer account transaction processing requires the database to respond quickly.
Data Warehouse: Applications provide flexible access to large amounts of data. An example of a data warehouse application is a consumer market database that analysts use to conduct product surveys.
Multipurpose: Some databases are available for multiple application types, including the combination of OLTP and data warehouses. Select the Multipurpose radio button and click the button.

(4) The scheme selection interface for the Index Tuning Wizard, shown in Figure 13.32, is used to specify where to search for an optimization opportunity for the index, with two options.
Any scenario: Index tuning Wizard will search for indexing optimization opportunities in all scenarios in the database. This is the recommended option because you will get more effective index recommendations if you include all scenarios within the search scope.
"Selected Scenarios": Index tuning Wizard will only search for indexing optimization opportunities in selected scenarios. You should use this option if your database administrator wants to limit the search scope for indexing problems to the scope of the scenarios that they manage. The index recommendations obtained through this search are less effective because there are many scenarios that are not included in the search scope.
Select the "any scheme" radio button here, click the button.
(5) The index recommendations interface appears as shown in Figure 13.33, the Index Tuning Wizard. Click the Build button the system will automatically analyze and evaluate the indexing performance used by the system, which can last several minutes depending on the specific scenario and the number of SQL statements involved in the analysis process. This action is displayed in the suggestions window during execution, which allows you to monitor the progress of the assessment process. If you do not check for problems, you will see a "No indexing problem checked" message, click the button.

(6) The Analysis report and script interface, as shown in Figure 13.34, can be used to view, save, and print the resulting analysis reports and related scripts after the index proposal is generated, using the Index Tuning Wizard.
(7) The "Finish" interface appears as shown in Figure 13.35.

The index tuning Wizard provides several options for using the indexing recommendations.
"Implementation of the Motion": Select this check box if you want to implement the index proposal immediately.
Save As enforcement script: Select this check box if you want to create a SQL script that can be reviewed, modified, and implemented in the future.
Save As Oracle Expert tuning session: Select this check box if Oracle Expert is installed and you want to create an Oracle Expert tuning session in the Index tuning Wizard session.
Here, select the Save as Oracle Expert tuning session check box, and click the OK button.
In general, when a database application executes SQL performance degradation, or develops a new application for the database, or modifies an existing application's SQL statement, you can perform an Index Tuning wizard that optimizes Oracle applications based on the cost of execution.

Use of SQL analysis

SQL analysis (SQL Analyze) is a tool that integrates the performance analysis of specific SQL statements in Enterprise Manager. The SQL statement that completes the same task, writes according to the different syntax, can obtain the different execution performance. Oracle SQL Analytics Tools provide intuitive visibility into the performance of SQL statements, and administrators and program developers can improve performance by optimizing SQL statements for optimal execution.
(1) In Enterprise Manager, in the Manage Target navigator, select the database "Myoracle.mynet" in which you want to index profiling, and in the toolbar, select the Tools/tuning Pack/SQL Analyze option.
(2) The initialization parameter interface for the Oracle SQL analysis appears as shown in Figure 13.36.
The Oracle SQL analysis tool Records SQL analysis of 3 types of information for each database.
Initialization parameters: includes routine parameters and session arguments.
Topsql: Top-level SQL, using the analysis results of system-intensive SQL statements.
SQL history: Analysis results for all SQL statements used.
(3) as shown in Figure 13.37, the Topsql Interface for SQL analysis, click to select the SQL text and display the corresponding profiling parameters for the statement, which is the basis for the Oracle database system to optimize the execution cost for SQL statements. Administrators understand the purpose of these parameters to optimize the performance of the system by designing the least-used SQL statement to do the same job.

Administrators can use this tool to test the system resources used by different SQL statements and to optimize program design.

Lock Manager

When the number of users of the database becomes more and more, the resources of the server will compete, if there is no reasonable mechanism to coordinate the competition of this resource, it may cause some users to occupy a large amount of resources, and some other users will never get the resources they want, which is called deadlock. Deadlocks will greatly affect the performance of the system. This is just like in the road after the traffic accident, other cars can not or only bypass the traffic system performance greatly reduced. And the administrator to do is in the event of an accident, hurriedly find out where the accident occurred, and then resolve as soon as possible.
The lock Manager is a tool provided by Oracle to monitor the locks used by system resources. By using this tool, an administrator can find out which users are causing the deadlock to occur, and thus the solution is obtained.
(1) In Enterprise Manager following the login Management Server, in the Manage target navigation tree, select the database "Myoracle.mynet" in which you want to index profiling, and select the Tools/diagnostic Pack/Lock Monitor option on the toolbar.
(2) The Lock manager interface, shown in Figure 13.38, has 4 options for selecting "Drill Down" in the toolbar to help administrators analyze system resources for deadlock occurrence.

History data: A record of historical data.
User Type Lock chart: The lock that the user obtains.
Block/Wait Lock chart: A lock that is waiting or is in a blocking/waiting state.
Terminate session: Terminates the user's session.
The above describes some of the main integration tools that can be used to analyze and tune performance, as the functionality of these tools is integrated into the performance manager.

Performance manager

Oracle Performance Manager is a performance analysis and Tuning tool integrated in Oracle Enterprise Management Packs (Oracle Enterprise Management packs) for nodes, The resources and operating system performance of the database and HTTP server are analyzed and managed.
(1) Select "Start"/"Oracle-orahome90"/"Enterprise Management Packs"/"diagnostics"/"Performance Manager" option on the server's desktop. The login interface appears as shown in Figure 13.39.
There are two options.
"Log on to Oracle Management Server": Use Performance manager after logging on to the Management server.
"Independent, no Data archive connection": In the direct connection to the database use, if selected, the performance manager interface will appear garbled.
Select the "Log on to Oracle Management Server" radio button and enter it in the Administrator, password text box, as you set it.
(2) The Performance manager interface, shown in Figure 13.40, enables performance analysis of the following resources.
OLAP Services: Analysis of online analytical processing service performance.
Parallel Manager: The performance of parallel database system is analyzed.
Database: Analyze the performance of the database.
HTTP Server: Analysis of the performance of HTTP servers.
Node: Performance analysis of the resources managed by the node.
SQL Servers: Profiling a SQL Server server.

(3) If there is a phenomenon of garbled, you can follow the following steps to log on to the database. In the Manage Target navigator, select the network/database/myoracle.mynet option, log back into the database, appear in the database login interface shown in Figure 13.41, and click the Connection Details button.
(4) The connection details interface appears as shown in Figure 13.42, with two options.
"Direct connection": the client directly connected to monitor resources, easily generated garbled.
"Through the Intelligent Agent Connection": Through the intelligent agent to establish a connection, does not produce garbled.
Select the "Connect through Intelligent Agent" radio button, the name of the Management Server appears automatically in the Agent Host text box, and click OK to eliminate the garbled behavior that appears in Performance manager management.