Trace scheduler job execution in oracle

 

Scheduler jobs are executed with service name:SYS$USERS

begin

dbms_scheduler.drop_job ('TEST_JOB1');

end;

/

BEGIN dbms_scheduler.create_job ('TEST_JOB1', job_type => 'PLSQL_BLOCK', job_action => 'DECLARE   l_sql VARCHAR2(3999) := ''''; l_count NUMBER := 0; BEGIN DBMS_APPLICATION_INFO.set_module(module_name => ''DBMS_SCHEDULER'',action_name => ''CE_STREAM''); DBMS_OUTPUT.PUT_LINE(''This is a test job'');     END;', number_of_arguments => 0, start_date => systimestamp, repeat_interval => null, end_date => null, job_class => 'DEFAULT_JOB_CLASS', enabled => false, auto_drop => false, comments => 'A test Job ' ); END; 

/

Session 1:

------------------

  

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

BEGIN

DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=> 'SYS$USERS',  module_name=>'DBMS_SCHEDULER',  action_name=>'CE_STREAM',waits=>TRUE, binds=>TRUE ,plan_stat=>'ALL_EXECUTIONS');

END;

/

Session 2:

------------------

BEGIN

    dbms_scheduler.enable(name => 'TEST_JOB1');

END;

/ 

session 1:

----------------------- 

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name=> 'SYS$USERS',  module_name=>'DBMS_SCHEDULER',  action_name=>'CE_STREAM');

select * from all_Scheduler_jobs where job_name='TEST_JOB1';

select * From all_scheduler_running_jobs ;

select * from all_Scheduler_job_run_Details where job_name='TEST_JOB1';

select * from gv$session where module ='DBMS_SCHEDULER';

select * from gV$ACTIVE_SESSION_HISTORY where module ='DBMS_SCHEDULER';

select *  from dba_hist_active_sess_history where  module ='DBMS_SCHEDULER';

 

 

 col ADR_HOME for a60

 col trace_filename for a80

 set lines 400

 set timing on;

 set feedback on;

 

select distinct INST_ID, adr_home, trace_filename from gv$diag_trace_file_contents 

where regexp_like (payload, 'DBMS_SCHEDULER|CE_STREAM|TM_XMD_'  )

and trace_filename like '%.trc'

--and component_name='SQL_Trace' 

and TIMESTAMP > sysdate-1;

order by inst_id;

 col ADR_HOME for a60

 col trace_filename for a80

 set lines 400

 set timing on;

 set feedback on;

 SELECT DISTINCT

    inst_id,

    adr_home,

    trace_filename

FROM

    gv$diag_trace_file_contents

WHERE

    REGEXP_LIKE ( payload,

                  'DBMS_SCHEDULER|CE_STREAM|TM_XMD_' )

    AND trace_filename LIKE '%.trc'

    AND timestamp > sysdate - 1;

order

    by inst_id;

select PAYLOAD from gv$diag_trace_file_contents 

where trace_filename='DBNAME_ora_6338.trc'

and inst_id=1

and adr_home='..'

order by line_number

  -----------------

  

  grep -i CE_STREAM  *.trc

schedpoc3_j000_14008.trc:*** ACTION NAME:(CE_STREAM) 2021-08-04T05:33:43.715390-07:00

schedpoc3_j000_14008.trc:DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZONE := :mydate;  broken BOOLEAN := FALSE;  job_name VARCHAR2(128) := :job_name;  job_subname VARCHAR2(128) := :job_subname;  job_owner VARCHAR2(128) := :job_owner;  job_start TIMESTAMP WITH TIME ZONE := :job_start;  job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start;  window_start TIMESTAMP WITH TIME ZONE := :window_start;  window_end TIMESTAMP WITH TIME ZONE := :window_end;  chain_id VARCHAR2(14) :=  :chainid;  credential_owner VARCHAR2(128) := :credown;  credential_name  VARCHAR2(128) := :crednam;  destination_owner VARCHAR2(128) := :destown;  destination_name VARCHAR2(128) := :destnam;  job_dest_id varchar2(14) := :jdestid;  log_id number := :log_id;  BEGIN  DECLARE   l_sql VARCHAR2(3999) := ''; l_count NUMBER := 0; BEGIN DBMS_APPLICATION_INFO.set_module(module_name => 'DBMS_SCHEDULER',action_name => 'CE_STREAM'); DBMS_OUTPUT.PUT_LINE('This is a test job');     END;  :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

[mkusukun@denaa433 trace]$

tkprof schedpoc3_j000_14008.trc schedpoc3_j000_14008.log sys=no sort=EXEELA waits=yes aggregate=no

 TRCSESS command-line utility to consolidate tracing information from several trace files, then run TKPROF on the result.

trcsess  [output=output_file_name]

         [session=session_id]

         [clientid=client_id]

         [service=service_name]

         [action=action_name]

         [module=module_name]

         [trace_files]

 

tkprof input_file output_file

  [ waits=yes|no ] 

  [ sort=option ] PRSELA - Elapsed time spent parsing,EXECPU - CPU time spent executing EXEELA - Elapsed time spent executing

  [ print=n ]

  [ aggregate=yes|no ] 

  [ insert=filename3 ] 

  [ sys=yes|no ]

  [ table=schema.table ]

  [ explain=user/password ] 

  [ record=filename4 ] 

  [ width=n ]

  

TOP N PARTITION FROM EACH PARTITION TABLE

 TOP N PARTITION NAMES FROM EACH PARTITION TABLE IN SCHEMA:

SELECT

    table_name,

    LISTAGG(partition_name, '   ') WITHIN GROUP(

        ORDER BY

            partition_position

    ) AS partition_names

FROM

    (

        WITH rws AS (

            SELECT

                o.*,

                ROW_NUMBER() OVER(

                    PARTITION BY table_name

                    ORDER BY

                        partition_position DESC

                ) rn

            FROM

                user_tab_partitions o

        )

        SELECT

            table_name,

            partition_name,

            partition_position

        FROM

            rws

        WHERE

            rn <= 10

        ORDER BY

            table_name,

            partition_position

    )

GROUP BY

    table_name

ORDER BY

    table_name;

It will display top 10 partitions of each table in a schema.

Output will have 1 row per each table.

How to Enable Trace service name and module, another session in oracle

How to Enable Trace servicename and module, another session:

Note:

Test case 1 and Test case 2 : Enable /Disable tracing of sessions on combination of Service name and Module name

Test 3:   Enable/Disable trace on a particular user based on SID,SERIAL# .

Test case 1:

======================

Session 1:

----------------

sqlplus USERNAME/USERNAME@scanname.example.com:1521/DBNAME1

BEGIN

  DBMS_APPLICATION_INFO.set_module(module_name => 'MODULE_NAME',action_name => NULL); 

END;

/

session 2:

----------------

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=> 'DBNAME1',  module_name=>'MODULE_NAME',  action_name=>DBMS_MONITOR.ALL_ACTIONS,waits=>TRUE, binds=>TRUE ,plan_stat=>'ALL_EXECUTIONS');

Session 1:

----------------

create table test1 (a number, b number);

insert into test1 values (4,5);

insert into test1 values (4,5);

commit;

session 2:

----------------

sqlplus sys as sysdba

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name=>'DBNAME1', module_name=>'MODULE_NAME');

 

 

Session 1:

----------------

exit;

DBNAME1_ora_14963.trc:create table test1 (a number, b number)

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:insert into test1 values (4,5)

DBNAME1_ora_14963.trc:STAT #140240859786064 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  TEST1 (cr=4 pr=0 pw=0 str=1 time=621 us)'

DBNAME1_ora_14963.trc:insert into test1 values (4,5)

DBNAME1_ora_14963.trc:STAT #140240859786064 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  TEST1 (cr=0 pr=0 pw=0 str=1 time=91 us)'

Test case2:

======================

Session 1:

----------------

sqlplus USERNAME/USERNAME@scanname.example.com:1521/DBNAME1

BEGIN

  DBMS_APPLICATION_INFO.set_module(module_name => 'MODULE_NAME_2',action_name => NULL); 

END;

/

session 2:

----------------

sqlplus sys as sysdba

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=> 'DBNAME1',  module_name=>'MODULE_NAME_2',  action_name=>DBMS_MONITOR.ALL_ACTIONS,waits=>TRUE, binds=>TRUE ,plan_stat=>'ALL_EXECUTIONS');

Session 1:

----------------

create table test2 (a number, b number);

insert into test1 values (4,5);

insert into test1 values (4,5);

commit;

session 2:

----------------

sqlplus sys as sysdba

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name=>'DBNAME1', module_name=>'MODULE_NAME_2');

 

  

  

  

DBNAME1_ora_17154.trc:create table test2 (a number, b number)

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

 

TEST CASE 3:

======================

 

Session 1:

----------------

sqlplus USERNAME/USERNAME@scanname.example.com:1521/DBNAME1

session 2:

----------------

sqlplus sys as sysdba

col module for a30

col program for a30

col machine for a30

select sid, serial#, module, program, machine,SERVICE_NAME  from gv$session where username is not null;

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual; 

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 392, serial_num => 45590,waits => TRUE, binds => TRUE);

 

 

Session 1:

----------------

create table test3 (a number, b number);

insert into test1 values (4,5);

insert into test1 values (4,5);

commit;

session 2:

----------------

sqlplus sys as sysdba

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual; 

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 392, serial_num => 45590);

DBNAME1_ora_19678.trc:create table test3 (a number, b number)

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

Queries to find trace files of particualr SQL or whole trace file:

select distinct INST_ID, adr_home, trace_filename from gv$diag_trace_file_contents 

where regexp_like (payload, '8h284jvnd0z4k|g3jkap9hxf75k  )

and trace_filename like 'DBNAME%ora%.trc'

and component_name='SQL_Trace' 

--and TIMESTAMP > sysdate -0.5

select PAYLOAD from gv$diag_trace_file_contents 

where trace_filename='DBNAME_ora_6338.trc'

and inst_id=1

and adr_home='..'

order by line_number

How to run query with bind variables same as it run from application in oracle database

How to run query with bind variables same as it run from application in oracle database
We see queries run from application , we get different sql_id, plan hash value when we take that sql text and rum from command line, sql worksheet or sql developer for those queries that get values at run time in the form of bind variables.
the thing that I am going explain here how can we run a query exactly the way it is get executed from an application.
Take that exact query:

Orignal query:


SELECT TABLE_OWNER FROM TABLE_VIEW_NAME WHERE FIELD_NAME='MARK' AND OWNER= :B1

this is how I ran

create or replace procedure temp_p_123( param in varchar2 )
as
type rc is ref cursor;
l_cursor rc;
begin
open l_cursor for SELECT TABLE_OWNER FROM TABLE_VIEW_NAME WHERE FIELD_NAME='MARK' AND OWNER= param;
end;

then

- PASS bind variable value exactly as it was passed from application.

begin
temp_p_123( param =>'INPUT' )
end;


then take the explain plan very after query got executed, if you could not find sql_id, get it from GV$sql, dba_hist_active_sess_history etc.

in my case , i see its in cursor cache.
SELECT * from table (dbms_xplan.display_cursor ('sql_id',NULL,'allstats advanced last'));

I found that the same sql_id for same statement that I ran in database is same as the SQL_ID of same statement that ran from application. including plan hash value.

- this test is done to see if sql_profile created is really helped.. and had to evaluate if sql_profile created for sql_id is really helped or execution is really made use of sql_profile.

look for Notes section:
Note
-----
- SQL profile PROFILE_xxxxxxxx used for this statement