Home » RDBMS Server » Performance Tuning » dba_hist_sqlstat (11.2.0.3 || Solaris)
dba_hist_sqlstat [message #570584] Mon, 12 November 2012 09:04 Go to next message
Roachcoach
Messages: 1200
Registered: May 2010
Location: UK
Senior Member
Hello all,

I'm looking at the view dba_hist_sqlstat and I'm seeing oddities - specifically in the columns

PHYSICAL_READ_REQUESTS_DELTA
DISK_READS_DELTA


I'm seeing sql_ids with a zero in the read requests value, but showing non-zero disk reads.

What am I missing?

To save time, here are the documentation notes on the columns:

PHYSICAL_READ_REQUESTS_DELTA
NUMBER
Delta value of number of physical read I/O requests issued by the monitored SQL


DISK_READS_DELTA
NUMBER
Delta number of disk reads for this child cursor


I wonder if the key is "monitored" SQL - I know that not all is, but regardless it's counter intuitive to me to explain how this is reading from disk, yet doing so with zero requests...

Any help/pointers would be appreciated, as always.
Re: dba_hist_sqlstat [message #570658 is a reply to message #570584] Tue, 13 November 2012 17:25 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I usually query v$sqlarea directly
      EXEC READ_BYTES READS_PER_EXEC HASH_VALUE SQL_TEXT
---------- ---------- -------------- ---------- --------------------------------
     19302 1264975872     65535.9997  221529705 select count(*) from v$standby_l
       115 1463566336     12726652.7 4076776025       declare          agedFileR
       141 1727774720     12253712.7 3549317006 SELECT BS.SET_STAMP LIST_ORDER1,
     19302 2213707776     114687.999 2869311931 SELECT PROTECTION_LEVEL FROM V$D
      1747 2347073536     1343487.92 2954998844 SELECT SUM(bytes) value FROM SYS
      1747 2404319232     1376255.92 2968664790 SELECT SUM(bytes) value FROM V$L
      1801 2744205312     1523711.92  792993864  select     tablespace_name "Tab
       115 3066118144     26661873.7 1539603697      declare           lbRec
     19302 3794927616     196607.999 2764390589 select incarnation#,resetlogs_ch
         5 4390166528      878015745 3692951794 SELECT /*+NESTED_TABLE_GET_REFS+
     38980 4470423552     114685.057 1012578725 select name, instance_name, stat
      1783 5083004928     2850815.84 3509142393  SELECT /*+ ORDERED */     D.NAM
      1832 1.2299E+10     6713692.42 2999488838 SELECT     THREAD# AS "THREAD" ,
      3860 1.7455E+10     4521983.88 2131005159      SELECT greatest (     ((TO_

ENWEBP1P > list
  1  select executions exec,physical_read_bytes read_bytes,
  2  physical_read_bytes/(abs(executions)+.0001) reads_per_exec,
  3  hash_value,sql_text
  4  from v$sqlarea where physical_read_bytes>1000000
  5* order by physical_read_bytes

And I query dba_hist_set_stat to get the most heavily used objects per instance.
ENWEBP1P > select b.instance_number instance,
  2  to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DAY",
  3  sum(b.PHYSICAL_READS_DELTA) Daily_reads,a.object_name
  4   from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
  5  where a.object_id=b.OBJ#
  6  and b.SNAP_ID >(select max(SNAP_ID)-24*8 from sys.wRM$_SNAPSHOT)
  7  and upper(a.object_name) like upper('%')
  8  and b.PHYSICAL_READS_DELTA>0
  9  and c.instance_number=(select instance_number from v$instance)
 10  and c.snap_id=b.snap_id
 11  group by b.instance_number,
 12  to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),
 13  a.object_name
 14  having sum(b.PHYSICAL_READS_DELTA)>1000000
 15  order by 2,1;

INSTANCE DAY                         DAILY_READS OBJECT_NAME
-------- --------------------------- ----------- --------------------
       4 2012-11-05 Monday               1256570 REGISTRATIONS
       3 2012-11-06 Tuesday              1311136 REGISTRATIONS
       4 2012-11-06 Tuesday              3635387 REGISTRATIONS
       3 2012-11-07 Wednesday            1092344 REGISTRATIONS
       4 2012-11-07 Wednesday            2789116 REGISTRATIONS
       3 2012-11-08 Thursday             1197044 REGISTRATIONS
       4 2012-11-08 Thursday             4575081 REGISTRATIONS
       4 2012-11-09 Friday               5778550 REGISTRATIONS
       3 2012-11-10 Saturday             1402265 REGISTRATIONS
       4 2012-11-10 Saturday             7539927 REGISTRATIONS
       2 2012-11-11 Sunday               1464027 REGISTRATIONS
       3 2012-11-11 Sunday               3143117 REGISTRATIONS
       4 2012-11-11 Sunday               8771145 REGISTRATIONS
       1 2012-11-12 Monday               1032757 REGISTRATIONS
       3 2012-11-12 Monday               1972412 REGISTRATIONS
       4 2012-11-12 Monday               8007675 REGISTRATIONS
       3 2012-11-13 Tuesday              1816549 REGISTRATIONS
       4 2012-11-13 Tuesday              6652614 REGISTRATIONS

Re: dba_hist_sqlstat [message #570667 is a reply to message #570658] Tue, 13 November 2012 23:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How does this answer the question?
How is this have related to the question?

Regards
Michel

[Updated on: Tue, 13 November 2012 23:49]

Report message to a moderator

Re: dba_hist_sqlstat [message #570675 is a reply to message #570667] Wed, 14 November 2012 02:08 Go to previous messageGo to next message
Roachcoach
Messages: 1200
Registered: May 2010
Location: UK
Senior Member
Thanks, but it's not the read bytes I'm worried about for now, it's the io requests. The storage systems are not at bandwidth capacity, but they are drowning under request volume i.e. lots and lots and lots of small I/O requests.

I want to identify sql candidates to consider for possible tuning to help reduce this load or at least avoid certain items running concurrently - however since I'm seeing confusing data (at least to me, I'm sure there's a reason) in the dba_hist_* tables, this makes me doubt any analysis coming out.

As background I picked what I thought would be a likely contender - a query firing in excess of a million times per hour, with minimal data reuse and using all single block reads. However, said sql_id shows ~10 disc reads per exe, but 0 physical read requests per exe, hence my question Smile
Re: dba_hist_sqlstat [message #570711 is a reply to message #570675] Wed, 14 November 2012 15:50 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Whenever in doubt you can take two snapshots of gv$bh which has a row for each buffer in the buffer cache. The difference is the physical reads.
ENWEBP1P > @v$bh2

INSTANCE_NAME   PHYSICAL_READS_PER_MINUTE OBJECT_IN_MEMORY
--------------- ------------------------- ------------------------------
NWEBP3                                  1 EMAIL_AUDIT_TYPE_STATUS
NWEBP1                                  1 IDX_NFL_CONT_R_WEB2
NWEBP4                                  1 REGISTRATIONS_LLT_PK
NWEBP1                                  1 PERSON_SPLIT_STATS_COMP1
NWEBP4                                  1 CONTENT_PERSON_REL_1
NWEBP4                                  1 REGISTRATIONS_LAST_NAME
NWEBP4                                  1 ALAN9
NWEBP2                                  1 ALAN9
NWEBP4                                  1 EMAIL_AUDIT_TYPE_STATUS
NWEBP2                                  1 REGISTRATIONS_LLT_PK
NWEBP3                                  1 ALAN9
NWEBP3                                  1 REGISTRATIONS_LAST_NAME
NWEBP2                                  1 REGISTRATIONS_LOWER_USERNAME
NWEBP1                                  1 REGISTRATIONS_PK
NWEBP4                                  1 REGISTRATIONS_LOWER_LAST_FIRST
NWEBP1                                  1 DR$PERSONS_TEXT_INDEX$I
NWEBP4                                  1 REGISTRATIONS_EMAIL
NWEBP4                                  2 PERSON_SPLIT_STATS
NWEBP3                                  2 REGISTRATIONS_LOWER_USERNAME
NWEBP4                                  2 REGISTRATIONS_UPDATEPLUCK_IX
NWEBP3                                  2 CONTENT_PERSON_REL_1
NWEBP4                                  2 REGISTRATIONS_LOWER_USERNAME
NWEBP1                                  2 REGISTRATIONS_LOWER_USERNAME
NWEBP1                                  2 REG_EMAIL_UIX
NWEBP4                                  3 PERSON_GAME_STATS
NWEBP3                                  3 REG_EMAIL_UIX
NWEBP4                                  3 REGISTRATIONS
NWEBP2                                  3 REG_EMAIL_UIX
NWEBP3                                  4 EMAIL_AUDIT
NWEBP3                                  4 PLAYEREXTRACT_PK
NWEBP4                                  4 TRANSACTIONTABLE_IX01
NWEBP3                                  5 TRANSACTIONTABLE_PK
NWEBP3                                  5 REGISTRATIONS
NWEBP4                                  7 REG_EMAIL_UIX
NWEBP2                                  9 EMAIL_AUDIT_TYPE_STATUS
NWEBP3                                 12 REGISTRATIONS_LLT
NWEBP1                                 13 REGISTRATIONS
NWEBP2                                 15 REGISTRATIONS_LLT
NWEBP2                                 17 REGISTRATIONS
NWEBP4                                 21 REGISTRATIONS_LLT
NWEBP4                                 66 TRANSACTIONTABLE
NWEBP1                                480 ALAN9
                -------------------------
sum                                   705
 The full sql follows:
COLUMN OBJECT_in_memory FORMAT A40
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999
column buffers_read_into_memory format 9999999999
column instance_name format a15
break on report
compute sum of Physical_reads_per_minute on report
set termout off
drop table alan9 purge;
drop table alan10 purge;
create table alan9 nologging as 
select inst_id,OBJD,FILE#,BLOCK# from gv$bh;
@sleep 60
create table alan10 nologging as
select inst_id,OBJD,FILE#,BLOCK# from gv$bh
minus
select inst_id,OBJD,FILE#,BLOCK# from alan9;
set termout on
SELECT i.instance_name,COUNT(*) Physical_reads_per_minute,
o.OBJECT_NAME Object_in_Memory
     FROM DBA_OBJECTS o, alan10 bh, gv$instance i
    WHERE o.DATA_OBJECT_ID = bh.OBJD
      AND o.OWNER         != 'SYS'
      and i.inst_id=bh.inst_id
    GROUP BY o.OBJECT_NAME,i.instance_name
    having count(*)>0
    ORDER BY COUNT(*);
set termout off
drop table alan9 purge;
drop table alan10 purge;
set termout on

Sometimes what is hogging memory will cause important objects to get LRU and force more I/O. Usually big tables in memory can be reduced by creating an index.
OBJECT_TYPE DB       MEG_IN_MEMORY OBJECT_IN_MEMORY
----------- -------- ------------- ---------------------------------
TABLE       NWEBP2             772 SITE.PERSON_SPLIT_STATS
TABLE       NWEBP4             868 ALFC.NFL_ABSTRACT_CONTENT
TABLE       NWEBP1             929 ALFC.NFL_ABSTRACT_CONTENT
TABLE       NWEBP3             966 ALFC.NFL_ABSTRACT_CONTENT
TABLE       NWEBP2            1018 ALFC.NFL_ABSTRACT_CONTENT
TABLE       NWEBP1            1097 PROFILE.REGISTRATIONS_LLT
TABLE       NWEBP4            1421 CONTENT.NFL_DOCBASE_PROPERTIES1_S
TABLE       NWEBP3            1456 CONTENT.NFL_DOCBASE_PROPERTIES1_S
TABLE       NWEBP1            2639 PROFILE.REGISTRATIONS
TABLE       NWEBP3            3436 PROFILE.REGISTRATIONS
TABLE       NWEBP2            3751 PROFILE.REGISTRATIONS
TABLE       NWEBP4            4455 PROFILE.REGISTRATIONS
                     -------------
sum                          74803
The full sql of v$bh.sql follows:

set pages 30
set heading on
set lines 200
set wrap off
column db format a8
column object_type format a11
COLUMN OBJECT_in_memory FORMAT A50
col "V$BH_GIGABYTES_IN_USE" for 999999.9
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999
column meg_in_memory format 9999999999
break on report
compute sum of meg_in_memory on report
compute sum of MEGABYTES_OF_CACHE_IN_USE on report
--select count(*) buffers_in_use from v$bh;
--select count(*)*8192/1024/1024 megabytes_in_use from v$bh;
--select count(*)*8192/1024/1024/1024 "V$BH_GIGABYTES_IN_USE" from v$bh;
SELECT 
o.object_type,i.instance_name db,COUNT(*)*8192/1024/1024 meg_in_memory,
o.owner||'.'||o.OBJECT_NAME Object_in_Memory
     FROM DBA_OBJECTS o, gV$BH bh, gv$instance i
    WHERE o.DATA_OBJECT_ID = bh.OBJD
    and bh.status<>'free'
    and bh.inst_id = i.inst_id
and o.object_name like upper('%')
    GROUP BY o.owner||'.'||o.OBJECT_NAME,o.object_type,i.instance_name
    having count(*)>=128
    ORDER BY COUNT(*);
Re: dba_hist_sqlstat [message #570728 is a reply to message #570711] Wed, 14 November 2012 20:16 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I also look at the average elapsed time because some of the contentions disk read sql's have longer I/O waits.
ENWEBP1P > @@v$sqlarea_elapsed

TOT_SEC_ELAPSED HASH_VALUE EXECUTIONS INSTANC AVG_SEC_PER_EXEC DISK_READS SQL_TE
--------------- ---------- ---------- ------- ---------------- ---------- ------
      96.314298 4131400883         61 NWEBP3        1.57889703      45880 select
     116.120576 4062796995         69 NWEBP3        1.68288251      43867 select
     115.191377  448978319         67 NWEBP3        1.71924862      41726 select
     379.516844  877356406         98 NWEBP1        3.87258134         20 select
     226.850833   26185736         53 NWEBP2        4.28012364          0 select
     1513.33015  957254785        348 NWEBP3        4.34863735          4 select
     1526.46991  957254785        349 NWEBP2        4.37382675          3 select
     1366.94219  957254785        312 NWEBP1        4.38121092          0 select
     1608.99833   31948087        367 NWEBP4        4.38417969          4 select
       36506.94 3772518244       3668 NWEBP2        9.95281626    4643106 /* sel
      176124.91 3772518244       3447 NWEBP4        51.0951142   43585284 /* sel
                           ----------
sum                              8839

11 rows selected.

ENWEBP1P > list
  1  select elapsed_time/1000000 tot_sec_elapsed,hash_value,executions,i.instanc
  2  elapsed_time/(executions+.001)/1000000 Avg_sec_per_exec,DISK_READS,
  3  sql_text
  4  from gv$sqlarea s,gv$instance i
  5  where executions >50 and elapsed_time/(executions+.001)/1000000>1
  6  and upper(sql_text) not like '%DBMS_STATS%'
  7  and upper(sql_text) not like '%WRH$%'
  8  and upper(sql_text) not like '%WRI$%'
  9  and upper(sql_text) not like '%OEM%'
 10  and upper(sql_text) not like '%DR$%'
 11  and upper(sql_text) not like '%DBMS%'
 12  and upper(sql_text) not like '%DBID%'
 13  and upper(sql_text) not like '%OWNER%'
 14  and upper(sql_text) not like '%JOB$%'
 15  and upper(sql_text) not like '%V$%'
 16  and upper(sql_text) not like '%SEQUENCE#%'
 17  and upper(sql_text) not like '%DBSNMP%'
 18  and upper(sql_text) not like '%CTXSYS%'
 19  and upper(sql_text) not like '%BACKUP_TYPE%'
 20  and upper(sql_text) not like '%MGMT%'
 21  and upper(sql_text) not like '%OBJ#%'
 22  and elapsed_time/1000000>1 and s.inst_id=i.inst_id
 23* order by elapsed_time/(executions+.001)/1000000
Previous Topic: Slow Join between dba_tab_cols and dba_types
Next Topic: Options for optimising SQL running against Wide tables
Goto Forum:
  


Current Time: Tue Sep 02 22:19:56 CDT 2014

Total time taken to generate the page: 0.10010 seconds