Analyzing non-sequential snapshots in AWR [message #578824] |
Tue, 05 March 2013 09:56 |
|
belf
Messages: 11 Registered: June 2012
|
Junior Member |
|
|
Hi,
How someone can check database performance in a non-sequential parts of a day using AWR tool. For example, suppose we divide a day to 2 parts: low-traffic time and high-traffic time with the following time interval:
low-traffic time : 7:00am - 10:00am and 7:00pm - 11:00pm
high-traffic time : 10:00am - 01:00pm and 4:00pm - 06:00pm
I want to examine performance using snapshots gathered by AWR. If I get 2 AWR reports for low-traffic time of day (one for 7:00am - 10:00am, another for 7:00pm - 11:00pm ) and compute average of values in 2 reports, could I called it database performance in low-traffic part of day or not? If not, please guide me how to do this task?
Thanks,
Belf
|
|
|
|
|
|
|
|
|
|
|
Re: Analyzing non-sequential snapshots in AWR [message #579512 is a reply to message #578886] |
Tue, 12 March 2013 17:01 |
|
alan.kendall@nfl.com
Messages: 163 Registered: June 2012 Location: Culver City, California
|
Senior Member |
|
|
You can see what the database is waiting at the following:
http://www.orafaq.com/forum/mv/msg/183304/563118/173420/#msg_563118
I would also look at the logical verses physical reads:
ENDOCP1P > @physical
DATE PHYSICAL_READS_LAST_HOUR OBJECT_NAME
-------------------- ------------------------ --------------------
2013-11-03 Monday 6054 D_1F000D5D80000910
2013-11-03 Monday 26179 D_1F000D5D80000005
2013-11-03 Monday 40332 D_1F000D5D80000901
2013-11-03 Monday 46808 DM_SYSOBJECT_S_COMP2
2013-11-03 Monday 64930 DM_RELATION_S
2013-11-03 Monday 78228 NFL_CONTENT_R_COMP1
2013-11-03 Monday 79647 DM_SYSOBJECT_S_COMP1
2013-11-03 Monday 135421 DMR_CONTENT_S
2013-11-03 Monday 165087 NFL_CONTENT_R
2013-11-03 Monday 243480 DM_SYSOBJECT_R_COMP1
2013-11-03 Monday 548052 D_1F000D5D8000010A
2013-11-03 Monday 1475076 DM_SYSOBJECT_R_COMP2
ENDOCP1P > list
1 select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
2 sum(b.PHYSICAL_READS_DELTA) PHYSICAL_READS_LAST_HOUR,
3 a.object_name
4 from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
5 where b.SNAP_ID =(select max(SNAP_ID) from sys.wRM$_SNAPSHOT)
6 and a.object_id=b.OBJ#
7 and b.PHYSICAL_READS_DELTA>0
8 and c.instance_number=(select instance_number from v$instance)
9 and c.snap_id=b.snap_id
10 group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
11* order by 2
ENDOCP1P > @logical
DATE LOGICAL_READS_LAST_HOUR OBJECT_NAME
-------------------- ----------------------- --------------------
2013-11-03 Monday 12096 DM_REGISTERED_R
2013-11-03 Monday 12176 WRH$_SEG_STAT_OBJ_PK
2013-11-03 Monday 17232 LOB$
2013-11-03 Monday 20448 OBJ$
2013-11-03 Monday 23488 DM_SYSOBJECT_S_COMP2
2013-11-03 Monday 35184 I_SYSAUTH1
2013-11-03 Monday 36432 D_1F000D5D8000000F
2013-11-03 Monday 93744 DM_SYSOBJECT_R_COMP2
2013-11-03 Monday 163344 DM_SYSOBJECT_R_COMP1
2013-11-03 Monday 249952 DM_SYSOBJECT_R
2013-11-03 Monday 321568 D_1F000D5D80000901
2013-11-03 Monday 790624 DM_SYSOBJECT_S
2013-11-03 Monday 830560 NFL_CONTENT_S
2013-11-03 Monday 1408384 D_1F000D5D80000109
ENDOCP1P > list
1 select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
2 sum(b.LOGICAL_READS_DELTA) LOGICAL_READS_LAST_HOUR,
3 a.object_name
4 from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
5 where b.SNAP_ID =(select max(SNAP_ID) from sys.wRM$_SNAPSHOT)
6 and a.object_id=b.OBJ#
7 and b.LOGICAL_READS_DELTA>0
8 and c.instance_number=(select instance_number from v$instance)
9 and c.snap_id=b.snap_id
10 group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
11* order by 2
Also look at what is taking up all the memory, many times tables that are missing
indexes will hog all the memory and force unnecessary disk reads.
ENDOCP1P > @v$bh
OBJECT_TYPE DB MEG_IN_MEMORY OBJECT_IN_MEMORY
----------- -------- ------------- -----------------------------------------
TABLE NDOCP2 1050 NFLPROD.NFL_CONTENT_S
TABLE NDOCP1 2981 NFLPROD.DM_SYSOBJECT_S
TABLE NDOCP3 3219 NFLPROD.DM_SYSOBJECT_S
TABLE NDOCP4 3688 NFLPROD.DM_SYSOBJECT_S
TABLE NDOCP2 3740 NFLPROD.DM_SYSOBJECT_S
ENDOCP1P > list
1 SELECT
2 o.object_type,i.instance_name db,COUNT(*)*8192/1024/1024 meg_in_memory,
3 o.owner||'.'||o.OBJECT_NAME Object_in_Memory
4 FROM DBA_OBJECTS o, gV$BH bh, gv$instance i
5 WHERE o.DATA_OBJECT_ID = bh.OBJD
6 and bh.status<>'free'
7 and bh.inst_id = i.inst_id
8 and o.object_name like upper('%%')
9 GROUP BY o.owner||'.'||o.OBJECT_NAME,o.object_type,i.instance_name
10 having count(*)>0
11* ORDER BY COUNT(*)
|
|
|