Re: Slow create table statement

Andy, we have issues with hardware, not because of this query performance but because the hardware is almost 8 years old. Talks are already on to replace it.I definitely was not meaning to say that we have to replace hardware because of the create table statement.

This warehouse was designed before me. They access this big table in multiple ways using multiple columns. The table is hash partitioned (32 partitions) and the partition key is E VENT_SK. In this case, the DO2.REQ_27333_DX_ENCNTR table is joined using a different key (person key). I definitely have to look into ways to improving the partitioning scheme. If you have inputs, I am open to it. In the previous places I was in, we used to range partition on the 'main column' and sometimes hash subpartition the partitions. Out here they seem to access the table by several columns. I have to find out more about it and see if there is a way to design the table so that it is accessed by the partition key column only.

re the statement, it looks like a one off statement, but apparently they execute similar statements regularly. I have been trying to have a detailed discussion with them.

Thanks,
Ram.

On Fri, Jul 10, 2020 at 6:28 PM Andy Sayer <andysayer_at_gmail.com> wrote:

> 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