Slow Data Retrieval

[Rob van Gelder]

Hi.

We have a database with approximately 21000 tables. Opening the Tables folder in the Browser is painfully slow. About 30 seconds.
I traced the SQL and this is what is run:

select object_name, owner object_owner, status, object_type, created, last_ddl_time
from sys.all_objects o
where object_type = 'TABLE'
and object_name not like 'BIN$%'
and (owner not in ('SYS', 'SYSTEM', 'OUTLN'))
order by decode(owner, user, 0, 1), owner, object_name

In PLSQL Developer, the first few rows start appearing after 5 seconds. It takes a further 28 seconds for the remaining results to become available.

In Excel, I created an OLEDB querytable and used the same SQL. The results are available on the sheet within 7 seconds.

Have I misconfigured PLSQL Developer to retrieve results slow?
What must be done to speed things up?

Please reply?

 

[Marco Kalter]

What happens if you run the same SQL from SQL*Plus?

 

[mike]

If you don't need all tables at once, you could try using the browser filters to limit the rows returned. It goes quite a bit faster for me that way.

For example, I have one filter to return custom objects, and another for one listing objects like %PAY%.

The user's guide shows how to do this if you want to give it a try.

 

[Rob van Gelder]

In SQL*Plus it takes longer than 40 seconds.
But I expected that, due to the quantity of text output. It's not a fair comparison.

I could use filters, but I shouldnt have to.

My point is, PLSQL Developer is slow to retrieve results. I know it's slow because for the same SQL query I see all results 20 seconds earlier in Excel.

Is this my fault (misconfiguration) or is this a bottleneck in PLSQL Developer?

 

[Worker]

You can probably make a fair test in SQL*Plus by suppressing the output. Put the following in a script:

set termout off
<your select statement>

Then run that script in SQL*Plus.

"set termout off" only applies to scripts. Supposedly "set feedback off" suppresses output when entering commands interactively, but I can't get it to work.

 

[Rob van Gelder]

I did as you suggested. The query returns in 15 seconds.

 

[Worker]

If it takes SQL*Plus 15 seconds to get all rows, then it will take PL/SQL Developer at least that long as well.
The fact that it takes another 15 seconds to build the tree (like it takes SQL*Plus another 25+ seconds to show the output) is unfortunate and may be indicative of a bug. I don't know.

If it really takes Excel only 7 seconds to get the same exact complete result set that SQL*Plus takes 15 seconds to get, then that's weird but doesn't mean anything for PL/SQL Developer.

 

[Rob van Gelder]

Worker: It amazes me that you would accept this failure so easily.

The fact that it takes an additional (and unnecessary) 25 seconds is troubling me.

Marco: could you please confirm PLSQL Developer is using the additional 25 seconds on user interface overheads?

 

[Marco Kalter]

Marco: could you please confirm PLSQL Developer is using the additional 25 seconds on user interface overheads?

No, that seems way too much. If you add the debugsql parameter we can see what exactly takes so long. For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" debugsql

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory.