---table scans (long tables)过多的话,一般db file scattered read比较显著
---_small_table_threshold来定义大表和小表的界限。缺省为2%的Buffer数量 ,>这个参数为大表
---default 大表的全表扫描会被置于LRU的末端(最近最少使用,冷端),以期尽快老化(让其尽快换出buffer cache),减少Buffer的占用
--表访问统计
SELECT value, name
FROM V$SYSSTAT
WHERE name IN ('table fetch by rowid',
'table scans (short tables)',
'table scans (long tables)');
-----查看大表小表扫描对应的值
SELECT value, name FROM V$SYSSTAT WHERE name IN
('table fetch by rowid', 'table scans (short tables)'
, 'table scans (long tables)');
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;