Forum Stats

  • 3,733,964 Users
  • 2,246,849 Discussions
  • 7,856,954 Comments

Discussions

alter table exchange partiotion takes very long time

user12080851
user12080851 Member Posts: 9 Blue Ribbon
We load data every 5 minutes to partitioned table radius_log:
CREATE TABLE RADIUS.RADIUS_LOG
(
  USERNAME             VARCHAR2(50 BYTE)        NOT NULL,
  ACCT_SESSION         VARCHAR2(25 BYTE)        NOT NULL,
  IP                   NUMBER                   NOT NULL,
...
  )
TABLESPACE RADIUS_DATA_TS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
LOGGING
PARTITION BY RANGE (END_DATE)
SUBPARTITION BY LIST (START_STOP)
SUBPARTITION TEMPLATE
  (SUBPARTITION ACTION_STATUS VALUES ('Start', 'Stop') TABLESPACE RADIUS_STATUS_TS,
   SUBPARTITION ALIVE_STATUS VALUES ('Alive') TABLESPACE RADIUS_ALIVE_TS,
   SUBPARTITION MODEM_STATUS VALUES ('Modem-Start', 'Modem-Stop') TABLESPACE RADIUS_STATUS_TS,
   SUBPARTITION ACCOUNT_STATUS VALUES ('Accounting-On', 'Accounting-Off') TABLESPACE RADIUS_STATUS_TS,
   SUBPARTITION TUNNEL_STATUS VALUES ('Tunnel-Start', 'Tunnel-Stop', 'Tunnel-Reject', 'Tunnel-Link-Start', 'Tunnel-Link-Stop', 'Tunnel-Link-Reject') TABLESPACE RADIUS_STATUS_TS,
   SUBPARTITION ABORT_STATUS VALUES ('Failed', 'Cancel') TABLESPACE RADIUS_STATUS_TS
  )
This table has 2 global indexes and 5 local indexes.

Every night we move old (older than 2 days) partition to radius_log_hist table:
CREATE TABLE RADIUS.RADIUS_LOG_HIST
(
  USERNAME             VARCHAR2(50 BYTE)        NOT NULL,
  ACCT_SESSION         VARCHAR2(25 BYTE)        NOT NULL,
  IP                   NUMBER                   NOT NULL,
  ...
)
TABLESPACE RADIUS_DATA_TS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
LOGGING
PARTITION BY RANGE (END_DATE)
SUBPARTITION BY LIST (START_STOP)
SUBPARTITION TEMPLATE
  (SUBPARTITION ACTION_STATUS VALUES ('Start', 'Stop') TABLESPACE RADIUS_STATUS_TS,
   SUBPARTITION ALIVE_STATUS VALUES ('Alive') TABLESPACE RADIUS_ALIVE_TS,
   SUBPARTITION MODEM_STATUS VALUES ('Modem-Start', 'Modem-Stop') TABLESPACE RADIUS_STATUS_TS,
   SUBPARTITION ACCOUNT_STATUS VALUES ('Accounting-On', 'Accounting-Off') TABLESPACE RADIUS_STATUS_TS,
   SUBPARTITION TUNNEL_STATUS VALUES ('Tunnel-Start', 'Tunnel-Stop', 'Tunnel-Reject', 'Tunnel-Link-Start', 'Tunnel-Link-Stop', 'Tunnel-Link-Reject') TABLESPACE RADIUS_STATUS_TS,
   SUBPARTITION ABORT_STATUS VALUES ('Failed', 'Cancel') TABLESPACE RADIUS_STATUS_TS
  )
This table also has the same indexes.

The moving process takes long time (about 6-7 hours per partition).
The steps of moving data to history:
ALTER TABLE radius_log  EXCHANGE PARTITION "partition_name" WITH TABLE RADIUS_LOG_EXCH UPDATE GLOBAL INDEXES;
 
ALTER TABLE radius_log_HIST SPLIT PARTITION "v_max_part"  at '(TO_DATE('TO_CHAR (i.p_date + 1, 'yyyymmdd')','yyyymmdd'))' 
  into (partition "partition_name" , partition "v_max_part" ) UPDATE GLOBAL INDEXES';

ALTER TABLE  radius_log_HIST EXCHANGE PARTITION "partition_name" WITH TABLE RADIUS_LOG_EXCH including indexes UPDATE GLOBAL INDEXES;

ALTER TABLE radius_log  DROP PARTITION "partition_name" UPDATE GLOBAL INDEXES ;

sql_text :='ALTER INDEX '|| i.index_name || ' REBUILD SUBPARTITION '|| i.SUBPARTITION_NAME;   --FOR EVERY UNUSABLE INDEX SUBPARTITION

Gathering statistics for radius_log partitions;
Radius_log_exch table has the same indexes and columns like the previous 2 tables:
CREATE TABLE RADIUS.RADIUS_LOG_EXCH
(
  USERNAME             VARCHAR2(50 BYTE)        NOT NULL,
  ACCT_SESSION         VARCHAR2(25 BYTE)        NOT NULL,
  IP                   NUMBER                   NOT NULL,
 ...
)
TABLESPACE RADIUS_DATA_TS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
           )
LOGGING
PARTITION BY LIST (START_STOP)
The problem is that second exchange partition step takes more than 5 hours, all index subpartitions becames unusable. May be also, that rebuild global indexes takes a long time.

So, how can we reduce the execution time of this job?
Are we doing the correct steps at all?

Thanks a lot.
Tagged:

Answers

  • user3240461
    user3240461 Member Posts: 93

    Hi,

    I am facing the same problelm as second exchange partition step takes too much time.

    Oracle 10.2.0.4 (64 bit) and OS AIX 5,3 (64 bit).

  • HIJACKED THREAD!

    Please don't hijack another users thread to ask your own question. That thread is also three years old.

    If you have a question or issue create a new thread and post the information needed to help you. See the FAQ for how to post a tuning request and the information that you need to provide.


This discussion has been closed.