本文内容来自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
+----+--------------------------------+----------------------+-------
最低0.47元/天 解锁文章
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
