Re: Slow create table statement

I'm a little lost myself..
"I guess it is time for new hardware." - Probably not. The covering index was a side alternative to trying the full table scan (I suggested you hint at). I see Jonathan has pointed you back on to that path so great.

We also are yet to know about this partitioning scheme, is it partitioned in a way that should be helpful here? Hash partitioning with 32 partitions doesn't scream useful for this query but there's always the possibility that your DO2.REQ_27333_DX_ENCNTR table has some relation to your partition key.

What's the story behind this? It looks like a one off statement but if you're concerned about it's performance enough to consider buying new hardware maybe there's more to the problem and there's a completely different better solution than running this create table statement. Although I imagine the full tablescan + hash join with your current level of parallelism should fly considerably faster than your nested loops with table access.

Thanks,
Andy

On Fri, 10 Jul 2020 at 22:58, Ram Raman <veeeraman_at_gmail.com> wrote:

> Thanks Jonathan. There is no compression, but many columns dont have
> values in them. I am bit puzzled by the naming convention, but that is a
> different topic
>
> What you said makes good sense. It doesnt make sense to access them by
> nested loops. Here are the big table stats:
>
>
> NUM_ROWS BLOCKS LAST_ANAL AVG_ROW_LEN CHAIN_CNT
> ------------------------ ------------ --------- ----------- ----------
> 1,738,075,641 40,228,648 24-JUN-20 371 0
>
> Only one block size for the whole DB:
>
> SQL> sho parameter db_block_size
> NAME TYPE VALUE
> ------------------------------------ --------------------------------
> ------------------------------
> db_block_size integer 8192
>
> Partitions stats for the big table:
>
> PARTITION SUBPARTIT PARTITION_COUNT STATUS
> --------- --------- --------------- --------
> HASH NONE 32 VALID
>
> I did some calculation myself. If the query has to retrieve 14M rows from
> the big table, given the average row length is 371 and hence rows per block
> is about 22, it has to visit 634K blocks. Does not make sense to do nested
> loop join for that. Let me try a HASH join hint
>
> Thanks
> Ram.
>
>
> On Fri, Jul 10, 2020 at 2:07 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Have I missed some posts on this thread ?
>> It looks to me as if your problem is simply a bad execution plan -
>> possibly due to bad stats.
>>
>> The "inc" distinct count is about 1,700 but your total row count is 14M,
>> which means you're using an index to pick up about 8,000 randomly
>> distributed rows from a big table/ for each distinct value in the nested
>> loop driver. The optimizer thinks (for whatever reason) it will be about
>> 1,600 rows fairly well clustered (about 8 rows per block accessed). At 14M
>> rows that's about 1.75M random I/Os - and there's little doubt that a lot
>> of them will collide and most of them will be real physical reads.
>>
>> How many partitions in the table and what is the block size ? (It looks
>> like it might be 16KB blocks - and that might have an effect on
>> performance).
>>
>> You're optimum plan might be a parallel hash join with a broadcast/none
>> distribution to minimise messaging between parallel execution processes.
>>
>> One thing that might make the processing slow is your comment that you've
>> got 30 columns of varchar2(4000) - even though the average row size seems
>> to be in the region of 200 or 400 bytes - so maybe you've also got
>> compression in place. One feature of very long rows is that if the
>> declared length of the rows is greater than the size of a block Oracle will
>> probably fall back to single row processing on the insert. (I'd have to
>> check a combination of things to be sure, including the effect of the /*+
>> append */ hint). So it's almost certainly a good idea to pre-declare the
>> target table - ideally limiting the column sizes to get a row size less
>> than the block size.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Fri, Jul 10, 2020 at 6:12 PM Ram Raman <veeeraman_at_gmail.com> wrote:
>>
>>> Thanks Andy. I am not sure about a covering index as the table has
>>> around 50 columns, with 30 of them VARCHAR2(4000)! Not sure if creating a
>>> covering index on such a table with 2B rows a good idea.
>>>
>>> I did some tests and as you pointed out the count(*) comes back fast,
>>> but when i do a SELECT of all columns instead of count from the big table
>>> the query slows down to about 3 hours like the create table. I guess it is
>>> time for new hardware.
>>>
>>>
>>> On Wed, Jul 8, 2020 at 2:09 AM Andy Sayer <andysayer_at_gmail.com> wrote:
>>>
>>>> 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