Submit a ticketCall us

Training Class Getting Started with SolarWinds Backup - February 28

This course offers customers an introduction to SolarWinds Backup, focusing on configuring the backup technology, taking backups, data restoration and data security. It is a great primer and will get you up to speed quickly on SolarWinds Backup.
Register for class.

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: 782 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