Team Coding Performance

Toad for Oracle & DevOps Toolkit
1

Hello, Our site uses TOAD exclusively with Team Coding and SVN. The performance is really bad almost to the point where we want to remove team coding. We were able to determine one of the main queries that is really bad is below:

select * from user_tables where (table_name = :table_name or iot_name = :table_name);

It takes 44 seconds to run this query in DEV for a single table!! All SYS and SYSTEM tables are analyzed weekly.

We have 1426 tables in the view owned by one schema which is the user we have for all applications. We have a single IOT in that schema but it is rarely used.

Removing the OR clause works great but that is not an option because we are not generating these queries.

HELP!!!

2

Hi David,

Thanks for the details. 44 seconds sounds painful. Most of the time, I would think you are NOT looking for an IOT table so we could remove the IOT_NAME part of it, and only run that if don't find what we are looking for with just table name.

I have searched our source code and do not see that SQL in it. Can you give me a spool sql output so I can get some better context?

Go to main menu -> spool sql -> spool to screen
That will cause Toad to capture all of the sql that is run.
Next, check out a table or do whatever you need to in order to make that SQL run.
Toad will collect every SQL that it runs, along with timings, in the output panel at the bottom.

Post the spool sql output here, or if you are not comfortable with that, email it to me at john.dorlon@quest.com.

Thanks

3

This is what was captured using the toad “SQL Tracker”:

select
null as TABLE_TYPE_OWNER, null as TABLE_TYPE, null as OBJECT_ID_TYPE, TABLE_NAME,
TABLESPACE_NAME, BUFFER_POOL, IOT_NAME, IOT_TYPE, MIN_EXTENTS,
NEXT_EXTENT, MAX_EXTENTS, INI_TRANS, MAX_TRANS, INITIAL_EXTENT,
PCT_INCREASE, FREELISTS, FREELIST_GROUPS, PCT_FREE, PCT_USED,
INSTANCES, CLUSTER_NAME, DEGREE, NUM_ROWS, AVG_ROW_LEN,
TEMPORARY, LOGGING, PARTITIONED, NESTED, ROW_MOVEMENT,
MONITORING, DURATION, DEPENDENCIES, COMPRESSION, COMPRESS_FOR,
READ_ONLY, CACHE
,SECONDARY
,FLASH_CACHE, CELL_FLASH_CACHE, RESULT_CACHE, SEGMENT_CREATED
,INMEMORY, INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION, INMEMORY_DUPLICATE, CLUSTERING
,INMEMORY_SERVICE, INMEMORY_SERVICE_NAME, null as DEFAULT_COLLATION
,SHARDED, DUPLICATED, EXTERNAL
,MEMOPTIMIZE_READ, MEMOPTIMIZE_WRITE
,HYBRID
from sys.USER_TABLES t
where 1=1
and ((TABLE_NAME = :OneObjectName) or
(iot_name = :OneObjectName))
order by 4

4

Ah, ok, you were paraphrasing the first time around. :smiley:

I'll make the change I mentioned in my first reply.

5

John,

Can you post the QUEST Ticket# for this so we can track this and get the version that includes this change?

6

This is not an official support case, so there is no external ticket number. Internally this is TOR-10020, but I don't think you'll be able to track that anywhere

I am working on this today. It will likely be in Monday's beta. It will definitely be in the next beta after that and in Toad version 2026 R2, which is slated to be released in June.

1 Like
7

John, thank you for this BETA version. I have installed it and see that performance is much improved. Here is the longest query now whenever I pull up an object and CLICK into it:

-- Timestamp: 09:41:36.378
SELECT t.*
FROM TABLE (
ut_runner.get_suites_info (a_owner => :owner, a_package_name => :name))
t
ORDER BY CASE
WHEN t.item_type = 'UT_LOGICAL_SUITE' THEN 1
WHEN t.item_type = 'UT_SUITE' THEN 2
WHEN t.item_type = 'UT_SUITE_CONTEXT' THEN 3
ELSE 4
END;
:owner(VARCHAR[4],IN)='APPS'
:name(VARCHAR[0],IN)=
-- Elapsed Time: 21.768 seconds
-- Rows fetched: 7

8

This is when checking in/out a table?

If not, can you provide some mode detailed steps? I'm not sure exactly what you are doing in Toad when you say "pull up an object and click into it"

I think there is a way to disable that if you aren't using utplsql (even though someone else on your team might be) but I need to understand what you are doing a little bit better first.

9

This happens when I pull up a PACKAGE BODY in TEAM CODING and then click into the body in the editor. I have asked my team about unit testing. The only package part of that is a custom one.

10

Hi David,

This same query along with another one related to utPLSQL came through Quest Support as problematic and I’m working on it today. Can you share which version of utPLSQL you are using?

If utPLSQL was created with public synonym then you can run SELECT ut.version FROM dual

If it was installed without the public synonym you can open Unit Test Manager from Toad’s View menu and the the version will be shown in the status bar. Note that this may be slow on first load while all test suite data is retrieved.

Also, can you tell me if the installation was performed with the DDL Trigger option? You will find the ut_trigger_annotation_parsing trigger in the utPLSQL schema, UT3 by default, if it was installed.

Thanks,

Michael

11

Our version is currently v3.1.9.3270

12

Yes, this trigger is in our system and it is owned by TOAD.

13

Perfect, thanks.

utPLSQL 3.1.8 introduced some more efficient functions for determining if a package is a test suite. I’m adding these changes now.

Currently Toad is getting all tests suites for a schema and caching them. This can be costly on larger databases. I’ve setup a large test case that takes ~39 seconds to query for suites in my schema and the new function reduces this to 0.5 seconds or less.

Michael

14

@david.thompson The next version of Toad will have the slow utPLSQL queries resolved. If you are a beta user the change will be in next Monday’s beta. In your version of Toad you can uncheck the Enable utPLSQL Integration option on the Editor | Behavior page in Options. It’s on the Editor’s option page, but should improve your case as well.

Michael