# How to speed up schema browser using toad for oracle 12c database, including RAC **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [August 10, 2017, 7:04pm UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839 "2017-08-10T19:04:29Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![ty.cheng](https://avatars.discourse-cdn.com/v4/letter/t/bbce88/32.png) [@ty.cheng](https://forums.toadworld.com/u/ty.cheng)\ **Post date:** [August 10, 2017, 7:04pm UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/1 "2017-08-10T19:04:29Z")
I am having problem with slowness when using schema browsing in oracle 12c database and i just wondering have any one out there found a resolution to this issue. I have been put up with this issue for almost two years now. ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [August 11, 2017, 4:22am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/2 "2017-08-11T04:22:31Z")
Hi Cheng, Almost no one has problems with Schema browser. So please tell us Toad, Oracle client, Oracle server, Windows version and how do you affect performance slowness. Reason, out of the box may be: 1. Bad db statistic 2. Bad or slow connection 3. Huge amount of objects 4. slow or inappropriate workstation 5. Other (some incompatibility issues client-server, cToad version etc) Brg Damir ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [August 11, 2017, 5:17am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/3 "2017-08-11T05:17:14Z")
Also, the Schema Browser runs a lot of different queries. If all of them are slow, I don’t think the problem is the Schema Browser, but your database, or the network between your PC and the database. If one or two queries are slow, let me know which one it is. ---
**Author:** ![ty.cheng](https://avatars.discourse-cdn.com/v4/letter/t/bbce88/32.png) [@ty.cheng](https://forums.toadworld.com/u/ty.cheng)\ **Post date:** [August 14, 2017, 12:55am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/4 "2017-08-14T00:55:41Z")
Yes we are certainly experiencing slowness when we use toad for our oracle 12c database especially on schema browser and referential constraint. We are using toad 12.8, oracle client 11g, Oracle 12c database on RAC two nodes. We asked oracle and they could not give us any fix and I just wondering any one out there have this issue. ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [August 14, 2017, 1:49am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/5 "2017-08-14T01:49:32Z")
> We are using toad 12.8, oracle client 11g, Oracle 12c > please put x86 or x64 as well in decription. it is sometimes very important you are still missing windows version ? ---
**Author:** ![ty.cheng](https://avatars.discourse-cdn.com/v4/letter/t/bbce88/32.png) [@ty.cheng](https://forums.toadworld.com/u/ty.cheng)\ **Post date:** [August 14, 2017, 5:28am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/6 "2017-08-14T05:28:18Z")
We are using toad on Windows 7. ---
**Author:** ![ty.cheng](https://avatars.discourse-cdn.com/v4/letter/t/bbce88/32.png) [@ty.cheng](https://forums.toadworld.com/u/ty.cheng)\ **Post date:** [August 14, 2017, 5:29am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/7 "2017-08-14T05:29:20Z")
We are using oracle 12c on Solaris os on two nodes ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [August 14, 2017, 5:49am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/8 "2017-08-14T05:49:22Z")
Turn on Spool SQL. From the main menu: Database -\> Spool SQL -\> Spool to Screen. That will capture all of the queries that Toad runs, with timestamps on each, so we can get an idea of how long each query takes. So turn that on, then go to the Schema Browser and do whatever is slow, then post the spool SQL output here. ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [August 14, 2017, 6:06am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/9 "2017-08-14T06:06:08Z")
> Windows 7 > which version x86 or x64...the same question for all before. > do not know how more clear to state to describe your env. ---
**Author:** ![ty.cheng](https://avatars.discourse-cdn.com/v4/letter/t/bbce88/32.png) [@ty.cheng](https://forums.toadworld.com/u/ty.cheng)\ **Post date:** [August 14, 2017, 4:14pm UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/10 "2017-08-14T16:14:26Z")
We are using Windows 7 version x64 ---
**Author:** ![ty.cheng](https://avatars.discourse-cdn.com/v4/letter/t/bbce88/32.png) [@ty.cheng](https://forums.toadworld.com/u/ty.cheng)\ **Post date:** [August 15, 2017, 12:04am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/11 "2017-08-15T00:04:56Z")
Please find attached phase2dev.sql contains sql spool from the schema browser for display table columns and display FK constraints. ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [August 15, 2017, 12:32am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/12 "2017-08-15T00:32:29Z")
from log file, heavy queries: SESSION: PHASE2DEV@TRMPS\_DEV\_SVC.ADAM.SCAN **Timestamp: 16:22:07.144** Select CREATED, LAST\_DDL\_TIME, OBJECT\_ID, STATUS, TIMESTAMP FROM SYS.user\_objects WHERE object\_name = :n AND object\_type = :t n = ‘CLI\_CLIENTS’ t = ‘TABLE’ * * * CLICK A TABLE TO DISPLAY FK CONSTRAINTS * * * * * * SESSION: PHASE2DEV@TRMPS\_DEV\_SVC.ADAM.SCAN **Timestamp: 16:23:03.474** 1 minute for this one. all others are under few miliseconds. - So how many objects has your schema CLI\_CLIENTS has? - Can you show the execution plan for that query? - when was the last time you run fixed objects and sys stat? Please reply on this questions. ---
**Author:** ![ty.cheng](https://avatars.discourse-cdn.com/v4/letter/t/bbce88/32.png) [@ty.cheng](https://forums.toadworld.com/u/ty.cheng)\ **Post date:** [August 15, 2017, 12:40am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/13 "2017-08-15T00:40:26Z")
From my observation In RAC database TOAD use this SQL, which tent to be much slower _– 12C DATABASE_ Select c1.owner, c1.constraint\_name, c1.table\_name, c1.r\_owner, c1.r\_constraint\_name, c2. table\_name r\_table\_name, ``` c1.delete_rule, ``` c1.status, c1.DEFERRABLE, c1.DEFERRED, c1.validated, c2. constraint\_type r\_constraint\_type FROM sys. DBA\_CONSTRAINTS c1, sys. DBA\_CONSTRAINTS c2 WHERE c1.constraint\_type = ‘R’ AND c2.constraint\_type IN (‘P’, ‘U’) AND c2.owner = :town AND c2.table\_name = :tname AND c1.r\_owner = c2.owner AND c1.r\_constraint\_Name = c2.constraint\_name order by 1,3 In NON-RAC or 10G database TOAD use this SQL, which is much faster _— 10G DATABASE_ SELECT ou.NAME owner, oc.NAME constraint\_name, ``` o.NAME ``` table\_name, ru.NAME r\_owner, rc.NAME r\_constraint\_name, ``` rcobj.NAME ``` r\_table\_name, ``` DECODE(c.TYPE#, ``` 4, ``` DECODE(c.refact, ``` 1, ‘CASCADE’, 2, ‘SET NULL’, ‘NO ACTION’), ``` NULL) ``` delete\_rule, ``` DECODE(c.TYPE#, ``` 5, ‘ENABLED’, ``` DECODE(c.enabled, ``` NULL, ‘DISABLED’, ‘ENABLED’)) status, ``` DECODE(BITAND(c.defer, ``` 1), 1, ‘DEFERRABLE’, ‘NOT DEFERRABLE’) DEFERRABLE, ``` DECODE(BITAND(c.defer, ``` 2), 2, ‘DEFERRED’, ‘IMMEDIATE’) DEFERRED, ``` DECODE(BITAND(c.defer, ``` 4), 4, ‘VALIDATED’, ‘NOT VALIDATED’) validated, ``` DECODE(rcdef.TYPE#, ``` 2 ---
**Author:** ![ty.cheng](https://avatars.discourse-cdn.com/v4/letter/t/bbce88/32.png) [@ty.cheng](https://forums.toadworld.com/u/ty.cheng)\ **Post date:** [August 15, 2017, 12:59am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/14 "2017-08-15T00:59:43Z")
CLI\_CLIENTS is a table. PHASE2DEV is a schema which contain at least over 100 objects. Tomorrow I will double check when is the last time we run fx stats and sys stats. On 15 Aug 2017, at 5:04 PM, Damir Vadas [bounce-damirvadas\_250@toadworld.com](mailto:bounce-damirvadas_250@toadworld.com) wrote: > **[RE: How to speed up schema browser using toad for oracle 12c database, including RAC](http://www.toadworld.com/products/toad-for-oracle/f/10/p/34189/67121#67121)** > Reply by Damir Vadas > from log file, heavy queries: > SESSION: PHASE2DEV@TRMPS\_DEV\_SVC.ADAM.SCAN > > **Timestamp: 16:22:07.144** > > Select CREATED, LAST\_DDL\_TIME, OBJECT\_ID, STATUS, TIMESTAMP > > FROM SYS.user\_objects > > WHERE object\_name = :n > > AND object\_type = :t > > n = 'CLI\_CLIENTS' > > t = 'TABLE' > > * * * > > CLICK A TABLE TO DISPLAY FK CONSTRAINTS > > * * * > > * * * > > SESSION: PHASE2DEV@TRMPS\_DEV\_SVC.ADAM.SCAN > > **Timestamp: 16:23:03.474** > 1 minute for this one. > all others are under few miliseconds. > So how many objects has your schema CLI\_CLIENTS has? > when was the last time you run fixed objects and sys stat? > To reply, please reply-all to this email. > > [Stop receiving emails](http://www.toadworld.com/products/toad-for-oracle/f/10/t/34189?subscribe=false) on this subject. > > Or [ > Unsubscribe from Toad for Oracle Forum](http://www.toadworld.com/products/toad-for-oracle/f/10?subscribe=false) notifications altogether. > > Toad for Oracle - Discussion Forum > [Flag](http://www.toadworld.com/products/toad-for-oracle/f/10/p/34189/67121?AbuseContentId=95f4ec18-5c7c-44c3-8ecd-c0d430b6d8a1&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true) > this post as spam/abuse. ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [August 15, 2017, 1:02am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/15 "2017-08-15T01:02:29Z")
execution plan of problematic query=? it is hard to help you if you reply only on 50% of asked questions. ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [August 15, 2017, 1:06am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/16 "2017-08-15T01:06:40Z")
direct querying oracle core views is always faster than to query inherited generalized views. but DBA% views ensure more compatibility and they are not version dependent. on the other side, fixed tables views query on 10g works while oracle remove some columns or logic in 11g or 12c and query does not work anymore. and this is the answer on your why. 🙂 ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [August 15, 2017, 6:12am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/17 "2017-08-15T06:12:37Z")
Toad uses the 2nd query shown above if you have the SELECT Privilege on those sys tables involved. If you have the SELECT ANY DICTIONARY system privilege, then the only one you are missing the SELECT privilege on is SYS.USER$. Starting in 12c, Oracle removed that table from the SELECT ANY DICTIONARY system privilege, so try this, if your DBA will let you: Log in as SYS and run: GRANT SELECT ON SYS.USER$ TO PHASE2DEV; If you are already connected as PHASE2DEV in Toad, then disconnect and reconnect. Then try it again and Toad should start using the other query, which is much faster. ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [August 15, 2017, 7:59am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/18 "2017-08-15T07:59:34Z")
Hi John, poster started thread with performance problem in schema browser initial query. I have identified problematic query according log he sent. Then comes another question from poster with dba against $ views. this is where you replied as well and explain Toad logic, which is a nice things to know. Brg Damir ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [August 15, 2017, 8:09am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/19 "2017-08-15T08:09:16Z")
Hi Damir, I don’t think the query against user\_objects really took 1 minute to complete. I think he just paused there. Queries to USER\_OBJECTS are run from many places in Toad, so I think if that were the problem query, he would have experienced slowness before making it to the “Referential” tab in the Schema Browser. I think the problem all along was that he wasn’t happy with the performance of the query to DBA\_CONSTRAINTS. -John ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [August 15, 2017, 8:38am UTC](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839/20 "2017-08-15T08:38:00Z")
> think he just paused there. > You are magician as well-respect! This thread is really a mess. 🙂 [Next page](https://forums.toadworld.com/t/how-to-speed-up-schema-browser-using-toad-for-oracle-12c-database-including-rac/39839.md?page=2)