Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Statspack

Re: Statspack

From: Syltrem <syltremzulu_at_videotron.ca>
Date: Wed, 28 Apr 2004 12:44:04 -0400
Message-ID: <W7Rjc.3425$Xy3.13767@tor-nn1.netcom.ca>


Go to www.oraperf.com and run it through the tool there. It`s free.

-- 
Syltrem

OpenVMS 7.3-1 + Oracle 8.1.7.4
http://pages.infinit.net/syltrem (OpenVMS related web site, en français)
---zulu is not in my email address---
"Mill" <mill.h_at_ntlworld.com> a écrit dans le message de
news:9mPjc.305$fp.243_at_newsfe6-gui.server.ntli.net...

> Have just run statspack on a oracle 8.1.7.4 DB on windows 2k with 2gb RAM,
> can anybody see
> anything in the results i need to be concerned about ?
>
> Appreciate any comments/tips
>
>
> STATSPACK report for
>
> DB Name DB Id Instance Inst Num Release OPS Host
> ------------ ----------- ------------ -------- ----------- --- -----------
-
> IFSL 3136346323 ifsl 1 8.1.7.4.1 NO ORACLE1
>
> Snap Id Snap Time Sessions
> ------- ------------------ --------
> Begin Snap: 39 28-Apr-04 13:45:06 61
> End Snap: 40 28-Apr-04 14:49:01 61
> Elapsed: 63.92 (mins)
>
> Cache Sizes
> ~~~~~~~~~~~
> db_block_buffers: 12000 log_buffer: 1572864
> db_block_size: 8192 shared_pool_size: 1000000000
>
> Load Profile
> ~~~~~~~~~~~~ Per Second Per Transaction
> --------------- ---------------
> Redo size: 50,114.59 5,430.00
> Logical reads: 5,201.10 563.55
> Block changes: 331.89 35.96
> Physical reads: 410.16 44.44
> Physical writes: 11.50 1.25
> User calls: 20.99 2.27
> Parses: 32.43 3.51
> Hard parses: 0.23 0.02
> Sorts: 18.28 1.98
> Logons: 1.06 0.12
> Executes: 931.06 100.88
> Transactions: 9.23
>
> % Blocks changed per Read: 6.38 Recursive Call %: 99.26
> Rollback per transaction %: 0.05 Rows per Sort: 24.61
>
> Instance Efficiency Percentages (Target 100%)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 99.99 Redo NoWait %: 100.00
> Buffer Hit %: 92.11 In-memory Sort %: 100.00
> Library Hit %: 99.96 Soft Parse %: 99.30
> Execute to Parse %: 96.52 Latch Hit %: 99.93
> Parse CPU to Parse Elapsd %: 53.64 % Non-Parse CPU: 98.19
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 29.23 32.70
> % SQL with executions>1: 67.37 66.00
> % Memory for SQL w/exec>1: 59.37 58.74
>
> Top 5 Wait Events
> ~~~~~~~~~~~~~~~~~ Wait %
> Total
> Event Waits Time (cs) Wt
> Time
> -------------------------------------------- ------------ ------------ ---
--
> --
> db file sequential read 822,208 167,102
> 91.75
> db file scattered read 28,633 12,524
> 6.88
> log file sequential read 2,379 541
> .30
> log file parallel write 35,877 486
> .27
> file open 133 421
> .23
> -------------------------------------------------------------
> Wait Events for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> cs - centisecond - 100th of a second
> -> ms - millisecond - 1000th of a second
> -> ordered by wait time desc, waits desc (idle events last)
>
> Avg
> Total Wait wait
> Waits
> Event Waits Timeouts Time (cs) (ms)
> /txn
> ---------------------------- ------------ ---------- ----------- ------ --
--
> --
> db file sequential read 822,208 0 167,102 2
> 23.2
> db file scattered read 28,633 0 12,524 4
> 0.8
> log file sequential read 2,379 0 541 2
> 0.1
> log file parallel write 35,877 0 486 0
> 1.0
> file open 133 0 421 32
> 0.0
> db file parallel write 2,762 0 333 1
> 0.1
> enqueue 245 0 268 11
> 0.0
> buffer busy waits 1,216 0 183 2
> 0.0
> log file sync 3,361 0 111 0
> 0.1
> log file switch completion 1 0 27 270
> 0.0
> control file sequential read 98 0 24 2
> 0.0
> file identify 14 0 22 16
> 0.0
> SQL*Net more data to client 2,533 0 20 0
> 0.1
> control file parallel write 1,258 0 16 0
> 0.0
> latch free 1,703 964 13 0
> 0.0
> direct path read 240 0 13 1
> 0.0
> direct path write 127 0 8 1
> 0.0
> refresh controlfile command 8 0 7 9
> 0.0
> log file single write 4 0 7 18
> 0.0
> SQL*Net break/reset to clien 50 0 5 1
> 0.0
> LGWR wait for redo copy 75 0 1 0
> 0.0
> row cache lock 3 0 0 0
> 0.0
> SQL*Net message from client 88,891 0 5,833,502 656
> 2.5
> SQL*Net more data from clien 2,802 0 199 1
> 0.1
> SQL*Net message to client 88,888 0 21 0
> 2.5
> -------------------------------------------------------------
> Background Wait Events for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> ordered by wait time desc, waits desc (idle events last)
>
> Avg
> Total Wait wait
> Waits
> Event Waits Timeouts Time (cs) (ms)
> /txn
> ---------------------------- ------------ ---------- ----------- ------ --
--
> --
> log file sequential read 2,379 0 541 2
> 0.1
> log file parallel write 35,877 0 486 0
> 1.0
> db file parallel write 2,762 0 333 1
> 0.1
> file open 63 0 203 32
> 0.0
> db file scattered read 224 0 190 8
> 0.0
> control file sequential read 61 0 19 3
> 0.0
> control file parallel write 1,258 0 16 0
> 0.0
> db file sequential read 41 0 15 4
> 0.0
> file identify 8 0 12 15
> 0.0
> log file single write 4 0 7 18
> 0.0
> LGWR wait for redo copy 75 0 1 0
> 0.0
> direct path read 14 0 0 0
> 0.0
> direct path write 14 0 0 0
> 0.0
> latch free 4 4 0 0
> 0.0
> rdbms ipc message 122,517 3,715 1,668,818 136
> 3.5
> pmon timer 1,248 1,246 383,233 3071
> 0.0
> smon timer 13 13 368,644 ######
> 0.0
> -------------------------------------------------------------
> SQL ordered by Gets for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> 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
>
> Buffer Gets Executions Gets per Exec % Total Hash Value
> --------------- ------------ -------------- ------- ------------
> 8,513,149 80 106,414.4 42.7 3942090408
> DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
> broken BOOLEAN := FALSE; BEGIN Transaction_SYS.Process_All_Pendi
> ng__(2,'Heavy Queue','en',job); :mydate := next_date; IF broken
> THEN :b := 1; ELSE :b := 0; END IF; END;
>
> 8,501,059 1 8,501,059.0 42.6 1773115984
> BEGIN Mrp_Process_API.Do_Mrp_Process__(:attr_); END;
>
> 3,242,042 1 3,242,042.0 16.3 3366001589
> /* OracleOEM */ SELECT d.status "Status", d.tablespace_name "Nam
> e", d.contents "Type", d.extent_management "Extent Management",
> TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M
> )", NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 ||'/'||NVL(a.byt
> es/1024/1024, 0) "Used (M)", TO_CHAR(NVL((a.bytes - NVL(f.bytes,
>
> 2,872,008 3,496 821.5 14.4 478919936
> BEGIN Purchase_Req_Util_API.New_Line_Part(
> line_no_ => :line_no, release_no_
> => :release_no, requisition_no_ => :requ
> is_no, contract_ => :contract,
> part_no_ => :part_no, unit_meas_
>
> 1,768,483 2 884,241.5 8.9 2889509539
> select OBJID, OBJVERSION, ORDER_NO, CONTRACT, LINE_NO, RELEASE_N
> O, IFSAPP.PURCHASE_ORDER_API.Get_Vendor_No(order_no), IFSAPP.Sup
> plier_API.Get_Vendor_Name(IFSAPP.Purchase_Order_API.Get_Vendor_N
> o(order_no)), IFSAPP.PURCHASE_ORDER_API.Get_Buyer_Code(order_no)
> , IFSAPP.PURCHASE_ORDER_API.Get_Authorize_Code(order_no), STATE,
>
> 1,609,356 532,776 3.0 8.1 3833091949
> SELECT BUYER_CODE FROM PURCHASE_ORDER_TAB WHERE ORDER_NO = :b
> 1
>
> 1,354,740 1 1,354,740.0 6.8 1733251761
> BEGIN FOR cur_rec in Purchase_Order_Line_Part_API.Get_
> Open_Pur_Supply(:part_no, :contract) LOOP IF (Mrp_P
> art_API.Check_Exist(:contract, cur_rec.part_no)) THEN
> IF (cur_rec.open_po_flag = 1) THEN :mrp
> _status := :mrp_status_cl_open; ELSE
>
> 1,333,596 1 1,333,596.0 6.7 1353515300
> SELECT POL.PART_NO,POL.PLANNED_DELIVERY_DATE,POL.ORDER_NO,POL.LI
> NE_NO,POL.RELEASE_NO,POL.OBJSTATE,(PURCHASE_ORDER_LINE_PART_API.
> GET_DUE_IN_STORES(POL.ORDER_NO,POL.LINE_NO,POL.RELEASE_NO) - POL
> .QTY_ON_ORDER ) QTY_REMAINING,DECODE(POL.OBJSTATE,'Released',0,1
> ),POL.PLANNED_RECEIPT_DATE FROM PURCHASE_ORDER_LINE_PART POL
>
> 900,230 5,613 160.4 4.5 1200619234
> SELECT 'X' FROM PURCHASE_REQ_LINE_ALL WHERE REQUISITION_NO =
> :b1 AND OBJSTATE = 'Released'
> SQL ordered by Gets for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> 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
>
> Buffer Gets Executions Gets per Exec % Total Hash Value
> --------------- ------------ -------------- ------- ------------
>
> 798,951 1 798,951.0 4.0 2905441001
> SELECT SMA.PART_NO,SMA.DATE_REQUIRED,(SMA.QTY_REQUIRED - SMA.QTY
> _ISSUED + SMA.QTY_ON_ORDER ),SO.PART_NO,SO.REVISED_QTY_DUE,SO.R
> EVISED_DUE_DATE,SMA.ORDER_NO,SMA.RELEASE_NO,SMA.SEQUENCE_NO,SMA.
> LINE_ITEM_NO FROM SHOP_MATERIAL_ALLOC_TAB SMA,SHOP_ORD_TAB SO
> WHERE SMA.QTY_REQUIRED > (SMA.QTY_ISSUED + SMA.QTY_ON_ORDER )
>
> 753,348 188,337 4.0 3.8 1544149686
> SELECT ROWSTATE FROM PURCHASE_ORDER_LINE_TAB WHERE ORDER_NO =
> :b1 AND LINE_NO = :b2 AND RELEASE_NO = :b3
>
> 679,245 6 113,207.5 3.4 645788721
> select OBJID, OBJVERSION, VENDOR_NO, ORDER_NO, PART_NO, DESCRIPT
> ION, QTY_ARRIVED - NVL(QTY_INVOICED,0) - NVL(RETURNED_REWORK,0)
> - NVL(RETURNED_CREDIT,0) - NVL(QTY_CONSIGNMENT,0), ARRIVAL_DATE
> , CHARGE_TYPE, IFSAPP.PURCHASE_CHARGE_TYPE_API.Get_Description(C
> ONTRACT, CHARGE_TYPE), (QTY_ARRIVED - QTY_INVOICED - RETURNED_R
>
> 478,449 4,277 111.9 2.4 4216269827
> SELECT MAX(TO_NUMBER(LINE_NO)) FROM PURCHASE_REQ_LINE_PART WH
> ERE REQUISITION_NO = :b1 AND PART_NO = :b2 AND CONTRACT = :b3
> AND OBJSTATE != 'PO Created'
>
> 416,115 138,705 3.0 2.1 3546174284
> SELECT COUNTER FROM WORK_TIME_COUNTER_TAB WHERE WORK_DAY = :b
> 1 AND CALENDAR_ID = :b2
>
> 393,228 196,623 2.0 2.0 592256751
> SELECT 1 FROM INVENTORY_PART_TAB WHERE CONTRACT = :b1 AND PA
> RT_NO = :b2
>
> 338,690 169,352 2.0 1.7 3420518214
> SELECT ROWSTATE FROM WORK_TIME_CALENDAR_TAB WHERE CALENDAR_ID
> = :b1
>
> 338,517 307 1,102.7 1.7 1029202190
> BEGIN Purchase_Req_Util_API.Activate_Requisition(
> requisition_no_ => :requis_no ); END
> ;
>
> 331,514 1 331,514.0 1.7 3659986699
> SELECT "SALES_PART_NO_AND_DESC\\"."PART_DESC", "CUSTOMER_ORDER_J
> OIN\"."ORDER_NO", "CUSTOMER_ORDER_JOIN\"."CUSTOMER_NO", "CUSTOME
> R_ORDER_JOIN\"."WANTED_DELIVERY_DATE", "CUSTOMER_ORDER_JOIN\"."B
> UY_QTY_DUE", "CUSTOMER_ORDER_JOIN\"."BASE_SALE_UNIT_PRICE", "CUS
> TOMER_ORDER_JOIN\"."LINE_STATE", "CUST_ORD_CUSTOMER_ADDRESS_ENT\
>
> 326,292 60 5,438.2 1.6 731913234
> select DN.ORDER_NO, CONTRACT, STATE, ORDER_ID, CUSTOMER_NO, IFSA
>
> SQL ordered by Gets for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> 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
>
> Buffer Gets Executions Gets per Exec % Total Hash Value
> --------------- ------------ -------------- ------- ------------
> -------------------------------------------------------------
> SQL ordered by Reads for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> End Disk Reads Threshold: 1000
>
> Physical Reads Executions Reads per Exec % Total Hash Value
> --------------- ------------ -------------- ------- ------------
> 438,982 80 5,487.3 27.9 3942090408
> DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
> broken BOOLEAN := FALSE; BEGIN Transaction_SYS.Process_All_Pendi
> ng__(2,'Heavy Queue','en',job); :mydate := next_date; IF broken
> THEN :b := 1; ELSE :b := 0; END IF; END;
>
> 438,906 1 438,906.0 27.9 1773115984
> BEGIN Mrp_Process_API.Do_Mrp_Process__(:attr_); END;
>
> 321,291 60 5,354.9 20.4 731913234
> select DN.ORDER_NO, CONTRACT, STATE, ORDER_ID, CUSTOMER_NO, IFSA
> PP.Cust_Ord_Customer_API.Get_Name(CUSTOMER_NO), AUTHORIZE_CODE,
> SALESMAN_CODE, WANTED_DELIVERY_DATE, ROUTE_ID, IFSAPP.Customer_O
> rder_Route_API.Get_Description(ROUTE_ID), CO.PRIORITY
>
>
> 302,427 1 302,427.0 19.2 2905441001
> SELECT SMA.PART_NO,SMA.DATE_REQUIRED,(SMA.QTY_REQUIRED - SMA.QTY
> _ISSUED + SMA.QTY_ON_ORDER ),SO.PART_NO,SO.REVISED_QTY_DUE,SO.R
> EVISED_DUE_DATE,SMA.ORDER_NO,SMA.RELEASE_NO,SMA.SEQUENCE_NO,SMA.
> LINE_ITEM_NO FROM SHOP_MATERIAL_ALLOC_TAB SMA,SHOP_ORD_TAB SO
> WHERE SMA.QTY_REQUIRED > (SMA.QTY_ISSUED + SMA.QTY_ON_ORDER )
>
> 75,505 18 4,194.7 4.8 3008101071
> SELECT ORDER_NO,DELNOTE_DATE,AUTHORIZE_NAME,CUSTOMER_NO,CUSTOME
> R_PO_NO,CUST_REF,SHIP_ADDR_1,SHIP_ADDR_2,SHIP_ADDR_3,SHIP_ADDR_4
> ,SHIP_ADDR_5,SHIP_ADDR_6,SHIP_COUNTRY,BILL_ADDR_1,BILL_ADDR_2,BI
> LL_ADDR_3,BILL_ADDR_4,BILL_ADDR_5,BILL_ADDR_6,BILL_COUNTRY,SHIP_
> VIA_DESC,FORWARD_AGENT,LABEL_NOTE,DELIVERY_TERMS_DESC,WANTED_DEL
>
> 75,408 1 75,408.0 4.8 2602164364
> BEGIN FOR cur_rec in Customer_Order_API.Get_Order_Dema
> nd (:contract, :part_no) LOOP IF (Mrp_Part_API.Chec
> k_Exist(:contract, cur_rec.part_no)) THEN Mrp_Pa
> rt_Supply_Demand_API.New( contract_
> => :contract, part_no_ => cur_rec.p
>
> 75,373 1 75,373.0 4.8 2316491747
> SELECT PART_NO,ORDER_NO,LINE_NO,REL_NO,LINE_ITEM_NO,PLANNED_DUE_
> DATE,REVISED_QTY_DUE - (QTY_SHIPPED - QTY_SHIPDIFF + QTY_ON_ORD
> ER ) REMAINING_QTY_DUE FROM CUSTOMER_ORDER_LINE_TAB LINE WHE
> RE CONTRACT = :b1 AND PART_NO LIKE NVL(:b2,'%') AND LINE_ITEM
> _NO != -1 AND ROWSTATE NOT IN ( 'Planned','Invoiced','Cancelled
>
> 69,940 26 2,690.0 4.4 344082537
> select ORDER_NO, LINE_NO, RELEASE_NO, RECEIPT_NO, CONTRACT, PART
> _NO, DESCRIPTION, IFSAPP.PART_CATALOG_API.GET_CONFIGURABLE_DB(PA
> RT_NO), VENDOR_NO, IFSAPP.SUPPLIER_API.Get_Vendor_Name(VENDOR_NO
> ), QTY_IN_STORE / CONV_FACTOR, PALLET_ID, IFSAPP.Transport_Task_
> API.Get_Task_Id_Created_For_Pallet(PALLET_ID), BUY_UNIT_MEAS, QT
>
> 62,079 4 15,519.8 3.9 1738551072
> BEGIN IFSAPP.Receive_Purchase_Order_API.Packed_Arrival__( :a1
> ,:a2
> ,:a3 ,:a4
> SQL ordered by Reads for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> End Disk Reads Threshold: 1000
>
> Physical Reads Executions Reads per Exec % Total Hash Value
> --------------- ------------ -------------- ------- ------------
> ,:a5 ,:a6
> ); END;
>
> 45,023 1 45,023.0 2.9 1917948453
> SELECT DISTINCT "CUSTOMER_ORDER_LINE"."PURCHASE_PART_NO" FROM "I
> FSAPP"."CUSTOMER_ORDER_LINE" "CUSTOMER_ORDER_LINE"
>
> 41,547 26 1,598.0 2.6 3269668770
> SELECT COUNT(*) FROM SHORTAGE_DEMAND WHERE PART_NO = :b1 AND
> CONTRACT = :b2
>
> 39,029 5 7,805.8 2.5 2590194289
> SELECT NVL(COST,0) FROM INVENTORY_TRANSACTION_HIST_TAB WHERE
> ORDER_NO = :b1 AND NVL(RELEASE_NO,'DUMMY') = NVL(:b2,NVL(RELEAS
> E_NO,'DUMMY')) AND NVL(SEQUENCE_NO,'DUMMY') = NVL(:b3,NVL(SEQUE
> NCE_NO,'DUMMY')) AND NVL(LINE_ITEM_NO,-9999) = NVL(:b4,NVL(LINE
> _ITEM_NO,-9999)) AND ORDER_TYPE = :b5 AND TRANSACTION_CODE = :
>
> 38,444 10 3,844.4 2.4 3591110083
> select ORDER_NO, LINE_NO, RELEASE_NO, RECEIPT_NO, CONTRACT, PART
> _NO, DESCRIPTION, IFSAPP.PART_CATALOG_API.GET_CONFIGURABLE_DB(PA
> RT_NO), VENDOR_NO, IFSAPP.SUPPLIER_API.Get_Vendor_Name(VENDOR_NO
> ), QTY_ARRIVED, BUY_UNIT_MEAS, QTY_ARRIVED * CONV_FACTOR, UNIT_M
> EAS, QTY_INSPECTED, QTY_TO_INSPECT, NO_OF_INSPECTIONS, IFSAPP.Re
>
> 37,418 1 37,418.0 2.4 4029665038
> SELECT "CUSTOMER_ORDER_INV_ITEM"."CATALOG_NO", "CUSTOMER_ORDER_L
> INE"."WANTED_DELIVERY_DATE" FROM "IFSAPP"."CUSTOMER_ORDER_INV_IT
> EM" "CUSTOMER_ORDER_INV_ITEM", "IFSAPP"."CUSTOMER_ORDER_LINE" "C
> USTOMER_ORDER_LINE" WHERE "CUSTOMER_ORDER_INV_ITEM"."ORDER_NO" =
> "CUSTOMER_ORDER_LINE"."ORDER_NO" AND "CUSTOMER_ORDER_INV_ITEM".
>
> 34,221 60 570.4 2.2 3756641286
> SELECT NVL(SUM(QTY_SUPPLY - QTY_DEMAND ),0) FROM CUSTORD_SUPPL
> Y_DEMAND WHERE TRUNC(DATE_REQUIRED) <= :b1 AND CONTRACT = :b2
> AND PART_NO = :b3 AND (CONFIGURATION_ID = :b4 OR :b4 IS NULL
> ) AND ROW_ID != NVL(:b6,'ROWID')
>
> 31,377 2 15,688.5 2.0 2889509539
> select OBJID, OBJVERSION, ORDER_NO, CONTRACT, LINE_NO, RELEASE_N
>
> -------------------------------------------------------------
> SQL ordered by Executions for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> End Executions Threshold: 100
>
> Executions Rows Processed Rows per Exec Hash Value
> ------------ ---------------- ---------------- ------------
> 532,776 532,776 1.0 3833091949
> SELECT BUYER_CODE FROM PURCHASE_ORDER_TAB WHERE ORDER_NO = :b
> 1
>
> 196,623 188,560 1.0 592256751
> SELECT 1 FROM INVENTORY_PART_TAB WHERE CONTRACT = :b1 AND PA
> RT_NO = :b2
>
> 188,337 188,337 1.0 1544149686
> SELECT ROWSTATE FROM PURCHASE_ORDER_LINE_TAB WHERE ORDER_NO =
> :b1 AND LINE_NO = :b2 AND RELEASE_NO = :b3
>
> 169,352 169,345 1.0 3420518214
> SELECT ROWSTATE FROM WORK_TIME_CALENDAR_TAB WHERE CALENDAR_ID
> = :b1
>
> 138,705 138,705 1.0 3546174284
> SELECT COUNTER FROM WORK_TIME_COUNTER_TAB WHERE WORK_DAY = :b
> 1 AND CALENDAR_ID = :b2
>
> 111,081 110,476 1.0 3456764171
> SELECT COMPANY FROM SITE_TAB WHERE CONTRACT = :b1
>
> 98,029 97,342 1.0 2060504531
> SELECT 1 FROM USER_PROFILE_SYS_TAB WHERE ENTRY_CODE = :b1 AN
> D USER_NAME = :b2 AND INSTR('^' || VALUE_LIST ,'^' || :b3 || '
> ^' ) > 0
>
> 69,924 69,924 1.0 1581714939
> SELECT SUM(QTY_RETURNED) FROM PURCHASE_RECEIPT_RETURN WHERE O
> RDER_NO = :b1 AND LINE_NO = :b2 AND RELEASE_NO = :b3 AND RECE
> IPT_NO = :b4 AND RECEIPT_RETURN_TYPE_DB = 'CREDIT'
>
> 69,924 69,924 1.0 2809795541
> SELECT SUM(QTY_RETURNED) FROM PURCHASE_RECEIPT_RETURN WHERE O
> RDER_NO = :b1 AND LINE_NO = :b2 AND RELEASE_NO = :b3 AND RECE
> IPT_NO = :b4 AND RECEIPT_RETURN_TYPE_DB = 'REWORK'
>
> 50,816 50,816 1.0 285171515
> SELECT DIST_CALENDAR_ID FROM SITE_TAB WHERE CONTRACT = :b1
>
> 48,988 48,383 1.0 2888610502
> SELECT CURRENCY_CODE FROM COMPANY_FINANCE_TAB WHERE COMPANY =
> :b1
>
> 45,824 45,820 1.0 693618453
> SELECT CURRENCY_ROUNDING,INVERTED,EMU_CURRENCY_FROM_DATE FROM
> CURRENCY_CODE_TAB WHERE COMPANY = :b1 AND CURRENCY_CODE = :b2
>
> 45,205 45,194 1.0 3652095511
> SELECT MANUF_CALENDAR_ID FROM SITE_TAB WHERE CONTRACT = :b1
>
> 42,379 42,379 1.0 1010136994
> SELECT OFFSET FROM SITE_TAB WHERE CONTRACT = :b1
>
> SQL ordered by Executions for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> End Executions Threshold: 100
>
> Executions Rows Processed Rows per Exec Hash Value
> ------------ ---------------- ---------------- ------------
> 31,582 31,579 1.0 3845364290
> SELECT CONFIGURABLE FROM PART_CATALOG_TAB WHERE PART_NO = :b1
>
>
> 29,916 29,916 1.0 2500263242
> SELECT WORK_DAY FROM WORK_TIME_COUNTER_TAB WHERE CALENDAR_ID
> = :b1 AND COUNTER = :b2
>
> 25,054 25,054 1.0 3148415563
> INSERT INTO MRP_PART_SUPPLY_DEMAND_TAB ( CONTRACT,PART_NO,SUPPLY
> _DEMAND_SEQ,ORDER_NO,LINE_NO,RELEASE_NO,LINE_ITEM_NO,REQUIRED_DA
> TE,REQUIRED_COUNTER,DEMAND_QTY,SUPPLY_QTY,RELEASE_QTY,PARENT_PAR
> T_NO,PARENT_DATE,PARENT_COUNTER,PARENT_QTY,MRP_STATUS,MRP_SOURCE
> ,ROWVERSION ) VALUES ( :b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10
>
> 23,056 21,476 0.9 1361903089
> SELECT 1 FROM MRP_PART_TAB WHERE CONTRACT = :b1 AND PART_NO
> = :b2
>
> 21,978 14,497 0.7 796627733
> SELECT * FROM MRP_PART_EVENT_TAB WHERE CONTRACT = :b1 AND PA
> RT_NO = :b2 AND EVENT_COUNTER = :b3
>
> 18,764 18,641 1.0 1970486164
> SELECT TECHNICAL_COORDINATOR_ID FROM INVENTORY_PART_TAB WHERE
> CONTRACT = :b1 AND PART_NO = :b2
>
> 18,565 18,565 1.0 77501357
> SELECT TECHNICAL_COORDINATOR_ID FROM PURCHASE_PART_TAB WHERE
> CONTRACT = :b1 AND PART_NO = :b2
>
> 17,687 17,687 1.0 1051083899
> SELECT CHAR_VALUE FROM MPCCOM_DEFAULTS_TAB WHERE TRANSACTION
> = :b1 AND TABLE_NAME = :b2 AND COLUMN_NAME = :b3
>
> 17,397 123 0.0 3109831238
> SELECT DESCRIPTION FROM ORDER_PROC_TYPE_TAB WHERE PROCESS_TYP
> E = :b1
>
> 17,198 17,198 1.0 792260452
> SELECT 1 FROM PURCHASE_PART_SUPPLIER_TAB WHERE CONTRACT = :b1
> AND PART_NO = :b2 AND VENDOR_NO = :b3
>
> 16,168 4,628 0.3 11428330
> SELECT * FROM ARCHIVE_DISTRIBUTION WHERE RESULT_KEY = :b1 AN
> D USER_NAME = :b2
>
> 15,333 7,646 0.5 1242502328
> SELECT NAME FROM PERSON_INFO_TAB WHERE PERSON_ID = :b1
>
> 14,681 1 0.0 1093061670
> SELECT ROWID OBJID,ROWVERSION FROM SHOP_ORDER_PROP_TAB WHERE
> PART_NO LIKE :b1 AND CONTRACT LIKE :b2 AND ORG_COUNTER = NVL
> (:b3,ORG_COUNTER) AND SHOP_PROPOSAL_TYPE = :b4 AND ROWSTATE =
> 'ProposalCreated'
> SQL ordered by Executions for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> End Executions Threshold: 100
>
> Executions Rows Processed Rows per Exec Hash Value
> ------------ ---------------- ---------------- ------------
>
> 14,534 14,411 1.0 2872921204
> SELECT TYPE_DESIGNATION FROM INVENTORY_PART_TAB WHERE CONTRAC
> T = :b1 AND PART_NO = :b2
>
> 14,531 14,531 1.0 2345754342
> SELECT CONTRACT_REF FROM SITE_TAB WHERE CONTRACT = :b1
>
> 14,530 14,407 1.0 403258188
> SELECT DIM_QUALITY FROM INVENTORY_PART_TAB WHERE CONTRACT = :
> b1 AND PART_NO = :b2
>
> 14,523 14,523 1.0 1425443843
> update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,
> order$=:6,cache=:7,highwater=:8,audit$=:9 where obj#=:1
>
> -------------------------------------------------------------
> SQL ordered by Sharable Memory for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> End Sharable Memory Threshold: 1048576
>
> Sharable Mem (b) Executions % Total Hash Value
> ---------------- ------------ ------- ------------
> 3,045,532 62 0.3 3740750831
> select DATE_REQUIRED, ORDER_SUPPLY_DEMAND_TYPE, ORDER_NO, LINE_N
> O, REL_NO, LINE_ITEM_NO, PART_NO, CONTRACT, STATUS_DESC, INFO, Q
> TY_SUPPLY, QTY_DEMAND, QTY_SHORT, NULL, IFSAPP.ORDER_SUPPLY_DEMA
> ND_API.Get_Qty_Plannable_Fast(CONTRACT, PART_NO, CONFIGURATION_I
> D, :1 , :2
>
> 1,342,900 63 0.1 2541623304
> select OBJID, OBJVERSION, OBJSTATE, OBJEVENTS, ORDER_NO, CONTRAC
> T, VENDOR_NO, NOTE_ID, CURRENCY_CODE, OBJSTATE, DATE_ENTERED, LI
> NE_NO, RELEASE_NO, PART_NO, DESCRIPTION, ENG_CHG_LEVEL, BUY_QTY_
> DUE, BUY_UNIT_MEAS, IFSAPP.SUPP_BLK_PART_API.GET_STANDARD_ORDER_
> QTY(BLANKET_ORDER,BLANKET_LINE), FBUY_UNIT_PRICE, FBUY_UNIT_PRIC
>
> 1,309,520 52 0.1 1275831104
> select OBJID, OBJVERSION, PART_NO, IFSAPP.Inventory_Part_API.Get
> _Description(CONTRACT, PART_NO), CONTRACT, CONFIGURATION_ID, IFS
> APP.Inventory_Part_API.Get_Unit_Meas(CONTRACT, PART_NO), IFSAPP.
> Inventory_Part_API.Get_Planner_Buyer(CONTRACT, PART_NO), IFSAPP.
> Inventory_Part_API.Get_Manuf_Leadtime(CONTRACT, PART_NO), IFSAPP
>
> 1,306,196 8 0.1 1864488067
> select OBJID, OBJVERSION, PART_NO, IFSAPP.Inventory_Part_API.Get
> _Description(CONTRACT, PART_NO), CONTRACT, CONFIGURATION_ID, IFS
> APP.Inventory_Part_API.Get_Unit_Meas(CONTRACT, PART_NO), IFSAPP.
> Inventory_Part_API.Get_Planner_Buyer(CONTRACT, PART_NO), IFSAPP.
> Inventory_Part_API.Get_Manuf_Leadtime(CONTRACT, PART_NO), IFSAPP
>
> 1,096,152 9 0.1 1816652986
> select OBJID, OBJVERSION, OBJSTATE, OBJEVENTS, VENDOR_NO, CURREN
> CY_CODE, ORDER_NO, NOTE_ID, PRE_ACCOUNTING_ID, CONTRACT, CURRENC
> Y_RATE, DATE_ENTERED, LINE_NO, RELEASE_NO, DESCRIPTION, ASSORTME
> NT, IFSAPP.SUPPLIER_ASSORTMENT_API.Get_Description(ASSORTMENT),
> STAT_GRP, IFSAPP.PURCHASE_PART_GROUP_API.Get_Description(STAT_GR
>
> -------------------------------------------------------------
> Instance Activity Stats for DB: IFSL Instance: ifsl Snaps: 39 -40
>
> Statistic Total per Second per
Trans
> --------------------------------- ---------------- ------------ ----------
--
> CPU used by this session 49,280 12.9
1.4
> CPU used when call started 49,257 12.8
1.4
> CR blocks created 5,098 1.3
0.1
> Cached Commit SCN referenced 1 0.0
0.0
> Commit SCN cached 0 0.0
0.0
> DBWR buffers scanned 259,526 67.7
7.3
> DBWR checkpoint buffers written 33,936 8.9
1.0
> DBWR checkpoints 1 0.0
0.0
> DBWR free buffers found 253,383 66.1
7.2
> DBWR lru scans 1,792 0.5
0.1
> DBWR make free requests 2,302 0.6
0.1
> DBWR revisited being-written buff 0 0.0
0.0
> DBWR summed scan depth 259,526 67.7
7.3
> DBWR transaction table writes 5,791 1.5
0.2
> DBWR undo block writes 15,581 4.1
0.4
> SQL*Net roundtrips to/from client 88,798 23.2
2.5
> background checkpoints completed 1 0.0
0.0
> background checkpoints started 1 0.0
0.0
> background timeouts 4,759 1.2
0.1
> branch node splits 1 0.0
0.0
> buffer is not pinned count 14,427,092 3,762.0
407.6
> buffer is pinned count 39,333,972 10,256.6
1,111.3
> bytes received via SQL*Net from c 24,208,268 6,312.5
684.0
> bytes sent via SQL*Net to client 31,478,627 8,208.3
889.4
> calls to get snapshot scn: kcmgss 3,617,571 943.3
102.2
> calls to kcmgas 50,288 13.1
1.4
> calls to kcmgcs 2,654 0.7
0.1
> change write time 729 0.2
0.0
> cleanouts and rollbacks - consist 650 0.2
0.0
> cleanouts only - consistent read 1,149 0.3
0.0
> cluster key scan block gets 3,246,545 846.6
91.7
> cluster key scans 26,857 7.0
0.8
> commit cleanout failures: block l 497 0.1
0.0
> commit cleanout failures: buffer 7 0.0
0.0
> commit cleanout failures: callbac 0 0.0
0.0
> commit cleanout failures: cannot 207 0.1
0.0
> commit cleanouts 188,426 49.1
5.3
> commit cleanouts successfully com 187,715 49.0
5.3
> consistent changes 18,970 5.0
0.5
> consistent gets 18,587,889 4,846.9
525.2
> current blocks converted for CR
> cursor authentications 590 0.2
0.0
> data blocks consistent reads - un 18,224 4.8
0.5
> db block changes 1,272,808 331.9
36.0
> db block gets 1,358,314 354.2
38.4
> deferred (CURRENT) block cleanout 82,665 21.6
2.3
> dirty buffers inspected 2,521 0.7
0.1
> enqueue conversions 3,812 1.0
0.1
> enqueue releases 129,619 33.8
3.7
> enqueue requests 131,827 34.4
3.7
> enqueue timeouts 2,194 0.6
0.1
> enqueue waits 0 0.0
0.0
> execute count 3,570,619 931.1
100.9
> free buffer inspected 2,572 0.7
0.1
> free buffer requested 1,588,596 414.2
44.9
> hot buffers moved to head of LRU 306,611 80.0
8.7
> Instance Activity Stats for DB: IFSL Instance: ifsl Snaps: 39 -40
>
> Statistic Total per Second per
Trans
> --------------------------------- ---------------- ------------ ----------
--
> immediate (CR) block cleanout app 1,799 0.5
0.1
> immediate (CURRENT) block cleanou 4,325 1.1
0.1
> index fast full scans (full) 22 0.0
0.0
> leaf node splits 288 0.1
0.0
> logons cumulative 4,074 1.1
0.1
> messages received 62,420 16.3
1.8
> messages sent 62,420 16.3
1.8
> no buffer to keep pinned count 0 0.0
0.0
> no work - consistent read gets 9,498,398 2,476.8
268.4
> opened cursors cumulative 97,692 25.5
2.8
> opened cursors current
> parse count (hard) 873 0.2
0.0
> parse count (total) 124,373 32.4
3.5
> parse time cpu 891 0.2
0.0
> parse time elapsed 1,661 0.4
0.1
> physical reads 1,572,967 410.2
44.4
> physical reads direct 2,062 0.5
0.1
> physical writes 44,107 11.5
1.3
> physical writes direct 2,062 0.5
0.1
> physical writes non checkpoint 26,502 6.9
0.8
> pinned buffers inspected 20 0.0
0.0
> prefetched blocks 720,072 187.8
20.3
> prefetched blocks aged out before 39 0.0
0.0
> process last non-idle time ################ ############
############
> recursive calls 10,792,145 2,814.1
304.9
> recursive cpu usage 17,023 4.4
0.5
> redo blocks written 399,910 104.3
11.3
> redo buffer allocation retries 1 0.0
0.0
> redo entries 635,793 165.8
18.0
> redo log space requests 1 0.0
0.0
> redo log space wait time 27 0.0
0.0
> redo ordering marks 1 0.0
0.0
> redo size 192,189,436 50,114.6
5,430.0
> redo synch time 111 0.0
0.0
> redo synch writes 3,316 0.9
0.1
> redo wastage 5,996,304 1,563.6
169.4
> redo write time 1,356 0.4
0.0
> redo writer latching time 1 0.0
0.0
> redo writes 35,878 9.4
1.0
> rollback changes - undo records a 57 0.0
0.0
> rollbacks only - consistent read 5,021 1.3
0.1
> rows fetched via callback 2,486,672 648.4
70.3
> session connect time ################ ############
############
> session cursor cache count 208 0.1
0.0
> session cursor cache hits 67,094 17.5
1.9
> session logical reads 19,946,205 5,201.1
563.6
> session pga memory 18,674,530,308 4,869,499.4
527,618.5
> session pga memory max 34,253,469,788 8,931,804.4
967,776.2
> session uga memory 25,556,260 6,664.0
722.1
> session uga memory max 469,880,492 122,524.3
13,275.7
> sorts (disk) 2 0.0
0.0
> sorts (memory) 70,119 18.3
2.0
> sorts (rows) 1,725,965 450.1
48.8
> summed dirty queue length 2,329 0.6
0.1
> switch current to new buffer
> table fetch by rowid 24,462,533 6,378.8
691.2
> Instance Activity Stats for DB: IFSL Instance: ifsl Snaps: 39 -40
>
> Statistic Total per Second per
Trans
> --------------------------------- ---------------- ------------ ----------
--
> table fetch continued row 13,272 3.5
0.4
> table scan blocks gotten 881,671 229.9
24.9
> table scan rows gotten 60,868,137 15,871.7
1,719.7
> table scans (long tables) 270 0.1
0.0
> table scans (short tables) 40,295 10.5
1.1
> total file opens 133 0.0
0.0
> transaction rollbacks 42 0.0
0.0
> transaction tables consistent rea 3 0.0
0.0
> transaction tables consistent rea 627 0.2
0.0
> user calls 80,483 21.0
2.3
> user commits 35,376 9.2
1.0
> user rollbacks 18 0.0
0.0
> write clones created in foregroun 48 0.0
0.0
> -------------------------------------------------------------
> Tablespace IO Stats for DB: IFSL Instance: ifsl Snaps: 39 -40
> ->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)
> -------------- ------- ------ ------- ------------ -------- ---------- ---
--
> -
> IFSAPP_DATA
> 787,219 205 2.0 1.8 6,829 2 154
> 3.4
> IFSAPP_INDEX
> 52,070 14 4.0 1.0 11,751 3 119
> 5.7
> ROLLBACK
> 1,476 0 2.3 1.0 21,373 6 199
> 0.0
> SYSTEM
> 4,766 1 5.9 2.5 899 0 742
> 0.8
> PERFSTAT
> 3,674 1 4.2 1.0 1,056 0 0
> 0.0
> IFSAPP_REPORT_DATA
> 1,649 0 8.8 58.1 144 0 0
> 0.0
> TEMP1
> 245 0 7.2 8.4 263 0 0
> 0.0
> OEM_REPOSITORY
> 3 0 570.0 1.0 1 0 0
> 0.0
> IFSAPP_ARCHIVE_DATA
> 1 0 ###### 1.0 1 0 0
> 0.0
> IFSAPP_ARCHIVE_INDEX
> 1 0 ###### 1.0 1 0 0
> 0.0
> IFSAPP_REPORT_INDEX
> 1 0 ###### 1.0 1 0 0
> 0.0
> RF_SCANNING
> 1 0 ###### 1.0 1 0 0
> 0.0
> USERS
> 1 0 ###### 1.0 1 0 0
> 0.0
> -------------------------------------------------------------
> File IO Stats for DB: IFSL Instance: ifsl Snaps: 39 -40
> ->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)
> -------------- ------- ------ ------- ------------ -------- ---------- ---
--
> -
> IFSAPP_ARCHIVE_DATA E:\ORADB\IFSL\IFSAPP_ARCHIVE_DATA01.DBF
> 1 0 ###### 1.0 1 0 0
>
> IFSAPP_ARCHIVE_INDEX E:\ORADB\IFSL\IFSAPP_ARCHIVE_INDEX01.DBF
> 1 0 ###### 1.0 1 0 0
>
> IFSAPP_DATA D:\ORADB\IFSL\IFSAPP_DATA2.ORA
> 286,338 75 2.0 1.2 1,157 0 10
> 2.0
> E:\ORADB\IFSL\IFSAPP_DATA01.DBF
> 500,881 131 1.9 2.1 5,672 1 144
> 3.5
>
> IFSAPP_INDEX E:\ORADB\IFSL\IFSAPP_INDEX01.DBF
> 52,070 14 4.0 1.0 11,751 3 119
> 5.7
>
> IFSAPP_REPORT_DATA E:\ORADB\IFSL\IFSAPP_REPORT_DATA01.DBF
> 1,649 0 8.8 58.1 144 0 0
>
> IFSAPP_REPORT_INDEX E:\ORADB\IFSL\IFSAPP_REPORT_INDEX01.DBF
> 1 0 ###### 1.0 1 0 0
>
> OEM_REPOSITORY D:\ORADB\IFSL\OEM_REPOSITORY.ORA
> 3 0 570.0 1.0 1 0 0
>
> PERFSTAT E:\ORADB\IFSL\PERFSTAT01.DBF
> 3,674 1 4.2 1.0 1,056 0 0
>
> RF_SCANNING E:\ORADB\IFSL\RF_SCANNING1.DBF
> 1 0 ###### 1.0 1 0 0
>
> ROLLBACK E:\ORADB\IFSL\ROLLBACK01.DBF
> 1,476 0 2.3 1.0 21,373 6 199
> 0.0
>
> SYSTEM D:\ORADB\IFSL\SYSTEM01.DBF
> 4,766 1 5.9 2.5 899 0 742
> 0.8
>
> TEMP1 E:\ORADB\IFSL\TEMP101.DBF
> 245 0 7.2 8.4 263 0 0
>
> USERS E:\ORADB\IFSL\USERS01.DBF
> 1 0 ###### 1.0 1 0 0
>
> -------------------------------------------------------------
> Buffer Pool Statistics for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> Pools D: default pool, K: keep pool, R: recycle pool
>
> Free Write
> Buffer
> Buffer Consistent Physical Physical Buffer Complete
> Busy
> P Gets Gets Reads Writes Waits Waits
> Waits
> - ----------- ------------- ----------- ---------- ------- -------- ------
--
> --
> D 1,588,626 10,300,105 1,570,927 42,045 0 0
> 1,216
> -------------------------------------------------------------
>
>
>
>
>
> Buffer wait Statistics for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> ordered by wait time desc, waits desc
>
> Tot Wait Avg
> Class Waits Time (cs) Time (cs)
> ------------------ ----------- ---------- ---------
> data block 1,014 183 0
> undo block 171 0 0
> undo header 27 0 0
> segment header 1 0 0
> -------------------------------------------------------------
> Rollback Segment Stats for DB: IFSL Instance: ifsl Snaps: 39 -40
> ->A high value for "Pct Waits" suggests more rollback segments may be
> required
>
> Trans Table Pct Undo Bytes
> RBS No Gets Waits Written Wraps Shrinks Extends
> ------ ------------ ------- --------------- -------- -------- --------
> 0 14.0 0.00 0 0 0 0
> 1 3,394.0 0.00 2,099,854 1 0 0
> 2 3,615.0 0.00 1,848,142 0 0 0
> 3 3,120.0 0.00 1,384,224 0 0 0
> 4 4,034.0 0.00 1,514,136 0 0 0
> 5 3,945.0 0.00 1,830,104 0 0 0
> 6 3,609.0 0.00 2,872,916 1 0 0
> 7 3,639.0 0.00 1,550,268 0 0 0
> 8 3,502.0 0.00 6,480,608 1 0 0
> 9 3,205.0 0.00 4,434,572 0 0 0
> 10 7,718.0 0.00 15,874,924 3 0 0
> 11 3,061.0 0.00 1,476,608 0 0 0
> 12 4,622.0 0.00 6,384,936 1 0 0
> 13 4,059.0 0.00 1,757,414 0 0 0
> 14 3,011.0 0.00 1,349,284 0 0 0
> 15 3,111.0 0.00 1,220,748 0 0 0
> 16 3,279.0 0.00 2,640,820 1 0 0
> 17 2,916.0 0.00 1,185,410 0 0 0
> 18 4,461.0 0.00 1,792,372 0 0 0
> 19 3,107.0 0.00 4,366,548 0 0 0
> 20 3,420.0 0.00 1,311,218 0 0 0
> 21 2,713.0 0.00 1,082,928 0 0 0
> 22 2,738.0 0.00 1,011,972 1 0 0
> 23 6,341.0 0.00 2,163,046 0 0 0
> 24 3,335.0 0.00 1,341,674 0 0 0
> 25 3,697.0 0.00 1,539,702 0 0 0
> 26 4,079.0 0.00 1,568,134 0 0 0
> 27 2,610.0 0.00 1,217,060 0 0 0
> 28 2,688.0 0.00 1,020,494 1 0 0
> 29 3,021.0 0.00 1,185,622 0 0 0
> 30 3,127.0 0.00 1,721,084 0 0 0
> -------------------------------------------------------------
> Rollback Segment Storage for DB: IFSL Instance: ifsl Snaps: 39 -40
> ->Optimal Size should be larger than Avg Active
>
> RBS No Segment Size Avg Active Optimal Size Maximum Size
> ------ --------------- --------------- --------------- ---------------
> 0 401,408 8,192 401,408
> 1 46,129,152 1,173,585 41,943,040 46,129,152
> 2 46,129,152 0 41,943,040 46,129,152
> 3 46,129,152 0 41,943,040 46,129,152
> 4 46,129,152 838,860 41,943,040 46,129,152
> 5 46,129,152 0 41,943,040 46,129,152
> 6 41,934,848 1,173,585 41,943,040 41,934,848
> 7 46,129,152 838,860 41,943,040 46,129,152
> 8 46,129,152 838,860 41,943,040 46,129,152
> 9 46,129,152 838,860 41,943,040 46,129,152
> 10 46,129,152 4,045,271 41,943,040 46,129,152
> 11 46,129,152 838,860 41,943,040 46,129,152
> 12 46,129,152 838,860 41,943,040 46,129,152
> 13 46,129,152 418,611 41,943,040 46,129,152
> 14 46,129,152 418,611 41,943,040 46,129,152
> 15 41,934,848 2,841,570 41,943,040 41,934,848
> 16 46,129,152 4,740,160 41,943,040 46,129,152
> 17 46,129,152 4,706,007 41,943,040 46,129,152
> 18 46,129,152 838,860 41,943,040 46,129,152
> 19 46,129,152 838,860 41,943,040 46,129,152
> 20 46,129,152 2,432,695 41,943,040 46,129,152
> 21 46,129,152 0 41,943,040 46,129,152
> 22 46,129,152 838,860 41,943,040 46,129,152
> 23 46,129,152 838,860 41,943,040 46,129,152
> 24 46,129,152 0 41,943,040 46,129,152
> 25 46,129,152 838,860 41,943,040 46,129,152
> 26 46,129,152 838,860 41,943,040 46,129,152
> 27 46,129,152 0 41,943,040 46,129,152
> 28 46,129,152 838,860 41,943,040 46,129,152
> 29 46,129,152 838,860 41,943,040 46,129,152
> 30 41,934,848 0 41,943,040 41,934,848
> -------------------------------------------------------------
> Latch Activity for DB: IFSL Instance: ifsl Snaps: 39 -40
> ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
> willing-to-wait latch get requests
> ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
> ->"Pct Misses" for both should be very close to 0.0
>
> Pct Avg
> Pct
> Get Get Slps NoWait
> NoWait
> Latch Name Requests Miss /Miss Requests
> Miss
> ----------------------------- -------------- ------ ------ ------------ --
--
> --
> Token Manager 95 0.0 0
> active checkpoint queue latch 27,837 0.0 0
> archive control 2 0.0 0
> archive process latch 2 0.0 0
> cache buffer handles 6,866 0.0 0
> cache buffers chains 35,779,047 0.0 0.0 2,335,893
> 0.0
> cache buffers lru chain 864,932 0.0 0.0 1,586,536
> 0.0
> channel handle pool latch 36 0.0 0
> channel operations parent lat 53 0.0 0
> checkpoint queue latch 359,495 0.0 0.1 0
> dml lock allocation 143,205 0.1 0.0 0
> enqueue hash chains 265,496 0.2 0.0 0
> enqueues 287,512 0.1 0.0 0
> event group latch 17 0.0 0
> job_queue_processes parameter 59 0.0 0
> ktm global data 13 0.0 0
> latch wait list 970 0.0 971
> 0.0
> library cache 13,541,932 0.3 0.0 0
> library cache load lock 742 0.0 0
> list of block allocation 102,260 0.0 0.0 0
> loader state object freelist 14 0.0 0
> longop free list 54 0.0 0
> messages 313,195 0.0 0.0 0
> multiblock read objects 70,422 0.0 0.0 0
> ncodef allocation latch 59 0.0 0
> process allocation 17 0.0 17
> 0.0
> process group creation 36 0.0 0
> redo allocation 707,869 0.0 0.0 0
> redo writing 144,943 0.0 0.0 0
> row cache objects 264,244 0.0 0.0 0
> sequence cache 30,954 0.0 0.0 0
> session allocation 11,391,782 0.0 0.0 0
> session idle bit 169,305 0.1 0.0 0
> session switching 59 0.0 0
> shared pool 551,264 0.0 0.0 0
> sort extent pool 59 0.0 0
> transaction allocation 151,682 0.1 0.0 0
> transaction branch allocation 59 0.0 0
> undo global data 161,987 0.0 0.0 0
> user lock 48 0.0 0
> -------------------------------------------------------------
> Latch Sleep breakdown for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> ordered by misses desc
>
> Get Spin
&
> Latch Name Requests Misses Sleeps Sleeps
> 1->4
> -------------------------- -------------- ----------- ----------- --------
--
> --
> library cache 13,541,932 34,821 1,683
> 33852/278/66
> 8/23/0
> cache buffers chains 35,779,047 4,590 13
> 4577/13/0/0/
> 0
> enqueue hash chains 265,496 520 1
> 519/1/0/0/0
> enqueues 287,512 245 1
> 244/1/0/0/0
> cache buffers lru chain 864,932 238 1
> 237/1/0/0/0
> checkpoint queue latch 359,495 45 4
41/4/0/0/0
> -------------------------------------------------------------
> Latch Miss Sources for DB: IFSL Instance: ifsl Snaps: 39 -40
> -> only latches with sleeps are shown
> -> ordered by name, sleeps desc
>
> NoWait
> Waiter
> Latch Name Where Misses Sleeps
> Sleeps
> ------------------------ -------------------------- ------- ---------- ---
--
> --
> cache buffers chains kcbgtcr: kslbegin 0 10
> 6
> cache buffers chains kcbgcur: kslbegin 0 3
> 0
> cache buffers lru chain kcbbiop: lru scan 0 1
> 0
> checkpoint queue latch kcbk0rrd: update recovery 0 4
> 0
> enqueue hash chains ksqrcl 0 1
> 1
> enqueues ksqgel: create enqueue 0 1
> 0
> library cache kglhdgn: child: 0 948
> 21
> library cache kglget: child: KGLDSBRD 0 492
> 14
> library cache kglget: child: KGLDSBYD 0 205
> 11
> library cache kglic 0 20
> 14
> library cache kglpnal: child: alloc spac 0 8
> 35
> library cache kglpnal: child: before pro 0 3
> 93
> library cache kglpnc: child 0 3
> 728
> library cache kglhdgc: child: 0 1
> 2
> library cache kglpnp: child 0 1
> 291
> library cache kglupc: child 0 1
> 434
> library cache kgllkdl: child: cleanup 0 1
> 0
> -------------------------------------------------------------
> Dictionary Cache Stats for DB: IFSL Instance: ifsl Snaps: 39 -40
> ->"Pct Misses" should be very low (< 2% in most cases)
> ->"Cache Usage" is the number of cache entries being used
> ->"Pct SGA" is the ratio of usage to allocated size for that cache
>
> Get Pct Scan Pct Mod Final
> Pct
> Cache Requests Miss Requests Miss Req Usage
> SGA
> ---------------------- ------------ ------ -------- ----- -------- ------
--
> --
> dc_constraints 0 0 0 18
> 58
> dc_database_links 0 0 0 0
> 0
> dc_files 1 0.0 0 0 1
> 50
> dc_free_extents 834 0.1 1 0.0 1 3,944
> 97
> dc_global_oids 0 0 0 0
> 0
> dc_histogram_data 0 0 0 0
> 0
> dc_histogram_data_valu 0 0 0 0
> 0
> dc_histogram_defs 3,533 0.8 0 0 1,038
> 100
> dc_object_ids 14,267 0.3 0 0 1,314
> 99
> dc_objects 3,404 5.2 0 0 3,905
> 100
> dc_outlines 0 0 0 0
> 0
> dc_profiles 2,498 0.0 0 0 2
> 67
> dc_rollback_segments 806 0.0 0 0 32
> 78
> dc_segments 4,647 0.9 0 1 1,593
> 99
> dc_sequence_grants 0 0 0 0
> 0
> dc_sequences 14,513 0.0 0 14,509 38
> 84
> dc_synonyms 181 6.6 0 0 74
> 90
> dc_tablespace_quotas 4 0.0 0 1 5
> 22
> dc_tablespaces 66 0.0 0 0 8
> 62
> dc_used_extents 1 100.0 0 1 141
> 99
> dc_user_grants 113 0.9 0 0 80
> 82
> dc_usernames 3,028 0.0 0 0 65
> 96
> dc_users 22,619 0.0 0 0 83
> 90
> ifs_acl_cache_entries 0 0 0 0
> 0
> -------------------------------------------------------------
>
>
> Library Cache Activity for DB: IFSL Instance: ifsl Snaps: 39 -40
> ->"Pct Misses" should be very low
>
> Get Pct Pin Pct
> Invali-
> Namespace Requests Miss Requests Miss Reloads
> dations
> --------------- ------------ ------ -------------- ------ ---------- -----
--
> -
> BODY 33,466 0.1 32,423 0.1 0
> 0
> CLUSTER 60 0.0 48 0.0 0
> 0
> INDEX 0 0 0
> 0
> OBJECT 0 0 0
> 0
> PIPE 0 0 0
> 0
> SQL AREA 33,503 3.5 4,363,378 0.0 5
> 0
> TABLE/PROCEDURE 71,724 0.3 1,273,488 0.0 0
> 0
> TRIGGER 10 0.0 10 0.0 0
> 0
> -------------------------------------------------------------
> SGA Memory Summary for DB: IFSL Instance: ifsl Snaps: 39 -40
>
> SGA regions Size in Bytes
> ------------------------------ ----------------
> Database Buffers 98,304,000
> Fixed Size 75,804
> Redo Buffers 1,581,056
> Variable Size 1,008,771,072
> ----------------
> sum 1,108,731,932
> -------------------------------------------------------------
>
>
> SGA breakdown difference for DB: IFSL Instance: ifsl Snaps: 39 -40
>
> Pool Name Begin value End value
> Difference
> ----------- ------------------------ -------------- -------------- -------
--
> --
> java pool free memory 32,768 32,768
> 0
> large pool free memory 614,400 614,400
> 0
> shared pool Checkpoint queue 295,056 295,056
> 0
> shared pool DML locks 234,720 234,720
> 0
> shared pool KGFF heap 11,116 11,116
> 0
> shared pool KGK heap 17,560 17,560
> 0
> shared pool KQLS heap 12,435,144 13,162,340
> 727,196
> shared pool PL/SQL DIANA 16,461,664 17,359,304
> 897,640
> shared pool PL/SQL MPCODE 33,749,664 35,948,400
> 2,198,736
> shared pool PL/SQL SOURCE 4,404 4,404
> 0
> shared pool PLS non-lib hp 2,096 2,096
> 0
> shared pool State objects 533,360 533,360
> 0
> shared pool branches 117,600 117,600
> 0
> shared pool db_block_buffers 1,632,000 1,632,000
> 0
> shared pool db_block_hash_buckets 323,080 323,080
> 0
> shared pool db_files 370,988 370,988
> 0
> shared pool db_handles 200,000 200,000
> 0
> shared pool dictionary cache 4,144,952 4,267,992
> 123,040
> shared pool enqueue_resources 216,000 216,000
> 0
> shared pool event statistics per ses 1,530,800 1,530,800
> 0
> shared pool fixed allocation callbac 640 640
> 0
> shared pool free memory 713,396,352
> 678,453,704 -34,942,648
> shared pool ktlbk state objects 209,292 209,292
> 0
> shared pool library cache 47,782,424 52,339,232
> 4,556,808
> shared pool message pool freequeue 124,552 124,552
> 0
> shared pool miscellaneous 1,318,852
> 1,310,620 -8,232
> shared pool processes 323,200 323,200
> 0
> shared pool sessions 959,420 959,420
> 0
> shared pool sql area 171,192,816 197,637,360
> 26,444,544
> shared pool table columns 27,368 28,544
> 1,176
> shared pool table definiti 13,404 15,084
> 1,680
> shared pool transactions 436,188 436,188
> 0
> shared pool trigger defini 4,288 4,288
> 0
> shared pool trigger inform 3,836 3,896
> 60
> shared pool view columns d 31,140 31,140
> 0
> db_block_buffers 98,304,000 98,304,000
> 0
> fixed_sga 75,804 75,804
> 0
> log_buffer 1,572,864 1,572,864
> 0
> -------------------------------------------------------------
> init.ora Parameters for DB: IFSL Instance: ifsl Snaps: 39 -40
>
> End
value
> Parameter Name Begin value (if
> different)
> ----------------------------- --------------------------------- ----------
--
> --
> background_dump_dest E:\Oradb\IFSL\bdump
> compatible 8.1.7.4.1
> control_files D:\Oradb\IFSL\control01.ctl, E:\O
> cpu_count 2
> db_block_buffers 12000
> db_block_lru_latches 4
> db_block_size 8192
> db_file_multiblock_read_count 94
> db_files 1024
> db_name IFSL
> distributed_transactions 10
> global_names TRUE
> instance_name IFSL
> java_pool_size 32768
> job_queue_interval 10
> job_queue_processes 3
> large_pool_size 614400
> log_archive_dest_1 location=E:\Oradb\IFSL\arch
> log_archive_format arch%S.arc
> log_archive_start TRUE
> log_buffer 1572864
> log_checkpoint_interval 100000
> log_checkpoint_timeout 10
> max_dump_file_size 10240
> max_enabled_roles 120
> nls_date_format YYYY/MM/DD
> open_cursors 600
> optimizer_mode RULE
> oracle_trace_collection_name
> os_authent_prefix
> parallel_max_servers 5
> processes 400
> remote_login_passwordfile SHARED
> rollback_segments rb1, rb2, rb3, rb4, rb5, rb6, rb7
> service_names IFSL
> session_cached_cursors 150
> shared_pool_size 1000000000
> sort_area_retained_size 2097152
> sort_area_size 1572864
> timed_statistics TRUE
> user_dump_dest E:\Oradb\IFSL\udump
> -------------------------------------------------------------
>
> End of Report
>
>
>
Received on Wed Apr 28 2004 - 11:44:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US