Re: Slow create table statement

From: Ram Raman <>
Date: Tue, 7 Jul 2020 21:25:34 -0500
Message-ID: <>

Thank you Tim and Andy,

" besides making a covering index" Andy, there is a covering index which makes the access of CLN_EVNT table by index, per the plan

On Tue, Jul 7, 2020 at 5:39 PM Andy Sayer <> wrote:

> Tim,
> "Load as select" means it's doing direct path load. The additional step of
> "OPTIMIZER STATISTICS GATHERING" also only appears for direct path load.
> We can also tell that the insertion into the table is done in parallel as
> we are still using PCWP in the IN-OUT column, it only serializes about the
> load.
> Thanks,
> Andy
> On Tue, 7 Jul 2020 at 23:25, Tim Gorman <> wrote:
>> Ram,
>> Perhaps it is serializing on the INSERT phase of the CTAS, keeping in
>> mind that the CREATE clause both creates the table and does the insert?
>> Perhaps...
>> CREATE TABLE ... PARALLEL 8 SELECT /*+ full(ce) parallel(ce,8) */
>> CE.* FROM ... ;
>> Unless you are required to perform CTAS in one statement, could you
>> perhaps split it into two steps, the first to create the table and the
>> second to populate it? So as an alternative...
>> SELECT /*+ full(ce) parallel(ce,8) */ CE.* FROM ... ;
>> This way you can use the APPEND hint to force direct-path INSERTs which
>> are far more efficient than conventional INSERTs; I'm not sure if LOAD AS
>> SELECT (HYBRID TSM/HWMB)" is direct-path or conventional? At any rate,
>> with an APPEND hint you can be certain. You'll also get to specify the
>> PARALLEL hint on the INSERT statement as well as the SELECT statement, to
>> make sure of PX pipelining. Also, not sure if your session is enabled for
>> parallel DDL or parallel DML, but if you break it into two operations, you
>> will only need parallel DML.
>> Hope this helps?
>> -Tim
>> On 7/7/2020 2:48 PM, Ram Raman wrote:
>> Hi,
>> We are trying to create a table by pulling information from a big table,
>> the create statement is taking too long. However when I run select count(*)
>> with the same query it comes back in seconds. While the CREATE statement is
>> running I see waits on parallel read almost all the time, however, I do not
>> see the same waits during select count(*).
>> Tried a few variations with parallelism degree in the Create statement.
>> Also tried inserting after truncating the table, used INSERT with APPEND
>> hint, same slow results. v12c. FORCE_LOGGING is not on at DB level,
>> inserted table in nologging mode. During the run, the IO on the data disks
>> is about 50 to 70% util. 2 cores are consistently at 99-100% (most of them
>> are %iowait at ~95%) with the other cores idle during this run the last
>> time I checked - I only checked the individual core usage this last time it
>> was run. I was checking average before. The wait events are the same in all
>> cases.
>> The attached link
>> <>
>> has all the commands and the information. Any help is appreciated.
>> Thanks,
>> Ram.
>> --


Received on Wed Jul 08 2020 - 04:25:34 CEST

Original text of this message