{Denodo}パフォチュー関連 - HTN20190109の日記

https://community.denodo.com/kb/en/view/document/Best%20Practices%20to%20Maximize%20Performance%20IV%3A%20Detecting%20Bottlenecks%20in%20a%20Query
https://community.denodo.com/docs/html/browse/8.0/jp/vdp/administration/optimizing_queries/optimizing_join_operations/optimizing_join_operations
https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#creating-base-views-from-sql-queries

環境前提:
Denodo Express ※
ソースDB = Oracle 19c
キャッシュDB = PostgreSQL 15

※ 1つのクエリで返される行の最大数 = 10,000

テーブルを2件作成。10000件と10000件。主キーとインデックスを追加し、NLが使用されるクエリを実行

このテーブルをDenodoにBVとして作成。
(キャッシュは有効。インデックスは作成なし)

drop table tab1 purge;
drop table tab2 purge;

create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

create unique index ind11 on tab1(col1);
create index ind12 on tab1(col2);

create unique index ind21 on tab2(col1);
create index ind22 on tab2(col2);

alter table tab1 add constraint cons1 primary key(col1) using index ind11;
alter table tab2 add constraint cons2 primary key(col1) using index ind21;

declare
begin
for i in 1..1000 loop
insert into tab1 values(i,i*2);
commit;
end loop;
end;
/

declare
begin
for i in 1..10000 loop
insert into tab2 values(i,mod(i,10));
commit;
end loop;
end;
/

execute dbms_stats.gather_table_stats('TEST','TAB1');
execute dbms_stats.gather_table_stats('TEST','TAB2');

explain plan for
select t1.col1,t1.col2
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
where t1.col2 = 2
;

select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 12 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 8 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND12 | 1 | | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | IND21 | 1 | 4 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

SELECT * FROM "b_TAB1"
CONTEXT ( 'cache_preload' = 'true', 'cache_invalidate' = 'all_rows', 'cache_wait_for_load' = 'true', 'cache_return_query_results' = 'false')
;

SELECT * FROM "b_TAB2"
CONTEXT ( 'cache_preload' = 'true', 'cache_invalidate' = 'all_rows', 'cache_wait_for_load' = 'true', 'cache_return_query_results' = 'false')
;

SELECT count(*) FROM "b_TAB1" CONTEXT( 'cache' = 'on' );
SELECT count(*) FROM "b_TAB1" CONTEXT( 'cache' = 'off' );

SELECT count(*) FROM "b_TAB2" CONTEXT( 'cache' = 'on' );
SELECT count(*) FROM "b_TAB2" CONTEXT( 'cache' = 'off' );

キャッシュテーブル確認

select distinct cache_table_name from GET_CACHE_COLUMNS('db02','b_TAB1');
select distinct cache_table_name from GET_CACHE_COLUMNS('db02','b_TAB2');

select count(*) from c_b_tab1743724624464671575168086822081334441955353922702945586;

select count(*) from c_b_tab2584306560035110938710429844052172614706833901837335373;

その他、下記管理テーブルがキャッシュDBに作成される
select * from vdb_cache_names;
select * from vdb_cache_querypattern;
select * from vdb_cache_sequences;
select * from vdb_cache_viewname;

-- ①実行エンジンがデータベースにプッシュダウンするSQLの確認

select t1."COL1",t1."COL2"
from "b_TAB1" t1 inner join "b_TAB2" t2
on t1."COL1" = t2."COL1"
where t1."COL2" = 2
;

SELECT delegated_query FROM GET_DELEGATED_SQLSENTENCE()
WHERE vdp_query = '
select t1."COL1",t1."COL2"
from "b_TAB1" t1 inner join "b_TAB2" t2
on t1."COL1" = t2."COL1"
where t1."COL2" = 2
CONTEXT(''cache''=''off'')
';

SELECT t0.COL1, t0.COL2 FROM TEST.TAB1 t0 JOIN TEST.TAB2 t1 ON ((t0.COL1 = t1.COL1) ) WHERE t0.COL2 = 2

SELECT t0.col1, t0.col2
FROM c_b_tab1743724624464671575168086822081334441955353922702945586 t0
JOIN c_b_tab2584306560035110938710429844052172614706833901837335373 t1
ON ((t0.col1 = t1.col1) )
WHERE t0.col2 = 2
AND t0.rowstatus = 'V'
AND t0.expirationdate = 0
AND t1.rowstatus = 'V'
AND t1.expirationdate = 0

-- ②実行計画確認

Swapping, Memory limit reached and Effective time are some of the significant properties when identifying a bottleneck.

↑ Virtual DataPort Administration Toolで確認必要

DESC QUERYPLAN
select t1."COL1",t1."COL2"
from "b_TAB1" t1 inner join "b_TAB2" t2
on t1."COL1" = t2."COL1"
where t1."COL2" = 2
CONTEXT('cache'='on')
;

EXECUTION PLAN (
name = Execution
state = OK
requestId = 849
sessionId = 93
userName = admin
userAgent = Denodo-Web-Design-Studio
waitingTime = 0
STATIC OPTIMIZATION (
staticOptimized = false
staticOptimizationTime = 13
staticOptimizationStart = Sat Jul 22 18:38:14 021 JST 2023
staticOptimizationEnd = Sat Jul 22 18:38:14 034 JST 2023
)
QUERY BLOCKS (
213455420 (
leafViews = [db02.b_TAB1, db02.b_TAB2]
effectiveTime = 0 ns
comparable = true
)
)
CACHE PLAN (
name = -
database = db02
state = OK
queryBlockIds = [213455420]
fields = [t1."COL1", t1."COL2"]
search conditions =
filter conditions =
orderByFields =
ordered = false
viewType = PROJECT
indexes =
JDBC WRAPPER (
name = -
database = db03
state = OK
searchConditions =
orderByFields =
projectedFields = [t1."COL1", t1."COL2"]
additionalSubPlans = 0
additionalErroneousSubPlans = 0
JDBC ROUTE (
name = -
datasource = ds01
datasource database = db03
state = OK
SQLSentence = SELECT t0.col1, t0.col2 FROM c_b_tab1743724624464671575168086822081334441955353922702945586 t0 JOIN c_b_tab2584306560035110938710429844052172614706833901837335373 t1 ON ((t0.col1 = t1.col1) ) WHERE t0.col2 = ? AND t0.rowstatus = ? AND t0.expirationdate = ? AND t1.rowstatus = ? AND t1.expirationdate = ?
parameters = [2, V, 0, V, 0]
SQLSentence info =
DBUri = jdbc:postgresql://192.168.137.55:5432/test
userName = null
)
)
)
)

DESC QUERYPLAN
select t1."COL1",t1."COL2"
from "b_TAB1" t1 inner join "b_TAB2" t2
on t1."COL1" = t2."COL1"
where t1."COL2" = 2
CONTEXT('cache'='off')
;

EXECUTION PLAN (
name = Execution
state = OK
requestId = 850
sessionId = 93
userName = admin
userAgent = Denodo-Web-Design-Studio
waitingTime = 0
STATIC OPTIMIZATION (
staticOptimized = false
staticOptimizationTime = 15
staticOptimizationStart = Sat Jul 22 18:39:08 867 JST 2023
staticOptimizationEnd = Sat Jul 22 18:39:08 882 JST 2023
)
QUERY BLOCKS (
1327813906 (
leafViews = [db02.b_TAB1, db02.b_TAB2]
effectiveTime = 0 ns
comparable = true
)
)
VIRTUAL PLAN (
name = -
database = db02
state = OK
queryBlockIds = [1327813906]
fields = [t1."COL1", t1."COL2"]
search conditions =
filter conditions =
orderByFields =
ordered = false
viewType = PROJECT
indexes =
JDBC WRAPPER (
name = -
database = db02
state = OK
searchConditions =
orderByFields =
projectedFields = [t1."COL1", t1."COL2"]
additionalSubPlans = 0
additionalErroneousSubPlans = 0
JDBC ROUTE (
name = -
datasource = ds01
datasource database = db02
state = OK
SQLSentence = SELECT t0.COL1, t0.COL2 FROM TEST.TAB1 t0 JOIN TEST.TAB2 t1 ON ((t0.COL1 = t1.COL1) ) WHERE t0.COL2 = ?
parameters = [2]
SQLSentence info =
DBUri = jdbc:oracle:thin:@192.168.137.65:1521/pdb1.example.com
userName = test
)
)
)
)

-- ③実行時間確認

VQLシェルのExecuton Logsから確認

キャシュoff → 32-39 ms
キャシュon → 32-36 ms

-- ④ガベージ影響確認

キャッシュロードクエリを20回実行し、キャッシュonのクエリ実行時間が長くなるか確認

SELECT * FROM "b_TAB1"
CONTEXT ( 'cache_preload' = 'true', 'cache_invalidate' = 'all_rows', 'cache_wait_for_load' = 'true', 'cache_return_query_results' = 'false')
;

SELECT * FROM "b_TAB2"
CONTEXT ( 'cache_preload' = 'true', 'cache_invalidate' = 'all_rows', 'cache_wait_for_load' = 'true', 'cache_return_query_results' = 'false')
;

キャシュoff → 22-27 ms
キャシュon → 44-47 ms

キャッシュonのクエリ実行時間が長くなった

ガベージコレクトを実行し、キャッシュテーブルの件数が減ることおよび、キャッシュonのクエリ実行時間が元に戻ることを確認

select count(*) from c_b_tab1743724624464671575168086822081334441955353922702945586;
select count(*) from c_b_tab2584306560035110938710429844052172614706833901837335373;

call CLEAN_CACHE_DATABASE('db02');

※DB名はキャッシュテーブルのあるDB名ではなく、BVのあるDB名を指定する

キャッシュメンテナンスタスク実行後

キャシュoff → 22-26 ms
キャシュon → 27-31 ms -> ガベージコレクトによりクエリ実行時間は短くなった

-- ⑤主キー、インデックス作成と確認

select * from GET_PRIMARY_KEYS('db02','b_TAB1');
→ 結果1行
select * from GET_VIEW_INDEXES('db02','b_TAB1');
→ 結果0行、ただし、ビューの [Options] の [Indexes] タブにはインデックスが表示されている

select * from GET_PRIMARY_KEYS('db02','b_TAB2');
→ 結果1行
select * from GET_VIEW_INDEXES('db02','b_TAB2');
→ 結果0行、ただし、ビューの [Options] の [Indexes] タブにはインデックスが表示されている

WRAPPER定義に、ソーステーブルの主キーやインデックスと同じものがある。
BV定義に、ソーステーブルの主キーのみある。

The difference between the cache indexes and the indexes defined in the "Indexes" tab is
that the latter ones represent the indexes defined in the source.
The cache module actually creates the "Cache indexes" in the cache’s database, when they are defined in this tab.

When creating a JDBC base view, the Server automatically sets the definition of the primary key of the view
according to the definition of the selected table in the database.

Virtual DataPort does not enforce the primary key definitions so there is no guarantee
that a column marked as primary key indeed has unique values.
The responsibility of uniqueness relies on the data source.

(col2,col1)の複合インデックスをキャッシュテーブルに追加

ALTER TABLE "b_TAB1"
CREATE CACHE INDEX "IND13" ON ("COL2","COL1")
;

キャシュインデックスはビューの [Options] の [Cache] タブに表示される
下記クエリでも確認可能
select * from GET_VIEW_INDEXES('db02','b_TAB1');
→ 結果2行 (複合インデックスでカラム2個あるので、2行となる)

キャッシュDBで確認
\d c_b_tab1743724624464671575168086822081334441955353922702945586

追加したキャッシュインデックスが使用されるか確認

DESC QUERYPLAN
select t1."COL1",t1."COL2"
from "b_TAB1" t1 inner join "b_TAB2" t2
on t1."COL1" = t2."COL1"
where t1."COL1" = 10
and t1."COL2" = 20
CONTEXT('cache'='on')
;

→ クエリはキャッシュDBにpushed downされているので、使用されていない模様

-- ⑥統計情報確認

※ Automatic cost-based optimization: デフォルトoff。グローバルまたはDBレベルで有効化可能
it is very important to make sure the statistics are complete and accurate.
In cases where it is not possible to provide reliable statistics it is better to disable the cost-based optimizer

select * from GET_VIEW_STATISTICS('db02','b_TAB1');
select * from GET_VIEW_STATISTICS('db02','b_TAB2');

select stats_mode from GET_AVAILABLE_STATS_MODES('db02', 'b_TAB1', false) where available = true;
select stats_mode from GET_AVAILABLE_STATS_MODES('db02', 'b_TAB2', false) where available = true;

ATCACHE_THROUGH_VDP_ONLY
SMART_ONLY
ATSOURCE_THROUGH_VDP_ONLY
SMART_THEN_ATSOURCE_THROUGH_VDP

CALL GET_STATS_FOR_FIELDS('ATCACHE_THROUGH_VDP_ONLY'
, 'db02'
, 'b_TAB1'
, {
ROW('COL1',NULL,NULL),
ROW('COL2',NULL,NULL)
}
, true
, true
, true
, true);

CALL GET_STATS_FOR_FIELDS('ATCACHE_THROUGH_VDP_ONLY'
, 'db02'
, 'b_TAB2'
, {
ROW('COL1',NULL,NULL),
ROW('COL2',NULL,NULL)
}
, true
, true
, true
, true);

select * from GET_VIEW_STATISTICS('db02','b_TAB1');
select * from GET_VIEW_STATISTICS('db02','b_TAB2');

DESC QUERYPLAN
select t1."COL1",t1."COL2"
from "b_TAB1" t1 inner join "b_TAB2" t2
on t1."COL1" = t2."COL1"
where t1."COL1" = 10
and t1."COL2" = 20
CONTEXT('cache'='on')
;

-- ⑦ヒント句の使用

→ Creating Base Views from SQL Queriesで使用可能

explain plan for
select /*+ USE_HASH(t2) */ t1.col1,t1.col2
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
where t1.col2 = 2
;

select plan_table_output from table(dbms_xplan.display(format=>'ALL') );

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 9 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 12 | 9 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 8 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND12 | 1 | | 1 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN | IND21 | 10000 | 40000 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

CREATE OR REPLACE WRAPPER JDBC "b_TAB1_with_hint"
FOLDER = '/02bv'
DATASOURCENAME=ds01
SQLSENTENCE='select /*+ USE_HASH(t2) */ t1.col1,t1.col2
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
where t1.col2 = 2

'
OUTPUTSCHEMA (
"COL1" = 'COL1' :'java.math.BigDecimal' (sourcetypedecimals='0', sourcetypesize='38', sourcetypeid='2', sourcetypename='NUMBER') NOT NULL SORTABLE NOT UPDATEABLE,
"COL2" = 'COL2' :'java.math.BigDecimal' (sourcetypedecimals='0', sourcetypesize='38', sourcetypeid='2', sourcetypename='NUMBER') SORTABLE NOT UPDATEABLE
);

CREATE OR REPLACE TABLE "b_TAB1_with_hint" I18N jp (
"COL1":decimal (notnull, sourcetypeid = '3', sourcetypedecimals = '0', sourcetypesize = '38'),
"COL2":decimal (sourcetypeid = '3', sourcetypedecimals = '0', sourcetypesize = '38')
)
FOLDER = '/02bv'
CACHE FULL
BATCHSIZEINCACHE DEFAULT
TIMETOLIVEINCACHE NOEXPIRE
ADD SEARCHMETHOD "b_TAB1_with_hint"(
I18N jp
CONSTRAINTS (
ADD "COL1" NOS ZERO ()
ADD "COL2" NOS ZERO ()
)
OUTPUTLIST ("COL1", "COL2"
)
WRAPPER (jdbc "b_TAB1_with_hint")
);