Profiling Information

This section explains how to retrieve profiling information from your Exasol system.

In some scenarios you may be asked by Support to provide profiling information for a problematic query. To get this information you must first run a test of the problematic statement with profiling enabled, and then retrieve the log files for that session.

Prerequisites

You must have enough free disk space for the logs. For more information, see EXAsupport.

Procedure

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

  1. Open a new database connection using your preferred SQL client.
  2. Copy the problematic SQL query into the following statement and execute it:
    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';

    commit;

    flush statistics;

    commit;

    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 profile_output.csv to your support ticket.

Step 2: Get the log files for the test session

In this step, you will request the logs for a specific session.

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

The following examples use the c4 and confd_client command-line tools in a Linux terminal. For more information about these tools, see Exasol Deployment Tool (c4) and ConfD.

  1. Connect to EXAClusterOS (COS) on the cluster using c4 connect -t <DEPLOYMENT>[.<NODE>]/cos. For example:

    c4 connect -t 1.11/cos

    If you do not specify a node, c4 will connect to the first active node in the deployment.

    For more information about how to use c4 connect, see How to use c4.

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

    confd_client db_list
    - MY_DATABASE

  3. To collect information about a specific SQL session, use the EXAsupport tool and provide the relevant session ID. Specify the start date (-s) and end date (-t) using the format YYYY-MM-DD.

    In Exasol versions prior to 8.25.0, you can only collect SQL logs for a specific session on the day that the session occurred. Later versions do not have this limitation.

    For example:

    exasupport -s 2022-08-11 -t 2022-08-11 -e MY_DATABASE -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, use CTRL+D or type exit.

  5. Copy the log file to your local computer using the scp command (secure copy). 

    scp -P $COS_PORT -i ~/.ssh/$KEY root@$DATABASE_IP:/exa/tmp/support/$FILENAME $LOCAL_FOLDER
    Parameter Description
    $COS_PORT

    The SSH port for connecting to EXAClusterOS (COS). Default = 20002

    To find the port number in the configuration, use c4 config | grep CCC_PLAY_SSHD_PORT.
    $KEY

    The filename of the SSH private key.

    To find the filename in the configuration, use c4 config | grep CCC_AWS_KEY_PAIR_FILE.
    $DATABASE_IP

    The external IP address of the cluster node that you were connected to when creating the log file.

    To find the IP addresses of all nodes, use c4 ps.

    $FILENAME The name of the log file that was generated by EXAsupport in the previous step.
    $LOCAL_FOLDER Path to a folder on your computer where the log file will be stored.
    Example

    Get the necessary info: 

    c4 config | grep CCC_AWS_KEY_PAIR_FILE
    CCC_AWS_KEY_PAIR_FILE=my_private_key.pem

    c4 config | grep CCC_PLAY_SSHD_PORT
    CCC_PLAY_SSHD_PORT=20002

    c4 ps 
          N  PLAY_ID   NODE  MEDIUM  INSTANCE     EXTERNAL_IP     INTERNAL_IP  STAGE  STATE      UPTIME    TTL
      ┌─  1  3a4a7d8d  10    awscf   c5d.large    203.0.113.10    10.0.0.10    c      running    04:35:15  +∞
      │   1  3a4a7d8d  11    awscf   c5d.2xlarge  203.0.113.11    10.0.0.11    d      running    04:35:16  +∞
      └─  1  3a4a7d8d  12    awscf   c5d.2xlarge  203.0.113.12    10.0.0.12    d      running    04:35:15  +∞

    Copy the file to your local machine:

    scp -P 2002 -i ~/.ssh/my_private_key.pem \
      root@203.0.113.10:/exa/tmp/support/exacluster_debuginfo_2022_08_11-23_27_20.tar.gz ~/my_logs/

    Verification

    To verify that the log was successfully copied, use the ls command in the target folder on your local machine.

    Example
    ls -lahtr ~/mylogs | grep exacluster_debuginfo
    -rwxr-xr-x  1 user group 9.8K Sep 20 17:38 exacluster_debuginfo_2022_08_11-23_27_20.tar.gz