English

How To: Report compress statistics for Oracle in SQL*Plus

Summary

This article provides examples on how to query the ArcSDE compress_log for obtaining the duration of each compress and average compress durations.

Procedure

The examples below provide different approaches for reporting timing statistics about compress. The SQL statements access the ArcSDE compress_log table and subtract the compress_start date attribute, which represents the moment when compress was started from the compress_end date attribute, which represents when the compress command completed. The output from the various SQL statements report statistics about the average compress duration and the average number of states deleted.

To execute each query, connect in SQL*Plus as the sde user, copy the SQL statement from the Knowledge Base article, and paste the SQL statement into the console window. The results are dependent on the number of rows present in the sde.compress_log.

The following query reports the day when each compress was started: each compress' duration broken down by 'days', 'hours', 'minutes', 'seconds' and the number of states removed.

Code:
COL AVG_COMPRESS_TIME FORMAT A45

SELECT sde_id, compress_start,
TRUNC(compress_end - compress_start) DAYS,
TRUNC(MOD((compress_end - compress_start) * 24, 24)) HOURS,
TRUNC(MOD((compress_end - compress_start) * 24 * 60, 60)) MINUTES,
TRUNC(MOD((compress_end - compress_start) * 24 * 60 * 60, 60)) SECONDS,
start_state_count - end_state_count "NUM_STATES_DELETED"
FROM sde.compress_log
WHERE compress_end IS NOT NULL
ORDER BY compress_start ASC;

SDE_ID COMPRESS_ DAYS HOURS MINUTES SECONDS NUM_STATES_DELETED
---------- --------- ---------- ---------- ---------- ---------- ------------------
7930639 18-SEP-08 0 1 15 59 4701
7938381 19-SEP-08 0 2 47 41 7225
7943973 20-SEP-08 0 1 49 59 8532
7949027 21-SEP-08 0 0 1 1 1
7956584 22-SEP-08 0 0 50 49 4545


The next query reports the same information as the previous query, but the compresses' duration is concatenated into a single string value.

Code:
SELECT sde_id, compress_start,
TRUNC(compress_end - compress_start) ||' DAYS, '||
TRUNC(MOD((compress_end - compress_start) * 24, 24)) || ' HOURS, '||
TRUNC(MOD((compress_end - compress_start) * 24 * 60, 60)) ||' MINUTES, '||
TRUNC(MOD((compress_end - compress_start) * 24 * 60 * 60, 60)) || ' SECONDS' "AVG_COMPRESS_TIME",
start_state_count - end_state_count "NUM_STATES_DELETED"
FROM sde.compress_log
WHERE compress_end IS NOT NULL
ORDER BY compress_start ASC;

SDE_ID COMPRESS_ AVG_COMPRESS_TIME NUM_STATES_DELETED
---------- --------- --------------------------------------------- ------------------
7930639 18-SEP-08 0 DAYS, 1 HOURS, 15 MINUTES, 59 SECONDS 4701
7938381 19-SEP-08 0 DAYS, 2 HOURS, 47 MINUTES, 41 SECONDS 7225
7943973 20-SEP-08 0 DAYS, 1 HOURS, 49 MINUTES, 59 SECONDS 8532
7949027 21-SEP-08 0 DAYS, 0 HOURS, 1 MINUTES, 1 SECONDS 1
7956584 22-SEP-08 0 DAYS, 0 HOURS, 50 MINUTES, 49 SECONDS 4545


The next query simply reports the average compress duration broken down by 'days', 'hours', 'minutes', 'seconds' and the number of states removed.

Code:
SELECT TRUNC(avg_time) DAYS,
TRUNC(MOD((avg_time) * 24, 24)) HOURS,
TRUNC(MOD((avg_time) * 24 * 60, 60)) MINUTES,
TRUNC(MOD((avg_time) * 24 * 60 * 60, 60)) SECONDS,
avg_states AVG_NUM_STATES_DELETED
FROM
(SELECT AVG(compress_end - compress_start) avg_time,
ROUND(AVG(start_state_count - end_state_count)) avg_states
FROM sde.compress_log);

DAYS HOURS MINUTES SECONDS AVG_NUM_STATES_DELETED
---------- ---------- ---------- ---------- ----------------------
0 1 45 18 1308


The next query reports the same information as the previous query, but the compresses' duration is concatenated into a single string value.

Code:
SELECT TRUNC(avg_time) ||' DAYS, '||
TRUNC(MOD((avg_time) * 24, 24)) || ' HOURS, '||
TRUNC(MOD((avg_time) * 24 * 60, 60)) ||' MINUTES, '||
TRUNC(MOD((avg_time) * 24 * 60 * 60, 60)) || ' SECONDS' "AVG_COMPRESS_TIME",
avg_states AVG_NUM_STATES_DELETED
FROM
(SELECT AVG(compress_end - compress_start) avg_time,
ROUND(AVG(start_state_count - end_state_count)) avg_states
FROM sde.compress_log);

AVG_COMPRESS_TIME AVG_NUM_STATES_DELETED
--------------------------------------------- ----------------------
0 DAYS, 1 HOURS, 45 MINUTES, 18 SECONDS 1308


The final query below reports the average compress time for each occurrence of a compress grouped by the number of states deleted broken into ranges of 500 states.

Code:
COL states_deleted_range FORMAT A20

SELECT states_deleted_range, occurences,
/* TRUNC(avg_time) ||' DAYS, '|| */
TRUNC(MOD((avg_time) * 24, 24)) || ' HOURS, '||
TRUNC(MOD((avg_time) * 24 * 60, 60)) ||' MINUTES, '||
TRUNC(MOD((avg_time) * 24 * 60 * 60, 60)) || ' SECONDS' "AVG_COMPRESS_TIME"
FROM (WITH compress_log_stats AS
(SELECT CASE WHEN state_range < 1 THEN 0
ELSE state_range
END states_deleted, compress_start, compress_end
FROM (SELECT compress_start, compress_end, TRUNC((start_state_count - end_state_count)/500) state_range
FROM sde.compress_log
WHERE end_state_count IS NOT NULL))
SELECT CASE WHEN states_deleted = 0 then '1 - 500'
ELSE TO_CHAR(states_deleted * 500) ||' - '|| TO_CHAR(states_deleted * 500 + 499)
END states_deleted_range,
COUNT(*) occurences, AVG(compress_end - compress_start) avg_time
FROM sde.compress_log_stats
GROUP BY states_deleted
ORDER BY states_deleted);

STATES_DELETED_RANGE OCCURENCES AVG_COMPRESS_TIME
-------------------- ---------- ------------------------------------
1 - 500 1041 0 HOURS, 32 MINUTES, 29 SECONDS
500 - 999 209 0 HOURS, 27 MINUTES, 59 SECONDS
1000 - 1499 170 0 HOURS, 55 MINUTES, 43 SECONDS
1500 - 1999 205 1 HOURS, 55 MINUTES, 32 SECONDS
2000 - 2499 167 3 HOURS, 38 MINUTES, 53 SECONDS
2500 - 2999 97 3 HOURS, 53 MINUTES, 48 SECONDS
3000 - 3499 107 3 HOURS, 22 MINUTES, 1 SECONDS
3500 - 3999 102 4 HOURS, 24 MINUTES, 28 SECONDS
4000 - 4499 32 8 HOURS, 6 MINUTES, 53 SECONDS
4500 - 4999 18 6 HOURS, 18 MINUTES, 51 SECONDS
5000 - 5499 13 11 HOURS, 34 MINUTES, 57 SECONDS
5500 - 5999 16 8 HOURS, 25 MINUTES, 3 SECONDS
6000 - 6499 9 9 HOURS, 4 MINUTES, 39 SECONDS
6500 - 6999 5 5 HOURS, 25 MINUTES, 6 SECONDS
7000 - 7499 5 2 HOURS, 6 MINUTES, 34 SECONDS
7500 - 7999 2 2 HOURS, 9 MINUTES, 49 SECONDS
8000 - 8499 3 3 HOURS, 15 MINUTES, 7 SECONDS
8500 - 8999 2 1 HOURS, 56 MINUTES, 10 SECONDS
9000 - 9499 4 4 HOURS, 2 MINUTES, 55 SECONDS
9500 - 9999 2 1 HOURS, 19 MINUTES, 40 SECONDS
10000 - 10499 1 2 HOURS, 13 MINUTES, 48 SECONDS
10500 - 10999 1 10 HOURS, 57 MINUTES, 54 SECONDS
11000 - 11499 2 19 HOURS, 53 MINUTES, 9 SECONDS
12000 - 12499 2 4 HOURS, 51 MINUTES, 49 SECONDS
13500 - 13999 1 1 HOURS, 19 MINUTES, 14 SECONDS