oracle使用SPM和直方图解决数据倾斜导致的性能问题

原创墨香溪 2025-01-03

117

本测试环境使用19.22。

源参考文档：https://www.modb.pro/db/63305 本文做为测试学习记录

创建测试环境：

create table t1 as select * from dba_objects;
create table t2 as select * from t1;
create index idx_t1_status on t1(status);
create index idx_t2_object_id on t2(object_id);
--收集统计信息
exec  DBMS_STATS.GATHER_TABLE_STATS ( ownname=> 'SYS' ,  tabname => 'T1');
exec  DBMS_STATS.GATHER_TABLE_STATS ( ownname=> 'SYS' ,  tabname => 'T2');
复制

查看直方图信息：

col TABLE_NAME for a10
col column_name for a10

SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM   DBA_TAB_COL_STATISTICS
WHERE  TABLE_NAME in ('T1','T2')
AND    COLUMN_NAME='STATUS';

SELECT TABLE_NAME,ENDPOINT_NUMBER, to_char(ENDPOINT_VALUE)
FROM   DBA_HISTOGRAMS
WHERE  TABLE_NAME in  ('T1','T2')
AND    COLUMN_NAME='STATUS';复制

output:

TABLE_NAME COLUMN_NAM NUM_DISTINCT HISTOGRAM
---------- ---------- ------------ ---------------
T2         STATUS                2 NONE
T1         STATUS                2 NONE

2 rows selected.

--second query:
TABLE_NAME ENDPOINT_NUMBER TO_CHAR(ENDPOINT_VALUE)
---------- --------------- ----------------------------------------
T2                       0 380626532452853000000000000000000000
T1                       0 380626532452853000000000000000000000
T2                       1 447861930473196000000000000000000000
T1                       1 447861930473196000000000000000000000
复制

测试：

1. 无直方图

var B1 varchar2(100);
exec :B1 := 'VALID';    
select /*Ivan test SQL */ t2.owner,t1.object_name from t1,t2
where t1.status = :B1
and t1.object_id = t2.object_id
and t2.owner='APPS';复制

查看执行计划：

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST peeked_binds'));
复制

执行计划如下：

SQL_ID  884b4fx93nzx3, child number 0
-------------------------------------
select /*Ivan test SQL */ t2.owner,t1.object_name from t1,t2 where
t1.status = :B1 and t1.object_id = t2.object_id and t2.owner='APPS'

Plan hash value: 4148581417

-----------------------------------------------------------------------------------
| Id  | Operation                   | Name    | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |        |       |       |          |
|*  1 |  HASH JOIN                  |         |   1656 |    12M|  5123K| 9057K (0)|
|   2 |   JOIN FILTER CREATE        | :BF0000 |   1656 |       |       |          |
|*  3 |    TABLE ACCESS STORAGE FULL| T2      |   1656 |  1028K|  1028K|          |
|   4 |   JOIN FILTER USE           | :BF0000 |    253K|       |       |          |
|*  5 |    TABLE ACCESS STORAGE FULL| T1      |    253K|  1028K|  1028K|          |
-----------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=873): 'VALID'

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - storage("T2"."OWNER"='APPS')
       filter("T2"."OWNER"='APPS')
   5 - storage(("T1"."STATUS"=:B1 AND
              SYS_OP_BLOOM_FILTER(:BF0000,"T1"."OBJECT_ID")))
       filter(("T1"."STATUS"=:B1 AND SYS_OP_BLOOM_FILTER(:BF0000,"T1"."OBJE
              CT_ID")))
复制

使用绑定变量= INVALID:

exec :B1 := 'INVALID';
select /*Ivan test SQL */ t2.owner,t1.object_name from t1,t2
where t1.status = :B1
and t1.object_id = t2.object_id
and t2.owner='APPS';复制

再次查看执行计划：

SQL_ID  884b4fx93nzx3, child number 1
-------------------------------------
select /*Ivan test SQL */ t2.owner,t1.object_name from t1,t2 where
t1.status = :B1 and t1.object_id = t2.object_id and t2.owner='APPS'

Plan hash value: 4148581417

-----------------------------------------------------------------------------------
| Id  | Operation                   | Name    | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |        |       |       |          |
|*  1 |  HASH JOIN                  |         |   1656 |    12M|  5123K| 9090K (0)|
|   2 |   JOIN FILTER CREATE        | :BF0000 |   1656 |       |       |          |
|*  3 |    TABLE ACCESS STORAGE FULL| T2      |   1656 |  1028K|  1028K|          |
|   4 |   JOIN FILTER USE           | :BF0000 |    253K|       |       |          |
|*  5 |    TABLE ACCESS STORAGE FULL| T1      |    253K|  1028K|  1028K|          |
-----------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=873): 'INVALID'

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - storage("T2"."OWNER"='APPS')
       filter("T2"."OWNER"='APPS')
   5 - storage(("T1"."STATUS"=:B1 AND
              SYS_OP_BLOOM_FILTER(:BF0000,"T1"."OBJECT_ID")))
       filter(("T1"."STATUS"=:B1 AND SYS_OP_BLOOM_FILTER(:BF0000,"T1"."OBJE
              CT_ID")))
复制

在使用绑定变量status='INVALID' 的情况下，我们希望他走nestloop。

2. 有直方图

收集直方图信息：

BEGIN  DBMS_STATS.GATHER_TABLE_STATS ( 
    ownname          => 'SYS'
,   tabname          => 'T1'
,   method_opt       => 'FOR COLUMNS STATUS'
,   NO_INVALIDATE => FALSE
);
END;
/
复制

查看直方图信息，我们可以看到T1列的status 列有了直方图信息：

02-JAN 23:29:41 SYS@DEV11> SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
  2  FROM   DBA_TAB_COL_STATISTICS
  3  WHERE  TABLE_NAME in ('T1','T2')
  4  AND    COLUMN_NAME='STATUS';

TABLE_NAME COLUMN_NAM NUM_DISTINCT HISTOGRAM
---------- ---------- ------------ ---------------
T2         STATUS                2 NONE
T1         STATUS                2 FREQUENCY

2 rows selected.

02-JAN 23:31:11 SYS@DEV11> SELECT TABLE_NAME,ENDPOINT_NUMBER, to_char(ENDPOINT_VALUE)
  2  FROM   DBA_HISTOGRAMS
  3  WHERE  TABLE_NAME in  ('T1','T2')
  4  AND    COLUMN_NAME='STATUS';

TABLE_NAME ENDPOINT_NUMBER TO_CHAR(ENDPOINT_VALUE)
---------- --------------- ----------------------------------------
T1                     146 380626532452853000000000000000000000
T1                  506806 447861930473196000000000000000000000
T2                       0 380626532452853000000000000000000000
T2                       1 447861930473196000000000000000000000

4 rows selected.复制

清除执行计划，再次执行：

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '884b4fx93nzx3';
exec DBMS_SHARED_POOL.PURGE ('00000001DFEA4E38, 1379565475', 'C');
复制

会发现，SQL第一次执行的计划会根据获取到的绑定变量拿直方图找到对的执行计划。然后更改绑定变量，执行计划还是不对。

但是因为有ACS 的存在，如果是这个SQL会经常重新解析的话，比如换一个会话，那么会根据新的绑定变量会重新生成执行计划。

3. 直方图 + SPM

再次执行一下SQL：

var B1 varchar2(100);
exec :B1 := 'VALID';    
select /*Ivan test SQL */ t2.owner,t1.object_name from t1,t2
where t1.status = :B1
and t1.object_id = t2.object_id
and t2.owner='APPS';
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST peeked_binds'));

复制

这个是得到的走HASH的执行计划，使用SPM 绑定：

 DECLARE
    cnt NUMBER;
 BEGIN
    cnt := sys.dbms_spm.load_plans_from_cursor_cache(sql_id=>'884b4fx93nzx3',PLAN_HASH_VALUE=>4148581417);
 END;
 /
复制

查看绑定的baseline:

SELECT b.sql_handle, b.plan_name, b.enabled,b.accepted,b.OPTIMIZER_COST,b.ORIGIN
 FROM   dba_sql_plan_baselines b, v$sql s
 WHERE  s.sql_id='884b4fx93nzx3'
 AND    s.exact_matching_signature = b.signature;复制


SQL_HANDLE                     PLAN_NAME                                                                                                                        ENA ACC OPTIMIZER_COST ORIGIN
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- --- --- -------------- -----------------------------
SQL_2e4089d9e8044762           SQL_PLAN_2wh49v7n08jv2c1292905                                                                                                   YES YES           3251 MANUAL-LOAD-FROM-CURSOR-CACHE复制

那么我们还需要一个使用索引的执行计划，直接加hint 生成出来。

--create new plan for use index
var B1 varchar2(100);
exec :B1 := 'INVALID';    
select /*+ leading(t1 t2) use_nl(t1 t2) INDEX(t1) INDEX(t2) */ t2.owner,t1.object_name from t1,t2
where t1.status = :B1
and t1.object_id = t2.object_id
and t2.owner='APPS';


select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST peeked_binds'));
复制

output：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b78dazcnnrzzm, child number 0
-------------------------------------
select /*+ leading(t1 t2) use_nl(t1 t2) INDEX(t1) INDEX(t2) */
t2.owner,t1.object_name from t1,t2 where t1.status = :B1 and
t1.object_id = t2.object_id and t2.owner='APPS'

Plan hash value: 631200676

---------------------------------------------------------------------------
| Id  | Operation                             | Name             | E-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |        |
|   1 |  NESTED LOOPS                         |                  |    146 |
|   2 |   NESTED LOOPS                        |                  |    146 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1               |    146 |
|*  4 |     INDEX RANGE SCAN                  | IDX_T1_STATUS    |    146 |
|*  5 |    INDEX RANGE SCAN                   | IDX_T2_OBJECT_ID |      1 |
|*  6 |   TABLE ACCESS BY INDEX ROWID         | T2               |      1 |
---------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=873): 'INVALID'

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."STATUS"=:B1)
   5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   6 - filter("T2"."OWNER"='APPS')复制

将此SQL执行计划绑定到原来的SQL上


--bind the execution plan on target SQL
DECLARE
 cnt NUMBER;
BEGIN
 cnt := sys.dbms_spm.load_plans_from_cursor_cache(
               sql_id=>'b78dazcnnrzzm',   -- 加hint 的SQL 的SQL ID
               plan_hash_value=>'631200676',  --加hint 的SQL 的plan hash value, 
               sql_handle=>'SQL_2e4089d9e8044762');  --sql_handle为884b4fx93nzx3 的sql_handle
END;
/

复制

查看绑定情况，可以看到已经enable 和接受了两个执行计划：

SELECT b.sql_handle, b.plan_name, b.enabled,b.accepted,b.OPTIMIZER_COST,b.ORIGIN
     FROM   dba_sql_plan_baselines b, v$sql s
     WHERE  s.sql_id='884b4fx93nzx3'
     AND    s.exact_matching_signature = b.signature;

SQL_HANDLE                     PLAN_NAME                                                                                                                        ENA ACC OPTIMIZER_COST ORIGIN
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- --- --- -------------- -----------------------------
SQL_2e4089d9e8044762           SQL_PLAN_2wh49v7n08jv2810f78c7                                                                                                   YES YES            444 MANUAL-LOAD-FROM-CURSOR-CACHE
SQL_2e4089d9e8044762           SQL_PLAN_2wh49v7n08jv2c1292905                                                                                                   YES YES           3251 MANUAL-LOAD-FROM-CURSOR-CACHE
SQL_2e4089d9e8044762           SQL_PLAN_2wh49v7n08jv2810f78c7                                                                                                   YES YES            444 MANUAL-LOAD-FROM-CURSOR-CACHE
SQL_2e4089d9e8044762           SQL_PLAN_2wh49v7n08jv2c1292905                                                                                                   YES YES           3251 MANUAL-LOAD-FROM-CURSOR-CACHE
复制

再执行一下这个：

declare 
c clob;
begin
c:=dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_2074b601a2f7c2ec', plan_name=>'SQL_PLAN_2wh49v7n08jv2c1292905',Verify=>'NO',commit=>'YES');
end;
/复制

再来看看实际的执行情况：


var B1 varchar2(100);
exec :B1 := 'VALID';    
select /*Ivan test SQL */ t2.owner,t1.object_name from t1,t2
where t1.status = :B1
and t1.object_id = t2.object_id
and t2.owner='APPS';

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST peeked_binds'));


var B1 varchar2(100);
exec :B1 := 'INVALID';    
select /*Ivan test SQL */ t2.owner,t1.object_name from t1,t2
where t1.status = :B1
and t1.object_id = t2.object_id
and t2.owner='APPS';

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST peeked_binds'));
复制

测试好几次以后，发现也不是很稳定。

墨力计划 oracle

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

关注作者

oracle使用SPM和直方图解决数据倾斜导致的性能问题

创建测试环境：

测试：

1. 无直方图

2. 有直方图

评论

相关阅读