spool KCA_DB_TEST.txt

doc

KCA check script



Captuerd by OCM Yeonhong Min


1. ~~~ GENERAL ~~~

1.1 Oracle Instance Information

Purpose - ´ë»ó µ¥ÀÌÅͺ£À̽º ÀνºÅϽºÀÇ ±âº»Á¤º¸¸¦ È®ÀÎÇÑ´Ù.
#
set pages 40
set line 132
col host format a20

select name "DB Name", instance_number "Inst ID", instance_name "Inst Name",
host_name "Host", version "Version", startup_time "Startup Time",
parallel "Parallel", log_mode "Log Mode", archiver "Archiver"
from v$instance, v$database
/

doc
1.2 Current Session Count
Purpose - ´ë»ó µ¥ÀÌŸº£À̽º¿¡ Á¢¼ÓµÇ¾î ÀÖ´Â ¸ðµç Session°ú ÇöÀç ÀÛ¾÷ÁßÀÎ SessionÀÇ ¼ö¸¦ È®ÀÎÇÑ´Ù.
#

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') "Time",
count(*) "Total Sessions",
count(decode(status, 'ACTIVE',1) ) "Active Sessions"
from v$session
/

doc
1.3 Invalid Object Count

Purpose - ¿ÀºêÁ§Æ®ÀÇ À¯Çüº°·Î Invalid»óÅÂÀÎ ¿ÀºêÁ§Æ®ÀÇ ¼ö¸¦ Á¡°ËÇÑ´Ù. 

#
select /*+ ORDERED_PREDICATES */
object_type "Object Type",
count(*) "Invalid Count"
from dba_objects
where status = 'INVALID'
group by object_type
/


doc
1.4 Current Transactions

Purpose - ÇöÀç ÁøÇàÁßÀÎ Æ®·£Àè¼ÇµéÀÇ ¸ñ·Ï°ú °ü·ÃÁ¤º¸¸¦ È®ÀÎÇÑ´Ù. 

#

select /*+ ordered */ s.username,
s.sid sid ,
s.machine "Machine" ,
s.program "Pgm",
t.status "Status",
RECURSIVE "Recur",
to_char(to_date(t.start_time, 'mm/dd/yy HH24:MI:SS'), 'yyyy/mm/dd hh24:mi:ss') "StartTime",
r.name "RBS",
t.used_ublk "UBLKS",
t.used_urec "URECS",
t.log_io "L_IO",
t.PHY_IO "P_IO"
from v$transaction t, v$session s, v$rollname r, v$process p
where s.saddr = t.ses_addr and t.xidusn = r.usn
and s.paddr = p.addr
order by used_ublk
/

doc
1.5 Resource Limit

Purpose - µ¥ÀÌŸº£À̽ºÀÇ ½Ã½ºÅÛ¸®¼Ò½ºÀÇ ÇÒ´ç³»¿ª°ú ¼Ò¸ðÇöȲÀ» È®ÀÎÇÑ´Ù. 

#
select RESOURCE_NAME "Resource Name",
CURRENT_UTILIZATION "Current Util",
MAX_UTILIZATION "Max Util",
INITIAL_ALLOCATION "Initial Alloc",
LIMIT_VALUE "Limit"
from v$resource_limit
/

doc

1.6 WorkLoad Breakdown

Purpose - ¼¼¼Çº°·Î ¼­ºñ½º ½Ã°£°ú Wait½Ã°£ÀÇ ºñÀ² ¹× ¼­ºñ½º½Ã°£ÀÇ ±¸¼ºÇöȲÀ» Á¡°ËÇÑ´Ù. 

#

select cpu.sid, cpu.t_cpu "Service Time", wait.t_wait "Wait Time",
round(decode(cpu.t_cpu,0,0, (wait.t_wait/cpu.t_cpu )*100),2) "Wait/Service(%%)",
round(decode(cpu.t_cpu,0,0, (cpu.p_cpu/cpu.t_cpu)*100),2) "Service(Parse)%%",
round(decode(cpu.t_cpu,0,0, (cpu.r_cpu/cpu.t_cpu)*100),2) "Service(Recursive)%%",
round(decode(cpu.t_cpu,0,0, ((cpu.t_cpu-cpu.r_cpu-cpu.p_cpu)/cpu.t_cpu)*100),2) "Service(Other)%%"
from
(select /*+ ordered */ sid, sum(decode(n.name,'CPU used by this session', value)) t_cpu,
sum(decode(n.name,'parse time cpu', value)) p_cpu,
sum(decode(n.name,'recursive cpu usage', value)) r_cpu
from v$statname n, v$sesstat s
where s.statistic# = n.statistic#
and n.name in ( 'parse time cpu', 'CPU used by this session','recursive cpu usage')
group by sid ) cpu,
(select /*+ ordered */ sid, sum(time_waited) t_wait
from v$session_event
where event not like '%SQL*Net%'
and event not like '%pipe get%'
and event not like '%rdbms ipc message'
and event not like '%smon timer%'
and event not like '%pmon timer%'
group by sid ) wait
where cpu.sid = wait.sid
and cpu.t_cpu+wait.t_wait > 0
/

doc 
2. ~~~ SGA ~~~

2.1 Buffer Cache Hit Ratio

Purpose - µ¥ÀÌÅͺ£À̽º ¹öÆÛij½¬ÀÇ ÀûÁß·üÀ» Á¡°ËÇÑ´Ù. 

#

select to_char(sysdate,'yyyy/mm/dd HH24:MI:SS') "Time",
a.value+b.value "Logical Reads",
c.value "Physical Reads",
round((1 - (c.value/(a.value+b.value)))*100, 3) "Hit Ratio"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.name = 'db block gets'
and b.name = 'consistent gets'
and c.name = 'physical reads'
/

doc
2.2 Library Cache Hit Ratio (summary)

Purpose ¶óÀ̺귯¸® ij½¬ ÀüüÀÇ ÀûÁß·üÀ» Á¡°ËÇÑ´Ù. 

# 

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') "Time",
round((1-(sum(reloads)/sum(pins)))*100, 3) "Hit Ratio(Pins-Reloads)"
from v$librarycache
/

doc

2.3 Library Cache Hit Ratio (Namespace)

Purpose - Namespaceº° ¶óÀ̺귯¸® ij½¬ÀÇ ÀûÁß·üÀ» Á¡°ËÇÑ´Ù. 

#

select namespace "Namespace",
gets "Gets", round(gethitratio*100,3) "GetHit Ratio",
pins "Pins", round(pinhitratio*100,3) "PinHit Ratio",
invalidations "Invalidations",
decode(pins,0,-1,round((1-reloads/pins)*100, 3)) "Hit Ratio(Pins-Reloads)"
from v$librarycache
/

doc
2.4 Dictionary Cache Hit Ratio (summary)

Purpose - µñ¼Å³Ê¸® ij½¬ ÀüüÀÇ ÀûÁß·üÀ» Á¡°ËÇÑ´Ù. 

#

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') "Time",
round((1-sum(getmisses)/sum(gets))*100, 3) "Hit Ratio"
from v$rowcache
/

doc
2.5 Dictionary Cache Hit Ratio (Parameter)

Purpose - Parameterº° µñ¼Å³Ê¸® ij½¬ÀÇ ÀûÁß·üÀ» Á¡°ËÇÑ´Ù. 

#

SELECT parameter "Parameter",
sum(gets) "Gets",
sum(getmisses) "GetMisses" ,
decode(sum(gets), 0, -1, round((1-sum(getmisses)/sum(gets))*100, 3)) "Hit Ratio"
FROM v$rowcache
GROUP BY parameter
/

doc
2.6 Shared Pool Free Space

Purpose - Shared Pool³»ÀÇ Free Memory Å©±â¸¦ È®ÀÎÇÑ´Ù 

#

col "Shared Pool Reserved(MB)" format a40

select s.total "Shared Pool Total(MB)",
s.free - r.r_free "Shared Pool Free(MB)",
'Free : ' || r.r_free || ', Used : ' ||r.r_used "Shared Pool Reserved(MB)",
s.other - r.r_used "Shared Pool Other(MB)"
from (select round(sum(bytes/1024/1024),2) total,
round(sum(decode(name, 'free memory', bytes))/1024/1024,2) free,
round(sum(decode(name, 'free memory',null, bytes))/1024/1024,2) other
from v$sgastat
where pool = 'shared pool' ) s,
(select round(nvl(sum(free_space),0)/1024/1024,2) r_free,
round(nvl(sum(used_space),0)/1024/1024,2) r_used
from v$shared_pool_reserved ) r
/

doc
2.7 Redo Buffer Contention

Purpose - Redo Log Buffer¿¡ ´ëÇÑ °æÇÕ»óŸ¦ È®ÀÎÇÑ´Ù. 

#

select round(sum(decode(name, 'redo buffer allocation retries', value))/sum(decode(name, 'redo entries', value)),5)
"Redo Buffer Space Wait(%%)",
sum(decode(name, 'redo log space requests', value)) "Redo Space Reqeusts"
from v$sysstat
/

doc
2.8 Latch Hit Ratio

Purpose - SGA³»ÀÇ ¸ðµç Latch¿¡ ´ëÇÑ Hit Ratio¸¦ È®ÀÎÇÑ´Ù. 

#

select name "Name",
decode(sign(gets-immediate_gets), 1, gets, immediate_gets) "(I)Gets",
decode(sign(gets-immediate_gets), 1, misses, immediate_misses) "(I)Misses",
sleeps "Sleeps",
decode(sign(gets-immediate_gets), 1, round(decode(gets,0,-1,(1-misses/gets) * 100),3),
round(decode(immediate_gets+immediate_misses,0,-1,
(1- immediate_misses/ (immediate_gets+immediate_misses)) * 100), 3 )) "Hit Ratio"
from v$latch
where decode(sign(gets-immediate_gets), 1, gets, immediate_gets) > 0
order by 4 desc, 2 desc
/

doc
2.9 Session Cursor Cache Hit Ratio

Purpose - Àüü SQL Call °¡¿îµ¥ Session Cursor CacheÀÇ Hit Ratio¸¦ È®ÀÎÇÑ´Ù. 

#

select
round(100 * (calls - sess - hard) / calls, 3) "Soft Parses(%%)",
round(100 * hard / calls, 3) "Hard Parses(%%)",
decode(round(100 * sess / calls, 3),0,decode(parm,0,-1,0),round(100 * sess / calls, 3)) 
"Cursor Cache Hits(%%)"
from
( select value calls from v$sysstat where name = 'parse count (total)' ),
( select value hard from v$sysstat where name = 'parse count (hard)' ),
( select value sess from v$sysstat where name = 'session cursor cache hits' ),
( select value parm from v$parameter where name = 'session_cached_cursors')
/

doc
Wait Event

2.10 Block Waits

Purpose - ºí·Ï Ŭ·¡½ºº°·Î Wait ¹ß»ý³»¿ªÀ» È®ÀÎÇÑ´Ù. 

#
select class, count, time 
from
v$waitstat
/

doc
2.11 System Wait Event

Purpose - µ¥ÀÌŸº£À̽º ÀüüÀÇ Wait EventÀÇ Á¤º¸¸¦ È®ÀÎÇÑ´Ù. 

#
col event format a35
select event, total_waits "Total Waits",
time_waited "Time Waited(cs)",
round(average_wait,5) "Avg Wait Time(cs)",
total_timeouts "Total Timeouts"
from v$system_event
where event not like '%SQL*Net%'
and event not like '%pipe get%'
and event not like '%rdbms ipc%'
and event not like '%smon timer%'
and event not like '%pmon timer%'
and time_waited > 0
order by time_waited desc
/

doc
2.12 Session Wait Event

Purpose - ÇöÀç Á¢¼ÓÁßÀÎ SessionµéÀÇ Wait EventÁ¤º¸¸¦ È®ÀÎÇÑ´Ù. 

#
col event format a35
select sid, event,
total_waits "Total Waits",
time_waited "Time Waited (cs)",
round(average_wait,5) "Avg Wait Time (cs)",
total_timeouts "Total Timeouts"
from v$session_event
where event not like '%SQL*Net%'
and event not like '%pipe get%'
and event not like '%rdbms ipc%'
and event not like '%smon timer%'
and event not like '%pmon timer%'
and time_waited > 0
order by time_waited desc
/

doc

3. ~~~~ I/O ~~~~

3.1 File I/O 

Purpose - µ¥ÀÌÅÍ ÆÄÀÏÀÇ I/O Á¤µµ ¹× ½Ã°£À» È®ÀÎÇÑ´Ù. 

#

col "Name" format a50
SELECT name "Name",
phyrds "Phy Rds",
phywrts "Phy Wrts",
phyrds+phywrts "Total IO",
trunc(phyrds/read_sum*100,2) "Read(%%)",
trunc(phywrts/write_sum*100,2) "Write(%%)",
trunc( (phyrds+phywrts) / (read_sum+write_sum) * 100 , 2) "Total IO(%%)",
avgiotim "Avg IO Time"
FROM v$filestat a, v$datafile b,
(select sum(phyrds) read_sum, sum(phywrts) write_sum
from v$filestat ) c
WHERE a.file# = b.file#
order by phyrds+phywrts desc
/


doc
3.2 Memory Sort Ratio

Purpose - Á¤·ÄÀÛ¾÷ÀÌ ¸Þ¸ð¸®¿¡¼­ ¼öÇàµÈ ºñÀ²À» È®ÀÎÇÑ´Ù. 

#

select a.value "Sort(memory)",
b.value "Sort(disk)",
round(a.value/(a.value+b.value) * 100 ,2) "Memory Sort Ratio"
from v$sysstat a, v$sysstat b
where a.name = 'sorts (memory)'
and b.name = 'sorts (disk)'
/


doc

3.3 Sort Segment Usage

Purpose - Sort SegmentÀÇ »ç¿ë·®À» º¸¿© ÁØ´Ù. 

# 

SELECT tablespace_name "Tablespace Name",
extent_size "Extent Size",
current_users "Current Users",
total_extents "Total Exts",
used_extents "Used Exts",
free_extents "Free Exts",
max_used_size "Max Used Exts"
FROM v$sort_segment
/

doc
3.4 Redo Log Switch Count(Hourly)

Purpose - ½Ã°£º° Redo Log SwitchÀÇ È½¼ö¸¦ È®ÀÎÇÑ´Ù 

#

select thread#, to_char(first_time, 'yyyy/mm/dd hh24') "Time", count(*) "Count"
from v$loghist
where first_time > sysdate -3
group by thread#, to_char(first_time, 'yyyy/mm/dd hh24')
/

doc

3.5 Redo Log Switch Count(Daily)

Purpose - ÀÏÀÚº° Redo Log SwitchÀÇ È½¼ö¸¦ È®ÀÎÇÑ´Ù. 

#

select thread#, to_char(first_time, 'yyyy/mm/dd') "Time", count(*) "Count"
from v$loghist
where first_time > sysdate -30
group by thread#, to_char(first_time, 'yyyy/mm/dd')
/


doc
3.6 Redo Log Switch Time Interval

#


select /*+ use_hash(a b) */ a.thread#, to_char(a.first_time,'YYYY/MM/DD HH24') "Time",
round(avg(trunc((a.first_time - b.first_time)*24*60,3)),2) "Interval(Min)"
from
(select thread#, sequence#, first_time from v$loghist where first_time > sysdate -3) a,
(select thread#, sequence#, first_time from v$loghist where first_time > sysdate -3) b
where a.thread# = b.thread#
and a.sequence# = b.sequence# + 1
group by a.thread#, to_char(a.first_time,'YYYY/MM/DD HH24')
/

doc
4. ~~~ Space ~~~

4.1 Objects With Too Many Extents

Purpose - µ¥ÀÌŸº£À̽º ³»¿¡ Extent°¡ ¸¹ÀÌ ¹ß»ýÇÑ ObjectÀ» È®ÀÎÇÑ´Ù. 

#

col owner format a10
col "Segment" format a30
col "Tablespace" format a20

select owner,
segment_name "Segment",
segment_type "Type",
tablespace_name "Tablespace",
round(bytes/1024/1024,2) "Size(MB)",
extents "Extents",
round(initial_extent/1024/1024,2) "Initial(MB)",
round(next_extent/1024/1024,2) "Next(MB)"
from dba_segments
where owner != 'SYS'
and extents > 50
order by extents desc
/

doc
4.2 Tablespace Usage and Fragmentation

Purpose - Å×ÀÌºí ½ºÆäÀ̽ºÀÇ »ç¿ëÇöȲ°ú FragmentationÀ» Á¡°ËÇÑ´Ù 

#

select df.tablespace_name "Tablespace",
round(df.TBS_byte /1048576,2) "Total(MB)",
round((df.TBS_byte - fs.Free_byte)/1048576,2) "Used(MB)",
round(fs.Free_byte /1048576,2) "Free(MB)",
round((fs.Free_byte/df.TBS_byte) *100,0) "Free(%%)",
fs.pieces "Pieces",
round(fs.Max_free /1048576,2) "MaxFree(MB)"
from ( select tablespace_name, sum(bytes) TBS_byte
from dba_data_files group by tablespace_name ) df,
( select tablespace_name, max(bytes) Max_free, sum(bytes) Free_byte, count(*) pieces
from dba_free_space group by tablespace_name ) fs,
( select tablespace_name, initial_extent, next_extent
from dba_tablespaces ) db
where df.tablespace_name = db.tablespace_name
and df.tablespace_name = fs.tablespace_name(+)
order by 5
/

doc
4.3 Objects with Possible Extent Problem

Purpose - next extents°¡ ¹ß»ýÇÒ °æ¿ì¿¡ °ø°£ÀÇ ºÎÁ·À¸·Î Error°¡ ¹ß»ýÇÒ ¿©Áö°¡ ÀÖ´Â Object¸¦ È®ÀÎÇÑ´Ù 

#

select u.name owner,
o.name segment_name,
so.object_type segment_type,
f.name tablespace_name,
round(f.avail/1024/1024, 1) "Max Free(MB)",
round(s.extsize * f.blocksize/1024/1024, 1) "Next(MB)",
round(s.blocks*f.blocksize/1024/1024, 1) "Total Size(MB)",
s.extents "Exts"
from sys.obj$ o,
sys.sys_objects so,
sys.seg$ s,
sys.user$ u,
(
select ts.ts#, ts.name, ts.blocksize,
max(f.length * ts.blocksize) avail
from sys.ts$ ts,
sys.fet$ f
where ts.ts# = f.ts#
and ts.bitmapped = 0
group by ts.ts#, ts.name, ts.blocksize ) f
where s.file# = so.header_file
and s.block# = so.header_block
and f.ts# = so.ts_number
and s.ts# = f.ts#
and o.obj# = so.object_id
and o.owner# = u.user#
and s.type# = so.segment_type_id
and o.type# = so.object_type_id
and so.object_type in ('TABLE', 'INDEX', 'CLUSTER')
and sign(f.avail-s.extsize * f.blocksize) = -1
/

doc
4.4 Rollback Segment Hit Ratio

Purpose - Rollback SegmentÀÇ Hit Ratio¸¦ È®ÀÎÇÑ´Ù 

#

select name "Rollback Segment",
rssize "Rsize", writes "Writes", xacts "Xacts", status "Status",
waits "Waits", gets "Gets",
decode(gets,0,-1, trunc((1-(waits/gets))*100, 5)) "Hit ratio"
from v$rollstat, v$rollname
where v$rollstat.usn = v$rollname.usn
order by waits/gets desc
/

doc

4.5 User Objects in SYSTEM Tablespace

Purpose - System Tablespace³»ÀÇ ÀÏ¹Ý UserÀÇ Object¸¦ È®ÀÎÇÑ´Ù 

#

select owner "Owner", segment_name "Segment Name", segment_type "Segment Type"
from dba_segments
where tablespace_name = 'SYSTEM'
and owner not in ('SYSTEM', 'SYS', 'OUTLN', 'DBSNMP', 'MDSYS', 'ORDSYS','OLAPDBA', 'ORDPLUGINS', 
'OLAPSVR', 'OSE$HTTP$ADMIN', 'AURORA$ORB$UNAUTHENTICATED', 'AURORA$JIS$UTILITY$')
/


doc

5. ~~~ Access Type ~~~

5.1 SQLs by cached time

Purpose - ij½¬±â°£¿¡ µû¸¥ SQL °¹¼ö¿Í Æò±Õ ½ÇÇà Ƚ¼ö¸¦ ÆľÇÇÑ´Ù. 

#

select substr(cache_time,3) "Cached time", count(*) "SQL Count", round(avg(exec)) "Avg Exec Count"
from
( select decode(sign(1-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'1 00 ~ 01 (min)',
decode(sign(5-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'2 01 ~ 05 (min)',
decode(sign(10-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'3 05 ~ 10 (min)',
decode(sign(30-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'4 10 ~ 30 (min)',
decode(sign(60-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'5 30 ~ 60 (min)',
decode(sign(60*2-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'6 01 ~ 02 (hour)',
decode(sign(60*12-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'7 02 ~ 12 (hour)',
decode(sign(60*24-(sysdate-to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss'))*24*60),1,'8 12 ~ 24 (hour)','9 24 ~ (hour)')))))))) cache_time
, executions exec
from v$sql
where parsing_user_id > 0 and child_number = 0
)
group by cache_time
/

doc
5.2 SQLs by Execution Count

Purpose - ½ÇÇàȽ¼ö¿¡ µû¸¥ SQL °¹¼ö¸¦ ÆľÇÇÑ´Ù 

#

select decode(executions,0,' 0',
decode(sign(2-executions),1,' 01',
decode(sign(3-executions),1,' 02',
decode(sign(6-executions),1,'03-05',
decode(sign(11-executions),1,'06-10',
decode(sign(21-executions),1,'11-20',
decode(sign(41-executions),1,'21-40','41+'))))))) "Exec Count",
count(*) "SQL Count", round(sum(sharable_mem)/1024,2) "Used Mem(KB)"
FROM V$SQL
WHERE parsing_user_id > 0 and child_number = 0
GROUP BY decode(executions,0,' 0',
decode(sign(2-executions),1,' 01',
decode(sign(3-executions),1,' 02',
decode(sign(6-executions),1,'03-05',
decode(sign(11-executions),1,'06-10',
decode(sign(21-executions),1,'11-20',
decode(sign(41-executions),1,'21-40','41+')))))))
/

doc
5.3 Literal SQL

Purpose - SGA³»¿¡ »ç¿ëµÈ Literal SQLÀ» È®ÀÎÇÑ´Ù 

#
col "SQL" format a40
select substr(sql_text, 1, 40) "SQL",
count(*) "Count",
sum(executions) "TotExecs",
sum(sharable_mem) "Mem",
to_char(to_date(min(first_load_time), 'yyyy-mm-dd/hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss') "Start Time",
to_char(to_date(max(first_load_time), 'yyyy-mm-dd/hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss') "End TIme",
max(hash_value) "Hashval"
from v$sql
where executions < 5
group by substr(sql_text, 1, 40)
having count(*) > 30
order by 2 desc
/

doc
¸¸¾à À§ÀÇ ÀÚ¼¼ÇÑ Á¤º¸°¡ ÇÊ¿äÇÏ¸é ¼öÇàÇÒ °Í(40->100º¯°æ)

col module format a20
col sql_text format a60

select main.module, main.sql_text 
from
v$sql main, ( select substr(sql_text, 1, 40) as s_text from v$sql 
where executions < 5 group by substr(sql_text, 1, 40) having 
count(*) > 30) inview where substr(main.sql_text, 1, 40) = inview.s_text 
order by module, sql_text desc 
/
#

doc
5.4 Chained Row Ratio 

Purpose - Chained Row¸¦ AccessÇÑ ºñÀ²À» È®ÀÎÇÑ´Ù. 

#
SELECT sum(decode(name,'table fetch continued row',value,0)) "Chained row Access",
sum(decode(name,'table fetch by rowid',value,0)) "Index Access",
sum(decode(name,'table scan rows gotten',value,0)) "Table Scan Access",
round(sum(decode(name,'table fetch continued row',value,0)) /
(sum(decode(name,'table fetch by rowid',value,0)) + sum(decode(name,'table scan rows gotten',value,0))) * 100,5) "Ratio"
FROM v$sysstat
/


doc

5.5 Session Index Scan Ratio

Purpose - sessionº°·Î Full table scan°ú Index ScanÀÇ ºñÀ²À» È®ÀÎÇÑ´Ù. 

#

select /*+ ordered use_hash(c) swap_join_inputs(c) */ s.sid, s.program "Program",
sum(decode(c.name, 'table fetch by rowid', a.value)) "Index Scan",
sum(decode(c.name, 'table scan rows gotten',a.value)) "Full Scan",
decode(sum(decode(c.name, 'table fetch by rowid', a.value))+sum(decode(c.name, 'table scan rows gotten',a.value)),0,
-1, round(sum(decode(c.name, 'table fetch by rowid', a.value))/
(sum(decode(c.name, 'table fetch by rowid', a.value))+sum(decode(c.name, 'table scan rows gotten',a.value)))*100,2)) "Index Scan Ratio"
from v$session s, v$sesstat a, v$statname c
where c.name in ( 'table scan rows gotten', 'table fetch by rowid' )
and a.statistic# = c.statistic#
and s.sid = a.sid
and s.type != 'BACKGROUND'
and s.module <> 'Orange for ORACLE'
group by s.sid, s.program
order by 5
/

doc
MTS
#

select value 
from
sys.v_$parameter where name = 'mts_servers'
/

doc
OPS
#

select value 
from
v$option where parameter IN ('Parallel Server', 'Real Application Clusters')
/

spool off