Re: Slow create table statement

On the previous estimate (NL one) I would check the high and low values for the join key PERSON_SK.
Is this a growing value? What is the meaning behind table REQ_27333_DX_ENCNTR?
Wonder if the time difference for the stats make a difference on the join estimate.

Regards

Lothar

Am 11.07.2020 um 09:24 schrieb Jonathan Lewis:
>
> Ram,
>
> I see the plan did a broadcast - which was necessary for the best
> performance - but I was a little surprised to see that it didn't do a
> partition iterator for the parallelism on the big table, given that
> the degree was 8 and the number of partitions was 32 (viz: a multiple
> of 8). However, that probably wouldn't have made very much
> difference.  The minimal improvement at the higher degree was probably
> because DOP 32 probably pushed you into queueing at the disc, it's
> counter-intuitive but you might find 16 (e.g.) is better than 32.
>
> It looks as if someone, or something, may have gathered some stats
> since the nested loop run. The cardinality estimate for the hash join
> is now 15M when the estimate for the nested loop was 2.8M: if the
> stats hadn't changed the cardinality estimate wouldn't change; and a
> factor of 5 on the NL cost might have been enough to push it into a
> hash join automatically.
>
> Regards
> Jonathan Lewis
>
>
>
> On Sat, Jul 11, 2020 at 2:14 AM Ram Raman <veeeraman_at_gmail.com
> <mailto:veeeraman_at_gmail.com>> wrote:
>
> Jonathan, I hinted to use HASH join, and as you said it was lot
> faster - returns in15 mins overall. Tried increasing the
> parallelism upto 32, but I only gain 2 mins with higher degrees.
> Here is the same document with the new statement towards the end:
> https://drive.google.com/file/d/1hIUHbVfBxsvJz5zC3g5ZZGRg99MPz6p7/view?usp=sharing
>
> Now I need to find out why it was not using the HASH in the first
> place without hint and also see if I can make it even faster.
>
> On Fri, Jul 10, 2020 at 4:57 PM Ram Raman <veeeraman_at_gmail.com
> <mailto: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.
>
>

--
http://www.freelists.org/webpage/oracle-l