Tuning Global Cache - Expert Oracle RAC Performance Diagnostics and Tuning - page 412

FROM gv$policy_history
WHERE data_object_id IN (SELECT data_object_id
FROM gv$policy_history
GROUP BY data_object_id
HAVING Count (*) > 19)
ORDER BY data_object_id,
inst_id;
Data Target Event
Inst Policy Event Object ID Inst # Date
---- -------------------- ---------- ------- --------------------
2 push_affinity 468751 4 02/04/2014 09:24:34
2 push_affinity 468751 5 02/03/2014 21:33:53
2 push_affinity 468751 3 02/01/2014 07:39:58
3 dissolve_affinity 468751 3 02/02/2014 11:31:43
3 push_affinity 468751 1 02/08/2014 02:30:00
3 push_affinity 468751 5 02/04/2014 16:45:01
3 push_affinity 468751 5 02/01/2014 12:40:16
3 push_affinity 468751 2 02/02/2014 19:02:15
4 push_affinity 468751 1 01/31/2014 15:08:57
4 push_affinity 468751 3 02/02/2014 01:21:01
4 push_affinity 468751 5 02/01/2014 03:09:40
4 push_affinity 468751 1 02/04/2014 11:24:40
4 push_affinity 468751 2 02/04/2014 20:15:13
5 dissolve_affinity 468751 5 02/08/2014 14:20:46
5 push_affinity 468751 4 02/04/2014 16:55:02
5 push_affinity 468751 2 02/04/2014 08:44:32
5 push_affinity 468751 2 02/01/2014 07:29:57
5 push_affinity 468751 4 02/01/2014 18:50:39
Based on the type of transactions (shared, exclusive), the objects are mastered in the respective instances, providing
different access rights to the sessions accessing these objects. The following output from X$OBJECT_POLICY_STATISTICS
gives current statistics on the type of access the objects have on the instances that are currently being mastered. The
SOPENS column indicates the number of times the object has been master on the instance in shared mode. XOPENS
indicates exclusive mode, and XREFS indicates exclusive reference:
Script: MVRACPDnTap_objpolicystats.sql
SELECT object OBJID,
object_name ONAME,
Decode (object_type, 'TABLE PARTITION', 'TP',
'TABLE SUBPARTITION', 'TSP',
'TABLE', 'T',
'INDEX SUBPARTITION', 'ISP',
'INDEX PARTITION', 'IP',
'CLUSTER', 'C',
'SEQUENCE', 'S',
'INDEX', 'I') OT,
node,
sopens,
xopens,
xfers,
dirty