OraFAQ Forum: Server Administration » Split Partitioning a MAX VALUE partition

Home » RDBMS Server » Server Administration » Split Partitioning a MAX VALUE partition (Oracle 10g Release 1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Split Partitioning a MAX VALUE partition [message #323064]

Tue, 27 May 2008 04:04

himang
Messages: 282
Registered: March 2005
Location: Bangalore

Senior Member

We have a table which is Range partitioned on Date (Daily) and has 10 sub partitions (List). We have a MAX VALUE partition which we are trying to partition further by splitting the max value partition. The table has around 21 million records but the max value partition is currently empty. When we are trying to split the empty max value partition, it is taking approx 10-15 minutes for each partition split. Does it really takes this much of time for splitting an empty partition. We do have some global indexes on the table but does splitting an empty partition takes time?

Report message to a moderator

Re: Split Partitioning a MAX VALUE partition [message #323071 is a reply to message #323064]

Tue, 27 May 2008 04:22

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Copy and paste the table description and what you did.

Regards
Michel

Report message to a moderator

Re: Split Partitioning a MAX VALUE partition [message #323085 is a reply to message #323064]

Tue, 27 May 2008 04:43

himang
Messages: 282
Registered: March 2005
Location: Bangalore

Senior Member

Table table structure is as follows

CREATE TABLE OB_TEMP
(
  ob_audit_log_id                 VARCHAR2(36 CHAR) NOT NULL,
  order_group_id                  VARCHAR2(36 CHAR) NOT NULL,
  action_code                     VARCHAR2(10 CHAR) NOT NULL,
  action_code_level               VARCHAR2(1 CHAR),
  frictionless_code               VARCHAR2(1 CHAR),
  order_form_id                   VARCHAR2(36 CHAR),
  action_reason_code              VARCHAR2(6 CHAR),
  order_queue_id                  VARCHAR2(5 CHAR),
  business_unit_id                INTEGER,
  top_channel_code                VARCHAR2(20 CHAR),
  lower_channel_code              VARCHAR2(20 CHAR),
  company_num                     VARCHAR2(8 CHAR),
  op_rep_id                       VARCHAR2(36 CHAR),
  op_rep_name                     VARCHAR2(50 CHAR),
  op_manager_id                   VARCHAR2(36 CHAR),
  op_manager_name                 VARCHAR2(50 CHAR),
  primary_online_payment_code     VARCHAR2(8 CHAR),
  order_form_cnt                  NUMBER,
  system_cnt                      NUMBER,
  doms_quote_num                  NUMBER,
  doms_order_num                  NUMBER,
  salesrep_num                    NUMBER,
  keycode                         VARCHAR2(10 CHAR),
  customer_set_id                 VARCHAR2(9 CHAR),
  brand_id                        NUMBER,
  order_form_total_amt            NUMBER(18,3),
  order_group_total_amt           NUMBER(18,3),
  general_contact_id              VARCHAR2(36 CHAR),
  error_code                      VARCHAR2(10 CHAR),
  error_category_code             VARCHAR2(2 CHAR),
  error_desc                      VARCHAR2(160 CHAR),
  ship_to_contact_id              VARCHAR2(36 CHAR),
  export_flag                     VARCHAR2(1 CHAR),
  create_date                     DATE,
  update_by                       INTEGER,
  update_system                   VARCHAR2(20 CHAR),
  action_date                     TIMESTAMP(6),
  assisting_salesrep_num          NUMBER,
  disposition_code                VARCHAR2(3 CHAR),
  shipping_option_type            VARCHAR2(2 CHAR),
  tax_total_amt                   NUMBER(18,3),
  ship_total_amt                  NUMBER(18,3),
  service_tag                     VARCHAR2(50 CHAR),
  po_number                       VARCHAR2(30 CHAR),
  backend_customer_link_num       INTEGER,
  og_disposition_code             VARCHAR2(3 CHAR),
  ob_request_id                   VARCHAR2(36 CHAR),
  source_application_name         VARCHAR2(5 CHAR),
  country_cd                      VARCHAR2(2 CHAR),
  irn                             VARCHAR2(20 CHAR),
  order_form_total_amt_currency   VARCHAR2(3 CHAR),
  order_group_total_amt_currency  VARCHAR2(3 CHAR),
  tax_total_amt_currency          VARCHAR2(3 CHAR),
  ship_total_amt_currency         VARCHAR2(3 CHAR),
  oms_system_id                   VARCHAR2(5 CHAR),
  entered_date                    DATE,
  of_cost_amt                     NUMBER(18,3),
  og_cost_amt                     NUMBER(18,3),
  of_cost_amt_currency            VARCHAR2(3 CHAR),
  og_cost_amt_currency            VARCHAR2(3 CHAR),
  dp_id                           NUMBER(13)
)
TABLESPACE OO_TEMP
PCTUSED    0
PCTFREE    0
INITRANS   16
MAXTRANS   255
STORAGE    (
            MINEXTENTS       1
            MAXEXTENTS       2147483645
           )
NOLOGGING
PARTITION BY RANGE (ACTION_DATE) 
SUBPARTITION BY LIST (ACTION_CODE)
SUBPARTITION TEMPLATE
  (SUBPARTITION S_GROUP_START VALUES ('CUST'),
   SUBPARTITION S_FORM_NACKED VALUES ('OBNAK', 'IPNAK'),
   SUBPARTITION S_START VALUES ('START'),
   SUBPARTITION S_FRICLESS_GC VALUES ('OBCMP'),
   SUBPARTITION S_FRICNED_GC VALUES ('ESMAN'),
   SUBPARTITION S_CAN_GC VALUES ('ESCAN'),
   SUBPARTITION S_GDREQ VALUES ('GDREQ'),
   SUBPARTITION S_GDRSP VALUES ('GDRSP'),
   SUBPARTITION S_IPACK VALUES ('IPACK'),
   SUBPARTITION S_OGSVD VALUES ('OGSVD'),
   SUBPARTITION S_OTHER VALUES (DEFAULT)
  )
(  
    PARTITION TM_MAXVALUE VALUES LESS THAN (MAXVALUE)
      NOLOGGING
      NOCOMPRESS
      TABLESPACE OO_TEMP
      PCTFREE    0
      INITRANS   16
      MAXTRANS   255
      STORAGE    (
                  MINEXTENTS       1
                  MAXEXTENTS       2147483645
                  BUFFER_POOL      DEFAULT
                 )
    ( SUBPARTITION TM_MAXVALUE_S_GROUP_START VALUES ('CUST')    TABLESPACE OO_TEMP,
      SUBPARTITION TM_MAXVALUE_S_FORM_NACKED VALUES ('OBNAK', 'IPNAK')    TABLESPACE OO_TEMP,
      SUBPARTITION TM_MAXVALUE_S_START VALUES ('START')    TABLESPACE OO_TEMP,
      SUBPARTITION TM_MAXVALUE_S_FRICLESS_GC VALUES ('OBCMP')    TABLESPACE OO_TEMP,
      SUBPARTITION TM_MAXVALUE_S_FRICNED_GC VALUES ('ESMAN')    TABLESPACE OO_TEMP,
      SUBPARTITION TM_MAXVALUE_S_CAN_GC VALUES ('ESCAN')    TABLESPACE OO_TEMP,
      SUBPARTITION TM_MAXVALUE_S_GDREQ VALUES ('GDREQ')    TABLESPACE OO_TEMP,
      SUBPARTITION TM_MAXVALUE_S_GDRSP VALUES ('GDRSP')    TABLESPACE OO_TEMP,
      SUBPARTITION TM_MAXVALUE_S_IPACK VALUES ('IPACK')    TABLESPACE OO_TEMP,
      SUBPARTITION TM_MAXVALUE_S_OGSVD VALUES ('OGSVD')    TABLESPACE OO_TEMP,
      SUBPARTITION TM_MAXVALUE_S_OTHER VALUES (DEFAULT)    TABLESPACE OO_TEMP)
)
CACHE
NOPARALLEL
/

Then the table was splitted initally for 365 partitions, with ALTER TABLE SPLIT PARTITION command and data was loaded for each day. As of now also the TM_MAXVALUE partition is EMPTY and we are trying to SPLIT the max value partition with command

ALTER TABLE OB_TEMP SPLIT PARTITION TM_MAXVALUE AT (TIMESTAMP'2008-03-01 00:00:00') INTO (PARTITION TM_2008_03_01, PARTITION TM_MAXVALUE)

Please note there is no data in MAX VALUE partition but there are global indexes on the table and currently table is having 21 million records... Can this be a reason for slow performance?

Report message to a moderator

Re: Split Partitioning a MAX VALUE partition [message #323093 is a reply to message #323085]

Tue, 27 May 2008 04:57

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Do you any index on the table?
Do you have any activity on the table?
Oracle does not know there is no row in youor partition, above all if there are concurrent ongoing transactions.
Also, I think maybe Oracle has to checkpoint the whole table/indexes (and not only the segments of the splitted partition) in order to recover in case of failure during the process, this may take time.
Activate a SQL/10046 trace during the split to know where it spends its time.

Regards
Michel

Report message to a moderator

Re: Split Partitioning a MAX VALUE partition [message #323105 is a reply to message #323085]

Tue, 27 May 2008 05:37

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

According to your posting I am not able to see any empty MAX Partition. You are having only one partition and you are trying to create another partition by using the split partition method. So, Oracle has to scan all the 21 Million records in the current partition, delete from the partition you are splitting and it has to insert into the new partition and update the global indexes.

Also, I noticed you are using subpartition template. If you use a subpartition template you don't need to specify the list of values again. You need to mention is only the partition name. If you want to have a different list set of subpartition, then only you need to explicitly mention the subpartition names only.

So considering all this into account, time you have mentioned is reasonable.

By all means follow the advice given by Michael and let us know.

Regards

Raj

Report message to a moderator

Re: Split Partitioning a MAX VALUE partition [message #323106 is a reply to message #323064]

Tue, 27 May 2008 05:38

himang
Messages: 282
Registered: March 2005
Location: Bangalore

Senior Member

As I mentioned there are already Global indexes on the table, though we are not doing any activity while splitting the partition.

What we are thinking is to DROP the indexes and then split the empty MAX VALUE partition and then recreate the indexes...

Let us see if that helps...

Report message to a moderator

Re: Split Partitioning a MAX VALUE partition [message #323111 is a reply to message #323105]

Tue, 27 May 2008 05:47

himang
Messages: 282
Registered: March 2005
Location: Bangalore

Senior Member

S.Rajaram wrote on Tue, 27 May 2008 16:07

Raj,
No we initially splitted the empty table in 365 partitions and filled the data daily and the MAX VALUE partition is still empty. While splitting we don't mention the sub partition clause as it automatically takes care of it...

Report message to a moderator

Re: Split Partitioning a MAX VALUE partition [message #323115 is a reply to message #323111]

Tue, 27 May 2008 06:04

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

Is there any particular reason why you opted for global indexes instead of Local Indexes ?

Regards

Raj

Report message to a moderator

Re: Split Partitioning a MAX VALUE partition [message #323116 is a reply to message #323115]

Tue, 27 May 2008 06:09

himang
Messages: 282
Registered: March 2005
Location: Bangalore

Senior Member

S.Rajaram wrote on Tue, 27 May 2008 16:34

Is there any particular reason why you opted for global indexes instead of Local Indexes ?

Regards

Raj

Hmmm.. good question.. but I don't have any answer to it as this table and indexes were created before I joined the project. But yeah would like to know if this could be affecting the splitting performance. If yes and this is only reason then we would see that in future we can change these global indexes to local indexes...

Report message to a moderator

Re: Split Partitioning a MAX VALUE partition [message #323133 is a reply to message #323116]

Tue, 27 May 2008 07:15

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

First trace to know where it spend its time.
Then you can know where you have to investigate.

Regards
Michel

Report message to a moderator

Re: Split Partitioning a MAX VALUE partition [message #323636 is a reply to message #323133]

Thu, 29 May 2008 01:42

himang
Messages: 282
Registered: March 2005
Location: Bangalore

Senior Member

Just for information for other people, we resolved the issue by :-
1. First dropped the MAX_VALUE partition (as there was no data in max_value partition, so no data loss risk).
2. Then added the required partition. This didn't took time at all.
3. Then again added back the MAX_VALUE partition.

This whole activity took less than a minute and was achieved without any issue.

Regards
Himanshu

[Updated on: Thu, 29 May 2008 01:46]

Report message to a moderator

Re: Split Partitioning a MAX VALUE partition [message #323655 is a reply to message #323636]

Thu, 29 May 2008 02:53

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

But you still not know the reason of this execution time, so you can't say this will never happen with your new way to do it.

Regards
Michel

Report message to a moderator

Re: Split Partitioning a MAX VALUE partition [message #323755 is a reply to message #323064]

Thu, 29 May 2008 06:36

himang
Messages: 282
Registered: March 2005
Location: Bangalore

Senior Member

Yeah we know. It was due to the Global indexes.. It was taking time because of index rebuilding.Any splitting of partition was taking time due to the global indexes, which doesn't happens when we are adding new partition. So rather than splitting an empty partition, we dropped and added the new partitions...

Report message to a moderator

Re: Split Partitioning a MAX VALUE partition [message #323760 is a reply to message #323755]

Thu, 29 May 2008 06:43

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Nice to know the reason.

Regards
Michel

Report message to a moderator

Previous Topic:	extent size
Next Topic:	SGA Sizing

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 09 02:24:50 CDT 2024