03 October, 2016

Obj# and DataObj# in 12c AWR

The 12c AWR Report section on Segment Statistics now reports both Obj# (OBJECT_ID) and DataObj# (DATA_OBJECT_ID).  This is useful information when you have (table) objects undergoing TRUNCATEs during the workload.
A TRUNCATE (or a MOVE {or REBUILD for an Index}) causes a reallocation of the data segment and change of the DATA_OBJECT_ID.

Thus :

SQL> show user
USER is "HEMANT"
SQL> create table hkc_t_1 (id_col number);

Table created.

SQL> select object_id, data_object_id     
  2  from user_objects
  3  where object_name = 'HKC_T_1'
  4  and object_type = 'TABLE'
  5  /

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     94422          94422

SQL> insert into hkc_t_1 values (1);

1 row created.

SQL> truncate table hkc_t_1;

Table truncated.

SQL> select object_id, data_object_id
  2  from user_objects
  3  where object_name = 'HKC_T_1'
  4  and object_type = 'TABLE'
  5  /

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     94422          94423

SQL> 


The 12c AWR report shows both the Obj# and DataObj#..

In my lab, I created a workload where two tables were TRUNCATED 100 times in a loop that would insert fresh rows after each TRUNCATE.  (Those familiar with Peoplesoft Batch Jobs would know this behaviour)

Some extracts from the AWR show :

Segments by Physical Writes               DB/Inst: NONCDB/NONCDB  Snaps: 53-54
-> Total Physical Writes:         340,305
-> Captured Segments account for    0.7% of Total
-> When ** MISSING ** occurs, some of the object attributes may not be available

                     Tablespace
Owner                   Name
-------------------- ----------
                     Subobject  Obj.                            Physical
Object Name            Name     Type        Obj#   Dataobj#       Writes  %Total
-------------------- ---------- ----- ---------- ---------- ------------ -------
HEMANT               HEMANT
SOURCE_TB_1                     TABLE      94220      94220        1,575     .46
HEMANT               USERS
LIST_TB_2                       TABLE      94219      94231          263     .08
HEMANT               USERS
WORKLOAD_LOG                    TABLE      94221      94221           71     .02
SYS                  SYSTEM
COL$                            TABLE         21          2           46     .01
SYS                  SYSTEM
SEG$                            TABLE         14          8           45     .01
                          ------------------------------------------------------

Segments by Physical Write Requests       DB/Inst: NONCDB/NONCDB  Snaps: 53-54
-> Total Physical Write Requests:         175,206
-> Captured Segments account for   22.3% of Total
-> When ** MISSING ** occurs, some of the object attributes may not be available

                     Tablespace
Owner                   Name
-------------------- ----------
                     Subobject  Obj.                          Phys Write
Object Name            Name     Type        Obj#   Dataobj#     Requests  %Total
-------------------- ---------- ----- ---------- ---------- ------------ -------
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94370        1,086     .62
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94234          983     .56
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94228          981     .56
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94232          971     .55
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94218          964     .55
                          ------------------------------------------------------

Segments by Table Scans                   DB/Inst: NONCDB/NONCDB  Snaps: 53-54
-> Total Table Scans:             243
-> Captured Segments account for   18.9% of Total
-> When ** MISSING ** occurs, some of the object attributes may not be available

                     Tablespace
Owner                   Name
-------------------- ----------
                     Subobject  Obj.                               Table
Object Name            Name     Type        Obj#   Dataobj#        Scans  %Total
-------------------- ---------- ----- ---------- ---------- ------------ -------
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94240            1     .41
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94248            1     .41
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94388            1     .41
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94224            1     .41
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94234            1     .41
                          ------------------------------------------------------

Segments by DB Blocks Changes             DB/Inst: NONCDB/NONCDB  Snaps: 53-54
-> % of Capture shows % of DB Block Changes for each top segment compared
-> with total DB Block Changes for all segments captured by the Snapshot
-> When ** MISSING ** occurs, some of the object attributes may not be available

                     Tablespace
Owner                   Name
-------------------- ----------
                     Subobject  Obj.                            DB Block    % of
Object Name            Name     Type        Obj#   Dataobj#      Changes Capture
-------------------- ---------- ----- ---------- ---------- ------------ -------
** MISSING **        TEMP
** MISSING: -4001635 MISSING ** UNDEF 4.2550E+09    4218752       10,032    2.89
HEMANT               USERS
LIST_TB_2                       TABLE      94219      94235        7,616    2.20
HEMANT               USERS
LIST_TB_2                       TABLE      94219      94231        7,488    2.16
HEMANT               USERS
LIST_TB_2                       TABLE      94219      94403        7,392    2.13
HEMANT               USERS
LIST_TB_1                       TABLE      94218      94314        7,360    2.12
                          ------------------------------------------------------


These are the two target tables (with the current DATA_OBJECT_ID after 100 TRUNCATEs each) :

SQL> select object_name, object_id, data_object_id
  2  from user_objects
  3  where object_name like 'LIST_TB_%'
  4  and object_type = 'TABLE'
  5  order by 1
  6  /

OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
LIST_TB_1                           94218          94418
LIST_TB_2                           94219          94419

SQL> 


The fact that the AWR report shows a different Dataobj# from the Obj# indicates that a TRUNCATE may have occurred sometime in the past (The TRUNCATE may have occurred before the beignning of the AWR report window !).  Multiple entries for the same table indicate that multiple TRUNCATES occurred within the AWR report window.
Note : Although both tables underwent 100 TRUNCATEs in the workload, AWR reports only 5 occurrences of activity.
.
.
.

1 comment:

Foued said...

Thanks Hemant for sharing this post.
Foued