Re: Slow create table statement

From: Ram Raman <veeeraman_at_gmail.com>
Date: Tue, 7 Jul 2020 21:25:34 -0500
Message-ID: <CAHSa0M0_P_5Gh2B7YT5R4nb1DNRc_xj+2_UnmBLdicZkL4+UhQ_at_mail.gmail.com>



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 <andysayer_at_gmail.com> 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 <tim.evdbt_at_gmail.com> 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...
>>
>> ALTER SESSION ENABLE PARALLEL DDL;
>> 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...
>>
>> CREATE TABLE .... SELECT * FROM IX.S_CLN_EVNT WHERE 1 = 0;
>> ALTER SESSION ENABLE PARALLEL DML;
>> INSERT /*+ APPEND PARALLEL(x,8) */ INTO REQ_27333_CLN_EVENT
>> 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
>> <https://drive.google.com/file/d/1N8re2eAWLl0FHYdGs0c9I93e5ruoSNN6/view?usp=sharing>
>> has all the commands and the information. Any help is appreciated.
>>
>> Thanks,
>> Ram.
>>
>> --
>>
>>
>>
>>

--

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jul 08 2020 - 04:25:34 CEST

Original text of this message