Toad World® Forums

Very slow performance 11.x for the Schema Browser filter


#1

Hi, I have recently upgraded from 10.6 to 11.5 and noticed the schema browser filter is very slow. After investigating, I believe the cause is that in 11.x and up, UPPER() is being used in the object_name for the query. I have both queries below and in version 11 you can see AND UPPER (object_name) LIKE :filt) o. Is there anyway to get around this?

version 10

/* Formatted on 6/26/2015 2:33:44 PM (QP5 v5.215.12089.38647) */

SELECT o.*,

NVL (d.debuginfo, ‘F’) DEBUGINFO,

NVL (p.AUTHID, ‘DEFINER’) authid

FROM (SELECT :own owner,

object_name,

object_type,

DECODE (status, ‘VALID’, ‘V’, ‘I’) status,

last_ddl_time,

object_id,

created

FROM sys.user_objects

WHERE 1 = 1

AND object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)

AND object_name LIKE :filt) o,

sys.all_probe_objects d,

( SELECT object_id, AUTHID

FROM sys.user_procedures

WHERE 1 = 1 AND subprogram_id = 0 AND object_type = ‘PACKAGE’

GROUP BY object_id, AUTHID) p

WHERE p.object_id(+) = o.object_id

AND o.OBJECT_ID = d.object_id(+)

AND d.owner(+) = :own

AND O.Object_Name = D.Object_Name(+)

AND ( (d.object_type IS NULL)

OR (d.object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)))

ORDER BY 3, 2

version 11

/* Formatted on 6/26/2015 3:29:13 PM (QP5 v5.215.12089.38647) */

SELECT o.*,

NVL (d.debuginfo, ‘F’) DEBUGINFO,

NVL (p.AUTHID, ‘DEFINER’) authid

FROM (SELECT :own owner,

object_name,

object_type,

DECODE (status, ‘VALID’, ‘V’, ‘I’) status,

last_ddl_time,

object_id,

created

FROM sys.user_objects

WHERE 1 = 1

AND object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)

AND UPPER (object_name) LIKE :filt) o,

sys.all_probe_objects d,

( SELECT object_id, AUTHID

FROM sys.USER_PROCEDURES

WHERE 1 = 1 AND subprogram_id = 0 AND object_type = ‘PACKAGE’

GROUP BY object_id, AUTHID) p

WHERE p.object_id(+) = o.object_id

AND o.OBJECT_ID = d.object_id(+)

AND d.owner(+) = :own

AND O.Object_Name = D.Object_Name(+)

AND ( (d.object_type IS NULL)

OR (d.object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)))

ORDER BY 3, 2


#2

Click the filter button (it looks like a funnel) and clear it.


#3

I have just done that, but it does nothing. The response is still very slow and in the native SQL, AND UPPER (object_name) LIKE :filt) o still exists.


#4

I just tested using Toad 11.0 (tell me if your version is different). The only way I can see

Upper(object_name) like :filt

in the query is if I have something specified for object name here:

To make sure it is really clear, try this:

You might also want to check (from the main menu) Session -> Schema Browser Filters. This is where you set the default filter. Then click on “Packages” in the dialog and see if something is set there.


#5

Ugh. Pictures didn’t show up. trying again as attachments.

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Tuesday, June 30, 2015 8:58 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Very slow performance 11.x for the Schema Browser filter

RE: Very slow performance 11.x for the Schema Browser filter

Reply by John Dorlon

I just tested using Toad 11.0 (tell me if your version is different). The only way I can see

Upper(object_name) like :filt

in the query is if I have something specified for object name here:

To make sure it is really clear, try this:

You might also want to check (from the main menu) Session -> Schema Browser Filters. This is where you set the default filter. Then click on “Packages” in the dialog and see if something
is set there.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.