cpu used 100% when export AWR
各位好：昨天我在新环境上安装11.2.0.4，操作系统为LINUX ,在执行@?/RDBMS/ADMIN/AWRRPT时，持续时间将近10分钟，期间cpu使用率将近100% ，如下：
Cpu(s): 96.0%us, 3.3%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.7%si, 0.0%st
Mem: 2075500k total, 1694588k used, 380912k free, 58424k buffers
Swap: 5116660k total, 92k used, 5116568k free, 1432396k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9980 oracle 25 0 790m 94m 87m R 97.9 4.6 21:11.13 oracle
9902 oracle -2 0 783m 14m 12m S 2.0 0.7 0:23.01 oracle
alert日志未有明显信息
当时ASH 显示主要等待事件为CPU + Wait for CPU，
等待SQL语句为with sn as (select DBID, STAT_ID, STAT_NAME from DBA_HIST_STAT_NAME where STAT_NAME not in ('DB time', 'logons current', 'opened cursors current', 'workarea memory allocated', 'session cursor cache count', 'session uga memory', 'session uga memory max', 'session pga memory', 'session pga memory max') and STAT_NAME not like '%wait time' and dbid = 1397357371), s as (select distinct instance_number, stat_id from dba_hist_service_stat where dbid = 1397357371 and snap_id = 1) select b.stat_name st, e.value - b.value, round((e.value - b.value)/2, 2), round((e.value - b.value)/2, 2) from dba_hist_sysstat b, dba_hist_sysstat e where b.snap_id = 1 and e.snap_id = 1 and b.dbid = 1397357371 and e.dbid = 1397357371 and b.instance_number = 1 and e.instance_number = 1 and b.stat_id = e.stat_id and (e.stat_id, e.instance_number) not in (select stat_id, instance_number from s) and e.stat_id in (select stat_id from sn) and e.value >= b.value and e.value > 0 order by st，重新收集统计信息，手工跑这条语句，还是很慢，MOS上貌似找不到此问题解决办法，请大家给个解决思路，谢谢 获取此SQL的SQL_ID
并 对应执行下面的脚本 SELECT *
FROM (SELECT '1.v$sql'||'实例号:'||GV$SQL.inst_id source,
SQL_ID,
plan_hash_value,
TO_CHAR (FIRST_LOAD_TIME) begin_time,
'在cursor cache中' end_time,
executions "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM Gv$SQL
WHERE sql_id = '&A'
UNION ALL
SELECT '2.sqltuning set' source,
sql_id,
plan_hash_value,
'JUST SQLSET NO DATE' begin_time,
'JUST SQLSET NO DATE' end_time,
EXECUTIONS "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM dba_sqlset_statements
WHERE SQL_ID = '&A'
UNION ALL
SELECT '3.dba_advisor_sqlstats' source,
sql_id,
plan_hash_value,
'JUST SQLSET NO DATE' begin_time,
'JUST SQLSET NO DATE' end_time,
EXECUTIONS "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM dba_sqlset_statements
WHERE SQL_ID = '&A'
UNION ALL
SELECT DISTINCT
'4.dba_hist_sqlstat' || '实例号:' || SQL.INSTANCE_NUMBER
source,
sql_id,
PLAN_HASH_VALUE,
TO_CHAR (s.BEGIN_INTERVAL_TIME ,'YYYY-MM-DD hh24:mi:ss') begin_time,
TO_CHAR (s.END_INTERVAL_TIME,'YYYY-MM-DD hh24:mi:ss') end_time,
SQL.executions_delta,
SQL.buffer_gets_delta
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"LIO/exec",
(SQL.cpu_time_delta / 1000000)
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"CPUTIM/exec",
(SQL.elapsed_time_delta / 1000000)
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"ETIME/exec",
SQL.DISK_READS_DELTA
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"PIO/exec",
SQL.ROWS_PROCESSED_DELTA
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"ROWs/exec"
FROM dba_hist_sqlstat SQL, dba_hist_snapshot s
WHERE SQL.INSTANCE_NUMBER = s.INSTANCE_NUMBER
AND SQL.dbid = (SELECT dbid FROM v$database)
AND s.snap_id = SQL.snap_id
AND sql_id IN ('&A'))
ORDER BY source, begin_time DESC; 上面跑的结果如下：
set linesize 180
SQL> /
Enter value for a: 14hgfdjtb88js
old 14: WHERE sql_id = '&A'
new 14: WHERE sql_id = '14hgfdjtb88js'
Enter value for a: 14hgfdjtb88js
old 28: WHERE SQL_ID = '&A'
new 28: WHERE SQL_ID = '14hgfdjtb88js'
Enter value for a: 14hgfdjtb88js
old 42: WHERE SQL_ID = '&A'
new 42: WHERE SQL_ID = '14hgfdjtb88js'
SOURCE SQL_ID PLAN_HASH_VALUE BEGIN_TIME END_TIME No. of exec LIO/exec CPUTIM/exec
-------------------------------------------------------------------- ------------- --------------- -------------------- ------------------- ----------- ---------- -----------
ETIME/exec PIO/exec ROWs/exec
---------- ---------- ----------
1.v$sql实例号:1 14hgfdjtb88js 2511151543 2015-01-22/09:26:25 在cursor cache中 2 212559629 304.918146
311.426583 6.5 80.5
1.v$sql实例号:1 14hgfdjtb88js 1733646760 2015-01-22/09:26:25 在cursor cache中 1 328429442 436.819592
445.774639 0 161
4.dba_hist_sqlstat实例号:1 14hgfdjtb88js 2511151543 2015-01-22 09:12:05 2015-01-22 10:00:15 1 106579799 150.858065
154.089646 13 0
给出如下脚本的信息：
@?/rdbms/admin/awrinfo 附件已上传，请查看 SQL> @?/rdbms/admin/awrinfo
This script will report general AWR information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrinfo.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrinfo.txt
No errors.
No errors.
~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~
Report generated at
11:01:24 on Jan 22, 2015 ( Thursday ) in Timezone +08:00
Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days
DB_ID DB_NAME HOST_PLATFORM INST STARTUP_TIME LAST_ASH_SID PAR
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 1397357371 ORCL wzw - Linux IA (32-bit) 1 09:01:05 (01/22) 20044 NO
########################################################
(I) AWR Snapshots Information
########################################################
*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 497.1 MB ( 2% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema XDB occupies 127.1 MB ( 25.6% )
| Schema SYS occupies 116.6 MB ( 23.4% )
| Schema APEX_030200 occupies 84.4 MB ( 17.0% )
| Schema MDSYS occupies 74.3 MB ( 14.9% )
| Schema SYSMAN occupies 46.1 MB ( 9.3% )
| Schema SYSTEM occupies 14.8 MB ( 3.0% )
| Schema ORDDATA occupies 13.5 MB ( 2.7% )
| Schema OLAPSYS occupies 8.8 MB ( 1.8% )
| Schema EXFSYS occupies 3.6 MB ( 0.7% )
| Schema CTXSYS occupies 3.6 MB ( 0.7% )
| Schema WMSYS occupies 3.5 MB ( 0.7% )
| Schema ORDSYS occupies 0.4 MB ( 0.1% )
| Schema DBSNMP occupies 0.4 MB ( 0.1% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ----------------
| XDB XDB 127.1 MB
| SDO MDSYS 74.3 MB
| EM SYSMAN 46.1 MB
| AO SYS 36.3 MB
| XSOQHIST SYS 36.3 MB
| SM/AWR SYS 30.8 MB
| ORDIM/ORDDATA ORDDATA 13.5 MB
| LOGMNR SYSTEM 13.4 MB
| SM/OPTSTAT SYS 9.2 MB
| XSAMD OLAPSYS 8.8 MB
| SM/ADVISOR SYS 7.6 MB
| SM/OTHER SYS 7.6 MB
| EXPRESSION_FILTER EXFSYS 3.6 MB
| TEXT CTXSYS 3.6 MB
| WM WMSYS 3.5 MB
| SQL_MANAGEMENT_BASE SYS 1.7 MB
| PL/SCOPE SYS 1.6 MB
| LOGSTDBY SYSTEM 1.4 MB
| STREAMS SYS 1.0 MB
| ORDIM ORDSYS 0.4 MB
| EM_MONITORING_USER DBSNMP 0.4 MB
| JOB_SCHEDULER SYS 0.4 MB
| AUTO_TASK SYS 0.3 MB
| SMON_SCN_TIME SYS 0.3 MB
| AUDIT_TABLES SYS 0.0 MB
| ORDIM/ORDPLUGINS ORDPLUGINS 0.0 MB
| ORDIM/SI_INFORMTN_SC SI_INFORMTN_SCHEMA 0.0 MB
| STATSPACK PERFSTAT 0.0 MB
| TSM TSMSYS 0.0 MB
| ULTRASEARCH WKSYS 0.0 MB
| ULTRASEARCH_DEMO_USE WK_TEST 0.0 MB
|
| Others (Unaccounted space) 68.1 MB
|
******************************************
(1c) SYSAUX usage - Unregistered Schemas
******************************************
| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
| Schema APEX_030200 occupies 84.4 MB
|
| Total space 84.4 MB
|
*************************************************************
(1d) SYSAUX usage - Unaccounted space in registered schemas
*************************************************************
|
| This section displays unaccounted space in the registered
| schemas of V$SYSAUX_OCCUPANTS.
|
| Unaccounted space in SYS/SYSTEM -16.4 MB
|
| Total space -16.4 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
| AWR size/day 105.6 MB (4,507 K/snap * 24 snaps/day)
| AWR size/wk 739.5 MB (size_per_day * 7) per instance
|
| Estimates based on 7 snaps in past 24 hours:
| AWR size/day 35.2 MB (4,507 K/snap and 7 snaps in past 21.9 hours)
| AWR size/wk 246.5 MB (size_per_day * 7) per instance
|
**********************************
(3a) Space usage by AWR components (per database)
**********************************
COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
FIXED 15.8 51.3 2,313 18.1 126.5 50% : 50%
SQLPLAN 2.6 8.5 384 3.0 21.0 76% : 24%
SPACE 2.4 7.9 357 2.8 19.5 51% : 49%
EVENTS 1.6 5.1 229 1.8 12.5 52% : 48%
SQL 1.4 4.7 210 1.6 11.5 48% : 52%
RAC 0.6 2.0 91 0.7 5.0 50% : 50%
SQLTEXT 0.4 1.4 64 0.5 3.5 86% : 14%
ASH 0.4 1.4 64 0.5 3.5 57% : 43%
SQLBIND 0.3 1.0 46 0.4 2.5 60% : 40%
**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
SQLPLAN 2.0 WRH$_SQL_PLAN - 82% TABLE
SQLPLAN 0.6 WRH$_SQL_PLAN_PK - 69% INDEX
**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR 54.2 XDB.SYS_LOB0000069480C00025$$ LOBSEGMENT
NON_AWR 26.0 MDSYS.SYS_LOB0000075520C00006$$ LOBSEGMENT
NON_AWR 15.2 SYS.SYS_LOB0000077395C00004$$ LOBSEGMENT
NON_AWR 8.0 XDB.XDB$RESOURCE TABLE
NON_AWR 7.2 SYS.SYS_LOB0000077380C00004$$ LOBSEGMENT
NON_AWR 7.0 MDSYS.SDO_CS_SRS TABLE
NON_AWR 6.0 APEX_030200.WWV_FLOW_PAGE_PLUGS TABLE
NON_AWR 5.0 APEX_030200.WWV_FLOW_STEP_ITEMS TABLE
NON_AWR 5.0 APEX_030200.WWV_FLOW_DICTIONARY$CAPED INDEX
NON_AWR 4.0 SYSTEM.SYS_LOB0000001180C00009$$ LOBSEGMENT
NON_AWR 4.0 APEX_030200.WWV_FLOW_DICTIONARY$ TABLE
NON_AWR 3.2 SYS.SYS_LOB0000077375C00004$$ LOBSEGMENT
NON_AWR 3.2 SYS.SYS_LOB0000077385C00004$$ LOBSEGMENT
NON_AWR 3.0 SYS.SYS_LOB0000006417C00038$$ LOBSEGMENT
NON_AWR 3.0 APEX_030200.WWV_FLOW_DICTIONARY$SOUNDEX INDEX
NON_AWR 3.0 APEX_030200.WWV_FLOW_DICTIONARY$WORDS INDEX
NON_AWR 3.0 APEX_030200.SYS_LOB0000084275C00010$$ LOBSEGMENT
NON_AWR 3.0 APEX_030200.WWV_FLOW_STEP_PROCESSING TABLE
NON_AWR 3.0 XDB.XDB$H_INDEX TABLE
NON_AWR 2.2 SYS.SYS_LOB0000077390C00004$$ LOBSEGMENT
NON_AWR 2.2 SYS.SYS_LOB0000077400C00004$$ LOBSEGMENT
NON_AWR 2.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE
NON_AWR 2.0 APEX_030200.WWV_FLOW_DICTIONARY$_FKIDX INDEX
NON_AWR 2.0 APEX_030200.WWV_FLOW_REGION_REPORT_COLUMN TABLE
NON_AWR 2.0 APEX_030200.SYS_LOB0000084221C00009$$ LOBSEGMENT
NON_AWR 2.0 APEX_030200.WWV_FLOW_STEP_ITEM_HELP TABLE
NON_AWR 2.0 SYSMAN.MGMT_METRICS TABLE
NON_AWR 2.0 MDSYS.SDO_COORD_REF_SYS TABLE
NON_AWR 2.0 XDB.XDB$ELEMENT TABLE
NON_AWR 2.0 SYS.SYS_LOB0000006409C00004$$ LOBSEGMENT
NON_AWR 2.0 SYS.SYS_LOB0000005160C00005$$ LOBSEGMENT
NON_AWR 2.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX
NON_AWR 1.2 ORDDATA.SYS_LOB0000070963C00005$$ LOBSEGMENT
NON_AWR 1.1 ORDDATA.SYS_LOB0000070815C00005$$ LOBSEGMENT
NON_AWR 1.0 SYS.SYS$SERVICE_METRICS_TAB TABLE
NON_AWR 1.0 SYSMAN.MGMT_METRICS_IDX_01 INDEX
NON_AWR 1.0 SYSMAN.MGMT_METRICS_PK INDEX
NON_AWR 1.0 XDB.XDB$H_LINK TABLE
NON_AWR 0.9 SYS.I_WRI$_OPTSTAT_HH_ST INDEX
NON_AWR 0.9 SYSMAN.MGMT_METRICS_IDX_03 INDEX
NON_AWR 0.9 APEX_030200.WWV_FLOW_STEPS TABLE
NON_AWR 0.8 XDB.XDB$RESOURCE_ACLOID_IDX INDEX
NON_AWR 0.8 APEX_030200.WWV_FLOW_LIST_ITEMS TABLE
NON_AWR 0.8 APEX_030200.WWV_FLOW_STEP_VALIDATIONS TABLE
NON_AWR 0.6 XDB.XDB_PK_H_LINK INDEX
NON_AWR 0.6 APEX_030200.WWV_FLOW_STEP_ITEMS_U_NAME INDEX
NON_AWR 0.6 APEX_030200.WWV_FLOW_STEP_BRANCHES TABLE
COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR 0.6 APEX_030200.WWV_FLOW_STEP_BUTTONS TABLE
NON_AWR 0.6 XDB.XDB$COMPLEX_TYPE TABLE
NON_AWR 0.6 SYSMAN.MGMT_POLICIES TABLE
NON_AWR 0.6 SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS TABLE
NON_AWR 0.5 SYS.AW_OBJ$ TABLE
NON_AWR 0.5 SYSMAN.MGMT_JOB_STEP_PARAMS TABLE
NON_AWR 0.5 SYSMAN.PK_MGMT_JOB_STEP_PARAMS INDEX
NON_AWR 0.5 SYSMAN.SYS_LOB0000081524C00004$$ LOBSEGMENT
NON_AWR 0.5 APEX_030200.WWV_FLOW_LIST_OF_VALUES_DATA TABLE
**********************************
(5a) AWR snapshots - last 50
**********************************
Total snapshots in DB 1397357371 Instance 1 = 7
DBID SNAP_ID INST FLUSH_ELAPSED ENDTM STARTUP_TIME STATUS ERRCNT
---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------
1397357371 1 1 +00000 00:00:02.7 14:00:00 (01/21) 13:08:51 (01/21) 0 0
1397357371 2 1 +00000 00:00:01.0 15:00:11 (01/21) 13:08:51 (01/21) 0 0
1397357371 3 1 +00000 00:00:00.3 16:00:22 (01/21) 13:08:51 (01/21) 0 0
1397357371 4 1 +00000 00:00:00.5 17:00:38 (01/21) 16:14:32 (01/21) 0 0
1397357371 5 1 +00000 00:00:03.5 09:12:05 (01/22) 09:01:05 (01/22) 0 0
1397357371 6 1 +00000 00:00:00.1 10:00:15 (01/22) 09:01:05 (01/22) 0 0
1397357371 7 1 +00000 00:00:00.4 11:00:27 (01/22) 09:01:05 (01/22) 0 0
**********************************
(5b) AWR snapshots with errors or invalid
**********************************
no rows selected
**********************************
(5c) AWR snapshots -- OLDEST Non-Baselined snapshots
**********************************
DBID INST SNAP_ID ENDTM STATUS ERROR_COUNT
---------- ----- ---------- ----------------- ------ -----------
1397357371 1 1 14:00:00 (01/21) 0 0
**********************************
(6) AWR Control Settings - interval, retention
**********************************
DBID LSNAPID LSPLITID LSNAPTIME LPURGETIME FLAG INTERVAL RETENTION VRSN
----------- -------- -------- -------------- -------------- ----- ----------------- ----------------- ----
1397357371 7 0 01/22 11:00:28 01/21 13:04:14 0 +00000 01:00:00.0 +00008 00:00:00.0 5
**********************************
(7a) AWR Contents - row counts for each snapshots
**********************************
SNAP_ID INST ASH SQL SQBND FILES SEGST SYSEVT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
1 1 7 99 238 5 77 43
2 1 7 102 249 5 82 48
3 1 5 83 212 5 74 48
4 1 154 132 1567 5 37 48
5 1 5 104 245 5 36 50
6 1 17 84 218 5 57 56
7 1 96 102 282 5 60 58
**********************************
(7b) AWR Contents - average row counts per snapshot
**********************************
SNAP_COUNT INST ASH SQLSTAT SQLBIND FILES SEGSTAT SYSEVENT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
7 1 41.57 100.86 430.14 5 60.43 50.14
**********************************
(7c) AWR total item counts - names, text, plans
**********************************
SQLTEXT SQLPLAN SQLBMETA SEGOBJ DATAFILE TEMPFILE
---------- ---------- ---------- ---------- ---------- ----------
376 8134 1981 173 5 1
########################################################
(II) Advisor Framework Info
########################################################
**********************************
(1) Advisor Tasks - Last 50
**********************************
OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK 05:22:49 (08/25) AUTO INITIAL
SYS/ADDM 11/ADDM:1397357371_1_2 15:00:12 (01/21) 0 0 AUTO COMPLETED
SYS/ADDM 12/ADDM:1397357371_1_3 16:00:23 (01/21) 0 0 AUTO COMPLETED
SYS/ADDM 13/ADDM:1397357371_1_6 10:00:16 (01/22) 1 1 AUTO COMPLETED
SYS/ADDM 14/ADDM:1397357371_1_7 11:00:28 (01/22) 0 0 AUTO COMPLETED
**********************************
(2) Advisor Task - Oldest 5
**********************************
OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK 05:22:49 (08/25) AUTO INITIAL
SYS/ADDM 11/ADDM:1397357371_1_2 15:00:12 (01/21) 0 0 AUTO COMPLETED
SYS/ADDM 12/ADDM:1397357371_1_3 16:00:23 (01/21) 0 0 AUTO COMPLETED
SYS/ADDM 13/ADDM:1397357371_1_6 10:00:16 (01/22) 1 1 AUTO COMPLETED
SYS/ADDM 14/ADDM:1397357371_1_7 11:00:28 (01/22) 0 0 AUTO COMPLETED
**********************************
(3) Advisor Tasks With Errors - Last 50
**********************************
OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
TASK_DESC
--------------------------------------------------------------------------------------------------------------
ERROR_MSG
--------------------------------------------------------------------------------------------------------------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK 05:22:49 (08/25) AUTO INITIAL
Description: Automatic SQL Tuning Task
Error Msg :
########################################################
(III) ASH Usage Info
########################################################
**********************************
(1a) ASH histogram (past 3 days)
**********************************
NUM_ACTIVE_SESSIONS NUM_SAMPLES
-------------------- ------------
0000 - 0004 278
**********************************
(1b) ASH histogram (past 1 day)
**********************************
NUM_ACTIVE_SESSIONS NUM_SAMPLES
-------------------- ------------
0000 - 0004 278
**********************************
(2a) ASH details (past 3 days)
**********************************
INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ ------------ ----------
1 14:00:01 (01/21) 10:31:12 (01/22) 1,531 284 0.19
**********************************
(2b) ASH details (past 1 day)
**********************************
INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ ------------ ----------
1 14:00:01 (01/21) 10:31:12 (01/22) 1,531 284 0.19
**********************************
(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)
**********************************
Foreground % 94.0
Background % 6.0
MMNL % 0.0
End of Report
Report written to awrinfo.txt SYSAUX并不大，而该SQL 的逻辑读达到了 328429442
给出下面的结果
set linesize 200 pagesize 2000
@?/rdbms/admin/sqltrpt
输入 14hgfdjtb88js Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 14hgfdjtb88js
Sql Id specified: 14hgfdjtb88js
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_15
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 01/22/2015 11:22:37
Completed at : 01/22/2015 11:26:40
-------------------------------------------------------------------------------
Schema Name: HR
SQL ID : 14hgfdjtb88js
SQL Text : with sn as (select DBID, STAT_ID, STAT_NAME from
DBA_HIST_STAT_NAME where STAT_NAME not in ('DB time', 'logons
current', 'opened cursors current', 'workarea memory allocated',
'session cursor cache count', 'session uga memory', 'session uga
memory max', 'session pga memory', 'session pga memory max') and
STAT_NAME not like '%wait time' and dbid = 1397357371), s as
(select distinct instance_number, stat_id from
dba_hist_service_stat where dbid = 1397357371 and snap_id = 1)
select b.stat_name st, e.value - b.value, round((e.value -
b.value)/2, 2), round((e.value - b.value)/2, 2) from
dba_hist_sysstat b, dba_hist_sysstat e where b.snap_id = 1 and
e.snap_id = 1 and b.dbid = 1397357371 and e.dbid = 1397357371
and b.instance_number = 1 and e.instance_number = 1 and
b.stat_id = e.stat_id and (e.stat_id, e.instance_number) not in
(select stat_id, instance_number from s) and e.stat_id in
(select stat_id from sn) and e.value >= b.value and e.value > 0
order by st
-------------------------------------------------------------------------------
FINDINGS SECTION (5 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Optimizer statistics for table "SYS"."WRH$_STAT_NAME" and its indices are
stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'WRH$_STAT_NAME', estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Statistics Finding
---------------------
Optimizer statistics for table "SYS"."WRH$_SERVICE_NAME" and its indices are
stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'WRH$_SERVICE_NAME', estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
3- Statistics Finding
---------------------
Optimizer statistics for table "SYS"."WRH$_SERVICE_STAT" and its indices are
stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'WRH$_SERVICE_STAT', estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
4- Statistics Finding
---------------------
Optimizer statistics for table "SYS"."WRH$_SYSSTAT" and its indices are
stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'WRH$_SYSSTAT', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
5- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.99%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_15',
task_owner => 'SYS', replace => TRUE); Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: PARTIAL COMPLETE
Elapsed Time (s): 30.910384 .003107 99.98 %
CPU Time (s): 30.378382 .003099 99.98 %
User I/O Time (s): 0 0
Buffer Gets: 20856026 781 99.99 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 0 161
Fetches: 0 161
Executions: 0 1
Notes
-----
1. Statistics for the original plan were averaged over 0 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2511151543
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 420 | 24232 (100)| 00:04:51 | | |
| 1 | SORT ORDER BY | | 7 | 420 | 24232 (100)| 00:04:51 | | |
| 2 | NESTED LOOPS | | 7 | 420 | 24230 (100)| 00:04:51 | | |
| 3 | NESTED LOOPS | | 308M| 420 | 24230 (100)| 00:04:51 | | |
| 4 | NESTED LOOPS | | 308M| 15G| 11665 (100)| 00:02:20 | | |
| 5 | MERGE JOIN CARTESIAN | | 308M| 13G| 7 (72)| 00:00:01 | | |
| 6 | MERGE JOIN CARTESIAN | | 443K| 17M| 4 (50)| 00:00:01 | | |
| 7 | NESTED LOOPS | | 653 | 23508 | 2 (0)| 00:00:01 | | |
| 8 | NESTED LOOPS | | 1 | 30 | 2 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | WRM$_SNAPSHOT | 1 | 15 | 1 (0)| 00:00:01 | | |
|* 10 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | WRM$_SNAPSHOT | 1 | 15 | 1 (0)| 00:00:01 | | |
|* 12 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | WRH$_STAT_NAME | 653 | 3918 | 0 (0)| 00:00:01 | | |
|* 14 | INDEX RANGE SCAN | WRH$_STAT_NAME_PK | 696 | | 0 (0)| 00:00:01 | | |
| 15 | BUFFER SORT | | 679 | 4074 | 4 (50)| 00:00:01 | | |
| 16 | PARTITION RANGE SINGLE | | 679 | 4074 | 0 (0)| 00:00:01 | 1 | 1 |
| 17 | TABLE ACCESS BY LOCAL INDEX ROWID| WRH$_SYSSTAT | 679 | 4074 | 0 (0)| 00:00:01 | 1 | 1 |
|* 18 | INDEX RANGE SCAN | WRH$_SYSSTAT_PK | 2377 | | 0 (0)| 00:00:01 | 1 | 1 |
| 19 | BUFFER SORT | | 696 | 4176 | 7 (72)| 00:00:01 | | |
|* 20 | INDEX RANGE SCAN | WRH$_STAT_NAME_PK | 696 | 4176 | 0 (0)| 00:00:01 | | |
| 21 | TABLE ACCESS BY INDEX ROWID | WRH$_STAT_NAME | 1 | 6 | 0 (0)| 00:00:01 | | |
|* 22 | INDEX UNIQUE SCAN | WRH$_STAT_NAME_PK | 1 | | 0 (0)| 00:00:01 | | |
| 23 | PARTITION RANGE SINGLE | | 1 | | 0 (0)| 00:00:01 | 1 | 1 |
|* 24 | INDEX UNIQUE SCAN | WRH$_SYSSTAT_PK | 1 | | 0 (0)| 00:00:01 | 1 | 1 |
| 25 | NESTED LOOPS | | 35 | 1155 | 1 (0)| 00:00:01 | | |
| 26 | NESTED LOOPS | | 1 | 27 | 1 (0)| 00:00:01 | | |
| 27 | NESTED LOOPS | | 1 | 21 | 1 (0)| 00:00:01 | | |
|* 28 | INDEX UNIQUE SCAN | WRH$_STAT_NAME_PK | 1 | 6 | 0 (0)| 00:00:01 | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | WRM$_SNAPSHOT | 1 | 15 | 1 (0)| 00:00:01 | | |
|* 30 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 31 | INDEX RANGE SCAN | WRH$_SERVICE_NAME_PK | 1 | 6 | 0 (0)| 00:00:01 | | |
| 32 | PARTITION RANGE SINGLE | | 35 | 210 | 0 (0)| 00:00:01 | 1 | 1 |
|* 33 | INDEX UNIQUE SCAN | WRH$_SERVICE_STAT_PK | 35 | 210 | 0 (0)| 00:00:01 | 1 | 1 |
|* 34 | TABLE ACCESS BY LOCAL INDEX ROWID | WRH$_SYSSTAT | 1 | 6 | 0 (0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("SN"."STATUS"=0)
10 - access("SN"."DBID"=1397357371 AND "SN"."SNAP_ID"=1 AND "SN"."INSTANCE_NUMBER"=1)
11 - filter("SN"."STATUS"=0)
12 - access("SN"."DBID"=1397357371 AND "SN"."SNAP_ID"=1 AND "SN"."INSTANCE_NUMBER"=1)
13 - filter("STAT_NAME" NOT LIKE '%wait time' AND "STAT_NAME"<>'DB time' AND "STAT_NAME"<>'logons current' AND
"STAT_NAME"<>'opened cursors current' AND "STAT_NAME"<>'workarea memory allocated' AND "STAT_NAME"<>'session cursor cache
count' AND "STAT_NAME"<>'session uga memory' AND "STAT_NAME"<>'session uga memory max' AND "STAT_NAME"<>'session pga
memory' AND "STAT_NAME"<>'session pga memory max')
14 - access("DBID"=1397357371)
18 - access("S"."DBID"=1397357371 AND "S"."SNAP_ID"=1 AND "S"."INSTANCE_NUMBER"=1)
20 - access("DBID"=1397357371)
22 - access("DBID"=1397357371 AND "S"."STAT_ID"="STAT_ID")
24 - access("S"."DBID"=1397357371 AND "S"."SNAP_ID"=1 AND "S"."INSTANCE_NUMBER"=1 AND "S"."STAT_ID"="STAT_ID")
filter("S"."STAT_ID"="S"."STAT_ID" AND "S"."STAT_ID"="STAT_ID" AND NOT EXISTS (SELECT /*+ PUSH_SUBQ OPT_ESTIMATE
(JOIN ("NM" "SN" "SV" "ST") SCALE_ROWS=112.000000 ) LEADING ("NM" "SN" "SV" "ST") INDEX ("NM" "WRH$_STAT_NAME_PK") USE_NL
("SV") INDEX ("SV" "WRH$_SERVICE_NAME_PK") OPT_ESTIMATE (TABLE "ST" SCALE_ROWS=4.000000 ) USE_NL ("ST") INDEX ("ST"
"WRH$_SERVICE_STAT_PK") USE_NL ("SN") INDEX_RS_ASC ("SN" "WRM$_SNAPSHOT_PK") */ 0 FROM SYS."WRH$_STAT_NAME"
"NM",SYS."WRH$_SERVICE_NAME" "SV",SYS."WRH$_SERVICE_STAT" "ST",SYS."WRM$_SNAPSHOT" "SN" WHERE "SN"."INSTANCE_NUMBER"=:B1
AND "SN"."SNAP_ID"=1 AND "SN"."DBID"=1397357371 AND "SN"."STATUS"=0 AND "ST"."STAT_ID"=:B2 AND
"ST"."SERVICE_NAME_HASH"="SV"."SERVICE_NAME_HASH" AND "ST"."INSTANCE_NUMBER"="SN"."INSTANCE_NUMBER" AND "ST"."SNAP_ID"=1
AND "ST"."DBID"=1397357371 AND "ST"."INSTANCE_NUMBER"=:B3 AND "ST"."STAT_ID"="NM"."STAT_ID" AND "SV"."DBID"=1397357371
AND "NM"."STAT_ID"=:B4 AND "NM"."DBID"=1397357371))
28 - access("NM"."DBID"=1397357371 AND "NM"."STAT_ID"=:B1)
29 - filter("SN"."STATUS"=0)
30 - access("SN"."DBID"=1397357371 AND "SN"."SNAP_ID"=1 AND "SN"."INSTANCE_NUMBER"=:B1)
31 - access("SV"."DBID"=1397357371)
33 - access("ST"."DBID"=1397357371 AND "ST"."SNAP_ID"=1 AND "ST"."INSTANCE_NUMBER"="SN"."INSTANCE_NUMBER" AND
"ST"."SERVICE_NAME_HASH"="SV"."SERVICE_NAME_HASH" AND "ST"."STAT_ID"=:B1)
filter("ST"."INSTANCE_NUMBER"=:B1 AND "ST"."STAT_ID"="NM"."STAT_ID")
34 - filter("VALUE">0 AND "VALUE">="VALUE")
2- Using SQL Profile
--------------------
Plan hash value: 2745711042
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15349 | 1019K| | 253 (1)| 00:00:04 | | |
| 1 | SORT ORDER BY | | 15349 | 1019K| 1216K| 253 (1)| 00:00:04 | | |
|* 2 | HASH JOIN | | 15349 | 1019K| | 3 (0)| 00:00:01 | | |
| 3 | TABLE ACCESS BY INDEX ROWID | WRH$_STAT_NAME | 696 | 4176 | | 0 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN | WRH$_STAT_NAME_PK | 696 | | | 0 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 15349 | 929K| | 3 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE SINGLE | | 4753 | 28518 | | 0 (0)| 00:00:01 | 1 | 1 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID | WRH$_SYSSTAT | 4753 | 28518 | | 0 (0)| 00:00:01 | 1 | 1 |
|* 8 | INDEX RANGE SCAN | WRH$_SYSSTAT_PK | 2377 | | | 0 (0)| 00:00:01 | 1 | 1 |
|* 9 | HASH JOIN | | 4380 | 239K| | 3 (0)| 00:00:01 | | |
|* 10 | INDEX RANGE SCAN | WRH$_STAT_NAME_PK | 696 | 4176 | | 0 (0)| 00:00:01 | | |
|* 11 | HASH JOIN ANTI | | 4380 | 213K| | 3 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 4569 | 187K| | 2 (0)| 00:00:01 | | |
| 13 | NESTED LOOPS | | 4569 | 187K| | 2 (0)| 00:00:01 | | |
| 14 | NESTED LOOPS | | 653 | 23508 | | 2 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS | | 1 | 30 | | 2 (0)| 00:00:01 | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | WRM$_SNAPSHOT | 1 | 15 | | 1 (0)| 00:00:01 | | |
|* 17 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 1 | | | 0 (0)| 00:00:01 | | |
|* 18 | TABLE ACCESS BY INDEX ROWID | WRM$_SNAPSHOT | 1 | 15 | | 1 (0)| 00:00:01 | | |
|* 19 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 1 | | | 0 (0)| 00:00:01 | | |
|* 20 | TABLE ACCESS BY INDEX ROWID | WRH$_STAT_NAME | 653 | 3918 | | 0 (0)| 00:00:01 | | |
|* 21 | INDEX RANGE SCAN | WRH$_STAT_NAME_PK | 696 | | | 0 (0)| 00:00:01 | | |
| 22 | PARTITION RANGE SINGLE | | 1 | | | 0 (0)| 00:00:01 | 1 | 1 |
|* 23 | INDEX UNIQUE SCAN | WRH$_SYSSTAT_PK | 1 | | | 0 (0)| 00:00:01 | 1 | 1 |
|* 24 | TABLE ACCESS BY LOCAL INDEX ROWID| WRH$_SYSSTAT | 7 | 42 | | 0 (0)| 00:00:01 | 1 | 1 |
| 25 | VIEW | VW_NSO_1 | 12544 | 98K| | 1 (0)| 00:00:01 | | |
| 26 | NESTED LOOPS | | 12544 | 404K| | 1 (0)| 00:00:01 | | |
| 27 | NESTED LOOPS | | 112 | 3024 | | 1 (0)| 00:00:01 | | |
| 28 | NESTED LOOPS | | 4 | 84 | | 1 (0)| 00:00:01 | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | WRM$_SNAPSHOT | 1 | 15 | | 1 (0)| 00:00:01 | | |
|* 30 | INDEX UNIQUE SCAN | WRM$_SNAPSHOT_PK | 1 | | | 0 (0)| 00:00:01 | | |
|* 31 | INDEX RANGE SCAN | WRH$_SERVICE_NAME_PK | 4 | 24 | | 0 (0)| 00:00:01 | | |
| 32 | PARTITION RANGE SINGLE | | 28 | 168 | | 0 (0)| 00:00:01 | 1 | 1 |
|* 33 | INDEX RANGE SCAN | WRH$_SERVICE_STAT_PK | 28 | 168 | | 0 (0)| 00:00:01 | 1 | 1 |
|* 34 | INDEX UNIQUE SCAN | WRH$_STAT_NAME_PK | 112 | 672 | | 0 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."STAT_ID"="STAT_ID" AND "S"."DBID"="DBID")
4 - access("DBID"=1397357371)
5 - access("S"."STAT_ID"="S"."STAT_ID" AND "S"."SNAP_ID"="SN"."SNAP_ID" AND "S"."DBID"="SN"."DBID" AND
"S"."INSTANCE_NUMBER"="SN"."INSTANCE_NUMBER")
filter("VALUE">="VALUE")
8 - access("S"."DBID"=1397357371 AND "S"."SNAP_ID"=1 AND "S"."INSTANCE_NUMBER"=1)
9 - access("S"."STAT_ID"="STAT_ID" AND "S"."DBID"="DBID")
10 - access("DBID"=1397357371)
11 - access("S"."STAT_ID"="STAT_ID" AND "S"."INSTANCE_NUMBER"="INSTANCE_NUMBER")
16 - filter("SN"."STATUS"=0)
17 - access("SN"."DBID"=1397357371 AND "SN"."SNAP_ID"=1 AND "SN"."INSTANCE_NUMBER"=1)
18 - filter("SN"."STATUS"=0)
19 - access("SN"."DBID"=1397357371 AND "SN"."SNAP_ID"=1 AND "SN"."INSTANCE_NUMBER"=1)
20 - filter("STAT_NAME" NOT LIKE '%wait time' AND "STAT_NAME"<>'DB time' AND "STAT_NAME"<>'logons current' AND
"STAT_NAME"<>'opened cursors current' AND "STAT_NAME"<>'workarea memory allocated' AND "STAT_NAME"<>'session cursor cache
count' AND "STAT_NAME"<>'session uga memory' AND "STAT_NAME"<>'session uga memory max' AND "STAT_NAME"<>'session pga memory'
AND "STAT_NAME"<>'session pga memory max')
21 - access("DBID"=1397357371)
23 - access("S"."DBID"=1397357371 AND "S"."SNAP_ID"=1 AND "S"."INSTANCE_NUMBER"=1 AND "S"."STAT_ID"="STAT_ID")
24 - filter("VALUE">0)
29 - filter("SN"."STATUS"=0)
30 - access("SN"."DBID"=1397357371 AND "SN"."SNAP_ID"=1 AND "SN"."INSTANCE_NUMBER"=1)
31 - access("SV"."DBID"=1397357371)
33 - access("ST"."DBID"=1397357371 AND "ST"."SNAP_ID"=1 AND "ST"."INSTANCE_NUMBER"=1 AND
"ST"."SERVICE_NAME_HASH"="SV"."SERVICE_NAME_HASH")
34 - access("NM"."DBID"=1397357371 AND "ST"."STAT_ID"="NM"."STAT_ID") 感谢ML，我按上面的建议跑下，看看再导出怎么样，谢谢
页:
[1]