TOAD hangs when viewing Script of certain Views

Toad for Oracle
1

Toad 10.6.1.3
Connected to 11.2.0.2 database.

I’ve encountered a weird case at a client of mine. They have some views (I’ve identified 2 so far), that when I describe (F4) the view, and then try to look at the script (via the Script tab), it freezes TOAD. I have to manually kill it from Windows. Other views show up fine.

No Toad.elf information is written.

If I look at the database, the frozen TOAD’s database connection is idle and the last command executed is:

SELECT *
FROM SYS.DBA_SEC_RELEVANT_COLS
WHERE object_owner = :own
AND object_name = :OneObjectName;

When I spool the SQL to a file, that is also the last statement shown in the spool file and I can run the statement just fine manually.

I’ve included a support bundle that I generated right before clicking the Script tab. The exception listed in the bundle is from an earlier non-related AVI

=-=-=-=-=-=-=-

2

It sounds like it is waiting for a query to execute. The last statement shown in
spool SQL is the last statement executed, not the one currently executing. I
can’t tell you what the next statement is because there are a lot of
possibilities.

Try this:

3

Already did that.

When I looked with the 2nd TOAD session, it showed the connection as ‘INACTIVE’ and the current/last statement as the one I mentioned.

Wayne

4

hmm…another user mentioned something like this earlier – it ended up
being a multi monitor problem because the popup script dialog was coming up on
another monitor that was no longer plugged in.

Sounds like you are just going to the script tab though. I wonder if it’s
a formatting problem. If the format option is checked, try unchecking it.

5

John,

Old thread.
I hadn’t noticed it, but my colleague mentioned it this morning. He had this with Toad 10.6 on a 10.2.0.5-database. I checked it with my 11.1b and encountered the same. Didn’t matter which user you want to view the script of. I then took another arbitrary 10.2.0.5-database and had no problems. As this was one of another customer and another platform we gave it another try with a database on the same platform for the same customer and had the same problem. The used platform is Solaris[tm] OE (64-bit).

Should try to find another customer using this platform (or another db-version on the same platform). Well, a bit later: with 10.2.0.3 on the same platform no issues.

Greetings, Dick

6

Try this:

  1. start up 2 toads.

  2. In the 1st Toad, go to get your view script so it hangs

  3. In the 2nd Toad, go to the session browser, find the session for the 1st Toad
    and see what the current statement is

Once we know what the slow statement is, you might be able to gather stats on
some sys table, or I might be able to change the query so it is faster.

7

Suggest reading this and testing the suggested fix – it’s easy to do
and 80% of the time this is the culprit.

http://www.toadworld.com/Blogs/tabid/67/EntryId/135/Toad-Sometimes-Slow-on-Oracle
-10g.aspx

8

Bert,

Thanx, that did it for one of the databases. The production seems not to work
and stays slow. We’ll dig into this further. Involved query (from the 2nd
Toad-session) is:

/* Formatted on 30-12-2011 10:47:14 (QP5 v5.194.11349.34592) */

SELECT o.object_type, pp.*

FROM dba_objects o,

(SELECT p.grantee,

p.owner,

p.table_name,

NULL column_name,

p.privilege,

p.grantable

FROM dba_tab_privs p, dba_users u

WHERE p.grantee = u.username

UNION ALL

SELECT p.grantee,

p.owner,

p.table_name,

p.column_name,

p.privilege,

p.grantable

FROM dba_col_privs p, dba_users u

WHERE p.grantee = u.username) pp

WHERE o.OWNER = pp.owner

/* ADVICE: [131] This item has not been declared, or it refers to a label */

/* ADVICE: [131] This item has not been declared, or it refers to a label */

AND o.object_name = pp.table_name

/* ADVICE: [131] This item has not been declared, or it refers to a label */

/* ADVICE: [131] This item has not been declared, or it refers to a label */

AND o.object_type IN

/* ADVICE: [131] This item has not been declared, or it refers to a label */

(‘TABLE’,

‘VIEW’,

‘SEQUENCE’,

‘PACKAGE’,

‘PROCEDURE’,

‘FUNCTION’,

‘SNAPSHOT’,

‘MATERIALIZED VIEW’,

‘DIRECTORY’,

‘LIBRARY’,

‘TYPE’,

‘OPERATOR’,

‘INDEXTYPE’,

‘EDITION’,

‘JOB’,

‘PROGRAM’,

‘SCHEDULE’)

AND pp.grantee = :OneObjectName

/* ADVICE: [131] This item has not been declared, or it refers to a label */

/* ADVICE: [131] This item has not been declared, or it refers to a label */

ORDER BY pp.grantee,

/* ADVICE: [131] This item has not been declared, or it refers to a label */

pp.owner,

/* ADVICE: [131] This item has not been declared, or it refers to a label */

pp.table_name,

/* ADVICE: [131] This item has not been declared, or it refers to a label */

pp.column_name

/* ADVICE: [131] This item has not been declared, or it refers to a label */

/* ADVICE:

ADVICE SUMMARY

Count Recommendation

image003.gif

9

Bert,

Thanx, that did it for one of the databases. The production seems not to work
and stays slow. We’ll dig into this further. Involved query (from the 2nd
Toad-session) is:

/* Formatted on 30-12-2011 10:47:14 (QP5 v5.194.11349.34592) */

SELECT o.object_type, pp.*

FROM dba_objects o,

(SELECT p.grantee,

p.owner,

p.table_name,

NULL column_name,

p.privilege,

p.grantable

FROM dba_tab_privs p, dba_users u

WHERE p.grantee = u.username

UNION ALL

SELECT p.grantee,

p.owner,

p.table_name,

p.column_name,

p.privilege,

p.grantable

FROM dba_col_privs p, dba_users u

WHERE p.grantee = u.username) pp

WHERE o.OWNER = pp.owner

/* ADVICE: [131] This item has not been declared, or it refers to a label */

/* ADVICE: [131] This item has not been declared, or it refers to a label */

AND o.object_name = pp.table_name

/* ADVICE: [131] This item has not been declared, or it refers to a label */

/* ADVICE: [131] This item has not been declared, or it refers to a label */

AND o.object_type IN

/* ADVICE: [131] This item has not been declared, or it refers to a label */

(‘TABLE’,

‘VIEW’,

‘SEQUENCE’,

‘PACKAGE’,

‘PROCEDURE’,

‘FUNCTION’,

‘SNAPSHOT’,

‘MATERIALIZED VIEW’,

‘DIRECTORY’,

‘LIBRARY’,

‘TYPE’,

‘OPERATOR’,

‘INDEXTYPE’,

‘EDITION’,

‘JOB’,

‘PROGRAM’,

‘SCHEDULE’)

AND pp.grantee = :OneObjectName

/* ADVICE: [131] This item has not been declared, or it refers to a label */

/* ADVICE: [131] This item has not been declared, or it refers to a label */

ORDER BY pp.grantee,

/* ADVICE: [131] This item has not been declared, or it refers to a label */

pp.owner,

/* ADVICE: [131] This item has not been declared, or it refers to a label */

pp.table_name,

/* ADVICE: [131] This item has not been declared, or it refers to a label */

pp.column_name

/* ADVICE: [131] This item has not been declared, or it refers to a label */

/* ADVICE:

ADVICE SUMMARY

Count Recommendation

image002.gif

10

Bert,

Thanx, that did it for one of the databases. The production seems not to work
and stays slow. We’ll dig into this further. Involved query (from the 2nd
Toad-session) is:

/* Formatted on 30-12-2011 10:47:14 (QP5 v5.194.11349.34592) */

SELECT o.object_type, pp.*

FROM dba_objects o,

(SELECT p.grantee,

p.owner,

p.table_name,

NULL column_name,

p.privilege,

p.grantable

FROM dba_tab_privs p, dba_users u

WHERE p.grantee = u.username

UNION ALL

SELECT p.grantee,

p.owner,

p.table_name,

p.column_name,

p.privilege,

p.grantable

FROM dba_col_privs p, dba_users u

WHERE p.grantee = u.username) pp

WHERE o.OWNER = pp.owner

/* ADVICE: [131] This item has not been declared, or it refers to a label */

/* ADVICE: [131] This item has not been declared, or it refers to a label */

AND o.object_name = pp.table_name

/* ADVICE: [131] This item has not been declared, or it refers to a label */

/* ADVICE: [131] This item has not been declared, or it refers to a label */

AND o.object_type IN

/* ADVICE: [131] This item has not been declared, or it refers to a label */

(‘TABLE’,

‘VIEW’,

‘SEQUENCE’,

‘PACKAGE’,

‘PROCEDURE’,

‘FUNCTION’,

‘SNAPSHOT’,

‘MATERIALIZED VIEW’,

‘DIRECTORY’,

‘LIBRARY’,

‘TYPE’,

‘OPERATOR’,

‘INDEXTYPE’,

‘EDITION’,

‘JOB’,

‘PROGRAM’,

‘SCHEDULE’)

AND pp.grantee = :OneObjectName

/* ADVICE: [131] This item has not been declared, or it refers to a label */

/* ADVICE: [131] This item has not been declared, or it refers to a label */

ORDER BY pp.grantee,

/* ADVICE: [131] This item has not been declared, or it refers to a label */

pp.owner,

/* ADVICE: [131] This item has not been declared, or it refers to a label */

pp.table_name,

/* ADVICE: [131] This item has not been declared, or it refers to a label */

pp.column_name

/* ADVICE: [131] This item has not been declared, or it refers to a label */

/* ADVICE:

ADVICE SUMMARY

Count Recommendation

image001.gif

11

I’ll work with John on tuning this query. We’ve found some statements like
these in the past where using more modern SQL constructs (e.g. WITH clause) can
make huge differences J
image001.gif

12

I’ll work with John on tuning this query. We’ve found some statements like
these in the past where using more modern SQL constructs (e.g. WITH clause) can
make huge differences J
image002.gif

13

I’ll work with John on tuning this query. We’ve found some statements like
these in the past where using more modern SQL constructs (e.g. WITH clause) can
make huge differences J
image003.gif

14

Dick,

I’m happy to try to improve this query, but it is not executed when Toad is
creating view scripts. This query comes from generating user scripts. There is,
however, a similar query that is executed when generating view scripts. So if we
can find an improvement to this one, I may be able to apply it to the other.

Try these queries and let me know if either is significantly better.

This one is rewritten using a WITH.

with privs as

( Select p. grantee , p. owner , p. table_name , null column_name , p.privilege,
p. grantable

from dba_tab_privs p, dba_users u

where p. grantee = u. username

and p. grantee = : OneObjectName

UNION ALL

Select p. grantee , p. owner , p. table_name , p. column_name , p.privilege, p.
grantable

from dba_col_privs p, dba_users u

where p. grantee = u. username

and p. grantee = : OneObjectName )

Select o . object_type , pp . *

from dba_objects o , privs pp

where o . OWNER = pp . owner

and o . object_name = pp . table_name

and o . object_type in ( ‘TABLE’ , ‘VIEW’ , ‘SEQUENCE’ , ‘PACKAGE’ , ‘PROCEDURE’
, ‘FUNCTION’ , ‘SNAPSHOT’ , ‘MATERIALIZED VIEW’ , ‘DIRECTORY’ , ‘LIBRARY’ ,
‘TYPE’ , ‘OPERATOR’ , ‘INDEXTYPE’ , ‘EDITION’ , ‘JOB’ , ‘PROGRAM’ , ‘SCHEDULE’ )

order by pp . grantee , pp . owner , pp . table_name , pp . column_name

This one adds a USE_HASH hint. It seems others have run across this query being
a problem. If you add this line in the SETTINGS section of Toad.ini, the query
will use this hint.

HashHintforPrivsQuery=1

Select /*+ USE_HASH(PP,O) */ o . object_type , pp . *

from dba_objects o ,

(Select p. grantee , p. owner , p. table_name , null column_name , p.privilege,
p. grantable

from dba_tab_privs p, dba_users u

where p. grantee = u. username

and p . grantee = : OneObjectName

UNION ALL

Select p. grantee , p. owner , p. table_name , p. column_name , p.privilege, p.
grantable

from dba_col_privs p, dba_users u

where p. grantee = u. username

and p . grantee = : OneObjectName

) pp

where o . OWNER = pp . owner

and o . object_name = pp . table_name

and o .object_type in ( ‘TABLE’ , ‘VIEW’ , ‘SEQUENCE’ , ‘PACKAGE’ , ‘PROCEDURE’
, ‘FUNCTION’ , ‘SNAPSHOT’ , ‘MATERIALIZED VIEW’ , ‘DIRECTORY’ , ‘LIBRARY’ ,
‘TYPE’ , ‘OPERATOR’ , ‘INDEXTYPE’ , ‘EDITION’ , ‘JOB’ , ‘PROGRAM’ , ‘SCHEDULE’ )

order by pp . grantee , pp . owner , pp . table_name , pp . column_name
image002.gif

15

Dick,

I’m happy to try to improve this query, but it is not executed when Toad is
creating view scripts. This query comes from generating user scripts. There is,
however, a similar query that is executed when generating view scripts. So if we
can find an improvement to this one, I may be able to apply it to the other.

Try these queries and let me know if either is significantly better.

This one is rewritten using a WITH.

with privs as

( Select p. grantee , p. owner , p. table_name , null column_name , p.privilege,
p. grantable

from dba_tab_privs p, dba_users u

where p. grantee = u. username

and p. grantee = : OneObjectName

UNION ALL

Select p. grantee , p. owner , p. table_name , p. column_name , p.privilege, p.
grantable

from dba_col_privs p, dba_users u

where p. grantee = u. username

and p. grantee = : OneObjectName )

Select o . object_type , pp . *

from dba_objects o , privs pp

where o . OWNER = pp . owner

and o . object_name = pp . table_name

and o . object_type in ( ‘TABLE’ , ‘VIEW’ , ‘SEQUENCE’ , ‘PACKAGE’ , ‘PROCEDURE’
, ‘FUNCTION’ , ‘SNAPSHOT’ , ‘MATERIALIZED VIEW’ , ‘DIRECTORY’ , ‘LIBRARY’ ,
‘TYPE’ , ‘OPERATOR’ , ‘INDEXTYPE’ , ‘EDITION’ , ‘JOB’ , ‘PROGRAM’ , ‘SCHEDULE’ )

order by pp . grantee , pp . owner , pp . table_name , pp . column_name

This one adds a USE_HASH hint. It seems others have run across this query being
a problem. If you add this line in the SETTINGS section of Toad.ini, the query
will use this hint.

HashHintforPrivsQuery=1

Select /*+ USE_HASH(PP,O) */ o . object_type , pp . *

from dba_objects o ,

(Select p. grantee , p. owner , p. table_name , null column_name , p.privilege,
p. grantable

from dba_tab_privs p, dba_users u

where p. grantee = u. username

and p . grantee = : OneObjectName

UNION ALL

Select p. grantee , p. owner , p. table_name , p. column_name , p.privilege, p.
grantable

from dba_col_privs p, dba_users u

where p. grantee = u. username

and p . grantee = : OneObjectName

) pp

where o . OWNER = pp . owner

and o . object_name = pp . table_name

and o .object_type in ( ‘TABLE’ , ‘VIEW’ , ‘SEQUENCE’ , ‘PACKAGE’ , ‘PROCEDURE’
, ‘FUNCTION’ , ‘SNAPSHOT’ , ‘MATERIALIZED VIEW’ , ‘DIRECTORY’ , ‘LIBRARY’ ,
‘TYPE’ , ‘OPERATOR’ , ‘INDEXTYPE’ , ‘EDITION’ , ‘JOB’ , ‘PROGRAM’ , ‘SCHEDULE’ )

order by pp . grantee , pp . owner , pp . table_name , pp . column_name
image003.gif

16

Dick,

I’m happy to try to improve this query, but it is not executed when Toad is
creating view scripts. This query comes from generating user scripts. There is,
however, a similar query that is executed when generating view scripts. So if we
can find an improvement to this one, I may be able to apply it to the other.

Try these queries and let me know if either is significantly better.

This one is rewritten using a WITH.

with privs as

( Select p. grantee , p. owner , p. table_name , null column_name , p.privilege,
p. grantable

from dba_tab_privs p, dba_users u

where p. grantee = u. username

and p. grantee = : OneObjectName

UNION ALL

Select p. grantee , p. owner , p. table_name , p. column_name , p.privilege, p.
grantable

from dba_col_privs p, dba_users u

where p. grantee = u. username

and p. grantee = : OneObjectName )

Select o . object_type , pp . *

from dba_objects o , privs pp

where o . OWNER = pp . owner

and o . object_name = pp . table_name

and o . object_type in ( ‘TABLE’ , ‘VIEW’ , ‘SEQUENCE’ , ‘PACKAGE’ , ‘PROCEDURE’
, ‘FUNCTION’ , ‘SNAPSHOT’ , ‘MATERIALIZED VIEW’ , ‘DIRECTORY’ , ‘LIBRARY’ ,
‘TYPE’ , ‘OPERATOR’ , ‘INDEXTYPE’ , ‘EDITION’ , ‘JOB’ , ‘PROGRAM’ , ‘SCHEDULE’ )

order by pp . grantee , pp . owner , pp . table_name , pp . column_name

This one adds a USE_HASH hint. It seems others have run across this query being
a problem. If you add this line in the SETTINGS section of Toad.ini, the query
will use this hint.

HashHintforPrivsQuery=1

Select /*+ USE_HASH(PP,O) */ o . object_type , pp . *

from dba_objects o ,

(Select p. grantee , p. owner , p. table_name , null column_name , p.privilege,
p. grantable

from dba_tab_privs p, dba_users u

where p. grantee = u. username

and p . grantee = : OneObjectName

UNION ALL

Select p. grantee , p. owner , p. table_name , p. column_name , p.privilege, p.
grantable

from dba_col_privs p, dba_users u

where p. grantee = u. username

and p . grantee = : OneObjectName

) pp

where o . OWNER = pp . owner

and o . object_name = pp . table_name

and o .object_type in ( ‘TABLE’ , ‘VIEW’ , ‘SEQUENCE’ , ‘PACKAGE’ , ‘PROCEDURE’
, ‘FUNCTION’ , ‘SNAPSHOT’ , ‘MATERIALIZED VIEW’ , ‘DIRECTORY’ , ‘LIBRARY’ ,
‘TYPE’ , ‘OPERATOR’ , ‘INDEXTYPE’ , ‘EDITION’ , ‘JOB’ , ‘PROGRAM’ , ‘SCHEDULE’ )

order by pp . grantee , pp . owner , pp . table_name , pp . column_name
image001.gif

17

Actually I tried the rewrite in the reverse order and seemed to get better
results – so can we test this one too please J

WITH o AS

(

SELECT object_type, object_name, owner

FROM dba_objects

WHERE object_type IN

(‘TABLE’,

‘VIEW’,

‘SEQUENCE’,

‘PACKAGE’,

‘PROCEDURE’,

‘FUNCTION’,

‘SNAPSHOT’,

‘MATERIALIZED VIEW’,

‘DIRECTORY’,

‘LIBRARY’,

‘TYPE’,

‘OPERATOR’,

‘INDEXTYPE’,

‘EDITION’,

‘JOB’,

‘PROGRAM’,

‘SCHEDULE’)

)

SELECT o.object_type,

p.grantee,

p.owner,

p.table_name,

NULL column_name,

p.privilege,

p.grantable

FROM dba_tab_privs p, dba_users u, o

WHERE p.grantee = u.username

AND p.grantee = : OneObjectName

AND o.owner = p.owner

and o.object_name = p.table_name

UNION ALL

SELECT o.object_type,

p.grantee,

p.owner,

p.table_name,

p.column_name,

p.privilege,

p.grantable

FROM dba_col_privs p, dba_users u, o

WHERE p.grantee = u.username

AND p.grantee = : OneObjectName

AND o.owner = p.owner

and o.object_name = p.table_name

ORDER BY grantee,

owner,

table_name,

column_name
image003.gif

18

Actually I tried the rewrite in the reverse order and seemed to get better
results – so can we test this one too please J

WITH o AS

(

SELECT object_type, object_name, owner

FROM dba_objects

WHERE object_type IN

(‘TABLE’,

‘VIEW’,

‘SEQUENCE’,

‘PACKAGE’,

‘PROCEDURE’,

‘FUNCTION’,

‘SNAPSHOT’,

‘MATERIALIZED VIEW’,

‘DIRECTORY’,

‘LIBRARY’,

‘TYPE’,

‘OPERATOR’,

‘INDEXTYPE’,

‘EDITION’,

‘JOB’,

‘PROGRAM’,

‘SCHEDULE’)

)

SELECT o.object_type,

p.grantee,

p.owner,

p.table_name,

NULL column_name,

p.privilege,

p.grantable

FROM dba_tab_privs p, dba_users u, o

WHERE p.grantee = u.username

AND p.grantee = : OneObjectName

AND o.owner = p.owner

and o.object_name = p.table_name

UNION ALL

SELECT o.object_type,

p.grantee,

p.owner,

p.table_name,

p.column_name,

p.privilege,

p.grantable

FROM dba_col_privs p, dba_users u, o

WHERE p.grantee = u.username

AND p.grantee = : OneObjectName

AND o.owner = p.owner

and o.object_name = p.table_name

ORDER BY grantee,

owner,

table_name,

column_name
image001.gif

19

Actually I tried the rewrite in the reverse order and seemed to get better
results – so can we test this one too please J

WITH o AS

(

SELECT object_type, object_name, owner

FROM dba_objects

WHERE object_type IN

(‘TABLE’,

‘VIEW’,

‘SEQUENCE’,

‘PACKAGE’,

‘PROCEDURE’,

‘FUNCTION’,

‘SNAPSHOT’,

‘MATERIALIZED VIEW’,

‘DIRECTORY’,

‘LIBRARY’,

‘TYPE’,

‘OPERATOR’,

‘INDEXTYPE’,

‘EDITION’,

‘JOB’,

‘PROGRAM’,

‘SCHEDULE’)

)

SELECT o.object_type,

p.grantee,

p.owner,

p.table_name,

NULL column_name,

p.privilege,

p.grantable

FROM dba_tab_privs p, dba_users u, o

WHERE p.grantee = u.username

AND p.grantee = : OneObjectName

AND o.owner = p.owner

and o.object_name = p.table_name

UNION ALL

SELECT o.object_type,

p.grantee,

p.owner,

p.table_name,

p.column_name,

p.privilege,

p.grantable

FROM dba_col_privs p, dba_users u, o

WHERE p.grantee = u.username

AND p.grantee = : OneObjectName

AND o.owner = p.owner

and o.object_name = p.table_name

ORDER BY grantee,

owner,

table_name,

column_name
image002.gif