Submit a ticketCall us

Systems Monitoring for Dummies
Our new eBook will teach you the fundamentals and help you create monitors and alerts that are effective, meaningful, and actionable. Monitoring is more than a checkbox on your to-do list. This free eBook will give you practical advice to help you succeed in all aspects of monitoring – discovery, alerting, remediation, and troubleshooting. Don’t miss out on this indispensable resource for newbies, experienced IT pros, and everyone in between. Register Now.

Home > Success Center > Database Performance Analyzer (DPA) > Oracle table tuning statistics

Oracle table tuning statistics

Table of contents
No headers
Created by Interspire Import, last modified by Robert Holtam on May 23, 2017

Views: 45 Votes: 0 Revisions: 10

Use this script with reading execution plans to get more information about the table and existing indexes.


accept table_name prompt 'Enter table_name: '

set pagesize 999
set linesize 132
set verify off

column table_name format a40
column index_name format a40
column column_name format a40
column value format a65
column object_name format a60

select owner, object_name, object_type
from dba_objects
where upper(object_name) = upper('&table_name')
order by owner, object_name, object_type

accept owner prompt 'Enter table owner: '

Prompt Table Definition

describe &owner..&table_name

Prompt Index Definition

select distinct c.index_name, i.uniqueness, c.column_name, c.column_position
from dba_ind_columns c, dba_indexes i
where upper(c.table_name) = upper('&table_name')
and upper(c.table_owner) = upper('&owner')
and c.index_name = i.index_name
and c.index_owner = i.owner
order by index_name, column_position


Prompt Column Definitions

select column_name, num_distinct, num_nulls, num_buckets, density, sample_size
from dba_tab_columns
WHERE upper(owner) = upper('&owner')
AND upper(table_name) = upper('&table_name')
order by column_name



Prompt Existing Histograms


SELECT column_name, endpoint_number, endpoint_value
FROM dba_histograms
WHERE upper(table_name) = upper('&table_name')
AND upper(owner) = upper('&owner')
ORDER BY column_name, endpoint_number


prompt Row Counts

SELECT table_name, num_rows, degree, last_analyzed
FROM dba_tables
WHERE upper(owner) = upper('&owner')
AND upper(table_name) = upper('&table_name')



prompt Table and Indexes - Segment Sizes


column segment_name format a50
SELECT segment_name, segment_type, SUM(bytes)/1024/1024 size_mb
FROM dba_segments
WHERE upper(owner) = upper('&owner')
AND upper(segment_name) = upper('&table_name')
OR segment_name in (
  select index_name from dba_indexes
  where upper(table_name) = upper('&table_name')
  and upper(table_owner) = upper('&owner'))
GROUP BY segment_name, segment_type


REM uncomment this line if you want "live" row counts

REM - for large tables this could run for a while and cause performance problems


REM select count(*) from "&owner"."&table_name"



Last modified