Profiling Information
For some issues 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.
Step 1: Run a test of the problematic statement with profiling enabled
- Open a new database connection using your preferred SQL client.
- 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'; - 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 you have enough free disk space for the log. 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.
-
Connect to EXAClusterOS (COS) using
c4 connect -t <DEPLOYMENT>[.<NODE>]/cos
. For example:In most cases it does not matter on which node you access ConfD. If you do not specify a node, c4 will connect to the first active node in the deployment. The command prompt in COS indicates which node you are connected to:
For more information about how to use
c4 connect
, see How to use c4. -
To find the name of the database, use the ConfD job db_list. For example:
-
To collect information about a specific SQL session, use the EXAsupport command-line tool and provide the relevant session ID. Specify the start date (
-s
) and end date (-t
) using the formatYYYY-MM-DD
.You can only collect SQL logs for a specific session on the day that the session occurred.
For example:
If the log file was created successfully, you will see the following message containing the file name:
- To disconnect from EXAClusterOS, use CTRL+D or type
exit
. -
Copy the log file to your local computer using the
scp
command (secure copy).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