Home » RDBMS Server » Performance Tuning » Currently executing statement for Oracle Job- Oracle 10g
Currently executing statement for Oracle Job- Oracle 10g [message #260383] Sun, 19 August 2007 07:22 Go to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi,

I am using Oracle 10g R2 on Linux

While I am monitoring sessions for currently executing statements, i am using following query

select sql_text from v$sql where hash_value=(select sql_hash_value from v$session where sid=1940);


However, while i am trying to retrieve currently executing statement for a oracle job session, i am getting 'no rows returned' for above query all the time.
Moreover, hash_value for the session is always retrned as 0.

In oracle 9i R2, i was able to get currently executing sql statement for Oracle job using above query.

Is there any way getting it in Oracle 10g R2?(other than tracing session)

Thanks and Regards,
OraSaket
Re: Currently executing statement for Oracle Job- Oracle 10g [message #260400 is a reply to message #260383] Sun, 19 August 2007 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In 10g, use SQL_ID instead of address/hash_value.

Regards
Michel
Re: Currently executing statement for Oracle Job- Oracle 10g [message #260407 is a reply to message #260383] Sun, 19 August 2007 10:35 Go to previous messageGo to next message
orasaket
Messages: 70
Registered: November 2006
Member
Thanks Michel.

I will try it and will let you know


BTW, your replies are too fast...i feel you are sitting beside me and guiding me Smile

Regards,
OraSaket
Re: Currently executing statement for Oracle Job- Oracle 10g [message #260412 is a reply to message #260383] Sun, 19 August 2007 10:59 Go to previous messageGo to next message
orasaket
Messages: 70
Registered: November 2006
Member
Michel,

Unfortunately sql_id didn't work.

While the Oracle Job is executing, sql_id throughout the session was NULL

Where as there were N number of dmls executed by the Oracle job

Any workaround?

Regards,
OraSaket


Note :
I submitted Oracle Job to call following Procedure

Begin
tb1prc;
end;


table tb1 was created from all_objects

and procedure tb1prc with sufficient DMLs to check session details meanwhile

But Alas !!

create or replace procedure tb1prc as
begin

update tb1 set object_id=object_id+1;
update tb1 set object_id=object_id-1;
update tb1 set object_id=object_id-2;
update tb1 set object_id=object_id+2;
commit;
update tb1 set object_id=object_id-3;
update tb1 set object_id=object_id+3;
update tb1 set object_id=object_id-4;
update tb1 set object_id=object_id+4;
commit;
update tb1 set object_id=object_id-5;
update tb1 set object_id=object_id+5;
update tb1 set object_id=object_id-6;
update tb1 set object_id=object_id+6;
commit;
update tb1 set object_id=object_id+1;
update tb1 set object_id=object_id-1;
update tb1 set object_id=object_id-2;
update tb1 set object_id=object_id+2;
commit;
update tb1 set object_id=object_id-3;
update tb1 set object_id=object_id+3;
update tb1 set object_id=object_id-4;
update tb1 set object_id=object_id+4;
commit;
update tb1 set object_id=object_id-5;
update tb1 set object_id=object_id+5;
update tb1 set object_id=object_id-6;
update tb1 set object_id=object_id+6;
commit;
update tb1 set object_id=object_id+1;
update tb1 set object_id=object_id-1;
update tb1 set object_id=object_id-2;
update tb1 set object_id=object_id+2;
commit;
update tb1 set object_id=object_id-3;
update tb1 set object_id=object_id+3;
update tb1 set object_id=object_id-4;
update tb1 set object_id=object_id+4;
commit;
update tb1 set object_id=object_id-5;
update tb1 set object_id=object_id+5;
update tb1 set object_id=object_id-6;
update tb1 set object_id=object_id+6;
commit;
update tb1 set object_id=object_id+1;
update tb1 set object_id=object_id-1;
update tb1 set object_id=object_id-2;
update tb1 set object_id=object_id+2;
commit;
update tb1 set object_id=object_id-3;
update tb1 set object_id=object_id+3;
update tb1 set object_id=object_id-4;
update tb1 set object_id=object_id+4;
commit;
update tb1 set object_id=object_id-5;
update tb1 set object_id=object_id+5;
update tb1 set object_id=object_id-6;
update tb1 set object_id=object_id+6;
commit;
update tb1 set object_id=object_id+1;
update tb1 set object_id=object_id-1;
update tb1 set object_id=object_id-2;
update tb1 set object_id=object_id+2;
commit;
update tb1 set object_id=object_id-3;
update tb1 set object_id=object_id+3;
update tb1 set object_id=object_id-4;
update tb1 set object_id=object_id+4;
commit;
update tb1 set object_id=object_id-5;
update tb1 set object_id=object_id+5;
update tb1 set object_id=object_id-6;
update tb1 set object_id=object_id+6;
commit;
end tb1prc ;

Re: Currently executing statement for Oracle Job- Oracle 10g [message #260419 is a reply to message #260412] Sun, 19 August 2007 12:31 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sql_id in v$session gives the id of the top most level SQL the session is executing.
You're not executing SQL (at top most level) but PL/SQL so you don't have any SQL_ID.
If you want to follow what the procedure does you have to use dbms_application_info package to set client_info field.
Then you can query v$session for this.

Regards
Michel
Previous Topic: CONTEXT index
Next Topic: Currently Executing statement not available in Trace file- Oracle 10g
Goto Forum:
  


Current Time: Sat Dec 03 10:20:15 CST 2016

Total time taken to generate the page: 0.15443 seconds