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@xxxxxxxxx>
wrote:
consider in case we have some backlog to catch up during system downthis test which we are doing for ~1hr worth of date range, is to
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.
