SELECT max(timestamp) FROM ACTIVITY_ACCUMULATOR; returns: MAX(TIMESTAMP) ——————- 2011-10-26 17:08:04 SELECT count(*) FROM activity_accumulator_queue; returns COUNT(*) ———- 14045918 select job,broken from user_jobs where what=’activity_accumulator_update;’; JOB B ———- – 244 N 15:11:57 SQL> select * from dba_jobs_running; no rows selected 15:12:07 SQL> select * from activity_accumulator_status; QUEUE_FLAG ———- 1 — 15:13:57 SQL> SELECT a.sid, c.serial#, a.job, a.failures, to_char(a.this_date, ‘mm/dd/yyyy hh:mi pm’) startdatetime, b.what FROM dba_jobs_running a, dba_jobs b, v$session c WHERE a.job = b.job AND a.sid = c.sid order by a.this_date; no rows selected —- SELECT MIN(TIMESTAMP) FROM ACTIVITY_ACCUMULATOR_QUEUE; SELECT MAX(TIMESTAMP) FROM ACTIVITY_ACCUMULATOR_QUEUE; select count(*) from ACTIVITY_ACCUMULATOR_QUEUE; 2011/08/98: 08:49:26 SQL> @time Session altered. Elapsed: 00:00:00.00 MAX(TIMESTAMP) ——————- 2011-09-08 22:25:30 Elapsed: 00:00:00.00 COUNT(*) ———- 118824 2011/11/15: 15:14:15 SQL> SELECT MIN(TIMESTAMP) FROM ACTIVITY_ACCUMULATOR_QUEUE; MIN(TIMESTAMP) ——————- 2011-10-26 17:08:04 15:13:17 SQL> SELECT MAX(TIMESTAMP) FROM ACTIVITY_ACCUMULATOR_QUEUE; MAX(TIMESTAMP) ——————- 2011-11-15 15:16:53 SELECT MIN(TIMESTAMP) FROM ACTIVITY_ACCUMULATOR; [ran for 13 minutes, and I gave up] 15:18:07 SQL> SELECT MAX(TIMESTAMP) FROM ACTIVITY_ACCUMULATOR; MAX(TIMESTAMP) ——————- 2011-10-26 17:10:07 <task-entry key=”bb.stats.purging” version=”60″> <task classname=”blackboard.platform.tracking.PurgeApplicationTask”> <property name=”delay” value=”21600000″ /> <property name=”period” value=”86400000″ /> <property name=”xml.registered.delay” value=”1:00″ /> <property name=”xml.registered.period” value=”24″ /> <property name=”days_to_keep” value=”180″ /> <property name=”target” value=”live” /> </task> </task-entry> is list in one, and only one, of the application servers bb-tasks.unix.xml file. So it sounds like things are not moving from the activity_accumulator_queue into the activity_accumulator, from the KB: The likely cause of the initial failure of the ACTIVITY_ACCUMULATOR_UPDATE procedure in this case was a database restart while the job was running. This leaves the QUEUE_FLAG set to 1, which causes subsequent invocations of the stored procedure ACTIVITY_ACCUMULATOR_UPDATE to exit without moving the data. so we’ll need to manually fix some things: select job,broken from user_jobs where what=’activity_accumulator_update;’; select * from dba_jobs_running; EXEC DBMS_JOB.BROKEN(244, true); select * from activity_accumulator_status; update activity_accumulator_status set queue_flag=0; commit; exec activity_accumulator_update; [started 3:32 pm on 11/15/2011] then EXEC DBMS_JOB.BROKEN(244, false); to re-enable the job. I checked the next morning: SELECT max(timestamp) FROM ACTIVITY_ACCUMULATOR; MAX(TIMESTAMP) ——————- 2011-10-26 18:04:59 09:49:11 SQL> SELECT MIN(TIMESTAMP) FROM ACTIVITY_ACCUMULATOR_QUEUE; MIN(TIMESTAMP) ——————- 2011-10-26 18:04:59 Elapsed: 00:17:23.35 So it’s only progressed one hour in the activity_accumulator and activity_accumulator_queue after 18 hours of running. Since I started running this process manually, we’ve been getting reports of files taking a long time to upload again. I’ve replicated this behavior myself. Checking Xythos statistics:
09:54:17 SQL> select column_name, last_analyzed, density, num_distinct from dba_tab_col_statistics where table_name ='XYF_URLS'; COLUMN_NAME LAST_ANALYZED DENSITY NUM_DISTINCT ------------------------------ ------------------- ---------- ------------ PATH_ID 2011-11-15 23:01:39 1.9402E-07 5154197 PARENT_ID 2011-11-15 23:01:39 6.3024E-07 1586688 FILE_NAME 2011-11-15 23:01:39 5.0547E-07 1978368 FILE_ID 2011-11-15 23:01:39 1.9402E-07 5154197 VIRTUAL_SERVER 2011-11-15 23:01:39 9.8404E-08 1 FULL_PATH 2011-11-15 23:01:39 .001196172 5105152 6 rows selected. Elapsed: 00:00:05.42
09:54:30 SQL> select count(1) from bblearn_cms_doc.xyf_urls;COUNT(1) ———- 5156913 So I ran:
10:03:35 SQL> @set_stats PL/SQL procedure successfully completed. Elapsed: 00:00:26.25
declare v_cnt number; v_density number; begin select count(1) into v_cnt from bblearn_cms_doc.xyf_urls; v_density := 1 / v_cnt; dbms_stats.set_column_stats(ownname=>'BBLEARN_CMS_DOC',tabname=>'XYF_URLS',colname=>'FULL_PATH',distcnt=>v_cnt,density=>v_density); end; /
10:04:05 SQL> select column_name, last_analyzed, density, num_distinct from dba_tab_col_statistics where table_name ='XYF_URLS';
COLUMN_NAME LAST_ANALYZED DENSITY NUM_DISTINCT ------------------------------ ------------------- ---------- ------------ PATH_ID 2011-11-15 23:01:39 1.9402E-07 5154197 PARENT_ID 2011-11-15 23:01:39 6.3024E-07 1586688 FILE_NAME 2011-11-15 23:01:39 5.0547E-07 1978368 FILE_ID 2011-11-15 23:01:39 1.9402E-07 5154197 VIRTUAL_SERVER 2011-11-15 23:01:39 9.8404E-08 1 FULL_PATH 2011-11-16 10:04:02 1.9391E-07 5157032
6 rows selected.
Elapsed: 00:00:01.32
10:07:33 SQL> select count(1) from bblearn_cms_doc.xyf_urls;
COUNT(1) ---------- 5157087Even after doing this, there was no change in file upload speeds. exec activity_accumulator_update; is still manually running. I’ve killed that manual process, and executed: 10:06:35 SQL> delete FROM activity_accumulator WHERE pk1 IN (SELECT pk1 FROM activity_accumulator_queue); (The job processes 2000 rows at a time, then deletes them. If the job is cancelled, you need to run the following command in order to delete those lines) .]]>