隐含参数_db_block_numa

最新推荐文章于 2021-04-03 11:25:22 发布

bitko

最新推荐文章于 2021-04-03 11:25:22 发布

阅读量746

点赞数

分类专栏： Oracle-Parameter

本文链接：https://blog.csdn.net/gumengkai/article/details/60468131

版权

Oracle-Parameter 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本文探讨了数据库参数_db_block_numa对优化器选择执行计划的影响。当该参数设为false时，优化器将忽略索引跳跃扫描，而设为true时，则会根据成本估算选择是否使用索引跳跃扫描。

摘要由CSDN通过智能技术生成

SQL> select
  2    x.ksppinm  name,
  3    y.ksppstvl  value,
  4    y.ksppstdf  isdefault,
  5    decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,
  6    decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj
  7  from
  8    sys.x$ksppi x,
  9    sys.x$ksppcv y
 10  where
 11    x.inst_id = userenv('Instance') and
 12    y.inst_id = userenv('Instance') and
 13    x.indx = y.indx and
 14    x.ksppinm like '%_optimizer_skip_scan_enabled%'
 15  order by
 16    translate(x.ksppinm, ' _', ' ');

NAME                      VALUE                     ISDEFAULT ISMOD      ISADJ
------------------------- ------------------------- --------- ---------- -----
_optimizer_skip_scan_enab TRUE                      TRUE      FALSE      FALSE
led

当该参数被设置为false时，优化器将不会考虑索引跳跃扫描

# 创建测试表

SQL> create index ind_test on test(object_type,object_id,object_name); 

Index created.

SQL> analyze table test compute statistics for all indexed columns; 

Table analyzed.

SQL> select object_id from test where object_type='INDEX PARTITION' and rownum<2;

 OBJECT_ID
----------
      5851

# 将参数修改为false

SQL> alter session set "_optimizer_skip_scan_enabled"=false;

Session altered.

# 查询指定object_id,执行计划为全表扫描

SQL> select * from test where object_id=5851;


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   312 |   415   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |   312 |   415   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=5851)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         20  recursive calls
          0  db block gets
       1622  consistent gets
       1566  physical reads
          0  redo size
       1877  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

# 将参数修改为true

SQL> alter session set "_optimizer_skip_scan_enabled"=true; 

Session altered.

# 当此参数设置为true时，优化器将会估算根本，如果索引跳跃扫描成本更低，将选择索引跳跃扫描的方式来产生执行计划

SQL> select * from test where object_id=5851;


Execution Plan
----------------------------------------------------------
Plan hash value: 4219850847

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     1 |   312 |    47   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST     |     1 |   312 |    47   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | IND_TEST |     1 |       |    46   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=5851)
       filter("OBJECT_ID"=5851)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        102  consistent gets
         31  physical reads
          0  redo size
       1881  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed