MIME-Version: 1.0
References: <CAEjw_fg4AURJOGnCvnjgicYo9AKCVumsExhNzQXwVVKd90atNw@mail.gmail.com>
 <ac49f9c6-2af0-a3d3-4edc-9b8f1d1ed4f8@gmail.com> <CAEjw_fhr2unENrwAVAbJ-JuinaVUUdHOKjYH3bHcLtOrvUAFdA@mail.gmail.com>
 <CAGtsp8mqnZDNmq4AoMRG51bcC1CtW=cerbkd0y2vCWJSdYbw-A@mail.gmail.com>
 <CAEjw_fgVNvEgoXWdSNqxtuVar-8aRVbhKLmg5cqdz8u7kzvHWQ@mail.gmail.com>
 <CAGtsp8kk0rWjDFRyDOVb2vAGTvThd4bmHjaKVK8_ZC-C3UughA@mail.gmail.com>
 <CAEjw_fiiM7kw35t_m9N7m1Z6uV1cjpBG2H6p9Ba_imqS5T8LEg@mail.gmail.com>
 <CAGtsp8ky0tM3NTJavzYg=WRTDN-o=zx+BFSy0Q1hMZvJ5Tzstg@mail.gmail.com>
 <CAEjw_fjqVicN+EKr5DKZg9cWE-rbCFhegOjALgdz=OHegXJiXw@mail.gmail.com>
 <CAGtsp8=LFT8zKedFtGcGGipYnLKO4tCRHH8iuBx4wxi9xtX9Yw@mail.gmail.com>
 <CAEjw_fj_LSUQJZqS5AxyKcu_NZbiADoCP4VmPmn61axfemFM7Q@mail.gmail.com>
 <CAEjw_fi5crY6DPiTFOn7LYoRrzLEbehggm6RW-eUTB4dBQ1DQQ@mail.gmail.com>
 <CAEzWdqe8hZT+6SGJsdkwSt337FuScfGrFiuV6fXt=7U0ViPDyg@mail.gmail.com>
 <CAEjw_fgxeHwU05222ESKWYYmUWcj8YRHZTvGe6sR62hey7_TuQ@mail.gmail.com>
 <5f0001d934cc$2ec38ac0$8c4aa040$@rsiz.com> <CAEjw_fhyoCR5EWyMD_vcUPsi-EWRA90csCkdkgG=RhT4aA6cPQ@mail.gmail.com>
 <CAGtsp8k5Bq8gBP1r0R2QSx4k2Y+g69hBpHtHF6EbVFZr8cYzeA@mail.gmail.com> <CAEzWdqewVt7aJDcGBAqqqCdX+D66V_6Xrz8vDAiLZWV=+vb1Tg@mail.gmail.com>
In-Reply-To: <CAEzWdqewVt7aJDcGBAqqqCdX+D66V_6Xrz8vDAiLZWV=+vb1Tg@mail.gmail.com>
From: Jonathan Lewis <jlewisoracle@gmail.com>
Date: Thu, 2 Feb 2023 15:20:46 +0000
Message-ID: <CAGtsp8mL+Ug7cqbnduwMtSfEgUCsHAk5ZOGf_B3qN2ekb77Zvg@mail.gmail.com>
Subject: Re: Unique index access path seems very slow
To: Oracle L <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="000000000000c90ec905f3b91c45"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jlewisoracle@gmail.com
--000000000000c90ec905f3b91c45
Content-Type: text/plain; charset="UTF-8"

Yuhdi,

As I pointed out, I don't think that there's likely to be *much* change in
performance by hacking in a different cardinality estimates; but we have
seen that the two different figures produce significant changes in WHERE
the time is spent and some change in the effectiveness of off-loading.
Given that clue (and assuming that there isn't a more important task to
address) I would have spent an hour or two re-running the query with a few
different cardinality hints between 49K and 2M to see if there was a sweet
spot that reduced the CPU required to apply the filter, maximised the
effectiveness of offloading, and minimised the number of rows passed up the
plan.

IIRC none of the plans showed any writes on the hash join, so I wasn't
thinking about overheads of hash joins spilling to disk.

The suggestion for re-engineering the data so that Oracle could iterate
through a partition-wise join was also about offload and CPU efficiency. On
smaller data volumes a hash table could have both a smaller number of
buckets and be more accurate in its distribution, so a Bloom filter could
be more effective and cheaper to use on the offload.

The switch to RAW, of course, is mostly about reducing I/O:  the very slow
runs are probably about resource use by other users on the Cell Servers so
a smaler data size means less I/O which means less impact when the hardware
gets busy; it did occur to me to wonder if the CPU cost of hashing a 64
byte raw would be less than the cost of hashing a 128 byte varchar (answer:
probably) which would also reduce run time and the load on the cell server
(and that last one woudl reduce the risk of large volumes of data being
sent unprocessed to the database server).


Regards
Jonathan Lewis


On Tue, 31 Jan 2023 at 19:54, yudhi s <learnerdatabase99@gmail.com> wrote:

> So Jonathan, OP has supplied plans which shows both cases, I. E, with
> large estimation the bigger bloom filter is consuming additional CPU cycle
> and with smaller estimation the hash join is consuming higher CPU cycle.
> But both the cases the total query execution time is closely equal, also op
> mentioned both the plans running for ~30minutes+ many times of the day
> so...
>
> when you said below I. E favoring large bloom filter option, so I am
> wondering if it's because it might help in less temp spill? Or say, do you
> mean its better option of hinting the inline view or tran_tab estimation
> very high so that a bigger bloom filter will be applied and the lesser
> amounts of rows will be passed to the hash join which may also benefit in
> case of large data volume as temp spill will be minimal?
>
> *Note that the Offload Returned Bytes was 300GB for the 49K estimate with
> the small Bloom filter, and 500GB for the 2M estimate with the large Bloom
> filter.*
> *It looks like we need to "fake" the system so that the Bloom filter
> (estimate) is large enough to eliminate a lot of data while being small
> enough to be sent to the cell server so that the 14 concurrently active
> cells can do the row elimination.  Beyond that I don't think there's a way
> to make the query go faster than the (roughly) 650 seconds you've seen so
> far*.
>
>
> On Tue, 31 Jan, 2023, 3:51 am Jonathan Lewis, <jlewisoracle@gmail.com>
> wrote:
>
>> Comparing the 5 hash join plans you've posted:
>>
>>

--000000000000c90ec905f3b91c45
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div>Yuhdi,</div><div><br></div=
><div>As I pointed out, I don&#39;t think that there&#39;s likely to be <i>=
<b>much</b></i> change in performance by hacking in a different cardinality=
 estimates; but we have seen that the two different figures produce signifi=
cant changes in WHERE the time is spent and some change in the effectivenes=
s of off-loading.=C2=A0 Given that clue (and assuming that there isn&#39;t =
a more important task to address) I would have spent an hour or two re-runn=
ing the query with a few different cardinality hints between 49K and 2M to =
see if there was a sweet spot that reduced the CPU required to apply the fi=
lter, maximised the effectiveness of offloading, and minimised the number o=
f rows passed up the plan.</div><div><br></div><div>IIRC none of the plans =
showed any writes on the hash join, so I wasn&#39;t thinking about overhead=
s of hash joins spilling to disk.</div><div><br></div><div>The suggestion f=
or re-engineering the data so that Oracle could iterate through a partition=
-wise join was also about offload and CPU efficiency. On smaller data volum=
es a hash table could have both a smaller number of buckets and be more acc=
urate in its distribution, so a Bloom filter could be more effective and ch=
eaper to use on the offload.<br></div><div><br></div><div>The switch to RAW=
, of course, is mostly about reducing I/O:=C2=A0 the very slow runs are pro=
bably about resource use by other users on the Cell Servers so a smaler dat=
a size means less I/O which means less impact when the hardware gets busy; =
it did occur to me to wonder if the CPU cost of hashing a 64 byte raw would=
 be less than the cost of hashing a 128 byte varchar (answer: probably) whi=
ch would also reduce run time and the load on the cell server (and that las=
t one woudl reduce the risk of large volumes of data being sent unprocessed=
 to the database server).</div><div><br></div><div><br></div><div><br></div=
><div>Regards</div><div>Jonathan Lewis</div><div><br></div><div><br></div><=
div><br></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmai=
l_attr">On Tue, 31 Jan 2023 at 19:54, yudhi s &lt;<a href=3D"mailto:learner=
database99@gmail.com">learnerdatabase99@gmail.com</a>&gt; wrote:<br></div><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto"><div>So =
Jonathan, OP has supplied plans which shows both cases, I. E, with large es=
timation the bigger bloom filter is consuming additional CPU cycle and with=
 smaller estimation the hash join is consuming higher CPU cycle. But both t=
he cases the total query execution time is closely equal, also op mentioned=
 both the plans running for ~30minutes+ many times of the day so...=C2=A0<d=
iv dir=3D"auto"><br></div><div dir=3D"auto">when you said below I. E favori=
ng large bloom filter option, so I am wondering if it&#39;s because it migh=
t help in less temp spill? Or say, do you mean its better option of hinting=
 the inline view or tran_tab estimation very high so that a bigger bloom fi=
lter will be applied and the lesser amounts of rows will be passed to the h=
ash join which may also benefit in case of large data volume as temp spill =
will be minimal?=C2=A0<div dir=3D"auto"><br></div><div dir=3D"auto"><div st=
yle=3D"font-size:12.8px" dir=3D"auto"><i>Note that the Offload Returned Byt=
es was 300GB for the 49K estimate with the small Bloom filter, and 500GB fo=
r the 2M estimate with the large Bloom filter.</i></div><div style=3D"font-=
size:12.8px" dir=3D"auto"><i>It looks like we need to &quot;fake&quot; the =
system so that the Bloom filter (estimate) is large enough to eliminate a l=
ot of data while being small enough to be sent to the cell server so that t=
he 14 concurrently active cells can do the row elimination.=C2=A0 Beyond th=
at I don&#39;t think there&#39;s a way to make the query go faster than the=
 (roughly) 650 seconds you&#39;ve seen so far</i>.</div></div></div><br><br=
><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, 3=
1 Jan, 2023, 3:51 am Jonathan Lewis, &lt;<a href=3D"mailto:jlewisoracle@gma=
il.com" target=3D"_blank">jlewisoracle@gmail.com</a>&gt; wrote:<br></div><b=
lockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=
=3D"ltr"><div>Comparing the 5 hash join plans you&#39;ve posted:</div><br><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex">
</blockquote></div></div>
</blockquote></div></div></div>
</blockquote></div></div>

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