Performance Tuning Tips using Oracle Indexes

1)   Consider indexing keys that are used frequently in WHERE clauses and join tables and with high selectivity. The selectivity of an index is the percentage of rows in a table with the same value for the indexed key. 

   

     a.   Good Selectivity

A table having 1000 records and one of its indexed column has 800 distinct values, then the selectivity of this index is 800 / 1000 = 0.8

 

     b.   Bad Selectivity

lf an index on a table of 1000 records had only 5 distinct values, then the index's selectivity is 5 / 1000 = 0.005 and in this case a query which uses the limitation of such an index will return 1000 / 5 = 200 records for each distinct value. Full table scan is better than an indexed scan in this scenario

2)   Oracle database does not create an index for foreign key constraint automatically. If foreign key column often used in join conditions then create an index on them to enhance the join process

3)   Consider indexing foreign keys for referential integrity constraints in cases where large number of concurrent inserts updates and delete statements access the parent and client tables. Indexing foreign key columns helps avoid full table scans when searching for the matching rows in the child table when DML is performed on parent table. Without an index in the child table a table level lock may occur

4)   When choosing to index a key, consider whether the performance gain for queries is worth the performance loss of inserts, updates and deletes and use of space required storing the index.

5)   Deleting a row from a table in the oracle database results in the deletion of the index entry.  Updates to the key columns result in a logical deletion and insertion of index.

6)   It is recommended that after periods of high DML activity, you verify index statistics and reorganize indexes
Select used, monitoring,
From v$object_usage where index_name =name of the index

File Transfer Errors during initialization in LoadRunner Controller

File Transfer Errors are very common during initialization of load test with LoadRunner. It can be because of following reasons.

1. There may be situations where some of the runtime files of the script are not included by the controller while transferring files to different load generators (may be a bug in controller). It may not transfer all the files required for the script to run successfully. Under those situations if the scripts give compilation errors in different load generators other than local host then manually add the missing files using Add button shown in the snapshot below.

Controller Group Information

  
 

2. If the Length of the script name is too large then LoadRunner will throw error messages like below

Error: Mercury File Transfer error: Cannot create file C:\Documents and Settings\autodesk-admin\Local Settings\Temp\brr_mYv.195\netdir\C\Performance_Test\QAAutomation\Performance\LR\Stage\700ExodusSFDC\CallCenter\CC_SFDC_CreateGSSRequest_fromContactScreen_Attachment\CC_SFDC_CreateGSSRequest_fromContactScreen_Attachment.prm. Reason : Check the file path for illegal characters or length.

To double check if the length is the issue, go the Load Generator and try to add the file in the specified path. You may get the error as below

Error Copying File