APP TABLE PARTITION – Tuning

–create tablespaces for TBLMSESSIONUSAGECDR
DEFINE db_datafile=”&&dbf_storage_directory”
prompt &db_datafile

CREATE TABLESPACE tbs_usagecdr_1   DATAFILE ‘/ORADATA2/tbs_usagecdr_1.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_2   DATAFILE ‘/ORADATA2/tbs_usagecdr_2.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_3   DATAFILE ‘/ORADATA2/tbs_usagecdr_3.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_4   DATAFILE ‘/ORADATA2/tbs_usagecdr_4.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_5   DATAFILE ‘/ORADATA2/tbs_usagecdr_5.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_6   DATAFILE ‘/ORADATA2/tbs_usagecdr_6.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_7   DATAFILE ‘/ORADATA2/tbs_usagecdr_7.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_8   DATAFILE ‘/ORADATA2/tbs_usagecdr_8.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_9   DATAFILE ‘/ORADATA2/tbs_usagecdr_9.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_10  DATAFILE ‘/ORADATA2/tbs_usagecdr_10.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_11  DATAFILE ‘/ORADATA2/tbs_usagecdr_11.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_12  DATAFILE ‘/ORADATA2/tbs_usagecdr_12.dbf’ size 250M AUTOEXTEND ON;

connect &&username/&&password

–Keep history of TBLMSESSIONUSAGESUMMARY without depend on year so every
–new calendar month same partition will use next year as well

–Backup Existing Table
SELECT COUNT(1) FROM TBLMSESSIONUSAGECDR;

RENAME TBLMSESSIONUSAGECDR TO TBLMSESSIONUSAGECDR_BCK031213;

CREATE TABLE TBLMSESSIONUSAGECDR
(CREATE_DATE        TIMESTAMP(6),
USERID             VARCHAR2(50),
MONITORINGKEY       VARCHAR2(100) NOT NULL ENABLE,
USAGETIME           NUMBER(20,0) DEFAULT 0 NOT NULL ENABLE,
UPLOADOCTETS        NUMBER(20,0) DEFAULT 0 NOT NULL ENABLE,
DOWNLOADOCTETS      NUMBER(20,0) DEFAULT 0 NOT NULL ENABLE,
TOTALOCTETS         NUMBER(20,0) DEFAULT 0 NOT NULL ENABLE,
AGGREGATEKEY        VARCHAR2(128),
LASTUPDATETIME      TIMESTAMP (6),
METERINGLEVEL       VARCHAR2(50),
CUI                 VARCHAR2(50),
V_CREATE_DATE NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(CREATE_DATE,’MM’))) VIRTUAL VISIBLE
)
PARTITION BY LIST (V_CREATE_DATE)
(
PARTITION p_1 VALUES (1)  tablespace tbs_usagecdr_1,
PARTITION p_2 VALUES (2)  tablespace tbs_usagecdr_2,
PARTITION p_3 VALUES (3)  tablespace tbs_usagecdr_3,
PARTITION p_4 VALUES (4)  tablespace tbs_usagecdr_4,
PARTITION p_5 VALUES (5)  tablespace tbs_usagecdr_5,
PARTITION p_6 VALUES (6)  tablespace tbs_usagecdr_6,
PARTITION p_7 VALUES (7)  tablespace tbs_usagecdr_7,
PARTITION p_8 VALUES (8)  tablespace tbs_usagecdr_8,
PARTITION p_9 VALUES (9)  tablespace tbs_usagecdr_9,
PARTITION p_10 VALUES(10) tablespace  tbs_usagecdr_10,
PARTITION p_11 VALUES(11) tablespace  tbs_usagecdr_11,
PARTITION p_12 VALUES(12) tablespace  tbs_usagecdr_12
);

ALTER TABLE TBLMSESSIONUSAGECDR     INITRANS 100;

ALTER TABLE TBLMSESSIONUSAGECDR ENABLE ROW MOVEMENT;

–Create Procedure for TRUNCATE PARTITION which are older then 6 months
create or replace PROCEDURE PROC_NVUSAGECDR_MAINTAINANCE
AS
p_name varchar2(100);
BEGIN

/*
EliteCSM NetVertex Module
EliteCore Technologies Pvt. Ltd.
*/

FOR REC IN (SELECT distinct ‘p_’||TO_NUMBER(TO_CHAR(CREATE_DATE,’MM’))
p_name
FROM TBLMSESSIONUSAGECDR
WHERE CREATE_DATE + 185  < SYSDATE)
LOOP
EXECUTE IMMEDIATE ‘ALTER TABLE TBLMSESSIONUSAGECDR TRUNCATE PARTITION ‘ || REC.p_name || ‘ UPDATE INDEXES’;

DBMS_OUTPUT.PUT_LINE (‘ALTER TABLE TBLMSESSIONUSAGECDR TRUNCATE PARTITION ‘ || REC.p_name || ‘ UPDATE INDEXES; ——-> successfully completed’);

END LOOP;
END;
/

— Run on the next to last day of every month:
BEGIN
— Job defined entirely by the CREATE JOB procedure.
DBMS_SCHEDULER.create_job (
job_name => ‘JOB_USAGECDR_MAINTAINANCE’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN PROC_NVUSAGECDR_MAINTAINANCE(); END;’,
START_DATE => SYSTIMESTAMP,
repeat_interval => ‘freq=yearly;bymonth=6;bymonthday=30;byhour=7;byminute=5;’,
end_date => NULL,
enabled => TRUE,
comments => ‘Job defined entirely by the CREATE JOB procedure.’);
END;
/

begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => ‘”NETVERTEXP”‘,
tabname => ‘”TBLMSESSIONUSAGECDR”‘,
estimate_percent => 1
);
end;
/

–create tablespaces for TBLEDR
DEFINE db_datafile=”&&dbf_storage_directory”
prompt &db_datafile

CREATE TABLESPACE tbs_edr_1   DATAFILE ‘/ORADATA1/tbs_edr_1.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_2   DATAFILE ‘/ORADATA1/tbs_edr_2.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_3   DATAFILE ‘/ORADATA1/tbs_edr_3.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_4   DATAFILE ‘/ORADATA1/tbs_edr_4.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_5   DATAFILE ‘/ORADATA1/tbs_edr_5.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_6   DATAFILE ‘/ORADATA1/tbs_edr_6.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_7   DATAFILE ‘/ORADATA1/tbs_edr_7.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_8   DATAFILE ‘/ORADATA1/tbs_edr_8.dbf’ size 250M AUTOEXTEND ON;

connect &&username/&&password

–Keep history of TBLEDR without depend on year so every
–new calendar month same partition will use next year as well

–Backup Existing Table
SELECT COUNT(1) FROM TBLEDR;
RENAME TBLEDR TO TBLEDR_BCK03122013;

CREATE TABLE TBLEDR
(EDRID       NUMBER(20,0),
SESSIONID   VARCHAR2(100),
USERNAME    VARCHAR2(264),
EXPIREDRULE VARCHAR2(100),
NEWRULE     VARCHAR2(100),
OLDBW       VARCHAR2(32),
NEWBW       VARCHAR2(32),
PARAM1      VARCHAR2(64),
PARAM2      VARCHAR2(64),
CREATEDATE  TIMESTAMP (6),
LASTMODIFIEDDATE TIMESTAMP (6),
TIMESTAMP        TIMESTAMP (6),
V_CREATEDATE       NUMBER
GENERATED ALWAYS AS
(
TO_NUMBER(TO_CHAR(CREATEDATE,’DD’))
) VIRTUAL
)PARTITION BY LIST (V_CREATEDATE)
(
PARTITION p_1 VALUES  (1)   tablespace  tbs_edr_1,
PARTITION p_2 VALUES  (2)   tablespace  tbs_edr_2,
PARTITION p_3 VALUES  (3)   tablespace  tbs_edr_3,
PARTITION p_4 VALUES  (4)   tablespace  tbs_edr_4,
PARTITION p_5 VALUES  (5)   tablespace  tbs_edr_5,
PARTITION p_6 VALUES  (6)   tablespace  tbs_edr_6,
PARTITION p_7 VALUES  (7)   tablespace  tbs_edr_7,
PARTITION p_8 VALUES  (8)   tablespace  tbs_edr_8,
PARTITION p_9 VALUES  (9)   tablespace  tbs_edr_1,
PARTITION p_10 VALUES (10)   tablespace tbs_edr_2,
PARTITION p_11 VALUES (11)   tablespace tbs_edr_3,
PARTITION p_12 VALUES (12)   tablespace tbs_edr_4,
PARTITION p_13 VALUES (13)   tablespace tbs_edr_5,
PARTITION p_14 VALUES (14)   tablespace tbs_edr_6,
PARTITION p_15 VALUES (15)   tablespace tbs_edr_7,
PARTITION p_16 VALUES (16)   tablespace tbs_edr_8,
PARTITION p_17 VALUES (17)   tablespace tbs_edr_1,
PARTITION p_18 VALUES (18)   tablespace tbs_edr_2,
PARTITION p_19 VALUES (19)   tablespace tbs_edr_3,
PARTITION p_20 VALUES (20)   tablespace tbs_edr_4,
PARTITION p_21 VALUES (21)   tablespace tbs_edr_5,
PARTITION p_22 VALUES (22)   tablespace tbs_edr_6,
PARTITION p_23 VALUES (23)   tablespace tbs_edr_7,
PARTITION p_24 VALUES (24)   tablespace tbs_edr_8,
PARTITION p_25 VALUES (25)   tablespace tbs_edr_1,
PARTITION p_26 VALUES (26)   tablespace tbs_edr_2,
PARTITION p_27 VALUES (27)   tablespace tbs_edr_3,
PARTITION p_28 VALUES (28)   tablespace tbs_edr_4,
PARTITION p_29 VALUES (29)   tablespace tbs_edr_5,
PARTITION p_30 VALUES (30)   tablespace tbs_edr_6,
PARTITION p_31 VALUES (31)   tablespace tbs_edr_7
);

ALTER TABLE TBLEDR
ADD CONSTRAINT “PK_PEDR” PRIMARY KEY (“EDRID”);

ALTER TABLE TBLEDR INITRANS 100;

ALTER INDEX PK_PEDR INITRANS 100;

ALTER TABLE TBLEDR ENABLE ROW MOVEMENT;

–Create Procedure for TRUNCATE PARTITION which are older then 6 months
create or replace PROCEDURE PROC_TBLEDR_MAINTAINANCE
AS
p_name varchar2(100);
BEGIN

/*
EliteCSM NetVertex Module
EliteCore Technologies Pvt. Ltd.
*/

FOR REC IN (SELECT distinct ‘p_’||TO_NUMBER(TO_CHAR(CREATEDATE,’DD’)) p_name FROM TBLEDR WHERE CREATEDATE + 15 < SYSDATE)
LOOP

EXECUTE IMMEDIATE ‘ALTER TABLE TBLEDR TRUNCATE PARTITION ‘ || REC.p_name || ‘ UPDATE INDEXES’;

DBMS_OUTPUT.PUT_LINE (‘ALTER TABLE TBLEDR TRUNCATE PARTITION ‘ || REC.p_name || ‘ UPDATE INDEXES; ——-> successfully completed’);

END LOOP;

END;
/

— Run on the next to last day of every month:
BEGIN
— Job defined entirely by the CREATE JOB procedure.
DBMS_SCHEDULER.create_job (
job_name => ‘JOB_TBLEDR_MAINTAINANCE’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN PROC_TBLEDR_MAINTAINANCE(); END;’,
START_DATE => SYSTIMESTAMP,
repeat_interval => ‘freq=monthly;bymonthday=14,28;byhour=6;byminute=33;’,
end_date => NULL,
enabled => TRUE,
comments => ‘Job defined entirely by the CREATE JOB procedure.’);
END;
/

begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => ‘”NETVERTEXP”‘,
tabname => ‘”TBLEDR”‘,
estimate_percent => 1
);
end;
/

🙂

😦

Insert data only single table space so we should improve insert performance via taking advantage of SAN storage

specify multiple tablespaces

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.