How to trace

See How to tune a query

Generic Trace for Oracle Support
--This will enable tracing on both local and remote site where db links are used
    
connect system/manager
grant alter session to <user>;
/* create this procedure on local and remote site */
connect <user>/<password>
create or replace procedure settrace as
c1 integer;
r1 integer;
BEGIN
c1:=dbms_sql.open_cursor;
dbms_sql.parse(c1,'alter session set timed_statistics=true', dbms_sql.v7);
r1:=dbms_sql.execute(c1);
dbms_sql.parse(c1,'alter session set events ''10046 trace name context 
forever, level 12''', dbms_sql.v7);
r1:=dbms_sql.execute(c1);
END;
/

connect <user>/<passwd>
exec settrace 
exec settrace@<db link used by the MV>

10g

By identifier
--self mark session
exec DBMS_SESSION.SET_IDENTIFIER ('account_update');
--trace
exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('account_update');
--on udump
trcsess output=account_update_trc.txt clientid=account_update *

9i

trace file size

alter system set max_dump_file_size=<MB>*1024*1024/<os block size>

where block size is:
NT/2000      512 
Solaris      512 
HP/UX       1024 
Tru 64 Unix 1024

Enable trace on logon

as sys!!:

CREATE OR REPLACE TRIGGER maintenance_trg
   AFTER LOGON ON DATABASE
DECLARE
   v_sid       NUMBER;
   v_isdba     VARCHAR2 (10);
   v_program   VARCHAR2 (30);
BEGIN
   EXECUTE IMMEDIATE 'select distinct sid from sys.v_$mystat'
                INTO v_sid;
   EXECUTE IMMEDIATE 'select username from sys.v_$session where sid = :b1'
                INTO v_program
               USING v_sid;
   SELECT SYS_CONTEXT ('userenv', 'ISDBA')
     INTO v_isdba
     FROM DUAL;
   v_program := UPPER (v_program);
   IF     (v_program IN
                   ('ALDO')
          )
      AND (NOT v_program IS NULL)
   THEN
      EXECUTE IMMEDIATE 'alter session set sql_trace=true';
   END IF;
END;
/

Please see Note:148137.1 for Tkprof a Trace File With Explain Plan

Please see Note:15159.1 to see how to trace errors

Please see Note:33838.1 for Determining the execution plan for a distributed query

tkprof al11_ora_1944.trc al11_ora_1944.trc.out explain=aldo/aldo@al11 table=aldo.plan_table sort=exeela sys=no insert=al11_ora_1944.trc.insert

To see bind variables use (and directly see the .trc generated):

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'

ALTER system SET EVENTS '10046 TRACE NAME CONTEXT off'

begin
dbms_system.set_sql_trace_in_session(sid, process, TRUE);
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       0.13         13         74          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     85.35     269.19      67623    7000001          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     85.42     269.32      67636    7000075          0           0

Query: It means LOGICAL_READS_PER_CALL

Oracle Trace
    
init.ora
ORACLE_TRACE_COLLECTION_NAME = oraclesm 
ORACLE_TRACE_FACILITY_NAME = oraclesm

alter session set oracle_trace_enable=true

select * from dual

cd /app/oracle/product/9.2.0/otrace/admin/cdf

#Oracle Trace tables will be created
otrcfmt oraclesm.cdf aldo/aldo@oempro