RE: Performance issue on Oracle 9i

Disclaimer – I work for Quest Software and don’t claim to be anywhere near as good as tools like SQL Optimizer, but the first thing I’d look for is an index on exp_cmnt.

Can you post an explain plan? I’ve certainly forgotten how the old optimizer behaved, and there might be some clues there.

Clay Jackson
Database Solutions Sales Engineer
[cid:image001.jpg_at_01DA43D8.5FC7D6A0]<https://www.quest.com/solutions/database-performance-monitoring/> clay.jackson_at_quest.com<mailto:clay.jackson_at_quest.com> office 949-754-1203 mobile 425-802-9603

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Sandra Becker Sent: Wednesday, January 10, 2024 2:57 PM To: oracle-l <oracle-l_at_freelists.org> Subject: Performance issue on Oracle 9i

CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.

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.

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






(image/jpeg attachment: image001.jpg)