Run the follow groovy script to generate a list of the topologies with the highest row counts of data.
import com.quest.nitro.service.util.MBeanRef;
import com.quest.nitro.service.util.JDBCHelper;
import javax.management.ObjectName;
import java.sql.*;
def smSvc = new MBeanRef(ObjectName.getInstance("com.quest.nitro:service=StorageManager")).ref();
def cfg = smSvc.getStorageConfiguration();
def output = new StringBuilder();
def ds = JDBCHelper.getDataSource(null);
def conn = null;
try {
conn = JDBCHelper.getConnection(ds);
def stmt = conn.createStatement();
for (obsStorage in cfg.storageTypes) {
def idCol;
switch (obsStorage.storageType) {
case "AggregateMetric" :
idCol = "oma_object_id";
break;
case "ScalarMetric" :
idCol = "oms_object_id";
break;
case "String" :
idCol = "os_object_id";
break;
case "Binary" :
idCol = "ob_object_id";
break;
}
def obsGen = obsStorage.generations.last();
int i = 0;
for (obsTimeslice in obsGen.timeslices.reverse().subList(0, Math.min(3, obsGen.timeslices.size()))) {
for (obsTable in obsTimeslice.tables) {
output.append(obsTable.name).append(" row counts by type:\n");
def rs = null;
try {
rs = stmt.executeQuery("""
SELECT tt_name AS type_name,
COUNT(*) AS num_rows
FROM ${obsTable.name},
topology_object,
topology_type
WHERE to_id = ${idCol}
AND tt_id = to_type
GROUP BY tt_name
ORDER BY 2 DESC
""");
while (rs.next()) {
output.append(rs.getString(1)).append(",").append(rs.getInt(2)).append('\n');
}
}
catch (SQLException sqle) {
output.append("Failed to query row counts: ").append(sqle).append("\n");
}
finally {
JDBCHelper.cleanUp(rs);
}
output.append("\n\n");
}
}
}
}
catch (Exception e) {
output.append("Failed to obtain connection: ").append(e).append('\n');
}
finally {
JDBCHelper.cleanUp(conn);
}
return output.toString();
1). Enlarge all frequency for Oracle, DB2, or SQL Server collections that use the more number of rows so that the collections run less often.
For example, if DBO_Datafile_IO_Activity, DBO_Tablespace, and DBO_Job_Status had the most number of rows, then change the frequency for all of the agents in the Administration panel (Administration | Collections) for the Datafile IO Activity", "Datafiles Storage","Filesystem Disk","Filesystem IO", "Tablespaces Storage","Jobs Queue","Job Status", and "IO Activity" collections.
Then increase
"Filesystem Disk","Datafiles Storage" and "Tablespaces Storage" to 6 or 8 hours.
"IO Activity", "Datafile IO Activity" and "Filesystem IO" to 10 minutes.
"Jobs Queue" and "Job Status" to 10 minutes.
2). Then change the Retention Policy for those same collections
For example, "DBO_Datafile_IO_Activity", "DBO_Datafile","DBO_Tablespace","DBO_Job_Status","DBO_IO_Activity","DBO_OS_File_System_Disk","DBO_Job_Status","DBO_Locks_Activity" , change the purge date from 2 months to 15 days or 7 days.
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center