Hi,
I’ve installed a trial version of Toad for Data Analysts. When I connect to our database dwh_p, switching tabs of the object explorer is pretty slow. I takes about 5 seconds. If I login another database it’s much faster so it has to do with the database.
A typical thing about the “slow” database is that (compared to the “fast” database):
Can anyone explain this? What action does Toad carry out while switching tabs?
Thanks!
If you are on the Data tab of the database explorer we are making a new connection the first time you use the datatab. The data tab has its own dedicated conneciton. See if this is where the slowness seens to be.
The length of time to connect is a mystery to me
Debbie
Well, in my case switching tabs (e.g. between the table and view tab) also takes quite some time once I’m logged on and connected. And even when I switch many times between the same tabs, it always takes a few seconds (btw CPU jumps to 100% during the switch). I wonder why because the number of tables and views of the schema is very low.
It’s got to do with the database because I only experience this with one database. Any more idea’s?
How many tables/views are in each list? You can add a filter to shorten this even further.
However, the most likly issue is that the query we use to get the constraint data is taking time. This has been seen on many Oracle databases. I tried changing this query for one user to optimize it a bit but never hear back if it helped. You can try using the Tools|SQL Trace| to Out put feature. If you display the output window you can see the SQL that is being executed and perhaps determine which query is slow. These queries can be changed externally from the SQL Dictionary. Try this feature and see if you can determine which query we should focus on for this database. Post the output.
Debbie
The number of views of my schema BIC is 3, the number of tables is 5.
I enabled Tools->Execution Trace (btw there is no SQL Trace though I guess you meant Execution Trace) and intercepted the following SQL-output:
– 04-04-2011 16:39:54: Execution trace: DWH_P (BIC) SID : Executed on SID :
– f1:BIC
Select COUNT(1) from sys.ALL_OBJECTS where object_type = ‘VIEW’ and owner = :f1 order by object_name;
This happens when I switch to the View tab. Strangely, executing the above statement in the editor barely takes a second (I replaced :f1 by ‘BIC’)
It also struck me that during the switch all tabs disappear and that CPU usage jumps to about 50%. Is it polling for something or what? Anyway, it can’t wait for the above SQL- statement to finish!
Message was edited by: Erik0067
Can you execute this statement in the editor? Supply bind values for one of the tables. From the Options page turn on the command panel (Environment | Grid ). This will give you a panel in the editor that give you execution elapse time. Let me know the elapsed time.
Select tabs.table_name, tabs.owner, tabs.cluster_name
, partitioned, iot_type Iot_Type
, TEMPORARY, table_type, table_type_owner
, tablespace_name
, NESTED
, last_analyzed
, Dropped
, nvl(
decode(Temporary, ‘Y’, 'Temporary ') ||
decode(Partitioned, ‘YES’, 'Partitioned ') ||
ltrim(nvl(IOT_Type, ’ ') || ’ ') ||
decode (Nested, ‘YES’, 'Nested ') ||
decode(nvl(buffer_pool, ‘x’) || upper(partitioned) || nvl(iot_type, ‘x’), ‘xNOx’, 'External ') ||
trim(decode(nvl(cluster_name, ‘xYx’), ‘xYx’, ’ ', 'Cluster ')) ||
trim(decode(nvl(table_type, ‘xYx’), ‘xYx’, ’ ', ‘Object’))
, ‘Standard’) type1
, decode(nvl(buffer_pool, ‘x’) || upper(partitioned) || nvl(iot_type, ‘x’), ‘xNOx’, ‘YES’, ‘NO’) is_External
from sys.DBA_ALL_TABLES tabs
where tabs.owner = :f1 and (iot_type IS NULL OR iot_type = ‘IOT’)
order by tabs.table_name
It took some time, but here are my results…
The query you provided responded quickly, within a second. So this cannot be the cause of the problem. Any more idea’s?
BTW, it had to remove the column “dropped”, because this column wasn’t available. That has to do with the version of the database, it’s Oracle 8.1.7
Are you connecting to Oracle with an OCI client? Or using Direct Connect? If you are using Direct Connect, change to using OCI client.
If you are using OCI Client, unzip the attached. This tool will capture all SQL ran in the app and gives us timestamps. Start TDA and then start the SQL Monitor. Select the Toad.exe to monitor. Then reproduce the slow. Please let it wait until it becomes reponsive again. It records the SQL after it executes so if you don’t wait I will not see what SQL is actually hanging. RIght after that, spool the data to file and post.
Debbie
sqlmonitor_internal.zip (484 KB)
I’ve found the cause of this problem. It had to do with the enormous amount of schemas in our database (about 10000, each employee has its own schema). However the schemas of the users are of no importance to our application, so I applied the following filter condition:
NOT( SUBSTR(LOWER(username), 1, 1) = 'm' AND (SUBSTR(username, 2, 1)
BETWEEN '0' AND '9')) AND NOT (LOWER(username) LIKE '%batch_user')
Now all schemas starting with the letter ‘m’ followed by a digit weren’t shown anymore (thus our users are identified by the letter ‘m’ followed by a number)
Now switching between tabs takes less than a second!
Excellent! I did not think of filters. Great solution.
Debbie