Profiling Information

This article explains how to collect 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 collect the log files for that session using EXAsupport.

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:
    Copy
    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

This procedure uses the c4 and confd_client command-line tools in a Linux terminal. To learn more about these tools, see Exasol Deployment Tool (c4) and ConfD.

  1. Use c4 ps to find out the play ID of the deployment.

    Example:
    Copy 
    ./c4 ps
          N  PLAY_ID   NODE  MEDIUM  INSTANCE     DB_VERSION  EXTERNAL_IP     INTERNAL_IP  STAGE  STATE    UPTIME    TTL
      ┌─  1  c3275f84  11    host    -            2025.1.0    203.0.113.11    10.0.0.11    d      -        03:50:12  +∞
      │   1  c3275f84  12    host    -            2025.1.0    203.0.113.12    10.0.0.12    d      -        03:50:13  +∞
      │   1  c3275f84  13    host    -            2025.1.0    203.0.113.13    10.0.0.13    d      -        03:50:13  +∞
      └─  1  c3275f84  14    host    -            2025.1.0    203.0.113.14    10.0.0.14    d      -        03:50:13  +∞

  2. Connect to the cluster operating system (COS) using c4 connect -i PLAY_ID -s cos.

    Example:
    Copy 
    ./c4 connect -i c3275f84 -s cos

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

  3. To find the database name when you are connected to COS, use the ConfD job db_list.

    Example:
    Copy 
    confd_client db_list
    - MY_DATABASE

  4. To collect logs for the test session, use exasupport -i with the session ID.

    Specify a start date (-s) and end date (-t) using the format YYYY-MM-DD.

    Specify the database name with the option -e.

    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.

    Example:
    Copy 
    exasupport -i SESSION_ID -s 2022-08-11 -t 2022-08-11 -e MY_DATABASE

    If a file was successfully created, EXAsupport returns a success message with the file name.

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

  5. Use Control+D or type exit to disconnect from COS.

  6. Copy the file to your local computer by connecting to COS and using the cat command:

    Copy 
    ./c4 connect -t <DEPLOYMENT>.<NODE>/cos -- "cat /exa/tmp/support/$FILENAME" > $FILENAME
    Example:
    Copy 
    ./c4 connect -t 1.11/cos -- "cat /exa/tmp/support/my_log" > my_log

    Verification

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

    Example:
    Copy 
    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

    To learn more about how to use EXAsupport, see EXAsupport.