天行健，君子以自强不息

Oracle性能分析入门学习中，遇到Oracle数据库的性能问题，一般首要的步骤就是导出AWR的分析报告，AWR是10g中新引入的一个工具，在这之前一般是利用statspack。要导出AWR报告，只要利用Oracle的一个脚本即可以完成，下面是我利用script录制的一段导出AWR report的过程。
首先进入$ORACLE_HOME/rdbms/admin目录，在sqlplus下运行@awrrpt脚本，按照提示一步一步就可以达成，导出的格式有两种，txt格式和html格式。

在OTN上，有一个DBA新feature系列，第六周就是专门讲的这个feature，大家可以去那里了解一下（中文的）

Script started on Sun 27 Jan 2008 08:47:15 PM PST
[ractest@sun880-1 /u01/app/oracle/orahome/rdbms/admin]$ cd $ORACLE_HOME/rdbms/admin

[ractest@sun880-1 /u01/app/oracle/orahome/rdbms/admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Jan 27 20:47:38 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> @awrrpt

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
———– ———— ——– ————
2080780909 ORCLDB 1 orcldb1

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: text

Type Specified: text

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
———— ——– ———— ———— ————
* 2080780909 1 ORCLDB orcldb1 sun880-1
2080780909 4 ORCLDB orcldb4 sun880-4
2080780909 2 ORCLDB orcldb2 sun880-2
2080780909 3 ORCLDB orcldb3 sun880-3

Using 2080780909 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. Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day’s Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
orcldb1 ORCLDB 66 27 Jan 2008 00:55 1
67 27 Jan 2008 01:55 1
68 27 Jan 2008 02:55 1
69 27 Jan 2008 03:55 1
70 27 Jan 2008 04:55 1
71 27 Jan 2008 05:55 1
72 27 Jan 2008 06:55 1
73 27 Jan 2008 07:55 1
74 27 Jan 2008 08:55 1
75 27 Jan 2008 09:55 1
76 27 Jan 2008 10:55 1
77 27 Jan 2008 11:55 1
78 27 Jan 2008 12:55 1

83 27 Jan 2008 17:54 1
84 27 Jan 2008 18:55 1
85 27 Jan 2008 19:55 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 70
Begin Snapshot Id specified: 70

Enter value for end_snap: 71
End Snapshot Id specified: 71

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_70_71.txt. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_70_71.txt

WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Release RAC Host
———— ———– ———— ——– ———– — ————
ORCLDB 2080780909 orcldb1 1 10.2.0.4.0 YES sun880-1

Snap Id Snap Time Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap: 70 27-Jan-08 04:55:07 136 16.2
End Snap: 71 27-Jan-08 05:55:21 141 16.5
Elapsed: 60.23 (mins)
DB Time: 5,814.07 (mins)

Cache Sizes
~~~~~~~~~~~ Begin End
———- ———-
Buffer Cache: 1,152M 1,152M Std Block Size: 8K
Shared Pool Size: 336M 336M Log Buffer: 4,224K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
————— —————
Redo size: 621.24 605.10
Logical reads: 11.21 10.92
Block changes: 1.76 1.71
Physical reads: 0.03 0.03
Physical writes: 0.30 0.29
User calls: 2.68 2.61
Parses: 0.77 0.75
Hard parses: 0.00 0.00
Sorts: 0.52 0.51
Logons: 0.07 0.06
Executes: 1.46 1.42
Transactions: 1.03

% Blocks changed per Read: 15.70 Recursive Call %: 73.54
Rollback per transaction %: 98.79 Rows per Sort: 201.77

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: 99.74 In-memory Sort %: 100.00
Library Hit %: 99.94 Soft Parse %: 99.89
Execute to Parse %: 47.18 Latch Hit %: 99.94
Parse CPU to Parse Elapsd %: 119.35 % Non-Parse CPU: 99.37

Shared Pool Statistics Begin End
—— ——
Memory Usage %: 81.79 81.87
% SQL with executions>1: 93.14 89.92
% Memory for SQL w/exec>1: 84.67 82.14

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
—————————— ———— ———– —— —— ———-
reliable message 355,981 349,336 981 100.1 Other
CPU time 59 0.0
Streams AQ: qmn coordinator wa 7 40 5660 0.0 Other
DFS lock handle 11,223 14 1 0.0 Other
control file sequential read 8,314 8 1 0.0 System I/O
————————————————————-
RAC Statistics DB/Inst: ORCLDB/orcldb1 Snaps: 70-71

Begin End
—– —–
Number of Instances: 4 4

Global Cache Load Profile
~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction
————— —————
Global Cache blocks received: 0.27 0.26
Global Cache blocks served: 0.25 0.24
GCS/GES messages received: 10.08 9.82
GCS/GES messages sent: 11.30 11.01
DBWR Fusion writes: 0.02 0.02
Estd Interconnect traffic (KB) 8.34

Global Cache Efficiency Percentages (Target local+remote 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer access - local cache %: 97.34
Buffer access - remote cache %: 2.40
Buffer access - disk %: 0.26

Global Cache and Enqueue Services - Workload Characteristics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg global enqueue get time (ms): 0.9

Avg global cache cr block receive time (ms): 2.2
Avg global cache current block receive time (ms): 3.7

Avg global cache cr block build time (ms): 0.1
Avg global cache cr block send time (ms): 0.2
Global cache log flushes for cr blocks served %: 4.1
Avg global cache cr block flush time (ms): 3.1

Avg global cache current block pin time (ms): 0.1
Avg global cache current block send time (ms): 0.3
Global cache log flushes for current blocks served %: 4.4
Avg global cache current block flush time (ms): 3.5

Global Cache and Enqueue Services - Messaging Statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg message sent queue time (ms): 0.1
Avg message sent queue time on ksxp (ms): 0.4
Avg message received queue time (ms): 0.0
Avg GCS message process time (ms): 0.1
Avg GES message process time (ms): 0.0

% of direct sent messages: 52.96
% of indirect sent messages: 37.74
% of flow controlled messages: 9.30
————————————————————-

Time Model Statistics DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Total time in database user-calls (DB Time): 348844.4s
-> Statistics including the word “background” measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name

Statistic Name Time (s) % of DB Time
—————————————— —————— ————
sql execute elapsed time 348,840.8 100.0
PL/SQL execution elapsed time 348,833.5 100.0
DB CPU 59.0 .0
parse time elapsed 0.3 .0
connection management call elapsed time 0.1 .0
repeated bind elapsed time 0.0 .0
DB time 348,844.4 N/A
background elapsed time 1,219.8 N/A
background cpu time 88.3 N/A
————————————————————-

Wait Class DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc

Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
——————– —————- —— —————- ——- ———
Other 413,892 96.2 349,397 844 111.6
System I/O 10,536 .0 11 1 2.8
Cluster 1,278 .0 3 2 0.3
Concurrency 204 .0 3 14 0.1
Configuration 879 15.9 1 1 0.2
Application 740 .0 0 0 0.2
User I/O 114 .0 0 1 0.0
Commit 30 .0 0 2 0.0
Network 8,509 .0 0 0 2.3
————————————————————-

Wait Events DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
—————————- ————– —— ———– ——- ———
reliable message 355,981 99.5 349,336 981 96.0
Streams AQ: qmn coordinator 7 100.0 40 5660 0.0
DFS lock handle 11,223 .0 14 1 3.0
control file sequential read 8,314 .0 8 1 2.2
os thread startup 15 .0 2 152 0.0
latch free 914 .0 2 2 0.2
CGS wait for IPC msg 32,599 100.0 2 0 8.8
control file parallel write 1,200 .0 1 1 0.3
gc current block busy 35 .0 1 24 0.0
name-service call wait 8 .0 1 80 0.0
enq: HW - contention 733 .0 1 1 0.2
enq: PS - contention 359 .0 1 2 0.1
gc current block 2-way 325 .0 1 2 0.1
gc cr block 2-way 320 .0 1 2 0.1
IPC send completion sync 613 100.0 0 1 0.2
enq: US - contention 491 .0 0 1 0.1
db file parallel write 753 .0 0 1 0.2
log file parallel write 269 .0 0 2 0.1
enq: WF - contention 20 .0 0 20 0.0
row cache lock 113 .0 0 3 0.0
gc current block 3-way 116 .0 0 2 0.0
wait for scn ack 231 .0 0 1 0.1
enq: TM - contention 17 .0 0 15 0.0
ksxr poll remote instances 3,299 85.5 0 0 0.9
gc cr block 3-way 100 .0 0 2 0.0
PX Deq: reap credit 7,802 99.2 0 0 2.1
gc current grant busy 167 .0 0 1 0.0
db file sequential read 112 .0 0 1 0.0
gc current retry 4 .0 0 34 0.0
gc cr block busy 21 .0 0 5 0.0
gcs log flush sync 85 25.9 0 1 0.0
SQL*Net break/reset to clien 722 .0 0 0 0.2
enq: JQ - contention 36 .0 0 2 0.0
library cache lock 62 .0 0 1 0.0
log file sync 30 .0 0 2 0.0
gc cr grant 2-way 102 .0 0 1 0.0
rdbms ipc reply 122 .0 0 0 0.0
enq: TA - contention 23 .0 0 2 0.0
SQL*Net message to client 7,366 .0 0 0 2.0
library cache pin 9 .0 0 4 0.0
SQL*Net more data from clien 1,101 .0 0 0 0.3
enq: TT - contention 29 .0 0 1 0.0
gc current multi block reque 75 .0 0 0 0.0
enq: TQ - DDL contention 4 .0 0 2 0.0
PX Deq Credit: send blkd 23 56.5 0 0 0.0
enq: JS - job run lock - syn 2 .0 0 3 0.0
enq: FB - contention 6 .0 0 1 0.0
enq: PD - contention 4 .0 0 1 0.0
gc buffer busy 7 .0 0 1 0.0
enq: TX - allocate ITL entry 4 .0 0 1 0.0
gc cr multi block request 2 .0 0 1 0.0
gc current grant 2-way 4 .0 0 1 0.0
undo segment extension 142 98.6 0 0 0.0
SQL*Net more data to client 42 .0 0 0 0.0
enq: PI - contention 2 .0 0 1 0.0
enq: AF - task serialization 1 .0 0 2 0.0
enq: PW - flush prewarm buff 1 .0 0 1 0.0
direct path write 2 .0 0 0 0.0
buffer busy waits 4 .0 0 0 0.0
PX Deq: Signal ACK 3 66.7 0 0 0.0
Wait Events DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
—————————- ————– —— ———– ——- ———
lock escalate retry 5 80.0 0 0 0.0
latch: cache buffers chains 1 .0 0 0 0.0
SQL*Net message from client 7,365 .0 19,180 2604 2.0
PX Deq: Execution Msg 4,124 79.0 7,059 1712 1.1
gcs remote message 187,534 99.7 7,046 38 50.5
PX Idle Wait 1,843 87.8 4,347 2359 0.5
Streams AQ: qmn slave idle w 170 4.1 4,256 25034 0.0
DIAG idle wait 17,179 .0 3,527 205 4.6
ASM background timer 721 .0 3,526 4890 0.2
Streams AQ: waiting for mess 379 95.3 3,524 9299 0.1
ges remote message 68,711 96.6 3,521 51 18.5
virtual circuit status 120 100.0 3,513 29272 0.0
Streams AQ: waiting for time 18 .0 3,475 193079 0.0
Streams AQ: qmn coordinator 263 48.7 3,467 13181 0.1
class slave wait 39 76.9 3,465 88834 0.0
jobq slave wait 40 100.0 118 2938 0.0
PX Deq: Msg Fragment 466 30.7 0 0 0.1
PX Deq: Join ACK 53 45.3 0 1 0.0
PX Deq: Parse Reply 50 46.0 0 0 0.0
Streams AQ: RAC qmn coordina 263 100.0 0 0 0.1
PX Deq: Execute Reply 25 44.0 0 0 0.0
————————————————————-

Background Wait Events DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> ordered by wait time desc, waits desc (idle events last)

Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
—————————- ————– —— ———– ——- ———
events in waitclass Other 50,415 73.0 1,126 22 13.6
control file sequential read 7,477 .0 8 1 2.0
os thread startup 15 .0 2 152 0.0
control file parallel write 1,200 .0 1 1 0.3
enq: HW - contention 731 .0 1 1 0.2
log file parallel write 272 .0 0 2 0.1
db file parallel write 753 .0 0 1 0.2
row cache lock 110 .0 0 3 0.0
gc current block 2-way 86 .0 0 2 0.0
gc cr block 2-way 84 .0 0 2 0.0
gc cr block 3-way 28 .0 0 2 0.0
gc current block 3-way 26 .0 0 2 0.0
enq: TM - contention 12 .0 0 3 0.0
gc current retry 1 .0 0 34 0.0
library cache lock 24 .0 0 1 0.0
db file sequential read 3 .0 0 6 0.0
gc current multi block reque 62 .0 0 0 0.0
gc current block busy 2 .0 0 5 0.0
gc cr block busy 2 .0 0 3 0.0
library cache pin 2 .0 0 3 0.0
enq: TX - allocate ITL entry 4 .0 0 1 0.0
gc cr multi block request 2 .0 0 1 0.0
gc current grant 2-way 4 .0 0 1 0.0
gc cr grant 2-way 1 .0 0 1 0.0
gc buffer busy 1 .0 0 0 0.0
buffer busy waits 1 .0 0 0 0.0
gc current grant busy 30 .0 -0 -0 0.0
rdbms ipc message 52,576 93.9 48,076 914 14.2
gcs remote message 187,560 99.6 7,046 38 50.6
DIAG idle wait 17,180 .0 3,528 205 4.6
ASM background timer 721 .0 3,526 4890 0.2
ges remote message 68,735 96.6 3,521 51 18.5
pmon timer 1,224 100.0 3,521 2877 0.3
smon timer 736 .0 3,506 4763 0.2
Streams AQ: waiting for time 18 .0 3,475 193079 0.0
Streams AQ: qmn coordinator 263 48.7 3,467 13181 0.1
class slave wait 30 100.0 3,465 115483 0.0
PX Deq: Join ACK 53 45.3 0 1 0.0
PX Deq: Parse Reply 50 46.0 0 0 0.0
Streams AQ: RAC qmn coordina 263 100.0 0 0 0.1
PX Deq: Execute Reply 25 44.0 0 0 0.0
————————————————————-

Operating System Statistics DB/Inst: ORCLDB/orcldb1 Snaps: 70-71

Statistic Total
——————————– ——————–
AVG_BUSY_TIME 30,425
AVG_IDLE_TIME 330,863
AVG_IOWAIT_TIME 0
AVG_SYS_TIME 17,869
AVG_USER_TIME 12,453
BUSY_TIME 122,068
IDLE_TIME 1,323,841
IOWAIT_TIME 0
SYS_TIME 71,868
USER_TIME 50,200
LOAD 0
OS_CPU_WAIT_TIME 13,900
RSRC_MGR_CPU_WAIT_TIME 0
VM_IN_BYTES 565,248
VM_OUT_BYTES 0
PHYSICAL_MEMORY_BYTES 8,508,891,136
NUM_CPUS 4
————————————————————-

Service Statistics DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> ordered by DB Time

Physical Logical
Service Name DB Time (s) DB CPU (s) Reads Reads
——————————– ———— ———— ———- ———-
SYS$USERS 348,499.2 57.6 0 4,139
orcldb 2.9 1.2 1 3,436
SYS$BACKGROUND 0.0 0.0 103 32,414
orcldbXDB 0.0 0.0 0 0
————————————————————-

Service Wait Class Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Wait Class info for services in the Service Statistics section.
-> Total Waits and Time Waited displayed for the following wait
classes: User I/O, Concurrency, Administrative, Network
-> Time Waited (Wt Time) in centisecond (100th of a second)

Service Name
—————————————————————-
User I/O User I/O Concurcy Concurcy Admin Admin Network Network
Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time
——— ——— ——— ——— ——— ——— ——— ———
SYS$USERS
0 0 46 9 0 0 8153 7
orcldb
1 3 1 0 0 0 290 0
SYS$BACKGROUND
113 11 157 268 0 0 0 0
————————————————————-

SQL ordered by Elapsed Time DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100

Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
———- ———- ———— ———- ——- ————-
347,920 50 0 N/A 99.7 8qcqkskshyyqq
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ BEGIN dbms_server_alert.set_threshold(:metrics_id,
:warning_operator, :warning_value,
:critical_operator, :critical_value,
1, :consecutive_occurrences,

4 4 1 3.8 0.0 bunssq950snhf
insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZ
E, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbi
d, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R
EADS from v$sga_target_advice

3 1 13 0.2 0.0 bn30dmqukmymf
Module: OEM.CacheModeWaitPool
BEGIN MGMT_JOB_ENGINE.update_step_status(:1,:2,:3); END;

1 0 39 0.0 0.0 642qudg0p507b
Module: OEM.CacheModeWaitPool
UPDATE MGMT_JOB_EXECUTION SET STEP_STATUS = :B3 , STEP_STATUS_CODE=:B2 , END_TIM
E=CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE), DISPATCHER_ID=-1 WHERE STEP_ID=:B
1 RETURNING END_TIME INTO :O0

1 1 1 1.0 0.0 d92h3rjp0y217
begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end;

1 1 484 0.0 0.0 dj1sv9×4jwtrt
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ select metric_name from v$metricname where metric_i
d = :metrics_id

1 1 536 0.0 0.0 8ac2h3qfqhnxw
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ select metrics_id from v$threshold_types where aler
t_reason_id = :reason_id

1 1 3 0.3 0.0 8vkjh60z1g287
select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_
SCHLIM from ( select a.obj# OBJOID, a.class_oid CLSOID, decode(bitand(a.flags
, 16384), 0, a.next_run_date, a.last_enabled_time) RUNTIME, (2*a.priority +
decode(bitand(a.job_status, 4), 0, 0, decode(a.running_instance, :

1 0 1 0.7 0.0 c2aw4fq306q4k
INSERT /*+ APPEND */ INTO WRH$_ACTIVE_SESSION_HISTORY ( snap_id, dbid, instance_
number, sample_id, sample_time, session_id, session_serial#, user_id, sql_
id, sql_child_number, sql_plan_hash_value, force_matching_signature, service_
hash, session_type, flags , sql_opcode, plsql_entry_object_id, plsql

1 0 1 0.5 0.0 84qubbrsr0kfn
insert into wrh$_latch (snap_id, dbid, instance_number, latch_hash, level#, ge
ts, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, s
leep2, sleep3, sleep4, wait_time) select :snap_id, :dbid, :instance_number,
hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_ge

————————————————————-

SQL ordered by CPU Time DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100

CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
———- ———- ———— ———– ——- ————-
50 347,920 0 N/A 99.7 8qcqkskshyyqq
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ BEGIN dbms_server_alert.set_threshold(:metrics_id,
:warning_operator, :warning_value,
:critical_operator, :critical_value,
1, :consecutive_occurrences,

4 4 1 3.77 0.0 bunssq950snhf
insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZ
E, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbi
d, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R
EADS from v$sga_target_advice

1 1 536 0.00 0.0 8ac2h3qfqhnxw
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ select metrics_id from v$threshold_types where aler
t_reason_id = :reason_id

1 1 484 0.00 0.0 dj1sv9×4jwtrt
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ select metric_name from v$metricname where metric_i
d = :metrics_id

1 1 3 0.30 0.0 8vkjh60z1g287
select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_
SCHLIM from ( select a.obj# OBJOID, a.class_oid CLSOID, decode(bitand(a.flags
, 16384), 0, a.next_run_date, a.last_enabled_time) RUNTIME, (2*a.priority +
decode(bitand(a.job_status, 4), 0, 0, decode(a.running_instance, :

1 1 1 0.77 0.0 d92h3rjp0y217
begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end;

1 3 13 0.06 0.0 bn30dmqukmymf
Module: OEM.CacheModeWaitPool
BEGIN MGMT_JOB_ENGINE.update_step_status(:1,:2,:3); END;

0 1 1 0.49 0.0 84qubbrsr0kfn
insert into wrh$_latch (snap_id, dbid, instance_number, latch_hash, level#, ge
ts, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, s
leep2, sleep3, sleep4, wait_time) select :snap_id, :dbid, :instance_number,
hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_ge

0 0 685 0.00 0.0 6ssrk2dqj7jbx
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 < = next_date) and (n
ext_date <= :2)) or ((last_date is null) and (next_date < :3))) and (field1
= :4 or (field1 = 0 and 'Y' = :5)) and (this_date is null) order by next_date, j
ob

0 1 1 0.36 0.0 c2aw4fq306q4k
INSERT /*+ APPEND */ INTO WRH$_ACTIVE_SESSION_HISTORY ( snap_id, dbid, instance_
number, sample_id, sample_time, session_id, session_serial#, user_id, sql_
id, sql_child_number, sql_plan_hash_value, force_matching_signature, service_
hash, session_type, flags , sql_opcode, plsql_entry_object_id, plsql

-------------------------------------------------------------

SQL ordered by Gets DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total Buffer Gets: 40,516
-> Captured SQL account for 49.2% of Total

Gets CPU Elapsed
Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id
————– ———— ———— —— ——– ——— ————-
4,816 1 4,816.0 11.9 0.14 0.15 b7jn4mf49n569
select o.name, u.name from obj$ o, type$ t, user$ u where o.oid$ = t.tvoid and
u.user#=o.owner# and bitand(t.properties,8388608) = 8388608 and (sysdate-o.ctim
e) > 0.0007

3,782 3 1,260.7 9.3 0.89 0.91 8vkjh60z1g287
select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_
SCHLIM from ( select a.obj# OBJOID, a.class_oid CLSOID, decode(bitand(a.flags
, 16384), 0, a.next_run_date, a.last_enabled_time) RUNTIME, (2*a.priority +
decode(bitand(a.job_status, 4), 0, 0, decode(a.running_instance, :

3,269 1 3,269.0 8.1 0.77 1.00 d92h3rjp0y217
begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end;

2,724 13 209.5 6.7 0.76 2.63 bn30dmqukmymf
Module: OEM.CacheModeWaitPool
BEGIN MGMT_JOB_ENGINE.update_step_status(:1,:2,:3); END;

2,055 685 3.0 5.1 0.38 0.38 6ssrk2dqj7jbx
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 < = next_date) and (n
ext_date <= :2)) or ((last_date is null) and (next_date < :3))) and (field1
= :4 or (field1 = 0 and 'Y' = :5)) and (this_date is null) order by next_date, j
ob

1,288 12 107.3 3.2 0.14 0.22 c7sn076yz7030
select smontabv.cnt, smontab.time_mp, smontab.scn, smontab.num_mappings, smon
tab.tim_scn_map, smontab.orig_thread from smon_scn_time smontab, (sel
ect max(scn) scnmax, count(*)+sum(NVL2(TIM_SCN_MAP,NUM_MAPPINGS,
0)) cnt from smon_scn_time where thread=0) smontabv where smon

1,072 1 1,072.0 2.6 0.27 0.36 0dwr2vd6vbqzs
SELECT count(*) over () as total_count, sd_xe_ash_nm.event_name, s
d_xe_ash_nm.event_id, sd_xe_ash_nm.parameter1 as p1text, (CASE WHE
N (sd_xe_ash_nm.parameter1 is NULL OR sd_x
e_ash_nm.parameter1 = '0') THEN 0 ELSE 1 END)

1,017 1 1,017.0 2.5 0.36 0.74 c2aw4fq306q4k
INSERT /*+ APPEND */ INTO WRH$_ACTIVE_SESSION_HISTORY ( snap_id, dbid, instance_
number, sample_id, sample_time, session_id, session_serial#, user_id, sql_
id, sql_child_number, sql_plan_hash_value, force_matching_signature, service_
hash, session_type, flags , sql_opcode, plsql_entry_object_id, plsql

808 4 202.0 2.0 0.24 0.28 cutru1gtsj5hq
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ select metrics_name, object_name from dba_thresholds
where object_name is not null

720 720 1.0 1.8 0.21 0.21 g2wr3u7s1gtf3
select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd
ate+5/86400))

615 12 51.3 1.5 0.24 0.53 7qrr8supfkjh4
Module: OEM.CacheModeWaitPool
BEGIN :1 := MGMT_JOB_ENGINE.execute_param_sources(:2,:3,:4); END;

574 12 47.8 1.4 0.06 0.20 czt1p4jaq1s0n
Module: OEM.CacheModeWaitPool
UPDATE MGMT_JOB_EXECUTION SET STEP_STATUS=:B2 , START_TIME=:B1 WHERE STEP_ID IN
( SELECT STEP_ID FROM MGMT_JOB_EXECUTION START WITH STEP_ID=:B3 CONNECT BY STEP_
ID=PRIOR PARENT_STEP_ID)
SQL ordered by Gets DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total Buffer Gets: 40,516
-> Captured SQL account for 49.2% of Total

Gets CPU Elapsed
Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id
————– ———— ———— —— ——– ——— ————-

517 39 13.3 1.3 0.16 1.29 642qudg0p507b
Module: OEM.CacheModeWaitPool
UPDATE MGMT_JOB_EXECUTION SET STEP_STATUS = :B3 , STEP_STATUS_CODE=:B2 , END_TIM
E=CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE), DISPATCHER_ID=-1 WHERE STEP_ID=:B
1 RETURNING END_TIME INTO :O0

————————————————————-

SQL ordered by Reads DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Total Disk Reads: 105
-> Captured SQL account for 93.3% of Total

Reads CPU Elapsed
Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id
————– ———– ————- —— ——– ——— ————-
97 1 97.0 92.4 0.77 1.00 d92h3rjp0y217
begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end;

95 1 95.0 90.5 0.27 0.36 0dwr2vd6vbqzs
SELECT count(*) over () as total_count, sd_xe_ash_nm.event_name, s
d_xe_ash_nm.event_id, sd_xe_ash_nm.parameter1 as p1text, (CASE WHE
N (sd_xe_ash_nm.parameter1 is NULL OR sd_x
e_ash_nm.parameter1 = ‘0′) THEN 0 ELSE 1 END)

2 12 0.2 1.9 0.14 0.22 c7sn076yz7030
select smontabv.cnt, smontab.time_mp, smontab.scn, smontab.num_mappings, smon
tab.tim_scn_map, smontab.orig_thread from smon_scn_time smontab, (sel
ect max(scn) scnmax, count(*)+sum(NVL2(TIM_SCN_MAP,NUM_MAPPINGS,
0)) cnt from smon_scn_time where thread=0) smontabv where smon

1 24 0.0 1.0 0.03 0.03 3m8smr0v7v1m6
INSERT INTO sys.wri$_adv_message_groups (task_id,id,seq,message#,fac,hdr,lm,nl,p
1,p2,p3,p4,p5) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)

1 13 0.1 1.0 0.76 2.63 bn30dmqukmymf
Module: OEM.CacheModeWaitPool
BEGIN MGMT_JOB_ENGINE.update_step_status(:1,:2,:3); END;

0 1 0.0 0.0 0.00 0.00 0cn2wm9d7zq8d
SELECT decode(value,’FATAL’,1,’ERROR’,2,’WARNING’,3,’INFORMATION’,4,’INFORMATION
2′,5,’INFORMATION3′,6,’INFORMATION4′,7,’INFORMATION5′,8,’INFORMATION6′,9,0) FROM
sys.wri$_adv_parameters WHERE task_id = :1 AND name = ‘JOURNALING’

0 15 0.0 0.0 0.01 0.01 0ggmpn2j97hka
select a.obj# OBJOID, a.class_oid CLSOID, (2*a.priority + decod
e(bitand(a.job_status, 4), 0, 0, decode(a.running_instance, :1,
-1, 1))) PRI, a.job_weight WT, decode(a.running_instance, NULL,
0, a.running_instance) INST, decode(bitand(a.job_status, 8388608), 0, 0,

0 20 0.0 0.0 0.01 0.01 0h6b2sajwb74n
select privilege#,level from sysauth$ connect by grantee#=prior privilege# and p
rivilege#>0 start with grantee#=:1 and privilege#>0

0 19 0.0 0.0 0.01 0.01 0k8522rmdzg4k
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#
>0

0 15 0.0 0.0 0.01 0.01 0ws7ahf1d78qa
select SYS_CONTEXT(’USERENV’, ‘SERVER_HOST’), SYS_CONTEXT(’USERENV’, ‘DB_UNIQUE_
NAME’), SYS_CONTEXT(’USERENV’, ‘INSTANCE_NAME’), SYS_CONTEXT(’USERENV’, ‘SERVICE
_NAME’), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT(’USERENV’, ‘DB_DOMAIN’) from
v$instance where INSTANCE_NAME=SYS_CONTEXT(’USERENV’, ‘INSTANCE_NAME’)

————————————————————-

SQL ordered by Executions DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Total Executions: 5,259
-> Captured SQL account for 68.9% of Total

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id
———— ————— ————– ———- ———– ————-
720 720 1.0 0.00 0.00 g2wr3u7s1gtf3
select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd
ate+5/86400))

685 0 0.0 0.00 0.00 6ssrk2dqj7jbx
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n
ext_date <= :2)) or ((last_date is null) and (next_date < :3))) and (field1
= :4 or (field1 = 0 and 'Y' = :5)) and (this_date is null) order by next_date, j
ob

536 484 0.9 0.00 0.00 8ac2h3qfqhnxw
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ select metrics_id from v$threshold_types where aler
t_reason_id = :reason_id

484 644 1.3 0.00 0.00 dj1sv9x4jwtrt
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ select metric_name from v$metricname where metric_i
d = :metrics_id

125 125 1.0 0.00 0.00 6610ftkf71s85
Module: OEM.BoundedPool
UPDATE MGMT_JOB_HISTORY SET JOB_ID=:B22 , EXECUTION_ID=:B21 , STEP_ID=:B20 , SOU
RCE_STEP_ID=:B19 , ORIGINAL_STEP_ID=:B18 , RESTART_MODE=:B17 , STEP_NAME=:B16 ,
STEP_TYPE=:B15 , COMMAND_TYPE=:B14 , ITERATE_PARAM=:B13 , ITERATE_PARAM_INDEX=:B
12 , PARENT_STEP_ID=:B11 , STEP_STATUS=:B10 , STEP_STATUS_CODE=:B9 , NUM_CHILDRE

119 2,499 21.0 0.00 0.00 aykvshm7zsabd
select size_for_estimate, size_factor * 100 f,
estd_physical_read_time, estd_physical_reads
from v$db_cache_advice where id = '3'

99 0 0.0 0.00 0.00 350f5yrnnmshs
lock table sys.mon_mods$ in exclusive mode nowait

99 99 1.0 0.00 0.00 g00cj285jmgsw
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
+ :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn

69 3 0.0 0.00 0.00 c4nhd1ntptxq7
select message_level, sequence_id, time_suggested from WRI$_ALERT_OUTSTANDING wh
ere reason_id = :1 and object_id = :2 and subobject_id = :3 and internal_instanc
e_number = :4

64 536 8.4 0.00 0.00 7gskff4xc7qb7
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ select reason_id, internal_metric_category, interna
l_metric_name, scope, type, object_type from v$alert_types where int
ernal_metric_category = :1

-------------------------------------------------------------

SQL ordered by Parse Calls DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Total Parse Calls: 2,778
-> Captured SQL account for 71.0% of Total

% Total
Parse Calls Executions Parses SQL Id
———— ———— ——— ————-
536 536 19.29 8ac2h3qfqhnxw
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ select metrics_id from v$threshold_types where aler
t_reason_id = :reason_id

484 484 17.42 dj1sv9×4jwtrt
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ select metric_name from v$metricname where metric_i
d = :metrics_id

181 0 6.52 7frqszzdu8sgg
Module: racgimon@sun880-2 (TNS V1-V3)
select DECODE(UPPER(d.OPEN_MODE),’READ WRITE’,'R’,'N’) ,DECODE(UPPER(i.STATUS),’
OPEN’,'O’,'N’) into :b0,:b1 from v$database d ,gv$instance i where i.INSTANCE_N
AME=:b2

99 99 3.56 350f5yrnnmshs
lock table sys.mon_mods$ in exclusive mode nowait

99 99 3.56 g00cj285jmgsw
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
+ :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn

69 69 2.48 c4nhd1ntptxq7
select message_level, sequence_id, time_suggested from WRI$_ALERT_OUTSTANDING wh
ere reason_id = :1 and object_id = :2 and subobject_id = :3 and internal_instanc
e_number = :4

64 64 2.30 7gskff4xc7qb7
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ select reason_id, internal_metric_category, interna
l_metric_name, scope, type, object_type from v$alert_types where int
ernal_metric_category = :1

47 47 1.69 bsa0wjtftg3uw
select file# from file$ where ts#=:1

37 37 1.33 43c5ykm1mcp2a
Module: OMS
begin dbms_application_info.set_module(:1, :2); dbms_application_info.set_client
_info(:3); dbms_session.set_identifier(:4); end;

37 37 1.33 a4ct9tx8f9d4a
Module: OMS
begin execute immediate ‘alter session set NLS_NUMERIC_CHARACTERS = ”.,”’; end
;

37 37 1.33 bwhzpth8a3zgv
Module: OMS
select instance_number, instance_name, thread#, version, startup_time from v$ins
tance

36 685 1.30 6ssrk2dqj7jbx
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 < = next_date) and (n
ext_date <= :2)) or ((last_date is null) and (next_date < :3))) and (field1
= :4 or (field1 = 0 and 'Y' = :5)) and (this_date is null) order by next_date, j
ob

SQL ordered by Parse Calls DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Total Parse Calls: 2,778
-> Captured SQL account for 71.0% of Total

% Total
Parse Calls Executions Parses SQL Id
———— ———— ——— ————-
36 36 1.30 g2m68t7jmcctz
select job, nvl2(last_date, 1, 0) from sys.job$ where next_date < = :1 and (field
1 = :2 or (field1 = 0 and 'Y' = :3)) order by next_date, job

-------------------------------------------------------------

SQL ordered by Sharable Memory DB/Inst: ORCLDB/orcldb1 Snaps: 70-71

No data exists for this section of the report.
-------------------------------------------------------------

SQL ordered by Version Count DB/Inst: ORCLDB/orcldb1 Snaps: 70-71

No data exists for this section of the report.
-------------------------------------------------------------

SQL ordered by Cluster Wait Time DB/Inst: ORCLDB/orcldb1 Snaps: 70-71

Cluster CWT % of Elapsed CPU
Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id
------------- ---------- ----------- ----------- -------------- -------------
1.97 75.0 2.63 0.76 13 bn30dmqukmymf
Module: OEM.CacheModeWaitPool
BEGIN MGMT_JOB_ENGINE.update_step_status(:1,:2,:3); END;

1.17 91.1 1.29 0.16 39 642qudg0p507b
Module: OEM.CacheModeWaitPool
UPDATE MGMT_JOB_EXECUTION SET STEP_STATUS = :B3 , STEP_STATUS_CODE=:B2 , END_TIM
E=CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE), DISPATCHER_ID=-1 WHERE STEP_ID=:B
1 RETURNING END_TIME INTO :O0

.35 66.5 0.53 0.24 12 7qrr8supfkjh4
Module: OEM.CacheModeWaitPool
BEGIN :1 := MGMT_JOB_ENGINE.execute_param_sources(:2,:3,:4); END;

.27 72.6 0.37 0.16 24 70rz9rwjypgjt
Module: OEM.CacheModeWaitPool
UPDATE MGMT_JOB_EXECUTION SET STEP_STATUS=:B2 WHERE STEP_ID=:B1

.21 21.2 1.00 0.77 1 d92h3rjp0y217
begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end;

.17 68.7 0.25 0.16 125 6610ftkf71s85
Module: OEM.BoundedPool
UPDATE MGMT_JOB_HISTORY SET JOB_ID=:B22 , EXECUTION_ID=:B21 , STEP_ID=:B20 , SOU
RCE_STEP_ID=:B19 , ORIGINAL_STEP_ID=:B18 , RESTART_MODE=:B17 , STEP_NAME=:B16 ,
STEP_TYPE=:B15 , COMMAND_TYPE=:B14 , ITERATE_PARAM=:B13 , ITERATE_PARAM_INDEX=:B
12 , PARENT_STEP_ID=:B11 , STEP_STATUS=:B10 , STEP_STATUS_CODE=:B9 , NUM_CHILDRE

.15 77.5 0.19 0.04 13 cqgjk2qqgfuhk
Module: OEM.CacheModeWaitPool
UPDATE MGMT_JOB_EXEC_SUMMARY SET STATUS=:B3 , END_TIME=:B2 WHERE EXECUTION_ID=:B
1 RETURNING QUEUE_ID INTO :O0

.14 68.7 0.20 0.06 12 czt1p4jaq1s0n
Module: OEM.CacheModeWaitPool
UPDATE MGMT_JOB_EXECUTION SET STEP_STATUS=:B2 , START_TIME=:B1 WHERE STEP_ID IN
( SELECT STEP_ID FROM MGMT_JOB_EXECUTION START WITH STEP_ID=:B3 CONNECT BY STEP_
ID=PRIOR PARENT_STEP_ID)

.12 55.3 0.22 0.14 12 c7sn076yz7030
select smontabv.cnt, smontab.time_mp, smontab.scn, smontab.num_mappings, smon
tab.tim_scn_map, smontab.orig_thread from smon_scn_time smontab, (sel
ect max(scn) scnmax, count(*)+sum(NVL2(TIM_SCN_MAP,NUM_MAPPINGS,
0)) cnt from smon_scn_time where thread=0) smontabv where smon

.07 18.6 0.36 0.27 1 0dwr2vd6vbqzs
SELECT count(*) over () as total_count, sd_xe_ash_nm.event_name, s
d_xe_ash_nm.event_id, sd_xe_ash_nm.parameter1 as p1text, (CASE WHE
N (sd_xe_ash_nm.parameter1 is NULL OR sd_x
e_ash_nm.parameter1 = '0') THEN 0 ELSE 1 END)

.05 9.8 0.55 0.49 1 84qubbrsr0kfn
insert into wrh$_latch (snap_id, dbid, instance_number, latch_hash, level#, ge
ts, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, s
leep2, sleep3, sleep4, wait_time) select :snap_id, :dbid, :instance_number,
hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_ge

.05 7.2 0.74 0.36 1 c2aw4fq306q4k
INSERT /*+ APPEND */ INTO WRH$_ACTIVE_SESSION_HISTORY ( snap_id, dbid, instance_
number, sample_id, sample_time, session_id, session_serial#, user_id, sql_
SQL ordered by Cluster Wait Time DB/Inst: ORCLDB/orcldb1 Snaps: 70-71

Cluster CWT % of Elapsed CPU
Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id
------------- ---------- ----------- ----------- -------------- -------------
id, sql_child_number, sql_plan_hash_value, force_matching_signature, service_
hash, session_type, flags , sql_opcode, plsql_entry_object_id, plsql

.05 58.4 0.08 0.08 69 c4nhd1ntptxq7
select message_level, sequence_id, time_suggested from WRI$_ALERT_OUTSTANDING wh
ere reason_id = :1 and object_id = :2 and subobject_id = :3 and internal_instanc
e_number = :4

.03 19.3 0.14 0.12 4 8uxr3scz9bmxd
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ BEGIN :succ_sub := 0; dbms_aqadm.creat
e_aq_agent(:subscriber); :succ_sub := 1; EXCEPTION
WHEN OTHERS THEN IF SQLCODE = -24089 THEN NULL;
ELSE RAISE; END IF; END;

.02 64.5 0.04 0.03 3 6cxqh7mktnbjm
insert into smon_scn_time (thread, time_mp, time_dp, scn, scn_wrp, scn_bas, num
_mappings, tim_scn_map) values (0, :1, :2, :3, :4, :5, :6, :7)

.02 41.0 0.04 0.04 17 9qgtwh66xg6nz
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,e
xtpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decod
e(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:
17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3

.01 42.0 0.03 0.03 24 3m8smr0v7v1m6
INSERT INTO sys.wri$_adv_message_groups (task_id,id,seq,message#,fac,hdr,lm,nl,p
1,p2,p3,p4,p5) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)

.01 6.2 0.20 0.19 4 1w8m6dwy66ttn
Module: emagent@sun880-1 (TNS V1-V3)
/* OracleOEM */ DECLARE subscriber sys.aq$_agent; BEGIN
:succ_sub := 0; subscriber := sys.aq$_agent(:subscrib
er, null, null); dbms_aqadm.add_subscriber(queue_name => ‘SYS.ALERT
_QUE’, subscriber => subscriber);

.01 72.8 0.01 0.01 1 akh9zqqkx3wj7
select 1, max(id) from sys.wri$_adv_objects where task_id = :1 union all select
2, max(id) from sys.wri$_adv_recommendations where task_id = :1 union all sele
ct 3, max(id) from sys.wri$_adv_actions where task_id = :1 union all select 4,
max(id) from sys.wri$_adv_findings where task_id = :1 union all select 5, max(i

.01 41.0 0.02 0.02 7 19rkm1wsf9axx
insert into WRI$_ALERT_HISTORY (sequence_id, reason_id, owner, object_name, subo
bject_name, reason_argument_1, reason_argument_2, reason_argument_3, reason_argu
ment_4, reason_argument_5, time_suggested, creation_time, action_argument_1, act
ion_argument_2, action_argument_3, action_argument_4, action_argument_5, message

.01 37.6 0.02 0.02 6 3qsmy8ybvwt3n
insert into WRI$_ALERT_OUTSTANDING (reason_id, object_id, subobject_id, internal
_instance_number, owner, object_name, subobject_name, sequence_id, reason_argume
nt_1, reason_argument_2, reason_argument_3, reason_argument_4, reason_argument_5
, time_suggested, creation_time, action_argument_1, action_argument_2, action_ar

.01 71.1 0.01 0.01 1 5an8d9ctcysja
SELECT (case when snap_id = :startsn then 1 else 0 end) as star
t_snap, (case when name = ‘maximum PGA allocated’ then
1 when name = ‘total PGA allocated’ then 2
when name = ‘over allocation count’ then 3

SQL ordered by Cluster Wait Time DB/Inst: ORCLDB/orcldb1 Snaps: 70-71

Cluster CWT % of Elapsed CPU
Wait Time (s) Elapsd Tim Time(s) Time(s) Executions SQL Id
————- ———- ———– ———– ————– ————-
.01 55.1 0.01 0.01 12 8sztsnqd1vp8a
Module: OEM.CacheModeWaitPool
SELECT emd_url FROM mgmt_targets WHERE target_name=:1 AND target_type=:2

.01 7.5 0.08 0.08 99 g00cj285jmgsw
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
+ :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn

.01 50.2 0.01 0.01 1 31a13pnjps7j3
SELECT source, (case when time_secs < 1 then 1 else time_secs end) as tim
e_secs, operation FROM ( SELECT 1 as source, trunc((s
ysdate - cast(ll.log_date as date)) * 86400) as time_secs,
decode(ll.operation, 'OPEN', 0

-------------------------------------------------------------

Instance Activity Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71

Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
CPU used by this session 4,242 1.2 1.1
CPU used when call started 1,618 0.5 0.4
CR blocks created 28 0.0 0.0
Cached Commit SCN referenced 732 0.2 0.2
Commit SCN cached 1 0.0 0.0
DB time 374,667 103.7 101.0
DBWR checkpoint buffers written 897 0.3 0.2
DBWR checkpoints 0 0.0 0.0
DBWR fusion writes 78 0.0 0.0
DBWR thread checkpoint buffers w 0 0.0 0.0
DBWR transaction table writes 225 0.1 0.1
DBWR undo block writes 384 0.1 0.1
DFO trees parallelized 12 0.0 0.0
PX local messages recv'd 30 0.0 0.0
PX local messages sent 30 0.0 0.0
PX remote messages recv'd 804 0.2 0.2
PX remote messages sent 516 0.1 0.1
Parallel operations downgraded t 0 0.0 0.0
Parallel operations not downgrad 12 0.0 0.0
RowCR - row contention 0 0.0 0.0
RowCR attempts 312 0.1 0.1
RowCR hits 303 0.1 0.1
SMON posted for dropping temp se 0 0.0 0.0
SMON posted for instance recover 0 0.0 0.0
SMON posted for undo segment shr 1 0.0 0.0
SQL*Net roundtrips to/from clien 7,329 2.0 2.0
active txn count during cleanout 173 0.1 0.1
application wait time 36 0.0 0.0
auto extends on undo tablespace 0 0.0 0.0
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 15,886 4.4 4.3
branch node splits 0 0.0 0.0
buffer is not pinned count 12,163 3.4 3.3
buffer is pinned count 45,239 12.5 12.2
bytes received via SQL*Net from 3,185,377 881.5 858.6
bytes sent via SQL*Net to client 1,042,320 288.4 281.0
calls to get snapshot scn: kcmgs 9,124 2.5 2.5
calls to kcmgas 1,712 0.5 0.5
calls to kcmgcs 141 0.0 0.0
change write time 18 0.0 0.0
cleanout - number of ktugct call 229 0.1 0.1
cleanouts and rollbacks - consis 22 0.0 0.0
cleanouts only - consistent read 43 0.0 0.0
cluster key scan block gets 1,416 0.4 0.4
cluster key scans 137 0.0 0.0
cluster wait time 315 0.1 0.1
commit batch performed 4 0.0 0.0
commit batch requested 4 0.0 0.0
commit batch/immediate performed 9 0.0 0.0
commit batch/immediate requested 9 0.0 0.0
commit cleanout failures: block 16 0.0 0.0
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callba 3 0.0 0.0
commit cleanout failures: cannot 0 0.0 0.0
commit cleanouts 709 0.2 0.2
commit cleanouts successfully co 690 0.2 0.2
commit immediate performed 5 0.0 0.0
commit immediate requested 5 0.0 0.0
commit txn count during cleanout 142 0.0 0.0
Instance Activity Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71

Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
concurrency wait time 281 0.1 0.1
consistent changes 137 0.0 0.0
consistent gets 28,336 7.8 7.6
consistent gets - examination 8,284 2.3 2.2
consistent gets from cache 28,336 7.8 7.6
current blocks converted for CR 0 0.0 0.0
cursor authentications 4 0.0 0.0
data blocks consistent reads - u 91 0.0 0.0
db block changes 6,359 1.8 1.7
db block gets 12,180 3.4 3.3
db block gets direct 98 0.0 0.0
db block gets from cache 12,082 3.3 3.3
deferred (CURRENT) block cleanou 231 0.1 0.1
dirty buffers inspected 10 0.0 0.0
enqueue conversions 960 0.3 0.3
enqueue releases 41,758 11.6 11.3
enqueue requests 41,878 11.6 11.3
enqueue timeouts 120 0.0 0.0
enqueue waits 1,759 0.5 0.5
exchange deadlocks 0 0.0 0.0
execute count 5,259 1.5 1.4
failed probes on index block rec 0 0.0 0.0
free buffer inspected 351 0.1 0.1
free buffer requested 1,394 0.4 0.4
gc CPU used by this session 3,762 1.0 1.0
gc blocks lost 0 0.0 0.0
gc claim blocks lost 0 0.0 0.0
gc cr block build time 2 0.0 0.0
gc cr block flush time 5 0.0 0.0
gc cr block receive time 105 0.0 0.0
gc cr block send time 9 0.0 0.0
gc cr blocks received 474 0.1 0.1
gc cr blocks served 388 0.1 0.1
gc current block flush time 8 0.0 0.0
gc current block pin time 3 0.0 0.0
gc current block receive time 186 0.1 0.1
gc current block send time 13 0.0 0.0
gc current blocks received 499 0.1 0.1
gc current blocks served 520 0.1 0.1
gc local grants 113 0.0 0.0
gc remote grants 335 0.1 0.1
gcs messages sent 5,171 1.4 1.4
ges messages sent 35,676 9.9 9.6
global enqueue get time 1,917 0.5 0.5
global enqueue gets async 628 0.2 0.2
global enqueue gets sync 21,536 6.0 5.8
global enqueue releases 10,437 2.9 2.8
global undo segment hints helped 0 0.0 0.0
global undo segment hints were s 0 0.0 0.0
heap block compress 2 0.0 0.0
hot buffers moved to head of LRU 69 0.0 0.0
immediate (CR) block cleanout ap 65 0.0 0.0
immediate (CURRENT) block cleano 221 0.1 0.1
index crx upgrade (found) 0 0.0 0.0
index crx upgrade (positioned) 1,999 0.6 0.5
index fast full scans (full) 0 0.0 0.0
index fetch by key 5,017 1.4 1.4
index scans kdiixs1 2,871 0.8 0.8
leaf node 90-10 splits 0 0.0 0.0
leaf node splits 54 0.0 0.0
Instance Activity Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71

Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
lob reads 3 0.0 0.0
lob writes 20 0.0 0.0
lob writes unaligned 20 0.0 0.0
logons cumulative 238 0.1 0.1
messages received 1,481 0.4 0.4
messages sent 1,481 0.4 0.4
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 15,250 4.2 4.1
opened cursors cumulative 2,780 0.8 0.8
parse count (failures) 0 0.0 0.0
parse count (hard) 3 0.0 0.0
parse count (total) 2,778 0.8 0.8
parse time cpu 37 0.0 0.0
parse time elapsed 31 0.0 0.0
physical read IO requests 105 0.0 0.0
physical read bytes 860,160 238.0 231.9
physical read total IO requests 8,429 2.3 2.3
physical read total bytes 137,175,040 37,960.5 36,974.4
physical read total multi block 0 0.0 0.0
physical reads 105 0.0 0.0
physical reads cache 105 0.0 0.0
physical reads cache prefetch 0 0.0 0.0
physical reads direct 0 0.0 0.0
physical reads direct temporary 0 0.0 0.0
physical reads prefetch warmup 0 0.0 0.0
physical write IO requests 775 0.2 0.2
physical write bytes 8,781,824 2,430.2 2,367.1
physical write total IO requests 2,249 0.6 0.6
physical write total bytes 30,896,640 8,550.1 8,327.9
physical write total multi block 272 0.1 0.1
physical writes 1,072 0.3 0.3
physical writes direct 98 0.0 0.0
physical writes direct (lob) 0 0.0 0.0
physical writes from cache 974 0.3 0.3
physical writes non checkpoint 342 0.1 0.1
pinned buffers inspected 2 0.0 0.0
prefetch warmup blocks aged out 0 0.0 0.0
process last non-idle time 4,292 1.2 1.2
queries parallelized 12 0.0 0.0
recovery blocks read 0 0.0 0.0
recursive calls 26,911 7.5 7.3
recursive cpu usage 866 0.2 0.2
redo blocks read for recovery 0 0.0 0.0
redo blocks written 4,761 1.3 1.3
redo buffer allocation retries 0 0.0 0.0
redo entries 4,765 1.3 1.3
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo ordering marks 197 0.1 0.1
redo size 2,244,932 621.2 605.1
redo subscn max counts 242 0.1 0.1
redo synch time 9 0.0 0.0
redo synch writes 30 0.0 0.0
redo wastage 73,056 20.2 19.7
redo write time 56 0.0 0.0
redo writer latching time 0 0.0 0.0
redo writes 271 0.1 0.1
rollback changes - undo records 10 0.0 0.0
rollbacks only - consistent read 16 0.0 0.0
rows fetched via callback 2,066 0.6 0.6
Instance Activity Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71

Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
session connect time 0 0.0 0.0
session cursor cache hits 1,769 0.5 0.5
session logical reads 40,516 11.2 10.9
session pga memory 398,815,960 110,364.6 107,497.6
session pga memory max 854,291,160 236,408.6 230,267.2
session uga memory 11,589,304 3,207.1 3,123.8
session uga memory max 76,114,704 21,063.3 20,516.1
shared hash latch upgrades - no 2,436 0.7 0.7
shared hash latch upgrades - wai 0 0.0 0.0
sorts (memory) 1,895 0.5 0.5
sorts (rows) 382,352 105.8 103.1
sql area evicted 0 0.0 0.0
sql area purged 0 0.0 0.0
summed dirty queue length 18 0.0 0.0
switch current to new buffer 7 0.0 0.0
table fetch by rowid 24,502 6.8 6.6
table fetch continued row 0 0.0 0.0
table scan blocks gotten 8,184 2.3 2.2
table scan rows gotten 484,899 134.2 130.7
table scans (short tables) 942 0.3 0.3
total number of times SMON poste 736 0.2 0.2
transaction lock background get 0 0.0 0.0
transaction lock background gets 0 0.0 0.0
transaction lock foreground requ 0 0.0 0.0
transaction lock foreground wait 0 0.0 0.0
transaction rollbacks 9 0.0 0.0
transaction tables consistent re 0 0.0 0.0
transaction tables consistent re 0 0.0 0.0
undo change vector size 841,076 232.8 226.7
user I/O wait time 15 0.0 0.0
user calls 9,682 2.7 2.6
user commits 45 0.0 0.0
user rollbacks 3,665 1.0 1.0
workarea executions - optimal 1,207 0.3 0.3
write clones created in backgrou 0 0.0 0.0
write clones created in foregrou 0 0.0 0.0
-------------------------------------------------------------

Instance Activity Stats - Absolute ValuesDB/Inst: ORCLDB/orcldb1 Snaps: 70-7
-> Statistics with absolute values (should not be diffed)

Statistic Begin Value End Value
——————————– ————— —————
session cursor cache count 32,672 33,107
opened cursors current 2,197 2,325
logons current 136 141
————————————————————-

Instance Activity Stats - Thread ActivityDB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Statistics identified by ‘(derived)’ come from sources other than SYSSTAT

Statistic Total per Hour
——————————– —————— ———
log switches (derived) 0 .00
————————————————————-

Tablespace IO Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> ordered by IOs (Reads + Writes) desc

Tablespace
——————————
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
————– ——- —— ——- ———— ——– ———- ——
UNDOTBS1
0 0 0.0 .0 463 0 4 2.5
SYSAUX
102 0 1.7 1.0 291 0 0 0.0
SYSTEM
2 0 0.0 1.0 10 0 0 0.0
TBL
0 0 0.0 .0 11 0 0 0.0
————————————————————-

File IO Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> ordered by Tablespace, File

Tablespace Filename
———————— —————————————————-
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
————– ——- —— ——- ———— ——– ———- ——
SYSAUX +DB/orcldb/datafile/sysaux.257.644827233
102 0 1.7 1.0 291 0 0 0.0
SYSTEM +DB/orcldb/datafile/system.256.644827233
2 0 0.0 1.0 10 0 0 0.0
TBL +DB/orcldb/datafile/tbl.274.644879457
0 0 N/A N/A 11 0 0 0.0
UNDOTBS1 +DB/orcldb/datafile/undotbs1.258.644827235
0 0 N/A N/A 463 0 4 2.5
————————————————————-

Buffer Pool Statistics DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

Free Writ Buffer
Number of Pool Buffer Physical Physical Buff Comp Busy
P Buffers Hit% Gets Reads Writes Wait Wait Waits
— ———- —- ————– ———— ———– —- —- ———-
D 138,168 100 40,359 105 974 0 0 4
————————————————————-

Instance Recovery Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> B: Begin snapshot, E: End snapshot

Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- —– —– ———- ——— ——— ———- ——— ————
B 0 0 375 2059 560 92160 560 N/A
E 0 0 503 2115 2214 92160 2214 N/A
————————————————————-

Buffer Pool Advisory DB/Inst: ORCLDB/orcldb1 Snap: 71
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate

Est
Phys
Size for Size Buffers for Read Estimated
P Est (M) Factor Estimate Factor Physical Reads
— ——– —— —————- —— ——————
D 112 .1 13,433 11.4 1,643,046
D 224 .2 26,866 7.6 1,088,745
D 336 .3 40,299 5.5 791,348
D 448 .4 53,732 4.2 599,528
D 560 .5 67,165 3.2 466,081
D 672 .6 80,598 2.6 367,580
D 784 .7 94,031 2.0 287,276
D 896 .8 107,464 1.6 228,409
D 1,008 .9 120,897 1.3 183,134
D 1,120 1.0 134,330 1.1 151,018
D 1,152 1.0 138,168 1.0 143,679
D 1,232 1.1 147,763 0.9 129,263
D 1,344 1.2 161,196 0.8 114,723
D 1,456 1.3 174,629 0.8 108,086
D 1,568 1.4 188,062 0.7 104,519
D 1,680 1.5 201,495 0.7 102,932
D 1,792 1.6 214,928 0.7 102,513
D 1,904 1.7 228,361 0.7 102,376
D 2,016 1.8 241,794 0.7 102,259
D 2,128 1.8 255,227 0.7 102,238
D 2,240 1.9 268,660 0.7 102,238
————————————————————-

PGA Aggr Summary DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
————— —————— ————————–
100.0 100 0
————————————————————-

PGA Aggr Target Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> B: Begin snap E: End snap (rows dentified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of workarea memory under manual control

%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- ———- ———- ———- ———- —— —— —— ———-
B 809 353 660.6 0.0 .0 .0 .0 102,400
E 809 337 688.0 0.0 .0 .0 .0 102,400
————————————————————-

PGA Aggr Target Histogram DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Optimal Executions are purely in-memory operations

Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
——- ——- ————– ————– ———— ————
2K 4K 1,104 1,104 0 0
64K 128K 28 28 0 0
256K 512K 2 2 0 0
512K 1024K 51 51 0 0
1M 2M 10 10 0 0
2M 4M 12 12 0 0
————————————————————-

PGA Memory Advisory DB/Inst: ORCLDB/orcldb1 Snap: 71
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0

Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
———- ——- —————- —————- ——– ———-
101 0.1 5,949.4 522.7 92.0 127
202 0.3 5,949.4 343.4 95.0 87
405 0.5 5,949.4 272.0 96.0 58
607 0.8 5,949.4 272.0 96.0 53
809 1.0 5,949.4 0.0 100.0 39
971 1.2 5,949.4 0.0 100.0 0
1,133 1.4 5,949.4 0.0 100.0 0
1,294 1.6 5,949.4 0.0 100.0 0
1,456 1.8 5,949.4 0.0 100.0 0
1,618 2.0 5,949.4 0.0 100.0 0
2,427 3.0 5,949.4 0.0 100.0 0
3,236 4.0 5,949.4 0.0 100.0 0
4,854 6.0 5,949.4 0.0 100.0 0
6,472 8.0 5,949.4 0.0 100.0 0
————————————————————-

Shared Pool Advisory DB/Inst: ORCLDB/orcldb1 Snap: 71
-> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid.

Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem
Size(M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits
———- —– ——– ———— ——- —— ——- —— ———–
240 .7 59 4,090 ####### 1.0 5,383 28.3 6,239,170
288 .9 102 8,293 ####### 1.0 190 1.0 6,239,962
336 1.0 102 8,293 ####### 1.0 190 1.0 6,239,962
384 1.1 102 8,293 ####### 1.0 190 1.0 6,239,962
432 1.3 102 8,293 ####### 1.0 190 1.0 6,239,962
480 1.4 102 8,293 ####### 1.0 190 1.0 6,239,962
528 1.6 102 8,293 ####### 1.0 190 1.0 6,239,962
576 1.7 102 8,293 ####### 1.0 190 1.0 6,239,962
624 1.9 102 8,293 ####### 1.0 190 1.0 6,239,962
672 2.0 102 8,293 ####### 1.0 190 1.0 6,239,962
————————————————————-

SGA Target Advisory DB/Inst: ORCLDB/orcldb1 Snap: 71

SGA Target SGA Size Est DB Est Physical
Size (M) Factor Time (s) Reads
———- ———- ———— —————-
768 0.5 6,650,967 666,824
1,152 0.8 6,648,973 319,468
1,536 1.0 6,648,308 143,678
1,920 1.3 6,648,308 116,149
2,304 1.5 6,648,354 113,635
2,688 1.8 6,648,443 113,621
3,072 2.0 6,648,443 113,621
————————————————————-

Streams Pool Advisory DB/Inst: ORCLDB/orcldb1 Snap: 71

No data exists for this section of the report.
————————————————————-

Java Pool Advisory DB/Inst: ORCLDB/orcldb1 Snap: 71

No data exists for this section of the report.
————————————————————-

Buffer Wait Statistics DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> ordered by wait time desc, waits desc

Class Waits Total Wait Time (s) Avg Time (ms)
—————— ———– ——————- ————–
undo header 4 0 3
————————————————————-

Enqueue Activity DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc

Enqueue Type (Request Reason)
——————————————————————————
Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
———— ———— ———– ———– ———— ————–
HW-Segment High Water Mark
1,282 1,282 0 738 1 1.02
PS-PX Process Reservation
423 339 84 359 1 1.90
US-Undo Segment
622 622 0 490 1 1.10
WF-AWR Flush
25 25 0 20 0 20.30
TM-DML
3,379 3,379 0 17 0 15.71
JQ-Job Queue
36 0 36 36 0 2.61
TA-Instance Undo
24 24 0 23 0 2.35
TT-Tablespace
234 234 0 29 0 1.31
TQ-Queue table enqueue (DDL contention)
4 4 0 4 0 5.75
JS-Job Scheduler (job run lock - synchronize)
2 2 0 2 0 6.00
FB-Format Block
12 12 0 9 0 1.00
PD-Property Lock
4 4 0 4 0 1.00
TX-Transaction (allocate ITL entry)
3 3 0 3 0 1.00
PI-Remote PX Process Spawn Status
2 2 0 2 0 1.50
AF-Advisor Framework (task serialization)
7 7 0 1 0 2.00
PW-Buffer Cache PreWarm (flush prewarm buffers)
1 1 0 1 0 1.00
————————————————————-

Undo Segment Summary DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out of Space count
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsed

Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/
TS# Blocks (K) Transactions Len (s) Concurcy TR (mins) OOS eS/eR/eU
—- ———- ————— ——– ——– ——— —– ————–
1 .3 577 0 3 15/15 0/0 0/0/0/0/0/0
————————————————————-

Undo Segment Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Most recent 35 Undostat rows, ordered by Time desc

Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/
End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU
———— ———– ———— ——- ——- ——- —– ————
27-Jan 05:57 148 146 0 3 15 0/0 0/0/0/0/0/0
27-Jan 05:47 2 5 0 1 15 0/0 0/0/0/0/0/0
27-Jan 05:37 7 26 0 2 15 0/0 0/0/0/0/0/0
27-Jan 05:27 16 77 0 2 15 0/0 0/0/0/0/0/0
27-Jan 05:17 7 76 0 1 15 0/0 0/0/0/0/0/0
27-Jan 05:07 2 8 0 1 15 0/0 0/0/0/0/0/0
27-Jan 04:57 150 239 0 2 15 0/0 0/0/0/0/0/0
————————————————————-

Latch Activity DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> “Get Requests”, “Pct Get Miss” and “Avg Slps/Miss” are statistics for
willing-to-wait latch get requests
-> “NoWait Requests”, “Pct NoWait Miss” are for no-wait latch get requests
-> “Pct Misses” for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
———————— ————– —— —— —— ———— ——
ASM db client latch 2,792 0.0 N/A 0 0 N/A
ASM map operation freeli 36 0.0 N/A 0 0 N/A
ASM map operation hash t 21,396 0.0 N/A 0 0 N/A
ASM network background l 1,448 0.0 N/A 0 0 N/A
AWR Alerted Metric Eleme 26,373 0.0 N/A 0 0 N/A
Consistent RBA 273 0.0 N/A 0 0 N/A
FOB s.o list latch 70 0.0 N/A 0 0 N/A
JS broadcast add buf lat 827 0.0 N/A 0 0 N/A
JS broadcast drop buf la 827 0.0 N/A 0 0 N/A
JS broadcast load blnc l 720 0.0 N/A 0 0 N/A
JS mem alloc latch 16 0.0 N/A 0 0 N/A
JS queue access latch 16 0.0 N/A 0 0 N/A
JS queue state obj latch 26,096 0.0 N/A 0 0 N/A
JS slv state obj latch 8 0.0 N/A 0 0 N/A
KCL gc element parent la 7,948 0.0 N/A 0 20 0.0
KFK SGA context latch 1,224 0.0 N/A 0 0 N/A
KFMD SGA 24 0.0 N/A 0 0 N/A
KJC message pool free li 4,425 0.1 0.0 0 199 0.0
KJCT flow control latch 64,663 0.0 0.0 0 0 N/A
KMG MMAN ready and start 1,201 0.0 N/A 0 0 N/A
KSXR large replies 360 0.0 N/A 0 0 N/A
KTF sga latch 15 0.0 N/A 0 1,181 0.0
KWQMN job cache list lat 323 0.0 N/A 0 0 N/A
MQL Tracking Latch 0 N/A N/A 0 72 0.0
Memory Management Latch 0 N/A N/A 0 1,201 0.0
OS process 150 0.0 N/A 0 0 N/A
OS process allocation 1,305 0.0 N/A 0 0 N/A
OS process: request allo 52 0.0 N/A 0 0 N/A
PL/SQL warning settings 389 0.0 N/A 0 0 N/A
SQL memory manager latch 1 0.0 N/A 0 1,201 0.0
SQL memory manager worka 83,338 0.0 N/A 0 0 N/A
Shared B-Tree 127 0.0 N/A 0 0 N/A
active checkpoint queue 3,306 0.0 N/A 0 0 N/A
active service list 8,538 0.0 N/A 0 1,224 0.0
archive control 118 0.0 N/A 0 0 N/A
begin backup scn array 6 0.0 N/A 0 0 N/A
business card 24 0.0 N/A 0 0 N/A
cache buffer handles 170 0.0 N/A 0 0 N/A
cache buffers chains 93,916 0.0 0.2 0 1,517 0.0
cache buffers lru chain 2,755 0.0 N/A 0 3,690 0.1
channel handle pool latc 58 0.0 N/A 0 0 N/A
channel operations paren 501,611 0.2 1.0 2 0 N/A
checkpoint queue latch 52,218 0.0 N/A 0 1,242 0.0
client/application info 1,038 0.0 N/A 0 0 N/A
commit callback allocati 26 0.0 N/A 0 0 N/A
compile environment latc 241 0.0 N/A 0 0 N/A
database property servic 4 0.0 N/A 0 0 N/A
dml lock allocation 6,814 0.0 N/A 0 0 N/A
dummy allocation 477 0.0 N/A 0 0 N/A
enqueue hash chains 96,626 0.0 0.0 0 69 0.0
enqueues 80,369 0.0 0.0 0 0 N/A
error message lists 96 0.0 N/A 0 0 N/A
event group latch 28 0.0 N/A 0 0 N/A
file cache latch 635 0.0 N/A 0 0 N/A
gcs opaque info freelist 2,396 0.0 N/A 0 0 N/A
gcs partitioned table ha 15,391 0.0 N/A 0 0 N/A
gcs remastering latch 61 0.0 N/A 0 0 N/A
gcs resource freelist 36 0.0 N/A 0 49 0.0
gcs resource hash 9,383 0.1 0.0 0 22 0.0
gcs resource scan list 44 0.0 N/A 0 0 N/A
Latch Activity DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> “Get Requests”, “Pct Get Miss” and “Avg Slps/Miss” are statistics for
willing-to-wait latch get requests
-> “NoWait Requests”, “Pct NoWait Miss” are for no-wait latch get requests
-> “Pct Misses” for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
———————— ————– —— —— —— ———— ——
gcs shadows freelist 416 0.0 N/A 0 464 0.0
ges caches resource list 16,598 0.2 0.0 0 42,796 0.1
ges deadlock list 156 0.0 N/A 0 0 N/A
ges domain table 21,564 0.0 N/A 0 0 N/A
ges enqueue table freeli 53,160 0.0 0.0 0 0 N/A
ges group table 29,032 0.0 N/A 0 0 N/A
ges process hash list 5,494 0.0 N/A 0 0 N/A
ges process parent latch 98,896 0.0 N/A 0 0 N/A
ges process table freeli 52 0.0 N/A 0 0 N/A
ges resource hash list 114,216 0.0 0.0 0 9,579 0.1
ges resource table freel 18,298 0.1 0.0 0 0 N/A
ges timeout list 284 0.0 N/A 0 394 0.0
global KZLD latch for me 15 0.0 N/A 0 0 N/A
hash table column usage 16 0.0 N/A 0 70 0.0
hash table modification 69 0.0 N/A 0 0 N/A
job workq parent latch 0 N/A N/A 0 4 0.0
job_queue_processes para 62 0.0 N/A 0 0 N/A
kks stats 2 0.0 N/A 0 0 N/A
kokc descriptor allocati 514 0.0 N/A 0 0 N/A
ksuosstats global area 263 0.4 1.0 0 0 N/A
ktm global data 738 0.0 N/A 0 0 N/A
kwqbsn:qsga 127 0.0 N/A 0 0 N/A
lgwr LWN SCN 1,361 0.0 N/A 0 0 N/A
library cache 32,243 0.0 0.0 0 119 0.0
library cache load lock 18 0.0 N/A 0 0 N/A
library cache lock 24,485 0.0 N/A 0 0 N/A
library cache lock alloc 176 0.0 N/A 0 0 N/A
library cache pin 11,388 0.0 N/A 0 0 N/A
library cache pin alloca 25 0.0 N/A 0 0 N/A
list of block allocation 25 0.0 N/A 0 0 N/A
loader state object free 2 0.0 N/A 0 0 N/A
message pool operations 1,664 0.0 N/A 0 0 N/A
messages 107,857 0.0 0.0 0 0 N/A
mostly latch-free SCN 1,363 0.0 N/A 0 0 N/A
multiblock read objects 14 0.0 N/A 0 0 N/A
name-service memory obje 1,953 0.0 N/A 0 0 N/A
name-service namespace b 34,801 0.0 N/A 0 0 N/A
name-service pending que 378 0.0 N/A 0 0 N/A
name-service request 16 0.0 N/A 0 0 N/A
name-service request que 33,833 0.0 N/A 0 0 N/A
ncodef allocation latch 58 0.0 N/A 0 0 N/A
object queue header heap 3,308 0.0 N/A 0 2 0.0
object queue header oper 10,229 0.0 0.0 0 0 N/A
object stats modificatio 1 0.0 N/A 0 0 N/A
parallel query alloc buf 2,417 0.0 N/A 0 0 N/A
parallel query stats 246 0.0 N/A 0 0 N/A
parallel txn reco latch 14,856 0.0 N/A 0 0 N/A
parameter list 2,457 0.0 N/A 0 0 N/A
parameter table allocati 236 0.0 N/A 0 0 N/A
post/wait queue 150 0.0 N/A 0 124 0.0
process allocation 33,296 0.0 N/A 0 28 0.0
process group creation 52 0.0 N/A 0 0 N/A
process queue 785 0.0 N/A 0 0 N/A
process queue reference 24,108 0.0 0.0 0 1,416 0.0
qmn state object latch 14 0.0 N/A 0 0 N/A
qmn task queue latch 582 0.3 0.0 0 0 N/A
query server freelists 545 0.0 N/A 0 0 N/A
query server process 2 0.0 N/A 0 3 0.0
queued dump request 17,179 0.0 N/A 0 0 N/A
redo allocation 2,995 0.0 0.0 0 4,671 0.0
Latch Activity DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> “Get Requests”, “Pct Get Miss” and “Avg Slps/Miss” are statistics for
willing-to-wait latch get requests
-> “NoWait Requests”, “Pct NoWait Miss” are for no-wait latch get requests
-> “Pct Misses” for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
———————— ————– —— —— —— ———— ——
redo copy 0 N/A N/A 0 4,671 0.0
redo writing 6,648 0.0 N/A 0 0 N/A
resmgr group change latc 406 0.0 N/A 0 0 N/A
resmgr:active threads 470 0.0 N/A 0 0 N/A
resmgr:actses change gro 225 0.0 N/A 0 0 N/A
resmgr:free threads list 459 0.0 N/A 0 0 N/A
resmgr:schema config 11 0.0 N/A 0 0 N/A
row cache objects 84,098 0.0 0.0 0 2 0.0
rules engine aggregate s 13 0.0 N/A 0 0 N/A
rules engine rule set st 26 0.0 N/A 0 0 N/A
sequence cache 116 0.0 N/A 0 0 N/A
session allocation 15,410 0.0 0.0 0 0 N/A
session idle bit 23,653 0.0 N/A 0 0 N/A
session state list latch 513 0.0 N/A 0 0 N/A
session switching 58 0.0 N/A 0 0 N/A
session timer 1,224 0.0 N/A 0 0 N/A
shared pool 10,189 0.0 0.0 0 0 N/A
shared pool simulator 10,511 0.0 N/A 0 0 N/A
simulator hash latch 1,871 0.0 N/A 0 0 N/A
simulator lru latch 0 N/A N/A 0 1,827 0.0
slave class 9 0.0 N/A 0 0 N/A
slave class create 36 2.8 1.0 0 0 N/A
sort extent pool 854 0.0 N/A 0 0 N/A
state object free list 2 0.0 N/A 0 0 N/A
statistics aggregation 560 0.0 N/A 0 0 N/A
temp lob duration state 2 0.0 N/A 0 0 N/A
threshold alerts latch 261 0.0 N/A 0 0 N/A
transaction allocation 28 0.0 N/A 0 0 N/A
transaction branch alloc 1,224 0.0 N/A 0 0 N/A
undo global data 8,703 0.0 N/A 0 0 N/A
user lock 50 0.0 N/A 0 0 N/A
————————————————————-

Latch Sleep Breakdown DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> ordered by misses desc

Latch Name
—————————————-
Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
————– ———– ———– ———- ——– ——– ——–
channel operations parent latch
501,611 960 918 51 0 0 0
cache buffers chains
93,916 6 1 5 0 0 0
ksuosstats global area
263 1 1 0 0 0 0
slave class create
36 1 1 0 0 0 0
————————————————————-

Latch Miss Sources DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> only latches with sleeps are shown
-> ordered by name, sleeps desc

NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
———————— ————————– ——- ———- ——–
cache buffers chains kcbgtcr: kslbegin excl 0 1 0
channel operations paren ksrwait() 0 923 152
ksuosstats global area ksugetosstat 0 1 1
slave class