How to speed up schema browser using toad for oracle 12c database, including RAC

Toad for Oracle & DevOps Toolkit
21

Thanks for your help so far.

The GRANT SELECT ON SYS.USER$ TO PHASE2DEV did not make any different, the GRANT DBA TO PHASE2DEV did make the different.

DBA grant force TOAD to use (sys. con$ oc,
sys. con$ rc,
sys. USER$ ou,
sys. USER$ ru, sys. obj$ o,
sys. cdef$ c,
sys. cdef$ rcdef,
sys. obj$ rcobj)

See file attached.

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Tuesday, 15 August 2017 10:44 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] How to speed up schema browser using toad for oracle 12c database, including RAC

RE: How to speed up schema browser using toad for oracle 12c database, including RAC

Reply by John Dorlon

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.

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.

22

i have concluded we have resolved my toad schema browser issue and all our developers now are much happier. Thanks

23

it is nice that you can just like that grant dba privilege to your developers.
:slight_smile:

24

could i add more questions to this question, although it resolved the schema browser issue but I still want to do further tuning to speed up the connection in RAC. Please find attached spreadsheet contain sql statements when toad try to make connection with 12c RAC and 10g RedHat.

25

File attached:
connection in 12c RAC and RedHat.xlsx (18.5 KB)

26

Could you have another question I just added to this topic. It is falling in the same topic ….

From: Damir Vadas [mailto:bounce-damirvadas_250@toadworld.com]

Sent: Wednesday, 16 August 2017 4:41 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] How to speed up schema browser using toad for oracle 12c database, including RAC

RE: How to speed up schema browser using toad for oracle 12c database, including RAC

Reply by Damir Vadas

it is nice that you can just like that grant dba privilege to your developers.

:slight_smile:

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.

27

I don’t see much of a problem here. The RAC spool sql is about 3 seconds. The non-rac is about 2.

Am I missing something?

28

If you compare these you will see the RAC take too much time (RAC=2.330 sec and RH=475.9msec) and I just wondering why, is there more privilege I can grant to speed this up.

Timestamp: 16:21:28.937

Timestamp: 16:23:27.050

SELECT u.NAME FROM sys.USER$ u WHERE u.TYPE# = 1 ORDER BY 1

SELECT u.NAME FROM sys.USER$ u WHERE u.TYPE# = 1 ORDER BY 1

SESSION: CHENGTY@TIMSACPT

SESSION: CHENGTY@TIMSACPT.WORLD

Timestamp: 16:21:30.268

Timestamp: 16:23:27.476

I am trying to resove other performance issue which is out of this scope, something to do with ArcCatalog software and 12c RAC database. The RAC is taking 1 minute and 6.93 msec on the RedHat to connect

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Friday, 25 August 2017 11:51 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] How to speed up schema browser using toad for oracle 12c database, including RAC

RE: How to speed up schema browser using toad for oracle 12c database, including RAC

Reply by John Dorlon

I don’t see much of a problem here. The RAC spool sql is about 3 seconds. The non-rac is about 2.

Am I missing something?

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.

29

I also notice this the error produce in the toad spool screen, why toad produce these error???:

SESSION: CHENGTY@TIMSACPT

Timestamp: 16:21:28.865

BEGIN

if ((QU_VERSION.major_number >= ‘1’) and (QU_VERSION.minor_number >= ‘8’)) then

:retval := 1;

else

:retval := 0;

end if;

END;

retval =

Runtime error occurred: 6550 (ORA-06550: line 3, column 8:

PLS-00201: identifier ‘QU_VERSION.MAJOR_NUMBER’ must be declared

ORA-06550: line 3, column 3:

PL/SQL: Statement ignored)

SESSION: CHENGTY@TIMSACPT

Timestamp: 16:21:28.919

BEGIN IF DBMS_UTILITY.IS_CLUSTER_DATABASE THEN :RetVal := 1; ELSE :RetVal := 0; END IF; END;

RetVal = 1

SESSION: CHENGTY@TIMSACPT

Timestamp: 16:21:28.924

SESSION: CHENGTY@TIMSACPT

Timestamp: 16:21:30.275

Select CREATED, LAST_DDL_TIME, OBJECT_ID, STATUS, TIMESTAMP

FROM SYS.DBA_OBJECTS

WHERE object_name = :n

AND object_type = :t

AND owner = :o

n = ‘SQLNAV_PROGRAM_STATUS_LOG’

t = ‘TABLE’

o = ‘SQLNAV’

SESSION: CHENGTY@TIMSACPT

Timestamp: 16:21:30.520

SELECT ppr.runid, ppr.run_proc, ppr.run_date, ppr.run_comment, ppr.run_owner, SUM(ppu.total_time) as run_total_time

FROM plsql_profiler_runs ppr, plsql_profiler_units ppu

WHERE ppu.runid = ppr.runid

GROUP BY ppr.runid, ppr.run_proc, ppr.run_date, ppr.run_comment, ppr.run_owner

Runtime error occurred: 942 ( ORA-00942: table or view does not exist)

From: Cheng, Ty (DPTI)

Sent: Monday, 28 August 2017 10:02 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] How to speed up schema browser using toad for oracle 12c database, including RAC

If you compare these you will see the RAC take too much time (RAC=2.330 sec and RH=475.9msec) and I just wondering why, is there more privilege I can grant to speed this up.

Timestamp: 16:21:28.937

Timestamp: 16:23:27.050

SELECT u.NAME FROM sys.USER$ u WHERE u.TYPE# = 1 ORDER BY 1

SELECT u.NAME FROM sys.USER$ u WHERE u.TYPE# = 1 ORDER BY 1

SESSION: CHENGTY@TIMSACPT

SESSION: CHENGTY@TIMSACPT.WORLD

Timestamp: 16:21:30.268

Timestamp: 16:23:27.476

I am trying to resove other performance issue which is out of this scope, something to do with ArcCatalog software and 12c RAC database. The RAC is taking 1 minute and 6.93 msec on the RedHat to connect

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Friday, 25 August 2017 11:51 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] How to speed up schema browser using toad for oracle 12c database, including RAC

RE: How to speed up schema browser using toad for oracle 12c database, including RAC

Reply by John Dorlon

I don’t see much of a problem here. The RAC spool sql is about 3 seconds. The non-rac is about 2.

Am I missing something?

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

30

the ORA-06550 error sounds like you have a missing/corrupt installation of Quest Code Tester in the DB. It has found the repository but it can’t find expected items in the package. For the ORA-00942 error, it looks like you have an issue with something installed by SQL Navigator.

31

Actually that’s only 1.3 seconds. But you already have the highest privileges for this query. If there are lots of users on this database that do not own objects, you could try right-clicking on the username dropdown in the schema browser and then set “Users to Load” to “Load only users that own objects”, but that may actually slow things down. It’s just something to try.

But the most you have to gain on this query is only about 1 second. What part of the connection time is taking up most of that 1 minute? The entire spool SQL you sent was only a few seconds total.

32

The ORA-06550 and ORA-00942s are not an indication of a problem. We are just checking to see if certain things are installed and they are not. It’s OK.

33

Further discovery in toad for 12c RAC database when I try to generate script of a table in toad, for 12c RAC it took 3.4 second and it only take 52 msec in the redHat. Why is that?

But when I cut and paste (select name from sys.user_snapshots where 1=1) into toad editor and run in 12c RAC and 10g RedHat, they both take less than a second. It is really weird. Can you tell me what is toad doing at the background?

=============================================

12c RAC session, is taking approximately 3.4 seconds

=============================================

SESSION: SDE@TIMSACPT

Timestamp: 09:45:41.456

Select name

from sys.user_snapshots

where 1=1

SESSION: SDE@TIMSACPT

Timestamp: 09:45:44.948

============================================

10g RedHat session, is taking approximately 52 msec

=============================================

SESSION: SDE@TIMSACPT.WORLD

Timestamp: 09:55:20.440

Select name

from sys.user_snapshots

where 1=1

SESSION: SDE@TIMSACPT.WORLD

Timestamp: 09:55:20.492

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Monday, 28 August 2017 10:22 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] How to speed up schema browser using toad for oracle 12c database, including RAC

RE: How to speed up schema browser using toad for oracle 12c database, including RAC

Reply by John Dorlon

The ORA-06550 and ORA-00942s are not an indication of a problem. We are just checking to see if certain things are installed and they are not. It’s OK.

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.

34

The first time you run a query (or the first time you run it in a while), it needs to be parsed by Oracle, and blocks need to be read into memory.

If you run it again right away, it sometimes runs much faster because the parse information and some of the data blocks are already in memory.

So that may explain why it ran faster in the Editor than during script generation.

The only processing that Toad is doing after running that query is loading the results into a list in memory. It should happen very quickly unless there are lots of results.

35

Thanks for your explanation. From my observation the oracle 10g non-RAC on red hat OS always run fast first time round. The issue now is only toad in oracle 12c RAC is the issue and I am still not clear is it toad issue or oracle 12c issue.

On 30 Aug 2017, at 10:21 PM, John Dorlon bounce-jdorlon@toadworld.com wrote:

RE: How to speed up schema browser using toad for oracle 12c database, including RAC

Reply by John Dorlon
The first time you run a query (or the first time you run it in a while), it needs to be parsed by Oracle, and blocks need to be read into memory.

If you run it again right away, it sometimes runs much faster because the parse information and some of the data blocks are already in memory.

So that may explain why it ran faster in the Editor than during script generation.

The only processing that Toad is doing after running that query is loading the results into a list in memory. It should happen very quickly unless there are lots of results.

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.

36

Also take a look at this piece of code that is generating from toad schema browser when i clicked on create script for a table. This is taking a long time in 12c RAC ( 18821ms) but I don’t see any thing like this in the 10g red Hat environment.

SESSION: SDE@TIMSACPT
Timestamp: 09:37:11.634 42ms
declare
type rslt_tbl is table of varchar2(98) index by binary_integer;
rslt1 rslt_tbl;
rslt_size number;
i number;
cursor cs is
Select c.constraint_name, C.TABLE_NAME, cc.column_name, C.SEARCH_CONDITION
from SYS.DBA_CONSTRAINTS c, SYS.DBA_CONS_COLUMNS cc
where c.OWNER = cc.OWNER
and c.table_name = cc.TABLE_NAME
and c.CONSTRAINT_NAME = cc.constraint_name
and c.constraint_type in (‘C’, ‘?’)
and cc.column_name NOT LIKE ‘SYS_NC%’
and C.TABLE_NAME = :OneObjectName
and c.owner = :own
and exists (select owner, table_name, constraint_name
from DBA_CONS_COLUMNS cc2
where cc2.owner = c.owner
and cc2.constraint_name = c.constraint_name
and cc2.table_name = c.table_name
and cc2.column_name NOT LIKE ‘SYS_NC%’
group by owner, table_name, constraint_name
having count(*) = 1);
cs_var cs%rowtype;
procedure addit(in_str varchar2) is
begin
rslt_size := rslt_size + 1;
rslt1(rslt_size) := in_str;
end;
begin
rslt_size := 0;
for cs_var in cs loop
if (cs_var.search_condition = cs_var.column_name || ’ IS NOT NULL’) or (cs_var.search_condition = ‘"’ || cs_var.column_name || ‘" IS NOT NULL’) then
addit(cs_var.constraint_name || ‘.TN=’ || cs_var.table_name || ‘.CN=’ || cs_var.column_name);
end if;
end loop;
:outTbl1 := rslt1;
:outSize := rslt_size;
end;
OneObjectName = ‘COLUMN_REGISTRY’
own = ‘SDE’
outTbl1 = ‘SYS_C0013470.TN=COLUMN_REGISTRY.CN=TABLE_NAME’
outSize = 5

SESSION: SDE@TIMSACPT
Timestamp: 09:37:30.474 18821ms

37

Certain queries are run only in certain conditions. The pl/sql block above runs when we need to distinguish stand alone NOT NULL constraints from those automatically created when a primary key constraint is created.

38

John Dorlon wrote the following post at 14 Aug 2017 12:49 PM:

Turn on Spool SQL. From the main menu: Database -> Spool SQL -> Spool to Screen.

===========================

Hi John,

If one chooses Spool to File, what is the resulting filename and path?

Thank You

39

If you use spool to file, the file is debug.sql, in your user files folder. The easiest way to find your user files folder is to go to Options -> General. Then click the “Open Folder” button under “Application Data Directory”

40

Found it.

Thank You.