Databases Reference
In-Depth Information
the table
SYS.SOURCE$
for testing parallel query. Both a
FULL
and a
PARALLEL
hint are necessary
to scan the table in parallel. Event 10046 is used to enable tracing, since the procedure
DBMS_
MONITOR.SESSION_TRACE_ENABLE
has no effect on parallel execution processes. Embedding the
auditing session identifier in the client identifier guarantees a unique client identifier.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Session altered.
SQL> VARIABLE client_identifier VARCHAR2(64)
SQL> EXEC :client_identifier:='pqtest_' || userenv('sessionid')
PL/SQL procedure successfully completed.
SQL> PRINT client_identifier
CLIENT_IDENTIFIER
-----------
pqtest_1894
SQL> EXEC dbms_session.set_identifier(:client_identifier)
PL/SQL procedure successfully completed.
SQL> SELECT /*+ FULL(s) PARALLEL (s ,4) */ count(*) FROM sys.source$ s;
COUNT(*)
----------
298767
SQL> SELECT statistic, last_query
FROM v$pq_sesstat
WHERE statistic='Queries Parallelized';
STATISTIC LAST_QUERY
------------------------------ ----------
Queries Parallelized 1
SQL> EXEC dbms_session.set_identifier(NULL)
PL/SQL procedure successfully completed.
The query on
V$PQ_SESSTAT
confirms that the
SELECT
statement ran in parallel. At this
point, the client identifier has been emitted to four trace files from parallel execution processes.
C:\oracle\admin\orcl\bdump> grep pqtest_1894 *.trc
orcl_p000_5412.trc:*** CLIENT ID:(pqtest_1894) 2007-08-31 23:14:38.421
orcl_p001_2932.trc:*** CLIENT ID:(pqtest_1894) 2007-08-31 23:14:38.421
orcl_p002_4972.trc:*** CLIENT ID:(pqtest_1894) 2007-08-31 23:14:38.421
orcl_p003_1368.trc:*** CLIENT ID:(pqtest_1894) 2007-08-31 23:14:38.421
TRCSESS is used to combine the trace files of the four parallel execution processes and the
trace file of the query coordinator into a single trace file called
pqtest_1894.trc
. The client
identifier is passed to TRCSESS with the option
clientid
. Since TRCSESS supports wildcards
and scans all files that match, it is sufficient to pass
*.trc
for the local directory
bdump
and
..\udump\*.trc
for the directory where the query coordinator trace file resides as the input file
specification.
C:\oracle\admin\orcl\bdump> trcsess output=pqtest_1894.trc clientid=pqtest_1894 *.tr
c ..\udump\*.trc
