trace – Technology Geek

What is the TKPROF ??!!

The TKPROF program :

converts Oracle trace files into a more readable form. If you have a problem query you can user TKPROF to get more information.

But first you need to enable this tools since its unactivated in oracle to start using it follow the below steps :

1-ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

2-we need table called PLAN_TABLE if its dosen’t found create it by following steps :

@ORACLE_HOME\rdbms\admin\utlxplan.sql
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYS.PLAN_TABLE TO PUBLIC;

3-after doing the Previous steps , now you can use the amazing tools for example :

ALTER SESSION SET SQL_TRACE = TRUE;

SELECT COUNT(*)
FROM   dual;

ALTER SESSION SET SQL_TRACE = FALSE;

OR Another Way to trace file :

TKPROF

explain=user/password@service table=sys.plan_table

Output will be like :

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

 SELECT COUNT(*)
 FROM   dual

call    count    cpu elapsed    disk   query current     rows
------- -----  ----- ------- ------- ------- -------  -------
Parse       1   0.02    0.02       0       0       0        0
Execute     1   0.00    0.00       0       0       0        0
Fetch       2   0.00    0.00       0       1       4        1
------- -----  ----- ------- ------- ------- -------  -------
total       4   0.02    0.02       0       1       4        1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 121  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE 
      1   TABLE ACCESS FULL DUAL

Thank you

Osama mustafa

I faces the most strange issue that you will ever seen , Everything was works fine without any problem but suddenly without any error Replication on database stopped Why ?

No one was know , I try everything that you could be imagine , i try to register the archive log manually :

ALTER DATABASE REGISTER LOGICAL LOGFILE
‘Archivelog-name’ FOR ‘Capture-name’;

But nothing works, after that i start trace Capture process maybe it will give me hints with following produce:

1. Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)

2. Enable tracing:

sqlplus /nolog
connect / as sysdba
alter system set events ‘26700 trace name context forever, level 6’;
alter system set events ‘1349 trace name context forever , level 1024’;
exit

sqlplus strmadmin/passwd (streams admin)

exec dbms_capture_adm.set_parameter(‘yourcapturename’,’trace_level’,’127′);
exec dbms_capture_adm.start_capture(‘yourcapturename’);

Wait for about 30 minutes an check whether capture is or not progressing..

3. To turn off capture tracing:

sqlplus strmadmin/passwd (streams admin)

Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)
exec dbms_capture_adm.set_parameter(‘yourcapturename’,’trace_level’,null);
exit

sqlplus /nolog
connect / as sysdba

alter system set events ‘26700 trace name context off’;
alter system set events ‘1349 trace name context off’;
exit

Yes Nothing works , No error , its make me crazy

Tried checkpoint force, stop/start capture.

tracing the capture process:

1. Stop the capture(DBMS_CAPTURE_ADM.STOP_CAPTURE)

exec dbms_capture_adm.set_parameter(‘yourcapturename‘,’trace_level’,’127′);

exec dbms_capture_adm.start_capture(‘yourcapturename‘);
“set trace off after 10-15 mins”:

2. To turn off capture tracing:
exec dbms_capture_adm.set_parameter(‘yourcapturename‘,’trace_level’,null);

Yea Nothing Works too

Then I start check if there’s some missing Archivelogs By :

select NAME,SEQUENCE#,STATUS,DELETED from V$ARCHIVED_LOG

Information about archive logs that needed by Oracle Stream :

SELECT r.CONSUMER_NAME,
r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME AND
r.NEXT_SCN >= c.REQUIRED_CHECKPOINT_SCN
order by 3;

and using this query could help sometimes to give you information TXN about apply process :

select apply_name, xidusn||’.’||xidslt||’.’||xidsqn txn_id, first_scn, first_message_create_time, message_count, spill_creation_time from dba_apply_SPILL_TXN;

Output : (Usually The last one)

APPLY_FROM_MARKA               9.27.637276                         5600212722474 17-OCT-11            25    18-OCT-11
APPLY_FROM_MARKA               4.23.369085                         5600212761449 17-OCT-11             1       18-OCT-11
APPLY_FROM_MARKA               56.17.156892                        5600242177417 18-OCT-11             1       18-OCT-11
APPLY_FROM_MARKA               4.40.369226                        5600243099471 18-OCT-11             1       18-OCT-11
APPLY_FROM_MARKA               24.21.419884                         5600247380679 18-OCT-11            15      19-OCT-11
APPLY_FROM_MARKA               14.20.166840                         5600278453686 19-OCT-11             1       19-OCT-11

After Check The TXN we need to Run the Stream to ignore the required TXN :

1-exec dbms_apply_adm.stop_apply(‘Apply-process-name’);

2-exec dbms_apply_adm.set_parameter(‘Apply-process-name’,’_ignore_transaction’,’TXN-Number’);

3-exec sys.purge_spill_txn(‘Apply-process-name’,’TXN-Number’);

4-exec dbms_apply_adm.start_apply(‘Apply-process-name’);

5-exec dbms_apply_adm.set_parameter(‘Apply-process-name’,’_ignore_transaction’,null);

Capture Site:

1-execute dbms_capture_adm.stop_capture(‘capture-Process-name’);

2-execute dbms_capture_adm.set_parameter(‘capture-Process-name’,’_ignore_transaction’,’TXN-Number’);

3-execute dbms_capture_adm.start_capture(‘capture-Process-name’);

But Nothing works too .

But I remember something Stream got hidden parameter _SGA_Size :

Capture needs to be configured to have more space.

The default amount of space for capture / log miner activity is 10

Steps :

exec dbms_capture_adm.set_parameter(‘Capture-procees-name’,’_SGA_SIZE’,’50’);

exec dbms_capture_adm.start_capture(‘Capture-procees-name’);

The above changes space for named capture process : ‘STRMADMIN_CAPTURE’ from 10M -> 50M

Thank God Its works again .

Note that i write every steps maybe something will be useful for you .

Some metalink note will be useful too :

Troubleshooting Long-Running Transactions in Oracle Streams (Doc ID 783927.1)

LogMiner out-of-memory (Doc ID 336705.1)

Tag: trace

TKPROF And Oracle Trace Analysis

Oracle Stream Stop Replication without any error