Describes very slow to respond

I upgraded to a new Dell 64bit laptop running Windows 7 with 8gb of RAM. I installed Toad 12.1.0.22 with the Oracle 12 client.

Any time I do a desc on an object in any oracle database, the window appears in a few seconds but the data can sometimes take a couple minutes to populate.

Any advice?

Thanks!

Could you send spool (Database|Spool|Spool to screen) of whole action?

Please set spool before you issue problematic action

Hmmm…There really isn’t a lot of processing that happens as those dialogs are shown. It sounds like query execution is slow, but it’s interesting that this happens with every database. Do you think something could be ‘off’ in your network settings? If you go to the Schema Browser and click around on objects there, is it slow there too? Does a clean set of user files make any difference?

This is a new install on a new Dell Precision with 8GB of RAM.

Schema browsing is slow as well.

Actions I perform in SQLPlus via putty are very fast however.

Richard Wagner

Chippewa Valley Technical College | Oracle DBA/IT Application Admin

620 West Clairemont Avenue | Eau Claire WI 54701

www.cvtc.edu | 715-858-1894 |
rwagner33@cvtc.edu

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

Sent: Monday, January 27, 2014 11:24 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Describes very slow to respond

RE: Describes very slow to respond

Reply by John Dorlon

Hmmm…There really isn’t a lot of processing that happens as those dialogs are shown. It sounds like query execution is slow, but it’s interesting that this happens with every database. Do
you think something could be ‘off’ in your network settings? If you go to the Schema Browser and click around on objects there, is it slow there too? Does a clean set of user files make any difference?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

It sounds like queries are executing slowly. Does it make any difference if you look at objects in your login schema or other schemas? In your own schema, we use the USER_ views, which are generally pretty fast. When you browse to other schemas, we use ALL_ or DBA_, depending on privileges, and if you have the “Check for access to DBA views” option checked. That’s in Options -> Startup. If that’s not checked, check it, reconnect, and try again.

if that’s not the problem, see Damir’s reply above. Maybe the spool SQL output will tell us something.

After activating the spool, I did two things:

desc spriden

and it actually came up fairly quickly. But then I clicked the “Scripts” tab and had a delay of approximately 30 seconds. Sometimes the delay takes up to
two minutes.


Session: SYS@PCLONE

Timestamp: 11:47:16.231

Select owner, object_name, object_type

from sys.ALL_OBJECTS

where object_name = :obj

and object_type not like ‘%PARTITION%’

and owner = :own

union all

Select owner, constraint_name as object_name, ‘CONSTRAINT’ as object_type

from sys.ALL_CONSTRAINTS

where constraint_name = :obj

and owner = :own

order by 3

:obj(VARCHAR[7])=‘SPRIDEN’

:own(VARCHAR[3],IN/OUT)=‘SYS’


Session: SYS@PCLONE

Timestamp: 11:47:16.262

Select owner, object_name, object_type

from sys.ALL_OBJECTS

where object_name = :obj

and object_type not like ‘%PARTITION%’

union all

Select owner, constraint_name as object_name, ‘CONSTRAINT’ as object_type

from sys.ALL_CONSTRAINTS

where constraint_name = :obj

order by 3

:obj(VARCHAR[7])=‘SPRIDEN’


Session: SYS@PCLONE

Timestamp: 11:47:16.473

Select table_owner, table_name, db_link

from sys.ALL_SYNONYMS

where synonym_name = :obj

and owner = :own

:obj(VARCHAR[7])=‘SPRIDEN’

:own(VARCHAR[6],IN/OUT)=‘PUBLIC’


Session: SYS@PCLONE

Timestamp: 11:47:17.238

Select owner, object_name, object_type

from sys.ALL_OBJECTS

where object_name = :obj

and object_type in (‘VIEW’, ‘PROCEDURE’, ‘PACKAGE’, ‘FUNCTION’, ‘TABLE’, ‘SEQUENCE’, ‘SNAPSHOT’, ‘MATERIALIZED VIEW’, ‘OPERATOR’, ‘QUEUE’, ‘JAVA CLASS’, ‘JAVA
SOURCE’, ‘SYNONYM’, ‘TYPE’, ‘UNDEFINED’)

and owner in (:own, ‘PUBLIC’)

and not (object_type = ‘SYNONYM’ and owner = ‘PUBLIC’ and object_name = ‘SPRIDEN’)

order by 3

:obj(VARCHAR[7])=‘SPRIDEN’

:own(VARCHAR[6],IN/OUT)=‘SATURN’


Session: SYS@PCLONE

Timestamp: 11:47:19.415

Select last_ddl_time

from sys.ALL_OBJECTS

where owner = :own

and object_name = :nam

and object_type = :typ

:own(VARCHAR[6],IN/OUT)=‘SATURN’

:nam(VARCHAR[7],IN/OUT)=‘SPRIDEN’

:typ(VARCHAR[17],IN/OUT)=‘MATERIALIZED VIEW’


Session: SYS@PCLONE

Timestamp: 11:47:19.448

Select t.table_name,t.owner, t.cluster_name,

    t.partitioned, t.iot_type, t.tablespace_name, t.last_analyzed, round(t.num_rows) num_rows

   , t.temporary, t.table_type, t.table_type_owner, t.nested

   , decode(nvl(tablespace_name, 'x') || upper(partitioned) || nvl(iot_type, 'x') || to_char(pct_free), 'xNOx0', 'YES', 'NO') is_External

   , t.dropped

    , t.initial_extent

from sys.ALL_ALL_TABLES t

where 1=1

and t.owner = :own

and t.table_name = :obj

:own(VARCHAR[6],IN/OUT)=‘SATURN’

:obj(VARCHAR[7],IN/OUT)=‘SPRIDEN’


Session: SYS@PCLONE

Timestamp: 11:47:19.582

Select created, last_ddl_time, object_id, status

from sys.ALL_OBJECTS

where object_name = :nm

and owner = :o

and object_type = :t

:nm(VARCHAR[7],IN/OUT)=‘SPRIDEN’

:o(VARCHAR[6],IN/OUT)=‘SATURN’

:t(VARCHAR[5],IN/OUT)=‘TABLE’


Session: SYS@PCLONE

Timestamp: 11:47:19.631

Select cols.column_id, cols.column_name as Name, nullable,

data_type as Type,

decode(data_type, ‘CHAR’, char_length,

               'VARCHAR', char_length,

               'VARCHAR2', char_length,

               'NCHAR', char_length,

               'NVARCHAR', char_length,

               'NVARCHAR2', char_length,

               null) nchar_length,

Decode( data_type, ‘NUMBER’, data_precision + data_scale, data_length ) Length,

data_precision Precision, data_scale Scale, data_length dlength, data_default

,’ ’ comments

, DATA_TYPE_MOD

,cols.CHAR_USED

,cols.hidden_column, cols.internal_column_id

, qualified_col_name

,InitCap(histogram) histogram

,cols.virtual_column

,num_distinct, num_nulls, round(density, 5) density

FROM

sys.ALL_TAB_COLS cols

where 1=1

and cols.table_name = :TABNAME

and cols.owner = :OWNNAME

order by internal_column_id

:TABNAME(VARCHAR[7],IN/OUT)=‘SPRIDEN’

:OWNNAME(VARCHAR[6],IN/OUT)=‘SATURN’


Session: SYS@PCLONE

Timestamp: 11:47:19.698

select column_name, encryption_alg, salt

from sys.ALL_ENCRYPTED_COLUMNS

where owner = :own

and table_name = :tn

:own(VARCHAR[6],IN/OUT)=‘SATURN’

:tn(VARCHAR[7],IN/OUT)=‘SPRIDEN’


Session: SYS@PCLONE

Timestamp: 11:47:19.798

SELECT CN.NAME

FROM SYS.CDEF$ C, SYS.CON$ CN, SYS."_CURRENT_EDITION_OBJ" O, SYS.USER$ U

WHERE C.Type# = 2

AND C.CON# = CN.CON#

AND C.OBJ# = O.OBJ#

AND O.OWNER# = U.USER#

AND U.NAME = :uname

AND O.NAME = :tname

:uname(VARCHAR[6],IN/OUT)=‘SATURN’

:tname(VARCHAR[7],IN/OUT)=‘SPRIDEN’


Session: SYS@PCLONE

Timestamp: 11:47:20.032

Select t.trigger_name, t.trigger_type, t.triggering_event,

    t.when_clause, t.status enabled, null status, t.owner, -1 object_id, t.trigger_body

from sys.ALL_TRIGGERS t

where t.table_owner = :own

and t.table_name = :obj

:own(VARCHAR[6],IN/OUT)=‘SATURN’

:obj(VARCHAR[7],IN/OUT)=‘SPRIDEN’


Session: SYS@PCLONE

Timestamp: 11:47:20.115

select owner, object_name, object_id, status

from sys.ALL_OBJECTS

where object_type = ‘TRIGGER’

and (

(owner = ‘BANIMGR’ and object_name = ‘ET_SPRIDEN_AIR_EXTSOL’) or

(owner = ‘BANIMGR’ and object_name = ‘ET_SPRIDEN_AUR_EXTSOL’) or

(owner = ‘SATURN’ and object_name = ‘ST_SPRIDEN_NAME_COMPRESS’) or

(owner = ‘SATURN’ and object_name = ‘ST_SPRIDEN_AS_LDI’) or

(owner = ‘SATURN’ and object_name = ‘ST_SPRIDEN_AR_LDI’) or

(owner = ‘SATURN’ and object_name = ‘ST_SPRIDEN_INSERT_ODS_CHANGE’)

)

Session: SYS@PCLONE

Timestamp: 11:47:20.166

SELECT VALUE FROM V$OPTION WHERE PARAMETER=‘Objects’


Session: SYS@PCLONE

Timestamp: 11:47:20.174

Select t.*

from SYS.ALL_TRIGGERS t

where t.owner = :own

and t.trigger_name = :OneObjectName

order by t.trigger_name

:own(VARCHAR[6],IN)=‘SATURN’

:OneObjectName(VARCHAR[24],IN)=‘ST_SPRIDEN_NAME_COMPRESS’


Session: SYS@PCLONE

Timestamp: 11:47:20.236

Select t.*

from SYS.ALL_TRIGGERS t

where t.owner = :own

and t.trigger_name = :OneObjectName

order by t.trigger_name

:own(VARCHAR[7],IN)=‘BANIMGR’

:OneObjectName(VARCHAR[21],IN)=‘ET_SPRIDEN_AIR_EXTSOL’

some reading that might apply:

www.toadworld.com/…/help-toad-for-oracle-seems-slow.aspx

www.toadworld.com/…/speed-up-toad-for-oracle-s-schema-browser.aspx

Thanks. Yeah, a lot of queries are run when you click on the script tab. I see one query against all_indexes that’s taking a long time…search for Timestamp: 11:47:20.765, then look at the next query - it doesn’t happen until about 35 seconds later. Most of the other queries are executing in less than 1 second. So you might check to make sure the stats are up to date on the tables that make up ALL_INDEXES.

It looks like that “check for access to dba views” option is unchecked…because I see you are querying against ALL_ views, but at the end of the script, there is a query that’s executing against SYS’s tables (we check for privileges regardless of the option on a few of these because it makes a big difference in performance). From the main menu, click View -> Toad Options. In the options window, type “DBA” in the search box and that will take you to the option that I’m referring to. Make sure it’s checked. The DBA views are usually a lot faster than the ALL_ views.

This is a new install on a new Dell Precision

upgraded to a new Dell 64bit laptop running Windows 7 with 8gb of RAM

What is the true of both statements? What is upgraded?

Sorry for any confusion. I simply meant that I moved from an older Dell laptop to a new Dell laptop 64-bit. Both had Windows 7.

Richard Wagner

Chippewa Valley Technical College | Oracle DBA/IT Application Admin

620 West Clairemont Avenue | Eau Claire WI 54701

www.cvtc.edu | 715-858-1894 |
rwagner33@cvtc.edu

From: damir.vadas_531 [mailto:bounce-damirvadas_531@toadworld.com]

Sent: Monday, January 27, 2014 12:25 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Describes very slow to respond

RE: Describes very slow to respond

Reply by damir.vadas_531

This is a new install on a new Dell Precision

In first post you said this is Windows upgrade?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

I suspect something in Windows configuration … so here are mine questions.

  1. Where is your database-on separate server or on this laptop (seems to me separate server but want to recheck)?
  2. If on the same server, what is the speed of disk (i.e 5400 , 7200 rpm etc… is there any performance donwgrade in this case?)
    Is there any change in antivirus software?
  3. If is on separate server, what is the result of “tnsping db_sid”?
  4. What are Windows experience benchmark results (old->new laptop)