RE: Import Question

From: Wolfson Larry - lwolfs <lawrence.wolfson_at_acxiom.com>
Date: Mon, 12 Jul 2004 18:39:52 -0500
Message-ID: <433A07749711884D8032B6A0AB115262C2BC7B@conmsx07.corp.acxiom.net>

Mike,

I didn't see how big your database is or how much resources you have. It's just some of the guys suggested you increase SA to speed up the process.

This can help considerably if you have to build indexes on large tables. As I noticed your SARS was set to zero.

To only affect that process (R3load or imp) you might try the code below.

Set SA and SARS depending on what memory you have available. If you have plenty, I think you can go up to 2048576000.

Larry

SPOOL LOGON.trg.out
SELECT MAX(bytes)*1.5
  FROM DBA_SEGMENTS
  WHERE segment_type = 'INDEX'
;
-- Change SA/SAR for import program
CREATE OR REPLACE TRIGGER DB_LOGON after logon   on database
Declare
begin
  for rec in (SELECT program FROM V$SESSION

              WHERE audsid   = userenv('sessionid')
               AND   program = 'R3load'
             (
      loop
      execute immediate 'ALTER SESSION SET SORT_AREA_RETAINED_SIZE =
1000000000;';
      execute immediate 'ALTER SESSION SET SORT_AREA_SIZE          =
1000000000;';
      end loop;
      exception
        when others
          then
            null;

end;
/
SHOW ERRORS
SPOOL OFF
-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Vergara, Michael (TEM)
Sent: Monday, July 12, 2004 10:12 AM
To: oracle-l_at_freelists.org
Subject: RE: Import Question

Everyone:

Thanks for your tips. We found the problem and it was not the DB (as we DBA's suspected), it was the disk frame that the volues were on. It's an EMC Clariion frame, and it's a lemon. We moved the volumes to a different frame and we were off to the races!

Cheers,
Mike

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


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



**********************************************************************
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.



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

Please see the official ORACLE-L FAQ: http://www.orafaq.com


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

To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


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

Received on Mon Jul 12 2004 - 18:40:27 CDT