Home -> Mailing Lists -> Oracle-L -> Re: Unique index access path seems very slow

Re: Unique index access path seems very slow

From: Jonathan Lewis <jlewisoracle_at_gmail.com>
Date: Sun, 12 Feb 2023 22:16:44 +0000
Message-ID: <CAGtsp8=UBeGnwiUEfUQ22j8PvAGVXXtGhWaBn+vddWF+radAtA_at_mail.gmail.com> 








Yudhi,



I don't think there's a "best practice" as such. There are several details
that might need to be balanced in any particular situation. My immediate
response to 256 hash partition is that it sounds a little high for the
requirement, especially since it also has to apply to the sub-partitions of
a composite partitioned table.



    
  
  1.   the limit on data segments per object is 2^20 - 1, so at 256
subpartitions you have a maximum of approx. 4,000 partitions before you hit
a hard limit, which for daily partitions means about 11 years. That's not a
problem in this case, but I have seen someone asking why at 1,024
subpartitions their system "broke" after 3 years.

  
  2.   the larger number number of segments the more segment information has to
be kept in the data dictionary, and the longer it may take to optimize some
types of statement, and the most contention between concurrent processes.

  
  3.   You have to allow for the volume of statistical information that needs
to be held as the number of partitions grows - it can get very big
(especially with the older synopsis mechanism and the automatic choice of
histograms.

  
  4.   If you have too many segments they may get so small that Oracle stops
doing direct path reads, and switches back to cached reads, using more CPU
and not taking advantage of smart scan.







I will be writing some notes on the business of how partition size might
affect the efficiency of the hash join - but the symptom being displayed in
this case was more due to the optimizer's ESTIMATE of the rows in the build
table rather than the actual number of rows, and my initial thought is that
a  much smaller number of hash partitions would still mean the Bloom filter
could be small enough to be very useful.



Regards


Jonathan Lewis






On Sat, 11 Feb 2023 at 04:22, yudhi s <learnerdatabase99_at_gmail.com> wrote:



> And Jonathan, I am curious as OP also asked, what should be the optimal


> number of hash partitions in this scenario for the tab_encrypt table. Is

> there any best practice for deciding that or it's hit and trial only? Not

> sure if you missed, OP has posted the 10104 hash join traces in one of the

> reply.

>

>

>>>>>>>>

>>>>>>>>



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


Received on Sun Feb 12 2023 - 23:16:44 CET












Original text of this message