How to Add Index to an Existing Session to Improve ProTrace Query Performances?

Asset ID:	1-71-2224836.1
Update Date:	2017-09-19
Keywords:

Solution Type Technical Instruction Sure

Solution 2224836.1 : How to Add Index to an Existing Session to Improve ProTrace Query Performances?

Applies to:

Oracle Communications Performance Intelligence Center (PIC) Software - Version 9.0.3 and later
Tekelec

Goal

Improve query performances for ProTrace (Troubleshooting) by adding indexes.

Solution

Searching the column name corresponding the the xDR field to index:
1. Connect to IXP data base, display the table corresponding to the session and search in the table the column that correspond to the new field to index.
  On IXP data base the table name is the same as the session name.
  
  [oracle@ixp0001-1a ~]$ sqlplus ixp/<password>
  SQL> desc <SESSION_NAME>;
  Name                                      Null?    Type
  ----------------------------------------- -------- ----------------------------
  IMSI_                                              VARCHAR2(17)
  IMEI_                                              VARCHAR2(17)
  LAC_                                               NUMBER
  BNUMBER_                                           VARCHAR2(25)
  CNUMBER_                                           VARCHAR2(25)
  MSISDN_    VARCHAR2(25)
  ....
2. If you want to add an index on the MSISDN, use the column MSISDN_
Ensure index does not exist already for this session:
1. Use the ListIndexes.sh sql script provided in the cd_oracle_utils path:
  
  [root@ixp0001-1z ~]# cd_oracle_utils
  [root@ixp0001-1z cmd]# ./ListIndexes.sh ixp/ixp@locahost/ixp | grep -i <SESSION_NAME>
  table                 column                               index
  <SESSION_NAME>        STARTDATE_                           I_18761_2
  <SESSION_NAME>        ANUMBER_                             I_18761_3
  <SESSION_NAME>        BNUMBER_                             I_18761_4
  <SESSION_NAME>        IMSI_                                I_18761_5
  <SESSION_NAME>        IMEI_                                I_18761_6
  <SESSION_NAME>        TIMETAG                              I_TIMETAG_18761
Creating the new index:
If the column name (in the example : MSISDN_) doesn't exist in the current indexes, you can add in using the CreateIndex.sh sql script.

[root@ixp0001-1z ~]# cd_oracle_utils
[root@ixp0001-1z cmd]# ./CreateIndex.sh ixp/ixp@locahost/ixp <SESSION_NAME> MSISDN_

Important notes:

Create the index on all the storage servers of a pool.
Execute the 3 steps for each index and each session.
Rebuild index job will need time to index old partitions. Until it is completed, query on old partitions will fail (NSP-0404).

Attachments

This solution has no attachment