Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-75

How to explain query performance using profiling

    Details

    • Type: Explanation
    • Status: Published
    • Affects Version/s: EXASolution 4.2.0, EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.x
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Solution:
      Hide

      Background

      Query behavior is unexpected

      Sometimes EXASolution exhibits unexpected behavior when executing queries. Analyzing "this is slow" in support is impossible without additional information. The single foremost information we need is profiling data for the query in question, if possible also profiling data of a similar query that gives better results.

      How to get a profiling data for Exasol support?

      Execute the following steps:

      Step 1

      Obtain the SQL text of the query in question

      Step 2

      Open a new database connection, typically using EXAplus

      Step 3

      Execute the following:

        set autocommit on;
        alter session set profile='on';
        <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';
      

      Step 4

      Attach the generated CSV file to your support ticket.

      Show
      Background Query behavior is unexpected Sometimes EXASolution exhibits unexpected behavior when executing queries. Analyzing "this is slow" in support is impossible without additional information. The single foremost information we need is profiling data for the query in question, if possible also profiling data of a similar query that gives better results. How to get a profiling data for Exasol support? Execute the following steps: Step 1 Obtain the SQL text of the query in question Step 2 Open a new database connection, typically using EXAplus Step 3 Execute the following: set autocommit on; alter session set profile='on'; <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'; Step 4 Attach the generated CSV file to your support ticket.
    • Category 1:
      Database Administration - Profiling

      Attachments

        Issue Links

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: