Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › cpu used 100% when export AWR

0 积分	1 好友	1 主题

发消息

[升级] cpu used 100% when export AWR

1^#

发表于 2015-1-22 10:40:11 | 查看: 5496| 回复: 11

各位好：

昨天我在新环境上安装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上貌似找不到此问题解决办法，请大家给个解决思路，谢谢

分享0

收藏0 回复只看该作者道具举报

Liu Maclean(刘相兵

2^#

发表于 2015-1-22 10:42:06

获取此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;

复制代码

回复只看该作者道具举报

successwang

3^#

发表于 2015-1-22 10:53:00

上面跑的结果如下：
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

回复只看该作者道具举报

Liu Maclean(刘相兵

4^#

发表于 2015-1-22 10:59:12

给出如下脚本的信息：

@?/rdbms/admin/awrinfo

回复只看该作者道具举报

successwang

5^#

发表于 2015-1-22 11:02:59

附件已上传，请查看

awrinfo.txt

28.75 KB, 下载次数: 1198

回复只看该作者道具举报

successwang

6^#

发表于 2015-1-22 11:06:14

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

回复只看该作者道具举报

successwang

7^#

发表于 2015-1-22 11:06:37

**********************************
(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

回复只看该作者道具举报

Liu Maclean(刘相兵

8^#

发表于 2015-1-22 11:19:42

SYSAUX并不大，而该SQL 的逻辑读达到了 328429442

给出下面的结果

set linesize 200 pagesize 2000

@?/rdbms/admin/sqltrpt

输入 14hgfdjtb88js

回复只看该作者道具举报

successwang

9^#

发表于 2015-1-22 11:27:43

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);

回复只看该作者道具举报

successwang

10^#

发表于 2015-1-22 11:29:00

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")

回复只看该作者道具举报

successwang

11^#

发表于 2015-1-22 11:29:16

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")

回复只看该作者道具举报

successwang

12^#

发表于 2015-1-22 11:33:46

感谢ML，我按上面的建议跑下，看看再导出怎么样，谢谢

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册

[升级] cpu used 100% when export AWR

浏览过的版块