ob sql outline为用户级，相同SQL不同用户不共享

原创范计杰 2024-07-31

137

概述

OB中SQL OUTLINE为用户级，不同用户的OUTLINE不共享,OUTLINE是用户私有的

OB ORACLE模式，SCHEMA与用户相同。

测试验证

创建2个用户U1,U2

create user u1 identified by test;
grant connect to u1;

create user u2 identified by test;
grant connect to u2;

每个用户下分区创建一个相同的表，索引，INSERT 10万行数据,发起合并

create table u1.tab1(id number,c varchar2(100));
insert into u1.tab1 select rownum,'test'||rownum from dual connect by rownum<=100000;
create index idx_tab1 on u1.tab1(id);

create table u2.tab1(id number,c varchar2(100));
insert into u2.tab1 select rownum,'test'||rownum from dual connect by rownum<=100000;
create index idx_tab1 on u2.tab1(id);

alter system major freeze;

2个用户分别执行一条相同的SQL（SQL文本相同），查询各自用户下的表，使条件能走上创建的索引

obclient [U1]> explain select * from tab1 where id=10\G
*************************** 1. row ***************************
Query Plan: =============================================
|ID|OPERATOR  |NAME          |EST. ROWS|COST|
---------------------------------------------
|0 |TABLE SCAN|TAB1(IDX_TAB1)|1        |92  |
=============================================

Outputs & filters:
-------------------------------------
  0 - output([TAB1.ID], [TAB1.C]), filter(nil),
      access([TAB1.ID], [TAB1.C]), partitions(p0)

1 row in set (0.003 sec)


conn u1;
select * from tab1 where id=10 /* testoutlinesql */;
conn u2;
select * from tab1 where id=10 /* testoutlinesql */;

obclient [U1]> conn u1;
Connection id:    81606
Current database: U1

obclient [U1]> select * from tab1 where id=10 /* testoutlinesql */;
+------+--------+
| ID   | C      |
+------+--------+
|   10 | test10 |
+------+--------+
1 row in set (0.001 sec)

obclient [U1]> select * from tab1 where id=10 /* testoutlinesql */;
+------+--------+
| ID   | C      |
+------+--------+
|   10 | test10 |
+------+--------+
1 row in set (0.001 sec)

obclient [U1]> conn u2;
Connection id:    64781
Current database: U2

obclient [U2]> select * from tab1 where id=10 /* testoutlinesql */;
+------+--------+
| ID   | C      |
+------+--------+
|   10 | test10 |
+------+--------+
1 row in set (0.020 sec)

obclient [U2]> select * from tab1 where id=10 /* testoutlinesql */;
+------+--------+
| ID   | C      |
+------+--------+
|   10 | test10 |
+------+--------+

obclient [oceanbase]>  select tenant_id,db_id,svr_ip,svr_port,plan_id,sql_id,query_sql from gv$plan_cache_plan_stat where query_sql not like '%plan_cache_plan_stat%' and query_sql like 'select * from tab1 where id=10 /* testoutlinesql */%';
+-----------+------------------+---------------+----------+---------+----------------------------------+-----------------------------------------------------+
| tenant_id | db_id            | svr_ip        | svr_port | plan_id | sql_id                           | query_sql                                           |
+-----------+------------------+---------------+----------+---------+----------------------------------+-----------------------------------------------------+
|      1001 | 1100611139404851 | 192.168.56.36 |     2882 | 1244123 | F572210BDF51A91B9E817F0E51F5C56F | select * from tab1 where id=10 /* testoutlinesql */ |
|      1001 | 1100611139404852 | 192.168.56.36 |     2882 | 1244128 | F572210BDF51A91B9E817F0E51F5C56F | select * from tab1 where id=10 /* testoutlinesql */ |
+-----------+------------------+---------------+----------+---------+----------------------------------+-----------------------------------------------------+
2 rows in set (0.044 sec)

obclient [oceanbase]> select PLAN_DEPTH,PLAN_LINE_ID,OPERATOR,NAME from gv$plan_cache_plan_explain where tenant_id=1001 and ip='192.168.56.36' and PORT=2882 and plan_id=1244123;
+------------+--------------+----------------+----------------+
| PLAN_DEPTH | PLAN_LINE_ID | OPERATOR       | NAME           |
+------------+--------------+----------------+----------------+
|          0 |            0 | PHY_TABLE_SCAN | TAB1(IDX_TAB1) |
+------------+--------------+----------------+----------------+
1 row in set (0.009 sec)

obclient [oceanbase]> select PLAN_DEPTH,PLAN_LINE_ID,OPERATOR,NAME from gv$plan_cache_plan_explain where tenant_id=1001 and ip='192.168.56.36' and PORT=2882 and plan_id=1244128;
+------------+--------------+----------------+----------------+
| PLAN_DEPTH | PLAN_LINE_ID | OPERATOR       | NAME           |
+------------+--------------+----------------+----------------+
|          0 |            0 | PHY_TABLE_SCAN | TAB1(IDX_TAB1) |
+------------+--------------+----------------+----------------+
1 row in set (0.003 sec)

在U1上创建OUTLINE使SQL强制走全表扫,然后分别U1,U2执行

conn u1
create or replace outline otl_F572210BDF51A91B9E817F0E51F5C56F on 'F572210BDF51A91B9E817F0E51F5C56F' using hint /*+full(tab1) */;

obclient [u01]> conn u1
Connection id:    64787
Current database: U1

obclient [U1]> create or replace outline otl_F572210BDF51A91B9E817F0E51F5C56F on 'F572210BDF51A91B9E817F0E51F5C56F' using hint /*+full(tab1) */;
Query OK, 0 rows affected (0.016 sec


obclient [U1]> select * from tab1 where id=10 /* testoutlinesql */;
+------+--------+
| ID   | C      |
+------+--------+
|   10 | test10 |
+------+--------+
1 row in set (0.014 sec)

obclient [U1]> select * from tab1 where id=10 /* testoutlinesql */;
+------+--------+
| ID   | C      |
+------+--------+
|   10 | test10 |
+------+--------+
1 row in set (0.006 sec)

obclient [U1]> conn u2
Connection id:    82404
Current database: U2

obclient [U2]> select * from tab1 where id=10 /* testoutlinesql */;
+------+--------+
| ID   | C      |
+------+--------+
|   10 | test10 |
+------+--------+
1 row in set (0.004 sec)

obclient [U2]> select * from tab1 where id=10 /* testoutlinesql */;
+------+--------+
| ID   | C      |
+------+--------+
|   10 | test10 |
+------+--------+
1 row in set (0.001 sec)

查询执行计划，分别可以看到2条PLAN CACHE，U1走全表扫，U2走索引

obclient [oceanbase]>  select tenant_id,db_id,svr_ip,svr_port,plan_id,sql_id,outline_id,query_sql from gv$plan_cache_plan_stat where query_sql not like '%plan_cache_plan_stat%' and query_sql like 'select * from tab1 where id=10 /* testoutlinesql */%';
+-----------+------------------+---------------+----------+---------+----------------------------------+------------------+-----------------------------------------------------+
| tenant_id | db_id            | svr_ip        | svr_port | plan_id | sql_id                           | outline_id       | query_sql                                           |
+-----------+------------------+---------------+----------+---------+----------------------------------+------------------+-----------------------------------------------------+
|      1001 | 1100611139404851 | 192.168.56.36 |     2882 | 1244139 | F572210BDF51A91B9E817F0E51F5C56F | 1100611139404778 | select * from tab1 where id=10 /* testoutlinesql */ |
|      1001 | 1100611139404852 | 192.168.56.36 |     2882 | 1244140 | F572210BDF51A91B9E817F0E51F5C56F |               -1 | select * from tab1 where id=10 /* testoutlinesql */ |
+-----------+------------------+---------------+----------+---------+----------------------------------+------------------+-----------------------------------------------------+
2 rows in set (0.040 sec)

obclient [oceanbase]>  select PLAN_DEPTH,PLAN_LINE_ID,OPERATOR,NAME from gv$plan_cache_plan_explain where tenant_id=1001 and ip='192.168.56.36' and PORT=2882 and plan_id=1244139;
+------------+--------------+----------------+------+
| PLAN_DEPTH | PLAN_LINE_ID | OPERATOR       | NAME |
+------------+--------------+----------------+------+
|          0 |            0 | PHY_TABLE_SCAN | TAB1 |
+------------+--------------+----------------+------+
1 row in set (0.006 sec)

obclient [oceanbase]>  select PLAN_DEPTH,PLAN_LINE_ID,OPERATOR,NAME from gv$plan_cache_plan_explain where tenant_id=1001 and ip='192.168.56.36' and PORT=2882 and plan_id=1244140;
+------------+--------------+----------------+----------------+
| PLAN_DEPTH | PLAN_LINE_ID | OPERATOR       | NAME           |
+------------+--------------+----------------+----------------+
|          0 |            0 | PHY_TABLE_SCAN | TAB1(IDX_TAB1) |
+------------+--------------+----------------+----------------+
1 row in set (0.003 sec)

不同用户，查同一用户下的表,同样不能共享outline

obclient [SYS]> grant dba to u1;
Query OK, 0 rows affected (0.040 sec)

obclient [SYS]> grant dba to u2;
Query OK, 0 rows affected (0.020 sec)



conn u1
select * from u2.tab1 where id=10 /* testoutlinesql2 */;
conn u2
select * from u2.tab1 where id=10 /* testoutlinesql2 */;


select tenant_id,db_id,svr_ip,svr_port,plan_id,sql_id,outline_id,query_sql from gv$plan_cache_plan_stat where query_sql not like '%plan_cache_plan_stat%' and query_sql like 'select * from u2.tab1 where id=10 /* testoutlinesql2 */%';

obclient [oceanbase]> select tenant_id,db_id,svr_ip,svr_port,plan_id,sql_id,outline_id,query_sql from gv$plan_cache_plan_stat where query_sql not like '%plan_cache_plan_stat%' and query_sql like 'select * from u2.tab1 where id=10 /* testoutlinesql2 */%';
+-----------+------------------+---------------+----------+---------+----------------------------------+------------+---------------------------------------------------------+
| tenant_id | db_id            | svr_ip        | svr_port | plan_id | sql_id                           | outline_id | query_sql                                               |
+-----------+------------------+---------------+----------+---------+----------------------------------+------------+---------------------------------------------------------+
|      1001 | 1100611139404851 | 192.168.56.36 |     2882 | 1244485 | EC52FD01411D1B5039284FD455CE55EC |         -1 | select * from u2.tab1 where id=10 /* testoutlinesql2 */ |
|      1001 | 1100611139404852 | 192.168.56.36 |     2882 | 1244486 | EC52FD01411D1B5039284FD455CE55EC |         -1 | select * from u2.tab1 where id=10 /* testoutlinesql2 */ |
+-----------+------------------+---------------+----------+---------+----------------------------------+------------+---------------------------------------------------------+
2 rows in set (0.041 sec)

conn u2
create or replace outline otl_EC52FD01411D1B5039284FD455CE55EC on 'EC52FD01411D1B5039284FD455CE55EC' using hint /*+full(tab1) */;

obclient [U2]> conn u2
Connection id:    36094
Current database: U2

obclient [U2]> create or replace outline otl_EC52FD01411D1B5039284FD455CE55EC on 'EC52FD01411D1B5039284FD455CE55EC' using hint /*+full(tab1) */;
Query OK, 0 rows affected (0.016 sec)

obclient [oceanbase]> alter system flush plan cache;
Query OK, 0 rows affected (0.022 sec)

conn u1
select * from u2.tab1 where id=10 /* testoutlinesql2 */;
conn u2
select * from u2.tab1 where id=10 /* testoutlinesql2 */;

[root@ocp ~]# obclient -h192.168.56.36 -P2883 -uu1@test#obdemo -ptest -c
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 36156
Server version: OceanBase 3.2.4.5 (r105040022023111323-57088ca24eab4348125068a9ccf182d068a6a883) (Built Nov 13 2023 23:56:19)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [U1]> select * from u2.tab1 where id=10 /* testoutlinesql2 */;
+------+--------+
| ID   | C      |
+------+--------+
|   10 | test10 |
+------+--------+
1 row in set (0.001 sec)

obclient [U1]> exit
Bye
[root@ocp ~]# obclient -h192.168.56.36 -P2883 -uu2@test#obdemo -ptest -c
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 36163
Server version: OceanBase 3.2.4.5 (r105040022023111323-57088ca24eab4348125068a9ccf182d068a6a883) (Built Nov 13 2023 23:56:19)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [U2]> select * from u2.tab1 where id=10 /* testoutlinesql2 */;
+------+--------+
| ID   | C      |
+------+--------+
|   10 | test10 |
+------+--------+
1 row in set (0.011 sec)



obclient [oceanbase]> select tenant_id,db_id,svr_ip,svr_port,plan_id,sql_id,outline_id,query_sql from gv$plan_cache_plan_stat where query_sql not like '%plan_cache_plan_stat%' and query_sql like 'select * from u2.tab1 where id=10 /* testoutlinesql2 */%';
+-----------+------------------+---------------+----------+---------+----------------------------------+------------------+---------------------------------------------------------+
| tenant_id | db_id            | svr_ip        | svr_port | plan_id | sql_id                           | outline_id       | query_sql                                               |
+-----------+------------------+---------------+----------+---------+----------------------------------+------------------+---------------------------------------------------------+
|      1001 | 1100611139404851 | 192.168.56.36 |     2882 | 1244485 | EC52FD01411D1B5039284FD455CE55EC |               -1 | select * from u2.tab1 where id=10 /* testoutlinesql2 */ |
|      1001 | 1100611139404852 | 192.168.56.36 |     2882 | 1244497 | EC52FD01411D1B5039284FD455CE55EC | 1100611139404779 | select * from u2.tab1 where id=10 /* testoutlinesql2 */ |
+-----------+------------------+---------------+----------+---------+----------------------------------+------------------+---------------------------------------------------------+
2 rows in set (0.045 sec)

用户，就是database
obclient [oceanbase]> select database_id,database_name from __all_virtual_database where database_id in (1100611139404851,1100611139404852);
+------------------+---------------+
| database_id      | database_name |
+------------------+---------------+
| 1100611139404851 | U1            |
| 1100611139404852 | U2            |
+------------------+---------------+
2 rows in set (0.048 sec)

oceanbase 墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

ob sql outline为用户级，相同SQL不同用户不共享

概述

测试验证

评论