that which is monitored, improves
DESCRIPTION
That Which is Monitored, Improves. Dan McKee & Megan May. Sakai Metrics at Indiana University. At Indiana University we believe, That Which is Monitored, Improves How does Indiana do this? Extended Performance Metrics [EPM] - PowerPoint PPT PresentationTRANSCRIPT
12-Jun-2007 Sakai Metrics at Indiana University 1
That Which is Monitored, Improves
Dan McKee&
Megan May
12-Jun-2007 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
12-Jun-2007 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 60 Gbs The typical number of CL Sessions at any one point in
time is around 5,000
12-Jun-2007 Sakai Metrics at Indiana University 4
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, DBVisualizer, SQL Developer,SQL4X and others Tool Usage Metric DDL is available at:
http://confluence.sakaiproject.org/confluence/x/cKc(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)
12-Jun-2007 Sakai Metrics at Indiana University 5
EPM: Tool Usage Metrics Fact Table
12-Jun-2007 Sakai Metrics at Indiana University 6
EPM: Tool Usage Dimensions
12-Jun-2007 Sakai Metrics at Indiana University 7
EPM:Tool Usage Metrics ER* Diagram
* ER - Entity Relational
12-Jun-2007 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://confluence.sakaiproject.org/confluence/x/cKc
(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)
12-Jun-2007 Sakai Metrics at Indiana University 9
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
12-Jun-2007 Sakai Metrics at Indiana University 10
EPM: Usage Charts
Tool Usage for Courses by Semester
0
500
1000
1500
2000
2500
3000
3500
4000
4500
5000
Ann
ounc
emen
t
Ass
ignm
ent
Cal
enda
r
Cha
t
Dis
cuss
ion
Dro
p B
oxes
E-M
ail A
rchi
ve
Gra
debo
ok
Mes
sage
Cen
ter
Pod
cast
s
Pos
t 'E
m
Res
ourc
es
Site
Info
Gro
ups
Syl
labu
s
Test
& S
urve
ys CL
Test
& S
urve
y(o
rigin
al)
Wik
i
FA05 SP06 SU06 FA06
12-Jun-2007 Sakai Metrics at Indiana University 11
EPM: How is the migration progressing?
Course Section Adoption(of courses using a CMS)
0%10%20%30%40%50%60%70%80%
FA05 SP06 SU06 FA06 SP07
% Using original Oncourse % using Oncourse CL
Course Section Adoption
% Using CMS % Using original Oncourse % using Oncourse CL
FA05 43% 71% 29%
SP06 36% 60% 40%
SU06 22% 61% 39%
FA06 37% 47% 53%
SP07 36% 41% 59%
12-Jun-2007 Sakai Metrics at Indiana University 12
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
12-Jun-2007 Sakai Metrics at Indiana University 13
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
12-Jun-2007 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://confluence.sakaiproject.org/confluence/x/cKc
(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
12-Jun-2007 Sakai Metrics at Indiana University 15
EPM: Project Metrics Fact Table
12-Jun-2007 Sakai Metrics at Indiana University 16
EPM: Project Metrics Dimensions
12-Jun-2007 Sakai Metrics at Indiana University 17
EPM:Project Metrics ER* Diagram
* ER - Entity Relational
12-Jun-2007 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_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://confluence.sakaiproject.org/confluence/x/cKc
(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)
12-Jun-2007 Sakai Metrics at Indiana University 19
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%
5%
10%
5%5%1%7%3%10%
1%1%
18%
3%5%
3% 7%
Announcement
Assignment
Calendar
Chat
Discussion
Drop Boxes
E-Mail Archive
Gradebook
Message Center
Podcasts
Post 'Em
Resources
Site Info Groups
Syllabus
Test & Surveys
Wiki
12-Jun-2007 Sakai Metrics at Indiana University 20
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://confluence.sakaiproject.org/confluence/x/cKc
(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.
12-Jun-2007 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
12-Jun-2007 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 / AWR RAM USAGE -- System Administration / AWR
12-Jun-2007 Sakai Metrics at Indiana University 23
EPM:Database Performance ER* Diagram
* ER - Entity Relational
12-Jun-2007 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://confluence.sakaiproject.org/confluence/x/cKc
(see files extended_perf_metric_update.pl and extended_perf_metric_usercnt.pl)
12-Jun-2007 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
12-Jun-2007 Sakai Metrics at Indiana University 26
Example Response Time Chart
EPM: Database Performance Charts
12-Jun-2007 Sakai Metrics at Indiana University 27
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
12-Jun-2007 Sakai Metrics at Indiana University 28
That Which is Monitored, Improves
Questions?