Re: Slow create table statement

Hi Ram,

Covering index means that it covers every column that’s required by the query (even the ones in your select). Judging by the cost in the plan, it’s unlikely the index used contained more than one column. Covering indexes remove the need for table access. You would need to weigh up the cost of creating an index the size of the table and the reward (this query gets faster).

Thanks,
Andrew

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-l