that which is monitored, improves

06-Nov-2006 Sakai Metrics at Indiana University 1

That Which is Monitored, Improves

Dan McKee&

Megan May


Sakai Metrics at Indiana University

At Indiana University we believe,


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 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


EPM*: Sakai Tool Usage 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 file epm_tool_proj_20061102_ddl.sql)

SQL scripts and Perl scripts used to build and maintain EPM are available at the above link.

(* Extended Performance Metrics)


EPM: Tool Usage Metrics Fact Table


EPM: Tool Usage Dimensions


EPM:Tool Usage Metrics ER* Diagram

* ER - Entity Relational


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)


Tool Usage by Semester for All Course Sites

FA05 SP06 SU06 FA06 % Change FA05to FA06

Announcement 3014 3338 886 4622 53%

Assignment 975 1002 247 1721 77%

Calendar 960 897 233 1393 45%

Chat 1264 1152 277 1213 -4%

Discussion 378 402 33 216 -43%

Drop Boxes 77 73 19 11 -86%

E-Mail Archive 1270 1328 275 1919 51%

Gradebook 1868 2100 645 3099 66%

Message Center 36 397 318 2055 5608%

PM 34 380 265 1843 5321%

DF 20 52 96 468 2240%

Podcasts - - - 21 N/A

Post 'Em 8 70 15 243 2938%

Resources 1939 1990 557 3293 70%

Site Info Groups 15 65 23 244 1527%

Syllabus 2122 2549 702 3944 86%

Test & Surveys CL 290 250 23 0 -100%

Test & Survey (original) - - - 385 N/A

# Active 3014 3338 886 4622 53%

Sample Report


EPM: Usage Charts

Tool Usage for Courses by Semester

0

500

1000

1500

2000

2500

3000

3500

4000

4500

5000

Announcement

AssignmentCalendar

Chat

DiscussionDrop BoxesE-Mail Archive

GradebookMessage

CenterPodcastsPost 'Em

ResourcesSite InfoGroupsSyllabus

Test & Surveys

CL

Test & Survey

(original)

Wiki

FA05 SP06 SU06 FA06


EPM: How is the migration progressing?

Course Section Adoption

% Using CMS % Using original Oncourse % using Oncourse CL

FA05 43% 71% 29%

SP06 36% 60% 40%

SU06 22% 61% 39%

FA06 35% 47% 53%

Course Section Adoption(of courses using a CMS)

0%

10%

20%

30%

40%

50%

60%

70%

80%

FA05 SP06 SU06 FA06

% Using original Oncourse % using Oncourse CL


EPM: Tool Usage ReportsFall 06 by Campus

BL CO EA IN KO SB NW SE

Announcement 2393 107 76 1106 194 303 214 226

Assignment 854 56 41 479 55 90 75 67

Calendar 700 54 31 367 62 85 43 50

Chat 503 39 12 407 43 80 53 76

Discussion 104 14 12 62 8 6 8 0

Drop Boxes 4 0 0 5 0 0 2 0

E-Mail Archive 1411 8 7 364 19 51 20 34

Gradebook 1623 76 30 705 121 229 145 165

Message Center 712 123 39 734 111 132 109 90

PM 596 121 33 684 105 125 100 75

DF 205 13 9 136 32 24 27 19

Podcasts 8 3 0 6 0 4 0 0

Post 'Em 181 0 5 26 20 6 0 2

Resources 1872 57 45 756 121 174 136 123

Site Info Groups 126 3 2 65 35 7 1 1

Syllabus 1972 131 71 904 133 283 192 255

Test & Surveys (CL) 0 0 0 0 0 0 0 0

Test & Survey (original) 101 54 14 125 38 15 20 8

Wiki 89 7 6 41 8 7 6 2

# Active 2393 131 76 1106 194 303 214 255


EPM: Tool Usage Charts

Fall 06 Campus Usage Breakdown

BL CO EA IN KO NW SB SE

% Using CMS 29% 56% 34% 46% 36% 30% 31% 38%

% Using original 28% 49% 70% 64% 36% 40% 53% 61%

% using CL 72% 51% 30% 36% 64% 60% 47% 39%

0% 10% 20% 30% 40% 50% 60% 70% 80%

BL

CO

EA

IN

KO

NW

SB

SE

Section Adoption of Courses by Campus(of courses using a CMS)

% Using original % using CL

Active Oncourse CL Course Sites by Campus

51%

3%2%

24%

4%

6%5%

5%

BL CO EA IN KO SB NW SE


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


EPM: Project Metrics Fact Table


EPM: Project Metrics Dimensions


EPM:Project Metrics ER* Diagram



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_site

where 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)


EPM: Project Metric Report and Chart

Project Sites

Announcement 479

Assignment 159

Calendar 297

Chat 151

Discussion 170

Drop Boxes 50

E-Mail Archive 189

Gradebook 102

Message Center 250

PM 212

DF 112

Podcasts 13

Post 'Em 17

Resources 528

Site Info Groups 75

Syllabus 145

Test & Surveys 116

Wiki 113

Tool Usage for All Project Sites

16%

6%

10%

5%6%2%7%4%

9%0%1%

18%

3%5% 4% 4%

Announcement

Assignment

Calendar

Chat

Discussion

Drop Boxes

E-Mail Archive

Gradebook

Message Center

Podcasts

Post 'Em

Resources

Site Info Groups

Syllabus

Test & Surveys

Wiki


EPM: Oracle Database Performance Metrics Background

Oracle Database Performance Metrics are 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.


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


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


EPM:Database Performance ER* Diagram



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)


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


Example Response Time Chart

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:

[email protected] 812-855-7197

EPM: Database Performance Charts



Questions?

that which is monitored, improves

Documents