Database Administration - Assignment Example

Database Administration Name: Number: Course: Lecturer: Date: Question 2 The data security issues I would expect MVCH to encounter incase an ER system is implemented. The most obvious data security issues are ensuring confidentiality of patient records. The level of information that laboratories, insurance clerks and physicians should be allowed access to write manipulate and use. Another important area is records such as rooms where records of patients suffering from chronic diseases are released. We have issues to do with guardians rights to their relative’s medical data. Thus the hospital has to enforce a mechanism to lock out hackers from obtaining data from the hospitals database. Data security techniques that could be used to address the above data security issues MVCH is first needed to put in place procedures, data policies, and sufficient standards that relate to information security, much important is those data relating to patients to ensure confidentiality and rights of privacy. Furthermore, particular steps should be put in place to make sure that there is sufficient security for the online database, network and client/server architecture. MVCH ought to also consider incorporating views to give restrictive access to certain tables and rows within tables on the database, encryption mechanisms for highly sensitive records together with authentication schemes for access to the database and lastly authorization protocols. MVCH also needs to ensure that all signatories that have access to patient data are well trained with regard to the sensitivity of the information and how well to secure it from non authorized access by persons who might make use of social engineering to alter information. Question 3 How access problems such as the one that Dr. Z experienced at another hospital is prevented The best method to restrict access problems like the ones described by Dr Z would be to transitionally roll out the system CPOE and give sufficient documentation and user training. There should also be procedures of operations that are standard with regard too database security so as to prevent issues such as the reception unilaterally altering records. Comprehensive authorization matrices will be required to locate who can perform what with distinct data pieces. Again approval modes for altering data would be appropriate. Extensive audit trails would be needed to document all data entries, together with the persons that entered the data. It would be also important to set default values for each user on the electronic forms interfaces. Question 6 Should MVCH adopt a continuous data protection system Of course MVCH ought to adopt a CDP system since it would give a record of each transaction within the system. In case the CDP makes use of disks for back up data recovery would be made easier as opposed to a tape back up and could easily be applied to restore a correct copy of any data that might be tempered with[Daw05]. From the critical mission structure of the hospitals information, this type of back up would be most appropriate. As opposed to a cold back up, the system could still run even during the process of backing up. Some other strategies could also be still open to the hospital such as the incremental warm back ups done after periods daily integrated with a cold back up during the night. Question 7 Data storage at MVCH being treated as a strategic issue Most certainly data storage ought to be treated as strategic issue. In cases where data security is violated or there is data loss, the hospital would be able to face serious consequences since data accessibility may directly affect patient care. Furthermore, the hospital is required to project at long term issues when looking at the organizations growth. Strategic planning in this part will examine expected growth against pre-existing data storage space. Question 8 Data and database administration issues that should be addressed The projected information systems and business plan would best address adaptation together with growth to brand modalities of treatment. The segment that deals with information systems will be required to spell out data storage specifications together with capacity requirements, recovery and back up not forgetting security matters. Also an enterprise data model could also be applied to give major data categories for data stewards’ assignment. Part A-6 How views can be used as part of data security The main merit of a view is that it can be devised to present only information that a user needs access, hence effectively locking out the user from viewing some other data that might be confidential or private. Users might be granted access privileges to the view but denied access to the base tables where the view relies on. In many views, data might not be updated hence eliminating the probability of non authorized alterations to the base data. Despite all this views are not sufficient for security since persons that are not authorized might gain skills of or entry to certain views. Together with complex query languages a restricted individual might gain entry to information via simple practical’s. Views also insufficiently protect information from hackers outside the database management system. Case Exercise 1 Possible types of users who would need authorization to use From the case study its is clear that physicians have been given much focus, but also clinical employees together with all medical personnel would require access. Super administrators together with simple administrators too need access. The hospitals management might not require EMR direct access for their day to day task, but then there may be need to incorporate report summaries for the purposes of decision making. A small number of the system users would be granted privileges to read update and insert data an example is physicians must only be able to update and insert patient data for only those patients they are in charge of. Nurses can only me granted read, insert and update privileges for patients within their jurisdiction. Other users would be much restricted given only abilities to view certain table records and some few records attributes. Case Exercise 3 How a hospital such as MVCH could use RFID in connection with an EMR system. The hospital could use RFID together with the EMR system in multiple ways. Some time in the past RFID tags that were implanted under the patient’s skin for identity was approved. RFID could also be well applied in monitoring supplies, lab work together with supplies. In case RFID was applied the requirements for data storage would rise since each persons supply, specimen from the lab would contain unique identifiers. This could lead to huge increases in needed storage capacity. Simulate and Investigate the Problem. Execution plan that Oracle uses for the top SQL statement in the Top SQL report Stats pack report STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 2662754155 xe 1 22-May-12 21:30 10.2.0.1.0 NO Host Name: 2030-6FE659C57E Num CPUs: 1 Phys Memory (MB): 502 ~~~~ Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------- Begin Snap: 1 22-May-12 22:21:16 22 6.6 End Snap: 11 22-May-12 22:38:03 21 6.4 Elapsed: 16.78 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 56M Std Block Size: 8K Shared Pool Size: 64M Log Buffer: 2,792K Load Profile Per Second Per Transaction ~~~~~~~~~~~~ --------------- --------------- Redo size: 5,221.41 1,051,592.80 Logical reads: 1,442.81 290,581.40 Block changes: 36.51 7,352.60 Physical reads: 638.93 128,680.60 Physical writes: 1.05 211.60 User calls: 0.34 68.80 Parses: 5.08 1,023.00 Hard parses: 0.63 126.60 Sorts: 3.54 712.00 Logons: 0.01 2.20 Executes: 13.33 2,685.60 Transactions: 0.00 % Blocks changed per Read: 2.53 Recursive Call %: 99.81 Rollback per transaction %: 0.00 Rows per Sort: 8.49 Instance Efficiency Percentages ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 55.72 In-memory Sort %: 100.00 Library Hit %: 87.91 Soft Parse %: 87.62 Execute to Parse %: 61.91 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 15.13 % Non-Parse CPU: 97.36 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 83.38 82.34 % SQL with executions>1: 72.11 75.22 % Memory for SQL w/exec>1: 84.98 89.25 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ db file scattered read 35,701 329 9 75.3 CPU time 74 16.8 db file sequential read 2,321 23 10 5.2 control file sequential read 446 3 8 .8 log file parallel write 253 2 9 .5 ------------------------------------------------------------- Host CPU (CPUs: 1) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 8.59 7.11 84.30 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 7.41 % of busy CPU for Instance: 47.18 %DB time waiting for CPU - Resource Mgr: 0.00 Memory Statistics Begin End ~~~~~~~~~~~~~~~~~ ------------ ------------ Host Mem (MB): 502.0 502.0 SGA use (MB): 140.0 140.0 PGA use (MB): 46.9 43.1 % Host Mem used for SGA+PGA: 37.2 36.5 ------------------------------------------------------------- Time Model System Stats DB/Inst: XE/xe Snaps: 1-11 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % of DB time ----------------------------------- -------------------- ------------ sql execute elapsed time 419.8 99.8 DB CPU 70.3 16.7 parse time elapsed 4.1 1.0 hard parse elapsed time 4.0 1.0 PL/SQL compilation elapsed time 0.9 .2 PL/SQL execution elapsed time 0.3 .1 connection management call elapsed 0.3 .1 failed parse elapsed time 0.2 .0 hard parse (sharing criteria) elaps 0.1 .0 sequence load elapsed time 0.0 .0 repeated bind elapsed time 0.0 .0 DB time 420.5 background elapsed time 50.9 background cpu time 3.9 ------------------------------------------------------------- Wait Events DB/Inst: XE/xe Snaps: 1-11 -> s - second, cs - centisecond, ms - millisecond, us - microsecond -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn --------------------------------- ------------ ------ ---------- ------ -------- db file scattered read 35,701 0 329 9 7,140.2 db file sequential read 2,321 0 23 10 464.2 control file sequential read 446 0 3 8 89.2 log file parallel write 253 0 2 9 50.6 resmgr:cpu quantum 60 0 2 38 12.0 control file parallel write 339 0 2 5 67.8 rdbms ipc reply 66 0 1 10 13.2 db file parallel write 273 0 1 2 54.6 latch: library cache 24 0 0 19 4.8 os thread startup 5 0 0 28 1.0 latch: row cache objects 8 0 0 10 1.6 enq: RO - fast object reuse 33 0 0 2 6.6 latch: shared pool 10 0 0 4 2.0 latch: redo allocation 3 0 0 6 0.6 direct path write 9 0 0 2 1.8 SQL*Net break/reset to client 10 0 0 1 2.0 log file sync 6 0 0 1 1.2 reliable message 23 0 0 0 4.6 latch free 19 0 0 0 3.8 virtual circuit status 136 100 4,081 30004 27.2 Streams AQ: qmn coordinator idle 53 66 980 18493 10.6 Streams AQ: qmn slave idle wait 35 0 980 28003 7.0 SQL*Net message from client 232 0 579 2496 46.4 jobq slave wait 44 95 130 2951 8.8 class slave wait 1 100 5 5017 0.2 SQL*Net message to client 232 0 0 0 46.4 ------------------------------------------------------------- Background Wait Events DB/Inst: XE/xe Snaps: 1-11 -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn --------------------------------- ------------ ------ ---------- ------ -------- log file parallel write 253 0 2 9 50.6 control file parallel write 339 0 2 5 67.8 control file sequential read 41 0 2 37 8.2 db file sequential read 59 0 1 18 11.8 db file parallel write 273 0 1 2 54.6 os thread startup 5 0 0 28 1.0 latch: library cache 6 0 0 22 1.2 latch: shared pool 4 0 0 6 0.8 direct path write 6 0 0 3 1.2 events in waitclass Other 20 0 0 0 4.0 rdbms ipc message 3,710 86 9,808 2644 742.0 smon timer 41 5 1,088 26527 8.2 pmon timer 350 100 1,000 2857 70.0 Streams AQ: qmn coordinator idle 53 66 980 18493 10.6 Streams AQ: qmn slave idle wait 35 0 980 28003 7.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: XE/xe Snaps: 1-11 -> Total Waits - units: K is 1000, M is 1000000, G is 1000000000 -> % of Waits - column heading: =1024ms -> % of Waits - value: .0 indicates value was Ordered by Event (idle events last) Total ----------------- % of Waits ------------------ Event Waits SQL reported below exceeded 1.0% of Total Executions CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 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.samp lesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mo d(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl( 254 1,140 4.5 0.00 0.00 1937775682 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 254 68 0.3 0.00 0.00 2065408759 select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob j#=:1 253 0 0.0 0.00 0.00 204386021 select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) fro m objauth$ where obj#=:1 and col# is not null group by privilege #, col#, grantee# order by col#, grantee# 253 117 0.5 0.00 0.00 1980305124 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2) )from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(co l#,0) order by grantee# 252 252 1.0 0.00 0.00 3047156589 select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.int cols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.i nitrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt, t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degr ee,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl( 218 81 0.4 0.00 0.00 2018736380 select timestamp, flags from fixed_obj$ where obj#=:1 215 215 1.0 0.00 0.00 260339297 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) 200 2 0.0 0.00 0.00 1316169839 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 sysdate) and (n ext_date < (sysdate+5/86400)) SQL ordered by Executions DB/Inst: XE/xe Snaps: 1-11 -> End Executions Threshold: 100 Total Executions: 13,428 -> Captured SQL accounts for 86.3% of Total Executions -> SQL reported below exceeded 1.0% of Total Executions CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: XE/xe Snaps: 1-11 -> End Parse Calls Threshold: 1000 Total Parse Calls: 5,115 -> Captured SQL accounts for 73.1% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 529 529 10.34 1348827743 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user# ,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N VL(spare1,0),NVL(scanhint,0) from seg$ where ts#=:1 and file#=:2 and block#=:3 270 215 5.28 260339297 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) 264 264 5.16 215804654 select STATSTYPE# from USTATS$ where OBJ# = :1 and INTCOL# = :2 218 218 4.26 2018736380 select timestamp, flags from fixed_obj$ where obj#=:1 193 193 3.77 1198893840 select order#,columns,types from access$ where d_obj#=:1 193 193 3.77 2062595902 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_ obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# 122 122 2.39 2554034351 lock table sys.col_usage$ in exclusive mode nowait 122 307 2.39 3665763022 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), 117 167 2.29 311489723 select cols,audit$,textlength,intcols,property,flags,rowid from view$ where obj#=:1 100 100 1.96 3468666020 select text from view$ where rowid=:1 99 2,474 1.94 4274598960 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 92 549 1.80 2703824309 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, SQL ordered by Parse Calls DB/Inst: XE/xe Snaps: 1-11 -> End Parse Calls Threshold: 1000 Total Parse Calls: 5,115 -> Captured SQL accounts for 73.1% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa ce=:3 and remoteowner is null and linkname is null and subname i s null 86 337 1.68 839312984 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 84 562 1.64 431456802 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 82 254 1.60 2065408759 select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob j#=:1 81 254 1.58 1937775682 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 74 74 1.45 1232162599 select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.blo ck#, l.chunk, l.pctversion$, l.flags, l.property, l.retention, l .freepools from lob$ l where l.obj# = :1 order by l.intcol# asc 60 74 1.17 120978412 select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intc ol# asc 60 74 1.17 162044583 select col#,intcol#,charsetid,charsetform from col$ where obj#=: 1 order by intcol# asc 60 74 1.17 1520971034 select intcol#, toid, version#, intcols, intcol#s, flags, synobj # from subcoltype$ where obj#=:1 order by intcol# asc 60 74 1.17 2364027057 select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#, nvl(typidcol#, 0) from coltype$ where obj#=:1 order by intcol# desc 60 74 1.17 3302995849 select col#,intcol#,reftyp,stabid,expctoid from refcon$ where ob j#=:1 order by intcol# asc 60 74 1.17 3309219733 select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, ele SQL ordered by Parse Calls DB/Inst: XE/xe Snaps: 1-11 -> End Parse Calls Threshold: 1000 Total Parse Calls: 5,115 -> Captured SQL accounts for 73.1% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- mnum from opqtype$ where obj# = :1 order by intcol# asc 55 55 1.08 336764478 select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece# 55 55 1.08 386388955 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece fr ------------------------------------------------------------- Instance Activity Stats DB/Inst: XE/xe Snaps: 1-11 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 7,362 7.3 1,472.4 CPU used when call started 23,046 22.9 4,609.2 CR blocks created 129 0.1 25.8 Cached Commit SCN referenced 0 0.0 0.0 Commit SCN cached 0 0.0 0.0 DB time 172,782 171.6 34,556.4 DBWR checkpoint buffers written 421 0.4 84.2 DBWR checkpoints 37 0.0 7.4 DBWR object drop buffers written 0 0.0 0.0 DBWR revisited being-written buff 0 0.0 0.0 DBWR thread checkpoint buffers wr 421 0.4 84.2 DBWR transaction table writes 5 0.0 1.0 DBWR undo block writes 197 0.2 39.4 IMU Flushes 1 0.0 0.2 IMU Redo allocation size 46,188 45.9 9,237.6 IMU commits 4 0.0 0.8 IMU contention 0 0.0 0.0 IMU ktichg flush 0 0.0 0.0 IMU pool not allocated 0 0.0 0.0 IMU recursive-transaction flush 0 0.0 0.0 IMU undo allocation size 26,132 26.0 5,226.4 IMU- failed to get a private stra 0 0.0 0.0 SMON posted for undo segment shri 1 0.0 0.2 SQL*Net roundtrips to/from client 228 0.2 45.6 active txn count during cleanout 34 0.0 6.8 application wait time 10 0.0 2.0 auto extends on undo tablespace 0 0.0 0.0 background checkpoints completed 1 0.0 0.2 background checkpoints started 0 0.0 0.0 background timeouts 3,198 3.2 639.6 branch node splits 0 0.0 0.0 buffer is not pinned count 43,166 42.9 8,633.2 buffer is pinned count 47,124 46.8 9,424.8 bytes received via SQL*Net from c 53,791 53.4 10,758.2 bytes sent via SQL*Net to client 41,313 41.0 8,262.6 calls to get snapshot scn: kcmgss 22,449 22.3 4,489.8 calls to kcmgas 665 0.7 133.0 calls to kcmgcs 157 0.2 31.4 change write time 78 0.1 15.6 cleanout - number of ktugct calls 44 0.0 8.8 cleanouts and rollbacks - consist 0 0.0 0.0 cleanouts only - consistent read 7 0.0 1.4 cluster key scan block gets 5,254 5.2 1,050.8 cluster key scans 3,361 3.3 672.2 commit batch/immediate performed 0 0.0 0.0 commit batch/immediate requested 0 0.0 0.0 commit cleanout failures: buffer 0 0.0 0.0 commit cleanout failures: callbac 5 0.0 1.0 commit cleanouts 1,204 1.2 240.8 commit cleanouts successfully com 1,199 1.2 239.8 commit immediate performed 0 0.0 0.0 commit immediate requested 0 0.0 0.0 commit txn count during cleanout 24 0.0 4.8 concurrency wait time 72 0.1 14.4 consistent changes 1,147 1.1 229.4 consistent gets 1,421,897 1,412.0 284,379.4 Instance Activity Stats DB/Inst: XE/xe Snaps: 1-11 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ consistent gets - examination 30,643 30.4 6,128.6 consistent gets direct 1 0.0 0.2 consistent gets from cache 1,421,896 1,412.0 284,379.2 cursor authentications 125 0.1 25.0 data blocks consistent reads - un 129 0.1 25.8 db block changes 36,763 36.5 7,352.6 db block gets 31,010 30.8 6,202.0 db block gets direct 1 0.0 0.2 db block gets from cache 31,009 30.8 6,201.8 deferred (CURRENT) block cleanout 499 0.5 99.8 dirty buffers inspected 407 0.4 81.4 enqueue conversions 434 0.4 86.8 enqueue releases 10,472 10.4 2,094.4 enqueue requests 10,476 10.4 2,095.2 enqueue timeouts 0 0.0 0.0 enqueue waits 27 0.0 5.4 execute count 13,428 13.3 2,685.6 free buffer inspected 663,373 658.8 132,674.6 free buffer requested 644,394 639.9 128,878.8 heap block compress 1 0.0 0.2 hot buffers moved to head of LRU 2,041 2.0 408.2 immediate (CR) block cleanout app 7 0.0 1.4 immediate (CURRENT) block cleanou 261 0.3 52.2 index fast full scans (full) 0 0.0 0.0 index fetch by key 30,382 30.2 6,076.4 index scans kdiixs1 7,825 7.8 1,565.0 leaf node 90-10 splits 1 0.0 0.2 leaf node splits 11 0.0 2.2 lob reads 437 0.4 87.4 lob writes 367 0.4 73.4 lob writes unaligned 367 0.4 73.4 logons cumulative 11 0.0 2.2 messages received 748 0.7 149.6 messages sent 748 0.7 149.6 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 1,375,488 1,365.9 275,097.6 opened cursors cumulative 11,650 11.6 2,330.0 parse count (failures) 1 0.0 0.2 parse count (hard) 633 0.6 126.6 parse count (total) 5,115 5.1 1,023.0 parse time cpu 194 0.2 38.8 parse time elapsed 1,282 1.3 256.4 physical read IO requests 38,015 37.8 7,603.0 physical read bytes 5,270,757,376 5,234,118.6 ############ physical read total IO requests 38,460 38.2 7,692.0 physical read total bytes 5,278,007,296 5,241,318.1 ############ physical read total multi block r 35,685 35.4 7,137.0 physical reads 643,403 638.9 128,680.6 physical reads cache 643,396 638.9 128,679.2 physical reads cache prefetch 605,388 601.2 121,077.6 physical reads direct 7 0.0 1.4 physical reads direct (lob) 0 0.0 0.0 physical reads direct temporary t 0 0.0 0.0 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 479 0.5 95.8 physical write bytes 8,667,136 8,606.9 1,733,427.2 Instance Activity Stats DB/Inst: XE/xe Snaps: 1-11 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical write total IO requests 1,074 1.1 214.8 physical write total bytes 19,943,936 19,805.3 3,988,787.2 physical write total multi block 368 0.4 73.6 physical writes 1,058 1.1 211.6 physical writes direct 7 0.0 1.4 physical writes direct (lob) 1 0.0 0.2 physical writes direct temporary 0 0.0 0.0 physical writes from cache 1,051 1.0 210.2 physical writes non checkpoint 976 1.0 195.2 pinned buffers inspected 13 0.0 2.6 prefetch warmup blocks aged out b 0 0.0 0.0 prefetched blocks aged out before 0 0.0 0.0 process last non-idle time 1,036 1.0 207.2 recovery blocks read 0 0.0 0.0 recursive calls 176,688 175.5 35,337.6 recursive cpu usage 7,315 7.3 1,463.0 redo blocks read for recovery 0 0.0 0.0 redo blocks written 11,177 11.1 2,235.4 redo buffer allocation retries 0 0.0 0.0 redo entries 18,570 18.4 3,714.0 redo log space requests 0 0.0 0.0 redo log space wait time 0 0.0 0.0 redo ordering marks 2 0.0 0.4 redo size 5,257,964 5,221.4 1,051,592.8 redo synch time 2 0.0 0.4 redo synch writes 219 0.2 43.8 redo wastage 57,744 57.3 11,548.8 redo write time 231 0.2 46.2 redo writer latching time 0 0.0 0.0 redo writes 254 0.3 50.8 rollback changes - undo records a 0 0.0 0.0 rollbacks only - consistent read 129 0.1 25.8 rows fetched via callback 7,706 7.7 1,541.2 session connect time 0 0.0 0.0 session cursor cache hits 9,052 9.0 1,810.4 session logical reads 1,452,907 1,442.8 290,581.4 session pga memory 384,596 381.9 76,919.2 session uga memory 8,589,319,588 8,529,612.3 ############ session uga memory max 10,589,640 10,516.0 2,117,928.0 shared hash latch upgrades - no w 7,713 7.7 1,542.6 sorts (disk) 0 0.0 0.0 sorts (memory) 3,560 3.5 712.0 sorts (rows) 30,231 30.0 6,046.2 sql area purged 18 0.0 3.6 summed dirty queue length 2,462 2.4 492.4 switch current to new buffer 162 0.2 32.4 table fetch by rowid 16,630 16.5 3,326.0 table fetch continued row 69 0.1 13.8 table scan blocks gotten 1,355,914 1,346.5 271,182.8 table scan rows gotten 368,365,456 365,804.8 ############ table scans (cache partitions) 0 0.0 0.0 table scans (long tables) 1,255 1.3 251.0 table scans (short tables) 380 0.4 76.0 total number of times SMON posted 40 0.0 8.0 transaction rollbacks 0 0.0 0.0 undo change vector size 2,502,916 2,485.5 500,583.2 Instance Activity Stats DB/Inst: XE/xe Snaps: 1-11 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ user I/O wait time 35,110 34.9 7,022.0 user calls 344 0.3 68.8 user commits 5 0.0 1.0 workarea executions - optimal 1,951 1.9 390.2 write clones created in backgroun 0 0.0 0.0 write clones created in foregroun 1 0.0 0.2 ------------------------------------------------------------- Instance Activity Stats DB/Inst: XE/xe Snaps: 1-11 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 22 21 opened cursors current 145 134 session cursor cache count 698 880 workarea memory allocated 861 0 ------------------------------------------------------------- Instance Activity Stats DB/Inst: XE/xe Snaps: 1-11 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour --------------------------------- ------------------ --------- log switches (derived) 0 .00 ------------------------------------------------------------- OS Statistics DB/Inst: XE/xe Snaps: 1-11 -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------- BUSY_TIME 15,714 IDLE_TIME 84,370 SYS_TIME 7,114 USER_TIME 8,600 RSRC_MGR_CPU_WAIT_TIME 158 VM_IN_BYTES 43,008,000 VM_OUT_BYTES 47,185,920 PHYSICAL_MEMORY_BYTES 526,344,192 NUM_CPUS 1 NUM_CPU_CORES 1 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: XE/xe Snaps: 1-11 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ USERS 117,855 117 11.3 17.8 5,095 5 0 0.0 SYSTEM 8,775 9 12.6 1.3 757 1 13 54.6 UNDO 115 0 411.7 1.0 8,943 9 0 0.0 SYSAUX 3,090 3 62.5 1.7 967 1 19 28.4 HANDSONXE 26 0 71.5 1.0 18 0 0 0.0 TEMP 0 0 0.0 3 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: XE/xe Snaps: 1-11 ->Mx Rd Bkt: Max bucket time for single block read ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Mx Av Av Rd Rd Av Av Buffer BufWt Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ HANDSONXE C:\ORACLEXE\ORADATA\XE\HANDSONXE01.DBF 13 0 90.8 1.0 9 0 0 C:\ORACLEXE\ORADATA\XE\HANDSONXE02.DBF 13 0 52.3 1.0 9 0 0 SYSAUX C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 3,090 3 62.5 ### 1.7 967 1 19 28.4 SYSTEM C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 8,775 9 12.6 ### 1.3 757 1 13 54.6 TEMP C:\ORACLEXE\ORADATA\XE\TEMP.DBF 0 0 3 0 0 UNDO C:\ORACLEXE\ORADATA\XE\UNDO.DBF 115 0 ##### 32 1.0 8,943 9 0 USERS C:\ORACLEXE\ORADATA\XE\USERS.DBF 117,855 117 11.3 ### 17.8 5,095 5 0 ------------------------------------------------------------- File Read Histogram Stats DB/Inst: XE/xe Snaps: 1-11 ->Number of single block reads in each time range ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- 0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ ------------ SYSAUX C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 375 11 31 117 64 24 SYSTEM C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 100 12 49 160 190 60 UNDO C:\ORACLEXE\ORADATA\XE\UNDO.DBF 0 0 0 0 2 0 USERS C:\ORACLEXE\ORADATA\XE\USERS.DBF 392 140 230 279 70 19 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: XE/xe Snaps: 1-11 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k -> Buffers: the number of buffers. Units of K, M, G are divided by 1000 Free Writ Buffer Pool Buffer Physical Physical Buffer Comp Busy P Buffers Hit% Gets Reads Writes Waits Wait Waits --- ------- ---- -------------- ------------ ----------- ------- ---- ---------- D 6986 56 1,454,479 644,192 1,053 0 0 0 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: XE/xe Snaps: 1-11 -> 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 0 0 751 48642 47664 92160 47664 E 0 0 763 11797 11350 92160 11350 ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: XE/xe End Snap: 11 -> Only rows with estimated physical reads >0 are displayed -> ordered by Pool, Block Size, Buffers For Estimate Est Phys Estimated Est Size for Size Buffers Read Phys Reads Est Phys % dbtime P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds --- -------- ----- ------------ ------ -------------- ------------ -------- D 4 .1 0 1.0 2,140 1,733 87.4 D 8 .1 1 1.0 2,128 1,595 80.5 D 12 .2 1 1.0 2,125 1,563 78.9 D 16 .3 2 1.0 2,123 1,548 78.1 D 20 .4 2 1.0 2,122 1,533 77.3 D 24 .4 3 1.0 2,121 1,526 77.0 D 28 .5 3 1.0 2,119 1,506 76.0 D 32 .6 4 1.0 2,118 1,496 75.5 D 36 .6 4 1.0 2,118 1,489 75.1 D 40 .7 5 1.0 2,118 1,486 75.0 D 44 .8 5 1.0 2,117 1,482 74.8 D 48 .9 6 1.0 2,117 1,479 74.6 D 52 .9 6 1.0 2,117 1,476 74.5 D 56 1.0 7 1.0 2,117 1,474 74.4 D 60 1.1 7 1.0 2,116 1,470 74.2 D 64 1.1 8 1.0 2,116 1,468 74.1 D 68 1.2 8 1.0 2,116 1,465 73.9 D 72 1.3 9 1.0 2,115 1,461 73.7 D 76 1.4 9 1.0 2,093 1,310 66.1 D 80 1.4 10 1.0 2,083 1,310 66.1 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: XE/xe Snaps: 1-11 -> B: Begin snap E: End snap (rows identified 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 --------------- ---------------- ------------------------- 100.0 26 0 %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 40 17 46.9 0.4 .9 100.0 .0 8,192 E 40 18 43.1 0.0 .0 .0 .0 8,192 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: XE/xe Snaps: 1-11 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 1,917 1,917 0 0 64K 128K 3 3 0 0 512K 1024K 26 26 0 0 1M 2M 3 3 0 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: XE/xe End Snap: 11 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 10 0.3 155.2 41.9 79.0 2 20 0.5 155.2 41.9 79.0 2 30 0.8 155.2 10.5 94.0 0 40 1.0 155.2 0.0 100.0 0 48 1.2 155.2 0.0 100.0 0 56 1.4 155.2 0.0 100.0 0 64 1.6 155.2 0.0 100.0 0 72 1.8 155.2 0.0 100.0 0 80 2.0 155.2 0.0 100.0 0 120 3.0 155.2 0.0 100.0 0 160 4.0 155.2 0.0 100.0 0 240 6.0 155.2 0.0 100.0 0 320 8.0 155.2 0.0 100.0 0 ------------------------------------------------------------- Process Memory Summary Stats DB/Inst: XE/xe Snaps: 1-11 -> B: Begin snap E: End snap -> All rows below contain absolute values (i.e. not diffed over the interval) -> Max Alloc is Maximum PGA Allocation size at snapshot time Hist Max Alloc is the Historical Max Allocation for still-connected processes -> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of processes. For Category lines, it is the number of allocations -> ordered by Begin/End snapshot, Alloc (MB) desc Hist Num Avg Std Dev Max Max Procs Alloc Used Freeabl Alloc Alloc Alloc Alloc or Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs - -------- --------- --------- -------- -------- ------- ------- ------ ------ B -------- 47.2 21.8 .0 2.2 2.5 9 17 21 Other 45.0 2.1 2.5 9 12 21 PL/SQL 1.2 .1 .1 .1 0 0 20 SQL 1.1 .8 .1 .2 1 5 8 E -------- 43.3 20.7 .0 2.2 2.5 9 17 20 Other 41.7 2.1 2.4 9 12 20 PL/SQL 1.1 .1 .1 .1 0 0 19 SQL .5 .2 .0 .0 0 5 7 ------------------------------------------------------------- Top Process Memory (by component) DB/Inst: XE/xe Snaps: 1-11 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 6 LGWR -------- 8.8 4.2 .0 8.8 9.7 Other 8.8 8.8 9.7 PL/SQL .0 .0 .0 .0 14 S000 -------- 6.9 2.9 .0 6.9 13.1 Other 6.5 6.5 11.7 PL/SQL .4 .0 .4 .4 SQL .0 .0 .0 1.1 15 S001 -------- 5.7 2.5 .0 5.7 17.4 Other 5.4 5.4 12.1 PL/SQL .3 .0 .3 .3 SQL .0 .0 .0 5.0 17 S003 -------- 5.5 2.5 .0 5.5 11.7 Other 5.2 5.2 10.7 PL/SQL .3 .0 .3 .3 SQL .0 .0 .0 .7 16 S002 -------- 5.1 2.3 .0 5.1 8.3 Other 5.0 5.0 7.5 PL/SQL .1 .0 .1 .1 SQL .0 .0 .0 .7 20 SHAD -------- 2.1 1.1 .0 2.1 4.0 Other 2.0 2.0 2.0 SQL .1 .1 .1 2.2 PL/SQL .0 .0 .0 .0 5 DBW0 -------- 1.8 .3 .0 1.8 1.8 Other 1.8 1.8 1.8 PL/SQL .0 .0 .0 .0 7 CKPT -------- 1.8 .3 .0 1.8 1.8 Other 1.8 1.8 1.8 SQL .0 .0 .0 .0 PL/SQL .0 .0 .0 .0 18 SHAD -------- 1.5 1.2 .0 1.5 2.4 Other .8 .8 1.1 SQL .7 .6 .7 1.3 PL/SQL .0 .0 .0 .0 11 MMON -------- 1.5 1.1 .0 1.5 2.4 Other 1.4 1.4 1.4 SQL .1 .0 .1 .9 PL/SQL .0 .0 .0 .1 13 D000 -------- 1.2 .5 .0 1.2 1.2 Other 1.2 1.2 1.2 8 SMON -------- 1.1 .4 .0 1.1 1.1 Other 1.0 1.0 1.0 SQL .1 .0 .1 .2 PL/SQL .0 .0 .0 .0 E 6 LGWR -------- 8.8 4.2 .0 8.8 9.7 Other 8.8 8.8 9.7 PL/SQL .0 .0 .0 .0 14 S000 -------- 6.9 2.9 .0 6.9 13.1 Other 6.5 6.5 11.7 PL/SQL .4 .0 .4 .4 SQL .0 .0 .0 1.1 15 S001 -------- 5.7 2.5 .0 5.7 17.4 Other 5.4 5.4 12.1 Top Process Memory (by component) DB/Inst: XE/xe Snaps: 1-11 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 15 PL/SQL .3 .0 .3 .3 SQL .0 .0 .0 5.0 17 S003 -------- 5.5 2.5 .0 5.5 11.7 Other 5.2 5.2 10.7 PL/SQL .3 .0 .3 .3 SQL .0 .0 .0 .7 16 S002 -------- 2.6 2.4 .0 2.6 8.3 Other 2.5 2.5 7.5 PL/SQL .1 .0 .1 .1 SQL .0 .0 .0 .7 18 SHAD -------- 2.0 .9 .0 2.0 3.9 Other 1.9 1.9 1.9 SQL .1 .1 .1 2.2 PL/SQL .0 .0 .0 .0 5 DBW0 -------- 1.8 .3 .0 1.8 1.8 Other 1.8 1.8 1.8 PL/SQL .0 .0 .0 .0 7 CKPT -------- 1.8 .3 .0 1.8 1.8 Other 1.8 1.8 1.8 SQL .0 .0 .0 .0 PL/SQL .0 .0 .0 .0 11 MMON -------- 1.5 1.1 .0 1.5 2.8 Other 1.4 1.4 1.9 SQL .1 .0 .1 .9 PL/SQL .0 .0 .0 .1 13 D000 -------- 1.2 .5 .0 1.2 1.2 Other 1.2 1.2 1.2 8 SMON -------- 1.1 .4 .0 1.1 1.1 Other 1.0 1.0 1.0 SQL .1 .0 .1 .2 PL/SQL .0 .0 .0 .0 ------------------------------------------------------------- Enqueue activity DB/Inst: XE/xe Snaps: 1-11 -> only enqueues with waits are shown -> Enqueue stats gathered prior to 10g should not be compared with 10g data -> ordered by Wait Time desc, Waits desc Enqueue Type (Request Reason) ------------------------------------------------------------------------------ Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------- RO-Multiple Object Reuse (fast object reuse) 333 333 0 27 0 4.63 ------------------------------------------------------------- Undo Segment Summary DB/Inst: XE/xe Snaps: 1-11 -> Min/Max TR (mins) - Min and Max Tuned Retention (minutes) -> STO - Snapshot Too Old count, OOS - Out Of Space count -> Undo segment block stats: uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed eS - expired Stolen, eR - expired Released, eU - expired reUsed Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/ TS# Blocks (K) Transactions Len (s) Concy TR (mins) OOS eS/eR/eU ---- ---------- --------------- -------- ---------- --------- ----- ----------- 1 .3 318 114 3 16/16 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: XE/xe Snaps: 1-11 -> Most recent 35 Undostat rows, ordered by End Time desc Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/ End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU ------------ ----------- ------------ ------- ------- ------- ----- ----------- 22-May 22:30 326 318 114 3 16 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: XE/xe Snaps: 1-11 ->"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 256 0.0 0 0 FOB s.o list latch 46 0.0 0 0 In memory undo latch 464 0.0 0 213 0.0 JS queue state obj latch 7,236 0.0 0 0 JS slv state obj latch 6 0.0 0 0 KMG MMAN ready and start 333 0.0 0 0 KTF sga latch 2 0.0 0 328 0.0 KWQP Prop Status 1 0.0 0 0 MQL Tracking Latch 0 0 20 0.0 Memory Management Latch 0 0 333 0.0 OS process 60 0.0 0 0 OS process allocation 372 0.0 0 0 OS process: request allo 19 0.0 0 0 PL/SQL warning settings 129 0.0 0 0 SQL memory manager latch 1 0.0 0 333 0.0 SQL memory manager worka 25,030 0.0 0 0 Shared B-Tree 38 0.0 0 0 active checkpoint queue 1,019 0.0 0 0 active service list 2,061 0.0 0 350 0.0 archive control 53 0.0 0 0 begin backup scn array 1 0.0 0 0 cache buffer handles 60 0.0 0 0 cache buffers chains 3,595,603 0.0 0 3,549,738 0.0 cache buffers lru chain 648,256 0.0 0 3,651,182 0.0 cache table scan latch 35,671 0.0 0 35,670 0.0 channel handle pool latc 59 0.0 0 0 channel operations paren 6,129 0.0 0 0 checkpoint queue latch 8,056 0.0 0 1,039 0.0 client/application info 51 0.0 0 0 commit callback allocati 36 0.0 0 0 compile environment latc 73 0.0 0 0 dml lock allocation 1,363 0.0 0 0 dummy allocation 23 0.0 0 0 enqueue hash chains 21,455 0.0 0 11 0.0 enqueues 19,016 0.0 0 0 event group latch 9 0.0 0 0 file cache latch 154 0.0 0 0 global KZLD latch for me 4 0.0 0 0 hash table column usage 235 0.0 0 15,772 0.0 hash table modification 106 0.0 0 0 job workq parent latch 0 0 4 0.0 job_queue_processes para 18 0.0 0 0 kks stats 2,048 0.0 0 0 kmcptab latch 1 0.0 0 0 kmcpvec latch 0 0 1 0.0 ksuosstats global area 69 0.0 0 0 ktm global data 44 0.0 0 0 kwqbsn:qsga 38 0.0 0 0 lgwr LWN SCN 539 0.0 0 0 library cache 70,376 0.0 1.1 0 788 0.1 Latch Activity DB/Inst: XE/xe Snaps: 1-11 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ library cache load lock 3,514 0.0 0 0 library cache lock 21,538 0.0 0 0 library cache lock alloc 388 0.0 0 0 library cache pin 43,840 0.0 0 0 library cache pin alloca 344 0.0 0 0 list of block allocation 30 0.0 0 0 loader state object free 18 0.0 0 0 message pool operations 187 0.0 0 0 messages 8,895 0.0 0 0 mostly latch-free SCN 539 0.0 0 0 multiblock read objects 72,091 0.0 0 0 ncodef allocation latch 17 0.0 0 0 object queue header heap 288 0.0 0 145 0.0 object queue header oper 1,292,280 0.0 0 0 object stats modificatio 112 0.0 0 0 parallel query alloc buf 132 0.0 0 0 parameter table allocati 12 0.0 0 0 post/wait queue 8 0.0 0 6 0.0 process allocation 19 0.0 0 9 0.0 process group creation 19 0.0 0 0 qmn task queue latch 140 12.9 1.0 0 0 redo allocation 19,813 0.0 1.5 0 0 redo copy 2 0.0 0 18,590 0.0 redo writing 2,193 0.0 0 0 resmgr group change latc 12 0.0 0 0 resmgr:actses active lis 219 0.0 0 0 resmgr:actses change gro 6 0.0 0 0 resmgr:free threads list 17 0.0 0 0 resmgr:resource group CP 111 0.0 0 0 resmgr:schema config 205 0.0 0 0 row cache objects 77,497 0.0 1.1 0 1,670 0.1 rules engine rule set st 100 0.0 0 0 sequence cache 24 0.0 0 0 session allocation 329,382 0.0 1.0 0 0 session idle bit 752 0.0 0 0 session state list latch 39 0.0 0 0 session switching 17 0.0 0 0 session timer 350 0.0 0 0 shared pool 58,619 0.0 1.0 0 0 shared pool sim alloc 36 0.0 0 0 shared pool simulator 37,435 0.0 1.0 0 0 simulator hash latch 381,389 0.0 0 0 simulator lru latch 175,306 0.0 0 39,721 0.0 slave class 3 0.0 0 0 slave class create 12 0.0 0 0 sort extent pool 68 0.0 0 0 state object free list 2 0.0 0 0 temp lob duration state 2 0.0 0 0 threshold alerts latch 34 0.0 0 0 transaction allocation 22 0.0 0 0 Latch Activity DB/Inst: XE/xe Snaps: 1-11 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ transaction branch alloc 17 0.0 0 0 undo global data 1,513 0.0 0 0 user lock 22 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown DB/Inst: XE/xe Snaps: 1-11 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- library cache 70,376 21 24 0 qmn task queue latch 140 18 18 0 shared pool 58,619 10 10 0 row cache objects 77,497 7 8 0 redo allocation 19,813 2 3 0 session allocation 329,382 1 1 0 shared pool simulator 37,435 1 1 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: XE/xe Snaps: 1-11 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- event range base latch No latch 0 18 18 library cache kglobpn: child: 0 7 0 library cache kglobld 0 4 2 library cache kglpnc: child 0 3 6 library cache kglpndl: child: before pro 0 2 16 library cache kglhdgn: child: 0 1 3 library cache kglpin 0 1 1 redo allocation kcrfw_redo_gen 0 3 3 row cache objects kqreqd: reget 0 3 0 row cache objects kqrpre: find obj 0 3 2 row cache objects kqrso 0 1 0 session allocation ksudlc 0 1 2 shared pool kghalo 0 9 2 shared pool kghfrunp: clatch: nowait 0 7 0 shared pool kghalp 0 1 2 shared pool simulator kglsim_upd_newhp 0 1 0 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: XE/xe Snaps: 1-11 ->"Pct Misses" should be very low (< 2% in most cases) ->"Final Usage" is the number of cache entries being used in End Snapshot Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_awr_control 25 4.0 0 1 1 dc_files 12 50.0 0 0 6 dc_global_oids 142 7.0 0 0 10 dc_histogram_data 1,607 42.1 0 0 589 dc_histogram_defs 4,837 51.7 0 0 1,044 dc_object_grants 92 19.6 0 0 24 dc_object_ids 3,595 15.7 0 0 476 dc_objects 2,407 24.2 0 55 445 dc_profiles 6 16.7 0 0 1 dc_rollback_segments 86 0.0 0 0 12 dc_segments 1,227 43.2 0 111 254 dc_sequences 3 100.0 0 3 2 dc_tablespaces 5,360 0.1 0 0 4 dc_usernames 296 1.4 0 0 5 dc_users 5,405 0.2 0 0 18 outstanding_alerts 7 71.4 0 0 0 ------------------------------------------------------------- Library Cache Activity DB/Inst: XE/xe Snaps: 1-11 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 30 16.7 84 17.9 10 0 CLUSTER 42 2.4 97 2.1 1 0 INDEX 20 95.0 38 50.0 0 0 SQL AREA 561 90.2 18,165 6.0 185 18 TABLE/PROCEDURE 1,506 31.1 4,872 34.5 218 0 ------------------------------------------------------------- Rule Sets DB/Inst: XE/xe Snaps: 1-11 -> * indicates Rule Set activity (re)started between Begin/End snaps -> Top 25 ordered by Evaluations desc No-SQL SQL Rule * Eval/sec Reloads/sec Eval % Eval % ----------------------------------- - ------------ ----------- ------ ------ SYS.ALERT_QUE_R 0 0 0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: XE/xe End Snap: 11 -> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor -> 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 Est LC Est LC Est LC Est LC Shared SP Est LC Time Time Load Load Est LC Pool Size Size Est LC Saved Saved Time Time Mem Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 56 .9 8 1,186 8,311 1.0 102 1.3 2,011,478 64 1.0 15 2,198 8,333 1.0 80 1.0 2,012,824 72 1.1 22 2,894 8,337 1.0 76 1.0 2,013,067 80 1.3 29 3,559 8,344 1.0 69 .9 2,013,400 88 1.4 36 4,353 8,348 1.0 65 .8 2,013,747 96 1.5 43 4,937 8,348 1.0 65 .8 2,013,867 104 1.6 51 6,059 8,351 1.0 62 .8 2,014,064 112 1.8 58 6,548 8,351 1.0 62 .8 2,014,151 120 1.9 65 7,196 8,351 1.0 62 .8 2,014,187 128 2.0 72 8,022 8,351 1.0 62 .8 2,014,209 ------------------------------------------------------------- SGA Target Advisory DB/Inst: XE/xe End Snap: 11 SGA Target SGA Size Est DB Est DB Est Physical Size (M) Factor Time (s) Time Factor Reads ---------- -------- -------- ----------- -------------- 105 .8 2,034 1.0 2,119,158 140 1.0 1,982 1.0 2,116,407 175 1.3 1,807 .9 2,092,915 210 1.5 1,800 .9 2,082,756 245 1.8 1,800 .9 2,082,756 280 2.0 1,800 .9 2,082,756 ------------------------------------------------------------- SGA Memory Summary DB/Inst: XE/xe Snaps: 1-11 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ -------------------- -------------------- Database Buffers 58,720,256 Fixed Size 1,286,220 Redo Buffers 2,904,064 Variable Size 83,890,100 -------------------- -------------------- sum 146,800,640 ------------------------------------------------------------- SGA breakdown difference DB/Inst: XE/xe Snaps: 1-11 -> Top 35 rows by size, ordered by Pool, Name (note rows with null values for Pool column, or Names showing free memory are always shown) -> Null value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot Pool Name Begin MB End MB % Diff ------ ------------------------------ -------------- -------------- -------- java p free memory 4.0 4.0 0.00 large free memory 3.7 3.7 0.00 large session heap 8.2 8.2 0.00 shared ASH buffers 2.0 2.0 0.00 shared CCursor 1.1 1.2 5.46 shared Heap0: KGL .9 1.0 15.92 shared KCB Table Scan Buffer 3.8 3.8 0.00 shared KGLS heap 1.7 2.6 49.81 shared KQR M PO 1.0 ######## shared KSFD SGA I/O b 3.8 3.8 0.00 shared KSXR pending messages que .8 .8 0.00 shared KSXR receive buffers 1.0 1.0 0.00 shared KTI-UNDO .7 .7 0.00 shared PCursor .7 .7 10.15 shared PL/SQL DIANA .9 -100.00 shared PL/SQL MPCODE 2.6 -100.00 shared XDB Schema Cac 4.8 4.8 0.00 shared free memory 10.6 11.3 6.27 shared kglsim hash table bkts 2.0 2.0 0.00 shared kks stbkt .9 .9 0.00 shared library cache 3.0 3.2 6.45 shared message pool freequeue .7 .7 0.00 shared returns from metrics req .7 .7 0.00 shared row cache 3.6 3.6 0.00 shared sql area 3.4 3.3 -1.97 buffer_cache 56.0 56.0 0.00 fixed_sga 1.2 1.2 0.00 log_buffer 2.8 2.8 0.00 ------------------------------------------------------------- SQL Memory Statistics DB/Inst: XE/xe Snaps: 1-11 Begin End % Diff -------------- -------------- -------------- Avg Cursor Size (KB): 44.31 49.92 11.22 Cursor to Parent ratio: 1.12 1.12 -.06 Total Cursors: 379 390 2.82 Total Parents: 337 347 2.88 ------------------------------------------------------------- init.ora Parameters DB/Inst: XE/xe Snaps: 1-11 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- audit_file_dest C:\ORACLEXE\APP\ORACLE\ADMIN\XE\A background_dump_dest C:\ORACLEXE\APP\ORACLE\ADMIN\XE\B compatible 10.2.0.1.0 control_files C:\ORACLEXE\ORADATA\XE\CONTROL.DB core_dump_dest C:\ORACLEXE\APP\ORACLE\ADMIN\XE\C cursor_sharing EXACT db_name XE db_recovery_file_dest C:\oraclexe\app\oracle\flash_reco db_recovery_file_dest_size 10737418240 dispatchers (PROTOCOL=TCP) (SERVICE=XEXDB) job_queue_processes 4 open_cursors 300 os_authent_prefix pga_aggregate_target 41943040 remote_login_passwordfile EXCLUSIVE sessions 49 sga_target 146800640 shared_servers 4 spfile C:\ORACLEXE\APP\ORACLE\PRODUCT\10 undo_management AUTO undo_tablespace UNDO user_dump_dest C:\ORACLEXE\APP\ORACLE\ADMIN\XE\U ------------------------------------------------------------- End of Report ( D:\sp_1_2.txt ) Discover and Display Unindexed Foreign Keys Move the Flash Recovery Area and Online Redo Logs Enable Media Recovery Create a Hot Database Backup Create Log Groups with Multiple Members ALTER DATABASE Mirror the Database Control File SHOW ALL; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET; Back Up a Database Back Up an Individual Tablespace Back Up Archived Log Files List Information About Backup Sets Report Important Status Information for a Database References Daw05: , (Dawes, 2005), Read More