15.0.23.720 - Picklist dropdown freeze

Toad for Oracle - Beta
1

My dropdown froze again so I decided to look at the ASH of the affected session.

This was my code when I triggered the dropdown:

SELECT * FROM user_tab_cols WHERE column_|

The session was executing the following query for 30 seconds:

Select 'REF_INTEGRITY' as type, ...

After that time (guess I tried to cancel earlier though) the session did the following until I killed it:

"EVENT":"SQL*Net break/reset to client",

Not sure if there's anything you can do with this info but I would really appreciate if TOAD could stop freezing like that :slight_smile:

Full row from gv$active_session_history:

{
  "recordset": [
  {
    "INST_ID":1,
    "SAMPLE_ID":38726638,
    "SAMPLE_TIME":"2020-11-16T11:51:37.967000000+00:00",
    "IS_AWR_SAMPLE":"N",
    "SESSION_ID":249,
    "SESSION_SERIAL#":35330,
    "SESSION_TYPE":"FOREGROUND",
    "FLAGS":16,
    "USER_ID":300,
    "SQL_ID":null,
    "IS_SQLID_CURRENT":"Y",
    "SQL_CHILD_NUMBER":-1,
    "SQL_OPCODE":0,
    "FORCE_MATCHING_SIGNATURE":0,
    "TOP_LEVEL_SQL_ID":null,
    "TOP_LEVEL_SQL_OPCODE":0,
    "SQL_OPNAME":null,
    "SQL_ADAPTIVE_PLAN_RESOLVED":0,
    "SQL_FULL_PLAN_HASH_VALUE":0,
    "SQL_PLAN_HASH_VALUE":0,
    "SQL_PLAN_LINE_ID":null,
    "SQL_PLAN_OPERATION":null,
    "SQL_PLAN_OPTIONS":null,
    "SQL_EXEC_ID":null,
    "SQL_EXEC_START":null,
    "PLSQL_ENTRY_OBJECT_ID":null,
    "PLSQL_ENTRY_SUBPROGRAM_ID":null,
    "PLSQL_OBJECT_ID":null,
    "PLSQL_SUBPROGRAM_ID":null,
    "QC_INSTANCE_ID":null,
    "QC_SESSION_ID":null,
    "QC_SESSION_SERIAL#":null,
    "PX_FLAGS":null,
    "EVENT":"SQL*Net break/reset to client",
    "EVENT_ID":1963888671,
    "EVENT#":398,
    "SEQ#":34,
    "P1TEXT":"driver id",
    "P1":1413697536,
    "P2TEXT":"break?",
    "P2":0,
    "P3TEXT":null,
    "P3":0,
    "WAIT_CLASS":"Application",
    "WAIT_CLASS_ID":4217450380,
    "WAIT_TIME":0,
    "SESSION_STATE":"WAITING",
    "TIME_WAITED":0,
    "BLOCKING_SESSION_STATUS":"NO HOLDER",
    "BLOCKING_SESSION":null,
    "BLOCKING_SESSION_SERIAL#":null,
    "BLOCKING_INST_ID":null,
    "BLOCKING_HANGCHAIN_INFO":null,
    "CURRENT_OBJ#":185183,
    "CURRENT_FILE#":2,
    "CURRENT_BLOCK#":66257,
    "CURRENT_ROW#":0,
    "TOP_LEVEL_CALL#":94,
    "TOP_LEVEL_CALL_NAME":"V8 Bundled Exec",
    "CONSUMER_GROUP_ID":17345,
    "XID":null,
    "REMOTE_INSTANCE#":null,
    "TIME_MODEL":0,
    "IN_CONNECTION_MGMT":"N",
    "IN_PARSE":"N",
    "IN_HARD_PARSE":"N",
    "IN_SQL_EXECUTION":"N",
    "IN_PLSQL_EXECUTION":"N",
    "IN_PLSQL_RPC":"N",
    "IN_PLSQL_COMPILATION":"N",
    "IN_JAVA_EXECUTION":"N",
    "IN_BIND":"N",
    "IN_CURSOR_CLOSE":"N",
    "IN_SEQUENCE_LOAD":"N",
    "IN_INMEMORY_QUERY":"N",
    "IN_INMEMORY_POPULATE":"N",
    "IN_INMEMORY_PREPOPULATE":"N",
    "IN_INMEMORY_REPOPULATE":"N",
    "IN_INMEMORY_TREPOPULATE":"N",
    "CAPTURE_OVERHEAD":"N",
    "REPLAY_OVERHEAD":"N",
    "IS_CAPTURED":"N",
    "IS_REPLAYED":"N",
    "SERVICE_HASH":2429356423,
    "PROGRAM":"Toad.exe",
    "MODULE":"Toad.exe",
    "ACTION":null,
    "CLIENT_ID":null,
    "MACHINE":"X",
    "PORT":60319,
    "ECID":null,
    "DBREPLAY_FILE_ID":0,
    "DBREPLAY_CALL_COUNTER":0,
    "TM_DELTA_TIME":869020,
    "TM_DELTA_CPU_TIME":869005,
    "TM_DELTA_DB_TIME":869020,
    "DELTA_TIME":1000405,
    "DELTA_READ_IO_REQUESTS":0,
    "DELTA_WRITE_IO_REQUESTS":0,
    "DELTA_READ_IO_BYTES":0,
    "DELTA_WRITE_IO_BYTES":0,
    "DELTA_INTERCONNECT_IO_BYTES":0,
    "DELTA_READ_MEM_BYTES":245760,
    "PGA_ALLOCATED":4182016,
    "TEMP_SPACE_ALLOCATED":0,
    "CON_DBID":301373459,
    "CON_ID":0,
    "DBOP_NAME":null,
    "DBOP_EXEC_ID":0
  }
  ]
}
2

Thanks for the info.

Michael and I have an idea that may help with the freezes. It may be a few weeks before we get a chance to try it out though.

3

There's also a hang caused by the "Join Suggestions" in Code Insight if the timing is right. An underlying thread encounters deadlock. If you experience this often in the meantime you can disable "Join Suggestions" in Options | Editor |Code Assist in the Code Insight - Include list.

4

Thanks - let me know when I can re-enable this : )

5

Not sure if exactly related, but I'm experiencing significant "hangs" when editing SQL in Toad 15.0.97.1178 against 12.2 DBs if I happen to slow my typing down in the wrong place. Seems to be poor query performance of this SQL and If I'm patient enough it usually returns (just took 10+ minutes), but it often happens when I can't afford to be patient. Don't really want to turn off Code Insight, but if there's a way to do it surgically or entirely I can't seem to find a way to make these hangs go away entirely.

Select 'REF_INTEGRITY' as type,
       rel.*, 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) OR
              (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   cc1.table_name = rel.parent_table
AND   cc2.owner = rel.child_owner
AND   cc2.table_name = rel.child_table
AND   cc2.constraint_name = rel.fk_constraint
AND   cc1.position = cc2.position
ORDER BY 4, 10

I added a /*+ RULE */ hint to the subquery (too lazy/busy to try anything else) and it returned in 28 seconds, but that's still too slow. I can't really control my data dictionary stats. Perhaps this SQL could be reviewed and tuned/refactored to perform more consistently?

6

And if you know a way I can change the email address in my profile, I'd appreciate a point in the right direction. My former company spun off my new company. I'm now I'm persona non grata for Quest at large with my old company's email address but am still allowed in here. :wink:

7

Short answer: Update to 15.1 and this will be a lot better.

Long answer:
The query shown above is only in version 15.0. It was changed in hopes of improvement, but that turned out to not be the case for some users. If you want it to not run at all, you can go to Options -> Editor -> Code Assist. On the right, inside the "Include" tree for Code Insight, scroll down and uncheck "Join Suggestions".

In 15.1, we made another change so that we run the join suggestions query only once so it loads constraint info for the whole schema, as opposed to running it each time the popup is displayed as it does for 15.0 and prior. We also rewrote the query so that it is much faster.

Re: Email address - I looked around and didn't see a way to do that. I'm a moderator, and it won't let me change mine (or yours) either. If you make a new account using your new email address, I can increase your trust level so that it doesn't treat you like a brand-new user (not letting you post images, etc, for a while)

1 Like
8

Thanks! I turned off "Join Suggestions" and haven't had things hang since.

I usually stay on the bleeding edge (used to get the beta versions) and tried to download 15.1 when I was notified it was available. That's when I realized I'm no longer able to, apparently because my former company no longer licenses Toad. All that changed for me is my email domain. My chair/job hasn't really changed much since 2006 but my email address has changed three or four times because of mergers and spin offs.

Been using and depending on Toad since it was the freeware TOAD back in the late 90s. Hope to never have to do without Toad until I choose to do without Oracle altogether! :wink:

1 Like