Systems Engineering and RDBMS

DBMS_SPACE.CREATE_TABLE_COST

Posted by decipherinfosys on July 1, 2007

DBMS_SPACE package provides quite a few sub-programs (procedures/ functions) which can help us in estimating/calculating space requirements. There are two procedures available in capacity planning to determine the size of the table and size of the index based on certain parameters.

Today we will discuss estimating the table size by using CREATE_TABLE_COST.

CREATE_TABLE_COST:

This procedure has two versions. Basically same procedure is overloaded with two different sets of parameters. In one of the versions, one of the arguments is avg_row_size and in another version, it is column information. We need to mention data_type and size for each of the column in the table for which we want to see the cost. Since we need to specify list of data type and its size, type object has already been created in sys schema which has two elements column_type and column_size. Following is the syntax for both versions and type. Syntax has been taken from Oracle PL/SQL package manual.

DBMS_SPACE.CREATE_TABLE_COST
(
tablespace_name IN VARCHAR2,
avg_row_size IN NUMBER,
row_count IN NUMBER,
pct_free IN NUMBER,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER
);

OR

DBMS_SPACE.CREATE_TABLE_COST
(
tablespace_name IN VARCHAR2,
colinfos IN CREATE_TABLE_COST_COLUMNS,
row_count IN NUMBER,
pct_free IN NUMBER,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER
);

Example we are demonstrating is similar to the one shown in the Oracle manual. Let us assume that we want to determine cost of the following table:

CREATE TABLE SAMPLE
(
ID NUMBER(9),
Name VARCHAR(50),
Create_Date_Time DATE,
Mod_Date_Time DATE,
User_ID VARCHAR(15)
)
Here is the code snippet:

SET SERVEROUTPUT ON

DECLARE
v_used_bytes NUMBER(10);
v_Allocated_Bytes NUMBER(10);
v_type sys.create_table_cost_columns;
BEGIN
v_Type := sys.create_table_cost_columns
(
sys.create_table_cost_colinfo(‘NUMBER’,9),
sys.create_table_cost_colinfo(‘VARCHAR2′,50),
sys.create_table_cost_colinfo(‘VARCHAR2′,15),
sys.create_table_cost_colinfo(‘DATE’,NULL),
sys.create_table_cost_colinfo(‘DATE’,NULL)
);

DBMS_SPACE.CREATE_TABLE_COST(‘USERS’,v_Type,50000,10,v_used_Bytes,v_Allocated_Bytes);

DBMS_OUTPUT.PUT_LINE(‘Used Bytes: ‘ || TO_CHAR(v_used_Bytes));
DBMS_OUTPUT.PUT_LINE(‘Allocated Bytes: ‘ || TO_CHAR(v_Allocated_Bytes));

END;
/

In the above example, first we are building the column list. Our proposed table has two VARCHAR columns, two DATE columns and one NUMBER column. So we have defined them in TYPE object.

Next line, we are executing procedure it self.
• First argument is tablespace in which table is going to be created.
• Second argument is column list.
• Third and fourth arguments are number of rows and percentage of PCT_FREE for future updates in the table respectively.
• Last two are output parameters and based on the value of input parameters, they will return used bytes (actual bytes used by data) and allocated bytes (when table gets created) respectively.

Following is the output.

Used Bytes: 3563520
Allocated Bytes: 4194304

PL/SQL procedure successfully completed.

As we mentioned earlier, another version of the stored procedure takes average row length as an argument instead of column list. Let us assume that one of our existing tables goes through heavy updates and requires us to increase the PCT_FREE value. We want to know the cost associated with this change. Average row length for the table is 50 bytes and it contains 70000 rows. Following is the code snippet for it followed by an output.

DECLARE

v_used NUMBER(10);
v_Alloc NUMBER(10);

BEGIN
DBMS_SPACE.CREATE_TABLE_COST(‘USERS’,50,70000,15,v_used,v_Alloc);

DBMS_OUTPUT.PUT_LINE(‘Used Bytes: ‘ || TO_CHAR(v_used));
DBMS_OUTPUT.PUT_LINE(‘Allocated Bytes: ‘ || TO_CHAR(v_Alloc));

END;
/

Used Bytes: 4448256
Allocated Bytes: 5242880

PL/SQL procedure successfully completed.

This procedure can be used irrespective of whether the tablespace is locally managed or dictionary managed. Block overhead and PCT_FREE values are taken into consideration when used_bytes are displayed back to the user.   Similarly, for alloc_bytes, extent size and tablespace properties of a supplied tablespace are accounted for.

About these ads

2 Responses to “DBMS_SPACE.CREATE_TABLE_COST”

  1. [...] DBMS_SPACE.CREATE_TABLE_COST [...]

  2. [...] over at Decipherinfosys, there’s some handy info on how to calculate disk space cost for tables and [...]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers

%d bloggers like this: