Home » RDBMS Server » Server Administration » graphical (chart/graph) analysis of dynamic performance views [V$ Views] (10g)
graphical (chart/graph) analysis of dynamic performance views [V$ Views] [message #570051] Mon, 05 November 2012 03:06 Go to next message
oranooob
Messages: 88
Registered: May 2009
Member
Hi

is there some open source or free tool which can graphical display V$ Views. Can TOAD do that in a good maner?

in UNIX there is the "sar" command, but a Java tool "ksar" for displaying the statistics in user friendly fashion.
Re: graphical (chart/graph) analysis of dynamic performance views [V$ Views] [message #570053 is a reply to message #570051] Mon, 05 November 2012 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many on the web, just use Google.
You also have Entreprise Manager/DB Console that comes with your Oracle rdbms downloads.

Regards
Michel
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 Go to previous message
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?
Previous Topic: memory consumption - Insert script
Next Topic: Renaming Datafile
Goto Forum:
  


Current Time: Mon Jul 28 12:12:30 CDT 2014

Total time taken to generate the page: 0.22425 seconds