Oracle stats (Part 3)

Finally let’s find out how to gather per table statistics.

Note: If table is big enough, it would be better to create separate tablespace for it. Storing a big table in separate tablespace has numerous advantages [1]

Lets imagine we’ve made a mistake and putted all our tables into USERS tablespace. It’s time to make some optimization. First of all let’s see which objects in specified tablespace we have, how big are they and select candidates to be putted into separate tablespaces.

Please take a look at DBA_SEGMENTS reference before we continue. You might also need to review information about oracle segments.

select * from dba_segments where tablespace_name='USERS' order by bytes desc

The query above will show all objects in USERS tablespace, sorted by their segment size.

Here is output from sample database (some attributes are omitted):

segment_name segment_type header_block bytes blocks extents
DATA TABLE 59 3537895424 431872 273
INDEX1 INDEX 165771 2920284160 356480 255
DATA_PK INDEX 195 2422210560 295680 242
REPORTS TABLE 120859 8388608 1024 23
DATA_ISP TABLE 86803 4194304 512 19
DATA_ISP_PK INDEX 86811 2097152 256 17
PARAMS TABLE 120883 589824 72 9
REPORTSOLD TABLE 179 131072 16 2
REPORT_PARAM_TYPES_UK1 INDEX 227 131072 16 2
KVIT TABLE 115 65536 8 1
OBJECTS TABLE 155 65536 8 1
PRIBORS TABLE 163 65536 8 1
REPORT_PARAM_TYPES TABLE 171 65536 8 1
OBJECTS_PK INDEX 187 65536 8 1
KVIT_PK INDEX 203 65536 8 1
PRIBORS_PK INDEX 211 65536 8 1
REPORT_PARAM_TYPES_PK INDEX 219 65536 8 1
PARAMS_LABEL TABLE 120955 65536 8 1
PARAMS_LABEL_PK INDEX 120963 65536 8 1
STATS_PK INDEX 555 65536 8 1
STATS TABLE 147 65536 8 1
STATS_USERS_TABLESPACE_PK INDEX 139 65536 8 1
STATS_USERS_TABLESPACE TABLE 131 65536 8 1

Additionally you can determine to which datafile given table belongs:

select t.table_name,t.tablespace_name,df.file_name from dba_tables t, dba_data_files df
where t.tablespace_name = df.tablespace_name and t.tablespace_name = 'USERS'
table_name tablespace_name file_name
DATA_ISP USERS …\USERS01.DBF
PARAMS USERS …\USERS01.DBF
PARAMS_LABEL USERS …\USERS01.DBF
DATA USERS …\USERS01.DBF
KVIT USERS …\USERS01.DBF
OBJECTS USERS …\USERS01.DBF
PRIBORS USERS …\USERS01.DBF
REPORT_PARAM_TYPES USERS …\USERS01.DBF
REPORTSOLD USERS …\USERS01.DBF
DEPT USERS …\USERS01.DBF
EMP USERS …\USERS01.DBF
BONUS USERS …\USERS01.DBF
SALGRADE USERS …\USERS01.DBF
STATS_USERS_TABLESPACE USERS …\USERS01.DBF
STATS USERS …\USERS01.DBF
REPORTS USERS …\USERS01.DBF

I’d like to prepare separate blogpost about sample database optimization. As for now, let’s finish with gathering statistics.

Our first candidate to be monitored is DATA table. It has two index objects INDEX1 and DATA_PK. It would be nice to monitor them also.

We will also use several attributes from DBA_TABLES view:

select num_rows from dba_tables where
    tablespace_name='USERS' and table_name='DATA'

Let’s summarize attributes which will be monitored:

  1. DBA_SEGMENTS.bytes
  2. DBA_SEGMENTS.blocks
  3. DBA_SEGMENTS.extents
  4. DBA_TABLES.num_rows

Here is stats table definition:

  CREATE TABLE "STATS_DATA_TABLE"
 ("ID" NUMBER NOT NULL ENABLE,
 "TIMESTAMP" DATE NOT NULL ENABLE,
 "BYTES" NUMBER,
 "BYTES_DELTA" NUMBER,
 "BLOCKS" NUMBER,
 "BLOCKS_DELTA" NUMBER,
 "EXTENTS" NUMBER,
 "EXTENTS_DELTA" NUMBER,
 "ROWS" NUMBER,
 "ROWS_DELTA" NUMBER,
 CONSTRAINT "STATS_DATA_TABLE_PK" PRIMARY KEY ("ID")
 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "USERS"  ENABLE
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "USERS" ;

 CREATE SEQUENCE  "ST_DATA_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999
INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

 CREATE OR REPLACE TRIGGER "STATS_DATA_BFRINS_TRIGGER"
before insert on STATS_DATA_TABLE
for each row
begin
select st_data_seq.nextval into :new.ID from dual;
end;
/
ALTER TRIGGER "STATS_DATA_BFRINS_TRIGGER" ENABLE;

Now let’s create stored procedure which will collect stats about DATA table:

create or replace
procedure USP_NSERT_DATA_TABLE_STATS as
current_bytes number;
current_blocks number;
current_extents number;
current_rows number;
latest_bytes number;
latest_blocks number;
latest_extents number;
latest_rows number;
max_id number;
begin
  --get current stats about DATA table
  select bytes,blocks,extents into current_bytes, current_blocks, current_extents from dba_segments where segment_name='DATA';
  select num_rows into current_rows from dba_tables where table_name='DATA';

  --check whether stats table is empty
  max_id := -1;
  select count(1) into max_id from stats_data_table;

  if max_id <= 0 then
    --insert first stats record using zero deltas
    insert into stats_data_table("TIMESTAMP", bytes, bytes_delta, "BLOCKS", blocks_delta, "EXTENTS", extents_delta, "ROWS", rows_delta)
    values(sysdate, current_bytes,0, current_blocks,0, current_extents,0, current_rows,0);
  else
       --get id of record with latest timestamp
     select id into max_id from stats_data_table where timestamp = (select max(timestamp) from stats_data_table);

     --gets latest stats values stored in db
     select bytes,"BLOCKS","EXTENTS","ROWS"
     into latest_bytes,latest_blocks,latest_extents,latest_rows
     from stats_data_table where
     id = max_id;

     --insert stats row using updated tables
    insert into stats_data_table("TIMESTAMP", bytes, bytes_delta, "BLOCKS", blocks_delta, "EXTENTS", extents_delta, "ROWS", rows_delta)
    values(sysdate,current_bytes, current_bytes - latest_bytes,
      current_blocks, current_blocks - latest_blocks,
      current_extents, current_extents - latest_extents,
      current_rows, current_rows - latest_rows);
  end if;
end;
&#91;/sourcecode&#93;

Finally let's create <strong>DBMS_JOB </strong>and start it.


declare
   x number;
begin
dbms_job.submit(x,'USP_NSERT_DATA_TABLE_STATS;',sysdate + 1/24,'sysdate + 1/24');
commit;
end;

Now we can monitor database table size growth (as well as blocks/extents/rows count)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: