本篇內(nèi)容介紹了“oracle數(shù)據(jù)庫CPU過高問題定位分析”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
成都創(chuàng)新互聯(lián)公司堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：網(wǎng)站制作、做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的諸城網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！
一、ASH
顆粒度最細(xì)最常用的診斷工具，通常當(dāng)問題剛發(fā)生時(shí)，即可立即根據(jù)生成的ash報(bào)告診斷問題
下文示例為當(dāng)問題出現(xiàn)后，查看最近5分鐘的性能報(bào)告，運(yùn)行ashrpt腳本后，其他選項(xiàng)直接默認(rèn)跳過，在Enter value for begin_time選項(xiàng)中填寫"-5"
SQL> @ashrpt Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 1506959389 ORCL1 orcl Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name InstanceHost ------------ -------- ------------ ------------ ------------ * 1506959389 1 ORCL orcl localhost.lo caldomain Defaults to current database Using database id: 1506959389 Enter instance numbers. Enter 'ALL' for all instances in a RAC cluster or explicitly specify list of instances (e.g., 1,2,3). Defaults to current instance. Using instance number(s): 1 ASH Samples in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Oldest ASH sample available: 19-Jun-18 19:41:38 [ 52092 mins in the past] Latest ASH sample available: 25-Jul-18 23:52:52 [ 1 mins in the past] Specify the timeframe to generate the ASH report ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter begin time for report: -- Valid input formats: --To specify absolute begin time: -- [MM/DD[/YY]] HH24:MI[:SS] -- Examples: 02/23/03 14:30:15 -- 02/23 14:30:15 -- 14:30:15 -- 14:30 --To specify relative begin time: (start with '-' sign) -- -[HH24:]MI -- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins) -- -25 (SYSDATE - 25 Mins) Defaults to -15 mins Enter value for begin_time: -5 Report begin time specified: -5 Enter duration in minutes starting from begin time: Defaults to SYSDATE - begin_time Press Enter to analyze till current time Enter value for duration: Report duration specified: Using 25-Jul-18 23:48:39 as report begin time Using 25-Jul-18 23:53:41 as report end time Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- Explanation: -- In the 'Activity Over Time' section of the ASH report, -- the analysis period is divided into smaller slots -- and top wait events are reported in each of those slots. -- Default: -- The analysis period will be automatically split upto 10 slots -- complying to a minimum slot width of -- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or -- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY. Specify Slot Width in seconds to use in the 'Activity Over Time' section: Defaults to a value as explained above: Slot Width specified: Specify Report Targets (using ashrpti.sql) to generate the ASH report ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- Explanation: -- ASH Report can accept "Report Targets", -- like a particular SQL statement, or a particular SESSION, -- to generate the report on. If one or more report targets are -- specified, then the data used to generate the report will only be -- the ASH samples that pertain to ALL the specified report targets. -- Default: -- If none of the report targets are specified, -- then the target defaults to all activity in the database instance. Specify SESSION_ID (eg: from V$SESSION.SID) report target: Defaults to NULL: SESSION report target specified: Specify SQL_ID (eg: from V$SQL.SQL_ID) report target: Defaults to NULL: (% and _ wildcards allowed) SQL report target specified: Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target: [Enter 'CPU' to investigate CPU usage] Defaults to NULL: (% and _ wildcards allowed) WAIT_CLASS report target specified: Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target: Defaults to NULL: SERVICE report target specified: Specify MODULE name (eg: from V$SESSION.MODULE) report target: Defaults to NULL: (% and _ wildcards allowed) MODULE report target specified: Specify ACTION name (eg: from V$SESSION.ACTION) report target: Defaults to NULL: (% and _ wildcards allowed) ACTION report target specified: Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target: Defaults to NULL: (% and _ wildcards allowed) CLIENT_ID report target specified: Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target: Defaults to NULL: (% and _ wildcards allowed) PLSQL_ENTRY report target specified: Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is ashrpt_1_0725_2353.html. To use this name, pressto continue, otherwise enter an alternative. Enter value for report_name: /home/oracle/ash_test.html Using the report name /home/oracle/ash_test.html Summary of All User Input ------------------------- Format : HTML DB Id : 1506959389 Inst num : 1 Begin time : 25-Jul-18 23:48:39 End time : 25-Jul-18 23:53:41 Slot width : Default Report targets : 0 Report name : /home/oracle/ash_test.html
通過結(jié)果圖可以觀察到短時(shí)間內(nèi)引發(fā)問題的sql：
二、AWR報(bào)告
由oracle的兩份快照而產(chǎn)生的差異報(bào)告，通常一小時(shí)一次，問題情況下可通過手動(dòng)生成，縮小分析問題時(shí)段的范圍
一般可在問題出現(xiàn)時(shí)以及幾分鐘后，各運(yùn)行exec dbms_workload_repository.create_snapshot一次
SQL> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed. SQL> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed. SQL> @awrrpt Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 1506959389 ORCL 1 orcl Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 1506959389 1 ORCL orcl localhost.lo caldomain Using 1506959389 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressingwithout specifying a number lists all completed snapshots. Enter value for num_days: Listing all Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- orcl ORCL 21 25 Jul 2018 22:30 1 22 25 Jul 2018 23:42 1 23 25 Jul 2018 23:45 1 24 25 Jul 2018 23:47 1 25 25 Jul 2018 23:49 1 26 25 Jul 2018 23:51 1 27 26 Jul 2018 00:04 1 28 26 Jul 2018 00:07 1 29 26 Jul 2018 00:08 1 30 26 Jul 2018 00:09 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 29 Begin Snapshot Id specified: 29 Enter value for end_snap: 30 End Snapshot Id specified: 30 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_29_30.html. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: /home/oracle/awr_test1.html Using the report name /home/oracle/awr_test1.html
根據(jù)查看SQL ordered by CPU Time，可觀察出相應(yīng)時(shí)間段的高CPU消耗SQL
三、ADDM
與awr類似，依托于快照生成，但有詳細(xì)的分析建議（可在方法二中手動(dòng)生成兩次快照后使用）
運(yùn)行exec DBMS_ADDM.ANALYZE_DB()命令時(shí)，第2、3參數(shù)分別為兩次手動(dòng)生成的快照號(hào)
重慶分公司
重慶分公司
SQL> var task_name varchar2(30);
SQL> exec DBMS_ADDM.ANALYZE_DB(:task_name,21,36);
PL/SQL procedure successfully completed.
SQL> print :task_name
TASK_NAME
--------------------------------------------------------------------------------
TASK_236
SQL> set long 1000000 pagesize 0;
SQL> select dbms_addm.get_report('TASK_236') from dual;
通過報(bào)告可以精確的看到性能主要被消耗于哪里，消耗大小，以及事件描述
ADDM Report for Task 'TASK_236'
-------------------------------
Analysis Period
---------------
AWR snapshot range from 21 to 36.
Time period starts at 25-JUL-18 10.30.54 PM
Time period ends at 26-JUL-18 10.00.29 PM
Analysis Target
---------------
Database 'ORCL' with DB ID 1506959389.
Database version 11.2.0.4.0.
Analysis was requested for all instances, but ADDM analyzed instance orcl,
numbered 1 and hosted at localhost.localdomain.
See the "Additional Information" section for more information on the requested instances.
Activity During the Analysis Period
-----------------------------------
Total database time was 1445 seconds.
The average number of active sessions was .02.
ADDM analyzed 1 of the requested 1 instances.
Summary of Findings
-------------------
Description Active Sessions Recommendations Percent of Activity
---------------------------------------- ------------------- --------------
-
1 Top SQL Statements .02 | 88.73 4
2 PL/SQL Execution .01 | 41.55 1
3 Unusual "User I/O" Wait Event 0 | 28.78 4
4 Top Segments by "User I/O" and "Cluster" 0 | 28.64 1
5 Undersized instance memory 0 | 23.6 1
6 Sequence Usage 0 | 2.14 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Findings and Recommendations
----------------------------
Finding 1: Top SQL Statements
Impact is .02 active sessions, 88.73% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.
Recommendation 1: SQL Tuning
Estimated benefit is .01 active sessions, 36.62% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the INSERT statement with SQL_ID "5hrxg25g8bdpd".
Related Object SQL statement with SQL_ID 5hrxg25g8bdpd.
INSERT INTO T1 VALUES (S_T1_ID.NEXTVAL,DBMS_RANDOM.STRING('u', 10),SYSDATE)
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 45% for SQL execution, 0% for parsing, 55% for PL/SQL execution and 0% for Java execution.
Rationale
SQL statement with SQL_ID "5hrxg25g8bdpd" was executed 8000000 times and
had an average elapsed time of 0.00004 seconds.
Rationale
Top level calls to execute the PL/SQL statement with SQL_ID
"7j89gjdpf4m4u" are responsible for 100% of the database time spent on
the INSERT statement with SQL_ID "5hrxg25g8bdpd".
Related Object
SQL statement with SQL_ID 7j89gjdpf4m4u.
begin
for i in 1 .. 1000000 loop
insert into t1 values (s_t1_id.Nextval,dbms_random.string('u',
10),sysdate);
end loop;
end;
Recommendation 2: SQL Tuning
Estimated benefit is 0 active sessions, 28.87% of total activity.
-----------------------------------------------------------------
Action
Investigate the ALTER TABLESPACE statement with SQL_ID "6sqc239sgbmqf"
for possible performance improvements. You can supplement the
information given here with an ASH report for this SQL_ID.
Related Object
SQL statement with SQL_ID 6sqc239sgbmqf.
Rationale
The SQL Tuning Advisor cannot operate on ALTER TABLESPACE statements.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
I/O and Cluster wait for TABLE "SYS.KOTTD$" with object ID 543 consumed
97% of the database time spent on this SQL statement.
Recommendation 3: SQL Tuning
Estimated benefit is 0 active sessions, 14.08% of total activity.
-----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"a0qbnz3z4x4ns".
Related Object
SQL statement with SQL_ID a0qbnz3z4x4ns.
select * from scott.t1 where name=dbms_random.string('u', 10)
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 5% for SQL execution,
0% for parsing, 95% for PL/SQL execution and 0% for Java execution.
Rationale
SQL statement with SQL_ID "a0qbnz3z4x4ns" was executed 2 times and had
an average elapsed time of 89 seconds.
Recommendation 4: SQL Tuning
Estimated benefit is 0 active sessions, 9.15% of total activity.
----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"gmkaj9nz7vyvw".
Related Object
SQL statement with SQL_ID gmkaj9nz7vyvw.
select * from scott.t3 where name=dbms_random.string('u', 10)
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 16% for SQL
execution, 0% for parsing, 84% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "gmkaj9nz7vyvw" was executed 2 times and had
an average elapsed time of 62 seconds.
Finding 2: PL/SQL Execution
Impact is .01 active sessions, 41.55% of total activity.
--------------------------------------------------------
PL/SQL execution consumed significant database time.
Recommendation 1: SQL Tuning
Estimated benefit is .01 active sessions, 41.55% of total activity.
-------------------------------------------------------------------
Action
Tune the entry point PL/SQL "SYS.DBMS_RANDOM.STRING" of type "PACKAGE"
and ID 9372. Refer to the PL/SQL documentation for addition information.
Rationale
193 seconds spent in executing PL/SQL
"SYS.DBMS_RANDOM.RECORD_RANDOM_NUMBER" of type "PACKAGE" and ID 9372.
Rationale
193 seconds spent in executing PL/SQL
"SYS.DBMS_RANDOM.REPLAY_RANDOM_NUMBER" of type "PACKAGE" and ID 9372.
Rationale
111 seconds spent in executing PL/SQL "SYS.DBMS_RANDOM.STRING" of type
"PACKAGE" and ID 9372.
Rationale
101 seconds spent in executing PL/SQL "SYS.DBMS_RANDOM.VALUE#1" of type
"PACKAGE" and ID 9372.
Finding 3: Unusual "User I/O" Wait Event
Impact is 0 active sessions, 28.78% of total activity.
------------------------------------------------------
Wait event "Data file init write" in wait class "User I/O" was consuming
significant database time.
Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 28.78% of total activity.
-----------------------------------------------------------------
Action
Investigate the cause for high "Data file init write" waits. Refer to
Oracle's "Database Reference" for the description of this wait event.
Recommendation 2: Application Analysis
Estimated benefit is 0 active sessions, 28.78% of total activity.
-----------------------------------------------------------------
Action
Investigate the cause for high "Data file init write" waits in Module
"sqlplus@localhost.localdomain (TNS V1-V3)".
Recommendation 3: Application Analysis
Estimated benefit is 0 active sessions, 28.78% of total activity.
-----------------------------------------------------------------
Action
Investigate the cause for high "Data file init write" waits in Service
"SYS$USERS".
Recommendation 4: Application Analysis
Estimated benefit is 0 active sessions, 28.78% of total activity.
-----------------------------------------------------------------
Action
Investigate the cause for high "Data file init write" waits with
P1,P2,P3 ("count, intr, timeout") values "1", "256" and "4294967295"
respectively.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is .01 active sessions, 32.41% of total activity.
Finding 4: Top Segments by "User I/O" and "Cluster"
Impact is 0 active sessions, 28.64% of total activity.
------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.
Recommendation 1: Segment Tuning
Estimated benefit is 0 active sessions, 28.64% of total activity.
-----------------------------------------------------------------
Action
Investigate application logic involving I/O on TABLE "SYS.KOTTD$" with
object ID 543.
Related Object
Database object with ID 543.
Action
Look at the "Top SQL Statements" finding for SQL statements consuming
significant I/O on this segment. For example, the ALTER TABLESPACE
statement with SQL_ID "6sqc239sgbmqf" is responsible for 98% of "User
I/O" and "Cluster" waits for this segment.
Rationale
The I/O usage statistics for the object are: 0 full object scans, 2
physical reads, 0 physical writes and 0 direct reads.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is .01 active sessions, 32.41% of total activity.
Finding 5: Undersized instance memory
Impact is 0 active sessions, 23.6% of total activity.
-----------------------------------------------------
The Oracle instance memory (SGA and PGA) was inadequately sized, causing
additional I/O and CPU usage.
The value of parameter "memory_target" was "300 M" during the analysis period.
Recommendation 1: Database Configuration
Estimated benefit is 0 active sessions, 14.46% of total activity.
-----------------------------------------------------------------
Action
Increase memory allocated to the instance by setting the parameter
"memory_target" to 450 M.
Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is .01 active sessions, 32.41% of total activity.
Hard parsing of SQL statements was consuming significant database time.
Impact is 0 active sessions, 2.07% of total activity.
Finding 6: Sequence Usage
Impact is 0 active sessions, 2.14% of total activity.
-----------------------------------------------------
Sequence cache misses were consuming significant database time.
Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 2.14% of total activity.
----------------------------------------------------------------
Action
Investigate application or look at top SQL to find hot sequences. Use a
larger cache size for those sequences. Try avoiding the use of the ORDER
setting if running RAC.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.“oracle數(shù)據(jù)庫CPU過高問題定位分析”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
