Share

How to EXTRACT SQL EXECUTION LOGS for specific User

In day to day life Siebel application developer has to deal with scenarios where few functionality or some configuration is making Siebel application latent. Developer has to extract the SQL queries to analyze the problematic functionality. We are sharing structured steps to analyze and extract the SQL queries which are leading to latency in Siebel application

*Note: If the SQL execution Time for a SQL query is greater than 10 seconds for the SQL statements generated at the time of navigation in CRM application,then it means that there is possibility of Latency.

 

STEPS TO EXTRACT SQL EXECUTION LOGS (Causing Latency) FROM SIEBEL SERVERS FOR A PARTICULAR USER-ID :



 

  •  First of all make sure that, all the specific USERIDS are added in the “List of Users” parameter  for the AOM component.  
  • Now , create a temporary directory to fetch the logs of all the specific userids as configured in the List of User parameter ,in the siebfs mountpoint of the Siebel server.
  • Now,go to the path where the logs of the ‘List of User’  User ID’s are kept. 

Grep ‘SQL Statement Execute Time for SQL ‘ ecommunicationsobjmgr_enu *.log > SQL_EXEC_USERID.log

  •  A SQL log file will be generated in the below path:

            <Siebfs/USERID>.

  •          Now open Winscp and navigate to the above given path.
  •   Copy the SQL_EXEC_USERID log file to local desktop and open a new Excel sheet and copy the Log file details in the excel sheet,and format the log file in the excel sheet ,in such a way that,it  appears of the form : (with columns):

Logfilename           SQLID                    SQL_EXECUTION_TIME



  • Filter  it for SQL statements , having SQL execution time greater than 10 seconds. You will get the respective SQL IDs of the logs having SQL execution time greater than 10 seconds.      
  • Now, search the corresponding logs with the SQLIDs  found above,for the given time limits,for which the Latency was observed. (use : ctrl F- for search ). 
  • The respective SQLIDS for the latent time limits,is the required query of a particular functionality,which is causing the Latency in the Siebel application.
  • The above query causing Latency ,found can be further analyzed, and optimized to improve the performance of the Siebel application.

 

 

6 comments on “How to EXTRACT SQL EXECUTION LOGS for specific User”

  1. Nitin Reply

    Hi,
    I want to select copy/paste some text here to ask question. Why the right click disabled ?

  2. Ashish Kr. Bameta Ashish Kr. Bameta Reply

    Dear Nitin,

    This is for the safety of website content. We will keep the dowload feature for the post where readers needs to copy complex code or script from the post.

  3. Nitin Reply

     
    Hi what is the command to add the userID in "list of users" param of comp because I cannot found this entry in  more than 400 param defined for OM comp ?
     
    are you referring winscp to FTP utitlity ?

  4. sourav mukherjee Reply

    @Nitin–  please search it as %UserList% in the OM component parameters,if u r not getting in the command prompt,  then try to search it in UI level…n to let u kno ,its one of d most important parameters for varous testing n operational activities

  5. abhishek kumar Reply

    Alias Name is UserList and parameter name is "List of Users"
    So if you using parameter name to search it, use quotes.

Leave A Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!