BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter