How to ignore table tuning advisors?

Question

Hello,

I find how to get DPA to ignore Query advisors but I don't find the same to turn off table tuning advisors. We have 2 tables in an SAP system that I can't do anything about (without SAP to change them) and they are red all of the time. I would like to ignore these.

Is that possible?

Thanks!

melanie.boyd · Answer

Yes, you would have to turn off advisors for all inefficient queries that hit the table, as described here: Turn off DPA table tuning advice for a table - SolarWinds Worldwide, LLC. Help and Support

jaminsql · Answer

jsql

Did you try the above item also from the GUI?

Reading some on our internal page for this feature also turning off the advisor should remove a query from this section also. There is a note in the design documentw that reads.

"Do not include queries that have their Advisor Analysis setting disabled by the user.  (The SQL hash will be in CON_PROBLEM_SILENCE_X)"

jaminsql · Answer

jsql​

DPA does have some system options that might help but, I am thinking we might still not have anything that is granular enough for your use case here. There are items that allow you to filter out by the database and also by the database and an operator. It would be nice if we also had one for table.

These would be added to the system.properties file typically found in [DPA home]/iwc/tomcat/ignite_config/idc/system.properties

DPA would need restarted after this change. The issue I see here is that the closest we could get to filter your items is perhaps a filter on database and operation.

PropertyDescriptioncom.confio.idc.index.analysis.oracle.exclude.schemasDefines a list of Schema names that should be excluded from being considered an "Inefficient Step" for Oracle. This is a repository wide setting. Wildcards are supported and text is not case sensitive.com.confio.idc.index.analysis.oracle.exclude.schemas.[dbId]Defines a list of Schema names that should be excluded from being considered an "Inefficient Step" for Oracle. This is a database specific setting. Wildcards are supported and text is not case sensitive.com.confio.idc.index.analysis.oracle.operationsDefines the Operations to consider Inefficient Steps for Oracle. Both the Operations and Options must match (ignoring case) to be considered an Inefficient Step. This is a repository wide setting. Operations are a comma separated list.com.confio.idc.index.analysis.oracle.operations=Index, Table Access (exact matches ignoring case)com.confio.idc.index.analysis.oracle.operations=%Scan (anything that ends in Scan ignoring case)com.confio.idc.index.analysis.oracle.operations=%Scan% (anything that contains Scan ignoring case)com.confio.idc.index.analysis.oracle.operations.[dbId]Defines the Operations to consider Inefficient Steps for Oracle. Both the Operations and Options must match (ignoring case) to be considered an Inefficient Step. This is for the specified database only. Operations are a comma separated list.com.confio.idc.index.analysis.oracle.operations.1=Index, Table Access (exact matches ignoring)com.confio.idc.index.analysis.oracle.operations.2=%Scan% (anything that contains Scan ignoring case)com.confio.idc.index.analysis.oracle.optionsDefines the Options to consider Inefficient Steps for Oracle. Both the Operations and Options must match (ignoring case) to be considered an Inefficient Step. This is a repository wide setting. Options are a comma separated list.com.confio.idc.index.analysis.oracle.options=Skip Scan, Cluster (exact matches ignoring case)com.confio.idc.index.analysis.oracle.options=%Full (anything that ends in Full ignoring case)com.confio.idc.index.analysis.oracle.options=%Full% (anything that contains Full ignoring case)com.confio.idc.index.analysis.oracle.options.[dbId]Defines the Options to consider Inefficient Steps for Oracle. Both the Operations and Options must match (ignoring case) to be considered an Inefficient Step. This is for the specified database only. Options are a comma separated list.com.confio.idc.index.analysis.oracle.operations.1=Skip Scan, Cluster (exact matches ignoring case)com.confio.idc.index.analysis.oracle.operations.2=%Full% (anything that contains Full ignoring case)com.confio.idc.index.analysis.sqlserver.exclude.databasesDefines a list of Database names that should be excluded from being considered an "Inefficient Step" for SQL Server. This is a repository wide setting.

Wildcards are supported and text is not case sensitive.com.confio.idc.index.analysis.sqlserver.exclude.databases=master,model,msdb,mssqlsystemresource

com.confio.idc.index.analysis.sqlserver.exclude.databases=master,msdb,%test%

com.confio.idc.index.analysis.sqlserver.exclude.databases.[dbId]Defines a list of Database names that should be excluded from being considered an "Inefficient Step" for SQL Server. This is a database specific setting. Wildcards are supported and text is not case sensitive.com.confio.idc.index.analysis.sqlserver.exclude.schemasDefines a list of Schema names that should be excluded from being considered an "Inefficient Step" for SQL Server. This is a repository wide setting.

Wildcards are supported and text is not case sensitive.com.confio.idc.index.analysis.sqlserver.exclude.schemas=syscom.confio.idc.index.analysis.sqlserver.exclude.schemas=sys,dev

com.confio.idc.index.analysis.sqlserver.exclude.schemas.[dbId]Defines a list of Schema names that should be excluded from being considered an "Inefficient Step" for SQL Server. This is a database specific setting. Wildcards are supported and text is not case sensitive.com.confio.idc.index.analysis.sqlserver.operationsDefines the Logical Operations to consider Inefficient Steps for SQL Server. This is a repository wide setting. Logical Operations are a comma separated list.

Wildcards are supported and text is not case sensitive.com.confio.idc.index.analysis.sqlserver.operations=Table Scan,Clustered Index Scan,Index Scan (exact matches ignoring case)com.confio.idc.index.analysis.sqlserver.operations=%Scan (anything that ends in Scan ignoring case)com.confio.idc.index.analysis.sqlserver.operations=%Scan% (anything that contains Scan ignoring case)

com.confio.idc.index.analysis.sqlserver.operations.[dbId]Defines the Logical Operations to consider Inefficient Steps for SQL Server. This is for the specified database only. Logical Operations are a comma separated list.

Wildcards are supported and text is not case sensitive.com.confio.idc.index.analysis.sqlserver.operations.1=Table Scan,Clustered Index Scan,Index Scan (exact matches ignoring)com.confio.idc.index.analysis.sqlserver.operations.2=%Scan% (anything that contains Scan ignoring case)

com.confio.idc.index.analysis.sqlserver.operations.excludeDefines the Logical Operations to exclude as Inefficient Steps for SQL Server. This is a repository wide setting. Logical Operations are a comma separated list.

Wildcards are supported and text is not case sensitive.Any Inefficient Steps that were allowed from the com.confio.idc.index.analysis.sqlserver.operations

definition can be eliminated with a match in this definition. This allows us to set a generic inclusion filter but also remove specific steps we know we are not interested in.

com.confio.idc.index.analysis.sqlserver.operations.exclude.[dbId]Defines the Logical Operations to exclude as Inefficient Steps for SQL Server. This is for the specified database only. Logical Operations are a comma separated list.

Wildcards are supported and text is not case sensitive.Any Inefficient Steps that were allowed from the com.confio.idc.index.analysis.sqlserver.operations.[dbId]

definition can be eliminated with a match in this definition. This allows us to set a generic inclusion filter but also remove specific steps we know we are not interested in.

com.confio.idc.index.analysis.use.table.row.count.stepsDefines the steps in either Oracle or SQL Server that will use the table row count instead of the step row count for index analysis. This is a repository wide setting.

Values are comma separated and does an exact match toSQL Sever - OperationOracle - Operation + " " + OptionDefault value is the following:"TABLE ACCESS FULL","INDEX FULL SCAN","INDEX FAST FULL SCAN","CLUSTERED INDEX SCAN","TABLE SCAN","INDEX SCAN"

com.confio.idc.index.analysis.use.table.row.count.steps.[dbId]Defines the steps in either Oracle or SQL Server that will use the table row count instead of the step row count for index analysis. This is for the specified database only.

Values are comma separated and does an exact match toSQL Sever - OperationOracle - Operation + " " + OptionDefault value is the following:"TABLE ACCESS FULL","INDEX FULL SCAN","INDEX FAST FULL SCAN","CLUSTERED INDEX SCAN","TABLE SCAN","INDEX SCAN"