That Which is Monitored, Improves

1. That Which is Monitored, Improves Dan McKee & Megan May Sakai Metrics at Indiana University

2. Sakai Metrics at Indiana University • At Indiana University we believe,That Which is Monitored, Improves • How does Indiana do this? • Extended Performance Metrics [EPM] • Objective today: to present three such metric categories that are in use today • Course site tool usage metrics • Project site tool usage metrics • Database performance metrics Sakai Metrics at Indiana University

3. Sakai at Indiana University • Background • Sakai is called Oncourse CL at Indiana University • Supports over a 100,00 users • Measuring Migration • It runs on a Virtualized IBM AIX (Unix) Server • Its Database Management System is Oracle 10g • Size is approximately 50 Gbs • The typical number of CL Sessions at any one point in time is around 5,000 Sakai Metrics at Indiana University

4. EPM*: Sakai Tool Usage Metrics (* Extended Performance Metrics) • Background • Designed to be Database Platform Independent • Based upon data contained within the Sakai Application/Database • Metrics are populated via PERL scripts • Data Structures are: • Designed to ease reporting and sql query construction • Compatible with a variety of SQL Tools Aqua Data Studio, DB Visualizer, SQL Developer,SQL4X and others • Tool Usage Metric DDL is available at: • http://issues.sakaiproject.org/confluence/x/zHk (see fileepm_tool_proj_20061102_ddl.sql) • SQL scripts and Perl scripts used to build and maintain EPM are available at the above link. Sakai Metrics at Indiana University

5. EPM: Tool Usage Metrics Fact Table Sakai Metrics at Indiana University

6. EPM: Tool Usage Dimensions Sakai Metrics at Indiana University

7. EPM:Tool Usage Metrics ER* Diagram * ER - Entity Relational Sakai Metrics at Indiana University

8. EPM: SQL to Populate Tool Usage • Example SQL used to extract Tool Usage • SQL to Extract Tool Usage for Syllabus [SYL] • select substr(site_id,1,2)TM, '20'||substr(site_id,3,2)YR, substr(site_id,6,2)CMP, count(distinct(Site_Id)) CNTfrom onc.sakai_site_propertywhere site_id like 'FA06%’and SITE_ID in(select CONTEXTID from onc.sakai_syllabus_itemwhere ID in (select surrogatekey from onc.sakai_syllabus_datawhere not (title is null)))group by substr(site_id,1,2),substr(site_id,3,2),substr(site_id,6,2); • File containing complete sql for extracting Tool usage is available at: • http://issues.sakaiproject.org/confluence/x/zHk(see file epm_tool_upd_qry.sql) • Perl script used to extract and populate the Tool Usage Metrics is available at the above link • (see file update_epm_tool_ft.pl) Sakai Metrics at Indiana University

9. Sample Report Sakai Metrics at Indiana University

10. EPM: Usage Charts Sakai Metrics at Indiana University

11. EPM: How is the migration progressing? Sakai Metrics at Indiana University

12. EPM: Tool Usage Reports Sakai Metrics at Indiana University

13. EPM: Tool Usage Charts Sakai Metrics at Indiana University

14. EPM: Sakai Project Metrics • Background • Designed to be Database Platform Independent • Based upon data contained within the Sakai Application/Database • Metrics are populated via PERL scripts. • Data Structures are: • Designed to ease reporting and sql query construction • Compatible with a variety of SQL Tools • Aqua Data Studio, DB Visualizer, SQL Developer, SQL4X, and others • Project Count Metric DDL is available at: • http://issues.sakaiproject.org/confluence/x/zHk(see file epm_tool_proj_20061102_ddl.sql) • SQL scripts and Perl scripts used to build and maintain EPM are available at the above link Sakai Metrics at Indiana University

15. EPM: Project Metrics Fact Table Sakai Metrics at Indiana University

16. EPM: Project Metrics Dimensions Sakai Metrics at Indiana University

17. EPM:Project Metrics ER* Diagram * ER - Entity Relational Sakai Metrics at Indiana University

18. EPM: SQL to Populate Project Metrics • Example SQL used to extract Project Counts • Project Count for Syllabus [SYL] • select count (site_id) PRJ from onc.sakai_sitewhere type = 'project' and SITE_ID in (select distinct (CONTEXTID) from onc.sakai_syllabus_item where ID in (select surrogatekey from onc.sakai_syllabus_data where not (title is null))); • File containing complete sql is available at: • http://issues.sakaiproject.org/confluence/x/zHk(see file epm_proj_upd_qry.sql) • Perl script used to extract and populate the Project Count Metrics is available at the above link • (see file update_epm_proj_ft.pl) Sakai Metrics at Indiana University

19. EPM: Project Metric Report and Chart Sakai Metrics at Indiana University

20. EPM: Oracle Database Performance Metrics • Background • Oracle Database Performance Metricsare NOT Database Platform Independent-- Oracle 10g Specific • Based upon data contained within: • Oracle Automatic Workload Repository [AWR] • Other External Sources • Database Extended Performance Metrics are populated via Perl. • Data Structures are: • NOT Specifically designed to ease reporting and sql query construction(these structures are supplied by Oracle not Sakai) • compatible with a variety of SQL Tools • Aqua Data Studio • DB Visualizer • SQL Developer • SQL4X • And others • Oracle Database EPM DDL is available at: • http://issues.sakaiproject.org/confluence/x/zHk (see file epm_ora_db_ddl.sql) • SQL scripts, Perl scripts and documentation used to build and maintain Database EPM are available at the above link. Sakai Metrics at Indiana University

21. EPM: Oracle Database • Metrics from Oracle’s AWR include: • Buffer Cache Hit Ratio -- AWR • CPU Usage Per Sec -- AWR • CPU Usage Per Txn (server) • Current Logons Count • Cursor Cache Hit Ratio • Database CPU Time Ratio • Database Time Spent Waiting • Host CPU Utilization (%) • Library Cache Hit Ratio • PGA Cache Hit % • RAM USAGE • Response Time Per Txn • RowCache Hit Ratio • SQL Response Time (%) • Shared Pool Free % • Sorts in Memory Sakai Metrics at Indiana University

22. EPM: Oracle Database • Metrics Whose Source is External to Oracle’s AWR include:(Metrics populated from external sources via Perl scripts) • CL Sessions -- Sakai Database • CL User Count -- Sakai Database • Size of Database -- Sakai Database • Percent Used -- Sakai Database • Space Used -- Sakai Database • CPU USAGE -- System Administration • RAM USAGE -- System Administration / AWR Sakai Metrics at Indiana University

23. EPM:Database Performance ER* Diagram * ER - Entity Relational Sakai Metrics at Indiana University

24. EPM: SQL to Populate Database Performance Metrics • Example SQL to Oracle’s Response Time per Transaction • select metric_name, to_char(begin_time,'MM-DD HH24') HR, round((minval/10),1) "Min", round((maxval/10),1)"Max", round((average/10),1) "Avg”from sys.dba_hist_sysmetric_summary where begin_time >= sysdate - 2 and begin_time <= sysdate and metric_id = 2109order by begin_time; • Perl script used to extract and populate the Tool Usage Metrics is available at: • http://issues.sakaiproject.org/confluence/x/zHk(see files extended_perf_metric_update.pl and extended_perf_metric_usercnt.pl) Sakai Metrics at Indiana University

25. EPM: Database Performance Reports • Example Response Time Report • Metric Hour Min Max Avg------------------------- ----------- ----- ----- -----Response Per Txn 11-06 04 0.2 10.4 1.8Response Per Txn 11-06 05 0.6 16.2 1.8Response Per Txn 11-06 06 0.6 12.7 1.9Response Per Txn 11-06 07 0.2 11.9 2.3Response Per Txn 11-06 08 0.2 30.5 2.5Response Per Txn 11-06 09 0.1 82.6 4.4 Sakai Metrics at Indiana University

26. EPM: Database Performance Charts • Example Response Time Chart Sakai Metrics at Indiana University

27. EPM: Database Performance Charts • Available to Oracle Enterprise Manager [Grid] • Via Database Links • Views from the Sakai Database into your OEM Repository • Contact us if you have such an interest: • damckee@indiana.edu • 812-855-7197 Sakai Metrics at Indiana University

28. That Which is Monitored, Improves Questions? Sakai Metrics at Indiana University