Re: Performance issue on Oracle 9i

Hi ,

In 9i Runtime statistics (v$sql_plan_statistics) were already present. It would be very helpfull to see them, would answer Andies questions. It is possible to display plan Statistics along with the execution plan. The method is explained in Wolfang's paper "What is new in the Oracle 9i CBO "
open http://centrexcc.com/ and hit paper on the left menu.

Thanks

Lothar
**

Am 11.01.2024 um 00:40 schrieb Andy Sayer:
> 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 <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
>         )
>   and t1.send_date is null
> /
>
> Thank you,
>
> --
>
> Sandy B.
>

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