Object Explorer - getting Packages takes 5+ minutes to retrieve

Toad Data Point & Toad Intelligence Central
1

I've isolate with execution trace to the SQL statement issued by TOAD. Our All_Objects has ~114K rows.

SELECT 
     o.object_name
   , o.object_type
   , CASE WHEN (o.valid1 = 'false') THEN 'false' 
          WHEN (o.valid2 = 'false')  THEN 'false' 
          ELSE 'true' END valid
   , o.last_ddl_time
   , o.object_id
   , o.created
   , NVL((SELECT DECODE (NVL (c.debuginfo, 'F'), 'T', 'true', 'F', 'false') 
            FROM SYS.all_probe_objects c 
           WHERE c.object_id = o.object_id), 'false') debuginfo
   , (SELECT distinct NVL (p.AUTHID, 'CURRENT_USER') AUTHID 
        FROM SYS.all_procedures p 
       WHERE owner = :f1 
         and p.object_name(+) = o.object_name) AUTHID 
   , CASE WHEN (o.valid1 = 'false') THEN 'INVALID' 
          WHEN (o.valid2 = 'false')  THEN 'INVALID' 
          ELSE 'VALID' END  STATUS_1  
  FROM (SELECT 
                a.object_name
              , a.object_type
              , DECODE (NVL (a.status, 'VALID'), 'VALID', 'true', 'false') valid1
              , (SELECT DECODE (NVL (b.status, 'VALID'), 'VALID', 'true', 'false') 
                   FROM SYS.all_objects b 
                  WHERE b.owner = :f1 
                    and b.object_type(+) = 'PACKAGE BODY' 
                    AND a.owner = b.owner(+) 
                    AND a.object_name = b.object_name(+)) valid2
              , a.last_ddl_time
              , a.object_id
              , a.created
              , a.status   
          FROM SYS.all_objects a 
         WHERE a.owner = :f1 
           AND a.object_type = 'PACKAGE') o 
ORDER BY 2, 1;
2

If you or your DBA are up to tuning this SQL for your Oracle instance you can change the SQL in our SQL Dictionary. When you edit the dictionary it puts your edited sql in a file named SQLDictionary.xml in the AppdataDir. If it exists, it will use that SQL over the one compiled in the app. If you no longer want to use the edited SQL, delete the file.

sql%20Dictionary
sql Dictionary.PNG804×510 35.1 KB

3

Thanks Debbie. We will do this. With this work around, I believe we can make half a dozen data analysts here very happy.

addendum for those who may stumble upon. My rewrite of that SQL, executes for me in 5-6 seconds,

with pkgs
as (SELECT
a.object_name
, a.object_type
, DECODE (NVL (a.status, 'VALID'), 'VALID', 'true', 'false') valid1
, DECODE (NVL (b.status, 'VALID'), 'VALID', 'true', 'false') valid2
, a.last_ddl_time
, a.object_id
, a.created
, a.status
FROM SYS.dba_objects a
LEFT OUTER JOIN
SYS.dba_objects b
ON b.owner = :f1
AND b.object_type = 'PACKAGE BODY'
AND b.owner = a.owner
AND b.object_name = a.object_name
WHERE a.owner = :f1
AND a.object_type = 'PACKAGE'),
procs AS
(SELECT distinct
p1.AUTHID
, p1.OBJECT_NAME
FROM SYS.dba_procedures p1
WHERE p1.owner = :f1)
SELECT
o.object_name
, o.object_type
, CASE WHEN (o.valid1 = 'false') THEN 'false' WHEN (o.valid2 = 'false') THEN 'false' ELSE 'true' END valid
, o.last_ddl_time
, o.object_id
, o.created
, NVL((SELECT DECODE (NVL (c.debuginfo, 'F'), 'T', 'true', 'F', 'false')
FROM SYS.all_probe_objects c
WHERE c.OWNER= :f1
and c.object_id = o.object_id), 'false') debuginfo
, NVL (p.AUTHID, 'CURRENT_USER') AUTHID
, CASE WHEN (o.valid1 = 'false') THEN 'INVALID' WHEN (o.valid2 = 'false') THEN 'INVALID' ELSE 'VALID' END STATUS_1
FROM pkgs o
LEFT OUTER JOIN procs p
ON p.object_name = o.object_name
ORDER BY 2, 1