Re: Slow create table statement
Date: Tue, 7 Jul 2020 15:25:02 -0700
Message-ID: <e758b06b-8ba4-7554-ca30-d5dbb33552d1_at_gmail.com>
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...
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:
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 ... ;
> 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-lReceived on Wed Jul 08 2020 - 00:25:02 CEST