E-learn Weblog of Willem van Valkenburg

Gisteren was ik bij een lunchbijeenkomst waar het onderzoek over de verschillende toetssystemen die binnen de TU worden gebruikt. Hierbij is gekeken naar Etude, COZ en Blackboard.

Full story »

je kan zelf de gewenste periode instellen door de data aan te passen:

/* You may need to use BB_BB60_STATS SCHEMA if searching for old dates */

SELECT COUNT(*)
  FROM (SELECT USER_PK1
          FROM BB_BB60.ACTIVITY_ACCUMULATOR a
         WHERE TIMESTAMP > TO_DATE('01/02/2007', 'DD/MM/YYYY')
            AND TIMESTAMP < TO_DATE('01/03/2007', 'DD/MM/YYYY')
           AND USER_PK1 IS NOT NULL
        GROUP BY a.session_id);

select c.course_id "Course ID", c.course_name "Course Name",
c.available_ind "Currently Available" from course_main.c left join
activity_accumulator a on c.pk1 = a.course_pk1 where (a.event_type is
null)

select t.teller
,      sum(decode(sign(teller - trunc(last_call_et/60/60)),'0',1,0)) aantal
,      rpad('*',sum(decode(sign(teller - trunc(last_call_et/60/60)),'0',1,0))/5,'*') leuke_sterretjes
from v$session
,    (select rownum-1 teller
      from dba_objects
      where rownum <= (select trunc(max(last_call_et/60/60))+1
                         from v$session where username is not null)
     ) t
where username is not null
and machine like 'bbapp%'
group by t.teller order by 1,2;

select machine
,      to_char(logon_time,'mm/dd/yyyy hh24:mi:ss') Inlogdatum
,      last_call_et Sec_Inactief
,      trunc((sysdate-logon_time)*24)||' H '||trunc(((sysdate-logon_time)-trunc((sysdate-logon_time)*24)/24)*24*60)||' M' aangelogd
,      to_char(trunc(last_call_et/3600,0))||' H '||to_char(trunc((last_call_et - trunc(last_call_et/3600,0)*3600) / 60,0))||' M' inactief
from v$session
where username is not null
order by last_call_et desc;

select machine
,      count(*)
from v$session
group by machine
order by machine;

Select username
, sql_text
, count(*) aantal
,trunc(avg(last_call_et)/60) avg_idle_time
,trunc(min(last_call_et)/60) min_idle_time
,trunc(max(last_call_et)/60) max_idle_time
from v$session s
, v$sqlarea a
where a.address = s.PREV_SQL_ADDR
and s.username not in ('SYSTEM','DBSNMP','SYS')
and s.username is not null
group by username,sql_text order by 4 desc;

Bij een “connection leak” geeft een module de connectie niet terug aan de pool. We zien dan in de loop van de tijd voor een bepaald statement het veld “aantal” geleidelijk oplopen,avg_idle is hoog, max_idle is hoog en min_idle wordt 0 iedere keer als de lekkende module gestart wordt. Het sql statement is het laatste statement in de module die de connectie niet terug geeft aan de pool.

select
  to_char(timestamp,'YYYYMMDD'),
  count(distinct(user_pk1))
from
  (
  select user_pk1, timestamp from bb_bb60_stats.activity_accumulator
  union
  select user_pk1, timestamp from bb_bb60.activity_accumulator
  )
where
  timestamp between to_date('01092004','DDMMYYYY') and to_date('01112004','DDMMYYYY')  
group by
  to_char(timestamp,'YYYYMMDD')
order by
  1

select cm.pk1
,      cm.course_name
from bb_bb60.gateway_course_categories gcg
,    (select gc.pk1
      from bb_bb60.gateway_categories gc
      start with gc.pk1 = <pk1_gateway_categories>
      connect by prior gc.pk1 = gc.gatewaycat_pk1
     ) gc1
,    bb_bb60.course_main cm
where gc1.pk1 = gcg.gatewaycat_pk1
and cm.pk1 = gcg.crsmain_pk1
order by cm.course_name

SELECT PFLIO_PK1, COUNT(PFLIO_PK1)

FROM BB_BB60.PFLIO_ITEM

GROUP BY PFLIO_PK1