ANBOB™

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.

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.

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

— over —

打赏

微信扫一扫，打赏作者吧～