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
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:
|Problem cause||Indicator in DBA_PROFILE_LAST_DAY|
|Weak JOIN clauses||OUT_ROWS much higher than OBJECT_ROWS|
|Weak filter||OUT_ROWS close to OBJECT_ROWS|
|Data was not in memory||HDD_READ > 0|
|Global operations (JOIN/GROUP BY/SORT)||NET > 0|
|Materialization||High values for “tmp_subselect”|
|Index creation||INDEX CREATE (Expression Index)|