Submit a ticketCall us

WebinarUpcoming Webinar: How Help Desk and Remote Support Pays for Itself

Learn how help desk software can simplify ticketing management, allow you to track hardware and software assets, and accelerate the speed of IT support and service delivery. Gain insights on how remote support tools allow your IT team to maximize their efficiency and ticket resolution by expediting desktop troubleshooting, ultimately helping keep end-users happy and productive.

Register here.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > 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: 803 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: '


REM
Prompt Table Definition
REM

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
/

 

REM
Prompt Column Definitions
REM

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
/

 

REM

Prompt Existing Histograms

REM

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
/

 

REM
prompt Row Counts
REM

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

 

REM

prompt Table and Indexes - Segment Sizes

REM

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

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

REM /

 

Last modified

Tags

Classifications

Public