ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 国内数据库 > 【YashanDB知识库】崖山数据库Outline功能验证

【YashanDB知识库】崖山数据库Outline功能验证

原创 国内数据库 作者：崖山数据库系统YashanDB 时间：2024-09-29 10:41:43 0 删除 编辑

本文内容来自YashanDB官网，具体内容请见( https://www.yashandb.com/newsinfo/7488286.html?templateId=1718516)

测试验证环境说明

测试用例说明

1、相同版本下，新增表数据量，使统计信息失效。优化器优先使用outline的计划。

2、相同版本下，绑定参数执行场景下，优化器优先使用outline的计划。

3、单机主备环境，优化器优先使用outline的计划。

4、升级数据库版本后，优化器优先使用outline的计划。

测试过程记录

点击查看代码 
--建两张普通表test_tab1和test_tab2 并且分布插入200w数据。
create table test_tab1(col1 int, col2 int, col3 int);
create table test_tab2(col1 int, col2 int, col3 int);
SQL>
Succeed.
 
SQL>
Succeed.
 
SQL> begin
  for i in 1..2000000 loop
    insert into test_tab1 values(i+1,i+2,i+3);
    insert into test_tab2 values(i+2,i+3,i+4);
  end loop;
  commit;
end;
/
 
PL/SQL Succeed.
 
-- 收集统计信息
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS('GATHER AUTO', 1, 16, 'FOR ALL COLUMNS SIZE AUTO', 'AUTO', TRUE, FALSE);
 
 
PL/SQL Succeed.
--验证版本
SQL> select * from v$version;
 
BANNER VERSION_NUMBER
---------------------------------------------------------------- -----------------
Release 22.2.10.100 x86_64 22.2.10.100
 
1 row fetched.
SQL> explain select distinct t1.col1, t2.col1
from test_tab1 t1 inner join test_tab2 t2 on t1.col2=t2.col2
order by t1.col1 limit 500;
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 2118027925
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW | | | 500| 29542( 0)| |
| 2 | TOP SORT DISTINCT | | | 1999998| 29538( 0)| |
|* 3 | HASH JOIN INNER | | | 1999998| 24350( 0)| |
| 4 | TABLE ACCESS FULL | TEST_TAB1 | SALES | 2000000| 453( 0)| |
| 5 | TABLE ACCESS FULL | TEST_TAB2 | SALES | 2000000| 453( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   3 - Predicate : access("T1"."COL2" = "T2"."COL2")
 
18 rows fetched.
 
SQL> create unique index idx1 on test_tab2(col2, col1);
 
Succeed.
 
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS('GATHER AUTO', 1, 16, 'FOR ALL COLUMNS SIZE AUTO', 'AUTO', TRUE, FALSE);
 
PL/SQL Succeed.
 
-- 创建索引后，从HASH JOIN 转为NEXTED LOOPS INNER JOIN
SQL> explain select distinct t1.col1, t2.col1
   2 from test_tab1 t1 inner join test_tab2 t2 on t1.col2=t2.col2
order by t1.col1 limit 500;
   3
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 2118027925
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW | | | 500| 7785( 0)| |
| 2 | TOP SORT DISTINCT | | | 1999998| 7781( 0)| |
| 3 | NESTED LOOPS INNER | | | 1999998| 2594( 0)| |
| 4 | TABLE ACCESS FULL | TEST_TAB1 | SALES | 2000000| 453( 0)| |
|* 5 | INDEX RANGE SCAN | IDX1 | SALES | 1| 142( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   5 - Predicate : access("T2"."COL2" = "T1"."COL2")
 
18 rows fetched.
 
 
--删除索引后，执行计划从NEXTED LOOPS INNER 转为 HASH JOIN INNER
SQL> drop index IDX1;
 
Succeed.
 
SQL>
explain select distinct t1.col1, t2.col1
from test_tab1 t1 inner join test_tab2 t2 on t1.col2=t2.col2
order by t1.col1 limit 500;
 
SQL>
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 2118027925
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW | | | 500| 29542( 0)| |
| 2 | TOP SORT DISTINCT | | | 1999998| 29538( 0)| |
|* 3 | HASH JOIN INNER | | | 1999998| 24350( 0)| |
| 4 | TABLE ACCESS FULL | TEST_TAB1 | SALES | 2000000| 453( 0)| |
| 5 | TABLE ACCESS FULL | TEST_TAB2 | SALES | 2000000| 453( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   3 - Predicate : access("T1"."COL2" = "T2"."COL2")
 
18 rows fetched.
 
-- 创建索引前，增加outline
-- outline 分别是ol_ab和ol_a
SQL> CREATE OUTLINE ol_ab FOR CATEGORY ctgy_ab ON
select distinct t1.col1, t2.col1
from test_tab1 t1 inner join test_tab2 t2 on t1.col2=t2.col2
order by t1.col1 limit 500;
Succeed.
 
SQL> CREATE OUTLINE ol_a FOR CATEGORY ctgy_ab ON
select /*+ full(t2) */ distinct t1.col1, t 2 3 4 2.col1
from test_tab1 t1 inner join test_tab2 t2 on t1.col2=t2.col2
order by t1.col1 limit 500;
Succeed.
 
--应用outline，使得配置生效
SQL> ALTER SESSION SET USE_STORED_OUTLINES=ctgy_ab;
 
Succeed.
 
-- 整库收集统计信息，让执行计划重新生成
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS('GATHER AUTO', 1, 16, 'FOR ALL COLUMNS SIZE AUTO', 'AUTO', TRUE, FALSE);
 
PL/SQL Succeed.
--创建索引
-- 经前面测试得知，创建索引后，此版本数据库会选择NEXTED LOOP JOIN
SQL> create unique index idx1 on test_tab2(col2, col1);
 
Succeed.
 
-- 可以看到outline已生效，依然走HASH JOIN INNER
SQL> explain select distinct t1.col1, t2.col1
from test_tab1 t1 inner join test_tab2 t2 on t1.col2=t2.col2
order by t1.col1 limit 500;
 
SQL>
 
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 2118027925
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW | | | 500| 29542( 0)| |
| 2 | TOP SORT DISTINCT | | | 1999998| 29538( 0)| |
|* 3 | HASH JOIN INNER | | | 1999998| 24350( 0)| |
| 4 | TABLE ACCESS FULL | TEST_TAB1 | SALES | 2000000| 453( 0)| |
| 5 | TABLE ACCESS FULL | TEST_TAB2 | SALES | 2000000| 453( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   3 - Predicate : access("T1"."COL2" = "T2"."COL2")
                                                                
Outline Information :
---------------------------------------------------
                                                                
   - outline OL_AB used for this statement
 
23 rows fetched.
 
 
 SQL> SELECT join_pos, hint
FROM USER_OUTLINE_HINTS
WHERE name = 'OL_AB'; 2 3
 
JOIN_POS HINT
-------- ----------------------------------------------------------------
       0 LEADING(T1 T2)
       0 USE_HASH(T2)
       1 FULL(T1)
       2 FULL(T2)
 
4 rows fetched.
持续新增200w数据，使得两张表统计信息失效
点击查看代码 
begin
  for i in 2000003 ..4000003 loop
    insert into test_tab1 values(i+1,i+2,i+3);
    insert into test_tab2 values(i+2,i+3,i+4);
  end loop;
  commit;
end;
/
此时统计信息已失效
点击查看代码 
SQL> select * from dba_tab_statistics where STALE_STATS!='N';
 
OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS LOCKED_STATS STALE_STATS SCOPE
---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- --------------------- ----------------- --------------------- ------------- --------------------- --------------------- --------------------- ------------ --------------------- ------------ --------------------- -------------------------------- ------------ ---------- ------------ ----------- ---------
SALES TEST_TAB1 TABLE 2000000 6007 36 735 0 20 2000000 2024-08-07 Y N N Y SHARED
SALES TEST_TAB2 TABLE 2000000 6039 68 774 0 20 2000000 2024-08-07 Y N N Y SHARED
 
2 rows fetched.

统计信息失效后，优化器优先使用outline的计划

点击查看代码 
-- outline 没有生效时，业务查询的计划
SQL> explain select distinct t1.col1, t2.col1
from test_tab1 t1 inner join test_tab2 t2 on t1.col2=t2.col2
order by t1.col1 limit 500;
   2 3
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 2118027925
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW | | | 500| 943( 0)| |
| 2 | TOP SORT DISTINCT | | | 1000| 943( 0)| |
| 3 | NESTED LOOPS INNER | | | 100000| 683( 0)| |
| 4 | TABLE ACCESS FULL | TEST_TAB1 | SALES | 100000| 442( 0)| |
|* 5 | INDEX RANGE SCAN | IDX1 | SALES | 1| 142( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   5 - Predicate : access("T2"."COL2" = "T1"."COL2")
 
18 rows fetched.
 
--outline生效后，优化器优先使用outline的执行计划
SQL> ALTER SESSION SET USE_STORED_OUTLINES=ctgy_ab;
 
Succeed.
 
SQL> explain select distinct t1.col1, t2.col1
from test_tab1 t1 inner join test_tab2 t2 on t1.col2=t2.col2
order by t1.col1 limit 500;
 
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 2118027925
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW | | | 500| 29542( 0)| |
| 2 | TOP SORT DISTINCT | | | 1999998| 29538( 0)| |
|* 3 | HASH JOIN INNER | | | 1999998| 24350( 0)| |
| 4 | TABLE ACCESS FULL | TEST_TAB1 | SALES | 2000000| 453( 0)| |
| 5 | TABLE ACCESS FULL | TEST_TAB2 | SALES | 2000000| 453( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   3 - Predicate : access("T1"."COL2" = "T2"."COL2")
                                                                
Outline Information :
---------------------------------------------------
                                                                
   - outline OL_AB used for this statement
 
23 rows fetched.

绑定参数执行，优化器优先使用outline计划

点击查看代码 
--创建绑定参数的outline
CREATE OUTLINE ol_c FOR CATEGORY ctgy_ab ON
 select /*+ full(t2)*/ t2.col2,t2.col1 from test_tab2 t2 where t2.col2=? and t2.col1=?;
 
Succeed.
 
 
 ALTER system SET USE_STORED_OUTLINES=ctgy_ab;
 
-- outline生效
SQL> ALTER SESSION SET USE_STORED_OUTLINES=ctgy_ab;
 
Succeed.
 
SQL> explain select /*+ full(t2)*/ t2.col2,t2.col1 from test_tab2 t2 where t2.col2=? and t2.col1=?;
 
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 631693285
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
|* 1 | TABLE ACCESS FULL | TEST_TAB2 | SALES | 1| 448( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   1 - Predicate : filter("T2"."COL2" = Param(:0) AND "T2"."COL1" = Param(:1))
                                                                
Hint Information :
---------------------------------------------------
                                                                
full(t2) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
                                                                
Outline Information :
---------------------------------------------------
                                                                
   - outline OL_C used for this statement
 
24 rows fetched.

单机主备架构下，outline功能测试

主备数据库都需要开启outline功能。

点击查看代码 
[yashan@node01 install2210100]$ ./bin/yasboot cluster status -c yashandb -d
 hostid | node_type | nodeid | pid | instance_status | database_status | database_role | listen_address | data_path
--------------------------------------------------------------------------------------------------------------------------------------------------
 host0001 | db | 1-1:1 | 21925 | open | normal | primary | 192.168.33.103:1688 | /home/yashan/yasdb_data/db-1-1
          +-----------+--------+-------+-----------------+-----------------+---------------+---------------------+--------------------------------
          | db | 1-2:2 | 21959 | open | normal | standby | 192.168.33.103:1690 | /home/yashan/yasdb_data/db-1-2
----------+-----------+--------+-------+-----------------+-----------------+---------------+---------------------+--------------------------------
 
 
[yashan@node01 install2210100]$ yasql sys/yasdb_123@192.168.33.103:1688
YashanDB SQL Release 22.2.10.100 x86_64
 
Connected to:
YashanDB Server Release 22.2.10.100 x86_64 - X86 64bit Linux
 
SQL> ALTER SYSTEM SET USE_STORED_OUTLINES=ctgy_ab;
Succeed.
SQL> set autotrace on;
SQL> select distinct t2.col2,t3.col1 from test_tab2 t2 inner join test_tab3 t3 on t2.col2=t3.col1
where t2.col2 =3 limit 10;
 
COL2 COL1
---------------------------------------------------------------- ----------------------------------------------------------------
3 3
 
Execution Plan
----------------------------------------------------------------
SQL hash value: 4249347504
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | | | | | | |
| 1 | WINDOW | | | 1| | 2212( 0)| | | | | |
| 2 | SORT DISTINCT | | | 1| | 2212( 0)| | | | | |
|* 3 | NESTED LOOPS INNER | | | 1| | 2212( 0)| | | | | |
|* 4 | INDEX FAST FULL SCAN | IDX_COL2 | SYS | 1| | 929( 0)| | | | | |
| 5 | INDEX FAST FULL SCAN | IDX_COL3 | SYS | 10000000| | 600( 0)| | | | | |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   3 - Predicate : filter("T2"."COL2" = "T3"."COL1")
   4 - Predicate : filter(CAST("T2"."COL2" AS NUMBER) = 3)
                                                                
Outline Information :
---------------------------------------------------
                                                                
   - outline OL_AF used for this statement
 
Statistics
----------------------------------------------------------------------------------------------------
 
25 rows fetched.
 
SQL>
Disconnected from:
YashanDB Server Release 22.2.10.100 x86_64 - X86 64bit Linux
 
 
--备库上测试outline功能
[yashan@node01 install2210100]$ yasql sys/yasdb_123@192.168.33.103:1690
YashanDB SQL Release 22.2.10.100 x86_64
 
Connected to:
YashanDB Server Release 22.2.10.100 x86_64 - X86 64bit Linux
 
SQL> set autotrace on;
SQL> select distinct t2.col2,t3.col1 from test_tab2 t2 inner join test_tab3 t3 on t2.col2=t3.col1
where t2.col2 =3 limit 10;
COL2 COL1
---------------------------------------------------------------- ----------------------------------------------------------------
3 3
 
Execution Plan
----------------------------------------------------------------
SQL hash value: 4249347504
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | | | | | | |
| 1 | WINDOW | | | 1| | 1071( 0)| | | | | |
| 2 | SORT DISTINCT | | | 1| | 1071( 0)| | | | | |
| 3 | NESTED LOOPS INNER | | | 1| | 1071( 0)| | | | | |
|* 4 | INDEX FAST FULL SCAN | IDX_COL2 | SYS | 1| | 929( 0)| | | | | |
|* 5 | INDEX RANGE SCAN | IDX_COL3 | SYS | 1| | 142( 0)| | | | | |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   4 - Predicate : filter(CAST("T2"."COL2" AS NUMBER) = 3)
   5 - Predicate : access("T3"."COL1" = "T2"."COL2")
 
Statistics
----------------------------------------------------------------------------------------------------
20 rows fetched.
 
--备库需要单独开启outline
SQL> ALTER SYSTEM SET USE_STORED_OUTLINES=ctgy_ab;
 
 
Succeed.
 
SQL> select distinct t2.col2,t3.col1 from test_tab2 t2 inner join test_tab3 t3 on t2.col2=t3.col1
where t2.col2 =3 limit 10;
 
COL2 COL1
---------------------------------------------------------------- ----------------------------------------------------------------
3 3
 
Execution Plan
----------------------------------------------------------------
SQL hash value: 4249347504
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | | | | | | |
| 1 | WINDOW | | | 1| | 2212( 0)| | | | | |
| 2 | SORT DISTINCT | | | 1| | 2212( 0)| | | | | |
|* 3 | NESTED LOOPS INNER | | | 1| | 2212( 0)| | | | | |
|* 4 | INDEX FAST FULL SCAN | IDX_COL2 | SYS | 1| | 929( 0)| | | | | |
| 5 | INDEX FAST FULL SCAN | IDX_COL3 | SYS | 10000000| | 600( 0)| | | | | |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   3 - Predicate : filter("T2"."COL2" = "T3"."COL1")
   4 - Predicate : filter(CAST("T2"."COL2" AS NUMBER) = 3)
                                                                
Outline Information :
---------------------------------------------------
                                                                
   - outline OL_AF used for this statement
Statistics
----------------------------------------------------------------------------------------------------
 
25 rows fetched.

升级数据库版本后，优化器优先使用outline为准

22.2.10.100 版本执行计划和创建outline al_af

点击查看代码 
YashanDB Server Release 22.2.10.100 x86_64 - X86 64bit Linux
 
 
create table test_tab2(col1 varchar2(100),col2 varchar2(100),col3 varchar2(100));
 
create table test_tab3(col1 varchar2(100),col2 varchar2(100),col3 varchar2(100));
SQL>
Succeed.
 
 
Succeed.
 
SQL>
SQL>
begin
  for i in 1..10000000 loop
    insert into test_tab2 values(i+1,i+2,i+3);
    insert into test_tab3 values(i+2,i+3,i+4);
  end loop;
  commit;
end;
/
SQL>
 
PL/SQL Succeed.
 
 
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS('GATHER AUTO', 1, 16, 'FOR ALL COLUMNS SIZE AUTO', 'AUTO', TRUE, FALSE);
 
  
 
PL/SQL Succeed.
 
create index idx_col2 on test_tab2(col2);
create index idx_col3 on test_tab3(col1);
SQL>
Succeed.
SQL>
 
Succeed.
 
 
SQL> explain select distinct t2.col2,t3.col1 from test_tab2 t2 inner join test_tab3 t3 on t2.col2=t3.col1
where t2.col2 =3 limit 10;
 
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 4249347504
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW | | | 1| 1071( 0)| |
| 2 | SORT DISTINCT | | | 1| 1071( 0)| |
| 3 | NESTED LOOPS INNER | | | 1| 1071( 0)| |
|* 4 | INDEX FAST FULL SCAN | IDX_COL2 | SYS | 1| 929( 0)| |
|* 5 | INDEX RANGE SCAN | IDX_COL3 | SYS | 1| 142( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   4 - Predicate : filter(CAST("T2"."COL2" AS NUMBER) = 3)
   5 - Predicate : access("T3"."COL1" = "T2"."COL2")
 
19 rows fetched.
 
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS('GATHER AUTO', 1, 16, 'FOR ALL COLUMNS SIZE AUTO', 'AUTO', TRUE, FALSE);
 
 
PL/SQL Succeed.
 
SQL> explain select distinct t2.col2,t3.col1 from test_tab2 t2 inner join test_tab3 t3 on t2.col2=t3.col1
where t2.col2 =3 limit 10;
 
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 4249347504
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW | | | 1| 1071( 0)| |
| 2 | SORT DISTINCT | | | 1| 1071( 0)| |
| 3 | NESTED LOOPS INNER | | | 1| 1071( 0)| |
|* 4 | INDEX FAST FULL SCAN | IDX_COL2 | SYS | 1| 929( 0)| |
|* 5 | INDEX RANGE SCAN | IDX_COL3 | SYS | 1| 142( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   4 - Predicate : filter(CAST("T2"."COL2" AS NUMBER) = 3)
   5 - Predicate : access("T3"."COL1" = "T2"."COL2")
 
19 rows fetched.
 
SQL> CREATE OUTLINE ol_af FOR CATEGORY ctgy_ab ON
  select distinct t2.col2,t3.col1 from test_tab2 t2 inner join test_tab3 t3 on t2.col2=t3.col1
where t2.col2 =3 limit 10;
 
Succeed.
 
SQL>
ALTER SESSION SET USE_STORED_OUTLINES=ctgy_ab;
SQL>
Succeed.
 
SQL> explain select distinct t2.col2,t3.col1 from test_tab2 t2 inner join test_tab3 t3 on t2.col2=t3.col1
where t2.col2 =3 limit 10;
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 4249347504
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW | | | 1| 2212( 0)| |
| 2 | SORT DISTINCT | | | 1| 2212( 0)| |
|* 3 | NESTED LOOPS INNER | | | 1| 2212( 0)| |
|* 4 | INDEX FAST FULL SCAN | IDX_COL2 | SYS | 1| 929( 0)| |
| 5 | INDEX FAST FULL SCAN | IDX_COL3 | SYS | 10000000| 600( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   3 - Predicate : filter("T2"."COL2" = "T3"."COL1")
   4 - Predicate : filter(CAST("T2"."COL2" AS NUMBER) = 3)
                                                                
Outline Information :
---------------------------------------------------
                                                                
   - outline OL_AF used for this statement
 
24 rows fetched.
从22.2.10.100升级到23.2.3.100，同时开启outline，优化器优先使用outline存储执行计划
点击查看代码 
SQL> select * from v$version;
 
BANNER VERSION_NUMBER
---------------------------------------------------------------- -----------------
Enterprise Edition Release 23.2.3.100 x86_64 23.2.3.100
 
YashanDB Server Enterprise Edition Release 23.2.3.100 x86_64 - X86 64bit Linux
 
SQL> explain
select distinct t2.col2,t3.col1 from test_tab2 t2 inner join test_tab3 t3 on t2.col2=t3.col1
where t2.col2 =3 limit 10;
 
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 4249347504
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW | | | 1| 29( 0)| |
| 2 | SORT DISTINCT | | | 1| 29( 0)| |
| 3 | NESTED INDEX LOOPS INNER | | | 1| 29( 0)| |
|* 4 | INDEX FAST FULL SCAN | IDX_COL2 | SYS | 1| 28( 0)| |
|* 5 | INDEX RANGE SCAN | IDX_COL3 | SYS | 1| 1( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   1 - Limit Expression: (LIMIT: 10)
   2 - Distinct Expression: ("T2"."COL2")
   4 - Predicate : filter("T2"."COL2" = 3)
   5 - Predicate : access("T3"."COL1" = "T2"."COL2")
 
21 rows fetched.
 
SQL>
exec DBMS_STATS.GATHER_DATABASE_STATS('GATHER AUTO', 1, 16, 'FOR ALL COLUMNS SIZE AUTO', 'AUTO', TRUE, FALSE);
 
SQL>
PL/SQL Succeed.
 
SQL>
ALTER SESSION SET USE_STORED_OUTLINES=ctgy_ab;
 
Succeed.
 
SQL> explain select distinct t2.col2,t3.col1 from test_tab2 t2 inner join test_tab3 t3 on t2.col2=t3.col1
where t2.col2 =3 limit 10;
 
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 4249347504
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW | | | 1| 767( 0)| |
| 2 | SORT DISTINCT | | | 1| 767( 0)| |
|* 3 | NESTED LOOPS INNER | | | 1| 767( 0)| |
|* 4 | INDEX FAST FULL SCAN | IDX_COL2 | SYS | 1| 28( 0)| |
| 5 | INDEX FAST FULL SCAN | IDX_COL3 | SYS | 10000000| 28( 0)| |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   1 - Limit Expression: (LIMIT: 10)
   2 - Distinct Expression: ("T2"."COL2")
   3 - Predicate : filter("T2"."COL2" = "T3"."COL1")
   4 - Predicate : filter("T2"."COL2" = 3)
                                                                
Outline Information :
---------------------------------------------------
                                                                
   - outline OL_AF used for this statement
 
26 rows fetched.
 
 
SQL> set autotrace on;
SQL> select distinct t2.col2,t3.col1 from test_tab2 t2 inner join test_tab3 t3 on t2.col2=t3.col1
where t2.col2 =3 limit 10;
   2
 
COL2 COL1
---------------------------------------------------------------- ----------------------------------------------------------------
3 3
 
 
 
 
Execution Plan
----------------------------------------------------------------
SQL hash value: 4249347504
Optimizer: ADOPT_C
                                                                
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| Id | Operation type | Name | Owner | E - Rows | A - Rows | Cost(%CPU) | A - Time | Loops | Memory | Disk | Partition info |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
| 0 | SELECT STATEMENT | | | | | | | | | | |
| 1 | WINDOW | | | 1| | 767( 0)| | | | | |
| 2 | SORT DISTINCT | | | 1| | 767( 0)| | | | | |
|* 3 | NESTED LOOPS INNER | | | 1| | 767( 0)| | | | | |
|* 4 | INDEX FAST FULL SCAN | IDX_COL2 | SYS | 1| | 28( 0)| | | | | |
| 5 | INDEX FAST FULL SCAN | IDX_COL3 | SYS | 10000000| | 28( 0)| | | | | |
+----+--------------------------------+----------------------+------------+----------+----------+-------------+----------+----------+----------+----------+--------------------------------+
                                                                
Operation Information (identified by operation id):
---------------------------------------------------
                                                                
   1 - Limit Expression: (LIMIT: 10)
   2 - Distinct Expression: ("T2"."COL2")
   3 - Predicate : filter("T2"."COL2" = "T3"."COL1")
   4 - Predicate : filter("T2"."COL2" = 3)
                                                                
Outline Information :
---------------------------------------------------
                                                                
   - outline OL_AF used for this statement
 
 
 
 
Statistics
----------------------------------------------------------------------------------------------------
 
27 rows fetched.

测试总结



来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70028812/viewspace-3034441/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 【YashanDB知识库】YashanDB-OCI-快速上手
下一篇： 崖山数据库的共享集群机制初探
请登录后发表评论 登录
全部评论
崖山数据库系统YashanDB
崖山数据库系统YashanDB是深圳计算科学研究院完全自主研发设计的新型数据库系统，融入原创理论，支持单机/主备、共享集群、分布式等多种部署方式，覆盖OLTP/HTAP/OLAP交易和分析混合负载场景，为客户提供一站式的企业级融合数据管理解决方案。

注册时间：2023-04-11

  • 博文量
    722
  • 访问量
    639778

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593