Submit a ticketCall us

Quickly Address Software Vulnerabilities
Patch Manager is an intuitive patch management software which extends the capabilities of WSUS and SCCM to not only patch Windows® servers and workstations, and Microsoft® applications, but also other 3rd-party applications which are commonly exploited by hackers. Learn more about our patch management solution.

 

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: 20 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
10:11, 23 May 2017

Tags

Classifications

Public