Slow performing query

From: Mick <mjmstud_at_gmail.com>
Date: Sun, 14 Nov 2010 19:12:53 -0800 (PST)
Message-ID: <635cdc87-fe91-45f6-b7c6-e5310e389b2a_at_42g2000prt.googlegroups.com>



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. Received on Sun Nov 14 2010 - 21:12:53 CST

Original text of this message