Re: Performance issue on Oracle 9i

What can you tell us about the filters? Is the send_date is null predicate going to return a small number of rows? And is tx_ref not null? If so then create an index to support the is null and rewrite the query so that the not in (subquery) is replaced with analytics so you only have to read those rows once.

Hope that helps,
Andy

On Wed, 10 Jan 2024 at 23:20, Clay Jackson <dmarc-noreply_at_freelists.org> wrote:

> 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
>
> <https://www.quest.com/solutions/database-performance-monitoring/>
>
> 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
> )
> and t1.send_date is null
> /
>
>
>
>
>
> Thank you,
>
> --
>
> Sandy B.
>

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






(image/jpeg attachment: image001.jpg)