查询Oracle数据库中使用了全表扫描的SQL语句
Oracle排查全表扫描的语句
SELECT *
FROM V$SQL_PLAN V
WHERE V.OPERATION = 'TABLE ACCESS'
AND V.OPTIONS = 'FULL'
AND V.OBJECT_OWNER='SYSTEM';
依据SQL ID找出相对应的 SQL TEXT
SELECT SQL_TEXT,
SQL_FULLTEXT,
SQL_ID,
LOADS,
FIRST_LOAD_TIME,
PLSQL_EXEC_TIME,
JAVA_EXEC_TIME,
ROWS_PROCESSED,
COMMAND_TYPE,
PARSING_USER_ID,
PARSING_SCHEMA_ID,
PARSING_SCHEMA_NAME,
KEPT_VERSIONS,
ADDRESS,
SERVICE,
MODULE,
MODULE_HASH,
ACTION,
CPU_TIME,
ELAPSED_TIME,
OUTLINE_SID,
CHILD_ADDRESS,
SQLTYPE,
REMOTE,
OBJECT_STATUS,
LITERAL_HASH_VALUE,
LAST_LOAD_TIME,
PROGRAM_ID,
PROGRAM_LINE#,
LAST_ACTIVE_TIME
FROM V$SQL
WHERE SQL_ID = '9b0pykv6ww3jq';
或
SELECT * FROM V$SQL WHERE SQL_ID = '9b0pykv6ww3jq';
表访问的几种方式:(非全部)
- TABLE ACCESS FULL(全表扫描)
- TABLE ACCESS BY ROWID(通过ROWID的表存取)
- TABLE ACCESS BY INDEX SCAN(索引扫描)
索引扫描又分五种:
- INDEX UNIQUE SCAN(索引唯一扫描)
- INDEX RANGE SCAN(索引范围扫描)
- INDEX FULL SCAN(索引全扫描)
- INDEX FAST FULL SCAN(索引快速扫描)
- INDEX SKIP SCAN(索引跳跃扫描)
参考:
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 Yang!