Re: Slow performing query

From: Randolf Geist <mahrah_at_web.de>
Date: Mon, 15 Nov 2010 03:37:01 -0800 (PST)
Message-ID: <436c120b-23ac-451d-a197-8070e588aaeb_at_r14g2000yqa.googlegroups.com>

On Nov 14, 10:12 pm, Mick <mjms..._at_gmail.com> wrote:
> I have a query which is running very poorly (Oracle Database 10g
> Release 10.2.0.1.0 ):
>
> It looks like this part of the query is what is causing the
> bottleneck :
>
> The other area where there is a potential problem is :
> UPPER(objecttype.name) LIKE UPPER('%coghlan%')
> Unfortunately I have not control over this wildcard specification
> which is also causing a
> table scan because this is what is entered by the users in a third
> party application.
> There are 265002 records in the PHY_ALL_OBJECTS table
>
> I believe I have done everything possible from an indexing perspective
> and that it would
> appear that the table scans that are occurring are unavoidable
> and I am open to other suggestions as to what I can do to
> improve the performance of this query.

No need to guess, since you're on 10g (although it is a bad idea to run a unpatched 10.2.0.1) you can get cardinality feedback out of the box using DBMS_XPLAN.DISPLAY_CURSOR and STATISTICS_LEVEL = ALL (or at least using the GATHER_PLAN_STATISTICS hint).

You might want to follow these instructions that I've written a long time ago to gather basic information about a single SQL statement performance:

http://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Mon Nov 15 2010 - 05:37:01 CST