oracle高手帮忙分析Statspack.
来源：互联网 发布：如何运行c语言代码 编辑：程序博客网 时间：2024/05/22 03:22
要求分析一个Statspack report，选出那些需要优化的sql, 但以前自己没做过，同事也没有做过，只能上网求助，只要求给出一个具体思路就可以了，不用具体帮助分析，谢谢。
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
SOS1P 3429568817 SOS1P 1 9.2.0.8.0 NO webprddbsa00
1
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 77104 03-Oct-10 16:45:03 434 12.4
End Snap: 77168 04-Oct-10 08:45:04 434 7.4
Elapsed: 960.02 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 4,096M Std Block Size: 8K
Shared Pool Size: 400M Log Buffer: 1,024K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 17,829.61 9,767.41
Logical reads: 34,187.78 18,728.72
Block changes: 92.91 50.90
Physical reads: 650.54 356.38
Physical writes: 18.89 10.35
User calls: 583.65 319.73
Parses: 66.39 36.37
Hard parses: 1.10 0.60
Sorts: 10.77 5.90
Logons: 0.02 0.01
Executes: 352.85 193.30
Transactions: 1.83
% Blocks changed per Read: 0.27 Recursive Call %: 31.07
Rollback per transaction %: 0.39 Rows per Sort: 1705.98
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.14 In-memory Sort %: 100.00
Library Hit %: 99.64 Soft Parse %: 98.34
Execute to Parse %: 81.18 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 23.19 % Non-Parse CPU: 99.70
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 97.09 77.30
% SQL with executions>1: 21.47 88.47
% Memory for SQL w/exec>1: 27.86 49.74
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 28,622 55.76
db file scattered read 2,347,812 13,242 25.80
db file sequential read 3,990,725 6,323 12.32
db file parallel write 22,905 1,520 2.96
log file sync 129,656 947 1.85
-------------------------------------------------------------
Wait Events for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
db file scattered read 2,347,812 0 13,242 6 22.3
db file sequential read 3,990,725 0 6,323 2 38.0
db file parallel write 22,905 0 1,520 66 0.2
log file sync 129,656 6 947 7 1.2
direct path read 35,726 0 163 5 0.3
direct path write 27,533 0 147 5 0.3
control file parallel write 19,359 0 119 6 0.2
buffer busy waits 3,425 53 76 22 0.0
direct path read (lob) 34,374 0 26 1 0.3
log file sequential read 1,007 0 24 24 0.0
latch free 2,528 1,894 21 8 0.0
SQL*Net more data to client 745,618 0 21 0 7.1
control file sequential read 23,364 0 21 1 0.2
async disk IO 1,931 0 13 7 0.0
log file parallel write 147,756 0 9 0 1.4
db file parallel read 594 0 5 8 0.0
log buffer space 27 0 4 162 0.0
direct path write (lob) 393 0 3 8 0.0
process startup 64 0 2 28 0.0
SQL*Net break/reset to clien 1,094 0 1 1 0.0
switch logfile command 4 0 1 358 0.0
enqueue 151 0 1 8 0.0
library cache pin 16 0 1 45 0.0
log file single write 20 0 0 24 0.0
db file single write 62 0 0 3 0.0
log file switch completion 2 0 0 53 0.0
row cache lock 115 0 0 1 0.0
control file single write 12 0 0 3 0.0
LGWR wait for redo copy 424 0 0 0 0.0
undo segment extension 891 890 0 0 0.0
library cache lock 1 0 0 1 0.0
buffer deadlock 4 4 0 0 0.0
SQL*Net message from client 30,219,756 0 3,851,602 127 287.4
jobq slave wait 1,386 1,333 4,007 2891 0.0
SQL*Net more data from clien 16,125 0 635 39 0.2
SQL*Net message to client 30,219,750 0 22 0 287.4
-------------------------------------------------------------
Background Wait Events for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 22,901 0 1,519 66 0.2
control file parallel write 19,242 0 118 6 0.2
log file sequential read 934 0 22 24 0.0
async disk IO 1,796 0 13 7 0.0
control file sequential read 17,079 0 11 1 0.2
log file parallel write 147,749 0 9 0 1.4
db file scattered read 219 0 1 7 0.0
db file sequential read 106 0 1 7 0.0
log file single write 20 0 0 24 0.0
direct path read 217 0 0 1 0.0
log buffer space 2 0 0 56 0.0
enqueue 3 0 0 37 0.0
direct path write 217 0 0 0 0.0
rdbms ipc reply 167 0 0 0 0.0
LGWR wait for redo copy 424 0 0 0 0.0
latch free 4 1 0 3 0.0
buffer busy waits 4 0 0 0 0.0
rdbms ipc message 497,143 115,190 490,685 987 4.7
pmon timer 19,193 19,193 56,210 2929 0.2
smon timer 300 158 54,819 ###### 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
12,469,772 4,156,505 3.0 0.6 8.00 302.75 317853294
select count(*) from dual
7,548,047 25,223 299.3 0.4 10.32 55.00 735294811
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
7,548,047 25,226 299.2 0.4 10.43 55.29 778384724
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
7,548,047 25,224 299.2 0.4 10.14 55.81 2222750186
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
7,548,047 25,223 299.3 0.4 9.91 54.92 2371500597
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
7,548,016 25,222 299.3 0.4 9.94 55.34 3027725634
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
7,547,759 25,222 299.3 0.4 9.78 55.31 1608766143
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
7,547,728 25,224 299.2 0.4 9.48 54.75 407518252
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
SQL ordered by Gets for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
put_map input_1,
set_input_map input_2,
7,547,471 25,225 299.2 0.4 10.64 56.80 2436847568
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
4,941,004 1 4,941,004.0 0.3 1254.69 5460.71 2279745364
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
NW_PRDT_PARA_TRANS',estimate_percent=>5,cascade=> true); END;
2,886,612 1,050 2,749.2 0.1 308.55 325.78 4129230265
begin GET_PROD_BUNDLE_MATCH(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); end
;
2,633,422 1,316,776 2.0 0.1 92.57 723.05 853199580
begin get_set_function_result(:1,:2,:3); end;
2,631,348 34,625 76.0 0.1 49.76 151.06 1965505712
SELECT * FROM MASTER_CD WHERE IS_VALID = 'Y' AND KEY_TY = 'ICONR
SN_CODE'
2,631,272 34,624 76.0 0.1 22.81 93.90 49764941
SELECT CD,IS_VALID FROM MASTER_CD WHERE KEY_TY='CAM_DIVERT'
2,138,735 1 2,138,735.0 0.1 579.20 2225.03 639677948
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
NW_PRDT_TRANS',estimate_percent=>5,cascade=> true); END;
1,676,408 22,059 76.0 0.1 14.90 57.64 1922634261
SELECT DECODE FROM MASTER_CD WHERE KEY_TY = :1 AND SUPP_CD = '1'
1,559,292 20,518 76.0 0.1 13.28 55.48 4079928138
SELECT cd, decode FROM MASTER_CD WHERE key_ty = 'BCP_BANNER_MESS
AGE'
1,422,188 18,715 76.0 0.1 10.75 49.54 1076266187
SELECT cd, decode FROM MASTER_CD WHERE key_ty = 'CUST_IDENTIFICA
TION'
948,049 1 948,049.0 0.0 1033.60 1840.43 2386826238
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
ORDER_INFO',cascade=> true,method_opt=>'FOR columns flow_through
_ind,order_status,ws_consumer_ind size 254'); END;
SQL ordered by Gets for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
872,275 1 872,275.0 0.0 49.76 339.21 1823456512
Module: SQL*Plus
SELECT DISTINCT oi.order_no||','||woi.work_order_no||','||mc.dec
ode||','||to_char(oi.created_dt, 'DD-Mon-YYYY HH24:MI:SS')||','|
|oi.dealer_cd||','||p.product_ty||','||oi.created_by||','||su.ch
annel_ty||','||su.company_name||','||ci.first_nm||','||ci.last_n
-------------------------------------------------------------
SQL ordered by Reads for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
9,500,774 1 9,500,774.0 25.4 1254.69 5460.71 2279745364
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
NW_PRDT_PARA_TRANS',estimate_percent=>5,cascade=> true); END;
4,079,700 1 4,079,700.0 10.9 579.20 2225.03 639677948
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
NW_PRDT_TRANS',estimate_percent=>5,cascade=> true); END;
911,638 1 911,638.0 2.4 154.24 540.04 2278949785
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
TXN_COMP_MAP',estimate_percent=>5,cascade=> true); END;
846,789 1 846,789.0 2.3 1033.60 1840.43 2386826238
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
ORDER_INFO',cascade=> true,method_opt=>'FOR columns flow_through
_ind,order_status,ws_consumer_ind size 254'); END;
769,124 1 769,124.0 2.1 49.76 339.21 1823456512
Module: SQL*Plus
SELECT DISTINCT oi.order_no||','||woi.work_order_no||','||mc.dec
ode||','||to_char(oi.created_dt, 'DD-Mon-YYYY HH24:MI:SS')||','|
|oi.dealer_cd||','||p.product_ty||','||oi.created_by||','||su.ch
annel_ty||','||su.company_name||','||ci.first_nm||','||ci.last_n
m||','||ecaf.cust_acct_no||','||ecaf.cust_first_nm||','||replace
388,030 1 388,030.0 1.0 56.86 223.40 1119457860
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
WORK_ORDER_INFO',estimate_percent=>5,cascade=> true); END;
370,845 1 370,845.0 1.0 101.01 188.39 4025772863
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
SQ',estimate_percent=>5,cascade=> true); END;
265,609 1 265,609.0 0.7 60.59 172.23 4178106433
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
STATUS_CHECK',estimate_percent=>5,cascade=> true); END;
252,371 1 252,371.0 0.7 38.58 148.96 2674790521
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
TXN_COMP_MAP_CS_JOIN',estimate_percent=>5,cascade=> true); END;
250,829 1 250,829.0 0.7 53.07 143.64 3802347009
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
PREPAID_MOB_INSTALLATION',estimate_percent=>5,cascade=> true); E
SQL ordered by Reads for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
ND;
217,464 1 217,464.0 0.6 29.40 117.42 176192660
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
SQ_FIELD',estimate_percent=>5,cascade=> true); END;
182,594 1 182,594.0 0.5 32.24 104.65 751678291
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
SQ_JOIN',estimate_percent=>5,cascade=> true); END;
167,751 1 167,751.0 0.4 38.69 150.87 3613320745
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
TRANSACTIONS',estimate_percent=>5,cascade=> true); END;
153,776 1 153,776.0 0.4 38.44 95.01 1746288758
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
SQ_WS_JOIN',estimate_percent=>5,cascade=> true); END;
126,263 1 126,263.0 0.3 40.42 133.02 641027720
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
POSTPAID_MOB_INSTALLATION',estimate_percent=>5,cascade=> true);
END;
99,319 1 99,319.0 0.3 15.85 62.91 2973488363
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
RESIDENTIAL_CONTACT_DETAILS',estimate_percent=>5,cascade=> true)
; END;
91,560 1 91,560.0 0.2 9.98 51.50 1624649910
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
ORDER_INFO_JOIN',estimate_percent=>5,cascade=> true); END;
86,936 2 43,468.0 0.2 34.38 161.85 2209027148
Module: SQL*Plus
SELECT /*+ index(T TRA_ORD_CD) */ COUNT(1) FROM ORDER_INFO OI, T
RANSACTIONS T, PACKAGES P WHERE CREATED_DT BETWEEN TRUNC(:B1 ) A
ND :B1 AND FLOW_THROUGH_IND <> 9 AND OI.ORDER_NO = T.ORDER_NO AN
D T.PACKAGE_CD = P.PACKAGE_CD AND P.PRODUCT_TY = 'MOB'
80,218 1 80,218.0 0.2 13.72 51.83 1484162511
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
ORDER_NOTES',estimate_percent=>5,cascade=> true); END;
39,113 1 39,113.0 0.1 9.72 26.94 4070696616
Module: SQL*Plus
BEGIN dbms_stats.gather_table_stats(ownname=>'SOS_OC',tabname=>'
SQL ordered by Reads for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
PORT_TRANSACTION',estimate_percent=>5,cascade=> true); END;
27,558 1 27,558.0 0.1 1.49 14.13 772902781
Module: SQL*Plus
select distinct 'D,'||oi.order_no||','||to_char (oi.created_dt,
'YYYY-MM-DD HH24:MI:SS')||','||oi.sales_rep_id||','||oi.dealer_c
d||','||ci.title||','||ci.first_nm||','||ci.last_nm||','||pmi.po
rt_in_msn||','||pmi.plan_contract||','||mc_ot.decode||','||woi.w
ork_order_no||','||woi.product_ty||','||woi.flow_through_status|
27,558 1 27,558.0 0.1 1.49 11.82 2713496816
Module: SQL*Plus
select distinct 'D,'||oi.order_no||','||to_char (oi.created_dt,
'YYYY-MM-DD HH24:MI:SS')||','||oi.sales_rep_id||','||oi.dealer_c
d||','||ci.title||','||ci.first_nm||','||ci.last_nm||','||pmi.po
rt_in_msn||','||pmi.plan_contract||','||mc_ot.decode||','||woi.w
ork_order_no||','||woi.product_ty||','||woi.flow_through_status|
27,558 1 27,558.0 0.1 1.49 12.12 2995689359
Module: SQL*Plus
select distinct 'D,'||oi.order_no||','||to_char (oi.created_dt,
'YYYY-MM-DD HH24:MI:SS')||','||oi.sales_rep_id||','||oi.dealer_c
d||','||ci.title||','||ci.first_nm||','||ci.last_nm||','||pmi.po
rt_in_msn||','||pmi.plan_contract||','||mc_ot.decode||','||woi.w
-------------------------------------------------------------
SQL ordered by Executions for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
4,156,505 4,156,137 1.0 0.00 0.00 317853294
select count(*) from dual
1,316,776 1,316,590 1.0 0.00 0.00 853199580
begin get_set_function_result(:1,:2,:3); end;
188,233 188,233 1.0 0.00 0.00 3874482373
begin GET_COMP_DETAILS(:1,:2); end;
82,923 82,923 1.0 0.00 0.00 1805284188
begin GET_COMP_BY_SCN_TY(:1,:2,:3);end;
81,224 81,212 1.0 0.00 0.00 4102711756
begin GET_NW_PRDT_PARA (:1,:2,:3); end;
70,290 70,290 1.0 0.00 0.00 3716207873
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,
order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:
1
60,448 60,448 1.0 0.00 0.00 735563736
begin GET_NW_PRDT_LIST (:1,:2); end;
60,357 60,357 1.0 0.00 0.00 1018231328
begin GET_NW_PRDT_PARA_LIST (:1,:2); end;
37,259 37,259 1.0 0.00 0.00 828919034
begin SELECT_MOB_GENERIC_VAS(:1,:2,:3,:4,:5); end;
36,665 36,665 1.0 0.00 0.00 2690751713
begin SELECT_ALL_MOB_NPP(:1,:2,:3,:4,:5,:6,:7,:8); end;
34,625 173,115 5.0 0.00 0.00 1965505712
SELECT * FROM MASTER_CD WHERE IS_VALID = 'Y' AND KEY_TY = 'ICONR
SN_CODE'
34,624 34,622 1.0 0.00 0.00 49764941
SELECT CD,IS_VALID FROM MASTER_CD WHERE KEY_TY='CAM_DIVERT'
29,848 29,848 1.0 0.00 0.00 2766255915
begin SELECT_MOB_NPP(:1,:2,:3,:4,:5,:6,:7,:8); end;
25,226 0 0.0 0.00 0.00 778384724
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
25,225 0 0.0 0.00 0.00 2436847568
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
SQL ordered by Executions for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
put_map input_1,
set_input_map input_2,
25,224 0 0.0 0.00 0.00 407518252
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
25,224 0 0.0 0.00 0.00 2222750186
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
25,223 0 0.0 0.00 0.00 735294811
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
25,223 0 0.0 0.00 0.00 2371500597
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
25,222 0 0.0 0.00 0.00 1608766143
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
25,222 0 0.0 0.00 0.00 3027725634
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
22,059 -24 -0.0 0.00 0.00 1922634261
SELECT DECODE FROM MASTER_CD WHERE KEY_TY = :1 AND SUPP_CD = '1'
20,518 41,034 2.0 0.00 0.00 4079928138
SELECT cd, decode FROM MASTER_CD WHERE key_ty = 'BCP_BANNER_MESS
AGE'
19,285 19,285 1.0 0.00 0.00 2858754903
begin GET_BO_GROUP_RULES(:1,:2); end;
SQL ordered by Executions for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
18,715 74,852 4.0 0.00 0.00 1076266187
SELECT cd, decode FROM MASTER_CD WHERE key_ty = 'CUST_IDENTIFICA
TION'
17,399 17,399 1.0 0.00 0.00 2598786151
begin SELECT_MOB_COMP_NPP(:1,:2,:3,:4); end;
-------------------------------------------------------------
SQL ordered by Parse Calls for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
70,290 70,290 1.84 3716207873
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,
order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:
1
25,225 25,224 0.66 407518252
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
25,225 25,223 0.66 735294811
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
25,225 25,226 0.66 778384724
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
25,225 25,222 0.66 1608766143
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
25,225 25,224 0.66 2222750186
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
25,225 25,223 0.66 2371500597
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
25,225 25,225 0.66 2436847568
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
SQL ordered by Parse Calls for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
25,225 25,222 0.66 3027725634
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,
input_1.CODE_ID as CODE_VALUE_1,
input_2.CODE_ID as CODE_VALUE_2,
om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,
20,931 188,233 0.55 3874482373
begin GET_COMP_DETAILS(:1,:2); end;
20,519 20,518 0.54 4079928138
SELECT cd, decode FROM MASTER_CD WHERE key_ty = 'BCP_BANNER_MESS
AGE'
19,830 37,259 0.52 828919034
begin SELECT_MOB_GENERIC_VAS(:1,:2,:3,:4,:5); end;
18,710 18,715 0.49 1076266187
SELECT cd, decode FROM MASTER_CD WHERE key_ty = 'CUST_IDENTIFICA
TION'
17,875 82,923 0.47 1805284188
begin GET_COMP_BY_SCN_TY(:1,:2,:3);end;
17,863 19,285 0.47 2858754903
begin GET_BO_GROUP_RULES(:1,:2); end;
16,054 36,665 0.42 2690751713
begin SELECT_ALL_MOB_NPP(:1,:2,:3,:4,:5,:6,:7,:8); end;
15,741 16,489 0.41 93449598
begin select_handset_delphi(:1,:2,:3,:4,:5,:6,:7); end;
15,321 29,848 0.40 2766255915
begin SELECT_MOB_NPP(:1,:2,:3,:4,:5,:6,:7,:8); end;
13,250 1,316,776 0.35 853199580
begin get_set_function_result(:1,:2,:3); end;
13,049 60,448 0.34 735563736
begin GET_NW_PRDT_LIST (:1,:2); end;
12,179 17,399 0.32 2598786151
begin SELECT_MOB_COMP_NPP(:1,:2,:3,:4); end;
12,005 13,779 0.31 790736214
begin SELECT_ALL_MOB_BO(:1,:2,:3,:4,:5,:6,:7); end;
9,611 10,398 0.25 1752178925
begin SELECT_MOB_BO(:1,:2,:3,:4,:5,:6,:7); end;
9,340 16,214 0.24 736207242
begin SELECT_ALL_MOB_PLANS(:1,:2,:3,:4,:5,:6,:7,:8); end;
SQL ordered by Parse Calls for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
8,911 8,928 0.23 2892668502
begin GET_BILL_INST_PRDT_CS(:1,:2); end;
8,910 8,926 0.23 2985786962
begin GET_BILL_INST_PRDT (:1, :2); end;
8,622 14,844 0.23 2419289011
begin SELECT_RECON_ACTIVE_PLANS(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10);
end;
-------------------------------------------------------------
SQL ordered by Sharable Memory for DB: SOS1P Instance: SOS1P Snaps: 77104 -771
-> End Sharable Memory Threshold: 1048576
Sharable Mem (b) Executions % Total Hash Value
---------------- ------------ ------- ------------
1,604,009 1 0.2 214471873
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t
1,553,323 1 0.2 296774157
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t
1,485,727 2 0.2 2599403129
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t
1,483,720 1 0.2 4195789046
Module: SQL*Plus
select distinct 'D,'||oi.order_no||','||to_char (oi.created_dt,
'YYYY-MM-DD HH24:MI:SS')||','||oi.sales_rep_id||','||oi.dealer_c
d||','||ci.title||','||ci.first_nm||','||ci.last_nm||','||pmi.ms
n||','||pmi.plan_contract||','||mc_ot.decode||','||replace(oi.no
n_flow_through_reason, ',', '')||','||mc_nft.decode||','||woi.wo
1,463,816 1 0.2 712903891
Module: SQL*Plus
select distinct 'D,'||oi.order_no||','||to_char (oi.created_dt,
'YYYY-MM-DD HH24:MI:SS')||','||oi.sales_rep_id||','||oi.dealer_c
d||','||ci.title||','||ci.first_nm||','||ci.last_nm||','||pmi.ms
n||','||pmi.plan_contract||','||mc_ot.decode||','||replace(oi.no
n_flow_through_reason, ',', '')||','||mc_nft.decode||','||woi.wo
1,425,905 2 0.2 1918501853
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t
1,411,903 1 0.2 1747012080
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t
1,380,439 1 0.2 496056839
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
SQL ordered by Sharable Memory for DB: SOS1P Instance: SOS1P Snaps: 77104 -771
-> End Sharable Memory Threshold: 1048576
Sharable Mem (b) Executions % Total Hash Value
---------------- ------------ ------- ------------
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t
1,355,888 1 0.2 815230696
Module: SQL*Plus
select distinct 'D,'||oi.order_no||','||to_char (oi.created_dt,
'YYYY-MM-DD HH24:MI:SS')||','||oi.sales_rep_id||','||oi.dealer_c
d||','||ci.title||','||ci.first_nm||','||ci.last_nm||','||pmi.ms
n||','||pmi.plan_contract||','||mc_ot.decode||','||replace(oi.no
n_flow_through_reason, ',', '')||','||mc_nft.decode||','||woi.wo
1,346,496 1 0.2 155601975
Module: SQL*Plus
select distinct 'D,'||oi.order_no||','||to_char (oi.created_dt,
'YYYY-MM-DD HH24:MI:SS')||','||oi.sales_rep_id||','||oi.dealer_c
d||','||ci.title||','||ci.first_nm||','||ci.last_nm||','||pmi.ms
n||','||pmi.plan_contract||','||mc_ot.decode||','||replace(oi.no
n_flow_through_reason, ',', '')||','||mc_nft.decode||','||woi.wo
1,331,831 1 0.2 3128546817
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t
1,331,767 1 0.2 3096102258
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t
1,309,685 1 0.2 1378693305
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
-------------------------------------------------------------
Instance Activity Stats for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 2,862,236 49.7 27.2
CPU used when call started 2,833,414 49.2 27.0
CR blocks created 16,229 0.3 0.2
Cached Commit SCN referenced 0 0.0 0.0
Commit SCN cached 0 0.0 0.0
DBWR buffers scanned 1,294,691 22.5 12.3
DBWR checkpoint buffers written 175,972 3.1 1.7
DBWR checkpoints 8 0.0 0.0
DBWR free buffers found 1,276,082 22.2 12.1
DBWR lru scans 5,261 0.1 0.1
DBWR make free requests 7,274 0.1 0.1
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 1,294,691 22.5 12.3
DBWR transaction table writes 262 0.0 0.0
DBWR undo block writes 52,697 0.9 0.5
OS Integral shared text size 0 0.0 0.0
OS Integral unshared data size 0 0.0 0.0
OS Involuntary context switches 0 0.0 0.0
OS Maximum resident set size 0 0.0 0.0
OS Page faults 0 0.0 0.0
OS Page reclaims 0 0.0 0.0
OS Socket messages received 0 0.0 0.0
OS System time used 0 0.0 0.0
OS User time used 0 0.0 0.0
OS Voluntary context switches 0 0.0 0.0
SQL*Net roundtrips to/from client 30,218,589 524.6 287.4
active txn count during cleanout 39,294 0.7 0.4
background checkpoints completed 8 0.0 0.0
background checkpoints started 8 0.0 0.0
background timeouts 127,691 2.2 1.2
branch node splits 36 0.0 0.0
buffer is not pinned count 888,123,691 15,418.6 8,446.6
buffer is pinned count 1,301,136,648 22,588.8 12,374.6
bytes received via SQL*Net from c 1,392,231,652 24,170.3 13,240.9
bytes sent via SQL*Net to client 7,955,117,494 138,107.3 75,657.8
calls to get snapshot scn: kcmgss 46,683,311 810.5 444.0
calls to kcmgas 1,520,117 26.4 14.5
calls to kcmgcs 18,494 0.3 0.2
change write time 3,376 0.1 0.0
cleanout - number of ktugct calls 52,014 0.9 0.5
cluster key scan block gets 790,988 13.7 7.5
cluster key scans 552,202 9.6 5.3
commit cleanout failures: block l 28 0.0 0.0
commit cleanout failures: buffer 19 0.0 0.0
commit cleanout failures: callbac 3,120 0.1 0.0
commit cleanout failures: cannot 55 0.0 0.0
commit cleanout failures: hot bac 0 0.0 0.0
commit cleanouts 742,653 12.9 7.1
commit cleanouts successfully com 739,431 12.8 7.0
commit txn count during cleanout 133,561 2.3 1.3
consistent changes 61,151 1.1 0.6
consistent gets 1,961,511,022 34,053.4 18,655.1
consistent gets - examination 888,916,120 15,432.3 8,454.1
current blocks converted for CR 1 0.0 0.0
cursor authentications 3,808 0.1 0.0
data blocks consistent reads - un 54,177 0.9 0.5
Instance Activity Stats for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
db block changes 5,351,540 92.9 50.9
db block gets 7,773,807 135.0 73.9
deferred (CURRENT) block cleanout 253,236 4.4 2.4
dirty buffers inspected 9,478 0.2 0.1
enqueue conversions 59,908 1.0 0.6
enqueue deadlocks 0 0.0 0.0
enqueue releases 770,116 13.4 7.3
enqueue requests 770,122 13.4 7.3
enqueue timeouts 6 0.0 0.0
enqueue waits 151 0.0 0.0
exchange deadlocks 4 0.0 0.0
execute count 20,324,709 352.9 193.3
failed probes on index block recl 45 0.0 0.0
free buffer inspected 10,048 0.2 0.1
free buffer requested 36,628,640 635.9 348.4
hot buffers moved to head of LRU 682,027 11.8 6.5
immediate (CR) block cleanout app 25,561 0.4 0.2
immediate (CURRENT) block cleanou 62,488 1.1 0.6
index crx upgrade (found) 1 0.0 0.0
index crx upgrade (positioned) 89,247 1.6 0.9
index fast full scans (full) 305,590 5.3 2.9
index fetch by key 482,462,034 8,375.9 4,588.5
index scans kdiixs1 286,836,193 4,979.7 2,728.0
leaf node 90-10 splits 3,715 0.1 0.0
leaf node splits 8,994 0.2 0.1
logons cumulative 1,199 0.0 0.0
messages received 387,021 6.7 3.7
messages sent 387,021 6.7 3.7
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 1,044,129,069 18,126.9 9,930.3
opened cursors cumulative 3,812,569 66.2 36.3
parse count (failures) 12 0.0 0.0
parse count (hard) 63,535 1.1 0.6
parse count (total) 3,824,150 66.4 36.4
parse time cpu 8,640 0.2 0.1
parse time elapsed 37,262 0.7 0.4
physical reads 37,471,980 650.5 356.4
physical reads direct 879,859 15.3 8.4
physical reads direct (lob) 34,536 0.6 0.3
physical writes 1,087,859 18.9 10.4
physical writes direct 883,295 15.3 8.4
physical writes direct (lob) 504 0.0 0.0
physical writes non checkpoint 984,224 17.1 9.4
pinned buffers inspected 409 0.0 0.0
prefetch clients - default 0 0.0 0.0
prefetched blocks 30,219,560 524.6 287.4
prefetched blocks aged out before 8,672,390 150.6 82.5
process last non-idle time 57,601 1.0 0.6
recursive aborts on index block r 0 0.0 0.0
recursive calls 15,150,138 263.0 144.1
recursive cpu usage 499,311 8.7 4.8
redo blocks written 2,147,328 37.3 20.4
redo buffer allocation retries 29 0.0 0.0
redo entries 2,761,519 47.9 26.3
redo log space requests 2 0.0 0.0
redo log space wait time 11 0.0 0.0
Instance Activity Stats for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
redo ordering marks 0 0.0 0.0
redo size 1,027,003,568 17,829.6 9,767.4
redo synch time 97,277 1.7 0.9
redo synch writes 129,681 2.3 1.2
redo wastage 37,903,752 658.0 360.5
redo write time 95,821 1.7 0.9
redo writer latching time 4 0.0 0.0
redo writes 147,745 2.6 1.4
rollback changes - undo records a 670 0.0 0.0
rows fetched via callback 143,550,595 2,492.2 1,365.3
session connect time 0 0.0 0.0
session logical reads 1,969,250,270 34,187.8 18,728.7
session pga memory max 453,095,520 7,866.1 4,309.2
session uga memory 274,798,883,888 4,770,731.1 2,613,498.2
session uga memory max 1,475,356,432 25,613.4 14,031.5
shared hash latch upgrades - no w 479,034 8.3 4.6
shared hash latch upgrades - wait 29 0.0 0.0
sorts (disk) 14 0.0 0.0
sorts (memory) 620,366 10.8 5.9
sorts (rows) 1,058,357,733 18,374.0 10,065.6
summed dirty queue length 28,560 0.5 0.3
switch current to new buffer 7,395 0.1 0.1
table fetch by rowid 623,218,462 10,819.6 5,927.2
table fetch continued row 99,206 1.7 0.9
table scan blocks gotten 186,763,404 3,242.4 1,776.2
table scan rows gotten 13,787,178,250 239,356.6 131,124.1
table scans (cache partitions) 0 0.0 0.0
table scans (long tables) 201 0.0 0.0
table scans (rowid ranges) 0 0.0 0.0
table scans (short tables) 10,871,332 188.7 103.4
transaction rollbacks 65 0.0 0.0
transaction tables consistent rea 10 0.0 0.0
transaction tables consistent rea 5,070 0.1 0.1
user calls 33,618,769 583.7 319.7
user commits 104,733 1.8 1.0
user rollbacks 413 0.0 0.0
workarea executions - multipass 0 0.0 0.0
workarea executions - onepass 36 0.0 0.0
workarea executions - optimal 2,221,525 38.6 21.1
write clones created in backgroun 1 0.0 0.0
write clones created in foregroun 552 0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SOS_OC_DATA
4,556,338 79 2.5 5.5 19,124 0 2,711 6.9
SOS_OC_INDX
1,757,109 31 4.8 6.4 56,544 1 547 106.8
STATSPACK
29,407 1 10.5 3.1 75,293 1 0 0.0
TEMP
45,222 1 9.6 19.5 40,295 1 0 0.0
UNDO_01
844 0 6.2 1.0 53,032 1 126 0.2
SYSTEM
24,082 0 3.9 1.2 438 0 39 9.5
TOOLS
8,302 0 2.3 1.0 9 0 0 0.0
USERS
544 0 3.6 1.0 40 0 0 0.0
SPOTLIGHT
10 0 34.0 1.0 7 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SOS_OC_DATA /sos/db04/oracle/SOS1P/oradata/SOS1P_sos_oc_data_01.
801,663 14 2.6 6.0 360 0 226 5.7
/sos/db04/oracle/SOS1P/oradata/SOS1P_sos_oc_data_02.
772,414 13 3.6 5.8 84 0 309 4.9
/sos/db04/oracle/SOS1P/oradata/SOS1P_sos_oc_data_03.
557,191 10 2.5 5.8 31 0 235 9.3
/sos/db06/oracle/SOS1P/oradata/SOS1P_temp_04.dbf
825,204 14 2.1 5.4 63 0 359 6.2
/sos/db08/oracle/SOS1P/oradata/SOS1P_sos_oc_data_05.
704,091 12 2.1 5.2 3,793 0 735 7.3
/sos/db09/oracle/SOS1P/oradata/SOS1P_sos_oc_data_06.
696,148 12 2.1 5.2 7,568 0 666 7.5
/sos/db09/oracle/SOS1P/oradata/SOS1P_sos_oc_data_07.
199,627 3 2.0 4.5 7,225 0 181 6.3
SOS_OC_INDX /sos/db04/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_05.
72,544 1 4.2 5.6 820 0 24 5.0
/sos/db05/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_01.
216,943 4 4.0 5.1 631 0 11 10.0
/sos/db05/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_02.
167,536 3 4.2 6.4 716 0 26 9.2
/sos/db05/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_03.
131,515 2 5.2 7.8 927 0 23 6.1
/sos/db05/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_04.
72,338 1 5.0 5.7 772 0 127 448.7
/sos/db06/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_06.
156,953 3 4.8 6.7 3,650 0 42 3.1
/sos/db06/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_07.
163,273 3 4.7 6.5 3,676 0 48 9.0
/sos/db06/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_08.
120,676 2 4.6 6.6 1,733 0 0
/sos/db07/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_09.
157,825 3 4.9 6.6 2,528 0 39 4.1
/sos/db07/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_10.
156,608 3 4.9 6.5 6,377 0 52 1.3
/sos/db07/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_11.
146,876 3 5.0 6.9 7,346 0 16 0.6
/sos/db07/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_12.
74,731 1 4.8 6.5 9,065 0 9 0.0
/sos/db08/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_13.
70,860 1 6.3 6.8 5,525 0 44 0.7
/sos/db08/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_14.
25,076 0 7.3 7.2 8,769 0 61 0.2
/sos/db08/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_15.
23,355 0 6.8 6.6 4,009 0 25 0.4
SPOTLIGHT /sos/db07/oracle/SOS1P/oradata/SOS1P_spotlight_01.db
10 0 34.0 1.0 7 0 0
STATSPACK /sos/db05/oracle/SOS1P/oradata/SOS1P_statspack_01.db
25,909 0 11.0 3.2 65,673 1 0
/sos/db06/oracle/SOS1P/oradata/SOS1P_statspack_02.db
File IO Stats for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
3,246 0 6.6 2.4 8,662 0 0
STATSPACK /sos/db08/oracle/SOS1P/oradata/SOS1P_statspack_03.db
252 0 13.1 4.8 958 0 0
SYSTEM /sos/db04/oracle/SOS1P/oradata/SOS1P_system_01.dbf
24,082 0 3.9 1.2 438 0 39 9.5
TEMP /sos/db04/oracle/SOS1P/oradata/SOS1P_temp_01.dbf
10,006 0 7.8 16.3 6,400 0 0
/sos/db04/oracle/SOS1P/oradata/SOS1P_temp_03.dbf
8,981 0 8.0 17.0 9,006 0 0
/sos/db05/oracle/SOS1P/oradata/SOS1P_temp_02.dbf
10 0 3.0 1.0 30 0 0
/sos/db07/oracle/SOS1P/oradata/SOS1P_temp_04.dbf
26,225 0 10.9 21.5 24,859 0 0
TOOLS /sos/db04/oracle/SOS1P/oradata/SOS1P_tools_01.dbf
8,302 0 2.3 1.0 9 0 0
UNDO_01 /sos/db04/oracle/SOS1P/oradata/SOS1P_undo_01_01.dbf
804 0 5.8 1.0 19,757 0 78 0.4
/sos/db05/oracle/SOS1P/oradata/SOS1P_undo_01_02.dbf
40 0 14.3 1.0 33,275 1 48 0.0
USERS /sos/db04/oracle/SOS1P/oradata/SOS1P_users_01.dbf
544 0 3.6 1.0 40 0 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free Write Buffer
Number of Cache Buffer Physical Physical Buffer Complete Busy
P Buffers Hit % Gets Reads Writes Waits Waits Waits
--- ---------- ----- ----------- ----------- ---------- ------- -------- ------
D 508,160 98.1############ 36,557,552 204,118 0 0 3,425
-------------------------------------------------------------
Instance Recovery Stats for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- ---------- ---------- ---------- ---------- ----------
B 180 49 5766 88707 943713 943713
E 180 60 7622 89673 943713 943713
-------------------------------------------------------------
Buffer Pool Advisory for DB: SOS1P Instance: SOS1P End Snap: 77168
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate (default block size first)
Size for Size Buffers for Est Physical Estimated
P Estimate (M) Factr Estimate Read Factor Physical Reads
--- ------------ ----- ---------------- ------------- ------------------
D 416 .1 51,610 0.10 1,576,800
D 832 .2 103,220 3.44 52,440,948
D 1,248 .3 154,830 2.49 37,960,896
D 1,664 .4 206,440 1.95 29,704,367
D 2,080 .5 258,050 1.65 25,196,860
D 2,496 .6 309,660 1.45 22,091,402
D 2,912 .7 361,270 1.30 19,768,753
D 3,328 .8 412,880 1.17 17,888,788
D 3,744 .9 464,490 1.07 16,340,988
D 4,096 1.0 508,160 1.00 15,265,011
D 4,160 1.0 516,100 0.99 15,090,865
D 4,576 1.1 567,710 0.92 14,073,019
D 4,992 1.2 619,320 0.87 13,227,788
D 5,408 1.3 670,930 0.82 12,520,893
D 5,824 1.4 722,540 0.78 11,921,248
D 6,240 1.5 774,150 0.75 11,404,252
D 6,656 1.6 825,760 0.72 10,948,865
D 7,072 1.7 877,370 0.69 10,549,118
D 7,488 1.8 928,980 0.67 10,182,394
D 7,904 1.9 980,590 0.65 9,845,976
D 8,320 2.0 1,032,200 0.62 9,418,624
-------------------------------------------------------------
Buffer wait Statistics for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block 3,296 77 23
undo header 54 0 1
undo block 72 0 0
1st level bmb 2 0 0
-------------------------------------------------------------
PGA Aggr Target Stats for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> 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
--------------- ---------------- -------------------------
99.3 898,758 6,617
%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 3,000 2,444 375.2 0.0 .0 .0 .0 102,400
E 3,000 2,534 253.1 0.0 .0 100.0 .0 102,400
-------------------------------------------------------------
PGA Aggr Target Histogram for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 677,485 677,485 0 0
4K 8K 12,336 12,336 0 0
8K 16K 120,182 120,182 0 0
16K 32K 98,287 98,287 0 0
32K 64K 84,586 84,586 0 0
64K 128K 87,301 87,301 0 0
128K 256K 43,054 43,054 0 0
256K 512K 5,532 5,532 0 0
512K 1024K 1,056,268 1,056,268 0 0
1M 2M 33,050 33,050 0 0
2M 4M 2,870 2,870 0 0
4M 8M 284 283 1 0
8M 16M 162 160 2 0
16M 32M 86 78 8 0
32M 64M 29 22 7 0
64M 128M 16 11 5 0
128M 256M 2 0 2 0
256M 512M 7 0 7 0
512M 1024M 4 0 4 0
-------------------------------------------------------------
PGA Memory Advisory for DB: SOS1P Instance: SOS1P End Snap: 77168
-> 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
---------- ------- ---------------- ---------------- -------- ----------
375 0.1 1,040,716,662.0 81,294,860.3 93.0 1,587,174
750 0.3 1,040,716,662.0 18,838,834.9 98.0 601
1,500 0.5 1,040,716,662.0 16,792,260.9 98.0 0
2,250 0.8 1,040,716,662.0 15,794,998.8 99.0 0
3,000 1.0 1,040,716,662.0 12,819,940.0 99.0 0
3,600 1.2 1,040,716,662.0 11,740,340.3 99.0 0
4,200 1.4 1,040,716,662.0 11,740,244.0 99.0 0
4,800 1.6 1,040,716,662.0 11,740,244.0 99.0 0
5,400 1.8 1,040,716,662.0 11,740,244.0 99.0 0
6,000 2.0 1,040,716,662.0 11,740,244.0 99.0 0
9,000 3.0 1,040,716,662.0 11,740,244.0 99.0 0
12,000 4.0 1,040,716,662.0 11,740,244.0 99.0 0
18,000 6.0 1,040,716,662.0 11,740,244.0 99.0 0
24,000 8.0 1,040,716,662.0 11,740,244.0 99.0 0
-------------------------------------------------------------
Enqueue activity for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> Enqueue stats gathered prior to 9i should not be compared with 9i data
-> ordered by Wait Time desc, Waits desc
Avg Wt Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s)
-- ------------ ------------ ----------- ----------- ------------- ------------
TX 191,027 191,030 0 147 7.31 1
CF 27,757 27,756 1 4 28.75 0
-------------------------------------------------------------
Rollback Segment Stats for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
->A high value for "Pct Waits" suggests more rollback segments may be required
->RBS stats may not be accurate between begin and end snaps when using Auto Undo
managment, as RBS may be dynamically created and dropped as needed
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ -------------- ------- --------------- -------- -------- --------
0 298.0 0.00 468 0 0 0
1 320,401.0 0.00 183,063,268 43 15 12
2 45,503.0 0.02 128,756,466 96 17 67
3 12,059.0 0.00 7,288,126 13 2 0
4 10,605.0 0.01 6,238,388 11 3 4
5 11,620.0 0.00 9,517,408 15 1 0
6 10,236.0 0.00 5,868,316 11 1 0
7 9,556.0 0.00 4,823,938 8 1 0
8 10,697.0 0.00 5,610,176 8 1 0
9 11,138.0 0.00 6,353,202 12 2 0
10 10,327.0 0.00 5,090,400 4 1 2
-------------------------------------------------------------
Rollback Segment Storage for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
->Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 385,024 63,527 385,024
1 31,580,160 3,679,709 3,195,133,952
2 2,220,032 90,215,516 770,826,240
3 2,220,032 765,513 822,206,464
4 9,560,064 832,102 897,703,936
5 2,220,032 1,560,491 755,097,600
6 2,220,032 660,025 973,201,408
7 2,220,032 738,890 788,652,032
8 2,220,032 726,310 838,983,680
9 2,220,032 694,533 637,657,088
10 9,560,064 1,129,171 822,206,464
-------------------------------------------------------------
Undo Segment Summary for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsed
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 54,326 ########## 2,497 3 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> 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
------------ ------------ -------- ------- -------- ------- ------ -------------
04-Oct 08:46 332 ######## 14 2 0 0 0/0/0/0/0/0
04-Oct 08:36 228 ######## 13 1 0 0 0/0/0/0/0/0
04-Oct 08:26 190 ######## 17 1 0 0 0/0/0/0/0/0
04-Oct 08:16 256 ######## 13 2 0 0 0/0/0/0/0/0
04-Oct 08:06 261 ######## 15 2 0 0 0/0/0/0/0/0
04-Oct 07:56 188 ######## 4 1 0 0 0/0/0/0/0/0
04-Oct 07:46 172 ######## 14 2 0 0 0/0/0/0/0/0
04-Oct 07:36 129 ######## 345 1 0 0 0/0/0/0/0/0
04-Oct 07:26 58 ######## 6 1 0 0 0/0/0/0/0/0
04-Oct 07:16 177 ######## 152 1 0 0 0/0/0/0/0/0
04-Oct 07:06 177 ######## 14 1 0 0 0/0/0/0/0/0
04-Oct 06:56 58 ######## 3 1 0 0 0/0/0/0/0/0
04-Oct 06:46 159 ######## 11 1 0 0 0/0/0/0/0/0
04-Oct 06:36 133 ######## 15 1 0 0 0/0/0/0/0/0
04-Oct 06:26 51 ######## 9 1 0 0 0/0/0/0/0/0
04-Oct 06:16 108 ######## 13 2 0 0 0/0/0/0/0/0
04-Oct 06:06 62 ######## 18 1 0 0 0/0/0/0/0/0
04-Oct 05:56 29 ######## 209 1 0 0 0/0/0/0/0/0
04-Oct 05:46 97 ######## 232 2 0 0 0/0/0/0/0/0
04-Oct 05:36 74 ######## 177 1 0 0 0/0/0/0/0/0
04-Oct 05:26 20 ######## 368 2 0 0 0/0/0/0/0/0
04-Oct 05:16 85 ######## 131 1 0 0 0/0/0/0/0/0
04-Oct 05:06 91 ######## 112 2 0 0 0/0/0/0/0/0
04-Oct 04:56 64 ######## 151 1 0 0 0/0/0/0/0/0
04-Oct 04:46 139 ######## 849 1 0 0 0/0/0/0/0/0
04-Oct 04:36 121 ######## 12 1 0 0 0/0/0/0/0/0
04-Oct 04:26 55 ######## 460 1 0 0 0/0/0/0/0/0
04-Oct 04:16 86 ######## 967 2 0 0 0/0/0/0/0/0
04-Oct 04:06 1,471 ######## 2,497 1 0 0 0/0/0/0/0/0
04-Oct 03:56 790 ######## 139 1 0 0 0/0/0/0/0/0
04-Oct 03:46 127 ######## 577 1 0 0 0/0/0/0/0/0
04-Oct 03:36 112 ######## 570 2 0 0 0/0/0/0/0/0
04-Oct 03:26 57 ######## 1,141 2 0 0 0/0/0/0/0/0
04-Oct 03:16 127 ######## 670 2 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
Consistent RBA 147,750 0.0 0 0
FAL request queue 1,152 0.0 0 0
FIB s.o chain latch 72 0.0 0 0
FOB s.o list latch 6,351 0.0 0 0
SQL memory manager latch 64 0.0 0 19,144 0.0
SQL memory manager worka 5,366,422 0.0 0.0 0 0
active checkpoint queue 316,558 2.2 0.0 0 0
alert log latch 35 0.0 0 0
archive control 1,996 0.0 0 0
archive process latch 1,553 0.0 0 0
begin backup scn array 498 0.0 0 0
cache buffer handles 35,697,766 0.0 0.0 0 0
cache buffers chains 3,101,017,470 0.0 0.0 20 67,701,408 0.0
cache buffers lru chain 253,150 0.0 0.0 0 59,880,022 0.0
channel handle pool latc 2,474 0.0 0 0
channel operations paren 43,036 0.0 0 0
checkpoint queue latch 46,047,151 0.0 0.0 0 205,536 0.0
child cursor hash table 489,952 0.0 0.0 0 0
dml lock allocation 834,008 0.0 0.0 0 0
dummy allocation 2,398 0.0 0 0
enqueue hash chains 1,600,297 0.0 0.0 0 0
enqueues 2,807,613 0.0 0.0 0 0
event group latch 1,135 0.0 0 0
global tx hash mapping 632 0.0 0 0
hash table column usage 3,548 0.0 0 4,791,867 0.0
internal temp table obje 3 0.0 0 0
job workq parent latch 0 0 128 0.0
job_queue_processes para 1,024 0.0 0 0
ktm global data 354 0.0 0 0
lgwr LWN SCN 154,053 0.0 0.0 0 0
library cache 102,460,001 0.1 0.0 1 200,259 0.1
library cache load lock 8,960 0.0 0 0
library cache pin 70,937,572 0.0 0.0 0 0
library cache pin alloca 24,662,164 0.0 0.0 0 0
list of block allocation 29,913 0.0 0 0
loader state object free 21,342 0.0 0 0
longop free list parent 4,925 0.0 0 496 0.0
message pool operations 230 0.0 0 0
messages 1,453,295 0.6 0.0 0 0
mostly latch-free SCN 154,522 0.3 0.0 0 0
multiblock read objects 5,204,684 0.0 0.0 0 0
ncodef allocation latch 914 0.0 0 0
object stats modificatio 9 0.0 0 0
post/wait queue 252,836 0.0 0.0 0 129,680 0.0
process allocation 2,270 0.0 0 1,135 0.0
process group creation 2,270 0.0 0 0
redo allocation 3,062,781 0.0 0.0 0 0
redo copy 0 0 2,761,779 0.0
redo writing 800,053 0.2 0.0 0 0
row cache enqueue latch 35,458,310 0.1 0.0 0 0
Latch Activity for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
row cache objects 35,776,069 0.0 0.0 0 964 0.0
sequence cache 144,050 0.0 0.0 0 0
session allocation 5,038,855 0.0 0.0 0 0
session idle bit 69,718,878 0.0 0.0 0 0
session switching 914 0.0 0 0
session timer 19,193 0.0 0 0
shared pool 17,916,476 0.0 0.0 0 0
sim partition latch 0 0 5,363 0.2
simulator hash latch 110,895,219 0.0 0.0 0 0
simulator lru latch 401,784 0.0 0.0 0 3,045,955 0.1
sort extent pool 8,388 0.0 0 0
temp lob duration state 69 0.0 0 0
temporary table state ob 2 0.0 0 0
transaction allocation 16,260,752 0.0 0 0
transaction branch alloc 1,546 0.0 0 0
undo global data 804,139 0.0 0.0 0 0
user lock 4,460 0.0 0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> ordered by misses desc
Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
cache buffers chains 3,101,017,470 337,966 1,885 336096/1868/
7/1/0
library cache 102,460,001 89,327 445 88883/443/1/
0/0
row cache enqueue latch 35,458,310 21,723 13 21710/13/0/0
/0
row cache objects 35,776,069 15,231 47 15184/47/0/0
/0
cache buffer handles 35,697,766 10,037 1 10036/1/0/0/
0
library cache pin 70,937,572 8,983 61 8922/61/0/0/
0
messages 1,453,295 8,355 1 8354/1/0/0/0
shared pool 17,916,476 2,937 35 2902/35/0/0/
0
library cache pin allocati 24,662,164 1,970 20 1950/20/0/0/
0
session allocation 5,038,855 1,791 6 1785/6/0/0/0
redo writing 800,053 1,452 2 1450/2/0/0/0
multiblock read objects 5,204,684 968 1 967/1/0/0/0
session idle bit 69,718,878 436 9 427/9/0/0/0
redo allocation 3,062,781 404 1 403/1/0/0/0
simulator hash latch 110,895,219 35 1 34/1/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
cache buffer handles kcbzfs 0 1 1
cache buffers chains kcbgtcr: kslbegin excl 0 1,341 1,410
cache buffers chains kcbrls: kslbegin 0 320 333
cache buffers chains kcbgtcr: fast path 0 206 125
cache buffers chains kcbzib: multi-block read: 0 6 0
cache buffers chains kcbchg: kslbegin: bufs not 0 3 5
cache buffers chains kcbzgb: scan from tail. no 0 3 0
cache buffers chains kcbget: exchange 0 2 0
cache buffers chains kcbbxsv 0 1 1
cache buffers chains kcbzib: finish free bufs 0 1 3
cache buffers chains kcbget: pin buffer 0 1 2
library cache kglupc: child 0 105 29
library cache kgllkdl: child: cleanup 0 66 49
library cache kglpnc: child 0 65 178
library cache kglpndl: child: before pro 0 54 4
library cache kglpin: child: heap proces 0 42 0
library cache kglhdgn: child: 0 39 128
library cache kglobpn: child: 0 24 2
library cache kglpnp: child 0 20 42
library cache kglhdgc: child: 0 10 1
library cache kglpndl: child: after proc 0 9 0
library cache kglhdiv: child 0 3 0
library cache kgldte: child 0 0 2 3
library cache kglic 0 2 0
library cache kglget: child: KGLDSBRD 0 2 8
library cache kglobld 0 1 0
library cache pin kglupc 0 22 7
library cache pin kglpndl 0 17 2
library cache pin kglpnc: child 0 13 31
library cache pin kglpnal: child: alloc spac 0 6 14
library cache pin kglpnp: child 0 3 7
library cache pin alloca kglpnal 0 14 16
library cache pin alloca kgllkdl 0 6 4
messages ksaamb: after wakeup 0 1 0
multiblock read objects kcbzib: normal mbr free 0 1 0
redo allocation kcrfwr 0 1 1
redo writing kcrfwcr 0 2 2
row cache enqueue latch kqreqa 0 7 13
row cache enqueue latch kqreqd 0 6 0
row cache objects kqrpfl: not dirty 0 29 1
row cache objects kqrpre: find obj 0 18 46
session allocation ksuxds: not user session 0 5 1
session allocation ksucri 0 1 5
session idle bit ksupuc: set busy 0 6 3
session idle bit ksuxds 0 3 1
shared pool kghupr1 0 25 31
shared pool kghalp 0 5 2
shared pool kghalo 0 4 2
shared pool kghfrunp: alloc: wait 0 1 0
shared pool kghfrunp: clatch: nowait 0 1 0
simulator hash latch kcbsacc: lookup dba 0 1 0
-------------------------------------------------------------
Dictionary Cache Stats for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
->"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 4,991 0.0 0 0 31
dc_global_oids 2,654,491 0.0 0 0 18
dc_histogram_data 1,479 0.0 0 8 4
dc_histogram_defs 303,944 0.1 0 2,308 2,996
dc_object_ids 4,937,134 0.0 0 2 834
dc_objects 98,112 0.6 0 4 1,974
dc_profiles 1,131 0.0 0 0 1
dc_rollback_segments 26,049 0.0 0 2 63
dc_segments 547,611 0.0 0 4 837
dc_sequences 70,299 0.0 0 70,299 43
dc_tablespace_quotas 16 0.0 0 4 6
dc_tablespaces 3,197,789 0.0 0 0 17
dc_user_grants 20,667 0.0 0 0 77
dc_usernames 9,398 0.0 0 0 66
dc_users 5,881,856 0.0 0 0 77
-------------------------------------------------------------
Library Cache Activity for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 2,084 0.1 2,084 0.1 0 0
CLUSTER 1,436 0.2 734 0.8 0 0
INDEX 2,174 20.2 2,608 33.5 0 0
SQL AREA 2,859,913 2.1 29,960,016 0.4 4,548 22,210
TABLE/PROCEDURE 2,881,029 0.0 5,711,355 0.1 2,327 0
TRIGGER 13,315 0.0 13,315 0.1 5 0
-------------------------------------------------------------
Shared Pool Advisory for DB: SOS1P Instance: SOS1P End Snap: 77168
-> 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
----------- ----- ---------- ------------ ------------ ------- ---------------
224 .5 190 34,576 467,639,584 1.0 3,993,895,221
272 .7 238 41,712 467,639,586 1.0 3,993,895,650
320 .8 286 48,790 467,639,586 1.0 3,993,895,735
368 .9 334 55,963 467,639,586 1.0 3,993,895,880
416 1.0 382 63,053 467,639,586 1.0 3,993,896,090
464 1.1 430 70,202 467,639,586 1.0 3,993,896,654
512 1.2 478 77,360 467,639,586 1.0 3,993,897,053
560 1.3 526 84,494 467,639,586 1.0 3,993,897,381
608 1.5 574 91,647 467,639,586 1.0 3,993,897,760
656 1.6 622 98,718 467,639,586 1.0 3,993,898,034
704 1.7 670 105,902 467,639,587 1.0 3,993,898,533
752 1.8 696 109,617 467,639,589 1.0 3,993,899,589
800 1.9 707 111,008 467,639,590 1.0 3,993,900,474
848 2.0 707 111,008 467,639,591 1.0 3,993,901,238
-------------------------------------------------------------
SGA Memory Summary for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 4,294,967,296
Fixed Size 751,048
Redo Buffers 1,323,008
Variable Size 3,254,779,904
----------------
sum 7,551,821,256
-------------------------------------------------------------
SGA breakdown difference for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
Pool Name Begin value End value % Diff
------ ------------------------------ ---------------- ---------------- -------
java free memory 33,554,432 33,554,432 0.00
large PX msg pool 644,874,288 644,874,288 0.00
large free memory 9,437,136 9,437,136 0.00
shared Checkpoint queue 41,963,520 41,963,520 0.00
shared DML lock 4,073,952 4,073,952 0.00
shared FileOpenBlock 16,270,720 16,270,720 0.00
shared KGK heap 33,368 33,368 0.00
shared KGLS heap 2,213,512 4,289,536 93.79
shared KQR L PO 2,845,640 2,961,504 4.07
shared KQR M PO 2,271,944 2,276,040 0.18
shared KQR M SO 21,560 21,560 0.00
shared KQR S SO 25,120 25,120 0.00
shared KQR X PO 15,472 15,472 0.00
shared KSXR pending messages que 853,952 853,952 0.00
shared MTTR advisory 160,544 160,544 0.00
shared OUTLNCACHE 6,512 6,512 0.00
shared PL/SQL DIANA 1,060,056 3,146,624 196.84
shared PL/SQL MPCODE 2,443,848 3,580,784 46.52
shared PLS non-lib hp 2,088 2,088 0.00
shared PX subheap 328,632 328,632 0.00
shared constraints 1,096,240 1,096,240 0.00
shared db_block_hash_buckets 12,658,736 12,658,736 0.00
shared db_handles 4,640,000 4,640,000 0.00
shared dictionary cache 4,274,432 4,274,432 0.00
shared enqueue 8,576,472 8,576,472 0.00
shared enqueue resources 2,601,672 2,601,672 0.00
shared errors 22,320 22,320 0.00
shared event statistics per sess 50,076,040 50,076,040 0.00
shared fixed allocation callback 3,808 3,808 0.00
shared free memory 21,511,688 167,558,064 678.92
shared joxs heap init 4,240 4,240 0.00
shared ktlbk state objects 2,596,920 2,596,920 0.00
shared library cache 157,929,112 145,863,328 -7.64
shared message pool freequeue 517,336 517,336 0.00
shared messages 832,000 832,000 0.00
shared miscellaneous 105,785,008 105,896,880 0.11
shared parameters 182,032 277,536 52.47
shared processes 5,472,000 5,472,000 0.00
shared qmps connections 1,938,200 1,938,200 0.00
shared replication session stats 1,339,120 1,339,120 0.00
shared session param values 4,055,360 3,943,488 -2.76
shared sessions 11,911,120 11,911,120 0.00
shared sim memory hea 3,757,984 3,757,984 0.00
shared sim trace entries 1,572,864 1,572,864 0.00
shared sql area 252,166,448 112,656,968 -55.32
shared table definiti 6,392 19,968 212.39
shared transaction 8,063,224 8,063,224 0.00
shared trigger defini 1,552 2,912 87.63
shared trigger inform 1,936 1,160 -40.08
shared trigger source 480 392 -18.33
shared type object de 12,968 12,792 -1.36
buffer_cache 4,294,967,296 4,294,967,296 0.00
fixed_sga 751,048 751,048 0.00
log_buffer 1,311,744 1,311,744 0.00
SGA breakdown difference for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
Pool Name Begin value End value % Diff
------ ------------------------------ ---------------- ---------------- -------
-------------------------------------------------------------
init.ora Parameters for DB: SOS1P Instance: SOS1P Snaps: 77104 -77168
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
_b_tree_bitmap_plans FALSE
audit_file_dest /ora/oracle/admin/SOS1P/audit
background_dump_dest /ora/oracle/admin/SOS1P/bdump
compatible 9.2.0.8
control_files /sos/db01/oracle/SOS1P/oractrl/SO
core_dump_dest /ora/oracle/admin/SOS1P/cdump
db_block_size 8192
db_cache_size 4294967296
db_file_multiblock_read_count 16
db_files 1024
db_name SOS1P
enqueue_resources 20000
fast_start_mttr_target 180
global_names FALSE
instance_name SOS1P
java_pool_size 33554432
job_queue_processes 1
log_archive_dest_1 LOCATION=/sos/db03/oracle/SOS1P/o
log_archive_format SOS1P_arch_t%t_s%s.arc
log_archive_max_processes 2
log_archive_start TRUE
log_buffer 1048576
log_checkpoint_timeout 0
max_dump_file_size 20M
max_enabled_roles 100
open_cursors 5000
open_links 40
optimizer_mode CHOOSE
parallel_automatic_tuning TRUE
pga_aggregate_target 3145728000
processes 4000
remote_login_passwordfile EXCLUSIVE
sga_max_size 7551821256
shared_pool_size 419430400
timed_statistics TRUE
undo_management AUTO
undo_retention 10800
undo_tablespace UNDO_01
user_dump_dest /ora/oracle/admin/SOS1P/udump
workarea_size_policy auto
-------------------------------------------------------------
End of Report
- oracle高手帮忙分析Statspack.
- oracle statspack 分析
- 使用statspack进行oracle性能分析
- 高手帮忙！！！
- 高手帮忙
- oracle statspack
- Oracle statspack
- 我的程序是否有错？请高手帮忙分析
- Oracle进程连接数过多时的Statspack分析报告
- 如何配置oracle的性能分析工具statspack
- 数据库出问题，高手帮忙！
- jsp难题，高手帮忙
- 高手帮忙了
- 劳驾高手帮忙
- 高手帮忙啊!!!!!!!!!!!!!!!1
- 请高手们帮忙!!!!
- 请高手帮忙!
- 高手帮忙设计数据库
- 简单工厂模式 & 策略模式
- Cadence Allegro/Orcad 电路板方案全系列完美破解的特别说明（包括64位系统破解方法）
- 常用软件的各种端口
- url 编码问题
- RSA加密算法简介
- oracle高手帮忙分析Statspack.
- jquery
- EDI传输协议简介
- Asp.net 中HttpHandler,HttpModule,IHttpHandlerFactory的原理与应用
- error LNK2001
- jquery.validate API
- Dockpanel 实现浮动和多标签窗体解决方案(转)
- ARM与嵌入式linux如何入门
- 数字水印 IHttpHandler 学习总结
