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

How to explain query performance using profiling

    XMLWordPrintable

    Details

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

      Query behavior is unexpected

      Show
      Query behavior is unexpected
    • Explanation:
      Hide

      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.

      Show
      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.
    • Solution:
      Hide

      Execute the following steps:

      1) Obtain the SQL text of the query in question
      2) Open a new database connection, typically using EXAplus
      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';
      

      4) Attach the generated CSV file to your support ticket.

      Show
      Execute the following steps: 1) Obtain the SQL text of the query in question 2) Open a new database connection, typically using EXAplus 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'; 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: