In this section we present some techniques that can be used in order to find the performance bottlenecks and to increase the performance by using indexes when executing queries on blueriq tables.

A standard set of indexes is provided by default in each blueriq table create script, but depending on the nature of the blueriq model and the data that is queried the maintenance of a certain set of indexes could not worth the performance gain.

If the solutions presented here are not working for your we strongly suggest to contact a Database Administrator or to consider improving the hardware.

Generic considerations

  • Do not build indexes unless necessary
    • Indexes that are not used should be removed because their maintenance is expensive in terms of CPU usage and I/O resources demand.
    • Monitoring indexes usage tools functionalities can be used to check if an index is used or not.
    • It is strongly recommended that while monitoring the index usage a representative workload is performed on your system to avoid dropping indexes that are used but not by the specified workload sample.
    • See when is necessary to build indexes section for mode details. 
  • Consider to indexing key that contains data that is frequently queried.
    • For example if you work with numeric values more than with string values it is most probable to perform queries that require indexes on numeric columns, so persisting indexes for string column is useless.
  • Consider indexing columns for which a high percentage of rows have the same value. (For example boolean columns)
  • Avoid using poor selectivity keys or expressions with few distinct value as indexes. (For example the numeric columns in the attribute values table)
  • Avoid indexing columns that are updated frequently because additional processing is required for CREATE, UPDATE and DELETE operations when the indexes are present. 
  • Measure the performance with and without indexes in order to see if the performance gain on adding indexes is worth the performance loss on CREATE, UPDATE and DELETE operations.
  • If keys that represent data types form the model are frequently used together please consider composite indexes.

How to improve indexes

All used Blueriq tables have to be considered when improving the indexes.

Steps by step guide
  1. Log SQL queries performed by hibernate or nhibernate in the runtime logs.

    • In order to obtain the statements needed for the execution plan, the logging configuration has to be changed. Logging settings in Runtime are customizable and can be found here.

    • Change the logging level for "org.hibernate" from INFO to TRACE if using java and for NHibernate.SQL from ERROR to TRACE if using .NET.

    • Please be aware that DEBUG level is verbose.
  2. Make sure you are performing a relevant workload on your system or you are you operate on the system during a relevant period of time for the workload.
  3. Analyze the log and extract the relevant SQL statements.
  4. Analyze the execution plan for the relevant SQL statements in order to see if the plan is based on full optimization or not. If you need assistance interpreting the execution plan, please contact your Database Administrator.
  5. Monitor the index usage for the indexes that seem to be missing from the relevant statement execution plan and see if they are really needed.
  6. Consider adding bitmap indexes for index keys that frequently used.

Execution plan

Oracle

 An execution plan is a sequence of operations that Oracle optimizer performs when executing a query. In order to obtain the execution plan for a certain statement the following two steps have to be performed:

  • Run explain plan command

    EXPLAIN PLAN FOR statement

    Let's consider a simple statement SELECT STATUS FROM CASES;

    The command to be run is EXPLAIN PLAN FOR SELECT STATUS FROM CASES;

  • Display the execution plan

    SET LINESIZE 130
    SET PAGESIZE 0
    SELECT * 
    FROM TABLE(DBMS_XPLAN.DISPLAY);

After running the command from above you should see a similar output with the one below:

SQL Server

Consider the same statement as above. For SQL Server we will present what Microsoft SQL Server Management Studio provides.

Steps to be followed:

  • Open query window
  • Select Include actual execution plan (Ctrl + M) option
  • Write the following statement and execute it
  • Following tab should be available
  • For more details please hover the nodes:

Monitor the index usage

Indexing can both help and hurt performance. Maintaining indexes can slow things down and also require additional store. These are the main reasons why the indexes that are not used should be removed.

Oracle

A way of monitoring indexes in Oracle is presented bellow step by step:

  1. Start monitoring the index usage

    ALTER INDEX index MONITORING USAGE
  2. Workload on the system. Please make sure that the workload is representative for the normal usage of the system.

  3. Stop monitoring the index usage

    ALTER INDEX index NOMONITORING USAGE

  4. Query for the index in the V$OBJECT_USAGE to see if the index has been used.

    SELECT USED FORM V$OBJECT_USAGE WHERE INDEX_NAME = 'index'

    The query result will be either yes or no.

SQL Server

SQL Server's Dynamic Management Views are the key in this situation. Steps are presented below:

  1. Workload on the system. Please make sure that the workload is representative for the normal usage of the system.

  2. Run the following query that will expose how many times the index was used for user queries:

    SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       USER_SEEKS, 
       USER_SCANS, 
       USER_LOOKUPS, 
       USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S 
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
       AND S.database_id = DB_ID(),

     

  3. In order to find indexes that are not used, you need to check the values that have no seeks, scans or lookups but they have updates. 

Bitmap indexes

Bitmap indexes are oracle specific indexes that can be used on the columns that have low cardinality, meaning that the column has relatively few unique values. For example in the CASES table, the column STATUS can have only few values. Lets consider OPEN, LOCKED and CLOSED for this example.

In order to create a bitmap index in CASES table on the column STATUS the following query can be executed:

CREATE BITMAP INDEX index_name ON CASES (STATUS);

Please consider a bitmap index for any index that with less than 100 distinct values. Also, if your table has one million rows, a column with 10,000 distinct values is a candidate for a bitmap index. 

The following columns can represent good candidates considering the above condition:

  • In the case table
    • appication id
    • status
    • locked by
  • In the tasks table
    • status
    • type
  • In attfibutes value table
    • value type
    • boolean value
    • entity value
  • In the custom fields table
    • custom fields name
  • in the aggregate table
    • type
    • latest

SQL Server does not support bitmap indexes, but it has something called bitmap filtering. Contact your Database Administrator for more information about this topic.

  • No labels

Didn't find the answer you were looking for? Try the advanced search!