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

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


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


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 


Identifying Oracle sparse Tables

Don Burleson
 
Updated 16 July 2015

Sparse tables generally occur when an table is defined with many free lists (when not using ASSM), and the table has heavy insert and delete activity. This causes the table to extend, even though it may be largely empty. Extension occurs because each free list is unaware of the contents of other free lists inside each free list group.

This state can usually be detected by selecting tables whose actual size (number of rows times average row length) is greater than the size of the next extent for the table.  Of course, we must set the number of free lists to the number of simultaneous insert of update operations, so cannot reduce the number of freelists without introducing segment header contention.

Listing 5-9 shows the sparse tables found in our sample database. Listing 5-10 contains the script that produced the report. The query selects tables that contain multiple free lists, with more than one extent, where there is excessive free space.

To see excessive free space, we calculate the average row length (avg_row_len) in the data dictionary view and the number of rows (num_rows) with a weekly table analyze (i.e. dbms_stats). When we multiply the number of rows in the table by the average row length, we approximate the actual consumed size of the data within the table. We then compare this value with the actual number of allocated bytes in the table. The idea is that a sparse table will have far more allocated space than consumed space.

Table      Extents Bytes M NEXT M Empty M Row space M Blocks M Pct Full

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

TST03           65   1,241     20      14         118    1,241      .10

LIKP             3     148     49      24          76      148      .52

VBRK             2     124      4       0          69      124      .56

STXL            35   1,775     40       7       1,021    1,775      .57

VBAK             5     234     49       0         136      234      .58

KOCLU           27   1,889     49      27       1,144    1,889      .61

VBUP             2     866     49       0         570      866      .66

VBUK             2     147     28       0         103      147      .70

VBAP            46   4,314     50       0       3,034    4,314      .70

NAST             3     137     10       2          97      137      .71

VBPA             5     582     32       0         426      582      .73

LIPS             7   2,350     49       0       1,735    2,350      .74

VBRP            45   2,675     49       0       2,029    2,675      .76

WFPRC           30     123     10       7          95      123      .77

VLPMA           16     575     25      23         444      575      .77

EDIDOC          18     432     20      13         337      432      .78

VRPMA           24     700     20       7         549      700      .78

A sparse table report.

As we stated earlier, sparse tables are caused by an imbalance in multiple free lists, and are evidenced by tables that are continuing to extend although they are not very full. In the example report, we might take a closer look at the KOCLU, VBRP and TST03 tables. This oracle script shows portions of the script that generated this report.

column c1  heading "Tablespace";

column c2  heading "Owner";

column c3  heading "Table";

column c4  heading "Bytes M" format 9,999;

column c5  heading "Extents" format 999;

column c7  heading "Empty M" format 9,999;

column c6  heading "Blocks M" format 9,999;

column c8  heading "NEXT M" format 999;

column c9  heading "Row space M" format 9,999;

column c10  heading "Pct Full" format .99;

 

select

        substr(dt.table_name,1,10) c3,

        ds.extents c5,

        ds.bytes/1048576 c4,

        dt.next_extent/1048576 c8,

       (dt.empty_blocks*4096)/1048576 c7,

       (avg_row_len*num_rows)/1048576 c9,

       (ds.blocks*4096)/1048576 c6,

       (avg_row_len*num_rows)/(ds.blocks*4096) c10

from    sys.dba_segments ds,

        sys.dba_tables dt

see code depot for full script

where   ds.tablespace_name = dt.tablespace_name

  and   ds.owner = dt.owner

  and   ds.segment_name = dt.table_name

and dt.freelists > 1

and ds.extents > 1

and dt.owner not in ('SYS','SYSTEM')

and (avg_row_len*num_rows)/1048576 > 50

and ds.bytes/1048576 > 20

order by c10;

The sparse.sql report to identify sparse tables.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

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

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational