Too much data transfered

[Christo Kutrovsky]

Please take the content of this message seriously.

When using the browser in PL/SQL developer, and when expanding:

Tables->a table->Indexes->a specific index
then upon clicking on the "Columns" in my specific case, PL/SQL Developer generated 280 Kb of traffic to expand 3 columns of the index.

Because I am on a internet connection, this took about 30 seconds. I looked on the traffic between PL/SQL developer and the database and found the following queries to generate the biggest part of the traffic:

select col.*, com.Comments
from sys.all_tab_columns col,
sys.all_col_comments com
where col.owner = wner
and col.table_na@me = :table_name
and com.Owner (+) = :Owner
and com.Table_Name (+) = :table_name
and com.Column_Name (+) = col.Column_Name
order by col.column_id

because of "col.*" this result set is very wide, and if the table has 100+ columns, extremly slow.
This query alone is responsible for 60% of the traffic.

Right after this query the following is executed:

select * from sys.all_constraints
where table_name = bject_name
and owner = bject_owner
and constraint_type in ('P', 'U', 'R', 'C')
order by decode(constraint_type, 'P', 0, 'U', 1, 'R', 2,. 3), constraint_name

This is also relativelly wide, and given that most of the columns are not null also results in a lot of rows.

In adition to this this query is executed 8 times in a row (for each index)

select * from sys.all_ind_columns
where index_owner = :index_owner
and index_name = :index_name
order by column_position

However I am expanding ONLY ONE of the indexes, and if I am to expand another one, I have to wait another paitfull 30 seconds.

Do you intend to address this?

 

[Marco Kalter]

When fetching index columns the whole table definition is fetched. It seems to me that this should only be necessary once. I have added this to the list of enhancement requests.

 

[Christo Kutrovsky]

I think that it would be ideal if it did'nt do that at all.

It should do everything on the go. I may not need to get info on all the indexes, i might need just this one.

And there is also no need to get "*" from those tables. You only need some specific fields, not all of them.

If this is fixed I would be most happy, then any other feature. It's been preventing me from using the browser, and selecting the data dictionary my self as it is faster .. then waiting...

 

[Christo Kutrovsky]

Similar issue during connect. When you connect you execute:

select object_name, object_type
from sys.user_objects o
where o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')

If the schema I am connecting to has a large number of objects, this generates huge amounts of data been transfered, only to be re-transfered when I expand say "tables".

Same with the "describe" featue of a object.
On describe you execute:
select col.*, com.Comments
from sys.dba_tab_columns col,
sys.dba_col_comments com
where col.owner = wner
and col.table_na@me = :table_name
and com.Owner (+) = :Owner
and com.Table_Name (+) = :table_name
and com.Column_Name (+) = col.Column_Name
order. by col.column_id

Yet you only display name, type, nullable, defaults and comment. The rest of the columns are pure waste of bandwith and processing power from both the client and server.

Also I am not sure for what reason you are seding a large anonymous PL/SQL block, querying dba_synonyms, dba_users and session_roles.

 

[Marco Kalter]

It is indeed not easy to understand every query that PL/SQL Developer executes.

Note that there will always be a trade-off between one general query that can serve multiple purposes (which may fetch more information than needed in a specific situation) versus many specific queries (which fetch exactly what you need in a specific situation).

Anyway, the first query is executed in a background thread to obtain a list of all database object names that can be used by the Code Assistant. To suppress it, go to Tools > Preference > Code Assistant and disable the "Describe Context" feature.

The second query is used to resolve a symbol name. When you right-click on a symbol name in a source file, the name may be anything: a table, a synonym, a user, and so on.

 

[Christo Kutrovsky]

Marco,

Thank you very much for the detailed answer. I appreciate your time in explaining this.

About query one. Makes sense now. I was guessing it was used for the object browser, and yet it was re-quering everything again. I was wrong.

About the second one, my point is not why it is executed, but why is it necessary to use "col.*" and to fetch a very wide cursor, as you should need only about 9 of the 30 columns (in 9i). You never use all the statistical data, right ?

If you are to modify this query (which I am sure is shared between other functions) to select only data that is actually used, it will decrease the traffic by more than half.

Basically the most problems I have is due to "wide" tables, and the "col.*" and "select * from sys.all_constraints" for the table resulting in a lot of data been transfered. Especially if most of the table's columns are enforced "NOT NULL".

 

[molloycp]

I was just looking though old posts with respect to browser performance issues and noticed this one. I had previously posted about abnormal delays when expanding table columns in the object browser and all other related areas such as query builder.

I still experience the same delays, I also have access to SQL Navigator within which there is no delay when expanding the columns and I have also tried an evaluation copy of AQT which also does not have a delay when expanding columns.

It is very fustrating to have to wait everytime I expand a table. We have multiple databases with hundreds of tables with many fields. It is not a DB tuning issue as suggested previously as all other tools do not exibit this problem.

Please address this.

Even with the delay, I continue to use the product but it's a difficult sell to other due to the delays in expanding the columns of a table.

 

[ScottMattes]

I usually just right click the table and select describe. If I need more info I right click the table and select view.

 

[Stew Stryker]

Originally posted by ScottMattes:
I usually just right click the table and select describe. If I need more info I right click the table and select view.

I work this way too.

I used to use SQL/Navigator and found it terribly slow at looking up metadata. That's one of the things I love about PL/SQL Developer and why we converted to it. Developer is much faster than Navigator.

Stew