Re: Slow create table statement
Date: Wed, 8 Jul 2020 08:08:49 +0100
Message-ID: <CACj1VR4AZo7OAiboHC9Sizp4dQ2zzOYLdJ2OiOpZF6kigN1Q9Q_at_mail.gmail.com>
On Wed, 8 Jul 2020 at 03:25, Ram Raman <veeeraman_at_gmail.com> wrote:
> 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-lReceived on Wed Jul 08 2020 - 09:08:49 CEST