========================================================== ========================================================== Database Performance Report Run time: 01-Feb-00 09:05:17 ========================================================== ========================================================== ========================================================== Database I/O Run time: 01-Feb-00 09:05:17 fl# NAME Phy Rds Phy Wrts Blk Rds Blk Wr Total IO ---- ---------------------------------------- ----------- ------------ ----------- ----------- ----------- 1 /dev/rdsk/..1 1,436 1,002 1,797 1,002 2,438 2 /dev/rdsk/..dev/rdsk/THE_SCHEMA/GEMS_DATA0 0 0 0 0 0 3 /dev/rdsk/../dev/rdsk/THE_SCHEMA/ROLLBACK0 17 688,002 17 688,002 688,019 4 /dev/rdsk/..P01 56,579 18,571 811,794 296,627 75,150 5 /dev/rdsk/..01 2 0 2 0 2 6 /dev/rdsk/..01 0 0 0 0 0 7 /dev/rdsk/..A 6,763 23,702 71,097 71,267 30,465 8 /dev/rdsk/../dev/rdsk/THE_SCHEMA/RTS_INDEX 15,716 18,325 15,716 44,698 34,041 9 /dev/rdsk/..ev/rdsk/THE_SCHEMA/DB_NAME_TB 137,356 220,703 1,427,090 443,849 358,059 10 /dev/rdsk/..ev/rdsk/THE_SCHEMA/DB_NAME_ID 205,953 282,430 205,954 580,990 488,383 ----------- ------------ ----------- ----------- ----------- sum 423,822 1,252,735 2,533,467 2,126,435 1,676,557 10 rows selected. ========================================================== Cached Blocks Run time: 01-Feb-00 09:05:17 OWNER SEGMENT_NAME SEGMENT_TY NUMBER_CACHED -------------------- ------------------------------ ---------- --------------- SYS RS01 ROLLBACK 115230 SYS RS02 ROLLBACK 115230 DB_OWNER POS TABLE 45508 DB_OWNER MVE TABLE 34392 DB_OWNER PK_POS INDEX 16676 DB_OWNER RISK_LIM TABLE 14455 DB_OWNER RISK_LIM_DETL TABLE 11019 DB_OWNER POS_ACCT_POS_INSTR_IDX INDEX 6751 DB_OWNER SETTLE_INSTR_GRP_DETL TABLE 6510 DB_OWNER ISS_DENOM TABLE 5371 DB_OWNER PK_RISK_LIM INDEX 3674 DB_OWNER INC_EVNT TABLE 3278 DB_OWNER INS_ACTV_STATUS TABLE 1273 DB_OWNER MVE_MVE_ORIG_ID_IDX INDEX 1236 DB_OWNER INS_ACTV_STATUS_PK INDEX 1185 DB_OWNER RTS_RAW_EVNT TABLE 1114 DB_OWNER DEP_STRC TABLE 1032 DB_OWNER MVE_PROC_IND_IDX INDEX 895 DB_OWNER PIPE_OBJ_PROC_ID_INS_ID_IDX INDEX 730 DB_OWNER POS_POS_ID_IDX INDEX 700 DB_OWNER MVE_SETTLE_TXN_ID_IDX INDEX 617 --------------- Blocks Cached xxxxxx 1592 rows selected. STAT DIRTY NUM ---- ----- --------------- CR N 20 CR Y 43647 FREE N 49922 XCUR N 544225 XCUR Y 2186 --------------- sum 640000 5 rows selected. ========================================================== System Waits Run time: 01-Feb-00 09:18:18 CLASS COUNT TIME ------------------ --------------- --------------- data block 155984 0 sort block 0 0 save undo block 0 0 segment header 518 0 save undo header 0 0 free list 0 0 system undo header 0 0 system undo block 0 0 undo header 3608 0 undo block 1 0 10 rows selected. ========================================================== System Events Run time: 01-Feb-00 09:18:18 EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT ---------------------------------------------------------------- --------------- --------------- --------------- --------------- latch free 108762 82713 0 .000 pmon timer 15188 15147 0 .000 process startup 10 0 0 .000 rdbms ipc reply 3155 35 0 .000 rdbms ipc message 60275 45757 0 .000 enqueue 4546 1435 0 .000 control file sequential read 3313 0 0 .000 control file single write 2 0 0 .000 control file parallel write 507 0 0 .000 buffer deadlock 1895 1645 0 .000 write complete waits 122 0 0 .000 buffer busy waits 160784 107 0 .000 log file sequential read 42 0 0 .000 log file single write 76 0 0 .000 log file parallel write 14910 0 0 .000 log file switch completion 52 0 0 .000 log file sync 5405 0 0 .000 db file sequential read 357225 0 0 .000 db file scattered read 103057 0 0 .000 db file single write 170 0 0 .000 db file parallel write 142234 9 0 .000 direct access I/O 392 392 0 .000 instance recovery 1 0 0 .000 instance state change 2 0 0 .000 smon timer 155 151 0 .000 index block split 13 9 0 .000 library cache pin 72 0 0 .000 SQL*Net message to client 1902001 0 0 .000 SQL*Net more data to client 301307 0 0 .000 SQL*Net message from client 1902001 0 0 .000 SQL*Net more data from client 63952 0 0 .000 SQL*Net break/reset to client 11 0 0 .000 parallel query dequeue wait 22704 22704 0 .000 timer in sksawat 46807 46807 0 .000 34 rows selected. ========================================================== Datafile Waits Run time: 01-Feb-00 09:18:18 fl# NAME COUNT TIME ---- -------------------------------------------------- ------------ -------- 1 /dev/rdsk/THE_SCHEMA/SYSTEM01 51 0 7 /dev/rdsk/THE_SCHEMA/RTS_DATA 92 0 9 /dev/rdsk/THE_SCHEMA/DB_NAME_TBL 113 0 3 /dev/rdsk/THE_SCHEMA/ROLLBACK01 3,610 0 8 /dev/rdsk/THE_SCHEMA/RTS_INDEX 6,630 0 10 /dev/rdsk/THE_SCHEMA/DB_NAME_IDX 149,724 0 ------------ -------- sum 160,220 0 10 rows selected. ========================================================== Rollback Segment Statistics Run time: 01-Feb-00 09:18:18 USN EXTENTS RSSIZE WRITES XACTS GETS WAITS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE STATUS CUREXT CURBLK --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- 0 14 856064 1382 0 175 0 856064 0 0 0 0 0 ONLINE 9 2 1 30 471855104 744743351 0 197284 626 471855104 0 49 0 0 46196892 ONLINE 8 1131 2 30 471855104 657039794 0 174483 477 471855104 0 43 0 0 50874045 ONLINE 2 1596 3 30 471855104 492252511 0 133346 411 471855104 0 32 0 0 38235724 ONLINE 25 1449 4 30 471855104 847710142 0 224071 486 471855104 0 56 0 0 61760933 ONLINE 12 270 5 rows selected. ========================================================== System Statistics Run time: 01-Feb-00 09:18:18 STATISTIC# NAME CLASS VALUE --------------- -------------------------------------------------- --------------- --------------- 0 logons cumulative 1 2419 1 logons current 1 10 2 opened cursors cumulative 1 62556 3 opened cursors current 1 1 4 user commits 1 357 5 user rollbacks 1 594 6 user calls 1 1899518 7 recursive calls 1 1054244 8 recursive cpu usage 1 8508 9 session logical reads 1 191980994 10 session stored procedure space 1 0 11 CPU used when call started 128 0 12 CPU used by this session 1 0 13 session connect time 1 255558578252 14 process last non-idle time 128 255558578252 15 session uga memory 1 8110012 16 session uga memory max 1 849489300 17 messages sent 128 155108 18 messages received 128 155108 19 background timeouts 128 46639 20 session pga memory 1 1310288228 21 session pga memory max 1 1413045508 22 enqueue timeouts 4 11 23 enqueue waits 4 2769 24 enqueue deadlocks 4 0 25 enqueue requests 4 48686 26 enqueue conversions 4 76 27 enqueue releases 4 48662 28 global lock gets (non async) 32 0 29 global lock gets (async) 32 0 30 global lock get time 32 0 31 global lock converts (non async) 32 0 32 global lock converts (async) 32 0 33 global lock convert time 32 0 34 global lock releases (non async) 32 0 35 global lock releases (async) 32 0 36 global lock release time 32 0 37 db block gets 8 63112539 38 consistent gets 8 130999949 39 physical reads 8 3043310 40 physical writes 8 2133891 41 write requests 8 142234 42 summed dirty queue length 8 0 43 db block changes 8 49029998 44 change write time 8 0 45 consistent changes 8 807926 46 redo synch writes 8 5516 47 redo synch time 8 0 48 exchange deadlocks 8 1895 49 free buffer requested 8 2486367 50 dirty buffers inspected 8 0 51 free buffer inspected 8 462 52 commit cleanout failure: write disabled 8 0 53 commit cleanout failures: hot backup in progress 8 0 54 commit cleanout failures: buffer being written 8 5 55 commit cleanout failures: callback failure 8 639 56 total number commit cleanout calls 8 543503 57 commit cleanout number successfully completed 8 534785 58 DBWR timeouts 8 13855 59 DBWR make free requests 8 0 60 DBWR free buffers found 8 0 61 DBWR lru scans 8 137271 62 DBWR summed scan depth 8 35690460 63 DBWR buffers scanned 8 35690460 64 DBWR checkpoints 8 3113 65 DBWR cross instance writes 40 0 66 remote instance undo block writes 40 0 67 remote instance undo header writes 40 0 68 remote instance undo requests 40 0 69 cross instance CR read 40 0 70 recovery blocks read 8 0 71 recovery array reads 8 0 72 recovery array read time 8 0 73 CR blocks created 8 256814 74 Current blocks converted for CR 8 5 75 calls to kcmgcs 128 21242 76 calls to kcmgrs 128 7399344 77 calls to kcmgas 128 12198 78 next scns gotten without going to DLM 32 0 79 Unnecesary process cleanup for SCN batching 32 0 80 calls to get snapshot scn: kcmgss 32 2028323 81 kcmgss waited for batching 32 0 82 kcmgss read scn without going to DLM 32 0 83 kcmccs called get current scn 32 0 84 redo entries 2 24258400 85 redo size 2 5061282952 86 redo entries linearized 2 0 87 redo buffer allocation retries 2 31 88 redo small copies 2 369565 89 redo wastage 2 2130817 90 redo writer latching time 2 0 91 redo writes 2 14910 92 redo blocks written 2 10213986 93 redo write time 2 0 94 redo log space requests 2 52 95 redo log space wait time 2 0 96 redo log switch interrupts 2 0 97 redo ordering marks 2 0 98 hash latch wait gets 40 0 99 background checkpoints started 8 17 100 background checkpoints completed 8 16 101 serializable aborts 1 0 102 transaction lock foreground requests 128 0 103 transaction lock foreground wait time 128 0 104 transaction lock background gets 128 0 105 transaction lock background get time 128 0 106 transaction tables consistent reads - undo records 128 0 107 transaction tables consistent read rollbacks 128 0 108 data blocks consistent reads - undo records applie 128 807913 109 no work - consistent read gets 128 58255402 110 cleanouts only - consistent read gets 128 39309 111 rollbacks only - consistent read gets 128 120223 112 cleanouts and rollbacks - consistent read gets 128 136493 113 rollback changes - undo records applied 128 4962 114 transaction rollbacks 128 85 115 immediate (CURRENT) block cleanout applications 128 102951 116 immediate (CR) block cleanout applications 128 175802 117 deferred (CURRENT) block cleanout applications 128 69247 118 table scans (short tables) 64 3251 119 table scans (long tables) 64 230 120 table scans (rowid ranges) 64 0 121 table scans (cache partitions) 64 0 122 table scans (direct read) 64 0 123 table scan rows gotten 64 116777194 124 table scan blocks gotten 64 47789160 125 table fetch by rowid 64 9055759 126 table fetch continued row 64 553 127 cluster key scans 64 48946 128 cluster key scan block gets 64 96071 129 parse time cpu 64 0 130 parse time elapsed 64 0 131 parse count 64 78664 132 execute count 64 1077710 133 bytes sent via SQL*Net to client 1 1201682960 134 bytes received via SQL*Net from client 1 1456749464 135 SQL*Net roundtrips to/from client 1 1899259 136 bytes sent via SQL*Net to dblink 1 0 137 bytes received via SQL*Net from dblink 1 0 138 SQL*Net roundtrips to/from dblink 1 0 139 sorts (memory) 64 7944 140 sorts (disk) 64 29 141 sorts (rows) 64 52320913 142 session cursor cache hits 64 0 143 session cursor cache count 64 0 144 cursor authentications 128 13183 145 rows selected. ========================================================== Redo Logs Run time: 01-Feb-00 09:18:18 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME --------------- --------------- --------------- --------------- --------------- --- ---------------- --------------- -------------------- 1 1 28 314572800 2 NO CURRENT 65018 02/01/00 09:04:20 2 1 26 314572800 2 YES INACTIVE 59771 02/01/00 07:10:01 3 1 27 314572800 2 YES INACTIVE 63223 02/01/00 08:10:36 3 rows selected. GROUP# STATUS MEMBER --------------- ------- -------------------------------------------------------------------------------- 1 /dev/rdsk/THE_SCHEMA/LOG1_01 1 /dev/rdsk/THE_SCHEMA/LOG2_01 2 /dev/rdsk/THE_SCHEMA/LOG1_02 2 /dev/rdsk/THE_SCHEMA/LOG2_02 3 /dev/rdsk/THE_SCHEMA/LOG1_03 3 /dev/rdsk/THE_SCHEMA/LOG2_03 6 rows selected. ========================================================== Redo Log Stats Run time: 01-Feb-00 09:18:18 Small Entry Max Size --> 80 Log Simultaneous Copies --> 20 CPU_COUNT --> 20 Immed Immed NAME GETS MISSES RATIO Gets Miss RATIO -------------------- --------------- --------------- ------ --------------- --------------- ------ redo allocation 24455607 319947 1.3 0 0 .0 redo copy 241 184 76.3 24186793 9699 .0 2 rows selected. ========================================================== Initialisation Settings KSPPINM KSPPDESC KSPPSTVL DE ----------------------------------- --------------------------------------------- ------------------------------ -- _db_block_hash_buckets Number of database block hash buckets 8750 N _db_handles System-wide simultaneous buffer operations 1680 N _enqueue_hash enqueue hash table length 925 N _enqueue_hash_chain_latches enqueue hash chain latches 6 N _enqueue_locks locks for managed enqueues 5891 N _messages message queue resources - dependent on # 1346 N processes & # buffers _parallel_min_message_pool minimum size of shared pool memory to reserve 128880 N for pq servers _small_table_threshold threshold level of table size for forget-bit 700 N enabled during scan _trace_files_public Create publicly accessible trace files TRUE N ========================================================== ========================================================== End of Database Performance Report Run time: 01-Feb-00 09:18:18 ========================================================== ==========================================================