Submit a ticketCall us

Announcing NCM 7.7
With NCM 7.7, you can examine the rules that make up an access control list for a Cisco ASA device. Then you can apply filters to display only rules that meet the specified criteria, order the rules by line number or by the hit count, and much more.
See new features and improvements.

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