(V8.1.6 ~ V9.0.1) STATSPACK 성능 보고서 생성 방법
=================================================

PURPOSE
-------
이 자료는 StatsPack Package를 사용하여 인스턴스의 성능 보고서를
생성하는 방법에 대해 기술한다.


Explanation
-----------

1. StatsPack 성능 보고서 생성 방법

snapshot을 생성한 후에는 성능 보고서 생성이 가능하다. SQL 스크립트
실행 시, 비교할 두 개의 snapshot id를 물어본다.

첫번 째 지정하는 값이 시작되는 snapshot id 이고, 두번 째 지정하는 값이
끝나는 시점의 snapshot id 이다. 보고서는 비율 및 증가분 관련 사항을 계산
해서 생성된다. 두 개의 snapshot 사이의 모든 정보는 BSTAT/ESTAT 보고서와
마찬가지 방식으로 처리된다.

참고 : BSTAT/ESTAT 의 경우와 마찬가지로, snapshot 간에
인스턴스를 shutdown 하고 startup 하였다면, 적절한
값이 계산되지 않는다. 만약 snapshot 중간에 인스턴스
shutdown이 있었다면, 리포트에서는 해당 사항에 대한 적절한
에러 메시지가 출력된다.

리포트 생성 시점과 데이터 수집 시점을 분리함으로써, 원하는 두 시점 간의
정보를 비교할 수 있게 된다. 예를 들어, DBA가 통계정보 수집 작업을 매
시간 수행되도록 자동화시킨 후, 추후 필요할 때 원하는 시간대의 성능 자료
를 비교해 볼 수 있다.
DBA가 알아야 할 사항은, 원하는 보고서에 포함될 시작 시점과 종료 시점을
지정하는 것 뿐이다.


2. 리포트의 실행

두 시점 간의 변경된 통계 정보를 비교해 보기 위해서는, statsrep.sql
파일을 PERFSTAT 계정으로 접속하여 실행시키면 된다. statsrep.sql
파일은 $ORACLE_HOME/rdbms/admin 디렉토리에 존재한다.

참고 : OPS 환경에서는 리포트를 생성하기를 원하는 모든 인스턴스에서
각각 수행 시켜야 한다 - 이 제약사항은 다음 번 버젼에서
해결할 예정이다.

스크립트를 실행시키면 다음과 같은 사항을 묻는다.
1) 시작하는 시점의 snapshot id
2) 종료 시점의 snapshot id
3) 생성하고자 하는 report 파일의 이름


Oracle 8.1.6:
Unix 플랫폼:
SQL> connect perfstat/perfstat
SQL> @?/rdbms/admin/statsrep
NT 플랫폼:
SQL> connect perfstat/perfstat
SQL> @%ORACLE_HOME%\rdbms\admin\statsrep


Oracle8i 8.1.7, Oracle9i 9.0.1
Unix 플랫폼:
SQL> connect perfstat/perfstat
SQL> @?/rdbms/admin/spreport
NT 플랫폼:
SQL> connect perfstat/perfstat
SQL> @%ORACLE_HOME%\rdbms\admin\spreport


처리 결과 예제:

DB Id DB Name Instance# Instance
----------- ---------- ---------- ----------
2796063325 PRD1 1 prd1


Completed Snapshots

Instance DB Name SnapId Snap Started Snap Level
---------- ---------- ------ ---------------------- ----------
Comment
--------------------------------------------------------------
prd1 PRD1 1 10 Aug 1999 12:00:47 5

2 10 Aug 1999 15:00:54 5

Enter beginning Snap Id: 1
Enter ending Snap Id: 2

Enter name of output file [st_1_2] :

The report will now scroll past, and also be written to the file
specified (e.g. st_1_2.lis).


3. PERFSTAT 스키마에서 옵티마이져 통계 수집

StatsPack 리포트 수행 속도를 향상시키기 위해서는, PERFSTAT 계정에 속한
테이블과 인덱스 관련된 옵티마이져 통계를 수집하는 것이 도움이 된다.
이 작업은 PERFSTAT 테이블의 데이터 양에 현격한 변화가 있을 때마다
수행해 주는 것이 바람직하다.
이 작업 수행을 처리하기 위한 가장 간편한 방법은 DBMS_UTILITY 패키지나
DBMS_STATS 패키지를 사용하는 것이다.

execute dbms_utility.analyze_schema('PERFSTAT','COMPUTE');
또는
execute dbms_stats.gather_schema_stats('PERFSTAT');


Example
-------
위의 방법으로 생성되는 report는 다음과 같다.


STATSPACK report for

DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
ORA817 1626331562 ORA817 1 8.1.7.0.0 NO soju

Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 11 29-May-02 09:47:04 13
End Snap: 14 29-May-02 13:00:28 13
Elapsed: 193.40 (mins)

Cache Sizes
~~~~~~~~~~~
db_block_buffers: 2048 log_buffer: 163840
db_block_size: 8192 shared_pool_size: 10291456

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 117.77 136,658.40
Logical reads: 1.46 1,699.60
Block changes: 0.19 219.40
Physical reads: 0.04 47.60
Physical writes: 0.07 84.00
User calls: 0.28 323.90
Parses: 0.37 431.90
Hard parses: 0.01 6.80
Sorts: 0.08 96.90
Logons: 0.13 151.20
Executes: 0.12 138.80
Transactions: 0.00

% Blocks changed per Read: 12.91 Recursive Call %: 92.39
Rollback per transaction %: 0.00 Rows per Sort: 21.93

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 97.20 In-memory Sort %: 98.97
Library Hit %: 97.05 Soft Parse %: 98.43
Execute to Parse %: -211.17 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 97.67 % Non-Parse CPU: 100.00

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 89.71 92.47
% SQL with executions>1: 70.25 87.89
% Memory for SQL w/exec>1: 55.99 86.00

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file parallel write 11 251 77.23
log file parallel write 56 18 5.54
control file parallel write 3,768 16 4.92
db file sequential read 65 16 4.92
direct path write 86 11 3.38
-------------------------------------------------------------
Wait Events for DB: ORA817 Instance: ORA817 Snaps: 11 -14
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
db file parallel write 11 0 251 228 1.1
log file parallel write 56 0 18 3 5.6
control file parallel write 3,768 0 16 0 376.8
db file sequential read 65 0 16 2 6.5
direct path write 86 0 11 1 8.6
direct path read 162 0 5 0 16.2
control file sequential read 184 0 3 0 18.4
log file sync 5 0 3 6 0.5
db file scattered read 7 0 2 3 0.7
refresh controlfile command 30 0 0 0 3.0
file open 21 0 0 0 2.1
LGWR wait for redo copy 2 0 0 0 0.2
SQL*Net break/reset to clien 2 0 0 0 0.2
SQL*Net more data to client 1 0 0 0 0.1
SQL*Net message from client 241 0 858,010 35602 24.1
SQL*Net message to client 242 0 0 0 24.2
-------------------------------------------------------------
Background Wait Events for DB: ORA817 Instance: ORA817 Snaps: 11 -14
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
db file parallel write 11 0 251 228 1.1
log file parallel write 56 0 18 3 5.6
control file parallel write 3,768 0 16 0 376.8
db file scattered read 7 0 2 3 0.7
control file sequential read 90 0 1 0 9.0
LGWR wait for redo copy 2 0 0 0 0.2
db file sequential read 1 0 0 0 0.1
rdbms ipc message 11,512 11,376 5,753,802 4998 ######
smon timer 38 38 1,167,199 ###### 3.8
pmon timer 3,768 3,768 1,160,359 3080 376.8
-------------------------------------------------------------
SQL ordered by Gets for DB: ORA817 Instance: ORA817 Snaps: 11 -14
-> End Buffer Gets Threshold: 15000
-> Note that resources reported for PL/SQL includes the resources used
by all SQL statements called within the PL/SQL code. As individual
SQL statements are also reported, it is possible and valid for the
summed total % to exceed 100

Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
1,368 38 36.0 8.0 1714733582
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0

762 755 1.0 4.5 313510536
select job from sys.job$ where next_date < sysdate and (field1
= :1 or (field1 = 0 and 'Y' = :2)) order by next_date, job

184 45 4.1 1.1 2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1

152 16 9.5 0.9 1428100621
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#

113 16 7.1 0.7 3218356218
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece fr
om idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#

중간 생략....


SQL ordered by Reads for DB: ORA817 Instance: ORA817 Snaps: 11 -14
-> End Disk Reads Threshold: 2000

Physical Reads Executions Reads per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
17 16 1.1 3.6 1428100621
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#

3 16 0.2 0.6 3111103299
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#

2 7 0.3 0.4 4049165760
select order#,columns,types from access$ where d_obj#=:1

1 3 0.3 0.2 365454555
select cols,audit$,textlength,intcols,property,flags,rowid from
view$ where obj#=:1

1 10 0.1 0.2 955191413
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1 from obj$ where owner#=:1 and name=:2 and namespace=:3 an
d(remoteowner=:4 or remoteowner is null and :4 is null)and(linkn
ame=:5 or linkname is null and :5 is null)and(subname=:6 or subn
ame is null and :6 is null)

1 16 0.1 0.2 957616262
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece
from idl_char$ where obj#=:1 and part=:2 and version=:3 order by
piece#


중간 생략...


6 0 0.0 4261939565
select col#, grantee#, privilege#,max(nvl(option$,0)) from objau
SQL ordered by Executions for DB: ORA817 Instance: ORA817 Snaps: 11 -14
-> End Executions Threshold: 100

Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------
th$ where obj#=:1 and col# is not null group by privilege#, col#

-------------------------------------------------------------
Instance Activity Stats for DB: ORA817 Instance: ORA817 Snaps: 11 -14

Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 68,073 5.9 6,807.3
CPU used when call started 409 0.0 40.9
CR blocks created 9 0.0 0.9
DBWR buffers scanned 0 0.0 0.0
DBWR checkpoint buffers written 445 0.0 44.5
DBWR checkpoints 0 0.0 0.0
DBWR free buffers found 0 0.0 0.0
DBWR lru scans 0 0.0 0.0
DBWR make free requests 0 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 0 0.0 0.0
DBWR transaction table writes 14 0.0 1.4
DBWR undo block writes 108 0.0 10.8
SQL*Net roundtrips to/from client 213 0.0 21.3
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 12,099 1.0 1,209.9
buffer is not pinned count 3,887 0.3 388.7
buffer is pinned count 3,579 0.3 357.9
bytes received via SQL*Net from c 19,882 1.7 1,988.2
bytes sent via SQL*Net to client 28,354 2.4 2,835.4
calls to get snapshot scn: kcmgss 1,652 0.1 165.2
calls to kcmgas 51 0.0 5.1
calls to kcmgcs 105 0.0 10.5
change write time 24 0.0 2.4
cleanouts and rollbacks - consist 0 0.0 0.0
cleanouts only - consistent read 3 0.0 0.3
cluster key scan block gets 628 0.1 62.8
cluster key scans 267 0.0 26.7
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: callbac 4 0.0 0.4
commit cleanouts 285 0.0 28.5
commit cleanouts successfully com 281 0.0 28.1
consistent changes 9 0.0 0.9
consistent gets 9,346 0.8 934.6
cursor authentications 60 0.0 6.0
data blocks consistent reads - un 9 0.0 0.9
db block changes 2,194 0.2 219.4
db block gets 7,650 0.7 765.0
deferred (CURRENT) block cleanout 157 0.0 15.7
dirty buffers inspected 0 0.0 0.0
enqueue conversions 2 0.0 0.2
enqueue releases 2,270 0.2 227.0
enqueue requests 2,271 0.2 227.1
enqueue timeouts 1 0.0 0.1
enqueue waits 0 0.0 0.0
execute count 1,388 0.1 138.8
free buffer inspected 0 0.0 0.0
free buffer requested 290 0.0 29.0
hot buffers moved to head of LRU 85 0.0 8.5
immediate (CR) block cleanout app 3 0.0 0.3
immediate (CURRENT) block cleanou 88 0.0 8.8
leaf node splits 34 0.0 3.4
logons cumulative 1,512 0.1 151.2
logons current
messages received 103 0.0 10.3
Instance Activity Stats for DB: ORA817 Instance: ORA817 Snaps: 11 -14

Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
messages sent 103 0.0 10.3
no buffer to keep pinned count 3,558 0.3 355.8
no work - consistent read gets 2,982 0.3 298.2
opened cursors cumulative 4,987 0.4 498.7
opened cursors current
parse count (hard) 68 0.0 6.8
parse count (total) 4,319 0.4 431.9
parse time cpu 84 0.0 8.4
parse time elapsed 86 0.0 8.6
physical reads 476 0.0 47.6
physical reads direct 395 0.0 39.5
physical writes 840 0.1 84.0
physical writes direct 395 0.0 39.5
physical writes non checkpoint 611 0.1 61.1
prefetched blocks 9 0.0 0.9
process last non-idle time 6,135,827,886 528,768.4 ############
recursive calls 39,348 3.4 3,934.8
recursive cpu usage 261 0.0 26.1
redo blocks written 2,787 0.2 278.7
redo buffer allocation retries 0 0.0 0.0
redo entries 1,176 0.1 117.6
redo log space requests 0 0.0 0.0
redo ordering marks 0 0.0 0.0
redo size 1,366,584 117.8 136,658.4
redo synch time 3 0.0 0.3
redo synch writes 7 0.0 0.7
redo wastage 12,760 1.1 1,276.0
redo write time 31 0.0 3.1
redo writes 56 0.0 5.6
rollback changes - undo records a 0 0.0 0.0
rollbacks only - consistent read 9 0.0 0.9
rows fetched via callback 664 0.1 66.4
session connect time 6,135,827,886 528,768.4 ############
session logical reads 16,996 1.5 1,699.6
session pga memory 244,883,532 21,103.4 ############
session pga memory max 244,883,532 21,103.4 ############
session uga memory 8,415,456 725.2 841,545.6
session uga memory max 32,544,376 2,804.6 3,254,437.6
sorts (disk) 10 0.0 1.0
sorts (memory) 959 0.1 95.9
sorts (rows) 21,246 1.8 2,124.6
summed dirty queue length 0 0.0 0.0
switch current to new buffer
table fetch by rowid 2,832 0.2 283.2
table fetch continued row 26 0.0 2.6
table scan blocks gotten 1,011 0.1 101.1
table scan rows gotten 5,894 0.5 589.4
table scans (long tables) 4 0.0 0.4
table scans (short tables) 87 0.0 8.7
total file opens 21 0.0 2.1
transaction rollbacks 0 0.0 0.0
user calls 3,239 0.3 323.9
user commits 10 0.0 1.0
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 0 0.0 0.0
Instance Activity Stats for DB: ORA817 Instance: ORA817 Snaps: 11 -14

Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
-------------------------------------------------------------
Tablespace IO Stats for DB: ORA817 Instance: ORA817 Snaps: 11 -14
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
TEMP
284 0 0.0 1.4 395 0 0 0.0
TOOLS
27 0 0.7 1.0 271 0 0 0.0
RBS
1 0 0.0 1.0 122 0 0 0.0
SYSTEM
52 0 3.3 1.2 52 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: ORA817 Instance: ORA817 Snaps: 11 -14
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
RBS /boss01/oradata/ORA817/rbs01.dbf
1 0 0.0 1.0 122 0 0

SYSTEM /boss01/oradata/ORA817/system01.dbf
52 0 3.3 1.2 52 0 0

TEMP /boss01/oradata/ORA817/temp01.dbf
284 0 0.0 1.4 395 0 0

TOOLS /boss01/oradata/ORA817/tools01.dbf
27 0 0.7 1.0 271 0 0

-------------------------------------------------------------
Buffer Pool Statistics for DB: ORA817 Instance: ORA817 Snaps: 11 -14
-> Pools D: default pool, K: keep pool, R: recycle pool

Free Write Buffer
Buffer Consistent Physical Physical Buffer Complete Busy
P Gets Gets Reads Writes Waits Waits Waits
- ----------- ------------- ----------- ---------- ------- -------- ----------
D 283 0 86 445 0 0 0
-------------------------------------------------------------
Rollback Segment Stats for DB: ORA817 Instance: ORA817 Snaps: 11 -14
->A high value for "Pct Waits" suggests more rollback segments may be required

Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ ------------ ------- --------------- -------- -------- --------
0 42.0 0.00 0 0 0 0
1 50.0 0.00 19,212 0 0 0
2 46.0 0.00 1,790 0 0 0
3 205.0 0.00 138,164 0 0 0
4 47.0 0.00 508 0 0 0
5 46.0 0.00 418 0 0 0
6 202.0 0.00 307,288 1 0 0
7 46.0 0.00 1,668 0 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: ORA817 Instance: ORA817 Snaps: 11 -14
->Optimal Size should be larger than Avg Active

RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 13,131,776 0 13,131,776
1 1,589,248 235,703 1,589,248
2 3,719,168 53,248 3,719,168
3 2,654,208 153,681 2,654,208
4 5,349,376 143,482 5,349,376
5 15,966,208 53,248 15,966,208
6 12,771,328 101,171 12,771,328
7 1,589,248 52,428 1,589,248
-------------------------------------------------------------
Latch Activity for DB: ORA817 Instance: ORA817 Snaps: 11 -14
->"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 Pct
Get Get Slps NoWait NoWait
Latch Name Requests Miss /Miss Requests Miss
----------------------------- -------------- ------ ------ ------------ ------
Token Manager 21 0.0 7 0.0
active checkpoint queue latch 3,780 0.0 0
archive control 1 0.0 0
archive process latch 1 0.0 0
cache buffer handles 12 0.0 0
cache buffers chains 35,501 0.0 288 0.0
cache buffers lru chain 805 0.0 0
channel handle pool latch 6 0.0 6 0.0
channel operations parent lat 12 0.0 6 0.0
checkpoint queue latch 16,397 0.0 0
dml lock allocation 215 0.0 0
enqueue hash chains 4,536 0.0 0
enqueues 11,862 0.0 0
event group latch 6 0.0 0
file number translation table 10 0.0 0
job_queue_processes parameter 180 0.0 0
ktm global data 38 0.0 0
library cache 61,775 0.0 7 0.0
library cache load lock 202 0.0 0
list of block allocation 114 0.0 0
loader state object freelist 44 0.0 0
messages 24,663 0.0 0
multiblock read objects 16 0.0 0
ncodef allocation latch 180 0.0 0
process allocation 6 0.0 6 0.0
process group creation 12 0.0 0
redo allocation 5,046 0.0 0
redo writing 15,219 0.0 0
row cache objects 3,906 0.0 7 0.0
sequence cache 29 0.0 0
session allocation 7,502 0.0 0
session idle bit 8,097 0.0 0
session switching 180 0.0 0
shared pool 13,241 0.0 0
sort extent pool 98 0.0 0
transaction allocation 246 0.0 0
transaction branch allocation 180 0.0 0
undo global data 1,152 0.0 0
user lock 20 0.0 0
-------------------------------------------------------------
Dictionary Cache Stats for DB: ORA817 Instance: ORA817 Snaps: 11 -14
->"Pct Misses" should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache

Get Pct Scan Pct Mod Final Pct
Cache Requests Miss Requests Miss Req Usage SGA
---------------------- ------------ ------ -------- ----- -------- ------ ----
dc_constraints 0 0 0 193 94
dc_database_links 0 0 0 4 80
dc_files 0 0 0 4 31
dc_free_extents 123 2.4 3 0.0 9 45 14
dc_global_oids 0 0 0 17 74
dc_histogram_data 0 0 0 0 0
dc_histogram_data_valu 0 0 0 0 0
dc_histogram_defs 0 0 0 108 86
dc_object_ids 106 0.0 0 0 580 99
dc_objects 201 5.0 0 2 1,943 100
dc_outlines 0 0 0 0 0
dc_profiles 7 0.0 0 0 1 20
dc_rollback_segments 617 0.0 0 0 9 90
dc_segments 62 0.0 0 3 364 100
dc_sequence_grants 0 0 0 0 0
dc_sequences 3 33.3 0 1 18 64
dc_synonyms 28 17.9 0 0 67 93
dc_tablespace_quotas 12 0.0 0 3 9 82
dc_tablespaces 29 0.0 0 0 10 71
dc_used_extents 3 100.0 0 3 107 86
dc_user_grants 47 0.0 0 0 19 76
dc_usernames 63 0.0 0 0 31 94
dc_users 147 0.0 0 0 28 93
ifs_acl_cache_entries 0 0 0 0 0
-------------------------------------------------------------


Library Cache Activity for DB: ORA817 Instance: ORA817 Snaps: 11 -14
->"Pct Misses" should be very low

Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 40 5.0 41 9.8 0 0
CLUSTER 24 4.2 35 5.7 0 0
INDEX 0 0 0 0
OBJECT 0 0 0 0
PIPE 0 0 0 0
SQL AREA 4,319 1.0 10,395 1.3 54 0
TABLE/PROCEDURE 561 9.6 777 24.8 69 0
TRIGGER 0 0 0 0
-------------------------------------------------------------
SGA Memory Summary for DB: ORA817 Instance: ORA817 Snaps: 11 -14

SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 16,777,216
Fixed Size 73,888
Redo Buffers 172,032
Variable Size 13,291,520
----------------
sum 30,314,656
-------------------------------------------------------------


SGA breakdown difference for DB: ORA817 Instance: ORA817 Snaps: 11 -14

Pool Name Begin value End value Difference
----------- ------------------------ -------------- -------------- -----------
java pool free memory 32,768 32,768 0
large pool free memory 614,400 614,400 0
shared pool DML locks 89,760 89,760 0
shared pool KGFF heap 7,116 7,116 0
shared pool KGK heap 4,064 4,064 0
shared pool KQLS heap 990,104 853,404 -136,700
shared pool PL/SQL DIANA 402,888 718,976 316,088
shared pool PL/SQL MPCODE 1,083,628 869,200 -214,428
shared pool PLS non-lib hp 2,096 2,096 0
shared pool State objects 194,024 194,024 0
shared pool character set memory 77,884 77,884 0
shared pool db_block_buffers 278,528 278,528 0
shared pool db_block_hash_buckets 98,328 98,328 0
shared pool db_files 72,496 72,496 0
shared pool db_handles 75,000 75,000 0
shared pool dictionary cache 1,717,812 1,723,008 5,196
shared pool event statistics per ses 590,240 590,240 0
shared pool fixed allocation callbac 320 320 0
shared pool free memory 1,299,180 950,572 -348,608
shared pool ktlbk state objects 80,036 80,036 0
shared pool library cache 2,706,488 2,726,252 19,764
shared pool long op statistics array 74,800 74,800 0
shared pool message pool freequeue 116,176 116,176 0
shared pool miscellaneous 678,412 748,140 69,728
shared pool processes 119,400 119,400 0
shared pool sessions 366,520 366,520 0
shared pool sql area 1,244,272 1,601,716 357,444
shared pool table columns 18,692 18,964 272
shared pool table definiti 960 1,840 880
shared pool transactions 166,804 166,804 0
shared pool trigger inform 120 180 60
db_block_buffers 16,777,216 16,777,216 0
fixed_sga 73,888 73,888 0
log_buffer 163,840 163,840 0
-------------------------------------------------------------
init.ora Parameters for DB: ORA817 Instance: ORA817 Snaps: 11 -14

End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
background_dump_dest /boss01/admin/ORA817/bdump
compatible 8.1.0
control_files /boss01/oradata/ORA817/control01.
core_dump_dest /boss01/admin/ORA817/cdump
db_block_buffers 2048
db_block_size 8192
db_name ORA817
distributed_transactions 10
instance_name ORA817
java_pool_size 32768
job_queue_interval 60
job_queue_processes 4
large_pool_size 614400
log_archive_dest_1 location=/boss01/admin/ORA817/arc
log_archive_dest_2 location=/boss01/admin/ORA817/arc
log_archive_format arch_%t_%s.arc
log_archive_start TRUE
log_buffer 163840
log_checkpoint_interval 10000
log_checkpoint_timeout 1800
max_enabled_roles 30
open_cursors 300
open_links 4
os_authent_prefix
processes 150
remote_login_passwordfile EXCLUSIVE
rollback_segments RBS0, RBS1, RBS2, RBS3, RBS4, RBS
service_names ORA817
shared_pool_size 10291456
sort_area_retained_size 65536
sort_area_size 65536
timed_statistics TRUE
user_dump_dest /boss01/admin/ORA817/udump
-------------------------------------------------------------

End of Report


Reference Documents
-------------------


1568 보기 태그: