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 dj1sv9x4jwtrt
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 dj1sv9x4jwtrt
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 dj1sv9x4jwtrt
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 create ksvcreate 0 1 0
————————————————————-
Parent Latch Statistics DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
No data exists for this section of the report.
————————————————————-
Child Latch Statistics DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
No data exists for this section of the report.
————————————————————-
Segments by Logical Reads DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Total Logical Reads: 40,516
-> Captured Segments account for 68.9% of Total
Tablespace Subobject Obj. Logical
Owner Name Object Name Name Type Reads %Total
———- ———- ——————– ———- —– ———— ——-
SYS SYSTEM OBJ$ TABLE 5,088 12.56
SYS SYSTEM JOB$ TABLE 2,240 5.53
SYS SYSTEM I_TYPE2 INDEX 2,208 5.45
SYS SYSTEM TYPE$ TABLE 2,032 5.02
SYS SYSTEM I_DEPENDENCY2 INDEX 1,952 4.82
————————————————————-
Segments by Physical Reads DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Total Physical Reads: 105
-> Captured Segments account for 94.3% of Total
Tablespace Subobject Obj. Physical
Owner Name Object Name Name Type Reads %Total
———- ———- ——————– ———- —– ———— ——-
SYS SYSAUX WRH$_ACTIVE_SESSION_ 0780909_62 TABLE 95 90.48
SYS SYSTEM SMON_SCN_TIME TABLE 2 1.90
SYS SYSAUX ALERT_QT TABLE 1 .95
SYS SYSAUX SYS_IOT_TOP_8861 INDEX 1 .95
————————————————————-
Segments by Row Lock Waits DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> % of Capture shows % of row lock waits for each top segment compared
-> with total row lock waits for all segments captured by the Snapshot
Row
Tablespace Subobject Obj. Lock % of
Owner Name Object Name Name Type Waits Capture
———- ———- ——————– ———- —– ———— ——-
SYS SYSTEM SMON_SCN_TIME TABLE 1 100.00
————————————————————-
Segments by ITL Waits DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> % of Capture shows % of ITL waits for each top segment compared
-> with total ITL waits for all segments captured by the Snapshot
Tablespace Subobject Obj. ITL % of
Owner Name Object Name Name Type Waits Capture
———- ———- ——————– ———- —– ———— ——-
SYS SYSAUX SYS_IOT_TOP_8861 INDEX 1 33.33
SYS SYSAUX WRH$_ACTIVE_SESSION_ 0780909_62 INDEX 1 33.33
SYS SYSAUX WRH$_SYSMETRIC_HISTO INDEX 1 33.33
————————————————————-
Segments by Buffer Busy Waits DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
No data exists for this section of the report.
————————————————————-
Segments by Global Cache Buffer Busy DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
No data exists for this section of the report.
————————————————————-
Segments by CR Blocks Received DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Total CR Blocks Received: 474
-> Captured Segments account for 68.8% of Total
CR
Tablespace Subobject Obj. Blocks
Owner Name Object Name Name Type Received %Total
———- ———- ——————– ———- —– ———— ——-
SYSMAN SYSAUX MGMT_JOB_EXECUTION TABLE 57 12.03
SYS SYSTEM SMON_SCN_TIME TABLE 33 6.96
SYSMAN SYSAUX MGMT_JOB_EXEC_SUMMAR TABLE 27 5.70
SYS SYSAUX WRI$_ALERTS_OUTSTAND INDEX 24 5.06
SYSMAN SYSAUX MGMT_JOB_EXEC_IDX02 INDEX 14 2.95
————————————————————-
Segments by Current Blocks Received DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Total Current Blocks Received: 499
-> Captured Segments account for 72.5% of Total
Current
Tablespace Subobject Obj. Blocks
Owner Name Object Name Name Type Received %Total
———- ———- ——————– ———- —– ———— ——-
SYS SYSAUX SYS_IOT_TOP_8868 INDEX 30 6.01
SYSMAN SYSAUX MGMT_JOB_EXEC_IDX01 INDEX 29 5.81
SYSMAN SYSAUX MGMT_JOB_EXECUTION TABLE 26 5.21
SYSMAN SYSAUX MGMT_JOB_HISTORY TABLE 25 5.01
SYSMAN SYSAUX MGMT_JOB_EXEC_IDX04 INDEX 24 4.81
————————————————————-
Dictionary Cache Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> “Pct Misses” should be very low (< 2% in most cases)
-> “Final Usage” is the number of cache entries being used
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
————————- ———— —— ——- —– ——– ———-
dc_awr_control 64 3.1 0 N/A 0 1
dc_database_links 32 0.0 0 N/A 0 1
dc_global_oids 414 0.0 0 N/A 0 69
dc_histogram_data 58 0.0 0 N/A 0 1,640
dc_histogram_defs 19 0.0 0 N/A 0 3,482
dc_object_grants 12 0.0 0 N/A 0 79
dc_object_ids 1,318 0.0 0 N/A 0 1,137
dc_objects 964 0.2 0 N/A 0 1,542
dc_profiles 29 0.0 0 N/A 0 2
dc_rollback_segments 20,106 0.0 0 N/A 0 621
dc_segments 38 13.2 0 N/A 12 960
dc_sequences 1 100.0 0 N/A 1 1
dc_tablespaces 1,790 0.0 0 N/A 0 9
dc_usernames 146 0.0 0 N/A 0 46
dc_users 2,702 0.0 0 N/A 0 63
outstanding_alerts 120 57.5 0 N/A 13 28
————————————————————-
Dictionary Cache Stats (RAC) DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
GES GES GES
Cache Requests Conflicts Releases
————————- ———— ———— ————
dc_awr_control 2 2 0
dc_objects 2 2 0
dc_segments 24 8 0
dc_sequences 2 2 0
outstanding_alerts 190 62 0
————————————————————-
Library Cache Activity DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> “Pct Misses” should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
————— ———— —— ————– —— ———- ——–
BODY 85 0.0 297 0.0 0 0
CLUSTER 2 0.0 4 0.0 0 0
INDEX 1 0.0 5 0.0 0 0
SQL AREA 847 0.0 10,693 0.0 0 0
TABLE/PROCEDURE 84 0.0 1,220 0.2 2 0
TRIGGER 8 0.0 128 0.0 0 0
————————————————————-
Library Cache Activity (RAC) DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
GES Lock GES Pin GES Pin GES Inval GES Invali-
Namespace Requests Requests Releases Requests dations
————— ———— ———— ———— ———– ———–
CLUSTER 4 0 0 0 0
INDEX 5 0 0 0 0
TABLE/PROCEDURE 575 4 3 4 0
————————————————————-
Process Memory Summary DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> B: Begin snap E: End snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
-> Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> ordered by Begin/End snapshot, Alloc (MB) desc
Hist
Avg Std Dev Max Max
Alloc Used Alloc Alloc Alloc Alloc Num Num
Category (MB) (MB) (MB) (MB) (MB) (MB) Proc Alloc
– ——– ——— ——— ——– ——– ——- ——- —— ——
B Other 574.4 N/A 4.1 5.0 34 57 140 140
Freeable 78.0 .0 .7 .4 4 N/A 115 115
PL/SQL 4.3 2.9 .0 .0 0 0 138 136
SQL 4.0 1.8 .0 .0 0 4 121 115
E Other 596.6 N/A 4.1 4.9 34 57 144 144
Freeable 82.8 .0 .7 .4 4 N/A 120 120
PL/SQL 4.6 3.0 .0 .0 0 0 142 141
SQL 4.2 1.8 .0 .0 0 4 125 120
————————————————————-
SGA Memory Summary DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
—————————— ——————- ——————-
Database Buffers 1,207,959,552
Fixed Size 2,139,344
Redo Buffers 4,325,376
Variable Size 396,188,464
——————-
sum 1,610,612,736
————————————————————-
SGA breakdown difference DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> ordered by Pool, Name
-> N/A value for Begin MB or End MB indicates the size of that Pool/Name was
insignificant, or zero in that snapshot
Pool Name Begin MB End MB % Diff
—— —————————— ————– ————– ——-
java free memory 16.0 16.0 0.00
large ASM map operations hashta .4 .4 0.00
large PX msg pool 1.0 1.0 0.00
large free memory 14.6 14.6 0.00
shared ASH buffers 8.0 8.0 0.00
shared CCursor 10.7 10.7 0.04
shared Heap0: KGL 4.6 4.6 0.09
shared KCB Table Scan Buffer 4.0 4.0 0.00
shared KGLS heap 5.2 5.2 0.00
shared KQR L PO 7.6 7.6 0.00
shared KSFD SGA I/O b 4.0 4.0 0.00
shared PCursor 4.8 4.8 0.02
shared PL/SQL DIANA 3.1 3.1 0.00
shared PL/SQL MPCODE 4.3 4.3 0.00
shared free memory 61.2 60.9 -0.43
shared gcs resources 24.2 24.2 0.00
shared gcs shadows 14.5 14.5 0.00
shared ges big msg buffers 10.6 10.6 0.00
shared ges reserved msg buffers 4.7 4.7 0.00
shared ges resource 4.0 4.0 0.00
shared kglsim hash table bkts 4.0 4.0 0.00
shared library cache 16.5 16.5 0.10
shared row cache 7.2 7.2 0.00
shared sql area 51.3 51.3 0.10
shared trace buffer 4.7 4.7 0.00
buffer_cache 1,152.0 1,152.0 0.00
fixed_sga 2.0 2.0 0.00
log_buffer 4.1 4.1 0.00
————————————————————-
Streams CPU/IO Usage DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
No data exists for this section of the report.
————————————————————-
Streams Capture DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
No data exists for this section of the report.
————————————————————-
Streams Apply DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
No data exists for this section of the report.
————————————————————-
Buffered Queues DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
No data exists for this section of the report.
————————————————————-
Buffered Subscribers DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
No data exists for this section of the report.
————————————————————-
Rule Set DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
No data exists for this section of the report.
————————————————————-
Resource Limit Stats DB/Inst: ORCLDB/orcldb1 Snap: 71
-> only rows with Current or Maximum Utilization > 80% of Limit are shown
-> ordered by resource name
Current Maximum Initial
Resource Name Utilization Utilization Allocation Limit
—————————— ———— ———— ———- ———-
gcs_resources 102,220 153,991 156544 156544
gcs_shadows 93,367 156,504 156544 156544
ges_procs 146 300 320 320
processes 145 300 300 300
sessions 150 335 335 335
————————————————————-
init.ora Parameters DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
End value
Parameter Name Begin value (if different)
—————————– ——————————— ————–
audit_file_dest /u01/app/oracle/admin/orcldb/adum
background_dump_dest /u01/app/oracle/admin/orcldb/bdum
cluster_database TRUE
cluster_database_instances 4
compatible 10.2.0.3.0
control_files +DB/orcldb/controlfile/current.26
core_dump_dest /u01/app/oracle/admin/orcldb/cdum
db_block_size 8192
db_create_file_dest +DB
db_domain
db_file_multiblock_read_count 16
db_name orcldb
dispatchers (PROTOCOL=TCP) (SERVICE=orcldbXDB
instance_number 1
job_queue_processes 10
open_cursors 300
pga_aggregate_target 848297984
processes 300
remote_listener LISTENERS_ORCLDB
remote_login_passwordfile EXCLUSIVE
sessions 335
sga_target 1610612736
spfile +DB/orcldb/spfileorcldb.ora
thread 1
undo_management AUTO
undo_tablespace UNDOTBS1
user_dump_dest /u01/app/oracle/admin/orcldb/udum
————————————————————-
Global Enqueue Statistics DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
Statistic Total per Second per Trans
——————————— —————- ———— ————
acks for commit broadcast(actual) 925 0.3 0.2
acks for commit broadcast(logical 937 0.3 0.3
broadcast msgs on commit(actual) 810 0.2 0.2
broadcast msgs on commit(logical) 813 0.2 0.2
broadcast msgs on commit(wasted) 73 0.0 0.0
broadcast on commit wait time(ms) 19,568 5.4 5.3
broadcast on commit waits 240 0.1 0.1
dynamically allocated gcs resourc 0 0.0 0.0
dynamically allocated gcs shadows 0 0.0 0.0
false posts waiting for scn acks 0 0.0 0.0
flow control messages received 0 0.0 0.0
flow control messages sent 0 0.0 0.0
gcs assume cvt 13 0.0 0.0
gcs assume no cvt 594 0.2 0.2
gcs ast xid 0 0.0 0.0
gcs blocked converts 1,125 0.3 0.3
gcs blocked cr converts 773 0.2 0.2
gcs compatible basts 26 0.0 0.0
gcs compatible cr basts (global) 55 0.0 0.0
gcs compatible cr basts (local) 82 0.0 0.0
gcs cr basts to PIs 1 0.0 0.0
gcs cr serve without current lock 0 0.0 0.0
gcs dbwr flush pi msgs 76 0.0 0.0
gcs dbwr write request msgs 118 0.0 0.0
gcs error msgs 0 0.0 0.0
gcs forward cr to pinged instance 3 0.0 0.0
gcs immediate (compatible) conver 2 0.0 0.0
gcs immediate (null) converts 17 0.0 0.0
gcs immediate cr (compatible) con 11 0.0 0.0
gcs immediate cr (null) converts 49 0.0 0.0
gcs indirect ast 418 0.1 0.1
gcs lms flush pi msgs 100 0.0 0.0
gcs lms write request msgs 98 0.0 0.0
gcs msgs process time(ms) 585 0.2 0.2
gcs msgs received 5,749 1.6 1.5
gcs out-of-order msgs 0 0.0 0.0
gcs pings refused 2 0.0 0.0
gcs pkey conflicts retry 0 0.0 0.0
gcs queued converts 39 0.0 0.0
gcs recovery claim msgs 0 0.0 0.0
gcs refuse xid 0 0.0 0.0
gcs regular cr 400 0.1 0.1
gcs retry convert request 0 0.0 0.0
gcs side channel msgs actual 237 0.1 0.1
gcs side channel msgs logical 1,142 0.3 0.3
gcs stale cr 0 0.0 0.0
gcs undo cr 7 0.0 0.0
gcs write notification msgs 1 0.0 0.0
gcs writes refused 0 0.0 0.0
ges msgs process time(ms) 1,179 0.3 0.3
ges msgs received 30,677 8.5 8.3
global posts dropped 0 0.0 0.0
global posts queue time 27 0.0 0.0
global posts queued 39 0.0 0.0
global posts requested 39 0.0 0.0
global posts sent 39 0.0 0.0
implicit batch messages received 335 0.1 0.1
implicit batch messages sent 274 0.1 0.1
lmd msg send time(ms) 0 0.0 0.0
lms(s) msg send time(ms) 0 0.0 0.0
Global Enqueue Statistics DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
Statistic Total per Second per Trans
——————————— —————- ———— ————
messages flow controlled 2,464 0.7 0.7
messages queue sent actual 17,213 4.8 4.6
messages queue sent logical 17,690 4.9 4.8
messages received actual 32,405 9.0 8.7
messages received logical 36,426 10.1 9.8
messages sent directly 14,030 3.9 3.8
messages sent indirectly 10,000 2.8 2.7
messages sent not implicit batche 16,939 4.7 4.6
messages sent pbatched 23,040 6.4 6.2
msgs causing lmd to send msgs 12,286 3.4 3.3
msgs causing lms(s) to send msgs 1,285 0.4 0.3
msgs received queue time (ms) 948 0.3 0.3
msgs received queued 36,426 10.1 9.8
msgs sent queue time (ms) 1,360 0.4 0.4
msgs sent queue time on ksxp (ms) 14,769 4.1 4.0
msgs sent queued 17,499 4.8 4.7
msgs sent queued on ksxp 33,138 9.2 8.9
process batch messages received 21,031 5.8 5.7
process batch messages sent 15,513 4.3 4.2
————————————————————-
Global CR Served Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
Statistic Total
—————————— ——————
CR Block Requests 295
CURRENT Block Requests 94
Data Block Requests 295
Undo Block Requests 10
TX Block Requests 30
Current Results 383
Private results 2
Zero Results 3
Disk Read Results 0
Fail Results 0
Fairness Down Converts 43
Fairness Clears 0
Free GC Elements 0
Flushes 16
Flushes Queued 0
Flush Queue Full 0
Flush Max Time (us) 0
Light Works 60
Errors 0
————————————————————-
Global CURRENT Served Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Pins = CURRENT Block Pin Operations
-> Flushes = Redo Flush before CURRENT Block Served Operations
-> Writes = CURRENT Block Fusion Write Operations
Statistic Total % <1ms % <10ms % <100ms % <1s % <10s
———- ———— ——– ——– ——– ——– ——–
Pins 520 100.00 0.00 0.00 0.00 0.00
Flushes 23 26.09 65.22 8.70 0.00 0.00
Writes 78 29.49 67.95 2.56 0.00 0.00
————————————————————-
Global Cache Transfer Stats DB/Inst: ORCLDB/orcldb1 Snaps: 70-71
-> Immediate (Immed) – Block Transfer NOT impacted by Remote Processing Delays
-> Busy (Busy) – Block Transfer impacted by Remote Contention
-> Congested (Congst) – Block Transfer impacted by Remote System Load
-> ordered by CR + Current Blocks Received desc
CR Current
—————————– —————————–
Inst Block Blocks % % % Blocks % % %
No Class Received Immed Busy Congst Received Immed Busy Congst
—- ———– ——– —— —— —— ——– —— —— ——
3 data block 156 93.6 6.4 .0 237 91.1 8.9 .0
4 data block 185 95.7 4.3 .0 206 94.2 5.8 .0
3 Others 54 100.0 .0 .0 35 94.3 5.7 .0
4 Others 33 100.0 .0 .0 9 100.0 .0 .0
2 data block 7 100.0 .0 .0 23 100.0 .0 .0
3 undo header 24 83.3 16.7 .0 3 100.0 .0 .0
4 undo header 14 100.0 .0 .0 1 100.0 .0 .0
3 undo block 10 90.0 10.0 .0 0 N/A N/A N/A
2 Others 1 100.0 .0 .0 1 100.0 .0 .0
4 undo block 2 100.0 .0 .0 0 N/A N/A N/A
————————————————————-
End of Report
Report written to awrrpt_1_70_71.txt
SQL> quit
Disconnected from 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
[ractest@sun880-1 /u01/app/oracle/orahome/rdbms/admin]$ exit
script done on Sun 27 Jan 2008 08:48:16 PM PST
3433
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
