In session browser, selecting session (with huge sql running) "Current Statement" tab, basically hangs toad

Hi

Toad 13.3.0.181

I noticed, that when you have sql statement running, with for example 16k rows in that sql statement, then on another toad session, Session Browser window looks like hangs when selecting that session and moving to "Current Statement" tab. It took well over minute to load that.

From Editor window, gv$sql view (sql_fulltext column) I can select it and double click that clob value and get that statement instantly

select * from gv$sql where sql_ID='sql_id_value';

Raul

HI Raul,

I was playing with this a bit and it worked fast for me.

with for example 16k rows in that sql statement,

Do you mean that the SQL returned 16K rows of data? If so, was it a slow running query or did it just return a lot of data? Was it executing in Toad's editor or from non-Toad? If not executing, was it fetching?

Or maybe you meant that the SQL had 16K lines of text? That's a very long SQL statement so I'm thinking that isn't it.

Anyway, give me whatever details you can so I can reproduce it.

Thanks.

-John

Hmm, I just started wondering.

Well, today we had an issue with specific application query. There were sql statement, where there were close to a 20k rows (application code uses this dynamic sql, and the XML request favoured of generating this huge sql statement).
And actually there may have been a problem with sql parsing.

So this huge sql statement had hard parse time little over a minute. Execution itself (when sql statement was present in shared_pool) actually took only few seconds.
This sql had 500+ union operators, so lot of subqueries there. And I agree, not very fortunate sql. We need to do some changes there.

This may have very well been that, the toad SB "Current Statement" hanged during the sql parse operation. And after the parsing finished, and the execution started, then the toad was responsive again.

(later selecting it from gv$sql probably is not the same thing)

Can you confirm, which query is executed to get this "Session Browser" Current statement..?

Raul

You find the query that Toad executes at any time like this:

Go to main menu -> database -> Spool SQL -> Spool to screen.

Then do whatever you want in Toad and if Toad runs a SQL, you'll see it appear in the Output window at the bottom of Toad.

(In this case, yes, we select from V$SQL or GV$SQL depending on database and Toad settings)

Well, when I tried it again.

I executed this sql statement. I found out the sql_id, then executed it form Editor.
SQL> Select * from v$sql where sql_id = 'chtkdm29nzdh7';

And then I was able to double click this sql_fulltext just fine, and it opened instantly.

Then after that I spooled sql to screen. And opened Session Browser "Current Statment" tab, and was unresponsive again.
(so looks like this information was selected from the same view)

Select *
from gv$sql
where inst_id = :inst_id
and sql_id = :sqlid;
:inst_id(INTEGER,IN)=1
:sqlid(VARCHAR[13],IN)='chtkdm29nzdh7'

Raul

I wonder if it's related to syntax highlighting like your other issue?

If you paste that big SQL into Toad's editor, does it peform OK or does it lag? If it lags, can you send it to me? I'll send you my email address in a private message

When I copy it to Editor, it does not lagg at all.

Can't this "Explain Plan" be the culprit there. I mean does toad try to load plan also at the same time perhaps..? (at least "spool to screen" does not look to be executing any other sql)
(at the parsing time, there probably isn't this plan present yet)

When I open "Current Statement", the selected tab is Statement there (not Explain Plan)

image

Raul

No, it shouldn't be loading that information unless you click on that tab. You can verify that by spool SQL.

I tried a very long SQL statement and for me the current statement window loaded quickly.

Mine was just a long series of

select ..... from dual union all
select ...... from dual union all....

Hmm, this probably is related to parsing somehow.
I will try tomorrow, perhaps I can write some huge sql query, that has long enough parsing time, that you also are able to test in your system. Probably need to find some dictionary table, which is present in your system also.

But, getting late, so off to bed now.

Raul