The result of a query can be cached in Exasol to the effect that repeated identical queries complete in no time. This feature has been introduced in version 5 and is enabled by default.
SQL_EXA> select session_value,system_value from exa_parameters where parameter_name='QUERY_CACHE'; EXA: select session_value,system_value from exa_parameters where parameter... SESSION_VALUE SYSTEM_VALUE -------------------- -------------------- ON ON 1 row in resultset.
The Query Cache can be (de-)activated on the session level as well as on the system level.
SQL_EXA> alter session set query_cache='off';
EXA: alter session set query_cache='off';
Rows affected: 0
SQL_EXA> select object_name,mem_object_size/1024/1024 as mb from exa_user_object_sizes where object_name='T';
EXA: select object_name,mem_object_size/1024/1024 as mb from exa_user_objec...
OBJECT_NAME MB
-------------------- ----------------------------------
T 1537.49641990661621093750
1 row in resultset.
SQL_EXA> select count(*) from t;
EXA: select count(*) from t;
COUNT(*)
---------------------
320000000
1 row in resultset.
SQL_EXA> set timing on;
SQL_EXA> select count(*) from t where numcol2=42;
EXA: select count(*) from t where numcol2=42;
COUNT(*)
---------------------
888896
1 row in resultset.
Timing element: 4
Elapsed: 00:00:03.022
SQL_EXA> select count(*) from t where numcol2=42;
EXA: select count(*) from t where numcol2=42;
COUNT(*)
---------------------
888896
1 row in resultset.
Timing element: 5
Elapsed: 00:00:02.620
SQL_EXA> select count(*) from t where numcol2=42;
EXA: select count(*) from t where numcol2=42;
COUNT(*)
---------------------
888896
1 row in resultset.
Timing element: 6
Elapsed: 00:00:02.724
Without using the Query Cache the repeated query takes roughly 3 seconds.
SQL_EXA> alter session set query_cache='on';
EXA: alter session set query_cache='on';
Rows affected: 0
Timing element: 7
Elapsed: 00:00:00.008
SQL_EXA> select count(*) from t where numcol2=42;
EXA: select count(*) from t where numcol2=42;
COUNT(*)
---------------------
888896
1 row in resultset.
Timing element: 8
Elapsed: 00:00:00.009
Lightning fast! If statement profiling is enabled, QUERY CACHE RESULT shows as PART_NAME in tables like EXA_USER_PROFILE_LAST_DAY.
Also EXECUTION_MODE from EXA_SQL_LAST_DAY shows the usage of the Query Cache:
SQL_EXA> set timing off;
SQL_EXA> select current_statement;
EXA: select current_statement;
CURRENT_STATEMENT
---------------------
35
1 row in resultset.
SQL_EXA> show autocommit;
AUTOCOMMIT = "ON"
SQL_EXA> select command_name,duration,row_count,execution_mode from exa_sql_last_day where stmt_id=33 and session_id=current_session;
EXA: select command_name,duration,row_count,execution_mode from exa_sql_las...
COMMAND_NAME DURATION ROW_COUNT EXECUTION_MODE
---------------------------------------- ----------- --------------------- --------------------
SELECT 0.005 1 CACHED
1 row in resultset.
If DML changes the table, the result in the Query Cache is invalidated automatically:
SQL_EXA> update t set numcol2=1 where rowid in (select rowid from t limit 1); EXA: update t set numcol2=1 where rowid in (select rowid from t limit 1); Rows affected: 1 SQL_EXA> set timing on; SQL_EXA> select count(*) from t where numcol2=42; EXA: select count(*) from t where numcol2=42; COUNT(*) --------------------- 888896 1 row in resultset. Timing element: 10 Elapsed: 00:00:02.870 SQL_EXA> set timing off; SQL_EXA> select current_statement; EXA: select current_statement; CURRENT_STATEMENT --------------------- 51 1 row in resultset. SQL_EXA> select command_name,duration,row_count,execution_mode from exa_sql_last_day where stmt_id=49 and session_id=current_session; EXA: select command_name,duration,row_count,execution_mode from exa_sql_las... COMMAND_NAME DURATION ROW_COUNT EXECUTION_MODE ---------------------------------------- ----------- --------------------- -------------------- 0 rows in resultset.
There’s a 1 Minute interval for syncing the statistic tables. It can be triggered manually, though:
SQL_EXA> flush statistics;
EXA: flush statistics;
Rows affected: 0
SQL_EXA> commit;
EXA: commit;
Rows affected: 0
SQL_EXA> select command_name,duration,row_count,execution_mode from exa_sql_last_day where stmt_id=49 and session_id=current_session;
EXA: select command_name,duration,row_count,execution_mode from exa_sql_las...
COMMAND_NAME DURATION ROW_COUNT EXECUTION_MODE
---------------------------------------- ----------- --------------------- --------------------
SELECT 2.862 1 EXECUTE
1 row in resultset.
Runtime and EXECUTION_MODE EXECUTE confirms that the Query Cache was invalidated by the UPDATE above. Now it’s automatically refreshed:
SQL_EXA> set timing on;
SQL_EXA> select count(*) from t where numcol2=42;
EXA: select count(*) from t where numcol2=42;
COUNT(*)
---------------------
888896
1 row in resultset.
Timing element: 11
Elapsed: 00:00:00.010
Pickleball spielen 