# Object Explorer - getting Packages takes 5+ minutes to retrieve

**URL:** <https://forums.toadworld.com/t/object-explorer-getting-packages-takes-5-minutes-to-retrieve/46947>\
**Category:** Toad Data Point & Toad Intelligence Central\
**Created:** [July 2, 2019, 4:16pm UTC](https://forums.toadworld.com/t/object-explorer-getting-packages-takes-5-minutes-to-retrieve/46947 "2019-07-02T16:16:15Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![reerickson](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/reerickson/32/11252_2.png) [@reerickson](https://forums.toadworld.com/u/reerickson)\
**Post date:** [July 2, 2019, 4:16pm UTC](https://forums.toadworld.com/t/object-explorer-getting-packages-takes-5-minutes-to-retrieve/46947/1 "2019-07-02T16:16:16Z")

</div>

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;
```

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [July 3, 2019, 3:12am UTC](https://forums.toadworld.com/t/object-explorer-getting-packages-takes-5-minutes-to-retrieve/46947/2 "2019-07-03T03:12:36Z")

</div>

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](https://global.discourse-cdn.com/quest/original/2X/3/3b563d372cf312945753a1bcf0c0a9171c1c748f.png)

---

<div class="post-metadata">

**Author:** ![reerickson](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/reerickson/32/11252_2.png) [@reerickson](https://forums.toadworld.com/u/reerickson)\
**Post date:** [July 8, 2019, 5:05pm UTC](https://forums.toadworld.com/t/object-explorer-getting-packages-takes-5-minutes-to-retrieve/46947/3 "2019-07-08T17:05:10Z")

</div>

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