Profiling Information

For some issues, you might be required to provide profiling information for a problematic query. To get this information, you need to do the following:

  1. Run a test of the problematic statement with profiling switched on.
  2. Get the log files for this test session.

Step 1: Run a test of the problematic statement with profiling switched on

To run a test of the problematic statement with profiling switched on:

  1. Open a new database connection. For example, using DB Visualizer or any using desired client.
  2. Copy the problematic SQL query into the following statement, and execute it:
    set autocommit on;
      alter session set profile='on';
      <insert your query here>;
      alter session set profile='off';
      alter session set NLS_NUMERIC_CHARACTERS='.,';
      alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH:MI:SS.ff3';
     
      flush statistics;
     
      export (
        select *
        from EXA_STATISTICS.EXA_USER_PROFILE_LAST_DAY
        where session_id = current_session
      )
      into LOCAL CSV
      FILE 'profile_output.csv';
  3. Attach the generated CSV file to your support ticket.

Step 2: Get the log files for the test session

In this step, you will be requesting logs for a specific session. This will only work if you run step 1 after 04:00 am on the same day that you are downloading the log files. In other words, you need to perform both step 1 and step 2 on the same day after 04:00 am.

Make sure you have enough free disk space for the log. For more information, see EXAsupport.

  1. To interact with ConfD, connect to EXAClusterOS on the database access node. For example:

    c4 connect -t1/cos

    When you are connected to EXAClusterOS you can run ConfD jobs using confd_client.

  2. To find the name of the database, use the ConfD job db_list. For example:

    confd_client -c db_list
  3. To collect information about a specific SQL process, use the following command. Make sure to specify the start date (-s) and end date (-t). Dates are in the format YYYY-MM-DD. .

    You can only collect SQL logs for a specific session on the same day the session occurred.

    exasupport -s 2022-08-11 -t 2022-08-11 -e DB_NAME -i SESSION_ID

    If the log file was created successfully, you will see the following message containing the file name:

    Successfully stored debug information into file /exa/tmp/support/exacluster_debuginfo_2022_08_11-23_27_20.tar.gz

  4. To disconnect from EXAClusterOS, type exit or CTRL+D.

  5. The database access node is typically n10. The COS port is typically 20002. To find the database access node's IP address and COS port, use the c4 Show Current Deployments (ps) command. For example: 

    c4 ps
  6. Copy the logs to your local computer using a Secure File Copy (scp) command. Use the exact file name and path previously displayed. For example: 
    scp -P $COS_PORT -i ~/.ssh/$PEM_FILE.xml root@$DB_ACCESS_NODE_IP:/exa/tmp/support/$EXACLUSTER_FILENAME.tar.gz /path/to/folder/

    If you don't remember your PEM file name, search for the CCC_AWS_KEY_PAIR_FILE value in your config file using the following command: c4 config | grep CCC_AWS_KEY_PAIR_FILE.

    Verification

    To verify the log was successfully copied, use the List Directory Contents (ls) command on your local machine. For example:

    ls -lahtr | grep exacluster_debuginfo

    returns:

    -rwxrwxrwx 1 nc nc  2.3G Apr 13 16:59 exacluster_debuginfo_2022_08_11-23_27_20.tar.gz