RE: make create table run faster?
Date: Thu, 30 Apr 2009 08:26:30 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E36062229F7_at_CWYMSX04.Corp.Acxiom.net>
Guang, as others have suggested, gather wait events and statistics on the CTAS to see quantities of resources used and where waits are happening. Since it's a parallel query, you'll need to gather them at the system level when the no other activity is occurring on the database. The combination of statistics and waits will point out where time is spent.
Concerning parallelism, there's a break-even point on the parallel degree and whether or not to use it, based on server resources and size of the object in question. For example, on our servers I've set as a general guideline to only set a parallel degree on an object that is at least 200MB in size. The optimizer divides up the work based on extents, not rows, which is why I go off of object size. Objects under 200MB won't benefit from parallelism on full scans, due to overhead associated with parallelism. You'll have to find this cutoff line for yourself, as it'll vary per server, again, based on resources available.
Also, to me, never go with less than a parallel degree of 4 and stick with powers of 2 or at least divisable by 2.
HTH. David C. Herring | DBA, Acxiom Automotive
630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax 1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Guang Mei
Sent: Tuesday, April 28, 2009 10:56 AM
To: oracle-l_at_freelists.org
Subject: make create table run faster?
Oracle 10g in a NONARCHIVE mode.
I need to create some temp tables during some process. These temp tables
will not be updated. I am doing something like this now:
CREATE TABLE TMP_TABLE123 PCTFREE 0 parallel 3 nologging as select ... from <some_base_tables>
Is there any other oracle parameters that I could use to make this step faster?
Guang
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 resend this communication to the sender and delete the original message or any copy of it from your computer system.
Thank You.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Apr 30 2009 - 08:26:30 CDT