English

How To: Report geodatabase versioning statistics in an Oracle geodatabase

Summary

Instructions provided describe how to report geodatabase versioning statistics in an Oracle geodatabase. The SQL script provided below reports information about a versioned geodatabase in Oracle. The output lists the number of versions, the number of versions blocking the DEFAULT version from being compressed to state 0 and the owner.names of the first 5 blocking versions, the number of states, the number of entries in the state_lineages table, and the number of rows in the versioned table and delta tables.

Procedure

In SQL*Plus, connect as the sde user and execute the following SQL:

Code:
SET SERVEROUTPUT ON
DECLARE

TYPE cv_type IS REF CURSOR;
cv cv_type;
TYPE block_states IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
blocking_list block_states;
TYPE block_versions IS TABLE OF VARCHAR2(128);
blocking_ver block_versions := block_versions();

CURSOR tableinfo_cursor IS
SELECT owner,table_name,registration_id
FROM sde.table_registry
WHERE BITAND(sde.table_registry.object_flags,8) = 8
ORDER BY registration_id;

CURSOR ver_list IS
SELECT DISTINCT state_id
FROM sde.versions
WHERE name <> 'DEFAULT' AND owner <> 'SDE'
ORDER BY state_id;

compress_date DATE;
delta_table CHAR(1);
row_count NUMBER;
base_count NUMBER;
stmt VARCHAR2(2048);
state_id NUMBER;
lin_name NUMBER;
source_lin NUMBER;
com_anc_id NUMBER;
ver_blocking NUMBER DEFAULT 0;
cur_max_state NUMBER;
pos NUMBER;
idx INTEGER DEFAULT 1;
s1 NUMBER DEFAULT 0;
s2 NUMBER DEFAULT 0;
s3 NUMBER DEFAULT 0;
s4 NUMBER DEFAULT 0;
s5 NUMBER DEFAULT 0;

BEGIN

SELECT state_id, lineage_name INTO state_id, lin_name FROM sde.states
WHERE state_id = (SELECT state_id FROM sde.versions WHERE name = 'DEFAULT' AND owner = 'SDE');

SELECT COUNT(*) INTO row_count FROM sde.versions;

DBMS_OUTPUT.PUT_LINE(chr(10));
DBMS_OUTPUT.PUT_LINE(chr(160)||' Versioning statistics');
DBMS_OUTPUT.PUT_LINE(chr(160)||'=======================================================');
DBMS_OUTPUT.PUT_LINE(chr(160)||' Number of versions: '||row_count);

cur_max_state := state_id;

FOR ver_info IN ver_list LOOP

SELECT lineage_name INTO source_lin FROM sde.states WHERE state_id = ver_info.state_id;

SELECT MAX(lineage_id) INTO com_anc_id FROM
(SELECT lineage_id FROM sde.state_lineages WHERE lineage_name = lin_name AND lineage_id <= state_id
INTERSECT
SELECT lineage_id FROM sde.state_lineages WHERE lineage_name = source_lin AND lineage_id <= ver_info.state_id);

IF com_anc_id < state_id THEN
ver_blocking := ver_blocking + 1;

IF com_anc_id < cur_max_state THEN
IF blocking_list.COUNT < 5 AND blocking_list.EXISTS(com_anc_id) = FALSE THEN
blocking_list(com_anc_id) := com_anc_id;
ELSE
IF blocking_list.COUNT = 5 AND blocking_list.EXISTS(com_anc_id) = FALSE THEN
pos := blocking_list.FIRST;
WHILE pos IS NOT NULL LOOP
IF blocking_list(pos) = com_anc_id THEN
blocking_list.DELETE(pos);
blocking_list(com_anc_id) := com_anc_id;
cur_max_state := com_anc_id;
EXIT;
END IF;
pos := blocking_list.NEXT(pos);
END LOOP;
END IF;
END IF;
END IF;
END IF;
END LOOP;

DBMS_OUTPUT.PUT_LINE(chr(160)||' Number of versions blocking DEFAULT: '||ver_blocking);

IF ver_blocking > 0 THEN
DBMS_OUTPUT.PUT_LINE(chr(160)||' Top '||blocking_list.COUNT||' blocking versions...');

pos := blocking_list.FIRST;
WHILE pos IS NOT NULL LOOP
CASE
WHEN idx = 1 THEN s1 := blocking_list(pos);
WHEN idx = 2 THEN s2 := blocking_list(pos);
WHEN idx = 3 THEN s3 := blocking_list(pos);
WHEN idx = 4 THEN s4 := blocking_list(pos);
WHEN idx = 5 THEN s5 := blocking_list(pos);
END CASE;
idx := idx + 1;
pos := blocking_list.NEXT(pos);
END LOOP;

stmt := 'SELECT name FROM
(SELECT owner||''.''||name name FROM sde.versions
WHERE state_id IN
(SELECT DISTINCT lineage_id FROM sde.state_lineages
WHERE lineage_name IN
(SELECT DISTINCT lineage_name FROM sde.state_lineages
WHERE lineage_id IN (:state1,:state2,:state3,:state4,:state5)))
ORDER BY state_id) vers
WHERE rownum < 6';
EXECUTE IMMEDIATE stmt BULK COLLECT INTO blocking_ver USING s1, s2, s3, s4, s5;

FOR i IN 1..blocking_ver.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(chr(160)||' '||blocking_ver(i));
END LOOP;
END IF;

SELECT COUNT(*) INTO row_count FROM sde.states;
DBMS_OUTPUT.PUT_LINE(chr(160)||' Number of states: '||row_count);

SELECT COUNT(*) INTO row_count FROM sde.state_lineages;
DBMS_OUTPUT.PUT_LINE(chr(160)||' Number of state lineages: '||row_count);

SELECT COUNT(*) INTO row_count FROM sde.state_lineages WHERE lineage_name = lin_name AND lineage_id <= state_id;
DBMS_OUTPUT.PUT_LINE(chr(160)||' DEFAULT versions lineage length: '||row_count);

SELECT MAX(compress_start) INTO compress_date FROM sde.compress_log;
DBMS_OUTPUT.PUT_LINE(chr(160)||' Last compress: '||to_char(compress_date, 'MON-DD-YYYY'));

FOR table_info IN tableinfo_cursor LOOP
stmt := 'SELECT COUNT(*) FROM '||table_info.owner||'.'||table_info.table_name;
OPEN cv FOR stmt;
LOOP
FETCH cv INTO base_count;
EXIT WHEN cv%notfound;
END LOOP;
CLOSE cv;

FOR i IN 1..2 LOOP
IF i = 1 THEN
delta_table := 'a';
ELSE
delta_table := 'd';
END IF;

stmt := 'SELECT COUNT(*) FROM '||table_info.owner||'.'||delta_table||''||table_info.registration_id;
OPEN cv FOR stmt;
LOOP
FETCH cv INTO row_count;
EXIT WHEN cv%notfound;
END LOOP;
CLOSE cv;

IF i = 1 THEN
DBMS_OUTPUT.PUT_LINE(chr(10));
DBMS_OUTPUT.PUT_LINE(chr(160)||' Table - '||table_info.owner||'.'||table_info.table_name||' ('||table_info.registration_id||')');
DBMS_OUTPUT.PUT_LINE(chr(160)||' -- Base table: '||base_count);
DBMS_OUTPUT.PUT_LINE(chr(160)||' -- Adds table: '||row_count);

IF row_count > 0 THEN
stmt := 'SELECT ROUND(COUNT(*)/'||row_count||' * 100, 2)
FROM '||table_info.owner||'.a'||table_info.registration_id||'
WHERE sde_state_id IN (SELECT lineage_id
FROM sde.state_lineages
WHERE lineage_name = '||lin_name||' AND lineage_id <= '||state_id||')';
EXECUTE IMMEDIATE stmt INTO row_count;
DBMS_OUTPUT.PUT_LINE(chr(160)||' -- Percentage rows in the DEFAULT version lineage: '||row_count||'%');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(chr(160)||' -- Deletes table: '||row_count);

IF row_count > 0 THEN
stmt := 'SELECT ROUND(COUNT(*)/'||row_count||' * 100, 2)
FROM '||table_info.owner||'.d'||table_info.registration_id||'
WHERE deleted_at IN (SELECT lineage_id
FROM sde.state_lineages
WHERE lineage_name = '||lin_name||' AND lineage_id <= '||state_id||')';
EXECUTE IMMEDIATE stmt INTO row_count;
DBMS_OUTPUT.PUT_LINE(chr(160)||' -- Percentage rows in the DEFAULT version lineage: '||row_count||'%');
END IF;
END IF;
END LOOP;
END LOOP;
END;
/

  • Example output:

    Code:
    � Versioning statistics
    =======================================================
    �Number of versions: 7248
    �Number of versions blocking DEFAULT: 168
    � Top 5 blocking versions...
    � RANDY.REV_6847258
    � CHRIS.REV_7019237
    � CLARE.REV_7069544
    � LUCY.REV_7043793
    � SUSAN.REV_7069206
    �Number of states: 8371
    �Number of state lineages: 1804258
    �DEFAULT versions lineage length: 521
    �Last compress: SEP-21-2008

    �Table - ADMIN.PRIMARYCONDUCTOR (148)
    � -- Base table: 440065
    � -- Adds table: 15156
    � -- Percentage rows in the DEFAULT version lineage: 21.64%
    � -- Deletes table: 6056
    � -- Percentage rows in the DEFAULT version lineage: 34.79%

    �Table - ADMIN.BUILDING (338)
    � -- Base table: 58955
    � -- Adds table: 524
    � -- Percentage rows in the DEFAULT version lineage: 68.7%
    � -- Deletes table: 400
    � -- Percentage rows in the DEFAULT version lineage: 61%

Related Information