Cluster interconnect

 

If a block of data is on one node and the user asks for it on another node, oracle uses cache fusion to pass one block through the interconnect to the other node.Parallel processing relies on passing messages among multiple processors. Processors running parallel programs call for data and instructions,and then perform. calculations.Each processor checks back periodically with the other nodes or a master node to plan its next move or to synchronize the delivery of results.These activities rely on message-passing software, such as industry-standard Message Paassing interface(MPI).

  In parallel databases, there is a great deal of message passing and data blocks,or pages, transferring to the local cache of another node.Much of the functionality and performance depends on the efficiency of the transport medium or methodology. It becomes very critical for the overall performance of the cluster and usage of the parallel application. As the parallel databases do not impose any constraints on the nodes to which users can connect and access,users have a choice to connect to any node in the cluster.Irrespective of the nature of the application, OLTP , or data warehousing databases, the movement of data blocks from one node to another using the interconnect is widely  practiced. The role of the cluster interconnect to provide some kind of extended cache encompassing the cache from all the nodes is one of the most significant design features of the cluster. In general, the cluster interconnect is used for the following high-level functions:

Health, status, and synchronization of messages

Distributed lock manager messages

Accessing remote file systems

Application-specific traffic

Cluster alias routing

High performance , by distributing the computations across an array of nodes in the cluster, requires the cluster interconnect to provide a high data transfer rate and low latency communication between nodes. Also, the interconnect needs to be capable to detecting and isolating faults, and using alternative paths. Some of the essential requirements for the interconnect are

Low latency for short messages

High speed and sustained data rates for large messages

Low host-CPU utilization per message

Flow control, error control, and hearbeat continuity monitoring

Host interfaces that execute control programs to interact directly with host processes

Switch networks that scale well

 

Many of the cluster vendors have designed very competitive technology. Many of the interconnect products described next come close to the latency levels of a SMP(symmetric multiprocessing) bus. Table 11-1 summarizes the various interconnect capabilities(they will be faster yet by the time you read this).

The HP Memory channel : Memory channel interconnect is a high-speed network interconnect that provides applications with a cluster-wide address space. Applications map portions of this address space into their own virtual address space as 8kb pages and then read from or write into this address space just like normal memory.

 

Myrinet : Myrinet is a cost-effective, high-performance packet communication and switching technology . It is widely used in Linux clusters. Myrinet software supports most common hosts and operating systems. The software is supplied open source.

 

Scalable Interconnect (SCI) SCI is the Sun’s best-performing cluster interconnect because of its high data rate and low latency. Applications that stress the interconnect will scale better using SCI compared to using lower-performing alternatives. Sun SCI implements Remote Shared Memory(RSM), a feature that bypasses the TCP/IP communication overhead of Solaris. This improves cluster performance.

 

Veritas: Database Edition/Advanced cluster(DBE/AC) communications consist of LLT(low-latency transport) and GAB(Group Membership and Atomic Broadcast) services. LLT provides kernel-to-kernel communications and functions as a performance booster for the IP stack. Use of LLT rather than IP reduces latency and overhead with the IP stack. This is now known as Storage Foundations.

 

HP HyperFabric Hyper Mesasging Protocol(HMP) HP HyperFabric supports both standard TCP/UDP over IP and HP’s proprietary Hyper Messaging Protocol. HyperFabric extends the scalability and reliability of TCP/UPD by providing  transparent load balancing of connection traffic across multiple network interface cards. HMP coupled with OS bypass capability and the hardware support for protocol offload provides low latency and extremely low CPU utilization.

 

For building a high-performance oracle rac, selecting the right interconnect is important. Care should be taken to select the appropriate technology suitable for your environment. Check with your vendor to get the most up-to-date hardware that is available.

 

Table 11.1 ….

 

The key here is that going to disk is in the millisecond range, whereas going though the interconnect is in the microsecond or single-digit millisecond range.

cursor_sharing_exact hints的使用

主要当cursor_sharing=similar或者force的时候使用比较方便,

以下就是为cursor_sharing=similar的时候的一个sqltrace.

可以看到当使用hints cursor_sharing_exact后,

Misses in library cache during parse重新变为1,

也就是说oracle并不强制转换为bind var

与这个参数对应的也有cursor_sharing_similar.

alter session set sql_trace true

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

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 56 
********************************************************************************

select count(*)
from
 tt where a=:"SYS_B_0" and b=:"SYS_B_1"

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.03       0.03          0        106          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.03       0.03          0        106          0           2

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 56 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=53 pr=0 pw=0 time=13558 us)
      1   TABLE ACCESS FULL TT (cr=53 pr=0 pw=0 time=13473 us)

********************************************************************************

select /*+cursor_sharing_exact*/ count(*)
from
 tt where a=2 and b='kyp'

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         53          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          0         53          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 56 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=53 pr=0 pw=0 time=2726 us)
      1   TABLE ACCESS FULL TT (cr=53 pr=0 pw=0 time=2053 us)

********************************************************************************

select /*+cursor_sharing_exact*/ count(*)
from
 tt where a=1 and b='liu'

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

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 56 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=53 pr=0 pw=0 time=22016 us)
  27648   TABLE ACCESS FULL TT (cr=53 pr=0 pw=0 time=940507 us)

使用临时表来优化的存储过程

对于海量数据作检索的时候，通常会由于巨大的数据量，造成大量的排序和过滤。

SELECT TECHNOLOGY_RRN,
       TECHNOLOGY_ID,
       TECHNOLOGY_NAME,
       PART_RRN,
       PART_ID,
       LOT_TYPE_RRN,
       LOT_TYPE,
       STAGE_RRN,
       STAGE_ID,
       STAGE_ORDER,
       LOT_QTY AS LOT_BEGIN_QTY,
       MOVE_TARGET,
       WIP_TARGET,
       WIP_TARGET_BY_TECH,
       WIP_TARGET_BY_LOTTYPE
  FROM lot_wip_stage_day
 WHERE day_rrn = cnumDayRRN
   AND lot_qty <> 0
   AND (facility_rrn, TECHNOLOGY_ID, PART_ID, Lot_Type, STAGE_ID) NOT IN
       (SELECT s.FACILITY_RRN,
               s.TECHNOLOGY_ID,
               s.Part_ID,
               s.LOT_TYPE,
               s.STAGE_ID
          FROM lot_snapshot_summ s
         WHERE s.day_time = cdateMaxDayTime
           AND s.lot_status_category NOT IN
               ('SCHEDULE', 'COMPLETE', 'FINISH')
           AND substr(s.part_id, 1, 1) NOT IN ('W', 'M', 'D')
           AND substr(s.LOT_TYPE, 2, 1) IN ('P', 'E', 'T', 'C')
           AND s.stage_id is not null
           AND s.facility_rrn = InFaciRRN
         GROUP BY s.FACILITY_RRN,
                  s.TECHNOLOGY_ID,
                  s.Part_ID,
                  s.LOT_TYPE,
                  s.STAGE_ID);

其实类似于这样的sql语句，由于两表之间并不存在依赖关系，因此很难去从sql的写法上来优化，当然，这两个结果集之间分别来执行速度还是非常快的，但是一做not in操作，或者minus操作，就会慢100倍以上的速度，如果这时候，把两个结果集作为两个表
来处理，那么速度又是不一样的，这时候我们可以选择使用临时表把结果集存到临时表之后再作join操作，
先来看看之前的执行计划：

SQL> SELECT TECHNOLOGY_RRN,
  2         TECHNOLOGY_ID,
  3         TECHNOLOGY_NAME,
  4         PART_RRN,
  5         PART_ID,
  6         LOT_TYPE_RRN,
  7         LOT_TYPE,
  8         STAGE_RRN,
  9         STAGE_ID,
 10         STAGE_ORDER,
 11         LOT_QTY AS LOT_BEGIN_QTY,
 12         MOVE_TARGET,
 13         WIP_TARGET,
 14         WIP_TARGET_BY_TECH,
 15         WIP_TARGET_BY_LOTTYPE
 16    FROM lot_wip_stage_day
 17   WHERE day_rrn = 11222
 18     AND lot_qty <> 0
 19     AND (facility_rrn, TECHNOLOGY_ID, PART_ID, Lot_Type, STAGE_ID) NOT IN
 20         (SELECT s.FACILITY_RRN,
 21                 s.TECHNOLOGY_ID,
 22                 s.Part_ID,
 23                 s.LOT_TYPE,
 24                 s.STAGE_ID
 25            FROM lot_snapshot_summ s
 26           WHERE s.day_time = to_date('2006-01-01 07:30:00', 'yyyy-mm-dd hh24:mi:ss')
 27             AND s.lot_status_category NOT IN
 28                 ('SCHEDULE', 'COMPLETE', 'FINISH')
 29             AND substr(s.part_id, 1, 1) NOT IN ('W', 'M', 'D')
 30             AND substr(s.LOT_TYPE, 2, 1) IN ('P', 'E', 'T', 'C')
 31             AND s.stage_id is not null
 32             AND s.facility_rrn = 1
 33           GROUP BY s.FACILITY_RRN,
 34                    s.TECHNOLOGY_ID,
 35                    s.Part_ID,
 36                    s.LOT_TYPE,
 37                    s.STAGE_ID);

431 rows selected.

Elapsed: 00:032:43.60

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2033 Card=138 Bytes=
          15594)

   1    0   FILTER
   2    1     TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'LOT_WIP_STAGE_D
          AY' (Cost=2033 Card=138 Bytes=15594)

   3    2       INDEX (RANGE SCAN) OF 'PK_LWSTDF' (UNIQUE) (Cost=15 Ca
          rd=138)

   4    1     FILTER
   5    4       SORT (GROUP BY) (Cost=10 Card=1 Bytes=100)
   6    5         TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'LOT_SNAPSHO
          T_SUMM' (Cost=3 Card=1 Bytes=100)

   7    6           INDEX (RANGE SCAN) OF 'PK_LOTSNS' (UNIQUE) (Cost=2
           Card=1)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
   15644227  consistent gets
       1201  physical reads
          0  redo size
      54272  bytes sent via SQL*Net to client
       5225  bytes received via SQL*Net from client
         30  SQL*Net roundtrips to/from client
        525  sorts (memory)
          0  sorts (disk)
        431  rows processed

创建临时表，这种临时表是在session 结果以后oracle自己清除数据，关于更多的临时表信息可以参数oracle doc:

CREATE GLOBAL TEMPORARY TABLE temp_result
        (facility_rrn number(15),
         TECHNOLOGY_ID varchar2(32),
         part_id varchar2(32),
         lot_type varchar2(32),
         stage_id varchar2(32)
         )
      ON COMMIT PRESERVE ROWS ;

然后在存储过程中加上以下部分：
 insert into temp_result
   SELECT s.FACILITY_RRN,
          s.TECHNOLOGY_ID,
          s.Part_ID,
          s.LOT_TYPE,
          s.STAGE_ID
     FROM lot_snapshot_summ s
    WHERE s.day_time =
          to_date('2006-01-01 07:30:00', 'yyyy-mm-dd hh24:mi:ss')
      AND s.lot_status_category NOT IN ('SCHEDULE', 'COMPLETE', 'FINISH')
      AND substr(s.part_id, 1, 1) NOT IN ('W', 'M', 'D')
      AND substr(s.LOT_TYPE, 2, 1) IN ('P', 'E', 'T', 'C')
      AND s.stage_id is not null
      AND s.facility_rrn = 1
    GROUP BY s.FACILITY_RRN,
             s.TECHNOLOGY_ID,
             s.Part_ID,
             s.LOT_TYPE,
             s.STAGE_ID

并将文中开始提到的sql语句改为：

SELECT TECHNOLOGY_RRN,
       TECHNOLOGY_ID,
       TECHNOLOGY_NAME,
       PART_RRN,
       PART_ID,
       LOT_TYPE_RRN,
       LOT_TYPE,
       STAGE_RRN,
       STAGE_ID,
       STAGE_ORDER,
       LOT_QTY AS LOT_BEGIN_QTY,
       MOVE_TARGET,
       WIP_TARGET,
       WIP_TARGET_BY_TECH,
       WIP_TARGET_BY_LOTTYPE
  FROM lot_wip_stage_day
 WHERE day_rrn = 11222
   AND lot_qty <> 0
   AND (facility_rrn, TECHNOLOGY_ID, PART_ID, Lot_Type, STAGE_ID) NOT IN
       (select * from temp_result);

然后再来看这个执行计划：

SQL> SELECT TECHNOLOGY_RRN,
  2         TECHNOLOGY_ID,
  3         TECHNOLOGY_NAME,
  4         PART_RRN,
  5         PART_ID,
  6         LOT_TYPE_RRN,
  7         LOT_TYPE,
  8         STAGE_RRN,
  9         STAGE_ID,
 10         STAGE_ORDER,
 11         LOT_QTY AS LOT_BEGIN_QTY,
 12         MOVE_TARGET,
 13         WIP_TARGET,
 14         WIP_TARGET_BY_TECH,
 15         WIP_TARGET_BY_LOTTYPE
 16    FROM lot_wip_stage_day
 17   WHERE day_rrn = 11222
 18     AND lot_qty <> 0
 19     AND (facility_rrn, TECHNOLOGY_ID, PART_ID, Lot_Type, STAGE_ID) NOT IN
 20         (select * from temp_result);

431 rows selected.

Elapsed: 00:00:00.48

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2033 Card=138 Bytes=
          15594)

   1    0   FILTER
   2    1     TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'LOT_WIP_STAGE_D
          AY' (Cost=2033 Card=138 Bytes=15594)

   3    2       INDEX (RANGE SCAN) OF 'PK_LWSTDF' (UNIQUE) (Cost=15 Ca
          rd=138)

   4    1     TABLE ACCESS (FULL) OF 'TEMP_RESULT' (Cost=16 Card=1 Byt
          es=85)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
       2084  db block gets
       3128  consistent gets
          0  physical reads
          0  redo size
      54272  bytes sent via SQL*Net to client
       5225  bytes received via SQL*Net from client
         30  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        431  rows processed

SQL>

对比前后，优化前的逻辑读:15644227,执行时间为00:32:43.60
优化后：3128，时间：00:00:00.48

当然，并不是说所有情况都适合使用临时表，有时候可能使用array更加合适，
具体情况具体对待。"fast=true"是不可能存在的。

EXPLAIN PLAN WHEN USING PARTITIONS

Table partitions yield different outputs for their EXPLAIN PLANs (as shown in the following listing).Here, we create a  table with three partitions and a partitioned index. Broadly speaking, partitions are tables stored in multiple places in the database. For more information on partitioning tables

 

SQL> create table dept1(deptno number(2), dept_name varchar2(30))

  2  partition by range(deptno)

  3  (partition d1 values less than (10),

  4   partition d2 values less than (20),

  5   partition d3 values less than (maxvalue));

 

Table created.

 

SQL> insert into dept1 values(1,'dept1');

 

1 row created.

 

SQL> insert into dept1 values(7,'dept7');

 

1 row created.

 

SQL> insert into dept1 values(10,'dept10');

 

1 row created.

 

SQL> insert into dept1 values(15,'dept15');

 

1 row created.

 

SQL> insert into dept1 values(22,'dept22');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> create index dept_index on dept1(deptno)

  2  local

  3  (partition d1,

  4   partition d2,

  5   partition d3);

 

Index created.

 

SQL>

 

We now generate an EXPLAIN PLAN that forces a full table scan to access the first two partitions.

 

SQL> explain plan for

  2      select  * from dept1

  3       where deptno||''=1

  4          or deptno||''=15;

 

Explained.

 

 

SQL>  select * from table(dbms_xplan.display());

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 2415070041

 

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

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT    |       |     2 |    60 |     2   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE ALL|       |     2 |    60 |     2   (0)| 00:00:01 |     1 |     3 |

|*  2 |   TABLE ACCESS FULL | DEPT1 |     2 |    60 |     2   (0)| 00:00:01 |     1 |     3 |

-----

 

SQL>

 

The preceding examples shows that a full table scan on the DEPT1 table is performed. All three partitions are scanned. The starting partition is 1 and the ending partition is 3.

 

Next, an EXPLAIN plan is generated in the following listing for an index range scan of partition2 only(ensure that you delete from the plan table to clear it).

 

explain plan for

    select  * from dept1

     where deptno=1;

 

We now generate an EXPLAIN PLAN  for an index range scan accessing only the second partition:

 

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

| Id  | Operation                          | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT                   |            |     1 |    30 |     3   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE            |            |     1 |    30 |     3   (0)| 00:00:01 |     1 |     1 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| DEPT1      |     1 |    30 |     3   (0)| 00:00:01 |     1 |     1 |

|*  3 |    INDEX RANGE SCAN                | DEPT_INDEX |     1 |       |     2   (0)| 00:00:01 |     1 |     1 |

 

 

This output shows that the only partition of the table OR index that is accessed is the second partition. This is because the value for deptno=1 is within the second partition of the DEPT1 table.

The DEPTNO column is also indexes, and this value is also within the second partition of the index.

 

TIP：Partition can also be viewed by the EXPLAIN PLAN by accessing the columns PARTITION_STOP and PARTITION_START in the PLAN_TABLE table.

Using dbms_monitor

 

SQL> select sid,serial# ,username from v$session;

 

       SID    SERIAL# USERNAME

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

       140         57

       145          1

       147          1

       150          1

       153         34 SYS

       155          1

       156          1

       159          5 TEST

       160          1

       161          1

       162          1

 

       SID    SERIAL# USERNAME

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

       163          1

       164          1

       165          1

       166          1

       167          1

       168          1

       169          1

       170          1

 

19 rows selected.

 

SQL> exec dbms_monitor.session_trace_enable(159,5,true,false);

 

 

 

The third parameter is for waits( default is TRUE),and the fourth parameter is for bind variables(default is false)

 

 

To turn off the trace:

 

SQL>exec dbms_monitor.session_trace_disable(159,5).

 

 

 

To trace the current session,set the SID the serial# to null;

 

Exec dbms_monitor.session_trace_enable(null,null);

 

 

Setting trace based on client identifier

 

To set the trace based on client identifier as the user, run the following:

 

SQL> exec dbms_session.set_identifier('client.liu');

 

 

To verify the client identifier,

 

SQL>  select sid,serial#,username,client_identifier from v$session where client_identifier is not null;

 

       SID    SERIAL# USERNAME                       CLIENT_IDENTIFIER

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

       136         37 TEST                           client.liu

       159          5 TEST                           tony.liu

SQL>

 

 

Now we can set the trace for this client identifier:

 

SQL> exec dbms_monitor.client_id_trace_enable('client.liu',true,false);

 

The second parameter is for waits(default is TRUE), and the third parameter is for bind variables(default is FALSE)

 

 

To disable this client identifier trace,

SQL> exec dbms_monitor.client_id_trace_disable('client.liu');

 

 

Setting Trace for the Service Name/Module Name/Action Name

In order to use the action name, the module name and the service name must be present, in order to use the module name, the service name must be present. Tracing will be enable for a given combination of service name, module name,and action name globally for a database unless an instance name is specified for a procedure. The service name is is determined by the connect string used to connect to a service.

  An Oracle database is represented to clients as a service; that is, the database performs work on behalf of clients.  A database can have one or more services associated with it. For example, you could have one database with two different services for web clients : book.us.acme.com for clients making book purchases and soft.us.acme.com for clients making software purchases. In this example ,the database name is sales.acme.com, so the service name isn’t even based on the database name. The service name is specified by the SERVICE_NAMES parameter in the initialization parameter file. The service name defaults to the global database name,a name comprising the database name(DB_NAME parameter) and the domain name(DB_DOMAIN parameter).

To enable tracing for a service name,

 

SQL>exec dbms_monitor.serv_mod_act_trace_enable(service_name=>’orcl’);

 

This will trace all sessions with a service name of  orcl.

 

To enable tracing for a combination service,module , and action.

 

SQL>exec dbms_monitor.serv_mod_act_trace_enable(service_name=>’orcl’,module_name=>’salary_update’,action_name=>’insert_item’);

 

 

To Disable tracing in the preceding code, use the procedure

SERV_MOD_ACT_TRACE_DISABLE, as shown here:

 SQL>exec

dbms_monitor.serv_mod_act_trce_disable(service_name=>’orcl’, module_name=>’salary_update’,action_name=>’insert_item’);

 

To trace for entire db or instance(not recommended),

Execute dbms_monitor.database_trace_enable(‘waits=>true,binds=>false,instance_name=>’orcl’);

 

Enable tracing views

Dba_enabled_traces

Dba_enabled_aggregations

 

Example:

SQL> execute dbms_monitor.client_id_stat_enable('client.liu');

 

PL/SQL procedure successfully completed.

 

SQL> select * from dba_enabled_aggregations;

 

AGGREGATION_TYPE      PRIMARY_ID                                                       QUALIFIER_ID1

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

QUALIFIER_ID2

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

CLIENT_ID             client.liu

 

 

 

Trcsess multiple trace files into one file

 

The oracle 10g allows trace data to be selectively extracted from multiple trace files and saved into a single trace file based on criteria such as session ID or module name. This command-line utility is especially useful in connection pooling and shared server configurations, where each user request could end up in a separate trace file. TRCSESS lets you obtain consolidated trce information pertaining to a single user session.

 

Thie consolidated trace file can be created according to several criteria:

 

Session id

Client id

Service name

Action name

Module name

 

Example 1

 Trcsess utput=trace.trc service=’orcl’

 

Example 2

Exec dbms_session.set_identifier(‘client’);

 

Exec dbms_monitor.client_id_stat_enable(‘client’);

 

Trcsess utput=trace.trc client=client *.trc