RE: Huge import takes a long time

I smell a global/nonpartitioned index on a partitioned table.  

Yuck.  

First thing I'd be doing (did, here) is get rid of that thing. It may well take some application love, but really really really worth it.  

We had a 1.2Billion row index on a partitioned table, and I slept sooo much better after we partitioned it.    

-----Original Message-----

	From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Marquez, Chris
	Sent: Thursday, July 21, 2005 11:17 AM
	To: Michael.Kline_at_SunTrust.com; oracle-l_at_freelists.org
	Subject: RE: Huge import takes a long time
	
	

	Without knowing or looking at the specifics of your situation or the limitations of partition IMPorts, these are small and large things that have helped me and I have used many times in the past and even within the last week to complete a large db migration to a new platform (boy, I will be glad when RMAN can just move/copy/clone (data) files from OS-a to OS-b!!!)
	
	Be forewarned about using "_[parameter]" parameters!
	Using them and having a db/server crash during use, means corrupt database for sure.
	Un-set them when finished *AND* make sure you can switch / cycle all redo (arch logs)...test, test, test.
	You have been warned.
	
	Good luck...hth
	
	Chris Marquez
	Oracle DBA
	


	-----------------------

	Faster IMPORTS


	-----------------------

	Set IMP parameter COMMIT = Y.
	Set IMP parameter RECORDLENGTH >= EXP RECORDLENGTH value.
	Set IMP parameter BUFFER = MB (Set in the MB range not KB range)
	
	Set IMP parameter STATISTICS = NONE (9i, n/a 8i)
	
	Set IMP parameter RECALCULATE_STATISTICS = N. (8i, n/a 9i)
	Set IMP parameter ANALYZE = N. (8i, n/a 9i)
	
	Set IMP parameter INDEXES = N (Import them later...separately)
	
	Set init.ora parameter LOG_ARCHIVE_START = FALSE *&*   alter database noarchivelog;
	Set init.ora parameter _disable_logging = TRUE (Warning, this could be dangerous and unrecoverable in failure.  Backup immediately after import - * Redo records (to disk) will NOT be generated (redo WAS generated in the log buffer.) [Even with _disable_logging you still "switch" logs when they get "full"])
	Set init.ora parameter _wait_for_sync = FALSE (Warning, this could be dangerous and unrecoverable in failure.  Backup immediately after import - _wait_for_sync: Wait_for_sync is an oracle generic parameter which, when set to  false, will allow the system to complete commits without waiting  for the redo-log buffer flushes to complete.)
	
	Make redo logs enormous; 500MB, 1GB, etc.
	
	Use Locally Managed Tablespaces on target database.
	
	Chris Marquez
	Oracle DBA
	C-(703)507-1421
	cmarquez_at_capwiz.com
	
	


	-----Original Message-----

	From: oracle-l-bounce_at_freelists.org on behalf of Kline.Michael
	Sent: Thu 7/21/2005 10:00 AM
	To: oracle-l_at_freelists.org
	Subject: Huge import takes a long time
	
	I've got a massive import that takes a long time. This is one I'd love
	to drop and recreate the index, but that alone would probably be almost
	a terabyte, so that is out of the question.
	
	
	
	This is a table partitioned by month and a normal partition is about
	20-24GB. As it's coming in, it's getting a lot of I/O wait on the index
	datafiles, but only the datafiles of that partition. The index is also
	partitioned.
	
	
	
	This is now 9.2.0.6, so just how high can you set up the buffer?
	
	
	
	Are there any other "tricks"?
	
	
	
	As I get to the "end" of the partition my import rate is getting to be
	2-3GB per day.
	
	
	
	Yuck!!!
	
	
	
	
	
	Michael Kline
	Database Administration
	SunTrust Technology Center
	1030 Wilmer Avenue
	Richmond, Virginia  23227
	Outside 804.261.9446
	STNet 643.9446
	
	Cell 804.744.1545
	 <mailto:michael.kline_at_suntrust.com> michael.kline_at_suntrust.com
	 
	 
	 
	LEGAL DISCLAIMER
	The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer.
	 
	Seeing Beyond Money is a service mark of SunTrust Banks, Inc.
	[ST:XCL]
	
	
	
	
	
	
	
	
	

--
http://www.freelists.org/webpage/oracle-l