Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

Free Oracle Tips

HTML Text

 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle News

 Oracle Forum
 Class Catalog


 Our Staff
 Our Prices
 Help Wanted!

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 UNIX
 Oracle UNIX
 Linux
 Oracle Linux
 Monitoring
 Remote help

 Remote plans
 Remote
services
 Oracle C++
 Oracle Java
 Apache
 JDeveloper
 App Server

 Applications
 Oracle Forms
 Oracle Portal
 11i Upgrades
 SQL Server
 Oracle Concepts
 HTML-DB Tips
 Software Help

 Remote Help  
 Development  

 Implementation


 Financials Training
 Oracle 11i
 Oracle Apps 11i
 Oracle Workflow
 Oracle AR 11i Class
 Oracle AP 11i class
 Oracle GL 11i class
 Oracle HR 11i class
 Oracle FA 11i class
 11i Project Mgt
 11i procurement
 11i collections


 Oracle Posters
 Oracle Books

 Oracle Tuning Book
 Oracle RAC Book
 Oracle Security
 Easy Oracle Books
 Oracle Scripts
 SQL Server DBA
 SQL Design Patterns
 Ion
 Excel-DB   


 BC Oracle News


 Rednecks!
 Dress code
 Arabian Stallion

 Burleson Arabians
 Guide Horses
 Don Burleson Blog
 Golf & Travel


 Privacy Policy
 

 

 
 

Tracking Oracle full table scans


Oracle Tips by Burleson Consulting

 

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Note:  These scripts will only track SQL that you have directed Oracle to capture via your top SQL settings in AWR or STATSPACK, and STATSPACK and AWR will not collect "transient SQL" that did not appear in v$sql at snapshot time.  Hence, not all SQL will appear in these reports.  See my notes here on adjusting the SQL capture thresholds and What SQL is included in AWR/STATSPACK tables?

Tracking full scan access with AWR

All of the specific SQL access methods can be counted and their behavior tracked over time.  This is especially important for large-table full-table scans (LTFTS) because they are a common symptom of suboptimal execution plans (i.e. missing indexes). 

 

Once it has been determined that the large-table full-table scans are legitimate, the DBA must know those times when they are executed so that a selective parallel query can be implemented, depending on the existing CPU consumption on the server.  OPQ drives up CPU consumption, and should be invoked when the server can handle the additional load.

 

Also see this script for counting Oracle full table scans using v$sql_plan.

 

          awr_full_table_scans.sql

 

 

ttile ‘Large Full-table scans|Per Snapshot Period’

 

col c1 heading ‘Begin|Interval|time’ format a20

col c4 heading ‘FTS|Count’           format 999,999

 

break on c1 skip 2

break on c2 skip 2

 

select

  to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,

  count(1)                                         c4

from

   dba_hist_sql_plan p,

   dba_hist_sqlstat   s,

   dba_hist_snapshot sn,

   dba_segments      o

where

   p.object_owner <> 'SYS'

and

   p.object_owner = o.owner 

and

   p.object_name = o.segment_name 

and

   o.blocks > 1000

and

   p.operation like '%TABLE ACCESS%'

and

   p.options like '%FULL%'

and

   p.sql_id = s.sql_id

and

   s.snap_id = sn.snap_id  

group by

  to_char(sn.begin_interval_time,'yy-mm-dd hh24')

order by

  1;

 SEE CODE DEPOT FOR FULL SCRIPTS

 

The output below shows the overall total counts for tables that experience large-table full-table scans because the scans may be due to a missing index. 

 

 

     Large Full-table scans

      Per Snapshot Period

 

Begin

Interval                FTS

time                    Count

-------------------- --------

04-10-18 11                 4

04-10-21 17                 1

04-10-21 23                 2

04-10-22 15                 2

04-10-22 16                 2

04-10-22 23                 2

04-10-24 00                 2

04-10-25 00                 2

04-10-25 10                 2

04-10-25 17                 9

04-10-25 18                 1

04-10-25 21                 1

04-10-26 12                 1

04-10-26 13                 3

04-10-26 14                 3

04-10-26 15                11

04-10-26 16                 4

04-10-26 17                 4

04-10-26 18                 3

04-10-26 23                 2

04-10-27 13                 2

04-10-27 14                 3

04-10-27 15                 4

04-10-27 16                 4

04-10-27 17                 3

04-10-27 18                17

04-10-27 19                 1

04-10-28 12                22

04-10-28 13                 2

04-10-29 13                 9

 

This data can be easily plotted to see the trend for a database as shown in Figure 15.25:

 

Figure 15.25: – Trends of large-table full-table scans

 

__________________________________________________

Search for Symptoms! One of the most common manifestations of suboptimal SQL execution is a large-table full-table scan.  Whenever an index is missing, Oracle may be forced to read every row in the table when an index might be faster.

__________________________________________________

 

If the large-table full-table scans are legitimate, the DBA will want to know the periods that they are invoked, so Oracle Parallel Query (OPQ) can be invoked to speed up the scans as shown in the awr_sql_access_hr.sql script that follows:

 

          awr_sql_access_hr.sql

 

 

ttile ‘Large Tabe Full-table scans|Averages per Hour’

 

col c1 heading ‘Day|Hour’            format a20

col c2 heading ‘FTS|Count’           format 999,999

 

break on c1 skip 2

break on c2 skip 2

 

select

  to_char(sn.begin_interval_time,'hh24')  c1,

  count(1)                           c2

from

   dba_hist_sql_plan p,

   dba_hist_sqlstat   s,

   dba_hist_snapshot sn,

   dba_segments        o

where
SEE CODE DEPOT FOR FULL SCRIPTS

   p.object_owner <> 'SYS'

and

   p.object_owner = o.owner 

and

   p.object_name = o.segment_name 

and

   o.blocks > 1000

and

   p.operation like '%TABLE ACCESS%'

and

   p.options like '%FULL%'

and

   p.sql_id = s.sql_id

and

   s.snap_id = sn.snap_id  

group by

  to_char(sn.begin_interval_time,'hh24')

order by

  1;

 

The following output shows the average number of large-table full-table scans per hour.

 

Large Table Full-table scans

Averages per Hour

 

Day                       FTS

Hour                    Count

-------------------- --------

00                          4

10                          2

11                          4

12                         23

13                         16

14                          6

15                         17

16                         10

17                         17

18                         21

19                          1

23                          6

 

The script below shows the same data for day of the week:

 

          awr_sql_access_day.sql

 

 

ttile ‘Large Table Full-table scans|Averages per Week Day’

 

col c1 heading ‘Week|Day’            format a20

col c2 heading ‘FTS|Count’           format 999,999

 

break on c1 skip 2

break on c2 skip 2

 

select

  to_char(sn.begin_interval_time,'day')  c1,

  count(1)                           c2

from

   dba_hist_sql_plan p,

   dba_hist_sqlstat    s,

   dba_hist_snapshot sn,

   dba_segments       o

where
SEE CODE DEPOT FOR FULL SCRIPTS

   p.object_owner <> 'SYS'

and

   p.object_owner = o.owner  

and

   p.object_name = o.segment_name 

and

   o.blocks > 1000

and

   p.operation like '%TABLE ACCESS%'

and

   p.options like '%FULL%'

and

   p.sql_id = s.sql_id

and

   s.snap_id = sn.snap_id  

group by

  to_char(sn.begin_interval_time,'day')

order by

1;

 

The following sample query output shows specific times the database experienced large table scans.

 

Large Table Full-table scans

Averages per Week Day

 

 

Week                      FTS

Day                     Count

-------------------- --------

sunday                      2

monday                     19

tuesday                    31

wednesday                  34

thursday                   27

friday                     15

Saturday                    2

 

The awr_sql_scan_sums.sql script will show the access patterns of usage over time.  If a DBA is really driven to know their system, all they need to do is understand how SQL accesses the tables and indexes in the database to provide amazing insight.  The optimal instance configuration for large-table full-table scans is quite different than the configuration for an OLTP databases, and the report generated by the awr_sql_scan_sums.sql script will quickly identify changes in table access patterns.

 

awr_sql_scan_sums.sql

 

 

col c1 heading ‘Begin|Interval|Time’          format a20

col c2 heading ‘Large|Table|Full Table|Scans’ format 999,999

col c3 heading ‘Small|Table|Full Table|Scans’ format 999,999

col c4 heading ‘Total|Index|Scans’            format 999,999

 

 

select

  f.c1  c1,

  f.c2  c2,

  s.c2  c3,

  i.c2  c4

from 

(

select

  to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,

  count(1)                           c2

from

   dba_hist_sql_plan p,

   dba_hist_sqlstat   s,

   dba_hist_snapshot sn,

   dba_segments      o

where
SEE CODE DEPOT FOR FULL SCRIPTS

   p.object_owner <> 'SYS'

and

   p.object_owner = o.owner 

and

   p.object_name = o.segment_name 

and

   o.blocks > 1000

and

   p.operation like '%TABLE ACCESS%'

and

   p.options like '%FULL%'

and

   p.sql_id = s.sql_id

and

   s.snap_id = sn.snap_id  

group by

  to_char(sn.begin_interval_time,'yy-mm-dd hh24')

order by

1 ) f,

(

select

  to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,

  count(1)                           c2

from

   dba_hist_sql_plan p,

   dba_hist_sqlstat   s,

   dba_hist_snapshot sn,

   dba_segments        o

where

   p.object_owner <> 'SYS'

and

   p.object_owner = o.owner 

and

   p.object_name = o.segment_name 

and

   o.blocks < 1000

and

   p.operation like '%INDEX%'

and

   p.sql_id = s.sql_id

and

   s.snap_id = sn.snap_id  

group by

  to_char(sn.begin_interval_time,'yy-mm-dd hh24')

order by

1 ) s,

(

select

  to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,

  count(1)                           c2

from

   dba_hist_sql_plan p,

   dba_hist_sqlstat   s,

   dba_hist_snapshot sn

where

 SEE CODE DEPOT FOR FULL SCRIPTS

 

The sample output looks like the following, where there is a comparison of index versus table scan access.  This is a very important signature for any database because it shows, at a glance, the balance between index (OLTP) and data warehouse type access.

 

 

                          Large      Small

Begin                     Table      Table    Total

Interval             Full Table Full Table    Index

Time                      Scans      Scans    Scans

-------------------- ---------- ---------- --------

04-10-22 15                   2         19       21

04-10-22 16                              1        1

04-10-25 10                             18       20

04-10-25 17                   9         15       17

04-10-25 18                   1         19       22

04-10-25 21                             19       24

04-10-26 12                             23       28

04-10-26 13                   3         17       19

04-10-26 14                             18       19

04-10-26 15                  11          4        7

04-10-26 16                   4         18       18

04-10-26 17                             17       19

04-10-26 18                   3         17       17

04-10-27 13                   2         17       19

04-10-27 14                   3         17       19

04-10-27 15                   4         17       18

04-10-27 16                             17       17

04-10-27 17                   3         17       20

04-10-27 18                  17         20       22

04-10-27 19                   1         20       26

04-10-28 12                  22         17       20

04-10-28 13                   2         17       17

04-10-29 13                   9         18       19

 

This is a very important report because it shows the method with which Oracle is accessing data over time periods.  This is especially important because it shows when the database processing modality shifts between OLTP (first_rows index access) to a batch reporting mode (all_rows full scans) as shown in Figure 15.26.

 

Figure 15.26: Plot of full scans vs. index access

 

The example in Figure 15.26 is typical of an OLTP database with the majority of access being via small-table full-table scans and index access.  In this case, the large-table full-table scans must be carefully checked, their legitimacy verified for such things as missing indexes, and then they should be adjusted to maximize their throughput.

 

Of course, in a really busy database, there may be concurrent OLTP index access and full-table scans for reports and it is the DBA’s job to know the specific times when the system shifts table access modes as well as the identity of those tables that experience the changes.

 

The following awr_sql_full_scans_avg_dy.sql script can be used to roll-up average scans into daily averages.

 

          awr_sql_full_scans_avg_dy.sql

 

 

col c1 heading ‘Begin|Interval|Time’   format a20

col c2 heading ‘Index|Table|Scans’ format 999,999

col c3 heading ‘Full|Table|Scans’ format 999,999

 

select

  i.c1  c1,

  i.c2  c2,

  f.c2  c3

from 

(

select

  to_char(sn.begin_interval_time,'day')  c1,

  count(1)                           c2

from

   dba_hist_sql_plan p,

   dba_hist_sqlstat   s,

   dba_hist_snapshot sn

where

   p.object_owner <> 'SYS'

and

   p.operation like '%TABLE ACCESS%'

and

   p.options like '%INDEX%'  

and

   p.sql_id = s.sql_id

and

   s.snap_id = sn.snap_id  

group by

  to_char(sn.begin_interval_time,'day')

order by

1 ) i,

(

select

  to_char(sn.begin_interval_time,'day')  c1,

  count(1)                           c2

from

   dba_hist_sql_plan p,

   dba_hist_sqlstat   s,

   dba_hist_snapshot sn

where
SEE CODE DEPOT FOR FULL SCRIPTS

   p.object_owner <> 'SYS'

and

   p.operation like '%TABLE ACCESS%'

and

   p.options = 'FULL'  

and

   p.sql_id = s.sql_id

and

   s.snap_id = sn.snap_id  

group by

  to_char(sn.begin_interval_time,'day')

order by

1 ) f

where

      i.c1 = f.c1

;

 

The sample output is shown below:

 

Begin                   Index     Full

Interval                Table    Table

Time                    Scans    Scans

-------------------- -------- --------

sunday                    393      189

monday                    383      216

tuesday                   353      206

wednesday                 357      178

thursday                  488      219

friday                    618      285

saturday                  400      189

 

 

For example, the signature shown in Figure 15.27 below indicates that Fridays are very high in full-table scans, probably as the result of weekly reporting.

 

Figure 15.27:  Plot of full scans

 

With this knowledge, the DBA can anticipate the changes in processing from index access to LTFTS access by adjusting instance configurations.

 

Whenever the database changes into a mode dominated by LTFTS, the data buffer sizes, such as db_cache_size and db_nk_cache_size, can be decreased.  Since parallel LTFTS bypass the data buffers, the intermediate rows are kept in the pga_aggregate_target region.  Hence, it may be desirable to use dbms_scheduler to anticipate this change and resize the SGA just in time to accommodate the regularly repeating change in access patterns.

 

One important use for the AWR tables is tracking table join methods over time.

 

The Ion tool is also excellent for identifying SQL to tune and it can show SQL execution over time with stunning SQL graphics.

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm

 


 

 

 

Oracle performance tuning software 
 
 

Oracle performance tuning book

 

 
 
 
 

Search oracle
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.
 
 


Burleson Consulting

The Oracle of Database Support

&

Oracle Performance Tuning


 

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle © is the registered trademark of Oracle Corporation.