|
|
|
|
| Re: graphical (chart/graph) analysis of dynamic performance views [V$ Views] [message #570134 is a reply to message #570053] |
Mon, 05 November 2012 18:04  |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
I do snapshots of gv$segment_statistics to see what is being accessed now.
The following two sql that take snapshots of what is being read logically and physically currently.
ENDOCP1P > @io605
INSTANCE IO_PER_MINUTE STATISTIC_NAME OBJECT_NAME
--------- ------------- ----------------------- ----------------------------------------
NDOCP2 1776 physical reads NFLPROD.D_1F000D5D8000010A.
NDOCP2 1776 physical read requests NFLPROD.D_1F000D5D8000010A.
NDOCP2 5040 physical read requests NFLPROD.DM_RELATION_S.
NDOCP2 117900 physical read requests NFLPROD.DM_SYSOBJECT_R_COMP2.
NDOCP2 119148 physical reads NFLPROD.DM_SYSOBJECT_R_COMP2.
NDOCP2 639924 physical reads direct NFLPROD.DM_RELATION_S.
NDOCP2 639936 physical reads NFLPROD.DM_RELATION_S.
-------------
sum 1525500
ENDOCP1P > @logical605
GIG_PER_MINUTE INSTANC STATISTIC_NAME TYPE OBJECT_NAME
-------------- ------- -------------- ----- ----------------------------
.001 NDOCP1 logical reads TABLE SYS.SEG$
.001 NDOCP1 logical reads INDEX SYS.I_OBJ4
.001 NDOCP1 logical reads INDEX SYS.I_COL3
.001 NDOCP1 logical reads TABLE SYS.JOB$
.001 NDOCP1 logical reads INDEX SYS.I_SYN2
.001 NDOCP1 logical reads INDEX SYS.I_TS#
.001 NDOCP1 logical reads INDEX SYS.I_OBJ5
.003 NDOCP1 logical reads INDEX SYS.I_COL1
.003 NDOCP1 logical reads INDEX SYS.I_COL2
.003 NDOCP1 logical reads TABLE SYS.FILE$
.004 NDOCP1 logical reads INDEX SYS.I_OBJ#
.025 NDOCP2 logical reads TABLE SYS.IND$
.034 NDOCP4 logical reads TABLE SYS.IND$
.042 NDOCP4 logical reads INDEX SYS.I_IND1
.045 NDOCP3 logical reads TABLE SYS.IND$
.048 NDOCP3 logical reads INDEX SYS.I_IND1
.048 NDOCP1 logical reads INDEX SYS.I_IND1
.053 NDOCP2 logical reads INDEX SYS.I_IND1
.104 NDOCP3 logical reads TABLE SYS.OBJ$
.104 NDOCP4 logical reads TABLE SYS.OBJ$
.107 NDOCP2 logical reads TABLE SYS.OBJ$
.116 NDOCP3 logical reads TABLE SYS.USER$
.123 NDOCP4 logical reads TABLE SYS.USER$
.135 NDOCP2 logical reads TABLE SYS.USER$
.170 NDOCP1 logical reads TABLE SYS.OBJ$
.217 NDOCP1 logical reads TABLE SYS.USER$
.258 NDOCP1 logical reads TABLE SYS.TS$
.267 NDOCP1 logical reads INDEX SYS.I_OBJ1
.422 NDOCP2 logical reads TABLE NFLPROD.DM_SYSOBJECT_R
.595 NDOCP2 logical reads TABLE SYS.TS$
.602 NDOCP2 logical reads INDEX SYS.I_OBJ1
.608 NDOCP3 logical reads TABLE SYS.TS$
.624 NDOCP4 logical reads TABLE SYS.TS$
.640 NDOCP3 logical reads INDEX SYS.I_OBJ1
.642 NDOCP4 logical reads INDEX SYS.I_OBJ1
.776 NDOCP2 logical reads TABLE NFLPROD.DM_SYSOBJECT_S
.844 NDOCP2 logical reads INDEX NFLPROD.D_1F000D5D8000010A
1.649 NDOCP2 logical reads INDEX NFLPROD.D_1F000D5D80000109
1.649 NDOCP2 logical reads INDEX NFLPROD.D_1F000D5D80000146
2.451 NDOCP2 logical reads INDEX NFLPROD.DM_SYSOBJECT_R_COMP2
2.451
----------
sum 13.421
IO605.sql looks like the following:
set termout off
set lines 160
break on report
col instance for a9
compute sum of io_per_minute break on report
drop table gv$segment_statistics1;
drop table gv$segment_statistics2;
column statistic_name format a23
create table gv$segment_statistics1 as select * from gv$segment_statistics
where upper(statistic_name) like '%PHYSICAL%' and owner not like 'E%';
execute dbms_lock.sleep(5);
create table gv$segment_statistics2 as select * from gv$segment_statistics
where upper(statistic_name) like '%PHYSICAL%' and owner not like 'E%';
set wrap off
set termout on
select i.instance_name instance,(b.value-a.value)*12 IO_PER_MINUTE,
a.statistic_name,
a.owner||'.'||a.object_name||'.'||a.subobject_name object_name
from gv$segment_statistics2 b,gv$segment_statistics1 a,gv$instance i
where a.statistic_name=b.statistic_name
and a.inst_id=i.inst_id and b.inst_id=i.inst_id
and a.inst_id=b.inst_id and a.object_type=b.object_type
and a.owner||'.'||a.object_name||'.'||a.subobject_name=
b.owner||'.'||b.object_name||'.'||b.subobject_name
and upper(a.statistic_name) like '%PHYSICAL%'
and a.object_name not like 'GV$SEGMENT_STATISTICS%'
and b.value-a.value > 0
order by b.value-a.value;
set termout off
drop table gv$segment_statistics1 purge;
drop table gv$segment_statistics2 purge;
set termout on
logical605.sql looks like the following:
set termout off
drop table gv$segment_statistic1 purge;
drop table gv$segment_statistic2 purge;
column statistic_name format a14
column object_name format a50
column object_type format a11
column gig_per_minute format 9999.999
column type format a5
column instance format a7
SET WRAP OFF
SET TRUNC OFF
SET LINES 200
break on report
compute sum of gig_per_minute break on report
compute sum of buf_per_min break on report
compute sum of buf_per_sec break on report
create table gv$segment_statistic1 as
select * from gv$segment_statistics
where upper(statistic_name) like '%LOGICAL%'
and (upper(statistic_name) like '%READ%' or upper(statistic_name) like '%WRITE%');
execute dbms_lock.sleep(5);
create table gv$segment_statistic2 as
select * from gv$segment_statistics
where upper(statistic_name) like '%LOGICAL%'
and (upper(statistic_name) like '%READ%' or upper(statistic_name) like '%WRITE%');
set wrap off
set pages 50
set termout on
select b.value last, a.value first,(b.value-a.value)*12 buf_per_min,
(b.value-a.value)*12/60 buf_per_sec,
(b.value-a.value)*12*8192/1024/1024/1024*1 GIG_PER_MINUTE,
i.instance_name instance,a.statistic_name,a.object_type type,
a.owner||'.'||a.object_name||' '||a.subobject_name object_name
from gv$segment_statistic2 b,gv$segment_statistic1 a,gv$instance i
where a.statistic_name=b.statistic_name and a.inst_id=b.inst_id
and b.inst_id=i.inst_id and a.inst_id=i.inst_id
and a.object_type=b.object_type
and a.owner||'.'||a.object_name||' '||a.subobject_name
=b.owner||'.'||b.object_name||' '||b.subobject_name
and a.object_name not like 'GV$SEGMENT_STATISTICS%'
and b.value-a.value > 0
order by b.value-a.value;
set termout off
drop table gv$segment_statistic1 purge;
drop table gv$segment_statistic2 purge;
set termout on
I query dba_hist_seg_stat to see what was accessed in the past.
ENWEBP1P > @dba_hist_seg_stat_INPUT_OBJECT_to_see_if_PHYSICAL_WRITES_DELTA_LAST_7_DAYS_BY_DAILY_TOTALS.sql
Enter value for like_object_name: REGISTRATIONS_PK
DATE OBJECT_NAME PHYSICAL_WRITES_DELTA
-------------------- ------------------------------- ---------------------
2012-07-15 Sunday REGISTRATIONS_PK 1781
2012-07-16 Monday REGISTRATIONS_PK 3356
2012-07-17 Tuesday REGISTRATIONS_PK 3369
2012-07-18 Wednesday REGISTRATIONS_PK 3465
2012-07-19 Thursday REGISTRATIONS_PK 3179
2012-07-20 Friday REGISTRATIONS_PK 3087
2012-07-21 Saturday REGISTRATIONS_PK 2903
2012-07-22 Sunday REGISTRATIONS_PK 3401
2012-07-23 Monday REGISTRATIONS_PK 1650
9 rows selected.
ENWEBP1P > list
1 select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",a.object_name,
2 sum(b.PHYSICAL_WRITES_DELTA) physical_writes_delta
3 from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
4 where b.SNAP_ID >
5 (select max(SNAP_ID)-24*8 from sys.wRM$_SNAPSHOT) and a.object_id=b.OBJ#
6 and upper(a.object_name) like upper('%&like_object_name%') and b.PHYSICAL_WRITES_DELTA>0
7 and c.instance_number=(select instance_number from v$instance)
8 and c.snap_id=b.snap_id
9 group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
10* order by 1,3
And I query v$bh to see what is hogging memory (caused excess I/O on other objects).
ECSCDAP1P > @v$bh
OBJECT_TYPE DB MEG_IN_MEMORY OBJECT_IN_MEMORY
----------- -------- ------------- ------------------------------------
TABLE CSCDAP4 430 CDA_APPS.FORM_SUBMISSION
TABLE CSCDAP2 437 CDA_APPS.IMAGE
TABLE CSCDAP3 448 CDA_APPS.FORM_SUBMISSION
TABLE CSCDAP4 457 CDA_PV_APPS.IMAGE
TABLE CSCDAP2 457 CDA_PV_APPS.CMS_META_DATA
TABLE CSCDAP3 485 CDA_APPS.IMAGE
TABLE CSCDAP4 493 CDA_APPS.IMAGE
TABLE CSCDAP1 532 CDA_APPS.IMAGE
TABLE CSCDAP1 572 CDA_APPS.CMS_META_DATA
TABLE CSCDAP4 605 CDA_PV_APPS.CMS_META_DATA
LOB CSCDAP4 709 CDA_SHARED.SYS_LOB0000077712C00003$$
TABLE CSCDAP4 739 CDA_APPS.CONTENT
TABLE CSCDAP4 761 CDA_PV_APPS.CONTENT
LOB CSCDAP1 765 CDA_SHARED.SYS_LOB0000077712C00003$$
TABLE CSCDAP1 895 CDA_PV_APPS.CONTENT
TABLE CSCDAP1 1015 CDA_APPS.CONTENT
TABLE CSCDAP3 1067 CDA_PV_APPS.CONTENT
TABLE CSCDAP2 1164 CDA_PV_APPS.CONTENT
TABLE CSCDAP3 1306 CDA_APPS.CONTENT
TABLE CSCDAP2 1337 CDA_APPS.CONTENT
LOB CSCDAP4 1688 CDA_APPS.SYS_LOB0000078109C00004$$
LOB CSCDAP3 1979 CDA_APPS.SYS_LOB0000078109C00004$$
LOB CSCDAP2 2007 CDA_APPS.SYS_LOB0000078109C00004$$
LOB CSCDAP1 3004 CDA_APPS.SYS_LOB0000078109C00004$$
-------------
sum 59792
v$bh.sql looks like the following:
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(*);
What are you interested in finding out?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
