Home » RDBMS Server » Performance Tuning » Re: truncate take longer time.
Re: truncate take longer time. [message #65155] Tue, 25 May 2004 07:43 Go to next message
Raymond
Messages: 30
Registered: September 2000
Member
Hi Guru,

Currently I'm doing a housekeep for our gl tables. My client is running at MVS Oracle 8.0.6. (going to upgrade soon).

There are abt 11559551 row in our gl data.

I have planned 3 step for the housekeeping

1) Perform a record count.

2) Creating backup table based on the condition join to the source tables.

3) -Disable constraint ,truncate,drop index,insert from backup table ,recreate  index. enable constraint.

index What my problem now is when i trying to truncate the source tables. it take me more then 10 hours to perform ? What might be the cause of the problem

This is my table script

  A     VARCHAR2 (5)  NOT NULL,
  B     VARCHAR2 (2)  NOT NULL,
  C      NUMBER (6)    NOT NULL,
  D    NUMBER (2)    NOT NULL,
  E     VARCHAR2 (3)  NOT NULL,
  f   VARCHAR2 (2)  NOT NULL,
  g      VARCHAR2 (3)  NOT NULL,
  h    VARCHAR2 (5),
  i     VARCHAR2 (5),
  TRNDT     DATE          NOT NULL,
  GLCD      VARCHAR2 (20)  NOT NULL,
  DRCRFLG   VARCHAR2 (1)  NOT NULL,
  DESCR     VARCHAR2 (30)  NOT NULL,
  AMT       NUMBER (16,2) DEFAULT 0,
  EXAMT     NUMBER (16,2) DEFAULT 0,
  EXRATE    NUMBER (10,7),
  SEQNO     NUMBER (6),
  SEQCNT    NUMBER (6),
  USRTRNCD  VARCHAR2 (5),
  POSTDT    DATE)

   TABLESPACE SATA
   PCTFREE 10
   PCTUSED 90
   INITRANS 1
   MAXTRANS 255
  STORAGE (
   INITIAL 8192
   NEXT 10240000
   PCTINCREASE 0
   MINEXTENTS 1
   MAXEXTENTS 5000
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE;

 

 

 

 

 

 

 

 

 

 

 
Re: truncate take longer time. [message #65158 is a reply to message #65155] Tue, 25 May 2004 13:48 Go to previous messageGo to next message
sverch
Messages: 582
Registered: December 2000
Senior Member
I am not a DBA, but your storage parameters seem way too low to me. Try to rebuild the table using calculation formula and see what happens.
Re: truncate take longer time. [message #65159 is a reply to message #65155] Wed, 26 May 2004 00:38 Go to previous messageGo to next message
Himanshu
Messages: 457
Registered: December 2001
Senior Member
Hi,
Whatever you are doing is just fine except a couple of additions are required.
A general way to go about it is,
1. Disable the indexes
2. Truncate the table - using drop unused.
3. Perform a manual coalesce.
4. Rebuild the index.

HTH

Regards
Himanshu
Re: truncate take longer time. [message #65161 is a reply to message #65158] Wed, 26 May 2004 02:35 Go to previous messageGo to next message
Raymond
Messages: 30
Registered: September 2000
Member
My client is running under Oracle 8.0.6 , is there any way to rebuild the way with minimal downtime ? I know the extend is too slow .

I try simulte the no of record at my server when a higher initial extend and it did give me increase in performance.
Re: truncate take longer time. [message #65162 is a reply to message #65159] Wed, 26 May 2004 02:37 Go to previous messageGo to next message
Raymond
Messages: 30
Registered: September 2000
Member
Prompt "restructure table "

rename ln21batfold to ln21batf;
rename ln21batdold to ln21batd;

ALTER TABLE LN21BATD DISABLE CONSTRAINT ln21batd_ln21batf_fk;

ALTER TRIGGER cf20gldt_89log DISABLE;

TRUNCATE TABLE LN21BATF;
TRUNCATE TABLE LN21BATD;

INSERT INTO LN21BATF
SELECT * FROM LN21BATFBK;

COMMIT;

INSERT INTO LN21BATD
SELECT * FROM LN21BATDBK;

COMMIT;

Prompt "drop index ln21batf"

DROP INDEX LN21BATF_PK_I;

drop index LN21BATD_I;

CREATE INDEX LN21BATF_PK1 ON
LN21BATF(ORGCD)
TABLESPACE SSKSIXD PCTFREE 10 STORAGE(INITIAL 8192 NEXT 49152 PCTINCREASE 0 ) ;

CREATE INDEX LN21BATD_I ON
LN21BATD(TRNBRN, CRLINE, YR, SEQNO)
TABLESPACE SSKSIXD PCTFREE 10 STORAGE(INITIAL 1126400 NEXT 184320 PCTINCREASE 0 )
;

ALTER TABLE LN21BATD enable CONSTRAINT ln21batd_ln21batf_fk;

spool off;
Re: truncate take longer time. [message #65165 is a reply to message #65161] Wed, 26 May 2004 07:37 Go to previous message
sverch
Messages: 582
Registered: December 2000
Senior Member
The only way to solve this problem is to change storage parameters. You can either
1. Backup data
2. Drop tables with insufficient storage parameters.
3. Calculate correct parameters
4. Recreate tables using sufficient parameters
5. Load tables using backup data.
Or:
1. Calculate correct parameters
2. Alter tables using the following command:
ALTER TABLE tablename MOVE TABLESPACE tablespace_name
STORAGE ( INITIAL your_correct_numbers
NEXT your_correct_numbers
MINEXTENTS
etc);
Previous Topic: Statspack Viewer Enterprise for Oracle10g
Next Topic: DBMS_STATS.GATHER_TABLE_STATS
Goto Forum:
  


Current Time: Sun Sep 27 22:20:04 CDT 2020