Takes long time with F4 key on package or with schema browser when user tries to view package body from other schema.

We recently made some database security changes. After that, when user presses F4 key on the package name after connecting to the database, it takes 30 minutes before the package spec/body is displayed. Note that this package is in a different schema. Same thing happens if they try to pull database package via Schema browser. This used to work before. I captured the SQL that it runs on the back end which is below. I ran this SQL from toad and it ran for 30 min. It works fine if I connect as my id which has more privileges. I want to figure out which particular privilege they are missing. TOAD version is 12.1.0.22

select o.*, NVL(d.debuginfo, ‘F’) DEBUGINFO, nvl(p.AUTHID, ‘DEFINER’) authid
from (
Select owner, object_name, object_type, decode(status, ‘VALID’, ‘V’, ‘I’) status, last_ddl_time, object_id, created
from sys.ALL_OBJECTS
where owner = :own
and object_type in (‘PACKAGE’, ‘PACKAGE BODY’)
and object_name = :obj
) o, sys.all_probe_objects d
,(SELECT object_id, AUTHID
FROM sys.ALL_PROCEDURES
where 1=1
AND subprogram_id = 0
AND object_type = ‘PACKAGE’
AND object_name = :obj
and owner = :own
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’)))
union all
Select distinct o.owner, s.name, ‘PACKAGE BODY’, ‘V’, null, null, null, null, null
from sys.ALL_SOURCE s, sys.all_objects o
where s.type = ‘PACKAGE BODY’
and s.owner = :own
and o.owner = s.owner
and o.object_name = s.name
and o.object_type = ‘PACKAGE’
and not exists (select ‘x’
from sys.all_objects o_sub
where o_sub.owner = s.owner
and o_sub.object_name = s.name
and o_sub.object_type = ‘PACKAGE BODY’)
order by 3

Thanks for any help!

Sam

That query can take several different forms depending on privileges and which schema (logon schema vs. another schema) you are querying against.

Those ALL_ views can be slow, and the do take oracle privileges into consideration, so it’s certainly a possibility that your recent security changes had something do to with it.

If you are looking in your own schema, Toad will use the USER_ views as much as possible, and those are a lot faster than ALL_.

If you grant select on DBA_SOURCE, DBA_OBJECTS, DBA_PROCEDURES to your user then you should see some improvement.

Taking it further, if you have select on SYS.IDL_CHAR$, SYS.PROCEDUREINFO$, SYS.USER$, and SYS._CURRENT_EDITION_OBJ (11g or newer) or SYS.OBJ$ (10gR2 or older), then the query will use these tables instead of the USER/ALL/DBA views and will be much faster.

Toad only checks for privileges on these views when you first make a connection, so be sure to disconnect and reconnect after making them. And make sure that Options->Startup->“Check for access to DBA Views” is checked.

Another thing you can do is go into the SB-Packages filter and check “Skip Check for Debug Info”. That will exclude the join to SYS.ALL_PROBE_OBJECTS (which I forgot to mention in my first reply, but there is no such view as DBA_PROBE_OBJECTS)

Hi John,

Appreciate real quick responses to my thread. Outstanding!!!

I am working on granting additional access. Also, I tried to run the SQL in part. ie. before UNION ALL and after. I found that the query before UNION ALL runs fast but the one after runs very slow. So, problem is with the second part of the query

Select distinct o.owner, s.name, ‘PACKAGE BODY’, ‘V’, null, null, null, null, null
from sys.ALL_SOURCE s, sys.all_objects o
where s.type = ‘PACKAGE BODY’
and s.owner = :own
and o.owner = s.owner
and o.object_name = s.name
and o.object_type = ‘PACKAGE’
and not exists (select ‘x’
from sys.all_objects o_sub
where o_sub.owner = s.owner
and o_sub.object_name = s.name
and o_sub.object_type = ‘PACKAGE BODY’)
order by 3

  • Sam

There’s nothing you can do in 12.1 to make that part of the query not run.

However, in our latest version 12.9, that query has been rewritten a bit. If this runs faster for you, you might consider an upgrade.

with PACKAGES as
(Select owner, object_name, object_type, decode(status, ‘VALID’, ‘V’, ‘I’) status, last_ddl_time, object_id, created
from sys.all_objects
where 1=1
and object_type in (‘PACKAGE’, ‘PACKAGE BODY’)
and owner = :own)
SELECT PACKAGES.owner, PACKAGES.object_name, PACKAGES.object_type, PACKAGES.status,
PACKAGES.last_ddl_time, PACKAGES.object_id, PACKAGES.created
,NVL(pi.AUTHID, ‘DEFINER’) AUTHID
,NVL(d.debuginfo, ‘F’) DEBUGINFO
FROM PACKAGES
,(SELECT object_id, authid
FROM sys.ALL_PROCEDURES
WHERE subprogram_id = 0
AND object_type = ‘PACKAGE’
and owner = :own
GROUP BY object_id, authid) pi
, sys.all_probe_objects d
WHERE PACKAGES.object_id = pi.object_id (+)
AND d.object_id (+) = PACKAGES.object_id
AND d.owner (+) = PACKAGES.owner
order by 3, 2

This query took 1 min. 40 sec. which is still slow but much better than 30 min.

Can you provide exact version of TOAD to upgrade to?

Thanks!

12.9 - to be released any day now (tomorrow, I think)

That query that I gave you was the one we use to load all of the packages into the Schema Browser. The one for describe dialogs includes a variable for object name, and it’s probably faster (below).

with PACKAGES as
(Select owner, object_name, object_type, decode(status, ‘VALID’, ‘V’, ‘I’) status, last_ddl_time, object_id, created
from sys.all_objects
where 1=1
and object_type in (‘PACKAGE’, ‘PACKAGE BODY’)
AND owner = :own
AND object_name = :obj)
SELECT PACKAGES.owner, PACKAGES.object_name, PACKAGES.object_type, PACKAGES.status,
PACKAGES.last_ddl_time, PACKAGES.object_id, PACKAGES.created
,NVL(pi.AUTHID, ‘DEFINER’) AUTHID
,NVL(d.debuginfo, ‘F’) DEBUGINFO
FROM PACKAGES
,(SELECT object_id, authid
FROM sys.ALL_PROCEDURES
WHERE subprogram_id = 0
AND object_type = ‘PACKAGE’
AND owner = :own
AND object_name = :obj
GROUP BY object_id, authid) pi
, sys.all_probe_objects d
WHERE PACKAGES.object_id = pi.object_id (+)
AND d.object_id (+) = PACKAGES.object_id
AND d.owner (+) = PACKAGES.owner
order by 3

Cool. That finished very fast 35msecs.

Thanks for your help.

~ Sam