查询Oracle数据库中使用了全表扫描的SQL语句

Oracle排查全表扫描的语句

1
2
3
4
5
SELECT *
FROM V$SQL_PLAN V
WHERE V.OPERATION = 'TABLE ACCESS'
AND V.OPTIONS = 'FULL'
AND V.OBJECT_OWNER='SYSTEM';

依据SQL ID找出相对应的 SQL TEXT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

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';

1
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(索引跳跃扫描)

参考:

https://www.cnblogs.com/ShaYeBlog/p/10863118.html

https://www.cnblogs.com/gered/p/8568085.html