Page History

...

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 SQL statements.Check index
4. peformance by consulting 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 realy really needed.
6. Consider adding bitmap indexes for index keys that are relevantfrequently used.
   
   
7. Check the additional notes section.
   
   

Execution plan

Anchor
executionplan executionplan

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:

Image Added

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
  Image Added
• Write the following statement and execute it
• Following tab should be available
  Image Added
• For more details please hover the nodes:
  Image Added

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.

...

1. Start monitoring the index usage

   Panel
   borderStyle none
   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

   Panel
   borderStyle none
   ALTER INDEX index NOMONITORING USAGE

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

   Panel
   borderStyle none
   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. 

Execution plan

Oracle

...

• 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:

Image Removed

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
  Image Removed
• Write the following statement and execute it
• The following tab should be available
  Image Removed
• For more details please hover the nodes:
  Image Removed

...