Customer centre search taking long time while searching from account number

Problem Description : 
We observed that customer centre is taking long  time to search when we put account number .
Usually it takes 4-5 minutes to give the search results.

Solution offered :
We tried to capture sql being fired from OOTB search of customer centre  :

Sql text : select  distinct  account_no, poid_DB, poid_ID0, poid_TYPE, poid_REV, brand_obj_DB, brand_obj_ID0, brand_obj_TYPE, brand_obj_REV
from account_t   where lower( account_t.account_no ) = :1



  •  We tried making some changes in query by removing lower function on account_no field and cost reduced enormously.Now the search if producing output in less 5-6 seconds.
  • The best option here is to create function based index on the column account_id so that it can perform Index scan. 


Comments

Post a Comment

Popular posts from this blog

cm core dump analysis

Go to the location of core file , ideally it should be there in $PIN_HOME/sys/cm Then , execute below command :   gdb cm <core file name > It will execute debugger for the core file . Now , put below command : Thread apply all bt T he output of above command will dump the stack frames for all the threads in a process You can select thread a according to your issue. use below command to select a thread. Thread 1 This will switch to thread 1 after switchover the stack for the current thread can be dummped with bt command . bt Once the back trace is dumped we can inspect each frame, in this case frame 0 looks interesting to us, by using below command : frame 0 frame <number>" command is use to dump the particular frame of stack trace. The command "list +" will show the source for the stack dump . list+
Read more

NO_RESPONSE file handling during rating in ECE and OCOMC

Implementing NO RESPONSE file handling for the ECE Cartridge Pack and Offline mediation controller (OCOMC) PROBLEM STATEMENT :  The ECE DC receives the CDR input stream, which includes the ECE response records from Oracle Communications Billing and Revenue Management Elastic Charging Engine and checks for CDR errors. The records for which no response is received from ECE, they are sent to NO_RESPONSE Directory of that particular ECE DC node. If the number of files increases in this no_response directory, then it is a critical situation and this needs to be handled properly. Such type of Revenue Leakage of the CDRs need to be appropriately routed to avoid causing losses to the Bahrain operator. BACKGROUND STUDY : Step 1 : Network Accounting Record (NAR) Collection Cartridge (CC). The NAR CC reads the no response CDR input stream from the No response directory. The output is made available for the No Response DC ASCII .   Step 2 : NO RESPONSE OCECE ASCII Distrib...
Read more

Script to create snapshot in Oracle ECE

In Oracle ECE patch set 3 , we need to take pricing snapshot before restart so that it can be recovered with same pricing data again. If snapshot is not taken then you will end up loosing all your pricing data in ECE cache. Then you have to go for PDC republish which may take more than 48 hrs (depending upon your price plan structure). To avoid this situation I would recommend you to schedule snapshot creation in crontab so that always you have backup . You can put a simple login to delete snapshot older than 30 days as per your business policy. Please find below steps to create pricing snapshot in oracle ECE using script . Create groovy with below lines of code . import java.lang.management.* import javax.management.ObjectName import javax.management.remote.JMXConnectorFactory as JmxFactory import javax.management.remote.JMXServiceURL as JmxUrl import groovy.swing.SwingBuilder import javax.swing.WindowConstants as WC import java.time.LocalDate import java.t...
Read more