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

于 2024-09-29 10:32:20 发布
阅读量100 收藏 3
点赞数 4
分类专栏： YashanDB知识库 文章标签： 数据库 崖山数据库 YashanDB yashandb知识库 yashandb
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/cod0410/article/details/142629704
版权

本文内容来自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
<details>
<summary>点击查看代码</summary>


</details>
 

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.

测试总结

YashanDB
关注
专栏目录
OB数据库基础知识(学习记录)
时间墨客
08-05 5902
CDC: change data capture,数据变更捕获，核心思想就是监测并捕获数据库的变动(包括数据表的插入更新，删除，修改等)，将这些变更按发生的顺序完整记录下来，写入到目标数据库或消息中间件中来供其他服务进行订阅及消费。sysbench介绍说明:Sysbench 是一个基于 LualIT可编写脚本的多线程基准测试工具，常用于评估测试各种不同系统参数下的数据库负载情况。特点:测试场景简单、灵活测试各类业务基本性能但无法模拟复杂业务模型性能指标:RT、TPS、QPS。
Oracle数据库基础知识
中原
01-07 9312
Oracle基础知识
【YashanDB知识库】outline固化执行计划
cod0410的博客
06-06 294
【问题原因分析】防止SQL执行计划突变，用outline固化执行计划。【问题描述】防止SQL执行计划突变，用outline固化执行计划。【影响范围】严重降低SQL执行效率。【问题分类】性能优化，功能使用。【关键字】outline。【修复版本】22.2。
使用 Outline 构建知识库
Becomin' Charles
03-20 2617
本文介绍了如何使用 Outline 搭建本地知识库，使用类似 md 的语法来整理自己的知识和文档。
使用 Outline 构建 企业 or 个人 知识库面临的问题
Becomin' Charles
03-26 1575
本文介绍了 Outline 如果用于生产级的 企业知识库 或者个人知识库的各种缺陷和坑。希望大家避坑。
基于Outline构建团队的知识库 （上篇）
wbsu2004的博客
02-28 6113
Outline 是使用 React 和 Node.js 为您的团队构建的快速、协作的知识库。
基于Outline构建团队的知识库（下篇）
wbsu2004的博客
03-02 2692
Outline 是使用 React 和 Node.js 为您的团队构建的快速、协作的知识库。
outline使用
热门推荐
随手写下笔记和感悟
06-17 6万+
outline
基于 Nginx All In One 的 Outline Wiki 部署方法
CoderJiang
08-14 1407
Outline 是一个开源的知识库和团队协作工具🧠，旨在帮助团队共享、组织和协作文档📝。它提供了一个简洁的界面，使用户能够轻松创建、编辑和查看文档。
UGUI的Text描边Outline拓展
linxinfa的专栏
07-30 3783
UGUI自带的Text，用outline做描边效果很不好 Github上有人做了拓展，通过复制顶点的方式，不过这种方式很容易爆顶点数。 Github地址：https://github.com/n-yoda/unity-vertex-effects
Outline:为成长中的团队提供最快的 wiki 和知识库-开源
07-17
用于内部文档、产品规格、支持答案、会议记录、入职等的现代团队知识库。 一个直观的编辑器，支持降价、斜线命令、丰富的嵌入等等。 漂亮的文档，甚至无需尝试。 无需离开团队聊天即可搜索和共享文档。 在层次结构中...
大纲：成长中团队的最快Wiki和知识库。 美观，功能丰富且兼容Markdown
02-02
Slack或Google Developer身份验证应用程序 生产 对于手动的自托管产品安装，建议执行以下步骤： 克隆此仓库并使用yarn install安装依赖项 用yarn build构建源代码 使用.env.sample作为参考，在生产环境中设置所需...
数据库课件总结：Database Chapter Six Outline.docx
05-25
综上所述，《数据库课件总结：Database Chapter Six Outline》提供了关于数据库设计的重要基础知识，包括概念设计、实体-关系模型、属性类型以及E-R图的绘制方法。这些内容对于理解数据库设计的核心原则至关重要。
数据库课件总结：Database Chapter Seven Outline.docx
05-25
在数据库设计中，**功能依赖**(Functional Dependency,简称FD)是一个非常重要的概念，用于描述关系模式中的属性间的依赖关系。 - **平凡依赖**(Trivial Functional Dependency): 当一个功能依赖对于所有的关系实例...
Online DDL (Data Definition Language)
09-23 266
是MySQL中的一个功能，允许在修改表结构（如添加、删除或修改列）的同时，允许对表进行并发读写操作。在传统的DDL操作中，对表结构的修改会导致整个表被锁定，从而阻止其他事务对该表进行读写操作，这可能导致长时间的锁定和性能问题。Online DDL的引入解决了这个问题，它允许在修改表结构的同时，保持对表的并发访问。MySQL从5.6版本开始引入了Online DDL的部分功能，而在8.0版本中对Online DDL进行了进一步的优化。
Postgresql特殊关键字
czjnoe的博客
09-25 278
表字段名称如果出现这些特殊关键字，则需要通过单引号访问字段，如offset、group。PostgreSQL特殊关键字。
基于JavaWeb开发的Java+SpringMvc+vue+element实现驾校管理系统详细设计
央顺科技官方博客
09-24 902
机遇与挑战始终并存。在开放的互联网平台面前，驾校预约管理系统的信息管理面临着巨大的挑战。传统的管理模式局限于简单数据的管理，无法适应不断变化的市场格局。在早期阶段，在将计算机技术和网络技术融入驾校预约管理系统数据管理方法之前，所有管理方式都通过人工操作完成了管理信息的。系统管理也都将通过计算机进行整体智能化操作，对于驾校预约管理系统所牵扯的管理及数据保存都是非常多的，举例像所有详细信息包括，管理员；首页、个人中心、学员管理、驾校教练管理、驾校车辆管理、预约管理、取消预约管理、驾校公告管理、系统管理。
Java Alibaba Druid 数据库连接池
miracle的专栏
09-24 744
在Java开发中，数据库连接池是性能优化的重要一环。而作为一款强大的数据库连接池解决方案，凭借其和对多种数据库的支持，成为了许多企业级应用的首选。本篇文章将介绍Druid的核心特性，并结合不同数据库的实际用法。
JPA+Thymeleaf增删改查
最新发布
y050505的博客
09-25 465
在使用JPA（Java Persistence API）和Thymeleaf作为模板引擎进行Web开发时，实现增删改查（CRUD）操作是一个常见的需求。下面，我将简要介绍如何使用Spring Boot框架结合JPA和Thymeleaf来实现这一功能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值