Database

 

7) Check the Memory Usage REM Monitor memory usage select sid, name, value from v$statname n, v$sesstat s where n.statistic# = s.statistic# and n.name like '%memory%' order by sid; REM monitor CPU usage select sid, name, value from v$statname n, v$sesstat s where n.statistic# = s.statistic# and n.name like  '%cpu%' order by sid; REM monitor I/O select file#, phyrds, phywrts from v$filestat; Depending wesome can able find which file the is having more hits & i f possibleupon movethe thephyrds file to otherto disk for reducing cpu hit. REM The Following Query can give a good idea of what the session is doing and ho w much CPU they have consumed select ss.sid,se.command,ss.value CPU ,se.username,se.program from v$sesstat ss, v$session se where ss.statistic# in (select statistic# from v $statname where name = 'CPU used used by this this session') and se.sid=ss.sid se.sid=ss.sid and s ss.sid s.sid >6 order by ss.sid 3) If th the e tem tempo pora rary ry Ta Tabl bles espa pace ce is no not t tem tempo pora rary ry th then en cr crea eate te a n new ew te temp mpor orar ary y  Tablespace & switch over all the users from old to new temporary Tablespace CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/home1/oradata/enggaprd/TEMP01.DBF' '/home1/oradata/enggaprd/TEMP01.DBF' S IZE 750M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MA NAGEMENT MANUAL; 4) Th The e bel below ow Qu Quer ery y is is u use sed d to to f fin ind d whi which ch us user er is co cons nsum umin ing g mor more e in in t tem empo pora rar r y tablespaces. select s.username,u.tablespace, u.contents,u.extents, u.blocks from v$session s,  v$sort_usage u where s.saddr=u.session_addr; 5) To fi find nd ou out t if if a T TEM EMP P Se Segm gmen ent t is FR FREE EE or be bein ing g US USED ED an and d als also o in in wh whic ich h Ta Ta blespace the segment resides, query the new dynamic performance view V$SORT_SEGM ENT select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_B TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS LOCKS from v$sort_segment;