Workload Qualification - Oracle Exadata Recipes: A Problem-Solution Approach - page 91

from (
select owner,segment_name,segment_type,partition_name,bytes,(sum(bytes) over ())
totbytes
from dba_segments order by 4 asc) seg,
(
select obj.owner,obj.object_name,obj.object_id, obj.data_object_id,
sum(physical_writes_delta) pwrites,
sum(db_block_changes_delta) blkchanges
from dba_objects obj, dba_hist_seg_stat ss
where obj.object_id=ss.obj#(+)
and obj.data_object_id=ss.dataobj#(+)
group by obj.owner,obj.object_name,obj.object_id,obj.data_object_id
) segstat
where 100*(seg.bytes/seg.totbytes) > 2 --- where size > 2% the size of the database
and seg.owner=segstat.owner
and seg.segment_name=segstat.object_name
group by seg.owner, seg.segment_name,seg.segment_type,seg.partition_name,seg.bytes,seg.totbytes
order by 5 desc
/
Owner Segment SEGMENT_TYPE Partition GB %DB Size Writes Changes
------ --------------- ---------------- --------- ------- --------- ------- --------
MYFACT FACT_DETAIL TABLE PARTITION SYS_P488 206.35 7.19 0 0
MYDIM ITEM_LOC_DIM TABLE 185.69 6.47 0 0
MYDIM ITEMS_DIM TABLE 185.17 6.45 2 16
MYFACT FACT_DETAIL TABLE PARTITION SYS_P469 154.30 5.38 0 0
MYFACT FACT_DETAIL TABLE PARTITION SYS_P508 129.17 4.50 0 0
MYDIM CIL_INDX_UKY_01 INDEX PARTITION SYS_P41 92.99 3.24 0 0
MYFACT FACT_DETAIL TABLE PARTITION SYS_P445 89.16 3.11 0 0
... Output omitted
Based on this output, we can see that the only segment that has experienced any DML activity is the
MYDIM.ITEMS_DIM table, which would indicate that this table may not be a good candidate for Hybrid Columnar
Compression.
Let's run another scenario on a database that's experiencing more activity; in this example, we're looking at an
Oracle E-Business Suite environment:
Owner Segment Type (GB) %DB Writes Blk Chg
--------- -------------------------- ---------- ------ ------- -------- ---------
AZ AZ_REPORT TABLE 25.67 10.34 0 0
XXTAMS XX_EMF_DEBUG_TRACE TABLE 21.40 8.61 2825341 22860608
APPLSYS SYS_LOB0000135842C00015$$ LOBSEGMENT 16.60 6.68 312793 395504
APPLSYS WF_ITEM_ATTRIBUTE_VALUES_PK INDEX 10.95 4.41 3084226 84775376
XXTAMS XX_EMF_DEBUG_TRACE_TEMP TABLE 9.82 3.95 1464681 8213072
APPLSYS WF_ITEM_ATTRIBUTE_VALUES TABLE 6.52 2.63 1791574 205069728
CS CS_INCIDENTS_AUDIT_B TABLE 6.44 2.59 139510 1558560
AZ AZ_DIFF_RESULTS TABLE 5.53 2.23 0 0
This example shows that many of our larger tables experience relatively high DML activity and thus would not be
candidates for Hybrid Columnar Compression.