Average Session Load ASL The Golden Metric

1. Average Session Load (ASL)The Golden Metric ? Kyle Hailey http://perfvision.com

2. Copyright 2006 Kyle Hailey In this Session The Holy Grail of Performance : ASL ASL : stethoscope for Database Health Tap into the heart beat of the database Heart stopped - Hung? Beating very slow - Idle ? Beating fast - Heavily Loaded ? Subcomponent Trinity CPU Waits Time series Extra: use Maximum CPU as Yardstick

3. Copyright 2006 Kyle Hailey Word of Wisdom My Goal is to cut out a lot of the noise and extraneous data and hone in on the essential

4. Copyright 2006 Kyle Hailey Idle Database Value of proving the database is Idle It�s the Databases Fault How many times do you hear that? Database Idle No load on database Database �performance� is fine Under utilized Problem lies elsewhere Saved me time and stress many times Wouldn�t it be nice to have an easy way to show that the application wasn�t even putting any load on the databaseWouldn�t it be nice to have an easy way to show that the application wasn�t even putting any load on the database

5. Copyright 2006 Kyle Hailey What�s the Database Doing Often I want a quick and easy way to see what the database is doing Is it working? Is it blocked How much is going on? Is the database �healthy�?

6. Copyright 2006 Kyle Hailey Checking the Database How do *you* check the Database Health? Routine Exams? Statspack? 1300 lines of data Which lines How many stats Automated Alerts? What do you set alerts on? What if no alerts fire ? Are you ok? Do alerts really tell you what�s happening? Statspack can be an overwhelming amount of data clocking in at a typical 1300 lines of data. Graphics are a good solution displaying this amount of data more quickly, but what stats to you show and how? Alerts are great at automated notification, but what do you set alerts on? Alerts are generally set on a standard work load. What if there are no alerts going off? How do you check that things really are working correctly and there is not a problem with the alerts, their levels or possibly missing alerts?Statspack can be an overwhelming amount of data clocking in at a typical 1300 lines of data. Graphics are a good solution displaying this amount of data more quickly, but what stats to you show and how? Alerts are great at automated notification, but what do you set alerts on? Alerts are generally set on a standard work load. What if there are no alerts going off? How do you check that things really are working correctly and there is not a problem with the alerts, their levels or possibly missing alerts?

7. Copyright 2006 Kyle Hailey What�s the Database Doing Whip out the stethoscope ASL

8. Copyright 2006 Kyle Hailey The Cult of ASL Once you�ve been initiated � there is no going back

9. Copyright 2006 Kyle Hailey Welcome to �The Cult of ASL Magic Metric for Database Health Average Session Load (ASL) For those of you who are already members, welcome back IQ is a controversial metric as well but it has it�s place. Someone with an IQ of 70 probably would not be the best candidate for an Ivy League math professor. The problem is they might be good for other jobs but IQ lacks information about which professions they person might excel at. IQ lacks multiple dimensions that would break down abilities into different areas. Similarly the ASA by itself lacks the dimensions and by itself is limited but useful. It of course can be broken down into several parts to give us a more detailed picture of activity.IQ is a controversial metric as well but it has it�s place. Someone with an IQ of 70 probably would not be the best candidate for an Ivy League math professor. The problem is they might be good for other jobs but IQ lacks information about which professions they person might excel at. IQ lacks multiple dimensions that would break down abilities into different areas. Similarly the ASA by itself lacks the dimensions and by itself is limited but useful. It of course can be broken down into several parts to give us a more detailed picture of activity.

10. Copyright 2006 Kyle Hailey ASL The Golden Metric Powerful Multidimensional Indispensable

11. Copyright 2006 Kyle Hailey Average Session Load (ASL) goes under the guise of Session Load I often refer to it this way Average Active Sessions (aas?) The way I named it in OEM 10g Graphs Centi-seconds per second (or secs/sec) In the dark ages, before OEM 10g, waits were often measured as Centi-secs per sec

12. Copyright 2006 Kyle Hailey ASL � Average Session Load Average Value Averaged over 15 secs in OEM 10g Time period could 5 minutes, an hour Active Session Only Active sessions put load the database Inactive Sessions don�t put load Except for memory usage Active sessions are Sessions in a Call Started a SQL Statement but hasn�t returned yet DBWR writing blocks out

13. Copyright 2006 Kyle Hailey ASL Calculations ASL = DB TIME / Time Period

14. Copyright 2006 Kyle Hailey ASL � sources ASL can be found from or derived from 10g OEM V$systat �DB time� / elapsed time 9i v$system_event $sysstat This works Oracle 7 to 10g (probably 11)

15. Copyright 2006 Kyle Hailey ASL � 9i Data V$system_event Sum wait times Non-idle waits V$sysstat CPU used by this session ASL= (sums(wait)-cpu)/elapsed time ASL(CPU)=cpu/elasped ASL(wait)= sum(waits)/elapsed Produces Session time / elapsed time Session cent-secs/sec Session secs/ sec Avg Session Load (ASL)

16. Copyright 2006 Kyle Hailey ASL in OEM DB Home Page

17. Copyright 2006 Kyle Hailey Calculating ASL : Statspack Uses v$session_wait v$sysstat Look for Top 5 Timed Events Elapsed Time cpu_count helpful Seconds in Wait / Elapsed time = ASL

18. Copyright 2006 Kyle Hailey Use Statspack to Find Waits Statspack Cheat Sheet: Install Connect as SYSDBA @?/rdbms/admin/spcreate.sql Run Exec statspack.snap; Generate Reports @?/rdbms/admin/spreport.sql

19. Copyright 2006 Kyle Hailey Statspack Trusty statspack report Elapased Time Check �Top 5 Timed Events� Looking at the situation more concretely, when there is a slow down on the database we look at our trusted Statspack report for the period of the slowdown. The first step in analyzing the statspack report is to look at the Top 5 Timed Events The top 5 timed wait events will tell us if any wait event has crept up to cause a bottleneck. If we do find a wait event bottleneck we will need to know who or what is causing the problem in order to solve it. For example if there is a CPU bottleneck, we need to know what SQL statement is hogging the CPU. If there is an IO bottleneck we need to know what SQL statement is stuck on IO and needs tuning. If there is a complex situation like a buffer busy waits or latch contention we need to know which sessions were involved, what the wait event arguments were and what SQL they were executing. Statspack fails to give us the necessary detailed information but ASH does.Looking at the situation more concretely, when there is a slow down on the database we look at our trusted Statspack report for the period of the slowdown. The first step in analyzing the statspack report is to look at the Top 5 Timed Events The top 5 timed wait events will tell us if any wait event has crept up to cause a bottleneck. If we do find a wait event bottleneck we will need to know who or what is causing the problem in order to solve it. For example if there is a CPU bottleneck, we need to know what SQL statement is hogging the CPU. If there is an IO bottleneck we need to know what SQL statement is stuck on IO and needs tuning. If there is a complex situation like a buffer busy waits or latch contention we need to know which sessions were involved, what the wait event arguments were and what SQL they were executing. Statspack fails to give us the necessary detailed information but ASH does.

20. Copyright 2006 Kyle Hailey Elapsed Time

21. Copyright 2006 Kyle Hailey Used CPU Time and Wait Time Look at Top 5 Timed Events Statspack is probably the most reliable source of performance information. The statspack report, ?/rdbms/admin/spreport.sql , generates over 1000 lines of information, but the first and possible only place to go in the report is �Top 5 Timed Events�. In �Top 5 Timed Events� we can determine if the database has any performance issues. If it does have performance issues, then we can find out if it�s CPU or a wait. If it�s a wait we can tune that particular wait.Statspack is probably the most reliable source of performance information. The statspack report, ?/rdbms/admin/spreport.sql , generates over 1000 lines of information, but the first and possible only place to go in the report is �Top 5 Timed Events�. In �Top 5 Timed Events� we can determine if the database has any performance issues. If it does have performance issues, then we can find out if it�s CPU or a wait. If it�s a wait we can tune that particular wait.

22. Copyright 2006 Kyle Hailey Example CPU + WAITS CPU = 32 WAITS = 250 + 15 + 8 + 5 = 278 secs Elapsed Time = 60 secs (32 + 278) user secs / 60 secs = 5.1 average session load ( 4.6 waiting 0.5 on CPU )

23. Copyright 2006 Kyle Hailey Available CPU � init.ora Statspack 10g shows # of CPUS Statspack 9i # of CPUs

24. Copyright 2006 Kyle Hailey Available CPU vs ASL Far above available CPU => problem Plenty of Free CPU => wait bottleneck

25. Copyright 2006 Kyle Hailey ASL � Primary Purpose Answers the Question Is the database idle ? Active? How Active? If the users call up saying the database is hanging and ASL < 1 you know it�s not true.If ASL is near 0 you even know that the database is idle and that the users or application is not requesting any work from OracleIf the users call up saying the database is hanging and ASL < 1 you know it�s not true.If ASL is near 0 you even know that the database is idle and that the users or application is not requesting any work from Oracle

26. Copyright 2006 Kyle Hailey ASL < 1 Database is not blocked ASL ~= 0 Database basically idle ASL < # of CPUs Extra CPU to be had Database is probably not blocked ASL > # of CPUs Could have performance problems ASL >> # of CPUS There is a bottleneck

27. Copyright 2006 Kyle Hailey Going Farther with ASL ASL can tell you a lot But it�s components tell you much more To go farther need the components of ASL CPU How many CPUs (max CPU available) Wait Which waits Value over time

28. Copyright 2006 Kyle Hailey Components of ASL

29. Copyright 2006 Kyle Hailey EM DB Home Page OEM DB Home page only shows ASL at a point in time which is limited. Click on the performance page tab to get a time line view.OEM DB Home page only shows ASL at a point in time which is limited. Click on the performance page tab to get a time line view.

30. Copyright 2006 Kyle Hailey Performance Page In the middle of the page is Average Session Load, or Average Active SessionsIn the middle of the page is Average Session Load, or Average Active Sessions

31. Copyright 2006 Kyle Hailey Performance Page In the middle of the page is Average Session Load, or Average Active SessionsIn the middle of the page is Average Session Load, or Average Active Sessions

32. Copyright 2006 Kyle Hailey ASL � Performance Page

33. Copyright 2006 Kyle Hailey OEM 10g - ASL

34. Copyright 2006 Kyle Hailey OEM 10g - CPU

35. Copyright 2006 Kyle Hailey OEM 10g - Waits

36. Copyright 2006 Kyle Hailey OEM 10g � CPU vs Wait

37. Copyright 2006 Kyle Hailey OEM 10g � Max CPU

38. Copyright 2006 Kyle Hailey OEM 10g Zoom-In Maximum CPU line � ADDM report (folder with checkmark) Run ADDM Now � Run ASH Report � Top Activity � CPU Used � Wait Classes -Maximum CPU line � ADDM report (folder with checkmark) Run ADDM Now � Run ASH Report � Top Activity � CPU Used � Wait Classes -

39. Copyright 2006 Kyle Hailey OEM 10g � get to work ! Maximum CPU line � ADDM report (folder with checkmark) Run ADDM Now � Run ASH Report � Top Activity � CPU Used � Wait Classes -Maximum CPU line � ADDM report (folder with checkmark) Run ADDM Now � Run ASH Report � Top Activity � CPU Used � Wait Classes -

40. Copyright 2006 Kyle Hailey Calculating ASL

41. Copyright 2006 Kyle Hailey ASL Calculations ASL = DB TIME / Time Period

42. Copyright 2006 Kyle Hailey ASL alternative Calculation active sessions Count Active sessions over interval average by interval Less accurate, but surprisingly close v$session_wait (or v$active_session_history) Wait_time > 0 = ON CPU Filter out idle events 9i or lower, join to v$session status='ACTIVE' type='USER� 10g v$sessions has all the columns

43. Copyright 2006 Kyle Hailey Two Sources comparison V$system_event & v$sysstat Indirect measure via time spend in DB Accurate Measure of time counters Values can lag (especially CPU) v$session_wait Direct measure of # of sessions Closer to �real time� statistical approximation via samples

44. Copyright 2006 Kyle Hailey ASL in OEM

45. Copyright 2006 Kyle Hailey ASL � Performance Page select substr(name,0,25) event, substr(wait_class,0,25) class from v$event_name where wait_class != 'Other' and wait_class !='Idle' and wait_class != 'System I/O' order by wait_class /buffer pool resize Administrative switch logfile command Administrative index (re)build online st Administrative index (re)build online cl Administrative index (re)build online me Administrative enq: TM - contention Application enq: TX - row lock conten Application SQL*Net break/reset to cl Application SQL*Net break/reset to db Application enq: UL - contention Application log file sync Commit enq: TX - index contentio Concurrency latch: row cache objects Concurrency row cache lock Concurrency cursor: mutex X Concurrency cursor: mutex S Concurrency cursor: pin S wait on X Concurrency latch: shared pool Concurrency latch: library cache Concurrency latch: library cache lock Concurrency latch: library cache pin Concurrency library cache pin Concurrency library cache lock Concurrency library cache load lock Concurrency pipe put Concurrency os thread startup Concurrency latch: cache buffers chai Concurrency buffer busy waits Concurrency sort segment request Configuration enq: TX - allocate ITL en Configuration enq: SQ - contention Configuration free buffer waits Configuration write complete waits Configuration latch: redo writing Configuration latch: redo copy Configuration log buffer space Configuration log file switch (checkpoi Configuration log file switch (private Configuration log file switch (archivin Configuration log file switch completio Configuration enq: ST - contention Configuration enq: HW - contention Configuration enq: SS - contention Configuration undo segment extension Configuration undo segment tx slot Configuration SQL*Net more data from db Network SQL*Net message to client Network SQL*Net message to dblink Network SQL*Net more data to clie Network SQL*Net more data to dbli Network SQL*Net more data from cl Network Datapump dump file I/O User I/O BFILE read User I/O local write wait User I/O buffer read retry User I/O read by other session User I/O db file sequential read User I/O db file scattered read User I/O db file single write User I/O db file parallel read User I/O direct path read User I/O direct path read temp User I/O direct path write User I/O direct path write temp User I/O select substr(name,0,25) event, substr(wait_class,0,25) class from v$event_name where wait_class != 'Other' and wait_class !='Idle' and wait_class != 'System I/O' order by wait_class /buffer pool resize Administrative switch logfile command Administrative index (re)build online st Administrative index (re)build online cl Administrative index (re)build online me Administrative enq: TM - contention Application enq: TX - row lock conten Application SQL*Net break/reset to cl Application SQL*Net break/reset to db Application enq: UL - contention Application log file sync Commit enq: TX - index contentio Concurrency latch: row cache objects Concurrency row cache lock Concurrency cursor: mutex X Concurrency cursor: mutex S Concurrency cursor: pin S wait on X Concurrency latch: shared pool Concurrency latch: library cache Concurrency latch: library cache lock Concurrency latch: library cache pin Concurrency library cache pin Concurrency library cache lock Concurrency library cache load lock Concurrency pipe put Concurrency os thread startup Concurrency latch: cache buffers chai Concurrency buffer busy waits Concurrency sort segment request Configuration enq: TX - allocate ITL en Configuration enq: SQ - contention Configuration free buffer waits Configuration write complete waits Configuration latch: redo writing Configuration latch: redo copy Configuration log buffer space Configuration log file switch (checkpoi Configuration log file switch (private Configuration log file switch (archivin Configuration log file switch completio Configuration enq: ST - contention Configuration enq: HW - contention Configuration enq: SS - contention Configuration undo segment extension Configuration undo segment tx slot Configuration SQL*Net more data from db Network SQL*Net message to client Network SQL*Net message to dblink Network SQL*Net more data to clie Network SQL*Net more data to dbli Network SQL*Net more data from cl Network Datapump dump file I/O User I/O BFILE read User I/O local write wait User I/O buffer read retry User I/O read by other session User I/O db file sequential read User I/O db file scattered read User I/O db file single write User I/O db file parallel read User I/O direct path read User I/O direct path read temp User I/O direct path write User I/O direct path write temp User I/O

46. Copyright 2006 Kyle Hailey ASL � Top Activity Page

47. Copyright 2006 Kyle Hailey Active Average Sessions = Top Activty ?

48. Copyright 2006 Kyle Hailey DB TIME = area under the curve adfadf

49. Copyright 2006 Kyle Hailey ASL � Top Activity Page

50. Copyright 2006 Kyle Hailey ASL � Top Activity Page

51. Copyright 2006 Kyle Hailey Samples VS Counters

52. Copyright 2006 Kyle Hailey The Power ASH gives ASL

53. Copyright 2006 Kyle Hailey ASH in OEM

54. Copyright 2006 Kyle Hailey Top Activity : Based on ASH

55. Copyright 2006 Kyle Hailey Top Activity : ASH Dimensions

56. Copyright 2006 Kyle Hailey ASL � %Session Time Issue

57. Copyright 2006 Kyle Hailey Top Activity: ASH Sessions

58. Copyright 2006 Kyle Hailey Top Activity: ASH Sessions

59. Copyright 2006 Kyle Hailey SQL and Session

60. Copyright 2006 Kyle Hailey Session : ASH Activity

61. Copyright 2006 Kyle Hailey SQL : ASH Activity

62. Copyright 2006 Kyle Hailey Getting the Most Need to know the System�s Profile What your application is like Data Warehouse OLTP Typical load Once you get to know it you can see anomalies Is ASL near 0 when it should be higher Is that Data Warehouse query running normal Do you know what it looks like? Is there an unusual bottleneck Once you get to know a systems profile it will be easy to see aberations.Once you get to know a systems profile it will be easy to see aberations.

63. Copyright 2006 Kyle Hailey Knowing your DB Profile

64. Copyright 2006 Kyle Hailey When to tune General rules of Thumb Waits >> CPU CPU > Max CPU

65. Copyright 2006 Kyle Hailey Waits > CPU

66. Copyright 2006 Kyle Hailey CPU > Max CPU

67. Copyright 2006 Kyle Hailey CPU > Max CPU

68. Copyright 2006 Kyle Hailey Getting More out of ASL

69. Copyright 2006 Kyle Hailey In resume ASL is simple and Powerful ASL�s components are even more powerful CPU WAIT Value over Time Use # of CPUs as a yardstick Know your application load profile to see anomalies Data warehouse OLTP Heavy Load Light Load