My Oracle Support Banner

Inconsistent Values Of Statistics Value Between V$SYSSTAT (or DBA_HIST_SYSSTAT ) and AWR Report Statistics (Doc ID 2418370.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Goal

Why is the AWR information different for "HSC OLTP Space Saving" compared to DBA_HIST_SYSSTAT or V$SYSSTAT?

Customer is trying to calculate OLTP compression ratio with the <Document 1223705.1> Master Note for OLTP Compression, and they found that there is a huge discrepancy between V$SYSSTAT (or DBA_HIST_SYSSTAT ) and Instance Activity Stats in AWR report:

 

in <Document 1223705.1>, it stats about "HSC OLTP Space Saving" statistic.

Statistic #    Statistic                                   Description

407               HSC OLTP Space Saving         How many bytes saved in total using OLTP Compression. Take the delta on every compression and adds

--

 

customer queried on  gv$sysstat with keyword "Space Saving"

 

SQL> select inst_id I#, name, value from gv$sysstat where name like '%Space Saving%' order by 1;

  I# NAME VALUE

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

  1 HSC OLTP Space Saving 1,373,192,696,077

  2 HSC OLTP Space Saving 1,363,441,893,277  <<~~~~~~~~~~~ see on Instance 2.

  3 HSC OLTP Space Saving 1,409,354,873,197

  4 HSC OLTP Space Saving 1,366,673,223,870

  5 HSC OLTP Space Saving 1,308,919,347,513

  6 HSC OLTP Space Saving 990,973

 

6 rows selected.

 

They queried on two snaps which they are interested in. snap_id=155080, 155081


SQL> select instance_number I#, STAT_NAME, VALUE from DBA_HIST_SYSSTAT where STAT_NAME like '%Space Saving%' and SNAP_ID = 155080 order by 1;

 

  I# STAT_NAME VALUE

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

  1 HSC OLTP Space Saving 1,372,457,106,454

  2 HSC OLTP Space Saving 1,362,803,814,645   <<~~~~~~~~~ AWR HISTORY (DBA_HIST_SYSSTAT)

  3 HSC OLTP Space Saving 1,408,833,635,085

  4 HSC OLTP Space Saving 1,366,063,150,614

  5 HSC OLTP Space Saving 1,308,407,082,628

  6 HSC OLTP Space Saving 990,973

 

SQL> select instance_number I#, STAT_NAME, VALUE from DBA_HIST_SYSSTAT where STAT_NAME like '%Space Saving%' and SNAP_ID = 155081 order by 1;

 

  I# STAT_NAME VALUE

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

  1 HSC OLTP Space Saving 1,372,459,377,176

  2 HSC OLTP Space Saving 1,362,804,904,250   <<~~~~~~~~~ AWR HISTORY (DBA_HIST_SYSSTAT)

  3 HSC OLTP Space Saving 1,408,834,063,071

  4 HSC OLTP Space Saving 1,366,065,616,849

  5 HSC OLTP Space Saving 1,308,407,605,068

  6 HSC OLTP Space Saving 990,973

 

6 rows selected.



Customer compared this values with the one in AWR report of Instance 2 with snap id between 155080 and 155081.


WORKLOAD REPOSITORY report
------------------------------------------

DB Name DB Id Instance Inst num Startup Time Release RAC

PR01PIMI 2013469077 pr01pimi2 2 03-Mar-18 00:03 11.2.0.4.0 YES  <<~~~~~~~~~ Instance 2

 

Host Name Platform CPUs Cores Sockets Memory (GB)

dm04db02.pimco.imswest.sscims.com Linux x86 64-bit 72 36 2 251.34

 

Other Instance Activity Stats

 

  Ordered by statistic name

Statistic Total per Second per Trans

...

HSC Compressed Segment Block Changes 162,767 180.29 3.42

HSC Heap Segment Block Changes 9,962,304 11,034.53 209.36

HSC IDL Compressed Blocks 0 0.00 0.00

HSC OLTP Compressed Blocks 32 0.04 0.00

HSC OLTP Non Compressible Blocks 86 0.10 0.00

HSC OLTP Space Saving 1,089,605 1,206.88 22.90   <<~~~~~~~~~~~~~~~~~~  Totoal : 1,089,605

...


Customer found huge differences on statistics value  between DBA_HIST_SYSSTAT  and AWR report.

 

DBA_HIST_SYSSTAT   : 1,362,803,814,645 and 1,362,804,904,250

AWR report : 1,089,605

 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.