I have a query which is running very poorly (Oracle Database 10g
Release 10.2.0.1.0 ):

SELECT
objecttype.id_object
FROM PHY_ALL_OBJECTS objecttype
WHERE
objecttype.id_type_definition = 'duotA50'
AND objecttype.date_delete is null
AND
(
id_parent not in
(
SELECT id_object from folders f START WITH f.id_object = 'fA464'
CONNECT BY prior f.id_object = f.id_parent
)
)
AND UPPER(objecttype.name) LIKE UPPER('%coghlan%')
;

The execution plan is as follows :




--------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU)


--------------------------------------------------------------------------------
0 SELECT STATEMENT 13162 1529K 2007 (1)
* 1 HASH JOIN RIGHT ANTI 13162 1529K 2007 (1)
2 VIEW VW_NSO_1 31 310 3 (0)
* 3 CONNECT BY WITH FILTERING
4 TABLE ACCESS BY INDEX ROWID FOLDERS
* 5 INDEX RANGE SCAN INDEX_FOLDERS_ID_OBJECT_PARENT 1 9 3 (0)
6 NESTED LOOPS
7 BUFFER SORT
8 CONNECT BY PUMP
* 9 INDEX RANGE SCAN INDEX_FOLDERS_ID_PARENT 31 52 7 3 (0)
* 10 TABLE ACCESS FULL FOLDERS 31 527 3 (0)
* 11 TABLE ACCESS FULL PHY_ALL_OBJECTS 13250 1410K 2003 (1)


--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

--------------------------------------------------------------------------------

1 - access("ID_PARENT"="$nso_col_1")
3 - filter("F"."ID_OBJECT"='fA464')
5 - access("F"."ID_OBJECT"='fA464')
9 - access("F"."ID_PARENT"=NULL)
10 - access("F"."ID_PARENT"=NULL)
11 - filter(UPPER("OBJECTTYPE"."NAME") LIKE '%COGHLAN%' AND
"OBJECTTYPE"."DATE_DELETE" IS
NULL AND "OBJECTTYPE"."ID_TYPE_DEFINITION"='duotA50')

I have indexes on the following fields :

TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
PHY_ALL_OBJECTS PHY_ALL_OBJECTS_IDX2 1 ID_TYPE_DEFINITION
PHY_ALL_OBJECTS PHY_ALL_OBJECTS_IDX2 2 TYPE_OBJECT

PHY_ALL_OBJECTS PHY_ALL_OBJECTS_IND_BW 1 DATE_DELETE
PHY_ALL_OBJECTS PHY_ALL_OBJECTS_IND_BW 2 TYPE_OBJECT
PHY_ALL_OBJECTS PHY_ALL_OBJECTS_IND_BW 3 SYS_NC00039$

PHY_ALL_OBJECTS PHY_ALL_OBJECTS_IDX3 1 UPPER(NAME)

FOLDERS INDEX_FOLDERS_ID_OBJECT_PARENT 1 ID_OBJECT
FOLDERS INDEX_FOLDERS_ID_OBJECT_PARENT 2 ID_PARENT

FOLDERS INDEX_FOLDERS_ID_PARENT 1 ID_PARENT
FOLDERS INDEX_FOLDERS_ID_PARENT 2 ID_OBJECT

It looks like this part of the query is what is causing the
bottleneck :

SELECT id_object from folders f START WITH f.id_object = 'fA464'
CONNECT BY prior f.id_object = f.id_parent;
There are 532715 records returned when I run this part of the query in
isolation.

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.

The other thing I have noticed is that query performs reasonably
(i.e. less than 7 seconds after the instance is bounced)
but it tends to degrade after a few days.
I would be interested in why this would be the case as well.

Thank you in advance to anyone who can shine some light on this
intriguing problem.

On Nov 14, 10:12*pm, Mick <mjms... (AT) gmail (DOT) com> wrote:
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/2...rformance.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-.../dp/1430226684