Oracle RAC Cache Fusion

Until Oracle 8i released, Oracle Introduced Parallel Server on Oracle 6i version. Oracle Introduced Real Application Cluster concept on Oracle 8i version. Oracle Cache Fusion is one of the important component in Oracle RAC environment.

What is Cache Fusion:

Oracle RAC is composed of two or more instances. Oracle RAC Cache Fusion is mechanism to transfer data blocks from the one instance’s buffer cache to another instance’s buffer cache across the cluster interconnect. When a block of data is read from disk by an instance within the cluster and another instance is in need of the same block, it is easy to get the block image from the instance which has the block in its SGA rather than reading from the disk. Transfers across this private network are in order of magnitude better than disk I/O activity and they help Oracle RAC performance.

Cache Fusion Ships Blocks from Cache to Cache Across the Interconnect:

Buffer Cache, Shared Pool and the Undo Tablespace like single-instance database are required to facilitate the Cache Fusion. But Extra coordination is needed in the cluster to make a collection of instances work together.

Before explained how Cache Fusion works, We will know –

Global Resource Directory (GRD): On RAC, GRD process keeps track of the resources in the cluster. There is no true concept of a master node in Oracle RAC but each instance of cluster can becomes the resources mater.

  • Track master instances of all buffers.
  • GRD is present on all the instances of the cluster.

How to find the master node for a resource in RAC:

SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
    WHERE OBJECT_NAME = 'TBL_CFUSION';  

DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         93836      93836

SQL> SELECT b.dbablk, r.kjblmaster master_node
FROM x$le l, x$kjbl r, x$bh b
WHERE b.le_addr = l.le_addr and l.le_kjbl = r.kjbllockp and b.obj = &Data_Object_ID;

Enter value for data_object_id: 93836
old   3: WHERE b.obj = &Data_Object_ID and b.le_addr = l.le_addr and l.le_kjbl = r.kjbllockp
new   3: WHERE b.obj = 93836 and b.le_addr = l.le_addr and l.le_kjbl = r.kjbllockp

    DBABLK MASTER_NODE
---------- -----------
       225           1
       230           1
       227           1
       224           1
       229           1
       226           1
       231           1
       228           1

8 rows selected.

Global Cache Services (GCS): Global Cache Services are responsible to transfer blocks from one instance to another. A single-instance database relies on enqueues (locks) to protect for modifying the same records by two processes simultaneously. Buffer Cache on all instances appear to be global and enqueues on the resources is also global across the cluster.

LMS – LMS is a GCS process. This process used to called the Lock Manager Server.

#Use the following syntax to query V$SYSSTAT:
SQL> SELECT NAME,VALUE FROM V$SYSSTAT WHERE NAME LIKE '%global cache%';
SQL>

Global Enqueue Services (GES): Global Enqueue Services is responsible for managing locks across the cluster. GES was previously called the Distributed/Dynamic Lock Manager (DLM).

  • Holds the information on the locks on the buffers.
  • Each lock has a name shown in V$LOCK_ELEMENT (or X$LE).
  • If a buffer is locked, the lock element name is shown in V$BH.LOCK_ELEMENT.
SQL> UPDATE SAMAD.TBL_CFUSION SET NAME='Y'     WHERE ID=4;

1 row updated.

SQL> INSERT INTO SAMAD.TBL_CFUSION VALUES (3,'X');

1 row created.

SQL> DELETE FROM SAMAD.TBL_CFUSION WHERE ID =2;

1 row deleted.

SQL> Select LOCK_ELEMENT_NAME,MODE_HELD,CLASS,LOCK_ELEMENT_ADDR
From V$LOCK_ELEMENT
where LOCK_ELEMENT_ADDR in (SELECT LOCK_ELEMENT_ADDR FROM V$BH
WHERE OBJD IN (SELECT DATA_OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'TBL_CFUSION'));  

LOCK_ELEMENT_NAME  MODE_HELD      CLASS LOCK_ELEMENT_ADD
----------------- ---------- ---------- ----------------
              225          2          6 0000000077FDF6E8
              230          2          6 0000000087BDD960
              227          1          6 00000000877DA828
              224          2          6 0000000077FD99F0
              229          1          6 0000000087BE7168
              226          1          6 0000000087BECA00
              231          2          6 0000000087BE5098
              228          1          6 00000000877DB660

8 rows selected.

SQL> SELECT ADDR,LE_ADDR,LE_ID1,LE_RLS,LE_MODE,LE_LOCAL
    FROM X$LE
    WHERE LE_ADDR IN (SELECT LOCK_ELEMENT_ADDR FROM V$BH
                WHERE OBJD IN (SELECT DATA_OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'TBL_CFUSION'));  

ADDR             LE_ADDR              LE_ID1     LE_RLS    LE_MODE   LE_LOCAL
---------------- ---------------- ---------- ---------- ---------- ----------
00007F87B4376810 0000000077FDF6E8        225          0          2          1
00007F87B4376810 0000000087BDD960        230          0          2          1
00007F87B4376810 00000000877DA828        227          0          1          1
00007F87B4376810 0000000077FD99F0        224          0          2          1
00007F87B4376810 0000000087BE7168        229          0          1          1
00007F87B4376810 0000000087BECA00        226          0          1          1
00007F87B4376810 0000000087BE5098        231          0          2          1
00007F87B4376810 00000000877DB660        228          0          1          1

8 rows selected.

Note::: MODE_HELD : Platform dependent value for lock mode held; often: 3 = share; 5 = exclusive

How GES Workloads Affect Performance:

Calculate the ratio of local-to-remote global enqueue resource operations using this query:

SELECT 
    r.CONVERT_TYPE,
    SUM(r.AVERAGE_CONVERT_TIME),
    SUM(l.AVERAGE_CONVERT_TIME),
    SUM(r.CONVERT_COUNT),
    SUM(l.CONVERT_COUNT)
   FROM V$GES_CONVERT_LOCAL l, V$GES_CONVERT_REMOTE r
   WHERE r.convert_count <> 0 OR l.convert_count <> 0
   GROUP BY r.CONVERT_TYPE;

LMON: LMON process is the GES master process.
LMD – Lock Manager Daemon. This process manages incoming lock requests.
LCK0 – The instance enqueue process. This process manages lock requests for library Cache objects.

How Cache Fusion Works:

How Oracle RAC read block from disk or buffer of other Instance using Cache Fusion?

  • Object is not available on Buffer. See the output from V$BH.
  • 1st time run select query – SELECT ID FROM SAMAD.TBL_CFUSION
  • Collect sql_trace of the query and generate TKPROF report.

Node 1:

When we run any query in database first time or blocks are not available in cache then all blocks read from database file for rac and stand alone database.

SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
    WHERE OBJECT_NAME = 'TBL_CFUSION';  

DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         93836      93836

SQL> @obj_buffer.sql
Enter value for objid: 93836
old  21: WHERE OBJD= &OBJID
new  21: WHERE OBJD= 93836

no rows selected

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

Session altered.

SQL> SELECT ID FROM SAMAD.TBL_CFUSION;

        ID
----------
         1
         1
         4
         3

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

Session altered.

TKPROF output. There are 6 IOs from disk of this query.

SELECT ID FROM SAMAD.TBL_CFUSION;

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.00       0.00          6          8          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          6          8          0           4

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         4          4          4  TABLE ACCESS FULL TBL_CFUSION (cr=8 pr=6 pw=0 time=2709 us cost=3 size=12 card=4)
SQL> select s.name, st.value
  from v$statname s, v$mystat st
  where st.STATISTIC# = s.STATISTIC#
  and s.name in ('session logical reads','physical reads');  

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session logical reads                                                  3677
physical reads                                                           99

Node 2:

  • Collect total physical and logical IO information.
  • Object is not available in Buffer Cache on 2nd Instance.
  • Select query – SELECT ID FROM SAMAD.TBL_CFUSION with enabling SQL Trace.
  • Before and After execution of the query, Physical read is 1 on this session.
SQL> select s.name, st.value
  from v$statname s, v$mystat st
  where st.STATISTIC# = s.STATISTIC#
  and s.name in ('session logical reads','physical reads');  

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session logical reads                                                  1555
physical reads                                                            1

SQL> @obj_buffer.sql
Enter value for objid: 93836
old  21: WHERE OBJD= &OBJID
new  21: WHERE OBJD= 93836

no rows selected

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

Session altered.

SQL> SELECT ID FROM SAMAD.TBL_CFUSION;

        ID
----------
         1
         1
         4
         3

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

Session altered.

SQL>  select s.name, st.value
  from v$statname s, v$mystat st
  where st.STATISTIC# = s.STATISTIC#
  and s.name in ('session logical reads','physical reads');  

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session logical reads                                                  3026
physical reads                                                            1

There is no physical IO for the query execution even object was not in Buffer cache. Means all required blocks for this object retrieved from 1st Instance buffer cache instead of Disk.

SQL ID: 0yx6m42qawyb5 Plan Hash: 4124388744

SELECT ID FROM SAMAD.TBL_CFUSION

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

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         4          4          4  TABLE ACCESS FULL TBL_CFUSION (cr=8 pr=0 pw=0 time=1263 us cost=3 size=12 card=4)

How Oracle RAC maintain blocks in buffer across the cluster for DML?

When a block is requested by user:

  • The buffer cache is searched on local buffer cache
    • If not found, there are two options
      • Get from the other cache across the cluster
      • Get from disk
    • If found on buffer on other instance, there are three options:
      • Send the buffer to the user
      • Examine other caches for the presence of this buffer
      • Get from the disk
  • How does it decide which option to take?
    • The buffer can be retrieved in two modes
      • Consistent Read (CR) -> Block contains uncommitted changes. Session will get a version of the block prior to the changes. Intention to read block
      • Current -> If intention to modify
  • There can be several CR copies of a buffer
  • There can be only one current mode
    • For an instance
  • Each current buffer is Shared Current
  • Only one buffer in the entire cluster can be Exclusive Current
SQL> show user
USER is "SAMAD"
SQL> Create Table SAMAD.TBL_CFTEST(id number, name char(2000));

insert into SAMAD.TBL_CFTEST Values(1, 'Samad');

insert into SAMAD.TBL_CFTEST Values(2, 'Samad');

insert into SAMAD.TBL_CFTEST Values(3, 'Samad');

insert into SAMAD.TBL_CFTEST Values(4, 'Samad');
Table created.

SQL> SQL>
1 row created.

SQL> SQL>
1 row created.

SQL> SQL>
1 row created.

SQL> SQL>

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT EXTENT_ID, BLOCK_ID, BLOCKS FROM DBA_EXTENTS
WHERE SEGMENT_NAME = 'TBL_CFTEST';  

 EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0        232          8

SQL> SELECT ID,
        DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
        DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBL_CFTEST;  

        ID      FILE#     BLOCK#
---------- ---------- ----------
         1          6        238
         2          6        238
         3          6        238
         4          6        239

SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
    WHERE OBJECT_NAME = 'TBL_CFTEST';  

DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         93861      93861

For DML, rows on block status is xcur (exclusive current) and these are most recent current copy of block. xcur status for a rows is possible a copy on an instance across the cluster.

SQL> @obj_buffer.sql
Enter value for data_obj_id: 93861
old   9: where objd = &data_obj_id
new   9: where objd = 93861

     FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
         6        232 1st level bmb      xcur       00000000877DADA0
         6        233 2nd level bmb      xcur       00000000877E5958
         6        234 segment header     xcur       0000000087BF7C48
         6        235 data block         xcur       0000000087BEED00
         6        236 data block         xcur       00000000877D96A8
         6        237 data block         xcur       00000000877DBF20
         6        238 data block         xcur       00000000877E7C58
         6        239 data block         xcur       00000000877E4490

8 rows selected.

Node 2:

SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
    WHERE OBJECT_NAME = 'TBL_CFTEST'; 

DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         93861      93861

TBL_CFTEST object is not available in buffer cache on local instance. When run the select command of this table,

SQL> @obj_buffer.sql
Enter value for objid: 93861
old  21: WHERE OBJD= &OBJID
new  21: WHERE OBJD= 93861

no rows selected

SQL> SELECT INSTANCE_NUMBER FROM V$INSTANCE;

INSTANCE_NUMBER
---------------
              2

SQL> SELECT ID FROM SAMAD.TBL_CFTEST WHERE ID = &ID;
Enter value for id: 1
old   1: SELECT ID FROM SAMAD.TBL_CFTEST WHERE ID = &ID
new   1: SELECT ID FROM SAMAD.TBL_CFTEST WHERE ID = 1

        ID
----------
         1

These blocks are available in Node 1 which is resource master.

SQL> select b.dbablk, r.kjblmaster master_node
from x$le l, x$kjbl r, x$bh b
where b.le_addr = l.le_addr
and l.le_kjbl = r.kjbllockp and b.obj = &DataObjectId;  2    3    4
Enter value for dataobjectid: 93861
old   4: and l.le_kjbl = r.kjbllockp and b.obj = &DataObjectId
new   4: and l.le_kjbl = r.kjbllockp and b.obj = 93861

    DBABLK MASTER_NODE
---------- -----------
       238           1
       235           1
       237           1
       234           1
       239           1
       236           1

6 rows selected.

Selected only one record and it should return the relevant block (block # 238) and it copied all blocks as there was no index and it reads all block for full table scan.

When oracle cache fusion request a block for reading from another instance, block copies as Consistence Read (CR) mode from other instance then cope the same block as scur mode. Oracle remain CR mode block because that point user request to read these block. SCUR (Shared Current) means these rows are up to date for across the cluster and Oracle can rely this row for any further changes in cluster.

In this point on node 2, have two copies of the blocks.

SQL> @obj_buffer.sql
Enter value for objid: 93861
old  21: WHERE OBJD= &OBJID
new  21: WHERE OBJD= 93861

     FILE#     BLOCK# CLASS TYPE         STATUS     LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
         6        234 SEGMENT HEADER     cr         00
         6        234 SEGMENT HEADER     scur       00000000803FA178
         6        235 DATA BLOCK         scur       00000000803F1230
         6        235 DATA BLOCK         cr         00
         6        236 DATA BLOCK         scur       0000000087BE01D8
         6        236 DATA BLOCK         cr         00
         6        237 DATA BLOCK         scur       0000000087BE4D50
         6        237 DATA BLOCK         cr         00
         6        238 DATA BLOCK         cr         00
         6        238 DATA BLOCK         scur       0000000087BE6448
         6        239 DATA BLOCK         cr         00

     FILE#     BLOCK# CLASS TYPE         STATUS     LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
         6        239 DATA BLOCK         scur       0000000087BE00C0

12 rows selected.

Now on Node 1, rows status changed from xcur to scur means these block’s most recent copies are available in another nodes. in this case, we saw that these blocks copies to node 2 as scur. If we have any rows status xcur means these are exclusively current across the cluster.

XCUR and SCUR both mode for a row in buffer is not possible at the same time.

SCUR on both nodes means Oracle will consider any one instance for these rows as these are up to date on both nodes.

SQL> @obj_buffer.sql
Enter value for data_obj_id: 93861
old   9: where objd = &data_obj_id
new   9: where objd = 93861

     FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
         6        234 segment header     scur       0000000087BF7C48
         6        235 data block         scur       0000000087BEED00
         6        236 data block         scur       00000000877D96A8
         6        237 data block         scur       00000000877DBF20
         6        238 data block         scur       00000000877E7C58
         6        239 data block         scur       00000000877E4490

8 rows selected.

Now try to update a row on Instance 1, and we see that row status has been changed to xcur and also remain with CR copy for read consistence.

SQL> UPDATE SAMAD.TBL_CFTEST SET NAME='Y' WHERE ID=2;

1 row updated.

SQL> @obj_buffer.sql
Enter value for data_obj_id: 93861
old   9: where objd = &data_obj_id
new   9: where objd = 93861

     FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
         6        238 data block         xcur       00000000877E7C58
         6        238 data block         cr         00
         6        239 data block         scur       00000000877E4490

3 rows selected.

Now on Node 2, Now we got two cr copies for block# 238 and no scur copy as we had xcur on Node 1.

SQL> @obj_buffer.sql
Enter value for objid: 93861
old  21: WHERE OBJD= &OBJID
new  21: WHERE OBJD= 93861

     FILE#     BLOCK# CLASS TYPE         STATUS     LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
         6        238 DATA BLOCK         cr         00
         6        238 DATA BLOCK         cr         00
         6        239 DATA BLOCK         cr         00
         6        239 DATA BLOCK         scur       0000000087BE00C0

4 rows selected.

SQL> SELECT ID,
        DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
        DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBL_CFTEST;  

        ID      FILE#     BLOCK#
---------- ---------- ----------
         1          6        238
         2          6        238
         3          6        238
         4          6        239

Another row on same block update on Node 2, and we see 4 CR copies and 1 XCUR copy.

SQL> UPDATE SAMAD.TBL_CFTEST SET NAME='Y' WHERE ID=3;

1 row updated.

SQL> @obj_buffer.sql
Enter value for objid: 93861
old  21: WHERE OBJD= &OBJID
new  21: WHERE OBJD= 93861

     FILE#     BLOCK# CLASS TYPE         STATUS     LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
         6        238 DATA BLOCK         cr         00
         6        238 DATA BLOCK         cr         00
         6        238 DATA BLOCK         cr         00
         6        238 DATA BLOCK         xcur       0000000087BE6448
         6        238 DATA BLOCK         cr         00
         6        239 DATA BLOCK         scur       0000000087BE00C0
         6        239 DATA BLOCK         cr         00

7 rows selected.

On node 1, previously updated record (LOCK_ELEMENT_ADD-> 00000000877E7C58) status changed to Past Image (PI) mode.

SQL> @obj_buffer.sql
Enter value for data_obj_id: 93861
old   9: where objd = &data_obj_id
new   9: where objd = 93861

     FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
         6        238 data block         pi         00000000877E7C58
         6        238 data block         cr         00
         6        238 data block         cr         00
         6        238 data block         cr         00
         6        239 data block         scur       00000000877E4490

5 rows selected.

Once made checkpoint, PI changed to CR.

SQL> alter system checkpoint;

System altered.

SQL> @obj_buffer.sql
Enter value for data_obj_id: 93861
old   9: where objd = &data_obj_id
new   9: where objd = 93861

     FILE#     BLOCK# CLASS_TYPE         STATUS     LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
         6        238 data block         cr         00
         6        238 data block         cr         00
         6        238 data block         cr         00
         6        238 data block         cr         00
         6        239 data block         scur       00000000877E4490

5 rows selected.

If we compare IO statistic, we see that more physical IO on Node 1 than Node 2. And “gc cr blocks received” value is 8.

Node 1: IO details

SQL> SELECT statistic_name, value
FROM v$segstat
WHERE dataobj# = &data_object_id and value > 0;  
Enter value for data_object_id: 93861
old   3: WHERE dataobj# = &data_object_id and value > 0
new   3: WHERE dataobj# = 93861 and value > 0

STATISTIC_NAME                                                        VALUE
---------------------------------------------------------------- ----------
logical reads                                                            80
db block changes                                                         32
physical writes                                                           8
physical write requests                                                   4
space used                                                             9257
space allocated                                                       65536

6 rows selected.

Node 2: IO details

SQL> SELECT statistic_name, value
FROM v$segstat
WHERE dataobj# = &data_object_id and value > 0;  

Enter value for data_object_id: 93861
old   3: WHERE dataobj# = &data_object_id and value > 0
new   3: WHERE dataobj# = 93861 and value > 0

STATISTIC_NAME                                                        VALUE
---------------------------------------------------------------- ----------
logical reads                                                            48
db block changes                                                         16
physical reads                                                            6
physical writes                                                           1
physical read requests                                                    2
physical write requests                                                   1
gc cr blocks received                                                     8
gc current blocks received                                                1

8 rows selected.

Used Scripts:

[oracle@ocmnode2 ~]$ cat obj_buffer.sql
SELECT FILE#, BLOCK#, DECODE(CLASS#,
1, 'DATA BLOCK',
2, 'SORT BLOCK',
3, 'SAVE UNDO BLOCK',
4, 'SEGMENT HEADER',
5, 'SAVE UNDO HEADER',
6, 'FREE LIST',
7, 'EXTENT MAP',
8, '1ST LEVEL BMB',
9, '2ND LEVEL BMB',
10, '3RD LEVEL BMB',
11, 'BITMAP BLOCK',
12, 'BITMAP INDEX BLOCK',
13, 'FILE HEADER BLOCK',
14, 'UNUSED',
15, 'SYSTEM UNDO HEADER',
16, 'SYSTEM UNDO BLOCK',
17, 'UNDO HEADER',
18, 'UNDO BLOCK') "CLASS TYPE", STATUS, LOCK_ELEMENT_ADDR
FROM V$BH
WHERE OBJD= &OBJID
ORDER BY 1,2,3
/
[oracle@ocmnode2 ~]$ cat obj_stat.sql
SELECT statistic_name, value
FROM v$segstat
WHERE dataobj# = &data_object_id and value > 0
/

select s.name, st.value
  from v$statname s, v$mystat st
  where st.STATISTIC# = s.STATISTIC#
  and s.name in ('session logical reads','physical reads');

Leave a Reply