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:
- Run a test of the problematic statement with profiling switched on.
- 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:
- Open a new database connection. For example, using DB Visualizer or any using desired 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 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.
-
To interact with ConfD, connect to EXAClusterOS on the database access node. For example:
When you are connected to EXAClusterOS you can run ConfD jobs using
confd_client
. -
To find the name of the database, use the ConfD job db_list. For example:
-
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 formatYYYY-MM-DD
. .You can only collect SQL logs for a specific session on the same day the session occurred.
If the log file was created successfully, you will see the following message containing the file name:
- To disconnect from EXAClusterOS, type exit or CTRL+D.
-
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: - 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:returns: