It’s not stored automatically, you have to enable it.
Database -> Spool SQL -> Spool to screen.
Then you’ll see it in the output panel.
[ Attachment(s) from George.Meltser@aigfpc.com included below]
Weird. I see the same statement executed over and over for about 75 seconds. I
have no idea why it would be doing that, but something is definitely going
wrong.
If you zip up your user files folder and send it to me offline, I’ll see
if I can reproduce the problem.
The problem is very obvious though.
The new version (10.5.0.41) executes this query:
SELECT s . AUDSID , s . Client_Info , s . Command , s . Last_Call_ET , s .
LockWait , s . Logon_Time , s . Machine , s . Module , s . OSUser , s . PADDR ,
s . Process , s .Program, s . SchemaName , s . Serial# , s . Server , s .SID, s
. Status , s . Terminal , s .Type, s . Action , s . UserName , s .
Failover_Method , s . Failed_Over , s . Failover_Type , s . PDML_Enabled , s .
PDDL_Status , s . PDML_Status , s . PQ_Status , s . Resource_Consumer_Group , s
. SQL_Child_Number , s . SQL_ID , s . Service_Name , s .SQL_Trace
FROM V$SESSION S
WHERE ( UPPER( MACHINE ) = ‘AIGFP3’ or UPPER( MACHINE ) = ‘CLIENT_ADMIN’ or
UPPER( MACHINE ) = ‘CLIENT_ADMIN’ or UPPER( MACHINE ) = ‘CLIENT_ADMIN’ or UPPER(
MACHINE ) = ‘OPS$ORACLE’ or UPPER( MACHINE ) = ‘CLIENT_ADMIN’ )
AND ( ( s . USERNAME is not null) and (NVL( s . osuser , ‘x’ ) <> ‘SYSTEM’ ) and
( s .type <> ‘BACKGROUND’ ) );
which does full scan of sys.x$ksuse
Plan
SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 529 Cardinality: 1
3 NESTED LOOPS Cost: 0 Bytes: 529 Cardinality: 1
1 FIXED TABLE FULL TABLE (FIXED) SYS.X$KSUSE Cost: 0 Bytes: 516 Cardinality: 1
2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13
Cardinality: 1
The old version queries v$session using sid:
select decode( sql_hash_value , 0 , prev_hash_value , sql_hash_value ) hash
, decode( sql_id , null, prev_sql_id , sql_id ) sql_id
from v$session
where sid = :sid;
which DOES NOT DO FULL SCAN OF sys.x$ksuse
Plan
SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 120 Cardinality: 1
3 NESTED LOOPS Cost: 0 Bytes: 120 Cardinality: 1
1 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSUSE (ind:1) Cost: 0 Bytes: 107
Cardinality: 1
2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13
Cardinality: 1
That query is not in the file you sent me, but the first query did not replace
the 2nd. The first query you are showing is what we use to load the RHS session
tab. The 2nd query is what we use to get the current SQL.
Did you try running that query in the editor to be sure it is the one running
slowly? That plan looks like it has a fast running query to me (cost = 0 and
bytes = 516 is pretty insignificant)
It is definitely in the file that I sent you. And the query is not that fast, it
took 156 milliseconds for 1 machine and we have over a thousand machines
connected to oracle, so this query would take
156000 milliseconds or over 2.5 minutes.
Oops, my mistake, sorry. The query is there in the file. First one.
Did the debug come from a database where it runs quickly, but it runs slowly on
some other database? In the file you sent, the query takes about 2 seconds to
execute. That query executed at timestamp 13:23:27.330. The one after it is
13:23:29.752.
The debug came from the same database. I just tried two different version of
TOAD. They both select the same exact columns
(Machine, OSProgram, OSUser, Program, Server, SID, Status) and the detaild grid
points to the Session Tab.
I was just able to reproduce the repeated query execution here. Looking into it.
Hi George,
I’ve found the problem and it will be fixed in the next beta.
Unfortunately, there is nothing you can do to fix it on your end at this time.
One workaround that might work would be to come up with a filter that excludes
everything, and apply it before you change groupings, then remove it afterwards.
That’s kind of a pain, I know, but better than waiting several minutes.
-John
John, thanks for looking into it.
I will just stay away from 10.5 for time being as I use this query many times a
day.