| 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 -> Re: can anyone help me tune the database
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- -------
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- ---------
-------------------
Begin Snap: 52 23-Aug-06 22:28:51 16 5.4
End Snap: 53 23-Aug-06 22:49:26 16 4.7
Elapsed: 20.58 (mins)
Cache Sizes (end)
Buffer Cache: 504M Std Block Size: 8K
Shared Pool Size: 216M Log Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 8,152.77
1,006,867.60
Logical reads: 1,323.56
163,459.10
Block changes: 43.14
5,327.50
Physical reads: 12.50
1,543.40
Physical writes: 200.46
24,757.40
User calls: 0.09
11.10
Parses: 0.68
83.90
Hard parses: 0.07
8.30
Sorts: 0.41
51.10
Logons: 0.01
0.90
Executes: 14.71
1,816.80
Transactions: 0.01
% Blocks changed per Read: 3.26 Recursive Call %: 99.77
Rollback per transaction %: 40.00 Rows per Sort: 86.15
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.06 In-memory Sort %: 100.00
Library Hit %: 97.38 Soft Parse %: 90.11
Execute to Parse %: 95.38 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 92.42 % Non-Parse CPU: 97.64
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 91.75 94.29
% SQL with executions>1: 52.05 53.31
% Memory for SQL w/exec>1: 44.93 44.41
Top 5 Timed Events
Event Waits Time (s)Ela Time
CPU time 83
62.08
direct path write 16,504 43
32.23
db file sequential read 927 3
2.02
db file scattered read 962 2
1.25
control file parallel write 401 1
1.11
-------------------------------------------------------------
Wait Events for DB: OCTOPUS Instance: octopus Snaps: 52 -53
-> 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
---------------------------- ------------ ---------- ---------- ------
direct path write 16,504 0 43 3
1,650.4
db file sequential read 927 0 3 3
92.7
db file scattered read 962 0 2 2
96.2
control file parallel write 401 0 1 4
40.1
log file parallel write 96 91 1 7
9.6
ARCH wait on SENDREQ 20 0 1 31
2.0
control file sequential read 304 0 0 1
30.4
log file sync 6 0 0 8
0.6
db file parallel write 10 5 0 2
1.0
SQL*Net break/reset to clien 2 0 0 8
0.2
log buffer space 1 0 0 12
0.1
latch free 1 0 0 9
0.1
SQL*Net more data to client 14 0 0 0
1.4
LGWR wait for redo copy 5 0 0 0
0.5
SQL*Net message from client 96 0 3,070 31977
9.6
SQL*Net message to client 96 0 0 0
9.6
-------------------------------------------------------------
Background Wait Events for DB: OCTOPUS Instance: octopus Snaps: 52
-53
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
40.1
log file parallel write 96 91 1 7
9.6
ARCH wait on SENDREQ 20 0 1 31
2.0
control file sequential read 260 0 0 1
26.0
db file sequential read 2 0 0 81
0.2
db file parallel write 10 5 0 2
1.0
latch free 1 0 0 9
0.1
LGWR wait for redo copy 5 0 0 0
0.5
rdbms ipc message 1,426 1,345 11,689 8197
142.6
smon timer 4 4 1,229 ######
0.4
-------------------------------------------------------------
SQL ordered by Gets for DB: OCTOPUS Instance: octopus Snaps: 52 -53
-> 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,461,109 1 1,461,109.0 89.4 45.95 47.73
2010239668
begin Fspdf_Eod_Process_Pkg.FSPDFP_GEN_SALES_LEDGER_UPD(ip_compa
ny_id_pk=>:ip_company_id_pk, ip_loggedin_id=>:ip_loggedin_id, Er
rNumber=>:ErrNumber, ErrMessage=>:ErrMessage); end;
1,273,844 748 1,703.0 77.9 33.95 33.89 2933058741
SELECT NVL(SUM(bldtls.debt_approved_amt),0), NVL(S
UM(bldtls.debt_unapproved_amt),0) FROM BDS_BILL_
DETAILS bldtls WHERE bldtls.company_id_pk = :b3
AND bldtls.debtor_account_cd = :b2 AND TO_DATE(TO_CH
AR(bldtls.bill_batch_date,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE
65,183 1 65,183.0 4.0 1.02 1.39
96706093
begin Fspdf_Eod_Process_Pkg.FSPDFP_RECOURSE_UNAPPROV_UPD(ip_comp
any_id_pk=>:ip_company_id_pk, ip_loggedin_id=>:ip_loggedin_id, E
rrNumber=>:ErrNumber, ErrMessage=>:ErrMessage); end;
53,164 519 102.4 3.3 1.55 1.60 3550942769
SELECT NVL(SUM(invdtls.cl_approved_amt),0), NVL(SU
M(invdtls.cl_unapproved_amt),0) FROM FCT_INV_DET
AILS invdtls WHERE invdtls.company_id_pk = :b3
AND invdtls.client_account_cd = :b2 AND TO_DATE(TO_CH
AR(invdtls.inv_batch_date,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE
46,758 322 145.2 2.9 0.53 0.71 4041023944
SELECT invdtls.tran_id, invdtls.invoice_no, in
vdtls.invoice_dn_flag, invdtls.debt_approved_amt FROM
FCT_INV_DETAILS invdtls WHERE invdtls.company_
id_pk = :b4 AND invdtls.client_account_cd = :b3 AND in
vdtls.debt_approved_amt > 0 AND (TO_DATE(TO_CHAR(invdtls.du
40,913 1 40,913.0 2.5 1.58 4.38
4103447814
40,533 1 40,533.0 2.5 1.98 2.11
1428328184
begin Fspdf_Eod_Process_Pkg.FSPDFP_LIMIT_EXP_CHECK_UPD(ip_compan
y_id_pk=>:ip_company_id_pk, ip_loggedin_id=>:ip_loggedin_id, Err
Number=>:ErrNumber, ErrMessage=>:ErrMessage); end;
39,282 1 39,282.0 2.4 1.52 1.60 555896067
INSERT INTO TMP_INV_DEBT_APP_AMT_SUM (SELECT invdtl
s.company_id_pk, invdtls.Debtor_Account_Cd, NVL(SUM(in
vdtls.debt_approved_amt),0), NVL(SUM(invdtls.debt_unapprove
d_amt),0) FROM FCT_INV_DETAILS invdtls WHERE i
nvdtls.company_id_pk = :b2 AND TO_DATE(TO_CHAR(invdtls.inv_b
26,374 1 26,374.0 1.6 0.41 0.83
4059808258
INSERT into stats$sqltext ( hash_value
, text_subset , piece
, sql_text , address , comman
SQL ordered by Gets for DB: OCTOPUS Instance: octopus Snaps: 52 -53
-> 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
d_type , last_snap_id )
select st1.hash_value , ss.text_subset
20,895 1 20,895.0 1.3 5.91 7.17
4244596709
INSERT INTO TMP_EODD_DB_SALES_LEDGER (SELECT debsl.Comp
any_Id_Pk, debsl.debtor_account_cd, NVL(debsl.approved
_amount,0), NVL(debsl.unapproved_amount,0) FROM FC
T_DEBTOR_SALES_LEDGER debsl WHERE debsl.company_id_pk =
:b2 AND TO_CHAR(debsl.tran_date,'DD/MM/YYYY') = TO_CHAR((:b
17,562 399 44.0 1.1 0.33 0.36
2806890973
SELECT NVL(SUM(bldtls.cl_approved_amt),0), NVL(SUM
(bldtls.cl_unapproved_amt),0) FROM BDS_BILL_DETA
ILS bldtls WHERE bldtls.company_id_pk = :b3
AND bldtls.client_account_cd = :b2 AND TO_DATE(TO_CHAR(b
ldtls.bill_batch_date,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_
16,632 924 18.0 1.0 1.23 1.18 2158161567
SELECT MAX(procstat.tran_date) FROM FCT_P ROCESS_STATUS procstat WHERE procstat.company_id_p k = :b1 AND procstat.process_name = 'DAYEND' AND procstat.status = 'C'
16,632 924 18.0 1.0 1.30 1.20 4233014695
SELECT MAX(procstat.tran_date) FROM FCT_PROC ESS_STATUS procstat WHERE procstat.company_id_pk = : b1 AND procstat.process_name = 'DAYEND' AND procstat.status = 'C'
14,684 266 55.2 0.9 0.14 0.22 691246908
SELECT billdtls.tran_id, billdtls.bill_lc_no,
billdtls.bill_dn_flag, billdtls.debt_approved_amt FRO
M bds_bill_details billdtls WHERE billdtls.comp
any_id_pk = :b4 AND billdtls.client_account_cd = :b3 A
ND billdtls.debt_approved_amt > 0 AND (TO_DATE(TO_CHAR(bill
13,841 3,242 4.3 0.8 0.28 0.37
9943475
INSERT INTO FCT_DEBTOR_SALES_LEDGER ( company_id_pk
, tran_date, debtor_account_cd, currency_cd,
approved_amount, unapproved_amount, sl_balance )
VALUES ( :b6, :b5, :b4, :b3,
NVL(:b2,0), NVL(:b1,0), (NVL(:b2,0) + NVL(:b1,0))
10,064 1 10,064.0 0.6 0.09 0.29
2860301267
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
14,385 1 14,385.0 93.2 45.95 47.73
2010239668
begin Fspdf_Eod_Process_Pkg.FSPDFP_GEN_SALES_LEDGER_UPD(ip_compa
ny_id_pk=>:ip_company_id_pk, ip_loggedin_id=>:ip_loggedin_id, Er
rNumber=>:ErrNumber, ErrMessage=>:ErrMessage); end;
14,062 1 14,062.0 91.1 5.91 7.17 4244596709
INSERT INTO TMP_EODD_DB_SALES_LEDGER (SELECT debsl.Comp
any_Id_Pk, debsl.debtor_account_cd, NVL(debsl.approved
_amount,0), NVL(debsl.unapproved_amount,0) FROM FC
T_DEBTOR_SALES_LEDGER debsl WHERE debsl.company_id_pk =
:b2 AND TO_CHAR(debsl.tran_date,'DD/MM/YYYY') = TO_CHAR((:b
535 1 535.0 3.5 1.58 4.38
4103447814
451 1 451.0 2.9 0.27 0.73 1598700028
SELECT 1 AS Alert_Parameter_No, 'Forward
Contracts' AS Alert_Parameter_Name, 1.1 AS Sub_Grou
p_No, 'FC Booked Deal Not Updated' AS Alert_Param_De
tail, bank.long_name AS Name, NULL AS In
voice_No, booking.fc_no AS Batch_Reference,
172 1 172.0 1.1 0.41 0.83
4059808258
INSERT into stats$sqltext ( hash_value
, text_subset , piece
, sql_text , address , comman
d_type , last_snap_id )
select st1.hash_value , ss.text_subset
152 748 0.2 1.0 33.95 33.89
2933058741
SELECT NVL(SUM(bldtls.debt_approved_amt),0), NVL(S
UM(bldtls.debt_unapproved_amt),0) FROM BDS_BILL_
DETAILS bldtls WHERE bldtls.company_id_pk = :b3
AND bldtls.debtor_account_cd = :b2 AND TO_DATE(TO_CH
AR(bldtls.bill_batch_date,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE
93 1 93.0 0.6 1.52 1.60 555896067
INSERT INTO TMP_INV_DEBT_APP_AMT_SUM (SELECT invdtl
s.company_id_pk, invdtls.Debtor_Account_Cd, NVL(SUM(in
vdtls.debt_approved_amt),0), NVL(SUM(invdtls.debt_unapprove
d_amt),0) FROM FCT_INV_DETAILS invdtls WHERE i
nvdtls.company_id_pk = :b2 AND TO_DATE(TO_CHAR(invdtls.inv_b
55 1 55.0 0.4 1.02 1.39
96706093
55 1 55.0 0.4 0.09 0.29
2860301267
DELETE TMP_EODD_DB_SALES_LEDGER
48 104 0.5 0.3 0.02 0.17
1090910882
INSERT INTO FCT_DISAPP_ADJUSTMENT ( company_id_pk, tran
_id, init_type, disapproval_id, tran_date, value_date,
SQL ordered by Reads for DB: OCTOPUS Instance: octopus Snaps: 52 -53
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
41 11 3.7 0.3 0.03 0.09
1951417366
SELECT iq.brule, iq.NoParameters FROM business_rules iq
WHERE UPPER(iq.brname_pk) = UPPER(:b1)
33 1 33.0 0.2 1.98 2.11
1428328184
begin Fspdf_Eod_Process_Pkg.FSPDFP_LIMIT_EXP_CHECK_UPD(ip_compan
y_id_pk=>:ip_company_id_pk, ip_loggedin_id=>:ip_loggedin_id, Err
Number=>:ErrNumber, ErrMessage=>:ErrMessage); end;
13 5 2.6 0.1 0.00 0.07
3111103299
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fr
om idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
10 924 0.0 0.1 0.16 0.19 3916971036
INSERT INTO FCT_DAILY_PRODUCT ( company_id_pk,
prod_date, client_account_cd, client_currency,
sl_approved_amount, sl_unapproved_amount, sl_ba
lance, cur_bal, normal_bal, overpaid_bal,
credit_bal, normal_disc, overpaid_disc, cred
9 3,242 0.0 0.1 0.17 0.14
1005703547
SELECT acclmts.valid_from, acclmts.valid_to, acclmts
.limit_amount, acclmts.adhoc_valid_from, acclmts.adhoc_v
alid_to, acclmts.adhoc_amount FROM FCM_ACCOU
NT_LIMITS acclmts WHERE acclmts.company_id_pk = :b4
AND acclmts.group_type_pk = 'D' AND acclmts.group_id_pk = :b
6 3,242 0.0 0.0 0.28 0.37 9943475
INSERT INTO FCT_DEBTOR_SALES_LEDGER ( company_id_pk
, tran_date, debtor_account_cd, currency_cd,
approved_amount, unapproved_amount, sl_balance )
VALUES ( :b6, :b5, :b4, :b3,
NVL(:b2,0), NVL(:b1,0), (NVL(:b2,0) + NVL(:b1,0))
3 924 0.0 0.0 0.03 0.08
1858678999
SELECT acclmts.valid_from, acclmts.valid_to, accl
mts.limit_amount, acclmts.adhoc_valid_from, acclmts.ad
hoc_valid_to, acclmts.adhoc_amount FROM F
-------------------------------------------------------------
SQL ordered by Executions for DB: OCTOPUS Instance: octopus Snaps:
52 -53
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
3,242 3,242 1.0 0.00 0.00 9943475
INSERT INTO FCT_DEBTOR_SALES_LEDGER ( company_id_pk
, tran_date, debtor_account_cd, currency_cd,
approved_amount, unapproved_amount, sl_balance )
VALUES ( :b6, :b5, :b4, :b3,
NVL(:b2,0), NVL(:b1,0), (NVL(:b2,0) + NVL(:b1,0))
3,242 2,462 0.8 0.00 0.00
1005703547
SELECT acclmts.valid_from, acclmts.valid_to, acclmts
.limit_amount, acclmts.adhoc_valid_from, acclmts.adhoc_v
alid_to, acclmts.adhoc_amount FROM FCM_ACCOU
NT_LIMITS acclmts WHERE acclmts.company_id_pk = :b4
AND acclmts.group_type_pk = 'D' AND acclmts.group_id_pk = :b
2,474 645 0.3 0.00 0.00 3635468503
SELECT NVL(invSum.sum_debt_approved_amt, 0), NVL(i
nvSum.sum_debt_unapproved_amt,0) FROM TMP_INV_DE
BT_APP_AMT_SUM invSum WHERE invSum.company_id_pk
= :b2 AND invSum.debtor_account_cd = :b1
924 924 1.0 0.00 0.00
1314561766
INSERT INTO FCT_CLIENT_SALES_LEDGER ( company_id_pk
, tran_date, client_account_cd, currency_cd,
approved_amount, unapproved_amount, sl_balance )
VALUES ( :b6, :b5, :b4, :b3,
NVL(:b2,0), NVL(:b1,0), (NVL(:b2,0) + NVL(:b1,0))
924 792 0.9 0.00 0.00
1858678999
SELECT acclmts.valid_from, acclmts.valid_to, accl
mts.limit_amount, acclmts.adhoc_valid_from, acclmts.ad
hoc_valid_to, acclmts.adhoc_amount FROM F
CM_ACCOUNT_LIMITS acclmts WHERE acclmts.company_id_p
k = :b4 AND acclmts.group_type_pk = 'C' AND acclmts.grou
924 924 1.0 0.00 0.00
2158161567
SELECT MAX(procstat.tran_date) FROM FCT_P
ROCESS_STATUS procstat WHERE procstat.company_id_p
k = :b1 AND procstat.process_name = 'DAYEND' AND procs
tat.status = 'C'
924 924 1.0 0.00 0.00 3916971036
INSERT INTO FCT_DAILY_PRODUCT ( company_id_pk,
prod_date, client_account_cd, client_currency,
sl_approved_amount, sl_unapproved_amount, sl_ba
lance, cur_bal, normal_bal, overpaid_bal,
credit_bal, normal_disc, overpaid_disc, cred
924 924 1.0 0.00 0.00
4233014695
SELECT MAX(procstat.tran_date) FROM FCT_PROC
ESS_STATUS procstat WHERE procstat.company_id_pk = :
b1 AND procstat.process_name = 'DAYEND' AND procstat.sta
tus = 'C'
748 748 1.0 0.05 0.05
2933058741
SQL ordered by Executions for DB: OCTOPUS Instance: octopus Snaps:
52 -53
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
SELECT NVL(SUM(bldtls.debt_approved_amt),0), NVL(S
UM(bldtls.debt_unapproved_amt),0) FROM BDS_BILL_
DETAILS bldtls WHERE bldtls.company_id_pk = :b3
AND bldtls.debtor_account_cd = :b2 AND TO_DATE(TO_CH
AR(bldtls.bill_batch_date,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE
519 519 1.0 0.00 0.00 3550942769
SELECT NVL(SUM(invdtls.cl_approved_amt),0), NVL(SU
M(invdtls.cl_unapproved_amt),0) FROM FCT_INV_DET
AILS invdtls WHERE invdtls.company_id_pk = :b3
AND invdtls.client_account_cd = :b2 AND TO_DATE(TO_CH
AR(invdtls.inv_batch_date,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE
399 399 1.0 0.00 0.00 2806890973
SELECT NVL(SUM(bldtls.cl_approved_amt),0), NVL(SUM (bldtls.cl_unapproved_amt),0) FROM BDS_BILL_DETA ILS bldtls WHERE bldtls.company_id_pk = :b3 AND bldtls.client_account_cd = :b2 AND TO_DATE(TO_CHAR(bldtls.bill_batch_date,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_
322 22 0.1 0.00 0.00 4041023944
SELECT invdtls.tran_id, invdtls.invoice_no, in
vdtls.invoice_dn_flag, invdtls.debt_approved_amt FROM
FCT_INV_DETAILS invdtls WHERE invdtls.company_
id_pk = :b4 AND invdtls.client_account_cd = :b3 AND in
vdtls.debt_approved_amt > 0 AND (TO_DATE(TO_CHAR(invdtls.du
266 82 0.3 0.00 0.00
691246908
SELECT billdtls.tran_id, billdtls.bill_lc_no,
billdtls.bill_dn_flag, billdtls.debt_approved_amt FRO
M bds_bill_details billdtls WHERE billdtls.comp
any_id_pk = :b4 AND billdtls.client_account_cd = :b3 A
ND billdtls.debt_approved_amt > 0 AND (TO_DATE(TO_CHAR(bill
233 180 0.8 0.00 0.00
787810128
% Total
52 52 6.20 3935516425 update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5, order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=: 1
31 0 3.69 1635934014 insert into sys.col_usage$ values ( :objn, :coln, decode(bit
and(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decode(b itand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode (bitand(:flag,16),0,0,1), decode(bitand(:flag,32),0,0,1), :time)
31 112 3.69 3016965513 update sys.col_usage$ set equality_preds = equality_preds + decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre ds = range_preds + decode(bitand(:flag,8),0,0,1),
24 24 2.86 4059714361 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user# ,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
20 233 2.38 787810128
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
17 17 2.03 1491008679 select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled from obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.ob j#=o.obj# and o.owner#=u.user# order by o.obj#
15 22 1.79 114078687 select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob j#=:1
15 22 1.79 1536916657 select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c def$ where obj#=:1
15 19 1.79 2385919346
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180 ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
13 18 1.55 189272129
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
SQL ordered by Parse Calls for DB: OCTOPUS Instance: octopus Snaps:
52 -53
-> End Parse Calls Threshold: 1000
% Total
12 138 1.43 2085632044 select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
12 35 1.43 2591785020 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6 or subname is null and :6 is null)
12 12 1.43 3680293972 select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clu cols,0),audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt, blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime, samplesize,cols, property,nvl(degree,1),nvl(instances,1),avgspc_flb,flbcnt,kernel cols,nvl(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4,spare6
12 12 1.43 3844343967 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i .distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.sa mplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt ,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),n
11 11 1.31 1930240031 select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj# =:1
10 10 1.19 1819073277 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_ obj#, d_owner#, nvl(property,0),subname from dependency$,obj$ wh ere d_obj#=:1 and p_obj#=obj#(+) order by order#
10 10 1.19 4049165760 select order#,columns,types from access$ where d_obj#=:1
9 9 1.07 712140972 ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERI CA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CH ARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'DD-MO N-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZON E= '+05:30' NLS_DUAL_CURRENCY = '$' NLS_TIME_FORMAT = 'HH.MI.SSX 9 23 1.07 931956286 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2) )from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(co
Sharable Mem (b) Executions % Total Hash Value
---------------- ------------ ------- ------------
1,119,355 1 0.5 1598700028
SELECT 1 AS Alert_Parameter_No, 'Forward
Contracts' AS Alert_Parameter_Name, 1.1 AS Sub_Grou
p_No, 'FC Booked Deal Not Updated' AS Alert_Param_De
tail, bank.long_name AS Name, NULL AS In
voice_No, booking.fc_no AS Batch_Reference,
-------------------------------------------------------------
Instance Activity Stats for DB: OCTOPUS Instance: octopus Snaps: 52
-53
Statistic Total per Secondper Trans
CPU used by this session 8,259 6.7
825.9
CPU used when call started 8,259 6.7
825.9
CR blocks created 44 0.0
4.4
DBWR buffers scanned 0 0.0
0.0
DBWR checkpoint buffers written 14 0.0
1.4
DBWR checkpoints 0 0.0
0.0
DBWR free buffers found 0 0.0
0.0
DBWR lru scans 0 0.0
0.0
DBWR make free requests 0 0.0
0.0
DBWR summed scan depth 0 0.0
0.0
DBWR transaction table writes 6 0.0
0.6
DBWR undo block writes 6 0.0
0.6
SQL*Net roundtrips to/from client 70 0.1
7.0
active txn count during cleanout 333 0.3
33.3
background checkpoints completed 0 0.0
0.0
background checkpoints started 0 0.0
0.0
background timeouts 1,241 1.0
124.1
branch node splits 0 0.0
0.0
buffer is not pinned count 1,532,699 1,241.1
153,269.9
buffer is pinned count 819,951 663.9
81,995.1
bytes received via SQL*Net from c 22,131 17.9
2,213.1
bytes sent via SQL*Net to client 49,106 39.8
4,910.6
calls to get snapshot scn: kcmgss 28,991 23.5
2,899.1
calls to kcmgas 280 0.2
28.0
calls to kcmgcs 187 0.2
18.7
change write time 28 0.0
2.8
cleanout - number of ktugct calls 346 0.3
34.6
cleanouts and rollbacks - consist 0 0.0
0.0
cleanouts only - consistent read 3 0.0
0.3
cluster key scan block gets 1,070 0.9
107.0
cluster key scans 872 0.7
87.2
commit cleanout failures: block l 0 0.0
0.0
commit cleanout failures: buffer 0 0.0
0.0
commit cleanout failures: callbac 0 0.0
0.0
commit cleanout failures: cannot 0 0.0
0.0
commit cleanouts 863 0.7
86.3
commit cleanouts successfully com 863 0.7
86.3
commit txn count during cleanout 121 0.1
12.1
consistent changes 45 0.0
4.5
consistent gets 1,583,279 1,282.0
158,327.9
consistent gets - examination 23,710 19.2
2,371.0
cursor authentications 26 0.0
2.6
data blocks consistent reads - un 45 0.0
4.5
db block changes 53,275 43.1
5,327.5
db block gets 51,312 41.6
5,131.2
deferred (CURRENT) block cleanout 245 0.2
24.5
dirty buffers inspected 0 0.0
0.0
enqueue conversions 29 0.0
2.9
enqueue deadlocks 0 0.0
0.0
enqueue releases 3,190 2.6
319.0
enqueue requests 3,190 2.6
319.0
enqueue timeouts 0 0.0
0.0
enqueue waits 0 0.0
0.0
execute count 18,168 14.7
1,816.8
free buffer inspected 0 0.0
0.0
free buffer requested 16,270 13.2
1,627.0
Statistic Total per Secondper Trans
397.7
immediate (CR) block cleanout app 3 0.0
0.3
immediate (CURRENT) block cleanou 338 0.3
33.8
index fast full scans (full) 5 0.0
0.5
index fetch by key 10,833 8.8
1,083.3
index scans kdiixs1 26,725 21.6
2,672.5
leaf node 90-10 splits 15 0.0
1.5
leaf node splits 112 0.1
11.2
logons cumulative 9 0.0
0.9
messages received 92 0.1
9.2
messages sent 92 0.1
9.2
no buffer to keep pinned count 0 0.0
0.0
no work - consistent read gets 1,545,575 1,251.5
154,557.5
opened cursors cumulative 801 0.7
80.1
parse count (failures) 0 0.0
0.0
parse count (hard) 83 0.1
8.3
parse count (total) 839 0.7
83.9
parse time cpu 195 0.2
19.5
parse time elapsed 211 0.2
21.1
physical reads 15,434 12.5
1,543.4
physical reads direct 0 0.0
0.0
physical reads direct (lob) 0 0.0
0.0
physical writes 247,574 200.5
24,757.4
physical writes direct 247,560 200.5
24,756.0
physical writes non checkpoint 247,560 200.5
24,756.0
pinned buffers inspected 0 0.0
0.0
prefetched blocks 13,553 11.0
1,355.3
prefetched blocks aged out before 0 0.0
0.0
process last non-idle time 9,250,822,976 7,490,544.9
############
recursive calls 47,685 38.6
4,768.5
recursive cpu usage 5,129 4.2
512.9
redo blocks written 20,311 16.5
2,031.1
redo buffer allocation retries 1 0.0
0.1
redo entries 26,840 21.7
2,684.0
redo ordering marks 0 0.0
0.0
redo size 10,068,676 8,152.8
1,006,867.6
redo synch time 6 0.0
0.6
redo synch writes 6 0.0
0.6
redo wastage 26,692 21.6
2,669.2
redo write time 111 0.1
11.1
redo writer latching time 0 0.0
0.0
redo writes 96 0.1
9.6
rollback changes - undo records a 0 0.0
0.0
rollbacks only - consistent read 44 0.0
4.4
rows fetched via callback 6,842 5.5
684.2
session connect time 9,250,822,976 7,490,544.9
############
session logical reads 1,634,591 1,323.6
163,459.1
session uga memory 38,144 30.9
3,814.4
session uga memory max 5,690,528 4,607.7
569,052.8
shared hash latch upgrades - no w 29,632 24.0
2,963.2
shared hash latch upgrades - wait 0 0.0
0.0
sorts (disk) 0 0.0
0.0
sorts (memory) 511 0.4
51.1
sorts (rows) 44,021 35.6
4,402.1
summed dirty queue length 0 0.0
0.0
switch current to new buffer 30 0.0
3.0
Statistic Total per Secondper Trans
table fetch by rowid 470,809 381.2
47,080.9
table fetch continued row 32,087 26.0
3,208.7
table scan blocks gotten 1,349,776 1,092.9
134,977.6
table scan rows gotten 22,670,680 18,356.8
2,267,068.0
table scans (long tables) 752 0.6
75.2
table scans (short tables) 2,258 1.8
225.8
transaction rollbacks 0 0.0
0.0
user calls 111 0.1
11.1
user commits 6 0.0
0.6
user rollbacks 4 0.0
0.4
workarea executions - multipass 0 0.0
0.0
workarea executions - onepass 2 0.0
0.2
workarea executions - optimal 466 0.4
46.6
write clones created in foregroun 0 0.0
0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: OCTOPUS Instance: octopus Snaps: 52 -53
->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 12 0 0
0.0
-------------------------------------------------------------
File IO Stats for DB: OCTOPUS Instance: octopus Snaps: 52 -53
->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)
INDX E:\ORACLE\OCTOPUS\ORADATA\OCTOPUS\INDX01.DBF
120 0 1.8 1.0 0 0 0
OCTOPUS D:\ORACLE\ORADATA\OCTOPUS\OCTOPUS01.DBF
1,124 1 1.7 10.5 1 0 0
D:\ORACLE\ORADATA\OCTOPUS\OCTOPUS02.DBF
217 0 2.6 14.3 0 0 0
SYSTEM E:\ORACLE\OCTOPUS\ORADATA\OCTOPUS\SYSTEM01.DBF
21 0 5.2 1.0 1 0 0
E:\ORACLE\OCTOPUS\ORADATA\OCTOPUS\SYSTEM02.DBF
3 0 53.3 1.0 0 0 0
TEMP E:\ORACLE\OCTOPUS\ORADATA\OCTOPUS\TEMP01.DBF
1 0 0.0 1.0 8,520 7 0
E:\ORACLE\OCTOPUS\ORADATA\OCTOPUS\TEMP02.DBF
1 0 0.0 1.0 7,984 6 0
UNDOTBS1
E:\ORACLE\OCTOPUS\ORADATA\OCTOPUS\UNDOTBS01.DBF
0 0 12 0 0
USERS E:\ORACLE\OCTOPUS\ORADATA\OCTOPUS\USERS01.DBF
246 0 3.1 1.1 0 0 0
E:\ORACLE\OCTOPUS\ORADATA\OCTOPUS\USERS02.DBF
202 0 4.2 1.1 0 0 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: OCTOPUS Instance: octopus Snaps: 52
-53
-> 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 63,063 99.5 3,170,976 15,475 14 0
0 0
-------------------------------------------------------------
Instance Recovery Stats for DB: OCTOPUS Instance: octopus Snaps: 52
-53
-> 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 119 10 167 464 34 184320 34
E 119 14 1511 20767 20317 184320 20317
-------------------------------------------------------------
Buffer Pool Advisory for DB: OCTOPUS Instance: octopus End Snap: 53 -> 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
--- ------------ ----- ---------------- -------------PGA Aggr Target Stats for DB: OCTOPUS Instance: octopus Snaps: 52 -53
------------------
D 48 .1 6,006 45.14 1,403,179,934 D 96 .2 12,012 24.19 751,984,670 D 144 .3 18,018 14.63 454,666,619 D 192 .4 24,024 10.11 314,327,711 D 240 .5 30,030 7.13 221,526,257 D 288 .6 36,036 4.55 141,421,546 D 336 .7 42,042 2.62 81,420,544 D 384 .8 48,048 1.62 50,317,372 D 432 .9 54,054 1.28 39,738,623 D 480 1.0 60,060 1.05 32,635,371 D 504 1.0 63,063 1.00 31,087,946 D 528 1.0 66,066 0.94 29,276,072 D 576 1.1 72,072 0.85 26,527,408 D 624 1.2 78,078 0.79 24,546,447 D 672 1.3 84,084 0.74 22,910,872 D 720 1.4 90,090 0.69 21,349,824 D 768 1.5 96,096 0.62 19,131,660 D 816 1.6 102,102 0.52 16,040,015 D 864 1.7 108,108 0.44 13,539,773 D 912 1.8 114,114 0.39 12,104,537 D 960 1.9 120,120 0.38 11,945,868 -------------------------------------------------------------
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
--------------- ---------------- -------------------------
56.7 90 69
%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: OCTOPUS Instance: octopus Snaps: 52
-53
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
8K 16K 337 337 0 0
16K 32K 16 16 0 0
32K 64K 6 6 0 0
64K 128K 5 5 0 0
256K 512K 2 2 0 0
512K 1024K 18 18 0 0
1M 2M 2 2 0 0
32M 64M 2 0 2 0
-------------------------------------------------------------
PGA Memory Advisory for DB: OCTOPUS Instance: octopus End Snap: 53 -> 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
---------- ------- ---------------- ---------------- --------
----------
19 0.5 13,930.1 6,402.9 69.0
345
29 0.8 13,930.1 6,336.7 69.0
345
38 1.0 13,930.1 1,824.1 88.0
100
46 1.2 13,930.1 1,648.1 89.0
25
53 1.4 13,930.1 1,490.0 90.0
15
61 1.6 13,930.1 1,373.1 91.0
8
68 1.8 13,930.1 1,359.7 91.0
1
76 2.0 13,930.1 1,335.5 91.0
0
114 3.0 13,930.1 436.3 97.0
0
152 4.0 13,930.1 321.3 98.0
0
228 6.0 13,930.1 321.3 98.0
0
304 8.0 13,930.1 295.7 98.0
0
-------------------------------------------------------------
Rollback Segment Stats for DB: OCTOPUS Instance: octopus Snaps: 52
-53
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks
Extends
0 5.0 0.00 0 0 0 0
1 38.0 0.00 8,768 0 0 0
2 7.0 0.00 130 0 0 0
3 7.0 0.00 2,048 0 0 0
4 305.0 0.00 924,546 8 0 1
5 662.0 0.00 2,886,850 3 0 2
6 38.0 0.00 8,878 0 0 0
7 5.0 0.00 0 0 0 0
8 5.0 0.00 0 0 0 0
9 7.0 0.00 166 0 0 0
10 9.0 0.00 350 0 0 0
RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- ---------------
0 385,024 0 385,024
1 2,220,032 115,400 3,268,608
2 2,351,104 237,580 2,351,104
3 647,168 58,829 712,704
4 3,727,360 274,226 3,727,360
5 4,317,184 664,932 4,317,184
6 2,220,032 630,160 5,365,760
7 2,220,032 191,840 2,220,032
8 974,848 116,981 974,848
9 2,220,032 105,188 3,137,536
10 581,632 59,491 778,240
-------------------------------------------------------------
Undo Segment Summary for DB: OCTOPUS Instance: octopus Snaps: 52 -53
-> 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 585 16,051 67 2 0 0 0/0/0/0/0/0
Undo Segment Stats for DB: OCTOPUS Instance: octopus Snaps: 52 -53 -> 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
-------------------------- -------------- ----------- -----------
------------
messages 2,992 1 1
0/1/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: OCTOPUS Instance: octopus Snaps: 52 -53
-> only latches with sleeps are shown
NoWait
Waiter
Latch Name Where Misses Sleeps
Sleeps
messages ksaamb: after wakeup 0 1
0
-------------------------------------------------------------
Top 5 Logical Reads per Segment for DB: OCTOPUS Instance: octopus
Snaps: 52 -5
Subobject Obj.
Logical
Owner Tablespace Object Name Name Type
Reads %Total
---------- ---------- -------------------- ---------- -----
------------ -------
OCTOPUS OCTOPUS BDS_BILL_DETAILS TABLE 1,304,880 81.38 OCTOPUS OCTOPUS FCT_INV_DETAILS TABLE 148,144 9.24 OCTOPUS OCTOPUS FCT_PROCESS_STATUS TABLE 33,616 2.10 PERFSTAT USERS STATS$SQL_SUMMARY_PK INDEX 24,608 1.53 OCTOPUS OCTOPUS FCT_DEBTOR_SALES_LED TABLE 17,056 1.06 -------------------------------------------------------------
Top 5 Physical Reads per Segment for DB: OCTOPUS Instance: octopus
Snaps: 52 -
-> End Segment Physical Reads Threshold: 1000
Subobject Obj.
Physical
Owner Tablespace Object Name Name Type
Reads %Total
---------- ---------- -------------------- ---------- -----Dictionary Cache Stats for DB: OCTOPUS Instance: octopus Snaps: 52
------------ -------
OCTOPUS OCTOPUS FCT_DEBTOR_SALES_LED TABLE 14,064 94.43 OCTOPUS OCTOPUS FCT_REAL_CASH_RECEIP TABLE 405 2.72 OCTOPUS OCTOPUS BDS_BILL_DETAILS TABLE 152 1.02 OCTOPUS INDX INV_DET_BATCHDATE_CO INDEX 93 .62 OCTOPUS OCTOPUS FCT_REAL_ASSIGN_ACC TABLE 43 .29 -------------------------------------------------------------
-53
->"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_histogram_defs 1,158 20.1 0 0
2,345
dc_object_ids 2,221 1.7 0 0
504
dc_objects 408 29.2 0 0
685
dc_profiles 8 0.0 0 0
1
dc_rollback_segments 88 0.0 0 0
12
dc_segments 779 5.5 0 0
452
dc_sequences 52 0.0 0 52
3
dc_tablespace_quotas 1 100.0 0 1
1
dc_tablespaces 11,854 0.0 0 0
8
dc_user_grants 114 0.9 0 0
16
dc_usernames 88 0.0 0 0
5
dc_users 12,167 0.0 0 0
19
-------------------------------------------------------------
Library Cache Activity for DB: OCTOPUS Instance: octopus Snaps: 52
-53
->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
SQL AREA 975 8.9 21,559 0.8 31
0
TABLE/PROCEDURE 924 15.6 1,277 32.5 100
0
BODY 15 13.3 15 13.3 0
0
TRIGGER 17 0.0 17 0.0 0
0
CLUSTER 11 0.0 17 0.0 0
0
-------------------------------------------------------------
Shared Pool Advisory for DB: OCTOPUS Instance: octopus End Snap: 53
-> 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
----------- ----- ---------- ------------ ------------ -------
120 .6 124 6,177 2,935 1.0 831,843
144 .7 147 6,851 2,956 1.0 833,372
168 .8 170 7,193 2,967 1.0 834,941
192 .9 187 7,440 2,969 1.0 836,152
216 1.0 210 7,650 2,973 1.0 837,346
240 1.1 233 8,239 2,975 1.0 838,299
264 1.2 256 8,694 2,977 1.0 839,138
288 1.3 274 9,053 2,978 1.0 839,661
312 1.4 296 9,212 2,979 1.0 840,120
336 1.6 317 9,363 2,981 1.0 840,637
360 1.7 334 9,756 2,981 1.0 841,000
384 1.8 351 9,970 2,982 1.0 841,328
408 1.9 374 10,112 2,982 1.0 841,515
432 2.0 492 11,656 2,985 1.0 841,829
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 528,482,304 Fixed Size 455,356 Redo Buffers 667,648 Variable Size 520,093,696 ---------------- sum 1,049,699,004 -------------------------------------------------------------
SGA breakdown difference for DB: OCTOPUS Instance: octopus Snaps: 52 -53
Pool Name Begin value End value% Diff
------ ------------------------------ ---------------- ----------------
-------
large free memory 25,165,824 25,165,824
0.00
shared 1M buffer 2,098,176 2,098,176
0.00
shared Checkpoint queue 564,608 564,608
0.00
shared FileIdentificatonBlock 323,292 323,292
0.00
shared FileOpenBlock 695,504 695,504
0.00
shared KGK heap 3,756 3,756
0.00
shared KGLS heap 4,719,532 5,114,220
8.36
shared KQR M PO 2,547,240 2,597,420
1.97
shared KQR S PO 131,072 131,072
0.00
shared KQR S SO 4,096 4,096
0.00
shared KSXR large reply queue 166,104 166,104
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 MTTR advisory 79,368 79,368
0.00
shared PL/SQL DIANA 1,245,176 1,252,380
0.58
shared PL/SQL MPCODE 1,052,692 1,132,648
7.60
shared PL/SQL PPCODE 235,396 235,396
0.00
shared PLS non-lib hp 2,068 2,068
0.00
shared character set object 318,524 318,524
0.00
shared dictionary cache 1,610,880 1,610,880
0.00
shared enqueue 171,860 171,860
0.00
shared errors 31,768 31,768
0.00
shared event statistics per sess 1,718,360 1,718,360
0.00
shared fixed allocation callback 260 260
0.00
shared free memory 20,062,388 13,880,288
-30.81
shared joxs heap init 4,220 4,220
0.00
shared kgl simulator 1,812,620 1,812,620
0.00
shared library cache 13,080,424 13,547,992
3.57
shared message pool freequeue 834,752 834,752
0.00
shared miscellaneous 6,409,784 6,456,152
0.72
shared parameters 35,860 43,168
20.38
shared sessions 410,720 410,720
0.00
shared sim memory hea 283,796 283,796
0.00
shared sql area 180,734,480 185,861,404
2.84
shared table definiti 2,288 4,192
83.22
shared trigger defini 3,732 3,732
0.00
shared trigger inform 496 496
0.00
shared trigger source 304 304
0.00
buffer_cache 528,482,304 528,482,304
0.00
fixed_sga 455,356 455,356
0.00
log_buffer 656,384 656,384
0.00
-------------------------------------------------------------
init.ora Parameters for DB: OCTOPUS Instance: octopus Snaps: 52 -53
End
value
Parameter Name Begin value (if
different)
processes 150
timed_statistics TRUE
shared_pool_size 226492416
sga_max_size 1049699004
large_pool_size 25165824
java_pool_size 0
control_files E:\Oracle\octopus\oradata\octopus
db_block_size 8192
db_cache_size 528482304
compatible 9.2.0.0.0
log_archive_start TRUE
log_archive_dest_1 LOCATION=e:\oracle\octopus\admin\
log_archive_dest_2 SERVICE=octopus2 reopen=60
log_archive_dest_state_1 enable
log_archive_dest_state_2 ENABLE
log_archive_max_processes 5
log_archive_format ARC%S.%T
db_file_multiblock_read_count 16
fast_start_mttr_target 300
undo_management AUTO
undo_tablespace UNDOTBS1
undo_retention 10800
remote_login_passwordfile EXCLUSIVE
db_domain
instance_name octopus
hash_join_enabled TRUE
background_dump_dest E:\Oracle\octopus\admin\octopus\b
user_dump_dest E:\Oracle\octopus\admin\octopus\u
core_dump_dest E:\Oracle\octopus\admin\octopus\c
sort_area_size 524288
db_name octopus
open_cursors 1500
star_transformation_enabled FALSE
query_rewrite_enabled FALSE
pga_aggregate_target 39845888
-------------------------------------------------------------
End of Report
Here is the full report .The End Of Day completed between the two snaps
.Since direct path writes are more do I have to do anything related to
pga_aggregate_target.
Please help me .I am new to oracle .Any help will be greatly appreciated
Received on Tue Sep 12 2006 - 04:05:59 CDT
 |
 |