'Sessions' in awr report

From: Ram Raman <veeeraman_at_gmail.com>
Date: Mon, 3 Dec 2012 17:38:03 -0600
Message-ID: <CAHSa0M0+WWjLWfTOnh9RV9zi_O2fwxuTTwgq8g1ntdNg8m59UQ_at_mail.gmail.com>



Hi all,

   I was looking at couple of awr reports, esp the 'sessions' section on the top of the reports. The two reports were from v10.2 before upgrade and 11.2 (after upgrade) of the database belonging to the same application.

This one is from the 10.2 db:

Snap Id Snap Time Sessions Cursors/Session Begin Snap: 106540 24-Oct-12 23:00:40 191 21.5 End Snap: 106546 25-Oct-12 01:01:01 153 16.3 Elapsed: 120.36
(mins) DB Time: 2.38 (mins)

If it matters, here is the load profile and timed events:

Load Profile

Per Second Per Transaction Redo size: 8,708.87 14,878.59 Logical reads: 373.73 638.50 Block changes: 51.90 88.67 Physical reads: 13.51 23.09 Physical writes: 2.25 3.85 User calls: 17.34 29.62 Parses: 5.32 9.09 Hard parses: 0.12 0.20 Sorts: 3.11 5.31 Logons: 0.02 0.04 Executes: 13.91 23.77 Transactions: 0.59

Top 5 Timed Events

  Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class RMAN backup & recovery I/O 159,678 643 4 450.3 System I/O db file sequential read 45,190 107 2 74.8 User I/O CPU time 49 34.2 log file parallel write 4,750 8 2 5.5 System I/O log file sync 3,596 6 2 4.2 Commit

This is from 11.2 db:


Snap Id Snap Time Sessions Cursors/Session Begin Snap: 688 03-Nov-12 07:00:24 63 24.2 End Snap: 689 03-Nov-12 08:00:47 63 24.2 Elapsed: 60.38
(mins) DB Time: 0.05 (mins)

Load Profile

Per Second Per Transaction Per Exec Per Call DB Time(s): 0.0 0.0 0.00 0.00 DB

CPU(s): 0.0 0.0 0.00 0.00 Redo size: 735.9 28,061.6     Logical reads: 57.0
2,172.0     Block changes: 2.8 104.9     Physical reads: 0.0 0.2     Physical
writes: 0.4 13.9     User calls: 2.6 97.3     Parses: 1.5 56.0     Hard
parses: 0.0 1.3     W/A MB processed: 0.0 0.0     Logons: 0.0 0.4
Executes: 3.8 145.9     Rollbacks: 0.0 0.0     Transactions: 0.0


Top 5 Timed Foreground Events

  Event Waits Time(s) Avg wait (ms) % DB time Wait Class DB CPU 2 71.20   log file sync 73 0 2 4.13 Commit db file sequential read 10 0 6 1.80 User I/O SQL*Net message to client 8,133 0 0 0.74 Network Disk file operations I/O 4 0 1 0.10 User I/O

For this second report, I see about 25/30 SQLs listed under "SQL Ordered by elapsed time" section all with very good execution times except one which took 1.7 secs.

I thought that if the DB time was very negligible it means that database is very idle. If that was the case, how is it that I see 191/153 sessions in the beginning/end of the first report and 63 sessions in the second case. I am surprised by 63 during the beginning and end when the DB time is just 0.05 min in an hour. I assume that 'sessions' in the awr report is the number of sessions in the db at the beginning and end of the interval. is that a valid assumption?

I checked the sessions vs DB time from other time periods from both before and after upgrd, the sessions value seems to be pretty constant irrespective of the value of db time. I did that during the database's busiest periods too.

I tried google but could not find a satisfactory answer.

Thanks
Ram.

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 04 2012 - 00:38:03 CET

Original text of this message