Profiling

This article describes the profiling feature in Exasol.

Exasol uses a cost-based query optimizer that computes query execution plans based on automatically gathered statistics that inform the optimizer about database objects involved in the query. The optimizer is constantly improved when new database versions are developed and it will usually find an optimal plan.

Nevertheless, there can be reasons for query profiling. Firstly, the optimizer is not perfect. Under rare circumstances, it may take a wrong decision. Secondly, performance may be low for reasons the optimizer cannot influence, for example - data distribution, or slow hardware parts.

In these cases, profiling helps to reveal the root causes of performance problems. Profiling can be enabled on the system layer or on the session layer:

ALTER SYSTEM|SESSION SET PROFILE=’ON’;

This will populate EXA_(DBA/USER)_PROFILE_LAST_DAY

For more information, refer to ALTER SYSTEM and ALTER SESSION statements. For additional information on profiling, refer to the Profiling section.

Example

set autocommit off;
alter session set profile='ON';
select count(*) from sales s join sales_positions sp on 
s.sales_id=sp.sales_id;
alter session set profile='OFF';
select distinct stmt_id,sql_text
from exa_user_profile_last_day
where session_id=current_session
order by stmt_id desc;

select
PART_ID,PART_NAME,PART_INFO,OBJECT_NAME,OBJECT_ROWS,OUT_ROWS,DURATION,
CPU,TEMP_DB_RAM_PEAK,HDD_READ,HDD_WRITE,NET,REMARKS
from exa_user_profile_last_day
where stmt_id=12
and session_id=current_session;

Above profile shows that apparently the two tables were joined for the first time - therefore the INDEX CREATE part.

SALES_POSITIONS is indexed while SALES becomes the root table for the following join. It is a local join because the two tables are distributed on the join column SALES_ID.

Performance Problems Revealed by Profiling

Profiling helps to identify the following root causes of potential performance problems: