pop-up with suggested column names extremely slow

Toad for Oracle - Beta
1

All,

I’m currently on Beta 12.9.0.37 and experiencing extreme slowness from the pop-up that appears when I type an alias followed by a period.
The pop-up blocks my editing session for 30-60 seconds before allowing me to continue my work.

Admittedly, the query I’m working on is a bit extreme as it holds 5 common table expressions and 25 joins for a total of 1103 lines of code.
(the statement is used to generate a bunch of packagegs from template data)

So, while I was typing the predicate on line 1094, afer tmpl. the pop-up appears and freezes my edit session.

Biggest problem: it occurs sometimes, but surely not always!

I’m aware this makes it hard to track the problem down.

FWIW I’m attaching the source code.

Abe Kornelis

[View:/cfs-file/__key/communityserver-discussions-components-files/86/Toad_5F00_20160308_5F00_slow_5F00_popup.sql:320:240]

2

If I’m seeing the same as you this is a good one. Is the blocking occurring before you even see the popup? Are you just getting the wait cursor for a long period of time and then the popup displays?

Thanks,

Michael

From: kornelis.abe [mailto:bounce-kornelisabe@toadworld.com]

Sent: Tuesday, March 08, 2016 4:36 AM

To: toadoraclebeta@toadworld.com

Subject: [Toad for Oracle - Beta Discussion Forum] pop-up with suggested column names extremely slow

pop-up with suggested column names extremely slow

Thread created by kornelis.abe

All,

I’m currently on Beta 12.9.0.37 and experiencing extreme slowness from the pop-up that appears when I type an alias followed by a period.

The pop-up blocks my editing session for 30-60 seconds before allowing me to continue my work.

Admittedly, the query I’m working on is a bit extreme as it holds 5 common table expressions and 25 joins for a total of 1103 lines of code.

(the statement is used to generate a bunch of packagegs from template data)

So, while I was typing the predicate on line 1094, afer tmpl. the pop-up appears and freezes my edit session.

Biggest problem: it occurs sometimes, but surely not always!

I’m aware this makes it hard to track the problem down.

FWIW I’m attaching the source code.

Abe Kornelis

==========

http://www.toadworld.com/cfs-file/__key/communityserver-discussions-components-files/86/Toad_5F00_20160308_5F00_slow_5F00_popup.sql

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

3

Michael,

From recollection: I think the pop-up showed and was populated, then froze. IIRC I have been clicking around inside and outside of the popup to revive the thing - to no avail.
after 30-60 seconds the freeze spontaneously resolved. Since it happened before, I decided to create a report. Next time, I’ll try to pay more detailed attention to any visible symptoms.

Abe

4

Michael,

I just had two more occurrences of the issue. The pop-up appears, takes a little while to populate, then freezes for a much longer period.

HTH,
Abe

5

If you are seeing the popup and then it is freezing my guess is that it’s looking for relationships between tables so that it can suggest joins. When it’s working if you see the Cancel button enabled on the popup then it’s querying. The happens sometimes, but not always behavior would suggest the query taking more or less time to execute in some cases. If the hang were before the popup is shown it would suggest pre-execution work like parsing your statement and gathering what info we can before hitting the database. Also, when it’s sitting there have you tried cancelling it with ESC? The popup can be cancelled now, mostly. There is still some synchronous work that cannot be cancelled, but any database work can be terminated for the most part.

6

Michael,

I tried hitting Escape - to no avail. I am forced to wait until Toad deigns to check for any additional input.

If I enter the first few characters of the column name I need, then these are retained and used once Toad finishes its ‘introspective state’.

Kind regards,
Abe

7

Spool SQL leading up to the hang and post it here. I’ll look at what occurs following the last query that is run. I don’t believe you’re experiencing what I see with your example, but there is still be room for improvement elsewhere. I’m seeing ~10 seconds delay before the popup displays while the statement is parsed and Toad figures out where within the SQL the caret is located.

8

I’ve had another occurrence of the issue. It even repeated with SQL spooled to screen. First of all, it takes about ten seconds before the first SQL appears in the bottom window. Then teh following SQL statements are executed:

Timestamp: 08:29:23.001

Connect: IIB_KRG@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.61.122)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=OMSPRD)))

Session: IIB_KRG@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.61.122)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=OMSPRD)))

Timestamp: 08:29:23.091

Select ‘REF_INTEGRITY’ as type,

rel.parent_owner,

rel.parent_table,

rel.pk_constraint,

rel.child_owner,

rel.child_table,

rel.fk_constraint,

cc1.column_name pk_column,

cc2.column_name fk_column,

cc1.position column_position

FROM (Select c1.owner AS parent_owner,

c1.table_name AS parent_table,

c2.r_constraint_name AS pk_constraint,

c2.owner AS child_owner,

c2.table_name AS child_table,

c2.constraint_name AS fk_constraint

FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2

WHERE c1.table_name = :name

AND c1.owner = :owner

AND c1.constraint_type IN (‘P’, ‘U’)

AND c2.constraint_type = ‘R’

AND c2.r_constraint_name = c1.constraint_name

AND c2.r_owner = c1.owner) rel,

sys.DBA_CONS_COLUMNS cc1,

sys.DBA_CONS_COLUMNS cc2

WHERE cc1.owner = rel.parent_owner

AND cc1.constraint_name = rel.pk_constraint

AND cc2.owner = rel.child_owner

AND cc2.constraint_name = rel.fk_constraint

AND cc1.position = cc2.position

UNION ALL

Select ‘REF_INTEGRITY’ as type,

rel.parent_owner,

rel.parent_table,

rel.pk_constraint,

rel.child_owner,

rel.child_table,

rel.fk_constraint,

cc1.column_name pk_column,

cc2.column_name fk_column,

cc1.position column_position

FROM (Select c1.owner AS parent_owner,

c1.table_name AS parent_table,

c2.r_constraint_name AS pk_constraint,

c2.owner AS child_owner,

c2.table_name AS child_table,

c2.constraint_name AS fk_constraint

FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2

WHERE c2.table_name = :name

AND c2.owner = :owner

AND c1.constraint_type IN (‘P’, ‘U’)

AND c2.constraint_type = ‘R’

AND c2.r_constraint_name = c1.constraint_name

AND c2.r_owner = c1.owner) rel,

sys.DBA_CONS_COLUMNS cc1,

sys.DBA_CONS_COLUMNS cc2

WHERE cc1.owner = rel.parent_owner

AND cc1.constraint_name = rel.pk_constraint

AND cc2.owner = rel.child_owner

AND cc2.constraint_name = rel.fk_constraint

AND cc1.position = cc2.position

ORDER BY 4, 10

:name(VARCHAR[17],IN)=‘GEN_TABLE_COLUMNS’

:owner(VARCHAR[7],IN)=‘IIB_KRG’

Session: IIB_KRG@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.61.122)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=OMSPRD)))

Timestamp: 08:29:25.929

Select ‘REF_INTEGRITY’ as type,

rel.parent_owner,

rel.parent_table,

rel.pk_constraint,

rel.child_owner,

rel.child_table,

rel.fk_constraint,

cc1.column_name pk_column,

cc2.column_name fk_column,

cc1.position column_position

FROM (Select c1.owner AS parent_owner,

c1.table_name AS parent_table,

c2.r_constraint_name AS pk_constraint,

c2.owner AS child_owner,

c2.table_name AS child_table,

c2.constraint_name AS fk_constraint

FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2

WHERE c1.table_name = :name

AND c1.owner = :owner

AND c1.constraint_type IN (‘P’, ‘U’)

AND c2.constraint_type = ‘R’

AND c2.r_constraint_name = c1.constraint_name

AND c2.r_owner = c1.owner) rel,

sys.DBA_CONS_COLUMNS cc1,

sys.DBA_CONS_COLUMNS cc2

WHERE cc1.owner = rel.parent_owner

AND cc1.constraint_name = rel.pk_constraint

AND cc2.owner = rel.child_owner

AND cc2.constraint_name = rel.fk_constraint

AND cc1.position = cc2.position

UNION ALL

Select ‘REF_INTEGRITY’ as type,

rel.parent_owner,

rel.parent_table,

rel.pk_constraint,

rel.child_owner,

rel.child_table,

rel.fk_constraint,

cc1.column_name pk_column,

cc2.column_name fk_column,

cc1.position column_position

FROM (Select c1.owner AS parent_owner,

c1.table_name AS parent_table,

c2.r_constraint_name AS pk_constraint,

c2.owner AS child_owner,

c2.table_name AS child_table,

c2.constraint_name AS fk_constraint

FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2

WHERE c2.table_name = :name

AND c2.owner = :owner

AND c1.constraint_type IN (‘P’, ‘U’)

AND c2.constraint_type = ‘R’

AND c2.r_constraint_name = c1.constraint_name

AND c2.r_owner = c1.owner) rel,

sys.DBA_CONS_COLUMNS cc1,

sys.DBA_CONS_COLUMNS cc2

WHERE cc1.owner = rel.parent_owner

AND cc1.constraint_name = rel.pk_constraint

AND cc2.owner = rel.child_owner

AND cc2.constraint_name = rel.fk_constraint

AND cc1.position = cc2.position

ORDER BY 4, 10

:name(VARCHAR[17],IN)=‘GEN_TABLE_COLUMNS’

:owner(VARCHAR[7],IN)=‘IIB_KRG’

Session: IIB_KRG@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.61.122)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=OMSPRD)))

Timestamp: 08:29:26.599

Select ‘REF_INTEGRITY’ as type,

rel.parent_owner,

rel.parent_table,

rel.pk_constraint,

rel.child_owner,

rel.child_table,

rel.fk_constraint,

cc1.column_name pk_column,

cc2.column_name fk_column,

cc1.position column_position

FROM (Select c1.owner AS parent_owner,

c1.table_name AS parent_table,

c2.r_constraint_name AS pk_constraint,

c2.owner AS child_owner,

c2.table_name AS child_table,

c2.constraint_name AS fk_constraint

FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2

WHERE c1.table_name = :name

AND c1.owner = :owner

AND c1.constraint_type IN (‘P’, ‘U’)

AND c2.constraint_type = ‘R’

AND c2.r_constraint_name = c1.constraint_name

AND c2.r_owner = c1.owner) rel,

sys.DBA_CONS_COLUMNS cc1,

sys.DBA_CONS_COLUMNS cc2

WHERE cc1.owner = rel.parent_owner

AND cc1.constraint_name = rel.pk_constraint

AND cc2.owner = rel.child_owner

AND cc2.constraint_name = rel.fk_constraint

AND cc1.position = cc2.position

UNION ALL

Select ‘REF_INTEGRITY’ as type,

rel.parent_owner,

rel.parent_table,

rel.pk_constraint,

rel.child_owner,

rel.child_table,

rel.fk_constraint,

cc1.column_name pk_column,

cc2.column_name fk_column,

cc1.position column_position

FROM (Select c1.owner AS parent_owner,

c1.table_name AS parent_table,

c2.r_constraint_name AS pk_constraint,

c2.owner AS child_owner,

c2.table_name AS child_table,

c2.constraint_name AS fk_constraint

FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2

WHERE c2.table_name = :name

AND c2.owner = :owner

AND c1.constraint_type IN (‘P’, ‘U’)

AND c2.constraint_type = ‘R’

AND c2.r_constraint_name = c1.constraint_name

AND c2.r_owner = c1.owner) rel,

sys.DBA_CONS_COLUMNS cc1,

sys.DBA_CONS_COLUMNS cc2

WHERE cc1.owner = rel.parent_owner

AND cc1.constraint_name = rel.pk_constraint

AND cc2.owner = rel.child_owner

AND cc2.constraint_name = rel.fk_constraint

AND cc1.position = cc2.position

ORDER BY 4, 10

:name(VARCHAR[10],IN)=‘GEN_TABLES’

:owner(VARCHAR[7],IN)=‘IIB_KRG’

Session: IIB_KRG@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.61.122)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=OMSPRD)))

Timestamp: 08:29:27.755

Select ‘REF_INTEGRITY’ as type,

rel.parent_owner,

rel.parent_table,

rel.pk_constraint,

rel.child_owner,

rel.child_table,

rel.fk_constraint,

cc1.column_name pk_column,

cc2.column_name fk_column,

cc1.position column_position

FROM (Select c1.owner AS parent_owner,

c1.table_name AS parent_table,

c2.r_constraint_name AS pk_constraint,

c2.owner AS child_owner,

c2.table_name AS child_table,

c2.constraint_name AS fk_constraint

FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2

WHERE c1.table_name = :name

AND c1.owner = :owner

AND c1.constraint_type IN (‘P’, ‘U’)

AND c2.constraint_type = ‘R’

AND c2.r_constraint_name = c1.constraint_name

AND c2.r_owner = c1.owner) rel,

sys.DBA_CONS_COLUMNS cc1,

sys.DBA_CONS_COLUMNS cc2

WHERE cc1.owner = rel.parent_owner

AND cc1.constraint_name = rel.pk_constraint

AND cc2.owner = rel.child_owner

AND cc2.constraint_name = rel.fk_constraint

AND cc1.position = cc2.position

UNION ALL

Select ‘REF_INTEGRITY’ as type,

rel.parent_owner,

rel.parent_table,

rel.pk_constraint,

rel.child_owner,

rel.child_table,

rel.fk_constraint,

cc1.column_name pk_column,

cc2.column_name fk_column,

cc1.position column_position

FROM (Select c1.owner AS parent_owner,

c1.table_name AS parent_table,

c2.r_constraint_name AS pk_constraint,

c2.owner AS child_owner,

c2.table_name AS child_table,

c2.constraint_name AS fk_constraint

FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2

WHERE c2.table_name = :name

AND c2.owner = :owner

AND c1.constraint_type IN (‘P’, ‘U’)

AND c2.constraint_type = ‘R’

AND c2.r_constraint_name = c1.constraint_name

AND c2.r_owner = c1.owner) rel,

sys.DBA_CONS_COLUMNS cc1,

sys.DBA_CONS_COLUMNS cc2

WHERE cc1.owner = rel.parent_owner

AND cc1.constraint_name = rel.pk_constraint

AND cc2.owner = rel.child_owner

AND cc2.constraint_name = rel.fk_constraint

AND cc1.position = cc2.position

ORDER BY 4, 10

:name(VARCHAR[19],IN)=‘GEN_MESSAGE_COLUMNS’

:owner(VARCHAR[7],IN)=‘IIB_KRG’

Session: IIB_KRG@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.61.122)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=OMSPRD)))

Timestamp: 08:29:28.682

Select ‘REF_INTEGRITY’ as type,

rel.parent_owner,

rel.parent_table,

rel.pk_constraint,

rel.child_owner,

rel.child_table,

rel.fk_constraint,

cc1.column_name pk_column,

cc2.column_name fk_column,

cc1.position column_position

FROM (Select c1.owner AS parent_owner,

c1.table_name AS parent_table,

c2.r_constraint_name AS pk_constraint,

c2.owner AS child_owner,

c2.table_name AS child_table,

c2.constraint_name AS fk_constraint

FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2

WHERE c1.table_name = :name

AND c1.owner = :owner

AND c1.constraint_type IN (‘P’, ‘U’)

AND c2.constraint_type = ‘R’

AND c2.r_constraint_name = c1.constraint_name

AND c2.r_owner = c1.owner) rel,

sys.DBA_CONS_COLUMNS cc1,

sys.DBA_CONS_COLUMNS cc2

WHERE cc1.owner = rel.parent_owner

AND cc1.constraint_name = rel.pk_constraint

AND cc2.owner = rel.child_owner

AND cc2.constraint_name = rel.fk_constraint

AND cc1.position = cc2.position

UNION ALL

Select ‘REF_INTEGRITY’ as type,

rel.parent_owner,

rel.parent_table,

rel.pk_constraint,

rel.child_owner,

rel.child_table,

rel.fk_constraint,

cc1.column_name pk_column,

cc2.column_name fk_column,

cc1.position column_position

FROM (Select c1.owner AS parent_owner,

c1.table_name AS parent_table,

c2.r_constraint_name AS pk_constraint,

c2.owner AS child_owner,

c2.table_name AS child_table,

c2.constraint_name AS fk_constraint

FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2

WHERE c2.table_name = :name

AND c2.owner = :owner

AND c1.constraint_type IN (‘P’, ‘U’)

AND c2.constraint_type = ‘R’

AND c2.r_constraint_name = c1.constraint_name

AND c2.r_owner = c1.owner) rel,

sys.DBA_CONS_COLUMNS cc1,

sys.DBA_CONS_COLUMNS cc2

WHERE cc1.owner = rel.parent_owner

AND cc1.constraint_name = rel.pk_constraint

AND cc2.owner = rel.child_owner

AND cc2.constraint_name = rel.fk_constraint

AND cc1.position = cc2.position

ORDER BY 4, 10

:name(VARCHAR[19],IN)=‘GEN_MESSAGE_COLUMNS’

:owner(VARCHAR[7],IN)=‘IIB_KRG’

Session: IIB_KRG@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.61.122)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=OMSPRD)))

Timestamp: 08:29:29.351

Select ‘REF_INTEGRITY’ as type,

rel.parent_owner,

rel.parent_table,

rel.pk_constraint,

rel.child_owner,

rel.child_table,

rel.fk_constraint,

cc1.column_name pk_column,

cc2.column_name fk_column,

cc1.position column_position

FROM (Select c1.owner AS parent_owner,

c1.table_name AS parent_table,

c2.r_constraint_name AS pk_constraint,

c2.owner AS child_owner,

c2.table_name AS child_table,

c2.constraint_name AS fk_constraint

FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2

WHERE c1.table_name = :name

AND c1.owner = :owner

AND c1.constraint_type IN (‘P’, ‘U’)

AND c2.constraint_type = ‘R’

AND c2.r_constraint_name = c1.constraint_name

AND c2.r_owner = c1.owner) rel,

sys.DBA_CONS_COLUMNS cc1,

sys.DBA_CONS_COLUMNS cc2

WHERE cc1.owner = rel.parent_owner

AND cc1.constraint_name = rel.pk_constraint

AND cc2.owner = rel.child_owner

AND cc2.constraint_name = rel.fk_constraint

AND cc1.position = cc2.position

UNION ALL

Select ‘REF_INTEGRITY’ as type,

rel.parent_owner,

rel.parent_table,

rel.pk_constraint,

rel.child_owner,

rel.child_table,

rel.fk_constraint,

cc1.column_name pk_column,

cc2.column_name fk_column,

cc1.position column_position

FROM (Select c1.owner AS parent_owner,

c1.table_name AS parent_table,

c2.r_constraint_name AS pk_constraint,

c2.owner AS child_owner,

c2.table_name AS child_table,

c2.constraint_name AS fk_constraint

FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2

WHERE c2.table_name = :name

AND c2.owner = :owner

AND c1.constraint_type IN (‘P’, ‘U’)

AND c2.constraint_type = ‘R’

AND c2.r_constraint_name = c1.constraint_name

AND c2.r_owner = c1.owner) rel,

sys.DBA_CONS_COLUMNS cc1,

sys.DBA_CONS_COLUMNS cc2

WHERE cc1.owner = rel.parent_owner

AND cc1.constraint_name = rel.pk_constraint

AND cc2.owner = rel.child_owner

AND cc2.constraint_name = rel.fk_constraint

AND cc1.position = cc2.position

ORDER BY 4, 10

:name(VARCHAR[19],IN)=‘MESSAGE_COLUMN_DATA’

:owner(VARCHAR[7],IN)=‘IIB_KRG’

Session: IIB_KRG@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.61.122)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=OMSPRD)))

Timestamp: 08:29:30.029

Select ‘REF_INTEGRITY’ as type,

rel.parent_owner,

rel.parent_table,

rel.pk_constraint,

rel.child_owner,

rel.child_table,

rel.fk_constraint,

cc1.column_name pk_column,

cc2.column_name fk_column,

cc1.position column_position

FROM (Select c1.owner AS parent_owner,

c1.table_name AS parent_table,

c2.r_constraint_name AS pk_constraint,

c2.owner AS child_owner,

c2.table_name AS child_table,

c2.constraint_name AS fk_constraint

FROM sys.DBA_CONSTRAINTS c1, sys.DBA_CONSTRAINTS c2

WHERE c1.table_name = :name

AND c1.owner = :owner

AND c1.constraint_type IN (‘P’, ‘U’)

AND c2.constraint_type = ‘R’

AND c2.r_constraint_name = c1.constraint_name

AND c2.r_owner = c1.owner) rel,

sys.DBA_CONS_COLUMNS cc1,

sys.DBA_CONS_COLUMNS cc2

WHERE cc1.owner = rel.parent_owner

AND cc1.constraint_name = rel.pk_constraint