SELECT 'Short to Long Full Table Scans' "Ratio",
ROUND
( (SELECT SUM (VALUE)
FROM v$sysstat
WHERE NAME = 'table scans (short tables)')
/ (SELECT SUM (VALUE)
FROM v$sysstat
WHERE NAME IN
('table scans (short tables)',
'table scans (long tables)'
))
* 100,
2
)
|| '%' "Percentage"
FROM DUAL
UNION
SELECT 'Short Table Scans ' "Ratio",
ROUND
( (SELECT SUM (VALUE)
FROM v$sysstat
WHERE NAME = 'table scans (short tables)')
/ (SELECT SUM (VALUE)
FROM v$sysstat
WHERE NAME IN
('table scans (short tables)',
'table scans (long tables)',
'table fetch by rowid'
))
* 100,
2
)
|| '%' "Percentage"
FROM DUAL
UNION
SELECT 'Long Table Scans ' "Ratio",
ROUND
( (SELECT SUM (VALUE)
FROM v$sysstat
WHERE NAME = 'table scans (long tables)')
/ (SELECT SUM (VALUE)
FROM v$sysstat
WHERE NAME IN
('table scans (short tables)',
'table scans (long tables)',
'table fetch by rowid'
))
* 100,
2
)
|| '%' "Percentage"
FROM DUAL
UNION
SELECT 'Table by Index ' "Ratio",
ROUND
( (SELECT SUM (VALUE)
FROM v$sysstat
WHERE NAME = 'table fetch by rowid')
/ (SELECT SUM (VALUE)
FROM v$sysstat
WHERE NAME IN
('table scans (short tables)',
'table scans (long tables)',
'table fetch by rowid'
))
* 100,
2
)
|| '%' "Percentage"
FROM DUAL
UNION
SELECT 'Efficient Table Access ' "Ratio",
ROUND
( (SELECT SUM (VALUE)
FROM v$sysstat
WHERE NAME IN
('table scans (short tables)',
'table fetch by rowid'
))
/ (SELECT SUM (VALUE)
FROM v$sysstat
WHERE NAME IN
('table scans (short tables)',
'table scans (long tables)',
'table fetch by rowid'
))
* 100,
2
)
|| '%' "Percentage"
FROM DUAL;