Home » RDBMS Server » Performance Tuning » Analyzing non-sequential snapshots in AWR (Oracle 11gR2 )
Analyzing non-sequential snapshots in AWR [message #578824] Tue, 05 March 2013 09:56 Go to next message
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 #578826 is a reply to message #578824] Tue, 05 March 2013 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take 4 reports.

Regards
Michel
Re: Analyzing non-sequential snapshots in AWR [message #578827 is a reply to message #578824] Tue, 05 March 2013 10:50 Go to previous messageGo to next message
pvsarat
Messages: 8
Registered: October 2012
Location: CHENNAI
Junior Member
Verfiying two reports with average values .. will be little tough..

why don't you go for baseline of awr report.. so it will very easy to summarize the two reports....

Re: Analyzing non-sequential snapshots in AWR [message #578856 is a reply to message #578826] Tue, 05 March 2013 22:19 Go to previous messageGo to next message
belf
Messages: 11
Registered: June 2012
Junior Member
Dear Michel,

what do you mean from 4 reports?! reports by which snapshots?

Regards,
belf
Re: Analyzing non-sequential snapshots in AWR [message #578857 is a reply to message #578827] Tue, 05 March 2013 22:24 Go to previous messageGo to next message
belf
Messages: 11
Registered: June 2012
Junior Member

Dear Pvsarat,

Is it possible for you to explaine more about summarizing reports using baselines? I want to compute database performance in low-traffic time every day of week, does baseline work in this case?

Thanks in advance,
belf
Re: Analyzing non-sequential snapshots in AWR [message #578876 is a reply to message #578856] Wed, 06 March 2013 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
belf wrote on Wed, 06 March 2013 05:19
Dear Michel,

what do you mean from 4 reports?! reports by which snapshots?

Regards,
belf


The snapshots taken at the beginning and end if each of your periods.

Regards
Michel

Re: Analyzing non-sequential snapshots in AWR [message #578879 is a reply to message #578876] Wed, 06 March 2013 01:22 Go to previous messageGo to next message
belf
Messages: 11
Registered: June 2012
Junior Member

Michel

I want to report just one value for each value of all reports in low-traffic time of day. I can get 2 reports for low-traffic time but I don't know how to mix these values together and report just one value for them?

thanks,
belf
Re: Analyzing non-sequential snapshots in AWR [message #578882 is a reply to message #578879] Wed, 06 March 2013 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what I said, get 2 reports for each application period, mixing the data of 2 (non-subsequent) reports is meaningless.
If you really want to do this, directly query the AWR tables or views and built your own report.

Regards
Michel
Re: Analyzing non-sequential snapshots in AWR [message #578886 is a reply to message #578882] Wed, 06 March 2013 02:19 Go to previous messageGo to next message
belf
Messages: 11
Registered: June 2012
Junior Member
Michel,

You're really true... mixing 2 reports don't result in correct performance data.
Is is possible for you to give me some example sql queries for computing values in AWR report?

Thanks in advance,
belf
Re: Analyzing non-sequential snapshots in AWR [message #579512 is a reply to message #578886] Tue, 12 March 2013 17:01 Go to previous message
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(*)
Previous Topic: One job is hanging after SGA increase
Next Topic: execute to parse % - 50%
Goto Forum:
  


Current Time: Wed Oct 01 14:39:07 CDT 2014

Total time taken to generate the page: 0.08334 seconds