MIME-Version: 1.0
References: <CAKna9VYfit64QskJCDGhUnK=S_hydbbrPy3-E9yJMWrOR87=pg@mail.gmail.com>
 <CAKna9VZHgdXG4Y9DJwSPdGxKT5feF=Jwo5V6bsYbeuzi4nNRPg@mail.gmail.com>
 <CAGtsp8nw+6cR460arRvpEY+8AX-8bMKdgVtMiewCSDBxUo_28A@mail.gmail.com>
 <CAKna9VZ4NbV9=B=dm-7RhA-dXNAtSmhSGO-QpheusZRq2xY0uQ@mail.gmail.com>
 <CAGtsp8mbuuPRFz-3gcV=BM2ASi0d6LL79qBvWO=uDwCG6snA+Q@mail.gmail.com>
 <CAKna9VaJ_AiBRBYZtipDWeJes=qA3_FFJb1gRc=pY_Ts38G3+Q@mail.gmail.com>
 <CAGtsp8ndex_94zMB9F5sk3RYzLijY0xYUQMn73uaiHAcMX7qtg@mail.gmail.com>
 <CAKna9VZ7xG5HRc_qwVce_j63eac=M=B+JG1uodoCfFpcWqjOUw@mail.gmail.com> <CAKna9Vb7YHuQocbcp_0AZLQdMwzPdOXNQGs1Vg0tE6xnPsqE4A@mail.gmail.com>
In-Reply-To: <CAKna9Vb7YHuQocbcp_0AZLQdMwzPdOXNQGs1Vg0tE6xnPsqE4A@mail.gmail.com>
From: Jonathan Lewis <jlewisoracle@gmail.com>
Date: Tue, 2 Feb 2021 17:47:58 +0000
Message-ID: <CAGtsp8nkUS7Bz2iYQWzLj-ahXOa-_pSX1Ld4eaxrT_AMWnTexg@mail.gmail.com>
Subject: Re: Performance issue on query doing smart scan
To: Oracle L <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="000000000000e723af05ba5e12e1"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jlewisoracle@gmail.com
--000000000000e723af05ba5e12e1
Content-Type: text/plain; charset="UTF-8"

Deciding whether to talk to Oracle or not about the double decompression is
something you'll have to decide for yourself. You could show them the
figures I've raised (and then give them then entire set of non-zero figures
as a follow-up) and ask if double decompression is the correct
interpretation, and if there are any known bugs and related patches. It's
possible they'll just tell you to make your SQL more efficient and add
indexes to do less work.

As far as building the index is concerned. An interesting thought for
building the index is this:

You can create the index as unusable, and this will take virtually no time
at all.

Then you can start running 'alter index XXX rebuild partition YYY' from the
oldest end upwards.  (I assume that your millions of inserts/updates per
day are likely to be at the recent end of the data).

Oracle 11.2.0.4 is capable of using "TABLE EXPANSION" to split the query
into what is effectively a UNION ALL query against a view with a name like
VW_TE_nnn that uses index range scans for the partitions that have usable
indexes and tablescans for the partitions where the index is still
unusable.  Here's a linke to an article by Maria Colgan about the
transformation:
https://blogs.oracle.com/optimizer/optimizer-transformations:-table-expansion

Since you have 180 partitions, and since the plan will (should) split into
two distinct pieces, you can examine the SQL*Monitor report and see how
well the indexed branch performs relative to the tablescan part, and may
get some idea of how efficient the whole thing will be by the time you've
built the first 10 or 12 partitions.

Regards
Jonathan Lewis


On Tue, 2 Feb 2021 at 15:02, Lok P <loknath.73@gmail.com> wrote:

> Also Jonathan, apart from having the new index created(which will really
> need a lot of tests/agreement because this transaction table is 7TB in size
> and is exposed to heavy batch inserts happening each day 24/7,
> ~250-300million rows each day) to help cater this query,
>>
>>
>>>

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

<div dir=3D"ltr"><div dir=3D"ltr"><div><br></div><div>Deciding whether to t=
alk to Oracle or not about the double decompression is something you&#39;ll=
 have to decide for yourself. You could show them the figures I&#39;ve rais=
ed (and then give them then entire set of non-zero figures as a follow-up) =
and ask if double decompression is the correct interpretation, and if there=
 are any known bugs and related patches. It&#39;s possible they&#39;ll just=
 tell you to make your SQL more efficient and add indexes to do less work.<=
/div><div><br></div><div>As far as building the index is concerned. An inte=
resting thought for building the index is this:</div><div><br></div><div>Yo=
u can create the index as unusable, and this will take virtually no time at=
 all.</div><div><br></div><div>Then you can start running &#39;alter index =
XXX rebuild partition YYY&#39; from the oldest end upwards.=C2=A0 (I assume=
 that your millions of inserts/updates per day are likely to be at the rece=
nt end of the data).</div><div><br></div><div>Oracle 11.2.0.4 is capable of=
 using &quot;TABLE EXPANSION&quot; to split the query into what is effectiv=
ely a UNION ALL query against a view with a name like VW_TE_nnn that uses i=
ndex range scans for the partitions that have usable indexes and tablescans=
 for the partitions where the index is still unusable.=C2=A0 Here&#39;s a l=
inke to an article by Maria Colgan about the transformation: <a href=3D"htt=
ps://blogs.oracle.com/optimizer/optimizer-transformations:-table-expansion"=
>https://blogs.oracle.com/optimizer/optimizer-transformations:-table-expans=
ion</a></div><div><br></div><div>Since you have 180 partitions, and since t=
he plan will (should) split into two distinct pieces, you can examine the S=
QL*Monitor report and see how well the indexed branch performs relative to =
the tablescan part, and may get some idea of how efficient the whole thing =
will be by the time you&#39;ve built the first 10 or 12 partitions.<br></di=
v><div><br></div><div>Regards</div><div>Jonathan Lewis</div><div><br></div>=
<div><br></div><div><br></div></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Tue, 2 Feb 2021 at 15:02, Lok P &lt;<a hre=
f=3D"mailto:loknath.73@gmail.com" target=3D"_blank">loknath.73@gmail.com</a=
>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px=
 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><di=
v dir=3D"ltr">Also Jonathan, apart from having the new index created(which =
will really need a lot=C2=A0of tests/agreement because this transaction tab=
le is 7TB in size and is exposed to heavy batch inserts happening each day =
24/7, ~250-300million rows each day) to help cater this query,<blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex"><div class=3D"gmail_quote"><blockquote=
 class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px so=
lid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><br></div>
</blockquote></div>
</blockquote></div></blockquote><div>=C2=A0</div></div>
</div>

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