如何在Oracle、MySQL、Postgresql中查找全表扫描SQL

How to find full table scan SQL in Oracle,MySQL,Postgresql ?

Queries that do “full table scan” are the ones that don’t use indexes. However, it is more suitable to use a full table scan for small tables, and it will not cause performance problems. Or when the data on the large table is seriously skewed and a large proportion of data records need to be returned, a full table scan will also be better than an index scan.

进行“全表扫描”的查询是不使用索引的查询。但是，对小表使用全表扫描更合适，它不会导致性能问题。或者当大型表上的数据严重倾斜并且需要返回大部分数据记录时，全表扫描也将优于索引扫描。

ORACLE

-- prompt Top 50 Full table scan caused by implicit conversion:
select * from
(
select PARSING_SCHEMA_NAME,s.sql_id, s.sql_text,s.EXECUTIONS,
	fetches,
	rows_processed,
  rows_processed/nullif(fetches,0) rows_per_fetch,
	ROUND(cpu_time/NULLIF(executions,0)/1000000,3)     cpu_sec_exec,
	ROUND(elapsed_time/NULLIF(executions,0)/1000000,3) ela_sec_exec,
	ROUND(buffer_gets/NULLIF(executions,0),3)  lios_per_exec,
	ROUND(disk_reads/NULLIF(executions,0),3)   pios_per_exec,
	ROUND(cpu_time/1000000,3) total_cpu_sec,
	ROUND(elapsed_time/1000000,3) total_ela_sec,
  user_io_wait_time/1000000 total_iowait_sec,
	buffer_gets total_LIOS,
	disk_reads total_pios
  from v$sqlarea s
 where s.sql_id in
       (select p.sql_id
          from v$sql_plan p
         where p.OPERATION = 'TABLE ACCESS'
           and p.OPTIONS = 'FULL'
           and p.FILTER_PREDICATES like '%INTERNAL_FUNCTION%')
		   and PARSING_SCHEMA_NAME not in('SYS')
		   order by elapsed_time desc)
		   where rownum<=50;

COLUMN large_table_scans FORMAT 999,999,999,999,999 HEADING 'Large Table Scans' ENTMAP off
COLUMN small_table_scans FORMAT 999,999,999,999,999 HEADING 'Small Table Scans' ENTMAP off
COLUMN pct_large_scans HEADING 'Pct. Large Scans' ENTMAP off

SELECT
a.value large_table_scans
, b.value small_table_scans
, '' || ROUND(100*a.value/DECODE((a.value+b.value),0,1,(a.value+b.value)),2) || '%
' pct_large_scans
FROM
v$sysstat a
, v$sysstat b
WHERE
a.name = 'table scans (long tables)'
AND b.name = 'table scans (short tables)';

MySQL

Try to log them in the slow query log using this option log_queries_not_using_indexes, Be careful though that small tables that have frequent queries running against will fill your slow query log files. You may want to enable this option for limited amount of time. To reduce this chance, you may set min_examined_row_limit variable to a reasonable value, depending on your small tables.

尝试使用此选项“log_queries_not_using_indexes”将它们记录在慢速查询日志中，但是要小心，频繁运行查询的小表会填满慢速查询日志文件。您可能希望在有限的时间内启用此选项。为了减少这种可能性，您可以根据您的小表，将’ min_examined_row_limit '变量设置为一个合理的值。

You can use view with full query

select query, exec_count
from sys.x$statements_with_full_table_scans

PostgreSQL

First of all, PostgreSQL itself does not have system dictionary cascading SQL execution plan information like Oracle, but it can be recorded in the database log file with the auto_explain Extention. or There is a view pg_stat_user_tables with table-level indexing or full table scanning fields, you can refer to.

首先，PostgreSQL本身不像Oracle那样有系统字典级联SQL执行计划信息，但是可以用auto_explain扩展记录在数据库日志文件中。或者有带表级索引或者全表扫描字段的视图pg_stat_user_tables，可以参考。

-- The ratio of index scans
SELECT 
  relname table_name, 
  idx_scan index_scan,
  seq_scan table_scan,
   100*idx_scan / (seq_scan + idx_scan) index_usage, 
  n_live_tup rows_in_table
FROM 
  pg_stat_user_tables
WHERE 
    seq_scan + idx_scan > 0 and 100 * idx_scan / (seq_scan + idx_scan) < 99 and n_live_tup > 70000
ORDER BY 
  4 DESC;

select seq_scan, n_live_tup, relname
from pg_stat_user_tables
order by seq_scan desc
limit 10;