Products | Versions |
---|---|
Spotfire Server | All Versions |
Highly fragmented indexes in a database can lead to poor performance. Therefore when seeing performance issues in activities in Spotfire that rely heavily on the application database (using Information Designer, Administration Manager, Library Administration tool, etc), it can be useful to ensure the indices in the TIBCO Spotfire Server application database are not in poor condition.
It is the responsibility of your database administrator to ensure that the indices in the Spotfire application database are kept up to date and to perform any required index maintenance. A general best practice is to reorganize indexes with more than 10 percent and up to 30 percent fragmentation. An index rebuild operation could be more appropriate if you have fragmentation greater than 30 percent. Fragmentation of 10 percent or less should not be considered a problem.
SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id WHERE indexstats.database_id = DB_ID() ORDER BY indexstats.avg_fragmentation_in_percent desc
SET SERVEROUTPUT ON; DECLARE Fragmentation number; CURSOR cs IS select * from all_indexes where OWNER='SPOTFIRE_DB103'; index_row cs%ROWTYPE; cnt number; hgt number; leaf_blocks number; leaf_rows number; del_lf number; BEGIN fragmentation := 20; cnt := 0; OPEN cs; LOOP FETCH cs INTO index_row; EXIT WHEN cs%NOTFOUND; IF index_row.segment_created = 'NO' then DBMS_OUTPUT.PUT_LINE('*TABLE_NAME*'||' - '||index_row.TABLE_NAME||' , '||'*INDEX*'||' - '||index_row.INDEX_NAME|| ' , ' ||'*Percentage*'|| ' - ' ||'0'|| '%'); cnt:=cnt+1; continue; END IF; EXECUTE IMMEDIATE 'ANALYZE INDEX '||index_row.INDEX_NAME||' VALIDATE STRUCTURE'; select height, lf_blks, lf_rows, del_lf_rows into hgt, leaf_blocks, leaf_rows, del_lf from index_stats; if leaf_rows=0 then DBMS_OUTPUT.PUT_LINE('*TABLE_NAME*'||' - '||index_row.TABLE_NAME||' , '||'*INDEX*'||' - '||index_row.INDEX_NAME|| ' , ' ||'*Percentage*'|| ' - ' ||'0'|| '%'); cnt:=cnt+1; CONTINUE; END IF; IF leaf_rows>100 AND del_lf>0 then select round((del_lf_rows/lf_rows)*100,2) into fragmentation from index_stats; DBMS_OUTPUT.PUT_LINE('*TABLE_NAME*'||' - '||index_row.TABLE_NAME||' , '||'*INDEX*'||' - '||index_row.INDEX_NAME|| ' , ' ||'*Percentage*'|| ' - ' ||fragmentation|| '%'); ELSE DBMS_OUTPUT.PUT_LINE('*TABLE_NAME*'||' - '||index_row.TABLE_NAME||' , '||'*INDEX*'||' - '||index_row.INDEX_NAME|| ' , ' ||'*Percentage*'|| ' - ' ||'0'|| '%'); END IF; cnt := cnt+1; END LOOP; DBMS_OUTPUT.PUT_LINE('TOTAL INDEXES ANALYZED'||' - '||cnt||''); CLOSE cs; END;