| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Statspack report for you to look at
Hello,
I've run a statspack report of our not-quite-behaving test system and will try to figure out what means what over the next days.
Maybe someone would be interested in looking at it shortly and point me at things that look fishy at a glance. Would be great!
Otherwise maybe you have good pointers on resources that explain the statspack report.
thanks, br,
Martin
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- -------
FIP 3429414729 fip 1 9.2.0.1.0 NOXXXX-TEST
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- ---------
-------------------
Begin Snap: 2 19-Jan-07 17:08:08 50 15.6
End Snap: 3 19-Jan-07 17:31:54 50 15.8
Elapsed: 23.77 (mins)
Cache Sizes (end)
Buffer Cache: 24M Std Block Size: 8K
Shared Pool Size: 48M Log Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 12,283.18
1,233.25
Logical reads: 3,607.73
362.22
Block changes: 45.75
4.59
Physical reads: 293.60
29.48
Physical writes: 0.81
0.08
User calls: 181.03
18.18
Parses: 47.17
4.74
Hard parses: 2.61
0.26
Sorts: 24.75
2.48
Logons: 0.37
0.04
Executes: 273.44
27.45
Transactions: 9.96
% Blocks changed per Read: 1.27 Recursive Call %: 71.79
Rollback per transaction %: 0.04 Rows per Sort: 20.87
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.96 Redo NoWait %: 100.00
Buffer Hit %: 91.86 In-memory Sort %: 100.00
Library Hit %: 99.19 Soft Parse %: 94.46
Execute to Parse %: 82.75 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 74.49 % Non-Parse CPU: 90.77
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 94.63 94.61
% SQL with executions>1: 23.17 22.59
% Memory for SQL w/exec>1: 27.74 24.81
Top 5 Timed Events
Event Waits Time (s)Ela Time
db file scattered read 64,242 270
56.23
CPU time 118
24.49
db file sequential read 13,332 42
8.83
log file sync 7,072 19
4.04
enqueue 9 9
1.94
-------------------------------------------------------------
Wait Events for DB: FIP Instance: fip Snaps: 2 -3
-> 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
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
4.5
db file sequential read 13,332 0 42 3
0.9
log file sync 7,072 5 19 3
0.5
enqueue 9 0 9 1036
0.0
buffer busy waits 2,296 0 9 4
0.2
log file parallel write 12,606 12,344 7 1
0.9
latch free 701 21 2 3
0.0
SQL*Net more data to client 14,451 0 1 0
1.0
control file parallel write 478 0 1 2
0.0
db file parallel write 566 283 1 1
0.0
control file sequential read 116 0 0 4
0.0
local write wait 7 0 0 33
0.0
SQL*Net break/reset to clien 192 0 0 0
0.0
LGWR wait for redo copy 3 0 0 5
0.0
async disk IO 255 0 0 0
0.0
db file single write 2 0 0 0
0.0
SQL*Net message from client 243,538 0 10,164 42
17.1
jobq slave wait 2,777 1,698 7,280 2621
0.2
SQL*Net more data from clien 14,216 0 4 0
1.0
SQL*Net message to client 243,537 0 1 0
17.1
-------------------------------------------------------------
Background Wait Events for DB: FIP Instance: fip Snaps: 2 -3
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
0.9
control file parallel write 464 0 1 2
0.0
db file parallel write 566 283 1 1
0.0
db file scattered read 20 0 0 5
0.0
db file sequential read 11 0 0 5
0.0
control file sequential read 23 0 0 2
0.0
LGWR wait for redo copy 3 0 0 5
0.0
latch free 1 0 0 5
0.0
rdbms ipc message 32,275 13,998 4,461 138
2.3
smon timer 5 5 1,462 ######
0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: FIP Instance: fip Snaps: 2 -3
-> End Buffer Gets Threshold: 10000
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
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
1,714,796 1,316 1,303.0 33.3 45.28 57.57
3172615181
BEGIN MONITOR.COLLECT_CYCLIC_MACHINE_DATA; END;
775,666 240 3,231.9 15.1 16.31 19.86
979285868
begin MONITOR.GET_VALUES_ON_SHIFT_CHANGE(:1,:2,:3,:4, :5); end;
603,117 7,143 84.4 11.7 4.84 6.10 1088321592
SELECT ID, STARTED, FINISHED FROM SHIFTS WHERE star
ted <= :b2 AND finished > :b2 AND machine = :b1
572,523 890 643.3 11.1 14.61 155.62
541669470
572,523 890 643.3 11.1 15.27 156.84
1073641791
begin MONITOR.GET_ORDER_WITH_MATERIAL(:1,:2,:3, :4,:5,:6); end;
526,680 13,860 38.0 10.2 6.19 6.29
28586213
SELECT /*+ INDEX (t PK_TEXTS)*/ is_set, is_error, text, error_
bit FROM texts t, (SELECT ts.error_bit, ts.text ID, POWER
(2, ts.error_bit), MOD (TRUNC (v.VALUE / POWE
R (2, ts.error_bit)),2) is_error, MOD (TRUNC
(:1 / POWER (2, ts.error_bit)), 2) is_set FROM ter
518,378 335 1,547.4 10.1 15.39 153.95
3766815070
478,062 5,264 90.8 9.3 2.25 3.40
2739879392
SELECT NVL(TO_CHAR(Common.GetText(c.Text, :b2)),'-'), 1 from
current_machine_states a, DOWN_TIME_GROUP_REASONS b, DOWN_TIME_
GROUPS c WHERE b.reason = (a.main_dt_1_text-10000) and a.mac
hine_id = :b1 and c.ID = b."GROUP" union SELECT NVL(TO_CHAR(Co
mmon.GetText(c.Text, :b2)),'-'), 2 from current_machine_stat
381,584 731 522.0 7.4 6.56 7.93
395610491
SELECT MDT.ID , MB.Time_stamp TS_START , ME.Time_stamp TS_
END , VALIDATED_REASON , DECODE( SIGN(MB.Time_stamp - NVL
(:b4, sysdate)), -1,-- Zeit vor Setup-Ende DECODE(
SIGN(900 - MDT.Validated_Reason), 1, -- manuelle Down
times DECODE(MDT.Validated_Reason, 0, 0, 1, 0, 1),
344,406 14,342 24.0 6.7 8.11 9.26
3367168551
SELECT id from measures where order_id = :b1 and order_s
SQL ordered by Gets for DB: FIP Instance: fip Snaps: 2 -3
-> End Buffer Gets Threshold: 10000
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
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
ort = (select max(order_sort) from measur
es where order_id = :b1 and time_stamp <= :b2)
285,592 5,264 54.3 5.6 4.05 5.52
2752577108
198,592 825 240.7 3.9 3.75 5.51
3440205312
begin MONITOR.GET_VALUES_ON_ORDER_CHANGE(:1,:2, :3,:4,:5,:6,:7,:
8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24
,:25,:26, :27,:28); end;
148,723 5,264 28.3 2.9 2.48 3.54 2417773183
SELECT o.ID FROM ORDERS o WHERE 1=1 AND o.machine_id=:b1 AND o.started<=SYSDATE
AND o.finished IS NULL
107,884 13,672 7.9 2.1 0.63 1.16
248083791
SELECT id from measures where order_sort = ( SELECT MIN(ord
er_sort) FROM MEASURES m1 WHERE m1.order_sort > (select or
der_sort from MEASURES M2 where M2.id= :b2 ) AND m1.order_id = :b1 ) and order_id = :b1
96,310 24 4,012.9 1.9 2.19 2.96
1078465406
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN begin -- ID string: calculate_
actualshift_values -- MONITOR.CALCULATE_ACTUALSHIFT_VALUES; ex
ception when others then Log_String('**** Job run for calculat
e_actualshift_values failed with: '||SQLERRM); end; :mydate :=
86,544 1,822 47.5 1.7 0.69 0.89
2191507256
SELECT DECODE(SIGN(O.Started - :b3), 1, O.Started, :b3) Started
, DECODE(SIGN(O.Finished - :b2), 1, :b2, -
- if O.fin > shift.fin then use shift end NULL, :b2, -- if
O.fin is NULL (shift_fin NEVER is NULL) use shift end
O.Finished) Finished -- else (i.e. Ordefin < shiftfin) use
80,807 5,264 15.4 1.6 0.73 1.05
3234184439
SELECT NVL(MACHINE_REASON_ID,0), 1 FROM MACHINE_DOWN_TIME_
REASONS WHERE reason_id = (SELECT (main_dt_1_text-10000) F
ROM CURRENT_MACHINE_STATES WHERE machine_id = :b1) and machine_i
d = :b1 union SELECT NVL(MACHINE_REASON_ID,0), 2 FROM MAC
HINE_DOWN_TIME_REASONS WHERE reason_id = (SELECT (main_dt_2
80,512 27,344 2.9 1.6 0.77 0.78
3832984725
SQL ordered by Gets for DB: FIP Instance: fip Snaps: 2 -3
-> End Buffer Gets Threshold: 10000
-> 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
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
275,679 890 309.8 65.8 14.61 155.62
541669470
SELECT o.id, nvl(t.text,'-'), NVL(po.production_number, '-'), NV
L(po.sales_number, '-'), TO_CHAR(o.started, date_time_format),
NVL(TO_CHAR(po.end_setup, date_time_format), '-'), nvl(TO_CHA
R(o.finished, date_time_format),'-'), nvl(Materials_Pcg.get_ma
terial_number(po.material_number),'-') from orders o, production
275,679 890 309.8 65.8 15.27 156.84
1073641791
begin MONITOR.GET_ORDER_WITH_MATERIAL(:1,:2,:3, :4,:5,:6); end;
111,498 335 332.8 26.6 15.39 153.95
3766815070
select /*+ORDERED*/ ((sysdate - :1/24)- TO_DATE('1970-01-01', '
YYYY-MM-DD'))* 24 * 60 * 60 * 1000 time_stamp, 'M' event, m.orde
r_id, NULL order_type, mechanical_speed, machine_speed, out
put_count, machine_status, NULL production_number, NULL target_s
peed, NULL minimum_run_speed, DECODE(mdt.validated_reason,
18,997 19 999.8 4.5 1.02 21.36
501170509
SELECT /*+ INDEX(mdt IX_MDT_1) */ DECODE(mdt.validated_reaso
n,NULL,NULL,DECODE(mdt.validated_reason,0,'U','A')) error_type,
t.text error_text, DECODE(mdt.validated_reason,NULL,NULL
, DECODE( SIGN(NVL(po.end_setup, sysdate) -
m.time_stamp), 1, -- in setup DECODE(
18,997 19 999.8 4.5 1.09 21.46
1909573811
9,278 143 64.9 2.2 0.36 15.36
177173545
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN begin -- ID string: calculate_
downtime_values -- MONITOR.CALCULATE_DOWNTIME_VALUES; exceptio
n when others then Log_String('**** Job run for CALCULATE_DOWN
TIME_VALUES failed with: '||SQLERRM); end; :mydate := next_date;
4,641 7 663.0 1.1 0.13 6.90 2403536560
SELECT Text_Id , Validated_Reason , COUNT(*) occur encies , SUM(DOWN_TIME) total_duration FROM DOWN_TIMES WHERE Order_Id = :b1 AND DOWN_TIME > 0GROUP BY Validated_Reason, Text_Id ORDER BY total_duration DES C, occurencies DESC
4,628 7 661.1 1.1 0.16 8.28 2846634461
SELECT NVL(SUM(WAITING_TIME), 0) , NVL(SUM(UNATTENDED_TIME ), 0) , NVL(SUM(DOWN_TIME_SETUP), 0) FROM DOWN_TIMESWHERE ORDER_ID = :b1
572 1 572.0 0.1 0.19 2.39
1567150439
Module: T.O.A.D.
SELECT SEGMENT_TYPE, owner, DECODE( PARTITION_NAME, NULL, segment_name, segment_name || ':' || PARTITION_NAME), file_id, block_id, blocks, bytes FROM DBA_EXTENTS WHERE tables pace_name = :tName ORDER BY 3, 4
473 4 118.3 0.1 0.23 1.65
109602229
SQL ordered by Reads for DB: FIP Instance: fip Snaps: 2 -3
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
SELECT O.Id FROM PRODUCTION_ORDERS PO, ORDERS O, SHIFT_ORD
ERS SO WHERE PO.Order_Id = O.Id AND O.Id
= SO."ORDER" AND O.Machine_Id = :b3
AND o.Finished <= :b2 --TO_DATE('17.01.2005 20:
48:11', 'DD.MM.YYYY HH24:MI:SS') AND O.TYPE
473 2 236.5 0.1 0.27 1.67
2264690778
344 140 2.5 0.1 0.02 1.00 95462927
SELECT max(output_count) --23776 FROM MEASURES M, produc
tion_orders PO WHERE po.order_id = :b1 and po.order_i
d = m.order_id and m.time_stamp <= po.end_Setup
185 825 0.2 0.0 3.75 5.51
3440205312
137 825 0.2 0.0 0.44 1.82
1168573709
SELECT sum(output_count) from measures m, (select ord
er_id, measures_pcg.get_last_order_measure(order_id) last_id
from production_orders po where production_number = :b1
) p_orders where p_orders.order_id = m.order_id and m.
id = p_orders.last_id and p_orders.order_id <> :b2
59 1,316 0.0 0.0 45.28 57.57
3172615181
33 240 0.1 0.0 16.31 19.86
979285868
begin MONITOR.GET_VALUES_ON_SHIFT_CHANGE(:1,:2,:3,:4, :5); end;
33 1,822 0.0 0.0 0.69 0.89
2191507256
SELECT DECODE(SIGN(O.Started - :b3), 1, O.Started, :b3) Started
, DECODE(SIGN(O.Finished - :b2), 1, :b2, -
- if O.fin > shift.fin then use shift end NULL, :b2, -- if
O.fin is NULL (shift_fin NEVER is NULL) use shift end
O.Finished) Finished -- else (i.e. Ordefin < shiftfin) use
29 147 0.2 0.0 0.02 0.22
2385919346
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
41,737 22,990 0.6 0.00 0.00
1032246506
SELECT text FROM texts WHERE ID=:b2 and language_id=:b1
27,344 26,836 1.0 0.00 0.00
3832984725
SELECT Output_Count, Time_Stamp FROM MEASURES WHERE ID=:b1
14,342 14,212 1.0 0.00 0.00 3367168551
SELECT id from measures where order_id = :b1 and order_s
ort = (select max(order_sort) from measur
es where order_id = :b1 and time_stamp <= :b2)
14,286 14,286 1.0 0.00 0.00
1952305027
SELECT ID, STARTED, FINISHED FROM SHIFTS WHERE machi
ne = :b2 AND STARTED <= :b1 ORDER BY STARTED desc
14,286 14,286 1.0 0.00 0.00
4093655816
13,860 180,180 13.0 0.00 0.00
28586213
SELECT /*+ INDEX (t PK_TEXTS)*/ is_set, is_error, text, error_
bit FROM texts t, (SELECT ts.error_bit, ts.text ID, POWER
(2, ts.error_bit), MOD (TRUNC (v.VALUE / POWE
R (2, ts.error_bit)),2) is_error, MOD (TRUNC
(:1 / POWER (2, ts.error_bit)), 2) is_set FROM ter
13,860 13,860 1.0 0.00 0.00
2618599410
13,839 13,839 1.0 0.00 0.00
2881733146
SELECT order_id from measures where id = :b1
13,672 13,164 1.0 0.00 0.00
248083791
SELECT id from measures where order_sort = ( SELECT MIN(ord
er_sort) FROM MEASURES m1 WHERE m1.order_sort > (select or
der_sort from MEASURES M2 where M2.id= :b2 ) AND m1.order_id = :b1 ) and order_id = :b1
13,672 13,672 1.0 0.00 0.00
2447817262
SELECT Time_Stamp FROM MEASURES WHERE ID=:b1
10,028 10,028 1.0 0.00 0.00
2856961286
SELECT o.started FROM ORDERS o WHERE o.ID=:b1
9,712 7,932 0.8 0.00 0.00 3009726945
SELECT sg.abbreviation ||'/'|| st.abbreviation ||'/'||
m.sack_width ||'/'|| m.sack_length ||'/'||
CASE UPPER(TRIM(sg.abbreviation)) -- upper and trim just to ma
SQL ordered by Executions for DB: FIP Instance: fip Snaps: 2 -3
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
ke sure WHEN 'F200' THEN NVL(m.bottom_width_drive_side, 0)
WHEN 'F210' THEN NVL(m.bottom_width_drive_side, 0)
8,086 8,074 1.0 0.00 0.00
2044415509
7,552 0 0.0 0.00 0.00
3615375148
COMMIT
7,143 7,143 1.0 0.00 0.00
1088321592
SELECT ID, STARTED, FINISHED FROM SHIFTS WHERE star
ted <= :b2 AND finished > :b2 AND machine = :b1
5,264 5,264 1.0 0.00 0.00
446144609
SELECT (NVL(total_sack_count, 0) + NVL(:b2, 0)) FROM MACHINE
S WHERE id = :b1
5,264 5,264 1.0 0.00 0.00
1821253087
SELECT last_comm , DECODE(main_drive_off, NULL, NULL, DEC
ODE( SIGN(main_drive_off-order_started), -1, order_started, main
_drive_off)) , terminal_status , terminal_offline
--, (NVL(total_shift_count, 0) + NVL(Output_count, 0))
, Input_count , Output_count FROM CURRENT_MACHIN
5,264 5,264 1.0 0.00 0.00
2011832525
FROM PRODUCTION_ORDERS WHERE order_id = :b1
5,264 5,264 1.0 0.00 0.00
2094967429
SELECT date_time_format FROM LANGUAGES WHERE id = :b1
5,264 5,264 1.0 0.00 0.00 2417773183
SELECT o.ID FROM ORDERS o WHERE 1=1 AND o.machine_id=:b1 AND o.started<=SYSDATE
AND o.finished IS NULL
5,264 5,264 1.0 0.00 0.00 2546340231
SELECT deviator_1_count , deviator_2_count , D ECODE(:b3, NULL, 0, SYSDATE - :b3) , NVL(order_end_setu p, SYSDATE) - NVL(order_started, SYSDATE) , order_end_s etup , NVL(down_time, 0) + DECODE(:b3, NULL, 0, DECODE(current_error, 0, 0, 1, DECODE(order_end_setup, NULL, 0, SYSDATE
5,264 4,645 0.9 0.00 0.00
2739879392
SELECT NVL(TO_CHAR(Common.GetText(c.Text, :b2)),'-'), 1 from
current_machine_states a, DOWN_TIME_GROUP_REASONS b, DOWN_TIME_
GROUPS c WHERE b.reason = (a.main_dt_1_text-10000) and a.mac
hine_id = :b1 and c.ID = b."GROUP" union SELECT NVL(TO_CHAR(Co
mmon.GetText(c.Text, :b2)),'-'), 2 from current_machine_stat
5,264 5,264 1.0 0.00 0.00
2752577108
SELECT NVL(MIN(MEASURES_PCG.GET_PRODUCED_PCS(ID, O.Started, PO.E
nd_Setup)), 0) FROM ORDERS O, PRODUCTION_ORDERS PO WHERE
O.id = (SELECT Best_Order_Id FROM PRODUCTION_ORDERS WHERE Order_
SQL ordered by Executions for DB: FIP Instance: fip Snaps: 2 -3
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
% Total
13,860 13,860 20.61 28586213 SELECT /*+ INDEX (t PK_TEXTS)*/ is_set, is_error, text, error_
bit FROM texts t, (SELECT ts.error_bit, ts.text ID, POWER
(2, ts.error_bit), MOD (TRUNC (v.VALUE / POWE
R (2, ts.error_bit)),2) is_error, MOD (TRUNC
(:1 / POWER (2, ts.error_bit)), 2) is_set FROM ter
13,860 13,860 20.61 2618599410
select omcmd.order_id, omcmd.order_prod_number, omcmd.shift_id,
omcmd.deviator_1_count, omcmd.deviator_2_count, omcmd.total_time
, omcmd.actual_down_time, omcmd.setup_time, omcmd.down_time, omc
md.unattended_time, omcmd.attended_time, omcmd.running_time, o
mcmd.waiting_time_setup, omcmd.max_prod_speed_diff, omcmd.output
3,122 3,122 4.64 2091761008 select condition from cdef$ where rowid=:1
1,343 7,552 2.00 3615375148 COMMIT 1,316 1,316 1.96 65322375 SELECT upper(COMMAND) FROM OM_THREAD WHERE THREAD_ID = :1
1,316 1,316 1.96 1648283708 UPDATE OM_THREAD SET STATE = :1, UPTIME = SYSDATE WHERE THREAD_I D = :2
1,316 1,316 1.96 3172615181 BEGIN MONITOR.COLLECT_CYCLIC_MACHINE_DATA; END; 1,039 1,039 1.54 2952206694 SELECT TERMINAL_STATUS FROM CURRENT_MACHINE_STATES WHERE MACHINE _ID = 63
1,038 1,038 1.54 2363762331 update current_machine_states set terminal_offline = sysdate whe re MACHINE_ID = 63
1,037 1,037 1.54 1530097468 update current_machine_states set terminal_offline = sysdate whe re MACHINE_ID = 44
1,037 1,037 1.54 1586745005 SELECT TERMINAL_STATUS FROM CURRENT_MACHINE_STATES WHERE MACHINE _ID = 44
1,037 1,037 1.54 3822928087 SELECT TERMINAL_STATUS, ROWID InternalRowid FROM CURRENT_MACHINE _STATES WHERE MACHINE_ID = 63 1,035 1,035 1.54 4290538388 SELECT TERMINAL_STATUS, ROWID InternalRowid FROM CURRENT_MACHINE _STATES WHERE MACHINE_ID = 44 SQL ordered by Parse Calls for DB: FIP Instance: fip Snaps: 2 -3 -> End Parse Calls Threshold: 1000
% Total
1,029 1,029 1.53 1817764562 SELECT ID FROM ORDERS WHERE STARTED IS NOT NULL AND FINISHED IS NULL AND MACHINE_ID = 43 1,029 1,029 1.53 2116669357 SELECT ID, ROWID InternalRowid FROM ORDERS WHERE STARTED IS NOT NULL AND FINISHED IS NULL AND MACHINE_ID = 43 1,028 1,028 1.53 1322278153 UPDATE current_machine_states SET TERMINAL_STATUS = 1879048193 W HERE MACHINE_ID = 63 1,027 1,027 1.53 3409839948 UPDATE current_machine_states SET TERMINAL_STATUS = 1879048193 W HERE MACHINE_ID = 44 941 941 1.40 824379283 SELECT TERMINAL_STATUS FROM CURRENT_MACHINE_STATES WHERE MACHINE _ID = 43
940 940 1.40 2036392714 SELECT TERMINAL_STATUS, ROWID InternalRowid FROM CURRENT_MACHINE _STATES WHERE MACHINE_ID = 43 890 890 1.32 1073641791 begin MONITOR.GET_ORDER_WITH_MATERIAL(:1,:2,:3, :4,:5,:6); end;
825 825 1.23 3440205312 begin MONITOR.GET_VALUES_ON_ORDER_CHANGE(:1,:2, :3,:4,:5,:6,:7,: 8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24 ,:25,:26, :27,:28); end;
693 693 1.03 411902687 begin MONITOR.WRITE_SYSTEM_ONLINE_INFO(:1,:2,:3,:4, :5,:6,:7,:8, :9, :10,:11,:12,:13, :14,:15,:16,:17, :18,:19, :20, :21); end;
606 606 0.90 553489157 SELECT ID, ROWID InternalRowid FROM ORDERS WHERE STARTED IS NOT NULL AND FINISHED IS NULL AND MACHINE_ID = 23 606 606 0.90 708986501 SELECT ID FROM ORDERS WHERE STARTED IS NOT NULL AND FINISHED IS NULL AND MACHINE_ID = 23 604 604 0.90 1369924252 insert into shift_orders ("ORDER", "SHIFT") select 3542 "ORDER", 62627 "SHIFT" from dual where not exists ( select "ORDER" from shift_orders where "SHIFT" = 62627 and "ORDER" = 3542)
601 601 0.89 3013728279 select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#= 1) and privilege#>0
SQL ordered by Parse Calls for DB: FIP Instance: fip Snaps: 2 -3 -> End Parse Calls Threshold: 1000
% Total
526 526 0.78 493392946 update sys.job$ set this_date=:1 where job=:2
526 526 0.78 990457800 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
526 526 0.78 3128358781 update sys.job$ set failures=0, this_date=null, flag=:1, last_da te=:2, next_date = greatest(:3, sysdate), total=total+(sysdate
Statistic Total per Secondper Trans
CPU used by this session 11,767 8.3
0.8
CPU used when call started 11,732 8.2
0.8
CR blocks created 776 0.5
0.1
Cached Commit SCN referenced 0 0.0
0.0
Commit SCN cached 0 0.0
0.0
DBWR buffers scanned 2,709 1.9
0.2
DBWR checkpoint buffers written 561 0.4
0.0
DBWR checkpoints 0 0.0
0.0
DBWR free buffers found 2,243 1.6
0.2
DBWR lru scans 8 0.0
0.0
DBWR make free requests 8 0.0
0.0
DBWR revisited being-written buff 0 0.0
0.0
DBWR summed scan depth 2,709 1.9
0.2
DBWR transaction table writes 0 0.0
0.0
DBWR undo block writes 919 0.6
0.1
SQL*Net roundtrips to/from client 243,598 170.8
17.2
SQL*Net roundtrips to/from dblink 16 0.0
0.0
active txn count during cleanout 698 0.5
0.1
background checkpoints completed 0 0.0
0.0
background checkpoints started 0 0.0
0.0
background timeouts 1,683 1.2
0.1
buffer is not pinned count 3,996,225 2,802.4
281.4
buffer is pinned count 32,737,905 22,957.9
2,305.0
bytes received via SQL*Net from c 78,455,180 55,017.7
5,523.9
bytes received via SQL*Net from d 1,051 0.7
0.1
bytes sent via SQL*Net to client 91,382,166 64,082.9
6,434.0
bytes sent via SQL*Net to dblink 6,692 4.7
0.5
calls to get snapshot scn: kcmgss 584,086 409.6
41.1
calls to kcmgas 15,211 10.7
1.1
calls to kcmgcs 23 0.0
0.0
change write time 88 0.1
0.0
cleanout - number of ktugct calls 702 0.5
0.1
cleanouts and rollbacks - consist 640 0.5
0.1
cleanouts only - consistent read 10 0.0
0.0
cluster key scan block gets 11,635 8.2
0.8
cluster key scans 10,634 7.5
0.8
commit cleanout failures: block l 0 0.0
0.0
commit cleanout failures: callbac 10 0.0
0.0
commit cleanout failures: cannot 1 0.0
0.0
commit cleanouts 16,187 11.4
1.1
commit cleanouts successfully com 16,176 11.3
1.1
commit txn count during cleanout 33 0.0
0.0
consistent changes 1,034 0.7
0.1
consistent gets 5,089,527 3,569.1
358.3
consistent gets - examination 1,755,893 1,231.3
123.6
cursor authentications 274 0.2
0.0
data blocks consistent reads - un 1,034 0.7
0.1
db block changes 65,245 45.8
4.6
db block gets 55,092 38.6
3.9
deferred (CURRENT) block cleanout 11,297 7.9
0.8
dirty buffers inspected 120 0.1
0.0
enqueue conversions 40 0.0
0.0
enqueue releases 42,060 29.5
3.0
enqueue requests 42,067 29.5
3.0
enqueue timeouts 5 0.0
0.0
enqueue waits 9 0.0
0.0
Statistic Total per Secondper Trans
execute count 389,931 273.4
27.5
free buffer inspected 725 0.5
0.1
free buffer requested 420,416 294.8
29.6
hot buffers moved to head of LRU 2,126 1.5
0.2
immediate (CR) block cleanout app 650 0.5
0.1
immediate (CURRENT) block cleanou 310 0.2
0.0
index fast full scans (full) 1,562 1.1
0.1
index fetch by key 816,238 572.4
57.5
index scans kdiixs1 326,506 229.0
23.0
leaf node 90-10 splits 1 0.0
0.0
leaf node splits 12 0.0
0.0
logons cumulative 531 0.4
0.0
messages received 12,917 9.1
0.9
messages sent 12,917 9.1
0.9
no buffer to keep pinned count 0 0.0
0.0
no work - consistent read gets 2,835,343 1,988.3
199.6
opened cursors cumulative 61,821 43.4
4.4
parse count (failures) 35 0.0
0.0
parse count (hard) 3,723 2.6
0.3
parse count (total) 67,258 47.2
4.7
parse time cpu 1,086 0.8
0.1
parse time elapsed 1,458 1.0
0.1
physical reads 418,673 293.6
29.5
physical reads direct 0 0.0
0.0
physical writes 1,154 0.8
0.1
physical writes direct 0 0.0
0.0
physical writes non checkpoint 1,125 0.8
0.1
pinned buffers inspected 600 0.4
0.0
prefetched blocks 341,100 239.2
24.0
prefetched blocks aged out before 51 0.0
0.0
process last non-idle time 617,350,061,345 432,924,306.7
############
recovery array read time 0 0.0
0.0
recovery array reads 0 0.0
0.0
recovery blocks read 0 0.0
0.0
recursive calls 656,907 460.7
46.3
recursive cpu usage 4,516 3.2
0.3
redo blocks written 44,088 30.9
3.1
redo buffer allocation retries 0 0.0
0.0
redo entries 33,116 23.2
2.3
redo log space requests 0 0.0
0.0
redo log space wait time 0 0.0
0.0
redo ordering marks 0 0.0
0.0
redo size 17,515,812 12,283.2
1,233.3
redo synch time 2,045 1.4
0.1
redo synch writes 7,066 5.0
0.5
redo wastage 4,247,148 2,978.4
299.0
redo write time 923 0.7
0.1
redo writer latching time 0 0.0
0.0
redo writes 12,609 8.8
0.9
rollback changes - undo records a 73 0.1
0.0
rollbacks only - consistent read 141 0.1
0.0
rows fetched via callback 622,570 436.6
43.8
session connect time 617,350,061,345 432,924,306.7
############
session logical reads 5,144,619 3,607.7
362.2
session pga memory 9,327,796 6,541.2
656.8
session pga memory max 3,516,148 2,465.7
247.6
Instance Activity Stats for DB: FIP Instance: fip Snaps: 2 -3
Statistic Total per Secondper Trans
session uga memory 6,309,264 4,424.5
444.2
session uga memory max 143,290,880 100,484.5
10,088.8
shared hash latch upgrades - no w 327,877 229.9
23.1
shared hash latch upgrades - wait 0 0.0
0.0
sorts (disk) 0 0.0
0.0
sorts (memory) 35,291 24.8
2.5
sorts (rows) 736,351 516.4
51.8
summed dirty queue length 117 0.1
0.0
switch current to new buffer 17 0.0
0.0
table fetch by rowid 17,335,090 12,156.4
1,220.5
table fetch continued row 3,144 2.2
0.2
table scan blocks gotten 1,451,800 1,018.1
102.2
table scan rows gotten 112,173,648 78,663.2
7,897.9
table scans (cache partitions) 0 0.0
0.0
table scans (long tables) 1,288 0.9
0.1
table scans (short tables) 75,247 52.8
5.3
transaction rollbacks 66 0.1
0.0
user calls 258,153 181.0
18.2
user commits 14,198 10.0
1.0
user rollbacks 5 0.0
0.0
workarea executions - onepass 0 0.0
0.0
workarea executions - optimal 53,678 37.6
3.8
write clones created in foregroun 0 0.0
0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: FIP Instance: fip Snaps: 2 -3
->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)
0 0 0.0 7 0 0
0.0
-------------------------------------------------------------
File IO Stats for DB: FIP Instance: fip Snaps: 2 -3
->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)
STATSPACK C:\ORACLE\ORADATA\FIP\STATSPACK01.DBF
0 0 7 0 0
SYSTEM C:\ORACLE\ORADATA\FIP\SYSTEM01.DBF
1,370 1 5.7 1.6 15 0 1
90.0
UNDOTBS1 C:\ORACLE\ORADATA\FIP\UNDOTBS01.DBF
3 0 10.0 1.0 921 1 1
0.0
USERS C:\ORACLE\ORADATA\FIP\USERS01.DBF
76,183 53 4.0 5.5 211 0 2,294
4.0
Free
Write Buffer
Number of Cache Buffer Physical Physical Buffer
Complete Busy
P Buffers Hit % Gets Reads Writes Waits
Waits Waits
Instance Recovery Stats for DB: FIP Instance: fip Snaps: 2 -3 -> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size TimeoutInterval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks
Redo Blks
- ----- ----- ---------- ---------- ---------- ---------- ----------
B 0 0 52226 51684 184320 51684
E 0 0 59067 58400 184320 58400
-------------------------------------------------------------
Buffer Pool Advisory for DB: FIP Instance: fip End Snap: 3 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate
Size for Size Buffers for Est Physical
Estimated
P Estimate (M) Factr Estimate Read Factor Physical
Reads
--- ------------ ----- ---------------- -------------
------------------
D 4 .2 500 16.29 295,384,011 D 8 .3 1,000 1.50 27,283,868 D 12 .5 1,500 1.26 22,864,630 D 16 .7 2,000 1.06 19,140,642 D 20 .8 2,500 1.02 18,427,381 D 24 1.0 3,000 1.00 18,137,066 D 28 1.2 3,500 0.99 17,964,318 D 32 1.3 4,000 0.98 17,833,488 D 36 1.5 4,500 0.98 17,723,617 D 40 1.7 5,000 0.97 17,626,845 D 44 1.8 5,500 0.97 17,550,378 D 48 2.0 6,000 0.96 17,462,448 D 52 2.2 6,500 0.96 17,396,788 D 56 2.3 7,000 0.96 17,329,981 D 60 2.5 7,500 0.95 17,286,753 D 64 2.7 8,000 0.95 17,228,461 D 68 2.8 8,500 0.95 17,187,689 D 72 3.0 9,000 0.95 17,151,502 D 76 3.2 9,500 0.94 17,119,572 D 80 3.3 10,000 0.94 17,066,683 -------------------------------------------------------------
Buffer wait Statistics for DB: FIP Instance: fip Snaps: 2 -3 -> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block 2,295 9 4
undo header 1 0 0
-------------------------------------------------------------
PGA Aggr Target Stats for DB: FIP Instance: fip Snaps: 2 -3
-> B: Begin snap E: End snap (rows dentified 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 control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 3,071 0
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global
Mem
PGA Aggr Target Histogram for DB: FIP Instance: fip Snaps: 2 -3 -> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
8K 16K 49,524 49,524 0 0
16K 32K 27 27 0 0
32K 64K 266 266 0 0
64K 128K 519 519 0 0
128K 256K 196 196 0 0
512K 1024K 3,224 3,224 0 0
1M 2M 4 4 0 0
-------------------------------------------------------------
PGA Memory Advisory for DB: FIP Instance: fip End Snap: 3 -> 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
---------- ------- ---------------- ---------------- --------
----------
32 0.1 71,918.9 10,314.7 87.0
165
64 0.3 71,918.9 7,285.8 91.0
7
128 0.5 71,918.9 3,600.2 95.0
0
192 0.8 71,918.9 1,770.2 98.0
0
256 1.0 71,918.9 1,770.2 98.0
0
307 1.2 71,918.9 1,770.2 98.0
0
358 1.4 71,918.9 1,770.2 98.0
0
410 1.6 71,918.9 1,770.2 98.0
0
461 1.8 71,918.9 1,770.2 98.0
0
512 2.0 71,918.9 1,770.2 98.0
0
768 3.0 71,918.9 1,770.2 98.0
0
1,024 4.0 71,918.9 1,770.2 98.0
0
1,536 6.0 71,918.9 1,770.2 98.0
0
2,048 8.0 71,918.9 1,770.2 98.0
0
-------------------------------------------------------------
Enqueue activity for DB: FIP Instance: fip Snaps: 2 -3
-> Enqueue stats gathered prior to 9i should not be compared with 9i
data
Avg Wt
Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms)
Time (s)
TX 14,324 14,324 0 9 1,033.22
9
-------------------------------------------------------------
Rollback Segment Stats for DB: FIP Instance: fip Snaps: 2 -3
->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 6.0 0.00 0 0 0 0
1 3,587.0 0.00 845,498 0 0 0
2 3,589.0 0.00 848,650 1 0 1
3 3,592.0 0.00 855,800 1 0 1
4 3,601.0 0.00 852,772 2 0 1
5 3,589.0 0.00 804,376 1 0 1
6 3,622.0 0.00 848,320 1 0 0
7 3,596.0 0.00 827,080 1 0 1
8 3,629.0 0.00 1,001,044 0 0 0
RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- ---------------
0 401,408 0 401,408
1 6,414,336 710,336 7,462,912
2 6,414,336 702,376 14,802,944
3 6,414,336 710,933 13,754,368
4 5,365,760 590,360 10,608,640
5 6,414,336 711,134 11,657,216
6 5,365,760 711,661 13,754,368
7 6,414,336 710,890 7,462,912
8 10,608,640 299,827,687 268,558,336
-------------------------------------------------------------
Undo Segment Summary for DB: FIP Instance: fip Snaps: 2 -3
-> Undo segment block stats:
Undo Undo Num Max Qry Max Tx Snapshot Out of
uS/uR/uU/
TS# Blocks Trans Len (s) Concurcy Too Old Space
eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------
1 1,193 4,159,153 3 1 0 0 0/0/0/0/0/0
Undo Segment Stats for DB: FIP Instance: fip Snaps: 2 -3 -> ordered by Time desc
Undo Num Max Qry Max Tx Snap Out of
uS/uR/uU/
End Time Blocks Trans Len (s) Concy Too Old Space
eS/eR/eU
Pct Avg Wait
Pct
Get Get Slps Time
NoWait NoWait
Latch Requests Miss /Miss (s)
Requests Miss
Pct Avg Wait
Pct
Get Get Slps Time
NoWait NoWait
Latch Requests Miss /Miss (s)
Requests Miss
Get
Spin &
Latch Name Requests Misses Sleeps
Sleeps 1->4
-------------------------- -------------- ----------- -----------
------------
library cache 4,393,068 606 625
0/588/17/1/0
cache buffers chains 9,064,863 16 18
0/0/0/0/0
shared pool 1,653,320 14 14
0/14/0/0/0
session allocation 769,553 12 12
0/12/0/0/0
cache buffers lru chain 1,135,868 10 11
0/9/1/0/0
library cache pin allocati 731,312 5 5
0/5/0/0/0
multiblock read objects 149,308 5 6
0/4/1/0/0
library cache pin 1,778,180 3 3
0/3/0/0/0
session idle bit 555,706 3 3
0/0/0/0/0
enqueue hash chains 84,192 2 2
0/2/0/0/0
post/wait queue 25,250 1 1
0/1/0/0/0
row cache enqueue latch 192,494 1 1
0/1/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: FIP Instance: fip Snaps: 2 -3
-> only latches with sleeps are shown
NoWait
Waiter
Latch Name Where Misses Sleeps
Sleeps
cache buffers chains kcbrls: kslbegin 0 8
0
cache buffers chains kcbgtcr: fast path 0 6
4
cache buffers chains kcbgtcr: kslbegin excl 0 2
9
cache buffers chains kcbchg: kslbegin: call CR 0 1
0
cache buffers chains kcbzib: finish free bufs 0 1
0
cache buffers lru chain kcbzgb: wait 0 7
0
cache buffers lru chain kcbzar: KSLNBEGIN 0 3
11
cache buffers lru chain kcbzgm 0 1
0
enqueue hash chains ksqrcl 0 2
0
library cache kglic 0 216
0
library cache kglhdgn: child: 0 106
206
library cache kglupc: child 0 68
167
library cache kglpnc: child 0 67
187
library cache kgllkdl: child: cleanup 0 44
0
library cache kglpndl: child: before pro 0 35
7
library cache kglpndl: child: after proc 0 18
0
library cache kglpnp: child 0 17
13
library cache kglobpn: child: 0 16
3
library cache kgldte: child 0 0 14
42
library cache kglhdgc: child: 0 8
0
library cache kglpin: child: heap proces 0 6
0
library cache pin kglpnc: child 0 1
1
library cache pin kglpndl 0 1
0
library cache pin kglpnp: child 0 1
0
library cache pin alloca kglpnal 0 3
3
library cache pin alloca kgllkdl 0 2
2
multiblock read objects kcbzib: mbr get 0 4
0
multiblock read objects kcbzib: normal mbr free 0 2
6
post/wait queue ksliwat:add:nowait 0 1
0
row cache enqueue latch kqreqd 0 1
0
session allocation ksuprc 0 7
0
session allocation ksudlc 0 5
0
session idle bit ksupuc: set busy 0 2
2
session idle bit ksupuc: clear busy 0 1
1
shared pool kghalo 0 10
0
shared pool kghfrunp: clatch: nowait 0 10
0
shared pool kghfen: not perm alloc cla 0 3
0
shared pool kghalp 0 1
0
-------------------------------------------------------------
Dictionary Cache Stats for DB: FIP Instance: fip Snaps: 2 -3
->"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
Cache Requests Miss Reqs Miss Reqs
Usage
dc_files 11 36.4 0 3
4
dc_free_extents 5 0.0 0 0
1
dc_histogram_data 703 0.0 0 0
181
dc_histogram_data_values 18 0.0 0 0
6
dc_histogram_defs 28,270 0.3 0 0
952
dc_object_ids 9,497 0.3 0 0
591
dc_objects 2,712 3.5 0 0
1,041
dc_profiles 528 0.0 0 0
1
dc_rollback_segments 110 0.0 0 0
12
dc_segments 6,686 0.3 0 0
365
dc_sequences 10 0.0 0 10
6
dc_tablespaces 7,981 0.0 0 2
5
dc_user_grants 26,456 0.0 0 0
28
dc_usernames 837 0.0 0 0
17
dc_users 40,129 0.0 0 0
42
-------------------------------------------------------------
Library Cache Activity for DB: FIP Instance: fip Snaps: 2 -3 ->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
BODY 1,591 0.1 1,593 0.2 2
0
CLUSTER 136 0.0 139 0.0 0
0
JAVA DATA 18 0.0 426 0.0 0
0
JAVA SOURCE 6 0.0 12 0.0 0
0
SQL AREA 65,391 5.4 557,226 1.3 183
0
TABLE/PROCEDURE 428,078 0.0 397,034 0.1 203
0
TRIGGER 4,894 0.0 4,894 0.2 7
0
-------------------------------------------------------------
Shared Pool Advisory for DB: FIP Instance: fip End Snap: 3
-> 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
Estd
Shared Pool SP Estd Estd Estd Lib LC Time
Size for Size Lib Cache Lib Cache Cache Time Saved Estd
Lib Cache
Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem
Obj Hits
----------- ----- ---------- ------------ ------------ -------
24 .5 26 6,077 785,385 1.0 50,589,884
32 .7 33 8,616 785,392 1.0 50,593,673
40 .8 41 10,993 785,404 1.0 50,599,845
48 1.0 49 12,662 785,452 1.0 50,625,000
56 1.2 57 14,335 785,454 1.0 50,627,184
64 1.3 65 16,016 785,454 1.0 50,627,970
72 1.5 73 17,700 785,454 1.0 50,628,103
80 1.7 81 19,382 785,454 1.0 50,628,161
88 1.8 89 21,080 785,454 1.0 50,628,268
96 2.0 96 22,657 785,457 1.0 50,628,818
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 25,165,824 Fixed Size 453,492 Redo Buffers 667,648 Variable Size 109,051,904 ---------------- sum 135,338,868 -------------------------------------------------------------
SGA breakdown difference for DB: FIP Instance: fip Snaps: 2 -3
Pool Name Begin value End value% Diff
------ ------------------------------ ---------------- ----------------
-------
java free memory 27,754,496 27,754,496
0.00
java memory in use 5,799,936 5,799,936
0.00
large free memory 8,388,608 8,388,608
0.00
shared 1M buffer 2,098,176 2,098,176
0.00
shared KGK heap 3,756 3,756
0.00
shared KGLS heap 1,228,760 1,288,516
4.86
shared KQR L SO 6,172 6,172
0.00
shared KQR M PO 1,522,276 1,525,364
0.20
shared KQR M SO 92,948 92,948
0.00
shared KQR S PO 116,852 117,876
0.88
shared KQR S SO 7,168 7,168
0.00
shared KSXR pending messages que 841,036 841,036
0.00
shared KSXR receive buffers 1,033,000 1,033,000
0.00
shared PL/SQL DIANA 1,040,848 1,123,052
7.90
shared PL/SQL MPCODE 4,995,696 5,169,340
3.48
shared PL/SQL PPCODE 13,760 13,760
0.00
shared PLS non-lib hp 2,068 2,068
0.00
shared dictionary cache 1,610,880 1,610,880
0.00
shared errors 142,672 157,668
10.51
shared event statistics per sess 1,718,360 1,718,360
0.00
shared fixed allocation callback 180 180
0.00
shared free memory 3,605,912 3,617,856
0.33
shared joxlod: in ehe 342,820 342,848
0.01
shared joxlod: in phe 2,964 2,964
0.00
shared joxlod: init s 5,084 5,084
0.00
shared joxs heap init 4,220 4,220
0.00
shared kgl simulator 3,140,324 3,140,324
0.00
shared library cache 15,691,376 15,641,920
-0.32
shared message pool freequeue 834,752 834,752
0.00
shared miscellaneous 8,762,808 8,815,528
0.60
shared parameters 569,320 615,804
8.16
shared sim memory hea 21,164 21,164
0.00
shared sql area 17,644,100 17,248,236
-2.24
shared table definiti 2,928 2,112
-27.87
shared trigger defini 4,616 4,896
6.07
shared trigger inform 1,172 1,140
-2.73
shared trigger source 696 696
0.00
buffer_cache 25,165,824 25,165,824
0.00
fixed_sga 453,492 453,492
0.00
log_buffer 656,384 656,384
0.00
-------------------------------------------------------------
init.ora Parameters for DB: FIP Instance: fip Snaps: 2 -3
End
value
Parameter Name Begin value (if
different)
aq_tm_processes 0
background_dump_dest C:\Oracle\admin\FIP\bdump
compatible 9.2.0.0.0
control_files C:\Oracle\oradata\FIP\CONTROL01.C
core_dump_dest C:\Oracle\admin\FIP\cdump
db_block_size 8192
db_cache_size 25165824
db_domain
db_file_multiblock_read_count 16
db_name FIP
fast_start_mttr_target 0
hash_join_enabled TRUE
instance_name FIP
java_pool_size 33554432
job_queue_processes 10
large_pool_size 8388608
open_cursors 300
pga_aggregate_target 268435456
processes 150
query_rewrite_enabled FALSE
remote_login_passwordfile EXCLUSIVE
shared_pool_size 50331648
sort_area_size 524288
star_transformation_enabled FALSE
timed_statistics TRUE
undo_management AUTO
undo_retention 10800
undo_tablespace UNDOTBS1
user_dump_dest C:\Oracle\admin\FIP\udump
-------------------------------------------------------------
End of Report Received on Fri Jan 19 2007 - 10:44:36 CST
 |
 |