Solve ORACLECPU problems with AWR report analysis

Original address: http://www.cnblogs.com/crystal-guoguo/p/4213458.html

Introduction:
In the Oracle database, there are two very useful self-contained monitoring tools, EM (Enterprise Manager) and awr (Automatic Workload Repository). With AWR reports, you can generate easy-to-read monitoring reports that help diagnose and analyze performance problems.
This issue will introduce some of the basic use of AWR reports and the application in the diagnosis of performance problems.
What is awr:
Starting with Oracle 10g, using a scheduled task called Gather_stats_job to collect awr statistics, the AWR report collects a number of different statistics, including wait events, various system-level and session-level statistics, and information about SQL statements that use more resources.
By default, the Oracle database daemon collects the system's current state image at a certain interval (one hour), and is saved in the database, and the default retention time is 8 days, which can be adjusted according to the actual situation. At a specific point in time, such as before and after a test execution, you can increase the snapshot by manually creating a snapshot. When generating an AWR report, the snapshot point is selected to generate a statistical report for the specified time period.
generated by awr:
First, manually generate the AWR report
1) on the Oracle database server, log in to Sqlplus with SYSDBA identity.
2) Run the script
Enter the awrrpt execution command in the Sqlplus.
@?/rdbms/admin/awrrpt.sql
3) Input Report parameters
A) Select the type of the AWR report you want to generate, select the text type and HTML type, and enter HTML (default).
b) range of days covered by the report
Select a record of how many days the date to generate the report is. After entering the number of days, (enter 1, you want to generate a report starting today from 0 o'clock to the current time period; Enter 2, which means to generate a report for the time period from 0 o'clock to today; The interface displays a data table for the time period, with a snap ID at each point in time, with an interval of 1 hours for Oracle by default.
c) Enter the start and End snap numbers to generate the report.
d) Determine the name of the report.
Without entering a direct carriage return, the system automatically generates a report of the default name.
e) Generate reports.
When the report output is finished, the AWR report is stored in the specified directory (the directory that is logged into the Oracle database with Sqlplus).
Case Analysis:
Phenomenon:
A trading order scenario, the average CPU usage rate of the database is nearly 90%. The database for this case is 12Core, and from the instance CPU, Oracle occupies 99.5% of the CPU resources (89.9%) in the server.

parsing process:
    to locate the database CPU too high, use the AWR report during testing to observe the database usage:
1) First overall view of the report header information (header) and performance indicators (Buffer Nowait,buffer hit,library Hit,execute to Parse,parse CPUs to Parse Elapsd,redo Nowait).
2) Look at time Model Statistics, with the entire SQL execution time (SQL execute elapsed times) in 15,499 seconds, parse time elapsed takes 26 seconds, hard parse time Parse elapsed time) took 26 seconds, it can be found that the resolution is very few flowers, so it is possible to determine that SQL parsing has not become a performance bottleneck, and further speculated that SQL encountered a bottleneck in the process of acquiring data.
4) Focus on the summary information for the load. The TPS at the database level is only 1.3,tps low.
5) Further analysis found that SQL ordered by CPU time from SQL CJNZFF37C9GR3 and CQ32AGU0QKPD8 SQL statements, the number of executions, high CPU consumption.
6) For these 2 SQL statements, execute a command to get the SQL execution plan. (Enter SQL ID)
@ $ORACLE _home/rdbms/admin/awrsqrpt.sql
7) by viewing the execution plan, the cost of Ctr_loan_cont table reads is much higher. You need to read 196000 rows of data based on the index. The Ctr_loan_cont table is 1200多万条 data, occupy space 5888m, statistical update is also more accurate. Some of the plans are as follows:

8) View segments by Logical reads,96% Read all the logical reads for the Ctr_loan_cont table. Finally, the database CPU is too high for SQL.

Processing method:
(1) Transfer a portion of the database server logic to the application server. Some SQL-judged logic in the database is implemented by applying code to reduce the complexity of the database server.
(2) The view is still used for the XX module, but the third-party platform is added as a conditional filter in the view, and the Merge method Union (which filters out duplicate records after the table link) is changed to union ALL (no sorting deduplication).
Post-tuning effects:
(1) The cost of Ctr_loan_cont table reads is reduced from 15359 to 276.
(2) The CPU average utilization rate of the database server decreased from 89.9% to 11.6%.

Solve ORACLECPU problems with AWR report analysis (RPM)