Hi
Firstly if you know the meaning of column SQL_ID in view V$TEMPSEG_USAGE. Then you should be able to answer my question. Please check below. Thank you very much for you kindness.
We know that the view v$tempseg_usage has a column sql_id. I want to use the SQL_ID to find the sql statement which is consuming temp space. So I did below test. But seems this column has some problem. Below are my test.
First Step
I go to Session A run below statement
SQL> insert into temp3 values(1,'first','a');
1 row created.
SQL> commit;
Please be noted that temp3 is a temporary table. So insert values into it will consume temp space.
Second Step
I go to session B
SQL> select username , session_addr, sql_id from v$tempseg_usage;
USERNAME SESSION_ADDR SQL_ID
======= ========== ========
SCOTT 516F01C4 7y71dpx2qr5f1
Then I check the sql text associated with the sql_id.
SQL> select sql_text from v$sqltext where sql_id='7y71dpx2qr5f1';
SQL_TEXT
=======
insert into temp3 values(1,'first','a')
Here the sql_id helped me find the sql statement which consumed tem space.
But let`s continue.
Third Step
Go Back to session A run below statement
SQL> select * from dual;
D
-
X
Fourth Step
Go to Session B again
SQL> select username , session_addr, sql_id from v$tempseg_usage;
USERNAME SESSION_ADDR SQL_ID
======= ========== ========
SCOTT 516F01C4 a5ks9fhw2v9s1
select sql_text from v$sqltext where sql_id='a5ks9fhw2v9s1'
SQL_TEXT
======
select * from dual
Here the sql statement is select * from dual. This will not consume the tempspace.
So we can know that the sql_id is corresponding to the current sql statement of client session. Not exaclty the one who consume temp space.
If you want to find the statement which consumes temp space with this column you may get a wrong result sometimes.
Is this a bug of oracle? Or is there anything that I misunderstanding?
Kindly tell me please. Thanks in advance:)
Edited by: kramer on 2011-9-12 下午9:01