RE: Performance issue on Oracle 9i

select *

from aps1.txn_proc t1

where t1.tx_ref not in

        (select t.tx_ref

         from aps1.txn_proc t

         where t.send_date is null

           and t.exp_cmnt not like 'PR%'

           and t.exp_cmnt != projno||tno

        )

  and t1.send_date is null

/

Two queries:

select count(*) from t1 where t1.send_date is null;

select count(*) from t1 where t1.send_date is not null;

would be useful metrics.  

Using null send_date smacks of an overloaded date and action code.  

IF that is not correct the rest of my note is only coincidentally useful (if useful at all).  

IF that is true, probably what you want to do is add a column do_not_send_yet. (do_not_send_yet should be a bit if Oracle had single bit variables, I think small numbers is still the smallest storage we can take up.)  

So make it a number, nullable, on insert value 1, with all the current rows set to 1 if send_date is null and null otherwise.

Index it.

Whenever and wherever send_date is populated, punch do_not_send_yet to null (meaning okay to send You can define a meaning for null in a particular column whilst Oracle cannot and this is precisely in order to create a “sparse index” which has worked since at least Oracle 5. If you punch the value to 0 instead of null, it will be needlessly large.)  

select *

from aps1.txn_proc t1

where t1.tx_ref not in

        (select t.tx_ref

         from aps1.txn_proc t

         where t. do_not_send_yet = 1

           and t.exp_cmnt not like 'PR%'

           and t.exp_cmnt != projno||tno

        )

  and t1. do_not_send_yet =1

/
 

Whether or not this prunes enough to ignore JL’s idea and figuring out how to do that with dynamic values is a question of data.  

As a “something to beat” easy implementation of JL’s idea for dynamic values (so you don’t have PS in hand for the passed in PR),  

Make the first pruning (do_not_send_yet=1) an inline view. (whether and t.exp_cmnt != projno||tno is better in the first pruning inline view or the second is a question of data, I’ll pretend that IS in the inline view, because I have to type less that way).  

From the inline view, select

where t.exp_cmnt < ‘PR’

or (t.exp_cmnt >= 'PR' and t.exp_cmnt not like 'PR%')  

Now if you can’t add a column (or can add the column but can’t manage punching it to null or even 0 when the send_date is set, you CAN do something similar by making send_date “high values” on insert and setting all the existing send_date values that are null to “high_values.” So that MEANS don’t send it yet.  

Then your index would be send_date, exp_cmnt

and the query  

select *

from aps1.txn_proc t1

where t1.tx_ref not in

        (select t.tx_ref

         from aps1.txn_proc t

         where t.send_date = high_values

           and ( t.exp_cmnt < ‘PR’

                    or  (t.exp_cmnt >= 'PR' and t.exp_cmnt not like 'PR%')

                   )

           and t.exp_cmnt != projno||tno

        )

  and t1.send_date = high_values

/

(using whatever literal is high enough for high_values. If memory serves Oracle does have a highest possible date value that takes no more space than any other not null date. [which is quite different for default large number high values.] That should not require any maintenance code change when send_date is set. You might have other code that depends on send_date being null. That would be a potential problem.  

Good luck. I *think* all of that is right but I didn’t even start a database today, let alone 9i.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sandra Becker Sent: Thursday, January 11, 2024 8:55 AM To: jlewisoracle_at_gmail.com
Cc: oracle-l
Subject: Re: Performance issue on Oracle 9i  

Unfortunately, company policy prohibits me from providing the sql plan. I can tell you that pre-index the query was doing full table scans for both the query and subquery. Post-index build, it was using the index for both. I can create new indexes and/or rewrite the query. The sub-query returns 29k rows on average. The t.exp_cmnt not like replacement since it can be a combination of any two letters of the alphabet. I can play with that and see what I can come up with, possibly combining with the date column?  

Thanks for your assistance.  

Sandy    

On Thu, Jan 11, 2024 at 6:05 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:  

Are you allowed to rewrite the query or do you have to live with it and tweak the database.

How long does it take to run (pre- and post- index creation), and what did the two execution plans look like.

Was the index on Just the send_date, or was it on (send_date, some non-null declared column). If the former then it shouldn't have made any difference to the plan (in the general case - but there are some "exotic" anomalies) which would suggest that the improvement was from side effects (like better statistics, or the effects of blocks being cleaned out during the index build).  

If you can rewrite the query there are several possibilities, but the best strategy depends on know the effects of each of the data predicates and combinations of predicates. For example, how many rows have a null send_date, how many rows have exp_cmnt != projno||tno. (It would be better to include the "t." table alias in that predicate - even though it should make no difference in this case (unless you've been getting the wrong results because it was supposed to be a predicate correlating to "t1.".))  

Example step - if rewrites are allowed and necessary:

    t.exp_cmnt not like 'PR%'

is equivalent to

    t.exp_cmnt >= 'PS' or t.exp_cmnt < 'PR'  

(Note: greater than or equal to, strictly less than)

This means the subquery could be rewritten as a UNION ALL of two query blocks that could operate through an index.      

Regards

Jonathan Lewis    

On Wed, 10 Jan 2024 at 22:58, Sandra Becker <sbecker6925_at_gmail.com> wrote:

OS: SunOS 5.8

DB: Oracle 9.2.0.5  

We're sitting on really old hardware with a really old version of Oracle. There is a project to migrate to another application, but they estimate it will take another 18 months given the human resources that are available. That being said, this is a production financial application and is performing extremely poorly for queries against a specific table. It's not a huge table, 388,000 rows, but given the age of the hardware/software, I'm surprised we don't have more issues.  

The query itself is poorly written, but I haven't figured out how to make it more efficient. I did manage to reduce the cost and execution time of the query by 50% by creating an index on the SEND_TO_DATE column--which can contain nulls--but it's still very slow. I also set the degree on the table to 8, which gave us a minor bump in performance. Any suggestions would be appreciated, specifically on how I can change the "not like" and "!=" predicates.  

select *
from aps1.txn_proc t1
where t1.tx_ref not in

        (select t.tx_ref
         from aps1.txn_proc t
         where t.send_date is null
           and t.exp_cmnt not like 'PR%'
           and t.exp_cmnt != projno||tno
        )

Thank you,

-- 

Sandy B.




 

-- 

Sandy B.





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