Re: can anyone help me tune the database

I can see couple of things from this report.

> % Blocks changed per Read: 3.26 Recursive Call %: 99.77
> Rollback per transaction %: 40.00 Rows per Sort: 86.15

> CPU time 83
> 62.08
> direct path write 16,504 43
> 32.23

There are lot of sortings took plance and you may have problems with your temp tbs. what is your sort_area_size/pga_aggregate_target value?.

Recursive Calls almost 100%. Seems you are asking for too much dictionary info. Are your tablespace DMT or LMT?

Jaffar

trameshkumar_at_gmail.com wrote:
> STATSPACK report for
>
> DB Name DB Id Instance Inst Num Release Cluster Host
> ------------ ----------- ------------ -------- ----------- -------
> ------------
> OCTOPUS 2536858586 octopus 1 9.2.0.1.0 NO
> MUMOCTOPUS1
>
> 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
> ~~~~~~~~~~~~~~~~~~
> % Total
> 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
> -> ordered by wait time desc, waits desc (idle events last)
>
> Avg
> Total Wait wait
> Waits
> Event Waits Timeouts Time (s) (ms)
> /txn
> ---------------------------- ------------ ---------- ---------- ------
> --------
> 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 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
> -> 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
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> 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
> BEGIN statspack.snap(i_snap_level=>10); END;
>
> 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
> -> 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
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> 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 procs
> tat.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.sta
> tus = '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
> DELETE TMP_EODD_DB_SALES_LEDGER
>
> -------------------------------------------------------------
> 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
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> 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
> BEGIN statspack.snap(i_snap_level=>10); END;
>
> 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
> 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;
>
> 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
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> client_account_cd, debtor_account_cd, debtor_currency, in
> voice_no, inv_amount, unapproved_amount, reason_cd, tsta
> mp, draft_save_flag, created_date, created_by, modified_
>
> 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
> -> End Executions Threshold: 100
>
> 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(b
> ldtls.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
> select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
> estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
>
> -------------------------------------------------------------
> SQL ordered by Parse Calls for DB: OCTOPUS Instance: octopus Snaps:
> 52 -53
> -> End Parse Calls Threshold: 1000
>
> % Total
> Parse Calls Executions Parses Hash Value
> ------------ ------------ -------- ----------
> 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), :t
> ime)
>
> 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$,fixedsto
> rage,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
> Parse Calls Executions Parses Hash Value
> ------------ ------------ -------- ----------
>
> 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
>
> -------------------------------------------------------------
> SQL ordered by Sharable Memory for DB: OCTOPUS Instance: octopus
> Snaps: 52 -53
> -> End Sharable Memory Threshold: 1048576
>
> 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 Second
> per 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
> Instance Activity Stats for DB: OCTOPUS Instance: octopus Snaps: 52
> -53
>
> Statistic Total per Second
> per Trans
> --------------------------------- ------------------ --------------
> ------------
> hot buffers moved to head of LRU 3,977 3.2
> 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
> Instance Activity Stats for DB: OCTOPUS Instance: octopus Snaps: 52
> -53
>
> Statistic Total per Second
> per 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)
> -------------- ------- ------ ------- ------------ -------- ----------
> ------
> TEMP
> 2 0 0.0 1.0 16,504 13 0
> 0.0
> OCTOPUS
> 1,341 1 1.8 11.1 1 0 0
> 0.0
> USERS
> 448 0 3.6 1.1 0 0 0
> 0.0
> INDX
> 120 0 1.8 1.0 0 0 0
> 0.0
> SYSTEM
> 24 0 11.3 1.0 1 0 0
> 0.0
> UNDOTBS1
> 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 Timeout
> Interval
> (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
> --- ------------ ----- ---------------- -------------
> ------------------
> 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
> -------------------------------------------------------------
> PGA Aggr Target Stats for DB: OCTOPUS Instance: octopus Snaps: 52
> -53
> -> 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
> --------------- ---------------- -------------------------
> 56.7 90 69
>
> %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 38 10 31.7 0.0 .0 .0 .0
> 1,945
> E 38 10 31.6 0.0 .0 .0 .0
> 1,945
> -------------------------------------------------------------
>
> 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
> ->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 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
> -------------------------------------------------------------
> Rollback Segment Storage for DB: OCTOPUS Instance: octopus Snaps: 52
> -53
> ->Optimal Size should be larger than Avg Active
>
> 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:
> -> 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 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
> ------------ ------------ -------- ------- -------- ------- ------
> -------------
> 23-Aug 22:43 411 8,090 67 1 0 0
> 0/0/0/0/0/0
> 23-Aug 22:33 174 7,961 1 2 0 0
> 0/0/0/0/0/0
> -------------------------------------------------------------
> Latch Activity for DB: OCTOPUS Instance: octopus Snaps: 52 -53
> ->"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 96 0.0 0
> 0
> FIB s.o chain latch 4 0.0 0
> 0
> FOB s.o list latch 40 0.0 0
> 0
> SQL memory manager latch 1 0.0 0
> 401 0.0
> SQL memory manager worka 27,112 0.0 0
> 0
> active checkpoint queue 406 0.0 0
> 0
> archive control 41 0.0 0
> 0
> archive process latch 36 0.0 0
> 0
> cache buffer handles 252 0.0 0
> 0
> cache buffers chains 3,501,374 0.0 0
> 29,995 0.0
> cache buffers lru chain 15,821 0.0 0
> 16,207 0.0
> channel handle pool latc 18 0.0 0
> 0
> channel operations paren 833 0.0 0
> 0
> checkpoint queue latch 38,874 0.0 0
> 1,368 0.0
> child cursor hash table 1,205 0.0 0
> 0
> dml lock allocation 311 0.0 0
> 0
> dummy allocation 18 0.0 0
> 0
> enqueue hash chains 6,403 0.0 0
> 0
> enqueues 5,701 0.0 0
> 0
> event group latch 9 0.0 0
> 0
> file number translation 17,077 0.0 0
> 0
> hash table column usage 33 0.0 0
> 56,581 0.0
> ktm global data 4 0.0 0
> 0
> lgwr LWN SCN 469 0.0 0
> 0
> library cache 60,838 0.0 0
> 35 0.0
> library cache load lock 518 0.0 0
> 0
> library cache pin 42,563 0.0 0
> 0
> library cache pin alloca 4,968 0.0 0
> 0
> list of block allocation 54 0.0 0
> 0
> longop free list parent 1 0.0 0
> 1 0.0
> messages 2,992 0.0 1.0 0
> 0
> mostly latch-free SCN 472 0.2 0.0 0
> 0
> multiblock read objects 2,266 0.0 0
> 0
> ncodef allocation latch 19 0.0 0
> 0
> object stats modificatio 406 0.0 0
> 0
> post/wait queue 195 0.0 0
> 7 0.0
> process allocation 9 0.0 0
> 9 0.0
> process group creation 18 0.0 0
> 0
> redo allocation 27,410 0.0 0.0 0
> 0
> redo copy 0 0
> 26,848 0.0
> redo writing 1,537 0.0 0
> 0
> row cache enqueue latch 57,322 0.0 0
> 0
> row cache objects 58,032 0.0 0
> 0
> sequence cache 443 0.0 0
> 0
> session allocation 772 0.0 0
> 0
> session idle bit 351 0.0 0
> 0
> session switching 19 0.0 0
> 0
> session timer 415 0.0 0
> 0
> shared pool 30,697 0.0 0
> 0
> sim partition latch 0 0
> 7 0.0
> Latch Activity for DB: OCTOPUS Instance: octopus Snaps: 52 -53
> ->"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
> ------------------------ -------------- ------ ------ ------
> ------------ ------
> simulator hash latch 100,572 0.0 0
> 0
> simulator lru latch 1,045 0.0 0
> 761 0.0
> sort extent pool 2,110 0.0 0
> 0
> transaction allocation 22 0.0 0
> 0
> transaction branch alloc 19 0.0 0
> 0
> undo global data 1,457 0.0 0
> 0
> user lock 32 0.0 0
> 0
> -------------------------------------------------------------
> Latch Sleep breakdown for DB: OCTOPUS Instance: octopus Snaps: 52
> -53
> -> ordered by misses desc
>
> 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
> -> ordered by name, sleeps desc
>
> 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
> -> End Segment Logical Reads Threshold: 10000
>
> 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
> ---------- ---------- -------------------- ---------- -----
> ------------ -------
> 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
> -------------------------------------------------------------
> Dictionary Cache Stats for DB: OCTOPUS Instance: octopus Snaps: 52
> -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 Memory Summary for DB: OCTOPUS Instance: octopus Snaps: 52 -53
>
> 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:39:23 CDT