원문: http://www.akadia.com/services/ora_statspack_survival_guide.html
원문을 번역한것은 아니며, 원문을 참고하여, statspack을 설치, 구성하였음.
oracle STATSPACK의 설치와 보고서 생성.
1. statspack이 사용할 테이블 스페이스(perfstat) 생성.
SQL> create tablespace perfstat
2 datafile ‘/ora_data/stats_pack/perfstat.dbf’ size 1000M reuse
3 extent management local uniform size 512k
4 segment space management auto
5 permanent
6 online;
reuse 옵션: 해당 위치에 동일한 파일이 있는 경우 그 화일을 재사용. 옵션 사용하기전에 확인해야 할듯.
2. 설치스크립트가 있는곳($ORACLE_HOME/rdbms/admin)으로 이동하여 설치 시작한다. 비밀번호는 perfstat, 사용할테이블 스페이스는 이전에 생성한 perfstats, temporary 테이블스페이스는 temp 를 사용한다.
SQL> start spcreate.sql
Choose the PERFSTAT user’s password
———————————–
Not specifying a password will result in the installation FAILINGEnter value for perfstat_password: perfstat
perfstatChoose the Default tablespace for the PERFSTAT user
—————————————————
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user’s
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.Choose the PERFSTAT users’s default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
—————————— ——— —————————-
EXAMPLE PERMANENT
PERFSTAT PERMANENT
SNOWFOX PERMANENT
SYSAUX PERMANENT *
USERS PERMANENTPressing <return> will result in STATSPACK’s recommended default
tablespace (identified by *) being used.Enter value for default_tablespace: PERFSTAT
Using tablespace PERFSTAT as PERFSTAT default tablespace.
Choose the PERFSTAT user’s Temporary tablespace.
TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
—————————— ——— ————————–
SNOWFOX_TEMP TEMPORARY
TEMP TEMPORARY *Pressing <return> will result in the database’s default Temporary
tablespace (identified by *) being used.Enter value for temporary_tablespace: TEMP
……
SQL> set echo off;
Creating Package STATSPACK…Package created.
No errors.
Creating Package Body STATSPACK…Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
3. statspack 삭제.
설치시 원하지 않는 테이블스페이스나, 비밀번호를 사용했다면, 삭제후 다시 설치하면된다.
삭제는
SQL> @spdrop.sql
다시 설치하려고 start spcreate.sql 하면, 제대로 설치되지 않는다. 이때는 perfstat user를 삭제한다.
SQL> drop user perfstat;
혹시
SQL> drop user perfstat;
drop user perfstat
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop ‘PERFSTAT’
이런 에러가 난다면,
SQL> drop user perfstat cascade;
statspack을 완전히 삭제하려면,
SQL> @spdrop.sql
SQL> DROP TABLESPACE perfstat INCLUDING CONTENTS AND DATAFILES;
4. 데이타 수집(자동으로 수집하는 방법)
SQL> @spauto.sql
PL/SQL procedure successfully completed.
Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:JOBNO
———-
41
Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter – the value for this parameter must be greater
than 0 to use automatic statistics gathering:NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 10
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:JOB NEXT_DATE NEXT_SEC
———- ——— ——–
41 26-MAR-12 23:00:00SQL>
5. Report 보기
5.1 spreport.sql
$ORACLE_HOME/rdbms/admin/spreport.sql 실행.
SQL> conn perfstat/perfstat
[oracle@oracle admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Sat Mar 24 20:54:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn perfstat/perfstat
Connected.
SQL> @spreport.sqlCurrent Instance
~~~~~~~~~~~~~~~~DB Id DB Name Inst Num Instance
———– ———— ——– ————
1303881023 ORCL 1 orcl
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~DB Id Inst Num DB Name Instance Host
———– ——– ———— ———— ————
1303881023 1 ORCL orcl oracleUsing 1303881023 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 <return> without
specifying a number lists all completed snapshots.
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
———— ———— ——— —————– —– ——————–
orcl ORCL 1 20 Mar 2012 17:21 5
2 20 Mar 2012 18:00 5
11 20 Mar 2012 19:00 5
12 20 Mar 2012 20:00 5
13 20 Mar 2012 21:00 5
14 20 Mar 2012 22:00 521 20 Mar 2012 23:00 5
31 21 Mar 2012 00:00 5
41 21 Mar 2012 01:00 5
42 21 Mar 2012 02:00 5
43 21 Mar 2012 03:00 5
44 21 Mar 2012 04:00 5
45 21 Mar 2012 05:00 5
46 21 Mar 2012 06:00 5
47 21 Mar 2012 07:00 5
48 21 Mar 2012 08:00 5
49 21 Mar 2012 09:00 550 21 Mar 2012 10:00 5
60 21 Mar 2012 11:00 5
61 21 Mar 2012 12:00 5
70 21 Mar 2012 13:00 5
80 21 Mar 2012 14:00 5
90 21 Mar 2012 15:00 5
100 21 Mar 2012 16:00 5
110 21 Mar 2012 17:00 5111 22 Mar 2012 09:09 5
121 22 Mar 2012 10:00 5
122 22 Mar 2012 11:00 5
131 22 Mar 2012 12:00 5
132 22 Mar 2012 13:00 5
141 22 Mar 2012 14:00 5
151 22 Mar 2012 15:00 5
161 22 Mar 2012 16:00 5
162 22 Mar 2012 17:00 5
163 22 Mar 2012 18:00 5
164 22 Mar 2012 19:00 5
165 22 Mar 2012 20:00 5
166 22 Mar 2012 21:00 5
167 22 Mar 2012 22:00 5171 22 Mar 2012 23:00 5
172 23 Mar 2012 00:00 5
181 23 Mar 2012 01:00 5
182 23 Mar 2012 02:00 5
183 23 Mar 2012 03:00 5
184 23 Mar 2012 04:00 5
185 23 Mar 2012 05:00 5
186 23 Mar 2012 06:00 5
187 23 Mar 2012 07:00 5
188 23 Mar 2012 08:00 5
189 23 Mar 2012 09:00 5
190 23 Mar 2012 10:00 5Snap
Instance DB Name Snap Id Snap Started Level Comment
———— ———— ——— —————– —– ——————–
orcl ORCL 191 23 Mar 2012 11:00 5
192 23 Mar 2012 12:00 5
193 23 Mar 2012 13:00 5
194 23 Mar 2012 14:00 5
195 23 Mar 2012 15:00 5
196 23 Mar 2012 16:00 5
197 23 Mar 2012 17:00 5
198 23 Mar 2012 18:00 5
199 23 Mar 2012 19:00 5
200 23 Mar 2012 20:00 5
201 23 Mar 2012 21:00 5
202 23 Mar 2012 22:00 5
211 23 Mar 2012 23:00 5
212 24 Mar 2012 00:00 5
213 24 Mar 2012 01:00 5
214 24 Mar 2012 02:00 5
215 24 Mar 2012 03:00 5
216 24 Mar 2012 04:00 5
217 24 Mar 2012 05:00 5
218 24 Mar 2012 06:00 5
221 24 Mar 2012 07:00 5
222 24 Mar 2012 08:00 5
223 24 Mar 2012 09:00 5
224 24 Mar 2012 10:00 5
225 24 Mar 2012 11:00 5
226 24 Mar 2012 12:00 5
227 24 Mar 2012 13:00 5
228 24 Mar 2012 14:00 5
229 24 Mar 2012 15:00 5
230 24 Mar 2012 16:00 5
231 24 Mar 2012 17:00 5
232 24 Mar 2012 18:00 5
233 24 Mar 2012 18:59 5
234 24 Mar 2012 19:00 5
241 24 Mar 2012 20:00 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 171
Begin Snapshot Id specified: 171Enter value for end_snap: 241
End Snapshot Id specified: 241
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_171_241. To use this name,
press <return> to continue, otherwise enter an alternative.Enter value for report_name: report.rep
Using the report name report.rep
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ———– ———— ——– ————— ———– —
1303881023 orcl 1 22-Mar-12 22:31 10.2.0.1.0 NOHost Name: oracle Num CPUs: 2 Phys Memory (MB): 0
~~~~Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ———- —————— ——– ——— ——————-
Begin Snap: 171 22-Mar-12 23:00:05 19 3.4
End Snap: 241 24-Mar-12 20:00:04 19 4.6
Elapsed: 2,699.98 (mins)Cache Sizes Begin End
~~~~~~~~~~~ ———- ———-
Buffer Cache: 132M 176M Std Block Size: 8K
Shared Pool Size: 152M 108M Log Buffer: 6,027KLoad Profile Per Second Per Transaction
~~~~~~~~~~~~ ————— —————
Redo size: 977.36 11,187.86
Logical reads: 27.06 309.73
Block changes: 4.49 51.42
Physical reads: 0.20 2.33
Physical writes: 0.36 4.14
User calls: 0.10 1.18
Parses: 1.00 11.42
Hard parses: 0.04 0.48
Sorts: 0.72 8.28
Logons: 0.03 0.36
Executes: 2.46 28.20
Transactions: 0.09% Blocks changed per Read: 16.60 Recursive Call %: 99.41
Rollback per transaction %: 0.00 Rows per Sort: 116.70Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.25 In-memory Sort %: 100.00
Library Hit %: 96.25 Soft Parse %: 95.78
Execute to Parse %: 59.50 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 88.96 % Non-Parse CPU: 87.12Shared Pool Statistics Begin End
—— ——
Memory Usage %: 51.50 96.47
% SQL with executions>1: 81.25 87.68
% Memory for SQL w/exec>1: 88.87 95.30Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
—————————————– ———— ———– —— ——
control file parallel write 53,861 935 17 51.4
CPU time 361 19.9
os thread startup 2,464 234 95 12.9
log file parallel write 21,802 183 8 10.1
db file sequential read 14,217 44 3 2.4
————————————————————-
Host CPU (CPUs: 2)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
——- ——- ——- ——- ——- ——- ——–
0.08 0.00 0.13 0.11 99.77Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 0.19
% of busy CPU for Instance: 81.16
%DB time waiting for CPU – Resource Mgr:Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ———— ————
Host Mem (MB): .4 .0
SGA use (MB): 300.0 300.0
PGA use (MB): 88.6 94.1
% Host Mem used for SGA+PGA: 100041.3 4841243.1
————————————————————-Time Model System Stats DB/Inst: ORCL/orcl Snaps: 171-241
-> Ordered by % of DB time desc, Statistic nameStatistic Time (s) % of DB time
———————————– ——————– ————
sql execute elapsed time 612.2 129.6
DB CPU 338.3 71.6
parse time elapsed 115.8 24.5
PL/SQL execution elapsed time 105.5 22.3
hard parse elapsed time 64.4 13.6
PL/SQL compilation elapsed time 5.3 1.1
hard parse (sharing criteria) elaps 4.0 .8
repeated bind elapsed time 0.7 .2
connection management call elapsed 0.6 .1
hard parse (bind mismatch) elapsed 0.6 .1
failed parse elapsed time 0.1 .0
sequence load elapsed time 0.1 .0
DB time 472.2
background elapsed time 1,732.2
background cpu time 276.0
————————————————————-
Wait Events DB/Inst: ORCL/orcl Snaps: 171-241
-> s – second, cs – centisecond, ms – millisecond, us – microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
——————————— ———— —— ———- —— ——–
control file parallel write 53,861 0 935 17 3.8
os thread startup 2,464 0 234 95 0.2
log file parallel write 21,802 0 183 8 1.5
db file sequential read 14,217 0 44 3 1.0
log file sync 3,145 0 33 10 0.2
db file scattered read 2,848 0 18 6 0.2
latch free 58 0 4 70 0.0
Log archive I/O 202 0 1 7 0.0
log file switch completion 12 0 1 99 0.0
log file sequential read 198 0 1 6 0.0
control file sequential read 88,157 0 1 0 6.2
latch: shared pool 502 0 1 1 0.0
latch: library cache 594 0 1 1 0.0
rdbms ipc reply 167 0 0 1 0.0
SGA: allocation forcing component 8 25 0 11 0.0
log file single write 8 0 0 10 0.0
buffer busy waits 61 0 0 1 0.0
reliable message 15 0 0 4 0.0
LGWR wait for redo copy 770 0 0 0 0.1
row cache lock 1 0 0 23 0.0
undo segment extension 67 96 0 0 0.0
library cache load lock 1 0 0 10 0.0
enq: RO – fast object reuse 49 0 0 0 0.0
SQL*Net break/reset to client 24 0 0 0 0.0
latch: row cache objects 15 0 0 0 0.0
SQL*Net more data to client 34 0 0 0 0.0
latch: redo allocation 21 0 0 0 0.0
latch: cache buffers lru chain 9 0 0 0 0.0
latch: In memory undo latch 1 0 0 1 0.0
latch: cache buffers chains 5 0 0 0 0.0
Streams AQ: qmn slave idle wait 5,779 0 157,735 27295 0.4
Streams AQ: qmn coordinator idle 11,620 50 157,735 13574 0.8
virtual circuit status 5,400 100 157,601 29185 0.4
Streams AQ: waiting for time mana 1,450 58 150,318 ###### 0.1
jobq slave wait 50,327 99 147,050 2922 3.6
SQL*Net message from client 1,076 0 7,466 6939 0.1
class slave wait 54 100 264 4884 0.0
SQL*Net message to client 1,077 0 0 0 0.1
————————————————————-
Background Wait Events DB/Inst: ORCL/orcl Snaps: 171-241
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
——————————— ———— —— ———- —— ——–
control file parallel write 53,861 0 935 17 3.8
os thread startup 2,464 0 234 95 0.2
log file parallel write 21,802 0 183 8 1.5
db file sequential read 5,395 0 4 1 0.4
Log archive I/O 202 0 1 7 0.0
log file sequential read 198 0 1 6 0.0
control file sequential read 46,265 0 1 0 3.3
db file scattered read 21 0 0 7 0.0
latch: library cache 61 0 0 2 0.0
events in waitclass Other 916 0 0 0 0.1
log file single write 8 0 0 10 0.0
latch: shared pool 29 0 0 3 0.0
buffer busy waits 43 0 0 2 0.0
row cache lock 1 0 0 23 0.0
log file sync 2 0 0 11 0.0
rdbms ipc message 541,028 96 1,699,422 3141 38.2
pmon timer 56,118 100 158,127 2818 4.0
Streams AQ: qmn slave idle wait 5,779 0 157,735 27295 0.4
Streams AQ: qmn coordinator idle 11,620 50 157,735 13574 0.8
smon timer 618 85 152,608 ###### 0.0
Streams AQ: waiting for time mana 1,450 58 150,318 ###### 0.1
————————————————————-
Wait Event Histogram DB/Inst: ORCL/orcl Snaps: 171-241
-> Total Waits – units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits – column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits – value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)Total —————– % of Waits ——————
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
————————– —– —– —– —– —– —– —– —– —–
LGWR wait for redo copy 770 99.6 .1 .3
Log archive I/O 202 9.9 4.0 63.9 18.8 3.0 .5
SGA: allocation forcing co 8 100.0
SQL*Net break/reset to cli 24 100.0
SQL*Net more data to clien 34 100.0
buffer busy waits 61 82.0 8.2 4.9 1.6 3.3
control file parallel writ 53K 8.7 5.6 37.7 42.7 5.4 .0
control file sequential re 88K 100.0 .0 .0 .0 .0
db file scattered read 2846 42.6 16.6 11.1 6.1 11.8 9.1 2.6
db file sequential read 14K 81.9 2.8 1.5 4.3 4.8 3.1 1.5
direct path read 28 100.0
direct path write 58 100.0
direct path write temp 66 100.0
enq: RO – fast object reus 49 100.0
latch free 57 19.3 1.8 78.9
latch: In memory undo latc 1 100.0
latch: cache buffers chain 5 100.0
latch: cache buffers lru c 8 100.0
latch: enqueue hash chains 3 100.0
latch: library cache 534 87.6 6.4 2.8 1.3 .6 .9 .4
latch: messages 1 100.0
latch: object queue header 1 100.0
latch: redo allocation 21 100.0
latch: row cache objects 15 93.3 6.7
latch: session allocation 5 100.0
latch: shared pool 464 70.5 14.9 9.1 2.8 1.5 1.1 .2
library cache load lock 1 100.0
log file parallel write 21K 23.0 30.7 7.7 6.1 18.0 10.9 3.6 .0
log file sequential read 198 32.8 19.2 22.2 8.6 9.1 4.5 3.5
log file single write 8 75.0 12.5 12.5
log file switch completion 12 8.3 16.7 75.0
log file sync 3145 29.8 25.3 8.3 11.8 15.8 6.4 2.7
os thread startup 2464 .0 100.0
rdbms ipc reply 167 70.7 7.2 21.0 .6 .6
reliable message 15 93.3 6.7
row cache lock 1 100.0
undo segment extension 67 98.5 1.5
SQL*Net message from clien 1076 77.5 12.1 1.2 .6 .1 .3 .5 7.8
SQL*Net message to client 1077 100.0
SQL*Net more data from cli 64 100.0
Streams AQ: qmn coordinato 11K 49.6 .1 .0 .0 .0 50.3
Streams AQ: qmn slave idle 5779 .0 100.0
Streams AQ: waiting for ti 1450 41.5 17.0 41.5
class slave wait 54 100.0
dispatcher timer 2700 100.0
jobq slave wait 50K .0 .0 .0 .2 99.8
pmon timer 56K 3.7 .1 .0 .0 .0 96.2
rdbms ipc message 541K .7 .2 .2 .5 .3 .4 31.0 66.7
smon timer 618 1.5 .2 2.1 .5 1.8 2.9 91.1
virtual circuit status 5400 .1 99.9
Wait Event Histogram DB/Inst: ORCL/orcl Snaps: 171-241
-> Total Waits – units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits – column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits – value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)Total —————– % of Waits ——————
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
————————– —– —– —– —– —– —– —– —– —–
————————————————————-
SQL ordered by CPU DB/Inst: ORCL/orcl Snaps: 171-241
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 338
-> Captured SQL accounts for 38.3% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPUCPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
———- ———— ———- —— ———- ————— ———-
57.58 1,181 0.05 17.0 58.16 240,172 2689373535
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_J
OB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :
b := 0; END IF; END;12.34 13 0.95 3.6 12.56 41,395 333476733
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_d
ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;9.79 879 0.01 2.9 10.45 64,727 3142066274
CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :resu
lt)4.09 788 0.01 1.2 4.09 6,365 2594425492
select u1.user#, u2.user#, u3.user#, failures, flag, interval#,
what, nlsenv, env, field1 from sys.job$ j, sys.user$ u1, sys
.user$ u2, sys.user$ u3 where job=:1 and (next_date < sysdate o
r :2 != 0) and lowner = u1.name and powner = u2.name and cowner
= u3.name————————————————————-
SQL ordered by Elapsed DB/Inst: ORCL/orcl Snaps: 171-241
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB Time (s): 472
-> Captured SQL accounts for 30.0% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB TimeElapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
———- ———— ———- —— ———- ————— ———-
58.16 1,181 0.05 12.3 57.58 -931 2689373535
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_J
OB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :
b := 0; END IF; END;12.56 13 0.97 2.7 12.34 114 333476733
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_d
ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;10.45 879 0.01 2.2 9.79 137 3142066274
CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :resu
lt)————————————————————-
SQL ordered by Gets DB/Inst: ORCL/orcl Snaps: 171-241
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 4,383,310
-> Captured SQL accounts for 19.5% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer GetsCPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
————— ———— ————– —— ——– ——— ———-
240,172 1,181 203.4 5.5 57.58 58.16 2689373535
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_J
OB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :
b := 0; END IF; END;95,125 116 820.0 2.2 1.87 1.88 1393382093
select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM, WT, INS
T, 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, :85,418 285 299.7 1.9 0.44 0.46 146931816
delete from smon_scn_time where thread=0 and scn = (select min(
scn) from smon_scn_time where thread=0)80,326 264 304.3 1.8 0.55 0.61 4088878846
update smon_scn_time set orig_thread=0, time_mp=:1, time_dp=:2,
scn=:3, scn_wrp=:4, scn_bas=:5, num_mappings=:6, tim_scn_map=:
7 where thread=0 and scn = (select min(scn) from smon_scn_time
where thread=0)64,727 879 73.6 1.5 9.79 10.45 3142066274
CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :resu
lt)————————————————————-
SQL ordered by Reads DB/Inst: ORCL/orcl Snaps: 171-241
-> End Disk Reads Threshold: 1000 Total Disk Reads: 32,957
-> Captured SQL accounts for 8.1% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk ReadsCPU Elapsd Old
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
————— ———— ————– —— ——– ——— ———-
1,328 612 2.2 4.0 1.07 4.46 986338823
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fr
om idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#403 612 0.7 1.2 0.39 2.02 386388955
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece fr
om idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#————————————————————-
SQL ordered by Executions DB/Inst: ORCL/orcl Snaps: 171-241
-> End Executions Threshold: 100 Total Executions: 399,043
-> Captured SQL accounts for 37.4% of Total Executions
-> SQL reported below exceeded 1.0% of Total ExecutionsCPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
———— ————— —————- ———– ———- ———-
11,823 10,375 0.9 0.00 0.00 4274598960
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#
=:1 and intcol#=:28,653 12,739 1.5 0.00 0.00 2482976222
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c
on#=:17,484 7,484 1.0 0.00 0.00 1693927332
select count(*) from sys.job$ where (next_date > sysdate) and (n
ext_date < (sysdate+5/86400))5,454 113,481 20.8 0.00 0.00 87488304
select size_for_estimate, size_factor * 100
f, estd_physical_read_time, e
std_physical_reads from v$db_cache_advice where id
= ‘3’5,454 10,908 2.0 0.00 0.00 303149640
select java_pool_size_for_estimate s, java_pool_size_f
actor * 100 f, estd_lc_load_time l, 0 from
v$java_pool_advice5,454 77,116 14.1 0.00 0.00 1301067080
select shared_pool_size_for_estimate s, shared_pool_siz
e_factor * 100 f, estd_lc_load_time l, 0 fr
om v$shared_pool_advice4,127 33,960 8.2 0.00 0.00 4143084494
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>04,050 11,247 2.8 0.00 0.00 2889900621
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
=:1————————————————————-
SQL ordered by Parse Calls DB/Inst: ORCL/orcl Snaps: 171-241
-> End Parse Calls Threshold: 1000 Total Parse Calls: 161,598
-> Captured SQL accounts for 35.2% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls% Total Old
Parse Calls Executions Parses Hash Value
———— ———— ——– ———-
4,126 4,127 2.55 4143084494
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>02,030 2,030 1.26 2396279102
lock table sys.mon_mods$ in exclusive mode nowait2,002 2,002 1.24 1566118407
declare vsn varchar2(20); begin
vsn := dbms_rcvman.getPackageVersion; :p
kg_vsn:pkg_vsn_i := vsn; if vsn is not null then
:pkg_vsnub4 := to_number(subs
tr(vsn,1,2) || substr(vsn,4,2) || s————————————————————-
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 171-241Statistic Total per Second per Trans
——————————— —————— ————– ————
CPU used by this session 36,074 0.2 2.6
CPU used when call started 6,172 0.0 0.4
CR blocks created 3,508 0.0 0.3
Cached Commit SCN referenced 1,769 0.0 0.1
Commit SCN cached 15 0.0 0.0
DB time 15,098,331 93.2 1,066.9
DBWR checkpoint buffers written 55,593 0.3 3.9
DBWR checkpoints 41 0.0 0.0
DBWR transaction table writes 4,213 0.0 0.3
DBWR undo block writes 13,264 0.1 0.9
IMU CR rollbacks 517 0.0 0.0
IMU Flushes 1,131 0.0 0.1
IMU Redo allocation size 6,342,040 39.2 448.1
IMU commits 12,147 0.1 0.9
IMU contention 2 0.0 0.0
IMU ktichg flush 2 0.0 0.0
IMU pool not allocated 5,744 0.0 0.4
IMU recursive-transaction flush 12 0.0 0.0
IMU undo allocation size 57,534,072 355.2 4,065.4
IMU- failed to get a private stra 5,744 0.0 0.4
SMON posted for undo segment shri 26 0.0 0.0
SQL*Net roundtrips to/from client 1,071 0.0 0.1
active txn count during cleanout 4,031 0.0 0.3
application wait time 1 0.0 0.0
background checkpoints completed 4 0.0 0.0
background checkpoints started 4 0.0 0.0
background timeouts 521,077 3.2 36.8
buffer is not pinned count 1,837,703 11.3 129.9
buffer is pinned count 1,034,930 6.4 73.1
bytes received via SQL*Net from c 345,192 2.1 24.4
bytes sent via SQL*Net to client 462,909 2.9 32.7
calls to get snapshot scn: kcmgss 641,413 4.0 45.3
calls to kcmgas 39,889 0.3 2.8
calls to kcmgcs 3,338 0.0 0.2
change write time 1,872 0.0 0.1
cleanout – number of ktugct calls 5,423 0.0 0.4
cleanouts and rollbacks – consist 609 0.0 0.0
cleanouts only – consistent read 718 0.0 0.1
cluster key scan block gets 355,901 2.2 25.2
cluster key scans 82,992 0.5 5.9
commit batch/immediate performed 29 0.0 0.0
commit batch/immediate requested 29 0.0 0.0
commit cleanout failures: block l 88 0.0 0.0
commit cleanout failures: callbac 640 0.0 0.1
commit cleanouts 83,220 0.5 5.9
commit cleanouts successfully com 82,492 0.5 5.8
commit immediate performed 29 0.0 0.0
commit immediate requested 29 0.0 0.0
commit txn count during cleanout 3,133 0.0 0.2
concurrency wait time 23,522 0.2 1.7
consistent changes 5,032 0.0 0.4
consistent gets 3,766,593 23.3 266.2
consistent gets – examination 1,222,986 7.6 86.4
consistent gets direct 7 0.0 0.0
consistent gets from cache 3,766,586 23.3 266.2
cursor authentications 2,333 0.0 0.2
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 171-241Statistic Total per Second per Trans
——————————— —————— ————– ————
data blocks consistent reads – un 3,765 0.0 0.3
db block changes 727,668 4.5 51.4
db block gets 616,717 3.8 43.6
db block gets direct 635 0.0 0.0
db block gets from cache 616,082 3.8 43.5
deferred (CURRENT) block cleanout 50,262 0.3 3.6
dirty buffers inspected 1,801 0.0 0.1
enqueue conversions 34,853 0.2 2.5
enqueue releases 1,440,231 8.9 101.8
enqueue requests 1,441,531 8.9 101.9
enqueue timeouts 1,301 0.0 0.1
enqueue waits 28 0.0 0.0
execute count 399,043 2.5 28.2
free buffer inspected 33,019 0.2 2.3
free buffer requested 46,764 0.3 3.3
heap block compress 1,480 0.0 0.1
hot buffers moved to head of LRU 18,742 0.1 1.3
immediate (CR) block cleanout app 1,327 0.0 0.1
immediate (CURRENT) block cleanou 15,920 0.1 1.1
index fast full scans (full) 1,218 0.0 0.1
index fetch by key 553,614 3.4 39.1
index scans kdiixs1 475,134 2.9 33.6
leaf node 90-10 splits 459 0.0 0.0
leaf node splits 1,776 0.0 0.1
lob reads 6,532 0.0 0.5
lob writes 289 0.0 0.0
lob writes unaligned 289 0.0 0.0
logons cumulative 5,101 0.0 0.4
messages received 61,854 0.4 4.4
messages sent 61,854 0.4 4.4
no buffer to keep pinned count 0 0.0 0.0
no work – consistent read gets 1,885,457 11.6 133.2
opened cursors cumulative 212,509 1.3 15.0
parse count (failures) 19 0.0 0.0
parse count (hard) 6,825 0.0 0.5
parse count (total) 161,598 1.0 11.4
parse time cpu 4,648 0.0 0.3
parse time elapsed 5,225 0.0 0.4
physical read IO requests 16,751 0.1 1.2
physical read bytes 269,983,744 1,666.6 19,077.4
physical read total IO requests 105,526 0.7 7.5
physical read total bytes 1,907,022,336 11,771.8 134,752.9
physical read total multi block r 3,094 0.0 0.2
physical reads 32,957 0.2 2.3
physical reads cache 32,922 0.2 2.3
physical reads cache prefetch 16,206 0.1 1.2
physical reads direct 35 0.0 0.0
physical reads direct temporary t 0 0.0 0.0
physical reads prefetch warmup 2,369 0.0 0.2
physical write IO requests 41,339 0.3 2.9
physical write bytes 480,321,536 2,965.0 33,940.2
physical write total IO requests 224,721 1.4 15.9
physical write total bytes 3,487,707,136 21,529.2 246,446.2
physical write total multi block 25,613 0.2 1.8
physical writes 58,633 0.4 4.1
physical writes direct 663 0.0 0.1
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 171-241Statistic Total per Second per Trans
——————————— —————— ————– ————
physical writes direct (lob) 19 0.0 0.0
physical writes direct temporary 616 0.0 0.0
physical writes from cache 57,970 0.4 4.1
physical writes non checkpoint 14,241 0.1 1.0
pinned buffers inspected 1 0.0 0.0
prefetch warmup blocks aged out b 636 0.0 0.0
prefetched blocks aged out before 34 0.0 0.0
process last non-idle time 161,999 1.0 11.5
recursive calls 2,834,564 17.5 200.3
recursive cpu usage 32,247 0.2 2.3
redo blocks written 331,630 2.1 23.4
redo buffer allocation retries 9 0.0 0.0
redo entries 361,184 2.2 25.5
redo log space requests 12 0.0 0.0
redo log space wait time 123 0.0 0.0
redo ordering marks 5,446 0.0 0.4
redo size 158,330,644 977.4 11,187.9
redo synch time 3,382 0.0 0.2
redo synch writes 40,742 0.3 2.9
redo wastage 5,656,088 34.9 399.7
redo write time 18,790 0.1 1.3
redo writer latching time 9 0.0 0.0
redo writes 21,802 0.1 1.5
rollback changes – undo records a 54 0.0 0.0
rollbacks only – consistent read 2,899 0.0 0.2
rows fetched via callback 316,706 2.0 22.4
session connect time 158,415 1.0 11.2
session cursor cache hits 134,751 0.8 9.5
session logical reads 4,383,310 27.1 309.7
session pga memory 3,651,332,712 22,539.2 258,008.3
session pga memory max 4,162,382,440 25,693.9 294,119.7
session uga memory ################## 69,754,779.2 ############
session uga memory max 3,459,729,040 21,356.5 244,469.3
shared hash latch upgrades – no w 482,711 3.0 34.1
sorts (memory) 117,205 0.7 8.3
sorts (rows) 13,677,941 84.4 966.5
sql area purged 58 0.0 0.0
summed dirty queue length 2,265 0.0 0.2
switch current to new buffer 1,048 0.0 0.1
table fetch by rowid 817,172 5.0 57.7
table fetch continued row 2,607 0.0 0.2
table scan blocks gotten 821,482 5.1 58.1
table scan rows gotten 23,709,960 146.4 1,675.4
table scans (long tables) 26 0.0 0.0
table scans (short tables) 71,298 0.4 5.0
total number of times SMON posted 92 0.0 0.0
transaction rollbacks 29 0.0 0.0
undo change vector size 58,520,124 361.2 4,135.1
user I/O wait time 6,158 0.0 0.4
user calls 16,687 0.1 1.2
user commits 14,152 0.1 1.0
workarea executions – optimal 60,978 0.4 4.3
write clones created in foregroun 27 0.0 0.0
————————————————————-Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 171-241
-> Statistics with absolute values (should not be diffed)Statistic Begin Value End Value
——————————— ————— —————
logons current 19 19
opened cursors current 64 87
session cursor cache count 634 39,926
————————————————————-Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 171-241
-> Statistics identified by ‘(derived)’ come from sources other than SYSSTATStatistic Total per Hour
——————————— —————— ———
log switches (derived) 4 .09
————————————————————-OS Statistics DB/Inst: ORCL/orcl Snaps: 171-241
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), NameStatistic Total
————————- ———————-
BUSY_TIME 75,677
IDLE_TIME 32,143,182
NICE_TIME 18
SYS_TIME 34,058
USER_TIME 41,601
NUM_CPUS 2
————————————————————-
Tablespace IO Stats DB/Inst: ORCL/orcl Snaps: 171-241
->ordered by IOs (Reads + Writes) descTablespace
——————————
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
————– ——- —— ——- ———— ——– ———- ——
SYSAUX
7,093 0 4.0 2.5 21,134 0 0 0.0
SYSTEM
13,502 0 2.8 1.2 4,419 0 0 0.0
UNDOTBS1
35 0 3.4 1.0 9,372 0 61 1.5
PERFSTAT
1,477 0 6.7 4.0 6,638 0 0 0.0
TEMP
51 0 0.4 12.1 36 0 0 0.0
SNOWFOX
11 0 8.2 1.4 5 0 0 0.0
EXAMPLE
11 0 14.5 1.0 5 0 0 0.0
USERS
9 0 0.0 1.0 5 0 0 0.0
————————————————————-
File IO Stats DB/Inst: ORCL/orcl Snaps: 171-241
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, FileTablespace Filename
———————— —————————————————-
Av Mx Av
Av Rd Rd Av Av Buffer BufWt
Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms)
————– ——- —– — ——- ———— ——– ———- ——
EXAMPLE /oradata/orcl/example01.dbf
11 0 14.5 1 1.0 5 0 0PERFSTAT /ora_data/stats_pack/perfstat.dbf
1,477 0 6.7 ### 4.0 6,638 0 0SNOWFOX /ora_data/snowfox/snowfox1.dbf
11 0 8.2 ### 1.4 5 0 0SYSAUX /oradata/orcl/sysaux01.dbf
7,093 0 4.0 ### 2.5 21,134 0 0SYSTEM /oradata/orcl/system01.dbf
13,502 0 2.8 ### 1.2 4,419 0 0TEMP /oradata/orcl/temp01.dbf
51 0 0.4 1 12.1 36 0 0UNDOTBS1 /oradata/orcl/undotbs01.dbf
35 0 3.4 4 1.0 9,372 0 61 1.5USERS /oradata/orcl/users01.dbf
9 0 0.0 1 1.0 5 0 0————————————————————-
File Read Histogram Stats DB/Inst: ORCL/orcl Snaps: 171-241
->Number of single block reads in each time range
->ordered by Tablespace, FileTablespace Filename
———————— —————————————————-
0 – 2 ms 2 – 4 ms 4 – 8 ms 8 – 16 ms 16 – 32 ms 32+ ms
———— ———— ———— ———— ———— ————
PERFSTAT /ora_data/stats_pack/perfstat.dbf
689 23 62 94 88 21SYSTEM /oradata/orcl/system01.dbf
7,172 111 337 364 237 133UNDOTBS1 /oradata/orcl/undotbs01.dbf
6 1 0 0 0 0EXAMPLE /oradata/orcl/example01.dbf
1 0 0 0 0 0SYSAUX /oradata/orcl/sysaux01.dbf
3,874 84 224 238 123 64SNOWFOX /ora_data/snowfox/snowfox1.dbf
1 0 0 0 0 1USERS /oradata/orcl/users01.dbf
1 0 0 0 0 0TEMP /oradata/orcl/temp01.dbf
6 0 0 0 0 0————————————————————-
Buffer Pool Statistics DB/Inst: ORCL/orcl Snaps: 171-241
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers. Units of K, M, G are divided by 1000Free Writ Buffer
Pool Buffer Physical Physical Buffer Comp Busy
P Buffers Hit% Gets Reads Writes Waits Wait Waits
— ——- —- ————– ———— ———– ——- —- ———-
D 21K 99 4,383,147 33,403 57,965 0 0 61
————————————————————-Instance Recovery Stats DB/Inst: ORCL/orcl Snaps: 171-241
-> B: Begin snapshot, E: End snapshotTargt 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 12 224 228 184320 184320
E 0 12 286 181 764 184320 764
————————————————————-
Buffer Pool Advisory DB/Inst: ORCL/orcl End Snap: 241
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For EstimateEst
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
— ——– —– ———— —— ————– ———— ——–
D 16 .1 2 4.8 194 524 106.3
D 32 .2 4 2.4 98 242 49.1
D 48 .3 6 1.6 64 143 29.0
D 64 .4 8 1.4 57 124 25.2
D 80 .5 10 1.3 54 114 23.1
D 96 .5 12 1.2 51 104 21.1
D 112 .6 14 1.2 48 98 19.9
D 128 .7 16 1.1 46 90 18.3
D 144 .8 18 1.1 44 86 17.4
D 160 .9 20 1.0 42 80 16.2
D 176 1.0 22 1.0 41 75 15.2
D 192 1.1 24 1.0 40 73 14.8
D 208 1.2 26 0.9 36 63 12.8
D 224 1.3 28 0.8 32 50 10.1
D 240 1.4 30 0.7 30 43 8.7
D 256 1.5 32 0.7 28 37 7.5
D 272 1.5 34 0.6 26 32 6.5
D 288 1.6 36 0.6 24 26 5.3
D 304 1.7 38 0.5 22 20 4.1
D 320 1.8 40 0.5 19 20 4.1
————————————————————-Buffer wait Statistics DB/Inst: ORCL/orcl Snaps: 171-241
-> ordered by wait time desc, waits descClass Waits Total Wait Time (s) Avg Time (ms)
———————- ———– ——————- ————-
undo header 61 0 1
————————————————————-
PGA Aggr Target Stats DB/Inst: ORCL/orcl Snaps: 171-241
-> B: Begin snap E: End snap (rows identified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % – percentage of W/A (WorkArea) data processed only in-memory
-> 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 controlPGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
————— —————- ————————-
100.0 1,735 0%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 90 45 88.6 0.0 .0 .0 .0 18,432
E 100 50 94.1 0.0 .0 .0 .0 20,480
————————————————————-PGA Aggr Target Histogram DB/Inst: ORCL/orcl Snaps: 171-241
-> Optimal Executions are purely in-memory operationsLow High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
——- ——- ————– ————- ———— ————
2K 4K 58,554 58,554 0 0
64K 128K 457 457 0 0
128K 256K 51 51 0 0
256K 512K 38 38 0 0
512K 1024K 1,704 1,704 0 0
1M 2M 167 167 0 0
2M 4M 6 6 0 0
4M 8M 1 1 0 0
————————————————————-PGA Memory Advisory DB/Inst: ORCL/orcl End Snap: 241
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0Estd 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
———- ——- —————- —————- ——– ———-
13 0.1 15.7 0.0 100.0 1
25 0.3 15.7 0.0 100.0 1
50 0.5 15.7 0.0 100.0 0
75 0.8 15.7 0.0 100.0 0
100 1.0 15.7 0.0 100.0 0
120 1.2 15.7 0.0 100.0 0
140 1.4 15.7 0.0 100.0 0
160 1.6 15.7 0.0 100.0 0
180 1.8 15.7 0.0 100.0 0
200 2.0 15.7 0.0 100.0 0
300 3.0 15.7 0.0 100.0 0
400 4.0 15.7 0.0 100.0 0
600 6.0 15.7 0.0 100.0 0
800 8.0 15.7 0.0 100.0 0
————————————————————-
Process Memory Summary Stats DB/Inst: ORCL/orcl Snaps: 171-241
-> 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
-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of
processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) descHist Num
Avg Std Dev Max Max Procs
Alloc Used Freeabl Alloc Alloc Alloc Alloc or
Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs
– ——– ——— ——— ——– ——– ——- ——- —— ——
B ——– 88.6 40.2 7.0 4.2 7.5 22 22 21
Other 80.7 3.8 7.6 22 22 21
Freeable 7.0 .0 .8 .5 2 9
SQL .7 .4 .1 .1 0 2 9
PL/SQL .2 .1 .0 .0 0 0 19
E ——– 94.1 45.4 8.6 4.5 7.5 22 22 21
Other 84.5 4.0 7.6 22 22 21
Freeable 8.6 .0 1.0 .4 2 9
SQL .9 .4 .1 .1 0 4 10
PL/SQL .2 .1 .0 .0 0 0 19
————————————————————-Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 171-241
-> ordered by Begin/End snapshot, Alloc (MB) descAlloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
– —— ————- ——- ——- ——– ———- ———-
B 6 LGWR ——– 22.2 10.3 .1 22.2 22.3
Other 22.0 22.0 22.2
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
16 ARC0 ——– 21.9 10.3 .0 21.9 21.9
Other 21.9 21.9 21.9
PL/SQL .0 .0 .0 .0
17 ARC1 ——– 21.9 10.3 .0 21.9 21.9
Other 21.9 21.9 21.9
PL/SQL .0 .0 .0 .0
21 J001 ——– 4.4 1.4 1.8 4.4 4.4
Other 2.5 2.5 2.5
Freeable 1.8 .0 1.8
SQL .2 .1 .2 2.2
PL/SQL .0 .0 .0 .0
11 MMON ——– 3.2 1.6 1.1 3.2 3.3
Other 2.0 2.0 2.0
Freeable 1.1 .0 1.1
SQL .1 .1 .1 1.0
PL/SQL .0 .0 .0 .1
10 CJQ0 ——– 1.9 .6 1.1 1.9 1.9
Freeable 1.1 .0 1.1
Other .7 .7 .7
SQL .1 .0 .1 .5
PL/SQL .0 .0 .0 .0
15 TNS V1-V3 — 1.7 .5 .3 1.7 2.1
Other 1.4 1.4 1.4
Freeable .3 .0 .3
SQL .1 .0 .1 1.3
PL/SQL .0 .0 .0 .0
13 D000 ——– 1.7 .8 .0 1.7 1.7
Other 1.7 1.7 1.7
18 J000 ——– 1.6 .3 .9 1.6 4.3
Freeable .9 .0 .9
Other .6 .6 .8
PL/SQL .1 .0 .1 .1
SQL .0 .0 .0 2.5
5 DBW0 ——– 1.4 .3 .9 1.4 2.3
Freeable .9 .0 .9
Other .5 .5 1.4
PL/SQL .0 .0 .0 .0
20 q000 ——– 1.3 .6 .4 1.3 1.4
Other .8 .8 .8
Freeable .4 .0 .4
SQL .1 .1 .1 .5
PL/SQL .0 .0 .0 .0
8 SMON ——– 1.2 .5 .6 1.2 1.3
Other .6 .6 .6
Freeable .6 .0 .6
SQL .1 .0 .1 .2
PL/SQL .0 .0 .0 .0
E 6 LGWR ——– 22.2 10.3 .1 22.2 22.3
Other 22.0 22.0 22.2Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 171-241
-> ordered by Begin/End snapshot, Alloc (MB) descAlloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
– —— ————- ——- ——- ——– ———- ———-
E 6 Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
16 ARC0 ——– 21.9 10.3 .0 21.9 21.9
Other 21.9 21.9 21.9
PL/SQL .0 .0 .0 .0
17 ARC1 ——– 21.9 10.3 .0 21.9 21.9
Other 21.9 21.9 21.9
PL/SQL .0 .0 .0 .0
11 MMON ——– 7.0 5.5 1.0 7.0 7.1
Other 5.9 5.9 5.9
Freeable 1.0 .0 1.0
SQL .1 .0 .1 1.0
PL/SQL .0 .0 .0 .1
21 J000 ——– 4.5 1.3 1.6 4.5 4.5
Other 2.7 2.7 2.7
Freeable 1.6 .0 1.6
SQL .2 .1 .2 2.2
PL/SQL .1 .0 .1 .1
15 TNS V1-V3 — 2.4 1.0 1.0 2.4 5.6
Other 1.2 1.2 1.2
Freeable 1.0 .0 1.0
SQL .1 .1 .1 3.6
PL/SQL .1 .0 .1 .1
8 SMON ——– 2.1 .6 1.2 2.1 2.1
Freeable 1.2 .0 1.2
Other .8 .8 .8
SQL .1 .0 .1 .6
PL/SQL .0 .0 .0 .0
20 q000 ——– 2.1 .8 1.1 2.1 2.1
Freeable 1.1 .0 1.1
Other .9 .9 .9
SQL .1 .0 .1 .7
PL/SQL .0 .0 .0 .0
10 CJQ0 ——– 2.0 .7 1.1 2.0 2.0
Freeable 1.1 .0 1.1
Other .8 .8 .8
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .0
18 TNS V1-V3 — 1.7 .5 .7 1.7 1.9
Other .9 .9 .9
Freeable .7 .0 .7
SQL .1 .1 .1 1.1
PL/SQL .0 .0 .0 .0
7 CKPT ——– 1.3 .4 .8 1.3 2.2
Freeable .8 .0 .8
Other .6 .6 1.4
SQL .0 .0 .0 .1
PL/SQL .0 .0 .0 .0
————————————————————-
Enqueue activity DB/Inst: ORCL/orcl Snaps: 171-241
-> 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 descEnqueue Type (Request Reason)
——————————————————————————
Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
———— ———— ———– ———– ———— ————–
RO-Multiple Object Reuse (fast object reuse)
333 333 0 28 0 .71
————————————————————-
Undo Segment Summary DB/Inst: ORCL/orcl Snaps: 171-241
-> 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 reUsedUndo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/
TS# Blocks (K) Transactions Len (s) Concy TR (mins) OOS eS/eR/eU
—- ———- ————— ——– ———- ——— —– ———–
1 9.0 29,236 0 4 15/15 0/0 0/0/0/0/0/0
————————————————————-
Undo Segment Stats DB/Inst: ORCL/orcl Snaps: 171-241
-> Most recent 35 Undostat rows, ordered by End Time descNum 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
———— ———– ———— ——- ——- ——- —– ———–
24-Mar 19:51 4 86 0 1 15 0/0 0/0/0/0/0/0
24-Mar 19:41 5 51 0 1 15 0/0 0/0/0/0/0/0
24-Mar 19:31 4 84 0 1 15 0/0 0/0/0/0/0/0
24-Mar 19:21 6 52 0 2 15 0/0 0/0/0/0/0/0
24-Mar 19:11 12 274 0 2 15 0/0 0/0/0/0/0/0
24-Mar 19:01 166 147 0 3 15 0/0 0/0/0/0/0/0
24-Mar 18:51 10 98 0 3 15 0/0 0/0/0/0/0/0
24-Mar 18:41 3 58 0 1 15 0/0 0/0/0/0/0/0
24-Mar 18:31 5 65 0 1 15 0/0 0/0/0/0/0/0
24-Mar 18:21 3 49 0 1 15 0/0 0/0/0/0/0/0
24-Mar 18:11 5 165 0 1 15 0/0 0/0/0/0/0/0
24-Mar 18:01 94 130 0 3 15 0/0 0/0/0/0/0/0
24-Mar 17:51 5 74 0 1 15 0/0 0/0/0/0/0/0
24-Mar 17:41 3 48 0 1 15 0/0 0/0/0/0/0/0
24-Mar 17:31 5 60 0 1 15 0/0 0/0/0/0/0/0
24-Mar 17:21 3 47 0 1 15 0/0 0/0/0/0/0/0
24-Mar 17:11 3 170 0 1 15 0/0 0/0/0/0/0/0
24-Mar 17:01 115 140 0 3 15 0/0 0/0/0/0/0/0
24-Mar 16:51 2 52 0 1 15 0/0 0/0/0/0/0/0
24-Mar 16:41 4 49 0 2 15 0/0 0/0/0/0/0/0
24-Mar 16:31 5 64 0 1 15 0/0 0/0/0/0/0/0
24-Mar 16:21 4 54 0 1 15 0/0 0/0/0/0/0/0
24-Mar 16:11 6 166 0 3 15 0/0 0/0/0/0/0/0
24-Mar 16:01 104 137 0 3 15 0/0 0/0/0/0/0/0
24-Mar 15:51 2 71 0 1 15 0/0 0/0/0/0/0/0
24-Mar 15:41 5 48 0 1 15 0/0 0/0/0/0/0/0
24-Mar 15:31 5 86 0 2 15 0/0 0/0/0/0/0/0
24-Mar 15:21 6 54 0 1 15 0/0 0/0/0/0/0/0
24-Mar 15:11 9 229 0 2 15 0/0 0/0/0/0/0/0
24-Mar 15:01 111 140 0 3 15 0/0 0/0/0/0/0/0
24-Mar 14:51 1 48 0 1 15 0/0 0/0/0/0/0/0
24-Mar 14:41 2 66 0 1 15 0/0 0/0/0/0/0/0
24-Mar 14:31 9 47 0 3 15 0/0 0/0/0/0/0/0
24-Mar 14:21 5 237 0 1 15 0/0 0/0/0/0/0/0
————————————————————-
Latch Activity DB/Inst: ORCL/orcl Snaps: 171-241
->”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.0Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
———————— ————– —— —— —— ———— ——
AWR Alerted Metric Eleme 569,825 0.0 0 0
Consistent RBA 21,806 0.0 0 0
FAL request queue 3,247 0.0 0 0
FAL subheap alocation 3,247 0.0 0 0
FIB s.o chain latch 94 0.0 0 0
FOB s.o list latch 2,344 0.0 0 0
In memory undo latch 168,969 0.0 0.5 0 55,404 0.0
JS mem alloc latch 650 0.0 0 2 0.0
JS queue access latch 650 0.0 0 0
JS queue state obj latch 1,169,356 0.0 0 0
JS slv state obj latch 11,164 0.0 0 0
KGX 0 0 430,156 0.0
KMG MMAN ready and start 54,456 0.0 0 0
KMG resize request state 36 0.0 0 0
KTF sga latch 307 0.0 0 51,151 0.0
KWQMN job cache list lat 3,337 0.0 0 0
KWQP Prop Status 46 0.0 0 0
MQL Tracking Latch 0 0 3,236 0.0
Memory Management Latch 816 0.1 1.0 0 54,455 0.0
OS process 22,188 0.0 0 0
OS process allocation 68,771 0.0 0 0
OS process: request allo 4,940 0.0 0 0
PL/SQL warning settings 11,142 0.0 0 0
SGA IO buffer pool latch 221 0.0 0 221 0.0
SQL memory manager latch 91 0.0 0 53,657 0.0
SQL memory manager worka 3,632,934 0.0 0 0
Shared B-Tree 5,917 0.0 0 0
active checkpoint queue 94,592 0.0 0 2 0.0
active service list 346,042 0.0 0 56,118 0.0
alert log latch 0 0 1 0.0
archive control 3,120 0.0 0 0
archive process latch 56,904 0.0 1.0 0 0
begin backup scn array 18 0.0 0 0
buffer pool 26 0.0 0 0
cache buffer handles 13,011 0.0 0 0
cache buffers chains 9,361,809 0.0 0.2 0 88,046 0.0
cache buffers lru chain 228,308 0.0 0.4 0 29,923 0.1
cache table scan latch 0 0 2,511 0.0
cas latch 105,589 0.0 0 0
channel handle pool latc 5,022 0.0 0 0
channel operations paren 769,612 0.0 0 0
checkpoint queue latch 1,261,737 0.0 0 45,437 0.0
client/application info 65,661 0.0 0 0
commit callback allocati 2,618 0.0 0 0
compile environment latc 9,859 0.0 0 0
dictionary lookup 107 0.0 0 0
dml lock allocation 124,446 0.0 0 0
dummy allocation 10,202 0.0 0 0
enqueue hash chains 2,916,761 0.0 0.6 0 2 0.0
enqueues 2,702,194 0.0 0.0 0 0
Latch Activity DB/Inst: ORCL/orcl Snaps: 171-241
->”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.0Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
———————— ————– —— —— —— ———— ——
event group latch 2,470 0.0 0 0
file cache latch 4,455 0.0 0 0
global KZLD latch for me 5 0.0 0 0
hash table column usage 4,449 0.0 0 204,404 0.0
hash table modification 4,614 0.0 0 2 0.0
internal temp table obje 13 0.0 0 0
job workq parent latch 0 0 5,262 0.0
job_queue_processes free 3 0.0 0 2 0.0
job_queue_processes para 5,320 0.0 0 0
kks stats 18,636 0.0 0 0
kmcptab latch 10 0.0 0 0
kmcpvec latch 0 0 10 0.0
ksuosstats global area 10,982 0.0 0 0
ktm global data 671 0.0 0 0
kwqbsn:qsga 5,917 0.0 0 0
lgwr LWN SCN 68,098 0.0 0.0 0 0
library cache 2,296,703 0.0 0.5 1 16,915 66.6
library cache load lock 26,746 0.0 0.0 0 5 0.0
library cache lock 937,239 0.0 0.0 0 0
library cache lock alloc 55,463 0.0 0 0
library cache pin 1,400,649 0.0 0.0 0 0
library cache pin alloca 22,322 0.0 0 0
list of block allocation 6,757 0.0 0 0
loader state object free 186 0.0 0 0
longop free list parent 282 0.0 0 322 0.0
message pool operations 246 0.0 0 0
messages 1,191,392 0.0 0.0 0 0
mostly latch-free SCN 108,035 0.0 0.0 0 0
multiblock read objects 6,742 0.0 0 1 0.0
ncodef allocation latch 2,672 0.0 0 0
object queue header heap 976 0.0 0 537 0.0
object queue header oper 343,291 0.0 0.2 0 0
object stats modificatio 820 0.0 0 2 0.0
parallel query alloc buf 21,464 0.0 0 0
parameter table allocati 5,237 0.0 0 0
post/wait queue 7,481 0.0 0 3,187 0.0
process allocation 4,940 0.0 0 2,470 0.0
process group creation 4,940 0.0 0 0
qmn task queue latch 23,116 0.0 0.0 0 0
redo allocation 263,363 0.0 0.3 0 361,204 0.0
redo copy 0 0 361,240 0.2
redo on-disk SCN 218,443 0.0 0 0
redo writing 272,588 0.0 0.0 0 0
resmgr group change latc 13,331 0.0 0 0
resmgr:actses active lis 10,106 0.0 0 0
resmgr:actses change gro 2,637 0.0 0 0
resmgr:free threads list 10,014 0.0 0 0
resmgr:schema config 92 0.0 0 0
rm cas latch 25,831 0.0 0 0
row cache objects 1,488,101 0.0 0.1 0 294 0.0
Latch Activity DB/Inst: ORCL/orcl Snaps: 171-241
->”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.0Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
———————— ————– —— —— —— ———— ——
rules engine aggregate s 670 0.0 0 0
rules engine rule set st 5,940 0.0 0 0
sequence cache 2,025 0.0 0 0
session allocation 1,895,334 0.0 0.0 0 0
session idle bit 38,131 0.0 0 0
session state list latch 10,972 0.0 0 0
session switching 2,672 0.0 0 0
session timer 56,118 0.0 0 0
shared pool 781,373 0.1 0.5 1 0
simulator hash latch 388,602 0.0 0 0
simulator lru latch 382,196 0.0 0.2 0 3,265 0.0
slave class 94 0.0 0 0
slave class create 376 12.0 1.0 4 0
sort extent pool 4,238 0.0 0 0
state object free list 90 0.0 0 0
statistics aggregation 6,300 0.0 0 0
temporary table state ob 8 0.0 0 0
threshold alerts latch 13,558 0.0 0 0
trace latch 3 0.0 0 0
transaction allocation 6,512 0.0 0 0
transaction branch alloc 2,672 0.0 0 0
undo global data 160,700 0.0 0.0 0 0
user lock 5,272 0.0 0 0
————————————————————-
Latch Sleep breakdown DB/Inst: ORCL/orcl Snaps: 171-241
-> ordered by misses descGet Spin
Latch Name Requests Misses Sleeps Gets
————————– ————— ———— ———– ———–
library cache 2,296,703 1,114 594 580
shared pool 781,373 1,093 502 629
session allocation 1,895,334 940 5 935
row cache objects 1,488,101 135 15 120
redo allocation 263,363 64 21 43
slave class create 376 45 46 0
simulator lru latch 382,196 41 9 32
cache buffers chains 9,361,809 33 5 28
cache buffers lru chain 228,308 24 9 16
messages 1,191,392 23 1 22
enqueue hash chains 2,916,761 7 4 4
object queue header operat 343,291 6 1 5
archive process latch 56,904 2 2 0
In memory undo latch 168,969 2 1 1
Memory Management Latch 816 1 1 0
————————————————————-
Latch Miss Sources DB/Inst: ORCL/orcl Snaps: 171-241
-> only latches with sleeps are shown
-> ordered by name, sleeps descNoWait Waiter
Latch Name Where Misses Sleeps Sleeps
———————— ————————– ——- ———- ——–
In memory undo latch ktiFlush: child 0 1 0
Memory Management Latch kmgs_perform_parameter_upd 0 1 1
archive process latch kcrrsarc 0 2 0
cache buffers chains kcbzwb 0 1 0
cache buffers chains kcbgtcr: kslbegin excl 0 1 1
cache buffers chains kcbgcur: kslbegin 0 1 0
cache buffers chains kcbgtcr: fast path 0 1 0
cache buffers chains kcbchg: kslbegin: bufs not 0 1 0
cache buffers lru chain kcbzgws_1 0 8 4
cache buffers lru chain kcbzgb: posted for free bu 0 1 0
enqueue hash chains ksqgtl3 0 3 2
enqueue hash chains ksqcnl 0 1 0
library cache kglpndl: child: before pro 0 88 163
library cache kglobpn: child: 0 73 26
library cache kglpin: child: heap proces 0 67 26
library cache kglpndl: child: after proc 0 26 8
library cache kglrtl 0 25 6
library cache kglhdgc: child: 0 22 24
library cache kglhdgn: child: 0 18 43
library cache kglini: child 0 12 4
library cache kglpnc: child 0 10 123
library cache kglukp: child 0 5 4
library cache kglpin 0 5 6
library cache kglic 0 5 1
library cache kglobld 0 4 6
library cache kglhdiv0: parent: invalida 0 2 4
library cache kglivl: child 0 2 2
library cache kgldtld: 2child 0 2 7
library cache kgldti: 2child 0 2 6
library cache kglati 0 1 0
library cache kglupd: child 0 1 0
library cache kglprg: child 0 1 0
library cache kglkep: child 0 1 0
library cache lock kgllkdl: child: no lock ha 0 25 44
messages ksarcv: after wait 0 1 0
object queue header oper kcbw_unlink_q_bg 0 1 0
redo allocation kcrfw_redo_gen: redo alloc 0 17 0
redo allocation kcrfw_redo_gen: redo alloc 0 4 0
row cache objects kqreqd: reget 0 7 0
row cache objects kqrpre: find obj 0 6 8
row cache objects kqrso 0 1 0
row cache objects kqreqd 0 1 3
session allocation ksucri 0 2 0
session allocation ksuxds: KSUSFCLC not set 0 1 0
session allocation ksufap: active sessions 0 1 0
session allocation ksuprc 0 1 2
shared pool kghalo 0 352 216
shared pool kghfrunp: clatch: nowait 0 141 0
shared pool kghfrunp: alloc: cursor du 0 76 1
shared pool kghfrunp: clatch: wait 0 56 102
shared pool kghupr1 0 34 171
shared pool kghalp 0 29 87
shared pool kgh: add extent to quiesce 0 7 1
Latch Miss Sources DB/Inst: ORCL/orcl Snaps: 171-241
-> only latches with sleeps are shown
-> ordered by name, sleeps descNoWait Waiter
Latch Name Where Misses Sleeps Sleeps
———————— ————————– ——- ———- ——–
shared pool kghasp 0 2 10
shared pool kghfre 0 2 13
simulator lru latch kcbs_simulate: simulate se 0 9 9
slave class create ksvcreate 0 46 0
————————————————————-
Dictionary Cache Stats DB/Inst: ORCL/orcl Snaps: 171-241
->”Pct Misses” should be very low (< 2% in most cases)
->”Final Usage” is the number of cache entries being used in End SnapshotGet Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
————————- ———— —— ——- —– ——– ———-
dc_awr_control 3,016 0.0 0 92 1
dc_constraints 249 33.7 0 249 1
dc_files 56 0.0 0 0 7
dc_global_oids 5,315 0.4 0 0 45
dc_histogram_data 39,444 12.0 0 3,568 3,516
dc_histogram_defs 68,869 18.4 0 4,181 4,399
dc_object_grants 148 15.5 0 0 25
dc_object_ids 96,272 0.8 0 75 1,030
dc_objects 49,002 2.8 0 486 1,320
dc_profiles 2,732 0.0 0 0 1
dc_rollback_segments 29,037 0.0 0 0 22
dc_segments 19,990 3.8 0 331 919
dc_sequences 69 7.2 0 69 8
dc_table_scns 15 100.0 0 0 0
dc_tablespace_quotas 73 4.1 0 0 3
dc_tablespaces 63,910 0.0 0 0 10
dc_usernames 5,200 0.2 0 0 14
dc_users 115,483 0.0 0 0 42
outstanding_alerts 5,605 6.0 0 670 17
————————————————————-
Library Cache Activity DB/Inst: ORCL/orcl Snaps: 171-241
->”Pct Misses” should be very lowGet Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
————— ———— —— ————– —— ———- ——–
BODY 27,749 0.1 35,344 0.6 161 0
CLUSTER 411 0.2 1,018 2.4 23 0
INDEX 561 50.3 3,371 20.8 182 0
SQL AREA 11,344 22.7 479,375 2.3 4,360 248
TABLE/PROCEDURE 20,012 4.2 116,712 10.5 6,460 0
TRIGGER 977 2.4 8,521 1.0 56 0
————————————————————-
Rule Sets DB/Inst: ORCL/orcl Snaps: 171-241
-> * indicates Rule Set activity (re)started between Begin/End snaps
-> Top 25 ordered by Evaluations descNo-SQL SQL
Rule * Eval/sec Reloads/sec Eval % Eval %
———————————– – ———— ———– —— ——
SYS.ALERT_QUE_R 1,218 0 0 0
————————————————————-
Shared Pool Advisory DB/Inst: ORCL/orcl End Snap: 241
-> 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 invalidEst 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
———- —– ——– ———— ——- —— ——- —— ———–
96 .9 13 1,636 1,333 1.0 133 1.1 506,568
108 1.0 24 2,562 1,343 1.0 123 1.0 512,259
120 1.1 34 3,676 1,351 1.0 115 .9 516,526
132 1.2 45 5,117 1,357 1.0 109 .9 519,089
144 1.3 56 5,990 1,360 1.0 106 .9 520,576
156 1.4 67 7,089 1,362 1.0 104 .8 521,631
168 1.6 78 7,997 1,363 1.0 103 .8 522,501
180 1.7 89 8,691 1,364 1.0 102 .8 523,238
192 1.8 100 9,341 1,365 1.0 101 .8 523,964
204 1.9 111 10,543 1,367 1.0 99 .8 524,787
216 2.0 122 11,531 1,369 1.0 97 .8 525,645
————————————————————-
Cache Size Changes DB/Inst: ORCL/orcl Snaps: 171-241
-> Not all cache size changes may be captured. Only cache changes which are
evident at snapshot time are shownPrior New Difference
Snap Id Cache Size (MB) Size (MB) (MB)
——— ———— ——— ——— ———-
172 Buffer Cache 132 128 -4
Shared Pool 152 156 4
211 Shared Pool 156 128 -28
Buffer Cache 128 156 28
213 Shared Pool 128 124 -4
Buffer Cache 156 160 4
221 Shared Pool 124 120 -4
Buffer Cache 160 164 4
225 Shared Pool 120 116 -4
Buffer Cache 164 168 4
229 Shared Pool 116 112 -4
Buffer Cache 168 172 4
241 Shared Pool 112 108 -4
Buffer Cache 172 176 4
————————————————————-
SGA Target Advisory DB/Inst: ORCL/orcl End Snap: 241SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
———- ——– ——– ———– ————–
225 .8 524 1.1 49,308
300 1.0 493 1.0 40,660
375 1.3 457 .9 29,336
450 1.5 426 .9 20,119
525 1.8 417 .8 20,119
600 2.0 417 .8 17,171
————————————————————-
SGA Memory Summary DB/Inst: ORCL/orcl Snaps: 171-241End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
—————————— ——————– ——————–
Database Buffers 138,412,032 184,549,376
Fixed Size 2,020,480
Redo Buffers 6,365,184
Variable Size 167,775,104 121,637,760
——————– ——————–
sum 314,572,800 306,187,136
————————————————————-
SGA breakdown difference DB/Inst: ORCL/orcl Snaps: 171-241
-> Top 35 rows by size, ordered by Pool, Name (note rows with null values for
Pool column, or Names showing free memory are always shown)
-> Null value for Begin MB or End MB indicates the size of that Pool/Name was
insignificant, or zero in that snapshotPool Name Begin MB End MB % Diff
—— —————————— ————– ————– ——–
java p free memory 4.0 4.0 0.00
large PX msg pool .9 .9 0.00
large free memory 3.1 3.1 0.00
shared ASH buffers 4.0 4.0 0.00
shared CCursor 2.0 3.0 51.23
shared FileOpenBlock 1.4 ########
shared Heap0: KGL 2.0 ########
shared KCB Table Scan Buffer 3.8 3.8 0.00
shared KGLS heap 2.2 2.5 15.49
shared KQR L PO 1.3 ########
shared KQR M PO 3.2 ########
shared KQR M SO 1.7 ########
shared KSFD SGA I/O b 3.8 3.8 0.00
shared KSXR receive buffers 1.0 ########
shared KTI-UNDO 1.2 ########
shared PCursor 2.1 ########
shared PL/SQL MPCODE 2.2 2.1 -2.29
shared db_block_hash_buckets 1.2 ########
shared dbwriter coalesce buffer 1.0 ########
shared event statistics per sess 1.5 1.5 0.00
shared free memory 73.7 3.8 -94.84
shared kglsim hash table bkts 4.0 4.0 0.00
shared kglsim heap 1.2 ########
shared kglsim object batch 1.7 ########
shared kks stbkt 1.5 1.5 0.00
shared library cache 5.3 9.4 77.39
shared private strands 2.3 2.3 0.00
shared row cache 7.1 7.1 0.00
shared sql area 6.2 11.1 77.33
buffer_cache 132.0 176.0 33.33
fixed_sga 1.9 1.9 0.00
log_buffer 6.1 6.1 0.00
————————————————————-
SQL Memory Statistics DB/Inst: ORCL/orcl Snaps: 171-241Begin End % Diff
————– ————– ————–
Avg Cursor Size (KB): 65.21 62.41 -4.49
Cursor to Parent ratio: 1.05 1.32 20.03
Total Cursors: 405 728 44.37
Total Parents: 384 552 30.43
————————————————————-
init.ora Parameters DB/Inst: ORCL/orcl Snaps: 171-241End value
Parameter Name Begin value (if different)
—————————– ——————————— ————–
audit_file_dest /oracle/admin/orcl/adump
background_dump_dest /oracle/admin/orcl/bdump
compatible 10.2.0.1.0
control_files /oradata/orcl/control01.ctl, /ora
core_dump_dest /oracle/admin/orcl/cdump
db_block_size 8192
db_cache_size 0
db_domain
db_file_multiblock_read_count 16
db_name orcl
db_recovery_file_dest /oracle/flash_recovery_area
db_recovery_file_dest_size 2147483648
dispatchers (PROTOCOL=TCP) (SERVICE=orclXDB)
job_queue_processes 10
log_archive_dest_1 LOCATION=/ora_data/arch
log_archive_format %t_%s_%r.dbf
open_cursors 300
pga_aggregate_target 94371840 104857600
processes 150
remote_login_passwordfile EXCLUSIVE
sga_max_size 314572800
sga_target 314572800
shared_pool_size 0
undo_management AUTO
undo_tablespace UNDOTBS1
user_dump_dest /oracle/admin/orcl/udump
————————————————————-End of Report ( report.rep )
5.2 SQL문 분석결과보기
SQL> @sprepsql.sql
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1Enter value for end_snap: 75
End Snapshot Id specified: 75
Specify the old (i.e. pre-10g) Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 2689373535
Hash Value specified is:
hash 값은, 위의 spreport.sql 실행 보고서에 나오는 hash값을 사용하면 된다.
기타참고문서: