6 Replies Latest reply on Mar 19, 2019 4:08 PM by melanie.boyd

    How to ignore table tuning advisors?

    jsql

      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!

        • Re: How to ignore table tuning advisors?
          fabian.s

          Hi jsql

           

          I'm not sure if you can exclude tables but IF it is a handful of SQL statements you can exclude the statements from the tuning advisor.

           

           

          EDIT: Here we go -> Turn off DPA table tuning advice for a table - SolarWinds Worldwide, LLC. Help and Support

           

          regards

            • Re: How to ignore table tuning advisors?
              jsql

              Yes, DPA needs that for Table tuning advisors, It's only available for query advisors.

                • Re: How to ignore table tuning advisors?
                  jsql

                  Its any query that hits this one table. Not just a single query.  It has no unique key and has a clustered index on it, causing index scans for any query that hits it.

                    • Re: How to ignore table tuning advisors?
                      jaminsql

                      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.

                       

                       

                      PropertyDescription
                      com.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.databases

                      Defines 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.schemas

                      Defines 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.operations

                      Defines 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.exclude

                      Defines 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.steps

                      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 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"

                      • Re: How to ignore table tuning advisors?
                        jaminsql

                        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)"

                        • Re: How to ignore table tuning advisors?
                          melanie.boyd

                          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