RE: make create table run faster?
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: <gmei@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Apr 2009 08:26:30 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Guang Mei
Sent: Tuesday, April 28, 2009 10:56 AM
To: oracle-l@xxxxxxxxxxxxx
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.
****************************************************************************
Other related posts:
