隐藏问题: Oracle 11g存在index full scan替代index fast full scan的低效执行计划
在Oracle数据库中，索引是提高查询性能的关键工具之一。通过使用索引，数据库可以快速地定位和检索数据，从而加快查询速度并降低系统资源消耗。在索引扫描过程中，有两种主要的方法：索引快速全扫描（Index Fast Full Scan）和索引全扫描（Index Full Scan）。然而，在某些情况下，数据库可能会出现错误的执行计划，选择索引全扫描而不是预期的索引快速全扫描，导致性能下降和资源浪费。该类问题可能不容易发现，仅是SQL性能差，或主要的等待事件为db file sequential read，在本文中，我们将探讨Oracle 11g中出现索引全扫描替代索引快速全扫描的情况。
案例：env Oracle 11.2.0.4 4-nodes RAC
一日客户反馈SQL运行效率慢，1个多小时未执行完成，且没有sql monitor的信息。
SQL Text
------------------------------
DELETE /*+parallel(8) */ FROM ANBOB_1.MID***************_REL_DAY PARTITION(P20240408) A
WHERE EXISTS (SELECT 1 FROM TEMP_**************_REL B WHERE A.OFFER_INST_ID = B.OFFER_INST_ID AND A.INSTANCE_ID = B.INSTANCE_ID )
Global Information
------------------------------
Status : DONE (ERROR)
Instance ID : 1
Session : ANBOB_1 (2661:175)
SQL ID : 75z55ptrvn0ux
SQL Execution ID : 16777216
Execution Started : 04/09/2024 00:19:25
First Refresh Time : 04/09/2024 00:19:25
Last Refresh Time : 04/09/2024 00:19:25
Duration : 0s
Module/Action : JDBC Thin Client/-
Service : dayodsdb
Program : JDBC Thin Client
PLSQL Entry Ids (Object/Subprogram) : 42224025,1
PLSQL Current Ids (Object/Subprogram) : 42224025,1
Global Stats
SQL Plan Monitoring Details (Plan Hash Value=34080627)
================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
================================================================================================================================================================
| 0 | DELETE STATEMENT | | | | | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 597K | 564 | | | | | | |
| 3 | DELETE | MID***************_REL_DAY | | | | | | | | |
| 4 | PX RECEIVE | | 597K | 564 | | | | | | |
| 5 | PX SEND HASH (BLOCK ADDRESS) | :TQ10002 | 597K | 564 | | | | | | |
| 6 | HASH JOIN RIGHT SEMI BUFFERED | | 597K | 564 | | | | | | |
| 7 | PX RECEIVE | | 597K | 114 | | | | | | |
| 8 | PX SEND HASH | :TQ10000 | 597K | 114 | | | | | | |
| 9 | PX BLOCK ITERATOR | | 597K | 114 | | | | | | |
| 10 | TABLE ACCESS FULL | TEMP_**************_REL | 597K | 114 | | | | | | |
| 11 | PX RECEIVE | | 818M | 36 | | | | | | |
| 12 | PX SEND HASH | :TQ10001 | 818M | 36 | | | | | | |
| 13 | PX PARTITION LIST ALL | | 818M | 36 | | | | | | |
| 14 | INDEX FULL SCAN | IDX_MID_***********RELB2 | 818M | 36 | | | | | | |
================================================================================================================================================================
Note:
从上面的sql monitor report发现几个问题
1，SQL执行了一个多小时，但是状态是DONE (ERROR)，表示中断，duration 又是0秒。
2，执行计划的stats部分多数为空
3，表上亿级别的数据cost如此小
当时真的hang死了吗？
break on inst_id skip page on etime skip 1
col etime for a20
col event for a25
COL PCT FOR 9999999
col cpus for a5
col load for a30
col db_time for a15
col in_parse for a10
col qc for a10
col sql_id for a22
col avg_waited for 999,990.90 head avg_ms
col sesid for a15
col bs for a10
col TOP_LEVEL_SQL_ID for a22
col SQL_ID for a18
col p1_p2 for a15
select to_char(SAMPLE_TIME,'yyyymmdd hh24:mi:ss') etime,event,p1||':'||p2 p1_p2,inst_id||':'||SESSION_ID||':'||SESSION_SERIAL# sesid,IS_SQLID_CURRENT,--SQL_OPCODE,
sql_id --,TOP_LEVEL_SQL_ID ,TOP_LEVEL_SQL_OPCODE ,SQL_PLAN_HASH_VALUE,SQL_PLAN_LINE_ID,SQL_PLAN_OPERATION
,SQL_EXEC_ID,SQL_EXEC_START
--,PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID,PLSQL_OBJECT_ID
,QC_INSTANCE_ID||':'||QC_SESSION_ID||':'||QC_SESSION_SERIAL# qc,SEQ# ,
SESSION_STATE,TIME_WAITED,BLOCKING_INST_ID||':'||BLOCKING_SESSION bs,CURRENT_OBJ# ,TOP_LEVEL_CALL_NAME
IN_PARSE,IN_SQL_EXECUTION,IN_PLSQL_EXECUTION --,IN_BIND,IN_CURSOR_CLOSE,MACHINE,PROGRAM
--,PGA_ALLOCATED,TEMP_SPACE_ALLOCATED
from system.ASH_20240409
where QC_SESSION_ID=2661 and QC_SESSION_SERIAL#=175
order by 1
/
ETIME EVENT P1_P2 SESID IS SQL_ID SQL_EXEC_ID SQL_EXEC_START QC SEQ# SESSION_STATE TIME_WAITED BS CURRENT_OBJ# IN_PARSE IN IN
-------------------- ------------------------- --------------- --------------- -- ---------------------- ----------- ------------------- ---------- ---------- -------------- ----------- ---------- ------------ ---------- -- --
20240409 00:41:33 db file sequential read 296:2466880 3:1921:719 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 11627 WAITING 11986 : 78633106 VERSION2 Y N
db file sequential read 307:1549207 3:1937:785 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 12392 WAITING 42647 : 78633108 VERSION2 Y N
db file sequential read 129:2889654 3:1953:705 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 15144 WAITING 39689 : 78633109 VERSION2 Y N
db file sequential read 605:1452705 3:2178:3535 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 18043 WAITING 59995 : 78633103 LOGOFF Y N
db file sequential read 219:2707008 3:1987:1629 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 19671 WAITING 69086 : 78633104 LOGOFF Y N
db file sequential read 279:1342039 3:2130:2897 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 20740 WAITING 43806 : 78633101 VERSION2 Y N
db file sequential read 650:1753692 3:2147:1417 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 21085 WAITING 57736 : 78633102 Y N
db file sequential read 19:1403508 3:2162:391 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 25971 WAITING 71274 : 78633100 VERSION2 Y N
20240409 00:41:34 gc cr disk read 296:2467668 3:1921:719 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 11683 WAITING 7414 : 78633106 VERSION2 Y N
db file sequential read 307:1548682 3:1937:785 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 12427 WAITING 22742 : 78633108 VERSION2 Y N
db file sequential read 551:2326697 3:1953:705 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 15192 WAITING 128802 : 78633109 VERSION2 Y N
db file sequential read 194:1359784 3:2130:2897 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 20814 WAITING 81848 : 78633101 VERSION2 Y N
db file sequential read 650:1753871 3:2147:1417 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 21119 WAITING 136779 : 78633102 Y N
db file sequential read 542:1733688 3:2162:391 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 26022 WAITING 78606 : 78633100 VERSION2 Y N
db file sequential read 605:1453519 3:2178:3535 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 18069 WAITING 63887 : 78633103 LOGOFF Y N
db file sequential read 536:2329737 3:1987:1629 Y 75z55ptrvn0ux 16777216 2024-04-09 00:19:25 1:2661:175 19707 WAITING 82553 : 78633104 LOGOFF Y N
...
Note:
注意实际上SQL一直在运行，也未堵塞，因为没有配置parallel_force_local,所以QC在node1,但SLAVE在node3,当然还可以ASH信息查看时间花费在执行计划哪一步，或使用asqlmon.sql脚本。 这个SQL比较简单，从执行计划也能猜出应该是Index full scan那步。
为什么使用index full scan，而不是index fast full scan？
我们知道index fast full scan和Full table scan一样使用多块读，对于全扫索引扫，而大量的时间在db file sequential read单块读效率是低下的,对于两者的区别TOM在OTN上也有描述.
An index fast full scan reads the ENTIRE index, unsorted, as it exists on disk. It is basically using the index as a “skinny” version of the table. The query in question would only be accessing attributes in the index (we are not using the index as a way to get to the table, we are using the index INSTEAD of the table) We use multiblock IO and read all of the leaf, branch and the root block. We ignore the branch and root blocks and just process the (unordered) data on the leaf blocks.
An index full scan is when we read the index a block at a time – from start to finish. We’ll read the root block, navigate down the left hand side of the index (or right if we are doing a descending full scan) and then when we hit the leaf block – we’ll read across the entire bottom of the index – a block at a time – in sorted order. We use single block IO, not multiblock IO for this operation.
那么用IFFS好事还是坏事呢？不同的人可能会以不同的方式回答这个问题。
另一方面，当您进行IFFS时，它可能会执行直接路径读取，而IFS将执行缓存读取，并且缓存可能已经包含了很多索引叶块，因此有些人可能更喜欢当前行为;
另一方面，Exadata 系统在执行直接路径段扫描时可以使用存储索引（即使该段是索引），因此 Exadata IFFS可能比部分缓存的IFS更可取。
在11g的测试库先还原了该问题
SQL> create table test1 as select rownum id ,rownum||'anbob' name,lpad('x',4000,'xxxx') addr from dual connect by rownum<=1e4;
SQL> @gts test1
Gather Table Statistics for table test1...
SQL> create table test2 as select rownum id ,rownum||'anbob' name,lpad('x',4000,'xxxx') addr from dual connect by rownum<=1e5;
Table created.
SQL> create index idx_test2_id on test2(id);
Index created.
SQL> @gts test2
Gather Table Statistics for table test2...
PL/SQL procedure successfully completed.
SQL> explain plan for delete test2 where exists(select 1 from test1 where test1.id=test2.id);
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2072194808
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 9999 | 175K| 2937 (1)| 00:00:36 |
| 1 | DELETE | TEST2 | | | | |
|* 2 | HASH JOIN RIGHT SEMI| | 9999 | 175K| 2937 (1)| 00:00:36 |
| 3 | VIEW | VW_SQ_1 | 10000 | 126K| 2713 (1)| 00:00:33 |
| 4 | TABLE ACCESS FULL | TEST1 | 10000 | 40000 | 2713 (1)| 00:00:33 |
| 5 | INDEX FULL SCAN | IDX_TEST2_ID | 100K| 488K| 224 (1)| 00:00:03 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="TEST2"."ID")
Note:
如果您在你的生产环境使用我相同的测试用例相同现象，恭喜你，也存在相同的BUG. 对于数据量很大的表，使用index full scan单块读再回表效率确实不是最好的，该问题不容易被发现，如果你有SQL审核产品不妨看看有没有相同的场景，我们可以加hint让CBO判断IFFS能不能使用？
增加IFFS hint
SQL> explain plan for delete /*+index_ffs(test2)*/ test2 where exists(select 1 from test1 where test1.id=test2.id); Explained. SQL> @x2 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2566228551 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 9999 | 175K| 2776 (1)| 00:00:34 | | 1 | DELETE | TEST2 | | | | | |* 2 | HASH JOIN RIGHT SEMI | | 9999 | 175K| 2776 (1)| 00:00:34 | | 3 | VIEW | VW_SQ_1 | 10000 | 126K| 2713 (1)| 00:00:33 | | 4 | TABLE ACCESS FULL | TEST1 | 10000 | 40000 | 2713 (1)| 00:00:33 | | 5 | INDEX FAST FULL SCAN| IDX_TEST2_ID | 100K| 488K| 63 (2)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ITEM_1"="TEST2"."ID") 17 rows selected.
Note:
说明IFFS确实也可以使用，并且估的cost比IFS还要低。难道是这显示问题？我们可以做10053
10053 trace
SQL> @p multib
NAME VALUE
---------------------------------------- ----------------------------------------
db_file_multiblock_read_count 128
SQL> @p block_si
NAME VALUE
---------------------------------------- ----------------------------------------
db_block_size 8192
SQL> show parameter index_cost
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ --------------------------------- ----------------------------------
optimizer_index_cost_adj integer 100
SQL> @53on
alter session set events '10053 trace name context forever, level 1';
alter session set "_optimizer_trace"=all;
SQL> explain plan for delete from test2 where exists(select 1 from test1 where test1.id=test2.id);
Explained.
SQL> @53off
alter session set events '10053 trace name context off';
对于上面的SQL和加IFFS HINT的SQL分别做了10053, trace片段如下
Access path analysis for TEST2
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST2[TEST2]
Table: TEST2 Alias: TEST2
Card: Original: 100000.000000 Rounded: 100000 Computed: 100000.00 Non Adjusted: 100000.00
Access Path: index (index (FFS))
Index: IDX_TEST2_ID
resc_io: 62.00 resc_cpu: 13580960
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 62.56 Resp: 62.56 Degree: 1
Cost_io: 62.00 Cost_cpu: 13580960
Resp_io: 62.00 Resp_cpu: 13580960
Best:: AccessPath: IndexFFS
Index: IDX_TEST2_ID
Cost: 62.56 Degree: 1 Resp: 62.56 Card: 100000.00 Bytes: 0
Access path analysis for TEST2
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST2[TEST2]
Table: TEST2 Alias: TEST2
Card: Original: 100000.000000 Rounded: 100000 Computed: 100000.00 Non Adjusted: 100000.00
Access Path: TableScan
Cost: 27115.01 Resp: 27115.01 Degree: 0
Cost_io: 27085.00 Cost_cpu: 727144000
Resp_io: 27085.00 Resp_cpu: 727144000
Access Path: index (FullScan)
Index: IDX_TEST2_ID
resc_io: 223.00 resc_cpu: 21588081
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 223.89 Resp: 223.89 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_TEST2_ID
Cost: 223.89 Degree: 1 Resp: 223.89 Card: 100000.00 Bytes: 0
***************************************
NOTE:
由于索引快速完全扫描执行多块读取，实际也确实是IFFS的cost要低于IFS。 那oracle 19c是什么样的表现呢?
相同的用例在oracle 19c的执行计划
SQL> explain plan for delete from test2 where exists(select 1 from test1 where test1.id=test2.id); Explained. SQL> @x2 PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2566228551 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 10000 | 175K| 2775 (1)| 00:00:01 | | 1 | DELETE | TEST2 | | | | | |* 2 | HASH JOIN RIGHT SEMI | | 10000 | 175K| 2775 (1)| 00:00:01 | | 3 | VIEW | VW_SQ_1 | 10000 | 126K| 2712 (1)| 00:00:01 | | 4 | TABLE ACCESS FULL | TEST1 | 10000 | 40000 | 2712 (1)| 00:00:01 | | 5 | INDEX FAST FULL SCAN| IDX_TEST2_ID | 100K| 488K| 62 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ITEM_1"="TEST2"."ID")
Note:
在19c默认就可以使用IFFS. 那说明11.2.0.4确认可能存在某BUG或计算方法的错误。 另外也注意我们是没有调整optimizer_index_cost_adj参数值的，比如从100调到1，可能会产生IFFS切换到IFS的错误成本。JL大师在Index FFS Cost演示该问题,我这里不再复述。
单块读与多块读的COST计算
没有找到IFS/IFFS cost计算方法，但FTS的资料挺多可以用于多块读参考.
FTS Cost = ((BLOCKS/MBRC) x MREADTIM)/ SREADTIM + “CPU-costs”
而索引扫描单块读COST几乎就是IO次数，这也是为什么FTS多块读的cost要/SREADTIM单块读的时间，因为两个公式都*SREADTIM刚好就是多块读次数*多块读单次时间和单块读次数与单块读的时间，两个总时间的对比。
系统的统计信息记录在 aux_stats$
SQL> select sname,pname,pval1 from aux_stats$; SNAME PNAME PVAL1 ------------------------------------------------------------ ------------------------------------------------------------ ---------- SYSSTATS_INFO STATUS SYSSTATS_INFO DSTART SYSSTATS_INFO DSTOP SYSSTATS_INFO FLAGS 1 SYSSTATS_MAIN CPUSPEEDNW 2067 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN CPUSPEED SYSSTATS_MAIN MBRC SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN SLAVETHR 13 rows selected.
这里突出显示的 3 个指标正如我们所看到的没有设置。默认情况下，我们需要计算这些值，或者知道在哪里可以找到这些值的默认值才能获得它们。一旦有了这 3 个指标，我们就可以计算优化器用于将 MULTIBLOCK READ 转换为 SINGLE BLOCK READS 的成本指标的 RATIO。
CPU 速度用于计算查询的 COST（以所需的 CPU 为单位）。这只是查询成本的一小部分，几乎可以忽略, 但它可能有助于决定选择哪个计划。这也从 CPU 成本转换为单块读取的成本指标。
MBRC很容易。如果未显式设置，则使用 init.ora 参数 “db_file_multiblock_read_count” 。但是默认情况下不应设置此值，这意味着 Oracle 将使用隐藏参数“_db_file_optimizer_read_count”来计算查询费用。默认值为 8。[注意：这不是执行中使用的值。Oracle 尝试执行 1MB 读取，并使用“_db_file_exec_read_count”中的值来控制执行时的多块读取。对于 8K 块大小，此值设置为 128。
CPUSPEED – CPU Speed
IOTFRSPEED – I/O transfer speed
IOSEEKTIM – I/O seek time
SREADTIM – average single block read time
MREADTIM – average multiblock read time
MBRC – average multiblock read count
MAXTHR – maximum I/O system throughput
SLAVETHR – average slave I/O throughput
SREADTIM = IOSEEKTIM + DB_BLOCK_SIZE / IOTFRSPEED = 10 + 8192 / 4096 = 12 (ms)
MREADTIM = IOSEEKTIM + (DB_BLOCK_SIZE * MBRC) / IOTFRSPEED = 10 + (8192 * 8 ) / 4096 = 10 + 16 = 26 (ms)
# 删除系统统计信息
exec dbms_stats.delete_system_stats;
# 收集系统统计信息
exec dbms_stats.gather_system_stats;
# 手动配置统计信息
exec dbms_stats.set_system_stats('MREADTIM',1000);
exec dbms_stats.set_system_stats('SREADTIM',10);
exec dbms_stats.set_system_stats('MBRC',128);
Note:
注意这里我们也没有手动配置系统信息，因为SREADTIM和MREADTIM的时间差异也会影响单块读与多块读的差异。
– 如果您收集或设置了系统统计数据，请务必检查它们是否合理。
– 如果您确实使用非常低的 SREADTIM 和高 MREADTIM 来支持索引访问（不使用低值的 OPTIMIZER_INDEX_COST_ADJ），那么请确保 MREADTIM <= SREADTIM * MBRC。
关于 这两个计算方法ACED Neil Chandler在他的blog也有记录
select pname,pval1,calculated,formula from sys.aux_stats$ where sname='SYSSTATS_MAIN'
model
reference sga on (
select 'Database Buffers' name,sum(bytes) value from v$sgastat where name in ('shared_io_pool','buffer_cache')
) dimension by (name) measures(value)
reference parameter on (
select name,decode(type,3,to_number(value)) value from v$parameter where name='db_file_multiblock_read_count' and ismodified!='FALSE'
union all
select name,decode(type,3,to_number(value)) value from v$parameter where name='sessions'
union all
select name,decode(type,3,to_number(value)) value from v$parameter where name='db_block_size'
union all
SELECT a.ksppinm name, to_number(b.ksppstvl) value FROM x$ksppi a, x$ksppsv b WHERE a.indx=b.indx AND ksppinm like '_db_file_optimizer_read_count'
)
dimension by (name) measures(value)
partition by (sname) dimension by (pname) measures (pval1,pval2,cast(null as number) as calculated,cast(null as varchar2(120)) as formula) rules(
calculated['MBRC']=coalesce(pval1['MBRC'],parameter.value['db_file_multiblock_read_count'],parameter.value['_db_file_optimizer_read_count'],8),
calculated['MREADTIM']=coalesce(pval1['MREADTIM'],pval1['IOSEEKTIM'] + (parameter.value['db_block_size'] * calculated['MBRC'] ) / pval1['IOTFRSPEED']),
calculated['SREADTIM']=coalesce(pval1['SREADTIM'],pval1['IOSEEKTIM'] + parameter.value['db_block_size'] / pval1['IOTFRSPEED']),
calculated['_multi block Cost per block']=round(1/calculated['MBRC']*calculated['MREADTIM']/calculated['SREADTIM'],4),
calculated['_single block Cost per block']=1,
formula['MBRC']=case when pval1['MBRC'] is not null then 'MBRC' when parameter.value['db_file_multiblock_read_count'] is not null then 'db_file_multiblock_read_count' when parameter.value['_db_file_optimizer_read_count'] is not null then '_db_file_optimizer_read_count (db_file_multiblock_read_count not set, which is good!)' else '= not sure so used 8' end,
formula['MREADTIM']=case when pval1['MREADTIM'] is null then '= IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED' end||' = '||pval1['IOSEEKTIM']||'+('||parameter.value['db_block_size']||'*'||calculated['MBRC']||'/'||pval1['IOTFRSPEED']||')',
formula['SREADTIM']=case when pval1['SREADTIM'] is null then '= IOSEEKTIM + db_block_size / IOTFRSPEED' end||' = '||pval1['IOSEEKTIM']||'+('||parameter.value['db_block_size']||'/'||pval1['IOTFRSPEED']||')',
formula['_multi block Cost per block']='= 1/MBRC * MREADTIM/SREADTIM = 1/'||calculated['MBRC']||' * '||calculated['MREADTIM']||'/'||calculated['SREADTIM'],
calculated['_maximum mbrc']=sga.value['Database Buffers']/(parameter.value['db_block_size']*parameter.value['sessions']),
formula['_maximum mbrc']='= buffer cache blocks/sessions (small cache limiter) = ' || sga.value['Database Buffers']/parameter.value['db_block_size']||'/'||parameter.value['sessions'],
formula['_single block Cost per block']='relative to the multi blovk cost per block. Always 1!',
formula['CPUSPEED']='overrides CPUSPEEDNW when set',
formula['CPUSPEEDNW']='CPU speed Mhz - non workload',
formula['IOSEEKTIM']='IO seek time in ms',
formula['IOTFRSPEED']='IO transfer speed in KB/s',
formula['MAXTHR']='Maximum IO system throughput',
formula['SLAVETHR']='average parallel slave IO throughput'
) order by 1;
PNAME PVAL1 CALCULATED FORMULA
--------------------------------------- ---------- ---------- ----------------------------------------------------------------------------------------------
CPUSPEED overrides CPUSPEEDNW when set
CPUSPEEDNW 2067 CPU speed Mhz - non workload
IOSEEKTIM 10 IO seek time in ms
IOTFRSPEED 4096 IO transfer speed in KB/s
MAXTHR Maximum IO system throughput
MBRC 8 _db_file_optimizer_read_count (db_file_multiblock_read_count not set, which is good!)
MREADTIM 26 = IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED = 10+(8192*8/4096)
SLAVETHR average parallel slave IO throughput
SREADTIM 12 = IOSEEKTIM + db_block_size / IOTFRSPEED = 10+(8192/4096)
_maximum mbrc 238.575916 = buffer cache blocks/sessions (small cache limiter) = 364544/1528
_multi block Cost per block .2708 = 1/MBRC * MREADTIM/SREADTIM = 1/8 * 26/12
_single block Cost per block 1 relative to the multi blovk cost per block. Always 1!
另外Oracle CBO: Calculate Full Table Scan cost也有FULL table scan的计算方法，可以参考. 我转载附在后面.
目前是参数没有问题，系统统计信息也正常，接下来查找是否有相关BUG? 发现在11.2.0.4中使用IFS而没有使用IFFS的bug还确实有几个，目前使用11.2.0.4的生产还是挺多，虽然当前已主推19c很多年，以下BUG 都建议您修复。
FTS,IFS,IFFS错误切换已知BUG
Bug 17863980 Parallel query slow; not using better full table scan or index fast full scan
affected Versions >= 11.2.0.4 but BELOW 12.2
Description
This bug is only relevant when using Parallel Query (PQO)
With fix of bug 13097308 cost of parallel scan can become unreasonably high leading to sub-optimal plan.
Rediscovery Notes
If parallel queries run slowly due to plan changes, avoiding full table scans
and/or index fast full scans (index_ffs), workload system stats have been gathered,
MAXTHR and/or SLAVETHR are small when measured in bytes/sec, and setting
OFE=11.2.0.3 or earlier does not fix the issue, then you may be affected by this bug.
Workaround
Increase system stats value of SREADTIM
Bug 17908541 CBO does not consider INDEX_FFS for DELETE
Versions confirmed as being affected
11.2.0.4
11.2.0.3
Description
DELETE (on a non-IOT) does not choose to execute with index fast full scan
unless hinted.
Rediscovery Notes
If a DELETE gets suboptimal performance because it is not executing with an
index fast full scan but using a hint does enable this access path then you
may have encountered this bug.
Bug 13097308 MAXTHR and SLAVETHR System Statistics Not Working
the fix for 13097308 is first included in
12.1.0.1 (Base Release)
12.1.0.1 Release
11.2.0.4 (Server Patch Set)
Description
The optimizer does not correctly account for the system statistics
MAXTHR and SLAVETHR when evaluating parallel queries, using
values 1000x too high and effectively ignoring the limits.
Rediscovery Notes
Queries have poor performance due to choosing a parallel table access
over an index access due to a low I/O cost.
Workaround
Manually set MAXTHR and SLAVETHR to 1/1000th the value.
Bug 8926483 Running full table scan on two node RAC does "db file sequential read" - slow performance
affected Versions >= 10.2.0.4 but BELOW 11.1
Description
This bug is only relevant when using Real Application Clusters (RAC)
In a RAC environment Multi-block read may get converted to single-block
reads when the cursor gets invalidated.
Rediscovery Notes
- High disk read for queries.
- dumps using the events 10078 and 10046 shows:
Optimizer Chooses Expensive Index Full Scan over Index Fast Full Scan or Full Table Scan from 12.1 (Doc ID 2382922.1)
Unpublished Bug 24513980 : OPTIMIZER SHOULD IGNORE SYSTEM STATS WHEN MREADTIM > SREADTIM*MBRC
The above bug is closed as duplicate of below:
Unpublished Bug 27643128 : SQL NOT PICKING INDEX FAST FULL SCAN IN 12.1.0.2 - fixed in 19.1
Problem is due to MREADTIM system statistics value which is not correct leading to the optimizer choosing bad plans using single block IO(IFS) over good plans using
multi-block IO(IFFS/FTS).
Query Using Full Table Scan Over Index Scan After Upgrade to 12c (Doc ID 2362906.1)
Bug 24758816 - Query Slower after Upgrade to 12c Favoring Full Table Scan (FTS) instead of Index Scan (Doc ID 24758816.8)
Bug 27392876 Fusionapps: SQL_ID 70k3h0pgsn62j Shows Regression with Fix for Bug 24758816 Enabled
Scalar subquery unnesting taking place, but CBQT JPPD is not tried due
to the fact that query is not eligible for CBQT.
note:
可见错误的使用FTS,IFS在10g,11g,12-18c都有相关的bug. 我们当前11g r2匹配的范围的是17863980和17908541.
19c中复现问题
SQL> show parameter feature
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ --------------------------------- ----------------------------------------------
disable_pdb_feature big integer 0
optimizer_features_enable string 19.1.0.1
SQL> @fix "FFS"
SESSION_ID BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATU EVENT IS_DEFAULT CON_ID
---------- ---------- ---------- ----------------------------------- ---------------------------------------------------------------------- --------------- ---------- ---------- ----------
260 4483286 0 QKSFM_CBO_4483286 Discount FFS cost using optimizer_index_cost_adj. 38085 1 0
260 7155968 1 QKSFM_ACCESS_PATH_7155968 Restrict use of disabled FFS on IOTs 11.1.0.7 0 1 0
260 19563657 1 QKSFM_ACCESS_PATH_19563657 no IFFS on SELECT FOR UPDATE, add table access cost to index sca 12.2.0.1 0 1 0
260 25995431 1 QKSFM_INDEX_FFS_25995431 adjust leaf blocks for index fast full scan 18.1.0 0 1 0
260 27643128 1 QKSFM_CBO_27643128 put system stats sanity check to correct IFFS cost computation 19.1.0 0 1 0
SQL> @fix "fast full scan"
SESSION_ID BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATU EVENT IS_DEFAULT CON_ID
---------- ---------- ---------- ----------------------------------- ---------------------------------------------------------------------- --------------- ---------- ---------- ----------
260 6980350 1 QKSFM_CBO_6980350 fix fast full scan index only determination 11.2.0.1 0 1 0
260 17908541 1 QKSFM_ACCESS_PATH_17908541 consider index fast full scan on DELETE to non-IOT 12.1.0.2 0 1 0
260 25995431 1 QKSFM_INDEX_FFS_25995431 adjust leaf blocks for index fast full scan 18.1.0 0 1 0
SQL> alter session set "_fix_control"="17908541:off";
Session altered.
SQL> explain plan for delete test2 where exists(select 1 from test1 where test1.id=test2.id);
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2547202327
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 10000 | 175K| 231 (1)| 00:00:01 |
| 1 | DELETE | TEST2 | | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 10000 | 175K| 231 (1)| 00:00:01 |
| 3 | VIEW | VW_SQ_1 | 10000 | 126K| 7 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX_TEST1_ID | 10000 | 40000 | 7 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | IDX_TEST2_ID | 100K| 488K| 224 (1)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="TEST2"."ID")
Note:
从高版本复现低版本的问题，我们可以更改optimizer_features_enable到低版本，但并不一定总是好使，如果复现，可以对比两个优化器版本的参数和bug fixed差异，这里关闭17908541 bug fixed复现了问题，说明这个案例匹配的是这个bug. 然后就可以安装oneoff patch解决该问题，或使用index_ffs解决。
Summary:
这个案例因为SQL运行时间较长，sql monitor显示错误，引起我们的关注，发现SQL花费大量的时间在db file sequential read , 对应的执行计划为index full scan（IFS), 并不是最佳的执行计划，使用index_ffs确认也可以使用index fast full scan（IFFS），并且cost也更低，确认了参数和系统统计信息并没有错误配置，和19c对比确认存在bug， 最后明确了为bug 17908541.
— 脚本1
DECLARE
v_owner_name VARCHAR2 (30) := 'SYS'; -- Schema
v_table_name VARCHAR2 (30) := 'TEST2'; -- Table name
v_cost_model VARCHAR2 (30);
v_block_size NUMBER; -- db_block_size
v_dbforc NUMBER; --_db_file_optimizer_read_count
v_dbfmbrc NUMBER; -- db_file_multiblock_read_count
v_blocks NUMBER;
v_cost NUMBER;
v_io_cost NUMBER; -- from explain plan
v_#MRDs NUMBER; -- number of multi block reads
v_sreadtim NUMBER; -- single block read time
v_mreadtim NUMBER; -- multi block read time
v_msg VARCHAR2 (2000);
TYPE typ_sys_stat_tab IS TABLE OF NUMBER
INDEX BY VARCHAR2 (30);
v_sys_stat typ_sys_stat_tab;
BEGIN
-- db_block_size: 8192 (default)
SELECT VALUE
INTO v_block_size
FROM v$parameter
WHERE name = 'db_block_size';
-- Session Value of _db_file_optimizer_read_count: 8 (default)
SELECT b.ksppstvl "Session Value"
INTO v_dbforc
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm = '_db_file_optimizer_read_count';
SELECT b.ksppstvl "Session Value"
INTO v_dbfmbrc
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm = 'db_file_multiblock_read_count';
-- table block# used
SELECT blocks
INTO v_blocks
FROM all_tables
WHERE OWNER = v_owner_name AND table_name = v_table_name;
-- get system statistics parameters
FOR r1 IN (SELECT pname, pval1
FROM sys.aux_stats$)
LOOP
v_sys_stat (r1.pname) := r1.pval1;
END LOOP;
/*
_OPTIMIZER_COST_MODEL (hidden) parameter:
'CHOOSE' - default: chooses CPU costing based on the presence of statistics in aux_stats$,
'CPU' - forces CPU costing based on statistics in aux_stats$,
'IO' - forces IO costing
*/
SELECT b.ksppstvl "Session Value"
INTO v_cost_model
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm = '_optimizer_cost_model';
v_msg := 'Full Table Scan: ' || v_owner_name || '.' || v_table_name;
v_msg := v_msg || CHR (10) || ' number of used table blocks: ' || v_blocks;
v_msg := v_msg || CHR (10) || ' db_block_size: ' || v_block_size;
v_msg := v_msg || CHR (10) || ' _optimizer_cost_model: ' || v_cost_model;
v_msg := v_msg || CHR (10) || ' _db_file_optimizer_read_count (session value): ' || v_dbforc;
v_msg := v_msg || CHR (10) || ' db_file_multiblock_read_count (session value): ' || v_dbfmbrc;
IF v_cost_model = 'IO'
THEN
v_cost := CEIL (v_blocks * 0.5965 / POWER (v_dbforc, 0.6582));
v_msg :=
v_msg
|| CHR (10)
|| ' Formaula: io_cost = ceil(blocks * 0.5965/ _db_file_optimizer_read_count ^ 0.6582'
|| CHR (10)
|| ' _db_file_optimizer_read_count: '
|| v_dbforc;
ELSE -- 'CHOOSE' or 'CPU'
IF v_sys_stat ('FLAGS') = 0 OR v_sys_stat ('MBRC') IS NULL
THEN
IF v_sys_stat ('FLAGS') = 0
THEN
v_msg := v_msg || CHR (10) || 'System Statistics type: Default NOWORKLOAD';
ELSE
v_msg := v_msg || CHR (10) || 'System Statistics type: Gathered NOWORKLOAD';
END IF;
v_msg := v_msg || CHR (10) || ' IOSEEKTIM : ' || v_sys_stat ('IOSEEKTIM');
v_msg := v_msg || CHR (10) || ' IOTFRSPEED: ' || v_sys_stat ('IOTFRSPEED');
v_sreadtim := v_sys_stat ('IOSEEKTIM') + v_block_size / v_sys_stat ('IOTFRSPEED');
v_msg := v_msg || CHR (10) || 'SREADTIM = IOSEEKTIM + db_block_size / IOTFRSPEED';
v_msg := v_msg || CHR (10) || ' single-block I/O read time: ' || v_sreadtim;
v_mreadtim := v_sys_stat ('IOSEEKTIM') + v_dbforc * v_block_size / v_sys_stat ('IOTFRSPEED');
v_msg := v_msg || CHR (10) || 'MREADTIM = IOSEEKTIM + mbrc * db_block_size / IOTFRSPEED';
v_msg := v_msg || CHR (10) || ' multi-block I/O read time: ' || v_mreadtim;
v_#MRDs := v_blocks / v_dbforc;
v_msg := v_msg || CHR (10) || '#MRDs = table block# used / _db_file_optimizer_read_count';
v_msg := v_msg || CHR (10) || ' #MRDs: ' || v_#MRDs;
v_cost := GREATEST (1, CEIL (v_#MRds * v_mreadtim / v_sreadtim));
ELSE
v_msg := v_msg || CHR (10) || 'System Statistics type: Gathered WORKLOAD';
v_msg := v_msg || CHR (10) || ' use SYS.AUX_STATS$.MBRC to calculate #MRDs';
v_msg := v_msg || CHR (10) || ' SREADTIM : ' || v_sys_stat ('SREADTIM');
v_msg := v_msg || CHR (10) || ' MREADTIM : ' || v_sys_stat ('MREADTIM');
v_msg := v_msg || CHR (10) || ' MBRC : ' || v_sys_stat ('MBRC');
v_#MRDs := v_blocks / v_sys_stat ('MBRC');
v_msg := v_msg || CHR (10) || '#MRDs = table block# used / MBRC';
v_msg := v_msg || CHR (10) || ' #MRDs: ' || v_#MRDs;
v_cost := GREATEST (1, CEIL (v_#MRds * v_sys_stat ('MREADTIM') / v_sys_stat ('SREADTIM')));
END IF;
DBMS_OUTPUT.put_line (v_msg);
DBMS_OUTPUT.put_line ('-----------------');
END IF;
DBMS_OUTPUT.PUT_LINE ('io_cost = MAX(1, CEIL(blocks / MBRC * MREADTIM / SREADTIM))');
DBMS_OUTPUT.put_line ('COST calculated: ' || v_cost);
DBMS_OUTPUT.put_line ('-----------------');
-- EXPLAIN PLAN
--EXECUTE IMMEDIATE 'ALTER SESSION SET "_table_scan_cost_plus_one" = FALSE';
-- EXECUTE IMMEDIATE 'alter session set db_file_multiblock_read_count=' || v_mbrc;
EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR SELECT * FROM ' || v_owner_name || '.' || v_table_name;
SELECT IO_COST
INTO v_io_cost
FROM plan_table
WHERE operation = 'TABLE ACCESS'
AND options = 'FULL'
AND object_name = UPPER (v_table_name)
AND plan_id = (SELECT MAX (plan_id) FROM plan_table);
DBMS_OUTPUT.put_line ('COST from explain plan: ' || v_io_cost);
END;
Full Table Scan: SYS.TEST2
number of used table blocks: 100000
db_block_size: 8192
_optimizer_cost_model: CHOOSE
_db_file_optimizer_read_count (session value): 8
db_file_multiblock_read_count (session value): 8
System Statistics type: Gathered NOWORKLOAD
IOSEEKTIM : 10
IOTFRSPEED: 4096
SREADTIM = IOSEEKTIM + db_block_size / IOTFRSPEED
single-block I/O read time: 12
MREADTIM = IOSEEKTIM + mbrc * db_block_size / IOTFRSPEED
multi-block I/O read time: 26
#MRDs = table block# used / _db_file_optimizer_read_count
#MRDs: 12500
-----------------
io_cost = MAX(1, CEIL(blocks / MBRC * MREADTIM / SREADTIM))
COST calculated: 27084
-----------------
COST from explain plan: 27085
微信扫一扫，打赏作者吧～
目前这篇文章还没有评论(Rss)