1 Tracing

1.A Why you can’t use fixed views to trace

The main and only reason for not to use fixed views to trace, is simple: consistency. Some fixed views are updated periodically.
And t here are other reason like the fact you can’t always find the information about the session you want to trace and precision in time periods like trace files.
Fixed views are very important for several administration tasks like monitor and tune, like see all sql executed and order by consistent gets or other criteria to tune ;but to trace, use the correct utility

1.B Setting information to be gathered in trace

To trace, you have distinct trace levels and debugging events. 1.B.i DBMS_APPLICATION_INFO package Setting Session Information to filter information & to debug without commit J This has two uses to find what is doing a session
To inform other sessions what a session is actually doing, this is done setting values on memory views, you don’t need to commit to get other user session, can read that value. This values are too used for other purposes, for example on oracle tuning.
To register the status of a specific long operation, there you can register the total of work, and the current % you had done

The main view where you will see this columns is V$SESSION, but if you execute a query will see there are other too. I can see some interesting like V$SQL and V$SQLAREA, but you will see only the action and module data from the one who executed the query for first time. This means you could need to flush the shared pool.
select OWNER,TABLE_NAME,COLUMN_NAME
from dba_tab_cols
where column_name in ('ACTION','MODULE','CLIENT_INFO')
ORDER BY 3,2,1
/ If you execute on session a
EXEC DBMS_APPLICATION_INFO.SET_ACTION('ACTION_A_0');
EXEC DBMS_APPLICATION_INFO.SET_MODULE('MODULE_B','ACTION_B');
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('CLIENT_INFO_B');
you will see the same output from session a, and session b
IMPORTANT: This don’t executes a COMMIT statement.
SELECT MODULE,ACTION,CLIENT_INFO FROM V$SESSION
WHERE MODULE ='MODULE_B'; 1.B.ii Parameter:Statistics Level J STATISTICS_LEVEL is set by default on TYPICAL
SQL> show parameter statistics_level This parameter controls all major statistics collections or advisories in the database.
1) Setting to BASIC Disable advisories, statistics collection, monitoring and many automatic features. Oracle does not recommend this setting.
2) Setting to TYPICAL, Oracle collects major statistics providing the best overall database performance, recommended as default.
3) Setting to ALL Same as TYPICAL plus timed operating system statistics and row source execution statistics. You usually use it when you want recommended to temporarily trace in detail some activity.
If you check its view, you will see typical enabled all except Timed OS Statistics
But setting to typical or all, there are other important features enabled or can be enabled, not included in this list, like
Automatic Workload Repository.
Automatic database diagnostic monitoring
Automatic Shared Memory Management
Enables to Oracle to decide whether to cache a table depending on the table scan history. The table is cached only if a future table scan is likely to find the cached blocks.
Automatic purging
Etc.
Anyway you can enable/disable different modules independently, for example if you set to BASIC, you can enable timed statistics.
1* select * from v$statistics_level order by 1
2 / 1.B.iii Parameter TIMED_STATISTICS {TRUE|FALSE} J This parameter should be always TRUE (it is the default value).
Enables or disables the collection of timed statistics on the database, CPU, elapsed times, gather wait time for events in SQL Trace and the collection of various statistics in the dynamic performance tables.
The cost of having this parameter TRUE, is low and the benefits are very important.

Updates information in several dynamic performance views like: DBA_HIST_TEMPSTATXS, DBA_HIST_FILESTATXS, V$ASM_DISK, V$EVENT_HISTOGRAM, V$FILE_HISTOGRAM, V$FILE_HISTOGRAM, V$FILESTAT, V$SESSION_EVENT, V$SESSION_EVENT, V$SESSION_LONGOPS, V$SESSTATS, V$SYSSTATS, V$SESSION, V$SESSION_WAIT, V$SESSION_EVENT, V$SYSTEM_EVENT, etc. .
On trace, CPU Total CPU time in seconds for all parse, execute, or fetch calls; and Total elapsed time in seconds for all parse, execute, or fetch calls for the statement are (0) if TIMED_STATISTICS is not TRUE.

1.C Enabling Trace

On 10g you don’t need to use event 10046, dbms_monitor and dbms_session enables this functionality.
The functionality you could get from
10046 EVENT levels:
1 - Enable standard SQL_TRACE functionality (Default)
4 - As Level 1 PLUS trace bind values
8 - As Level 1 PLUS trace waits
This is especially useful for spotting latch wait etc.
but can also be used to spot full table scans and index scans.
12 - As Level 1 PLUS both trace bind values and waits
This event enabled trace (not need to use sql_trace)0

DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE)

DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(
client_id IN VARCHAR2,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE);

DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(
service_name IN VARCHAR2,
module_name IN VARCHAR2,
action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);

DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(
service_name IN VARCHAR2,
module_name IN VARCHAR2 DEFAULT ANY_MODULE,
action_name IN VARCHAR2 DEFAULT ANY_ACTION,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
instance_name IN VARCHAR2 DEFAULT NULL);

DBMS_SESSION.SESSION_TRACE_ENABLE(waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE);
DBMS_SESSION.SESSION_TRACE_ENABLE();

DBMS_SESSION.SET_SQL_TRACE (
sql_trace boolean); replaces the functionality of ALTER SESSION SET SQL_TRACE
DBMS.MONITOR

1.D Sizing dump files

ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;

1.E Analysis trace file with Bind/Waits

You must keep in mind raw file (not processed by tkprof) has more information, than the summary generated by tkprof. So you have two option to use or not to use tkprof. 1.E.i TKPROF 1.E.ii Trace Raw file Analysis

1.F Trace information on memory tables

Some trace information are in memory tables, not everything is on the trace file.
To analyze a process on detail, specially for tuning, you need to gather more information about it.
Here are some parameters and techniques to do it.

1.G Analysis trace file with CBO EVENTS (10053)

The reason to know how to analyze a raw file for CBO, is because it helps you to see something is not obvious to you when you are doing an analysis, example the view where the condition was wrong.1