Re: Unique index access path seems very slow

Thank You Jonathan, Andy, Mark, Yudhi.

So it appears to be the first thing we should do is to change this column store type from hex to raw, so that the length of the column and also size of this index can be halved.

My Apology if i am putting same silly stuff again and again and unable to understand it. But as Jonathan mentioned , it appears to be its just the toad client UI which is making the SHA512 value shown as HEX, but when i tried to do insert as below so as to bypass the client representation, I am still getting the length of the SHA512 converted string as 128 bytes. What exactly i am missing here?

drop table t1_encrypt

create table t1_encrypt (c1 raw(2000), c2 varchar2(4000))

insert into t1_encrypt
select (STANDARD_HASH ( TO_CHAR ( TRIM ('1234')),'SHA512')) , (STANDARD_HASH ( TO_CHAR ( TRIM ('1234')),'SHA512'))  from dual;

insert into t1_encrypt
select RAWTOHEX((STANDARD_HASH ( TO_CHAR ( TRIM ('1234')),'SHA512'))) , RAWTOHEX((STANDARD_HASH ( TO_CHAR ( TRIM ('1234')),'SHA512'))) from dual ;

select length(c1), lengthb(c1),length(c2), lengthb(c2) from t1_encrypt;

It gives 128bytes as the results in all above cases.

When Jonathan said,
"*If you compare operations 6 and 7 of the two plans in the latest git dump and the same two lines from the (3 times slower) plan you showed initially for the hash join you should notice some dramatic differences. Unfortunately you have omitted lots of bits of information from the newer git dumps that you showed in the original, so it's impossible to say for certain what the "expected" impact of those difference might be."*

It initially looked to me ,like the key difference is the bloom filter effectiveness, in the first one (which ran for ~30minutes+) at step no-6 the number of actual rows were still around 2Billion whereas in the latest sql monitor of same FTS+bloom filter path which finished in ~10minutes the actual rows resulted from step-6 becomes 99Million.

Then i saw there is a big difference in estimation of the TRAN_TAB , so even there is no difference in plan and it may be foolish of me, i tried to run the query with cardinality hints for the inline query, one with cardinality 2million and other as 49537 for the tran_tab.These were also running >30minutes.

But again kept try rerunning the query multiple times but saw the runtime exceeds ~30minute duration for same query. And then suddenly for one of the execution i saw it got finished in ~10minute. I have captured the sql monitor and non zero stats from v$sesstat for first 10minutes. Below is the one. So wondering , if its just the IO subsystem response during that time which helped the query to finish in ~10minutes vs ~30minutes+ during other times or anything else playing a role?

********With cardinality hint for the inline view (cardinality(TRAN_TAB

2000000))*****************

https://gist.github.com/oracle9999/01304c1301c7f4c9f712b33d3b9e01a2

********With cardinality hint for the inline view (cardinality(TRAN_TAB

49537))*****************

https://gist.github.com/oracle9999/1eb36966bc6f3a73e16c54598efd50a1

On Thu, 26 Jan, 2023, 1:27 am Jonathan Lewis, <jlewisoracle_at_gmail.com> wrote:

>
> >>> this test which we are doing for ~1hr worth of date range, is to
> consider in case we have some backlog to catch up during system down
> time etc.
>
> Just as an indication of thinking carefully about what you need to test:
>
> Your latest run for one hour's worth of data was about catching up in the
> event of a backlog, and that completed in 11 minutes, which means you've
> won back 49 minutes, and now have to start the next run immediately to
> catch up 11 minutes of data, if if that takes less than 5 minutes your next
> run (you said every 5 minutes) is back on schedule. So why are you thinking
> you need a 2 or 3 minute completion time for 1 hours worth of data?
>
> Realistically what's the worst case backlog you want to deal, and how fast
> do you want it cleared?
> Say you have an 8 hour backlog and you can clear one hour of data in 15
> minutes, what does that do for you? Winning back 45 minutes every 15
> minutes means that after 8 runs you're down to a 2 hour backlog; after
> another 2 runs you're down to 30 minutes backlog, and after one more run
> you're on schedule. So you've caught up an 8 hour backlog in less than 3
> hours.
>
>
>
>
>
>
>
>
>

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