Hi Folks

I am using PSD 9.0.0.1600. I have noticed that every time I run a SQL query in a SQL Window, there is a long delay (about 8 seconds) before the query starts to run during which time "Initializing..." appears on the status line. This time is not included in the execution time.

Below is a small extract from a Debug.txt file I generated that illustrates the problem, and may hold the answer. You will notice that there is nothing between these 2 lines:

19-Jul-2011 18:04:01 InitStatistics ends
19-Jul-2011 18:04:09 NavigationBookmarks.EnableButtons(Rebuild)

So for those 8 seconds nothing is happening.

Any ideas?

Best regards

Billy


19-Jul-2011 18:04:01 SQLForm Initializing...
19-Jul-2011 18:04:01 SetConnected starts
19-Jul-2011 18:04:01 Already connected: check connection
19-Jul-2011 18:04:01 TOracleQuery $03711650 Start Query.Execute
SQL = select 'x' from dual
19-Jul-2011 18:04:01 TOracleQuery $03711650 End (1 record processed)
Duration = 0.015
19-Jul-2011 18:04:01 Using existing connection
19-Jul-2011 18:04:01 EnableOutput starts
19-Jul-2011 18:04:01 TOracleQuery PLSQLDevForm.EnableOutputQuery Start Query.Execute
SQL = begin
if :enable = 0 then
sys.dbms_output.disable;
else
sys.dbms_output.enable(:size);
end if;
end;
:ENABLE = 1
:SIZE = 100000
19-Jul-2011 18:04:01 TOracleQuery PLSQLDevForm.EnableOutputQuery End
Duration = 0
19-Jul-2011 18:04:01 EnableOutput done
19-Jul-2011 18:04:01 InitStatistics starts
19-Jul-2011 18:04:01 TOracleQuery $03711650 Start Query.Execute
SQL = select 'x' from dual
19-Jul-2011 18:04:01 TOracleQuery $03711650 End (1 record processed)
Duration = 0.015
19-Jul-2011 18:04:01 StartValues: StatQuery.ReadBuffer = 381
19-Jul-2011 18:04:01 StartValues: StatQuery.ReadBuffer set to 381
19-Jul-2011 18:04:01 TOracleQuery $03712410 Start Query.Execute
SQL = select value from v$sesstat where sid = :sid order by statistic#
:SID = 1034
19-Jul-2011 18:04:01 TOracleQuery $03712410 End (0 records processed)
Duration = 0.016
19-Jul-2011 18:04:01 InitStatistics ends
19-Jul-2011 18:04:09 NavigationBookmarks.EnableButtons(Rebuild)
19-Jul-2011 18:04:09 NavigationBookmarks: Backward=0, Forward=0
19-Jul-2011 18:04:09 Lock
19-Jul-2011 18:04:09 UnLock
19-Jul-2011 18:04:09 SQLForm Executing...
19-Jul-2011 18:04:09 Lock
19-Jul-2011 18:04:09 UnLock
19-Jul-2011 18:04:09 SQLForm Executing...
19-Jul-2011 18:04:09 TSQLThread SQLThread Suspend ended
19-Jul-2011 18:04:09 TOracleQuery Query Start Query.Execute
SQL = begin :id := sys.dbms_transaction.local_transaction_id; end;
:ID = Null
19-Jul-2011 18:04:09 TOracleQuery Query End
Duration = 0
19-Jul-2011 18:04:09 TOracleQuery $03711BD0 Start Query.Execute
SQL = SELECT * FROM tdif_file_stages WHERE fstg_fle_id=90539 ORDER BY fstg_id
19-Jul-2011 18:04:09 TOracleQuery $03711BD0 End (8 records processed)
Duration = 0.016
19-Jul-2011 18:04:09 TOracleQuery SQLForm_2.TestQuery Start Query.Describe
SQL = SELECT * FROM tdif_file_stages
19-Jul-2011 18:04:09 TOracleQuery SQLForm_2.TestQuery End
Duration = 0.016
19-Jul-2011 18:04:09 TOracleQuery Query Start Query.Execute
SQL = begin :id := sys.dbms_transaction.local_transaction_id; end;
:ID = Null
19-Jul-2011 18:04:09 TOracleQuery Query End
Duration = 0.016
19-Jul-2011 18:04:09 TSQLThread SQLThread Postmessage
19-Jul-2011 18:04:09 TSQLThread SQLThread Suspend
19-Jul-2011 18:04:09 TSQLForm.ThreadFinished
19-Jul-2011 18:04:09 TSQLForm.ThreadFinished: SQLThread.Suspended
19-Jul-2011 18:04:09 SQLForm 8 rows selected in 0.078 seconds
19-Jul-2011 18:04:09 Lock
19-Jul-2011 18:04:09 UnLock
19-Jul-2011 18:04:09 EndValues: StatQuery.ReadBuffer = 381
19-Jul-2011 18:04:09 EndValues: StatQuery.ReadBuffer set to 381
19-Jul-2011 18:04:09 TOracleQuery $03712410 Start Query.Execute
SQL = select value from v$sesstat where sid = :sid order by statistic#
:SID = 1034
19-Jul-2011 18:04:09 TOracleQuery $03712410 End (0 records processed)
Duration = 0.015
19-Jul-2011 18:04:09 NavigationBookmarks.EnableButtons(Rebuild)
19-Jul-2011 18:04:09 NavigationBookmarks: Backward=0, Forward=0
19-Jul-2011 18:04:09 TOracleQuery $037110D0 Start Query.Execute
SQL = begin
sys.dbms_output.get_line(line => :line, status => :status);
end;
:LINE = Null
:STATUS = Null
19-Jul-2011 18:04:09 TOracleQuery $037110D0 End
Duration = 0
19-Jul-2011 18:04:09 SQLForm 8 rows selected in 0.078 seconds

We'll check out what's happening between these 2 log entries.

Hi Marco

I should point out that this "initializing..." delay did not just appear in Release 9, it has been there for a few months in Release 8 although I could not tell you exactly when it appeared.

I also have tried using the free Oracle SQL Developer IDE against the same database and there is no delay in running SQL queries, so the problem appears to be with PL/SQL Developer.

Best regards

Billy

Okay, thanks for the info.

Hi Marco

Another update on this, because it is becoming more and more frustrating. I noticed that if I select the SQL to be executed and click the Execute button, there is no delay, so the 8 second delay seems to be related to the AutoSelect Statement functionality where PSD is trying to determine what SQL statement to execute.

Following on this train of thought, I noticed that if I open a new SQL window and put a single SELECT statement in it and execute, there is no delay. Thus the delay I have been noticing seems to be caused by the size of the SQL script file in which I am executing individual SQL commands - in my case this file is currently 14,722 lines long, as it is a project SQL file that I keep adding to as the project develops.

Is it possible to stop the AutoSelect functionality from caring about the size of the file in which it is being asked to autoselect a SQL statement?

Best regards

Billy

We have not yet been able to reproduce this, nor have we had any similar problem reports. We'll add some diagnostics and will send you a test version shortly.

Thanks Marco

Hi!

I have similar issue, not the same, but maybe related. I'm using the latest version 9.0.0.1601.

I have really annoying delay of approx 10 seconds when doing any query which has many columns. When I select a query and press execute it kind of executes the query, in the result list I can see how many rows will be selected (it seems plsqldev is reserving the place and shows only first column of grid with select button where I can select whole row), it shows executing and after 10 secs result appears on the screen, while it tries to show the results IDE is completely frozen. When I execute the same query again it shows results immediately, even if I change where clause it selects that instantly. I observed increased network traffic when this happens. That particular table has 244 columns.
It seems that there is a problem getting description for result set, I tried to switch off the "Display dictionary info in result set" and this doesn't help.
This is extremely annoying

There is a test case, I ran 2 queries:
1. select sysdate from dual;
2. SELECT * FROM ap_invoice_distributions_all t WHERE invoice_distribution_id IN (-1,-2)

I remember there was similar issue with betas as well. Please fix this fast as there is no workaround this time

regards
Kirurgs

Hi!

Additional info.
I enabled DebugSQL as well and I see where the problem is. In debug.txt there is this kind of text repeated for 489 times after I run second query:

I think that's the cause. Please investigate and thank You in advance.

P.S. I can send You debug.txt as well, please post an address or site where to upload.

regards
Kirurgs

You can send it to support@allroundautomations.com. It may help if you mention this topic in your message.

Regards,
Gustavo