Re: Unique index access path seems very slow

Yes Mark, that is kind of what I got to know from the requirements so far. And as I understood from this discussion so far, the encrypted raw value is 50% smaller in size and is the same irrespective of the client and is not going to change with the new oracle version etc. So we are in agreement with the dev team to change the column to RAW data type. However, considering that will be a design change and will take some time, we were trying to see if we can make the existing query run faster by any quick fix or code change etc. And as I posted the two sql monitors above, it appears to be the same query sometimes running for 30 minutes and sometimes ~10minutes, so trying to understand the cause and if we can make it consistent?

On Mon, Jan 30, 2023 at 10:29 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> So what you really need is to see if a new transaction row is NOT in the
> master? Is that correct?
>
>
>
> So, like an inner join of the keys into a temp (GTT or private one time
> temp) that you use as a minus on the transactions, yielding the list of new
> values, the unique list of which you then insert into the master BEFORE you
> process the transaction batch.
>
>
>
> Sigh. Do I understand that correctly?
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Pap
> *Sent:* Monday, January 30, 2023 12:32 AM
> *To:* yudhi s
> *Cc:* Jonathan Lewis; Oracle L
> *Subject:* Re: Unique index access path seems very slow
>
>
>
> Actually this encrypted table holds sha512 value of a sensitive column and
> kind of treated as a base/master table for all the possible values of that
> sensitive column and .The 5 minutes delta incoming transaction rows, (which
> would be ~2-3millions) has to be validated/outer joined against all of its
> values and send the results out, so i don't see a way to reduce the
> possible results set scanning of encrypted table.
>
>
>
> Its outer join because if any one odd transaction rows for which it
> doesn't get the encrypted value or get it as NULL , that will be treated as
> a new sensitive value and will be inserted into the master tab_encrypt
> table later on.
>
>
>
> On Mon, Jan 30, 2023 at 10:48 AM yudhi s <learnerdatabase99_at_gmail.com>
> wrote:
>
> Is there any possibility of additional filters you can put on the column
> of the encrypted - tab_encrypt table so as to restrict the joined record
> set over there?
>
>
>
> On Mon, Jan 30, 2023 at 10:33 AM Pap <oracle.developer35_at_gmail.com> wrote:
>
> Thank you Jonathan. We are finally able to push the appdev team to make
> the sha512 column data type changed from the Varchar2(which was storing the
> HEX value) to RAW to store the SHA512 without conversion,
> Considering different clients/drivers to oracle (like JDBC, ODBC, Native),
> etc will yield the same result on the RAW value and even version upgrade
> won't change these values for specific inputs.
>
>
>
> As a short term solution with existing ~3billion rows with same data
> types/data in the encrypted table which is getting joined with ~2-3milion
> rows from the transaction tables rows, is there any possible solution to
> make it better? And also the latest sql monitor reports says the execution
> is ~10minutes vs earlier ~30minutes and sometimes it goes till ~1hrs too,
> so we were unable to understand if it's just because of the timing i.e. say
> the IO subsystem slowness or anything else is playing a role say e.g bloom
> filter effectiveness varying time to time for different input sets so we
> should set the non bloom filtered plan like you suggested ?
>
>
>
>
>
>
>
>
>
>
>
> On Sat, Jan 28, 2023 at 12:34 AM Pap <oracle.developer35_at_gmail.com> wrote:
>
> Understood the hex and raw thing now. Thank you so much.
>
>
>
> On Fri, 27 Jan, 2023, 7:02 pm Jonathan Lewis, <jlewisoracle_at_gmail.com>
> wrote:
>
>
>
> Just one quick answer, I'll get back to the rest of the posting later
> today (I hope).
>
> For the storage of the raw vs. hex, the length() and lengthb() will
> implicitly convert to hex before calculating the length.
>
> For internal storage size try:
>
>
>
> SQL> select sys_op_opnsize(c1), sys_op_opnsize(c2) from t1_encrypt;
> SQL> select vsize(c1), vsize(c2) from t1_encrypt;
> SQL> select dump(c1,16), dump(c2,16) from t1_encrypt;
>
>
>
> The first two are the newer and older (respectively) calls used in
> gather_table_stats() to get internal column lengths
>
> The last is a way to dump the actual bytes store, in this case in base 16,
> reportung the column type and length at the start
>
>
>
> You'll see the 64 vs. 128 in all three.
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>

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