Many times the performance of the database will be slow. We need to first find out if any big table full table scan is going on.
Let’s first check out what is Full table scan and then We will see the query to find full table scans in Oracle
What is a Full Table Scan in Oracle
- Full table scan is one of the access methods used by Optimizer. In this, All blocks in the table (up to HWM) are scanned and the WHERE clause filter conditions are applied, and rows that satisfy the filter condition are returned. Explain plan will show like this
Execution Plan
TABLE ACCESS FULL OF HZ_PARTIES
- Full table scan scanned the table using multiple block reads. Multiple blocks scanned for each IO –> less IO operations are made
- db_multiblock_read_count init.ora parameter decides the multiblock count. Recent version, oracle itself adjusts this parameter as per the System and you dont need to define it
- What is HWM – High Water Mark: It is the limit that separates the blocks that contain or have contained data from the blocks that have never been inserted. The number of blocks below the HWM can be obtained through the blocks column of the dba_tables view
Query to find full table scans in Oracle
col event format a25 col module format a50 col File format 9999 col Block format 9999999 set lines 130 set trimspool on select sessw.SID, sessw.EVENT, sessw.p1 "File",sessw.p2 "Block", s.MODULE from v$session_wait sessw, v$session sess where sessw.sid = sess.sid and sessw.event like '%scattered%' order by 1 /
The above query will report any current full table scan going into the database. You can find the table name from the below query
select segment_name
from dba_extents
where file_id = &fileid
and &block between block_id and block_id + blocks - 1
;
If you want to see the history of all the current sessions in the database for a full table scan, we can use the below query
column user_process heading "Name |SID" format a20;
column long_scans heading "Long Scans" format 999,999,999;
column short_scans heading "Short Scans" format 999,999,999;
column rows_retreived heading "Rows Retrieved" format 999,999,999;
set linesize 1000
set timing on
select ss.username||'('||se.sid||') ' "USER_PROCESS",
sum(decode(name,'table scans (short tables)',value)) "SHORT_SCANS",
sum(decode(name,'table scans (long tables)', value)) "LONG_SCANS",
sum(decode(name,'table scan rows gotten',value)) "ROWS_RETRIEVED"
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
or name like '%table scans (long tables)%'
or name like '%table scan rows gotten%' )
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') '
order by LONG_SCANS desc
/
If you want to find all the sql cached in the Library cache for the Full table scan statement
select sql_id,object_owner,object_name from V$SQL_PLAN where
operation='TABLE ACCESS' and
options='FULL' and
object_owner <> 'SYS';
You can get the full text from sql_id using the below query
SELECT sql_text, parsing_schema_name, module FROM v$sql WHERE sql_id = '&1'
How to Avoid Full Table Scan in Oracle
A full table scan is not a necessary evil. Oracle optimizer chooses the plan based on data points. If it has chosen a full data scan, then it has certainly found it good. Also Quite often index scan might not be good for the query and it is more costly than a full table scan. So we need to analyze thoroughly before taking any decision on a Full table scan
Following are some of the things to check
(a) Stale Optimizer statistics: Please check if the optimizer stats available on the tables are current and do not differ much from the actual data
(b) Check indexes and index clustering factor: You might be missing the right indexes
(c) The query might be using parallel clauses and so choosing a Full table scan as the optimal plan
(d) Incorrect parameter settings for Optimizer_mode,optimizer_index_cost_adj, optimizer_index_caching
Sometimes running sql tuning adviser on the query helps
Related Articles
explain plan in oracle : All about Explain Plan in Oracle, How to read Oracle explain plan for performance related issue, how to find the explain plan for query in cursor
what is logical read in oracle : what is logical read in oracle and Physical I/O in Oracle,Which is better logical and physical I/O in terms of performance,queries to find physical reads
sql tuning advisor : How to run sql tuning advisor for sql_id in the Cursor cache, how is the sql tuning task created and executed to get the recommendation
find indexes on a table in oracle : check out this article to find queries on how to find indexes on a table in oracle,list all indexes in schema,index status, index column
bind variables in oracle : Bind variable are the placeholder for values in sqlplus and PLSQL and it is replaced with values when the statement is executed
how to check sql profile in oracle : Check out this post on how to check sql profile in oracle, how to find the content of sql profile, how to drop the sql profile