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.
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:
- Bad db statistic
- Bad or slow connection
- Huge amount of objects
- slow or inappropriate workstation
- Other (some incompatibility issues client-server, cToad version etc)
Brg
Damir
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.
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.
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 ?
We are using toad on Windows 7.
We are using oracle 12c on Solaris os on two nodes
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.
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.
We are using Windows 7 version x64
Please find attached phase2dev.sql contains sql spool from the schema browser for display table columns and display FK constraints.
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.
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
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 wrote:
RE: How to speed up schema browser using toad for oracle 12c database, including RAC
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 on this subject.
Or
Unsubscribe from Toad for Oracle Forum notifications altogether.Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
execution plan of problematic query=?
it is hard to help you if you reply only on 50% of asked questions.
1 Like
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.
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.
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
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
think he just paused there.
You are magician as well-respect!
This thread is really a mess.
