崖山数据库Outline功能验证

首页 ꁇ 知识库 ꁇ 常见问题 ꁇ 崖山数据库Outline功能验证

测试验证环境说明

服务器主机名

CPU核数

内存

数据库版本

node01

2c

8g

22.2.10.100

23.2.3.100

测试用例说明

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);

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);

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

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);

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;

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;

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.

-- 创建索引前，增加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;

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;

--应用outline，使得配置生效

SQL> ALTER SESSION SET USE_STORED_OUTLINES=ctgy_ab;

-- 整库收集统计信息，让执行计划重新生成

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);

-- 可以看到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;

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

-------- ----------------------------------------------------------------

0 LEADING(T1 T2)

4 rows fetched.

持续新增200w数据，使得两张表统计信息失效

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);

此时统计信息已失效

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;

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;

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=?;

ALTER system SET USE_STORED_OUTLINES=ctgy_ab;

-- outline生效

SQL> ALTER SESSION SET USE_STORED_OUTLINES=ctgy_ab;

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

YashanDB Server Release 22.2.10.100 x86_64 - X86 64bit Linux

SQL> ALTER SYSTEM SET USE_STORED_OUTLINES=ctgy_ab;

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;

---------------------------------------------------------------- ----------------------------------------------------------------

----------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------

25 rows fetched.

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

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;

---------------------------------------------------------------- ----------------------------------------------------------------

----------------------------------------------------------------

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")

----------------------------------------------------------------------------------------------------

20 rows fetched.

--备库需要单独开启outline

SQL> ALTER SYSTEM SET USE_STORED_OUTLINES=ctgy_ab;

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;

---------------------------------------------------------------- ----------------------------------------------------------------

----------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------

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));

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);

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> 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;

ALTER SESSION SET USE_STORED_OUTLINES=ctgy_ab;

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

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.

exec DBMS_STATS.GATHER_DATABASE_STATS('GATHER AUTO', 1, 16, 'FOR ALL COLUMNS SIZE AUTO', 'AUTO', TRUE, FALSE);

PL/SQL Succeed.

ALTER SESSION SET USE_STORED_OUTLINES=ctgy_ab;

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;

---------------------------------------------------------------- ----------------------------------------------------------------

----------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------

27 rows fetched.

测试总结

测试用例	测试结果	有无其他疑问
相同版本下，新增表数据量持续新增，使统计信息失效。优化器优先使用outline的计划	outline生效后，优化器优先以outline存储计划为准	full(t2) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS 崖山里面仅仅保持跟oracle输出兼容，实际无特殊语义。
相同版本下，绑定参数执行场景下，优化器优先使用outline的计划
单机主备环境，优化器优先使用outline的计划
升级数据库版本后，优化器优先使用outline存储计划

ꄘ浏览量：0

뀷上一篇：无

뀠下一篇：无

友情链接：深圳计算科学研究院深圳崖山科技有限公司

版权所有© 深圳崖山科技有限公司粤ICP备2022018810号

公众号

加入技术交流群

YashanDB

崖山数据库系统YashanDB是深圳计算科学研究院自主设计研发的新型数据库管理系统，融入原创的有界计算、近似计算、并行可扩展和跨模融合计算理论，可满足金融、政企、能源等关键行业对高性能、高并发及高安全性的要求。

邮箱：info@yashandb.com

地址：广东省深圳市龙华区民宝路红山6979园区25座5-10层

数据库产品

数据库一体机

资源与学习

在线体验YashanDB

400 838 0400