 |
|
The Object Count and Bytes Report Output
Oracle Tips by Burleson Consulting |
The next section shows the total counts of
tables and indexes in the database. This is a very useful report for
the Remote DBA to ensure that no new objects have migrated into the
production environment. We also see the total bytes for all tables
and indexes and the size change over the past week. Here is the
section of the report that shows the total growth of tables and
indexes for the past week:
Mon Apr 22
page 1
Most recent database object counts and sizes
DB_NAME TAB_COUNT IDX_COUNT
TAB_BYTES IDX_BYTES
---------------- --------- ---------------- ----------------
prodzz1 451
674 330,219,520
242,204,672
-------- --------- ----------------
----------------
Total 451
674 330,219,520
242,204,672
Mon Jan 22
page 1
Database size change
comparing the most recent snapshot dates
DB_NAME OLD_BYTES
NEW_BYTES
CHANGE
--------- ------------- ---------------- ----------------
prodzz1 467,419,136
572,424,192 105,005,056
-------------
---------------- ----------------
Total 467,419,136
572,424,192 105,005,056
This is a very sophisticated Remote DBA report, and
one that can run for many hours without the use of temporary tables
because of Oracle’s use of the CARTESIAN access method. However,
with the use of temporary tables, the table and index counts can be
summarized and saved in the temp tables for fast analysis. We also
use the same technique to sum the number of bytes in all tables and
indexes into temporary tables, and then quickly interrogate the
summary tables for total sizes of our database.
The Report Generation SQL Script
Here is the section of code that computes the
date ranges and computes the total table and index counts and bytes.
While this query is more verbose than our original query, it runs
more than 100 times faster than our first query.
rpt_object.sql
set
lines 80;
set pages 999;
set feedback off;
set verify off;
set echo off;
--*********************************************************
-- This report compares the max(snap_time) to the second-highest
date
--*********************************************************
--*********************************************************
-- First we need to get the second-highest date in tab_stats
--*********************************************************
drop table d1;
create table d1 as
select distinct
to_char(snap_time,'YYYY-MM-DD') mydate
from
stats$tab_stats
where
to_char(snap_time,'YYYY-MM-DD') <
(select max(to_char(snap_time,'YYYY-MM-DD')) from
stats$tab_stats)
;
--*********************************************************
-- The second highest date is select max(mydate) from d1;
--*********************************************************
set heading off;
prompt '*********************************************'
select ' Most recent date '||
max(to_char(snap_time,'YYYY-MM-DD'))
from stats$tab_stats;
select ' Older date '||
max(mydate)
from d1;
prompt '*********************************************'
set heading on;
drop table t1;
drop table t2;
drop table t3;
drop table t4;
--
******************************************************************
-- Summarize the counts of all tables for the most recent
snapshot
--
******************************************************************
create table t1 as
select db_name, count(*) tab_count, snap_time from stats$tab_stats
where to_char(snap_time, 'YYYY-MM-DD') =
(select
max(to_char(snap_time,'YYYY-MM-DD'))
from stats$tab_stats)
group by db_name, snap_time;
--
*****************************************************************
-- Summarize the counts of all indexes for the most recent
snapshot
--
*****************************************************************
create table t2 as
select db_name, count(*) idx_count, snap_time from stats$idx_stats
where to_char(snap_time, 'YYYY-MM-DD') =
(select
max(to_char(snap_time,'YYYY-MM-DD'))
from stats$idx_stats)
group by db_name, snap_time;
--
*****************************************************************
-- Summarize sum of bytes of all tables for the 2nd
highest snapshot
--
*****************************************************************
create table t3 as
select db_name, sum(bytes) tab_bytes, snap_time from stats$tab_stats
where to_char(snap_time, 'YYYY-MM-DD') =
(select
max(to_char(snap_time,'YYYY-MM-DD'))
from stats$tab_stats)
group by db_name, snap_time;
--
*****************************************************************
-- Summarize sum of bytes of all indexes for the 2nd
highest snapshot
--
*****************************************************************
create table t4 as
select db_name, sum(bytes) idx_bytes, snap_time from stats$idx_stats
where to_char(snap_time, 'YYYY-MM-DD') =
(select
max(to_char(snap_time,'YYYY-MM-DD'))
from stats$idx_stats)
group by db_name, snap_time;
--*********************************************************
-- This report displays the most recent counts & size totals
--*********************************************************
column tab_bytes format 999,999,999,999
column idx_bytes format 999,999,999,999
column tab_count format 99,999
column idx_count format 99,999
clear computes;
compute sum label "Total" of tab_count on report;
compute sum label "Total" of idx_count on report;
compute sum label "Total" of tab_bytes on report;
compute sum label "Total" of idx_bytes on report;
break on report;
ttitle 'Most recent database object counts and sizes'
select
a.db_name,
tab_count,
idx_count,
tab_bytes,
idx_bytes
from
perfstat.t1 a, -- table counts
perfstat.t2 b, -- index counts
perfstat.t3 c, -- all table bytes
perfstat.t4 d -- all index bytes
where
a.db_name = b.db_name
and
a.db_name = c.db_name
and
a.db_name = d.db_name
;
--*********************************************************
-- These temp tables will compare size growth since last snap
--*********************************************************
drop table t1;
drop table t2;
drop table t3;
drop table t4;
create table t1 as
select db_name, sum(bytes) new_tab_bytes, snap_time
from stats$tab_stats
where to_char(snap_time, 'YYYY-MM-DD') =
(select
max(to_char(snap_time,'YYYY-MM-DD'))
from stats$tab_stats)
group by db_name, snap_time;
create table t2 as
select db_name, sum(bytes) new_idx_bytes, snap_time
from stats$idx_stats
where to_char(snap_time, 'YYYY-MM-DD') =
(select
max(to_char(snap_time,'YYYY-MM-DD'))
from stats$idx_stats)
group by db_name, snap_time;
create table t3 as
select db_name, sum(bytes) old_tab_bytes, snap_time
from stats$tab_stats
where to_char(snap_time, 'YYYY-MM-DD') =
(select max(mydate)
from d1)
group by db_name, snap_time;
create table t4 as
select db_name, sum(bytes) old_idx_bytes, snap_time
from stats$idx_stats
where to_char(snap_time, 'YYYY-MM-DD') =
(select max(mydate)
from d1)
group by db_name, snap_time;
--*********************************************************
-- This is the size comparison report
--*********************************************************
column old_bytes format 999,999,999,999
column new_bytes format 999,999,999,999
column change format 999,999,999,999
compute sum label "Total" of old_bytes on report;
compute sum label "Total" of new_bytes on report;
compute sum label "Total" of change on report;
break on report;
ttitle 'Database size change|comparing most recent snapshot dates';
select
a.db_name,
old_tab_bytes+old_idx_bytes old_bytes,
new_tab_bytes+new_idx_bytes new_bytes,
(new_tab_bytes+new_idx_bytes)-(old_tab_bytes+old_idx_bytes) change
from
perfstat.t1 a,
perfstat.t2 b,
perfstat.t3 c,
perfstat.t4 d
where
a.db_name = b.db_name
and
a.db_name = c.db_name
and
a.db_name = d.db_name
;
Upon close examination, we see that we create
temporary tables to hold the total counts, and we also create two
temporary tables to hold the sum of bytes for each table and index.
Once the sums are pre-calculated, it becomes fast and easy for
Oracle SQL to compute the total bytes for the whole database.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
