Home » RDBMS Server » Performance Tuning » dba_hist_sqlstat (11.2.0.3 || Solaris)
| dba_hist_sqlstat [message #570584] |
Mon, 12 November 2012 09:04  |
Roachcoach
Messages: 1576
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  |
 |
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 #570675 is a reply to message #570667] |
Wed, 14 November 2012 02:08 |
Roachcoach
Messages: 1576
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 
|
|
|
|
| Re: dba_hist_sqlstat [message #570711 is a reply to message #570675] |
Wed, 14 November 2012 15:50 |
|
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 |
|
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
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 17:21:26 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
