v$statname 视图记录了所有的统计指标:
SQL> select * from v$statname where rownum < 11;
STATISTIC# NAME CLASS STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
0 OS CPU Qt wait time 1 576270482
1 logons cumulative 1 2666645286
2 logons current 1 3080465522
3 opened cursors cumulative 1 85052502
4 opened cursors current 1 2301954928
5 user commits 1 582481098
6 user rollbacks 1 3671147913
7 user calls 1 2882015696
8 recursive calls 1 2656001462
9 recursive cpu usage 1 4009879262
已选择10行。
查看全表扫描的系统统计信息:
SQL> select name,value from v$sysstat where name like '%table scans%';
NAME VALUE
---------------------------------------------------------------- ----------
table scans (short tables) 3451
table scans (long tables) 5
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0
table scans (short tables) :小表全表扫描次数。
table scans (long tables) :大表全表扫描次数。
下面最一张大表执行一次全表扫描:
SQL> select count(*) from t;
COUNT(*)
----------
402344
SQL> select * from t;
再次查看统计信息:
SQL> select name,value from v$sysstat where name like '%table scans%';
NAME VALUE
---------------------------------------------------------------- ----------
table scans (short tables) 3580
table scans (long tables) 6
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0