И вот теперь случилось применить ее в одном интересном случае. Вот такой план имел место быть на промышленной площадке.
Сразу же бросается в глаза серъезное расхождение между прогнозом оптимизатора и статистикой времени исполнения. Первая стадия анализа привела к фрагменту плана, в котором, как мне кажется, находится ключевая ошибка оптимизатора в оценке кардинальности:
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows |
-------------------------------------------------------------------------------------------------------------------------------------------------------
...
|* 17 | HASH JOIN ANTI | | 1 | 1 | 54 | | 7 (15)| 00:00:01 | 18 |
| 18 | NESTED LOOPS | | 1 | 1 | 42 | | 2 (0)| 00:00:01 | 21 |
|* 19 | TABLE ACCESS BY USER ROWID | IKIS_USERS_ATTR | 1 | 1 | 25 | | 1 (0)| 00:00:01 | 1 |
|* 20 | INDEX FULL SCAN | PK_IKIS_ROLE | 1 | 1 | 17 | | 1 (0)| 00:00:01 | 21 |
| 21 | VIEW | VW_NSO_3 | 1 | 12 | 144 | | 4 (0)| 00:00:01 | 3 |
| 22 | NESTED LOOPS | | 1 | 12 | 600 | | 4 (0)| 00:00:01 | 3 |
|* 23 | TABLE ACCESS FULL | IKIS_RESOURCE | 1 | 9 | 162 | | 3 (0)| 00:00:01 | 4 |
|* 24 | INDEX RANGE SCAN | PK_RSRC2ROLE | 4 | 1 | 32 | | 1 (0)| 00:00:01 | 3 |
...
все остальное лавинообразно преумножало ошибку, сделанную на 20 шаге плана.
Интересный предикат был сгенерен для этого 20 шага:
20 - filter(("IKIS_SUBSYS_UTIL"."GETINSTANCEPREF"()||"RS"."IRL_NAME"<>'PUBLIC' AND "IKIS_SUBSYS_UTIL"."GETINSTANCEPREF"()||"RS"."IRL_NAME"<>'_NEXT_USER'))
Собственно, данный предикат сгенерен на основе where clause представления dba_roles, которое участвует в данном запросе.
Далее простой test case, имитирующий данную ситуацию:
drop table r1;
drop table r2;
create table r1 as select rownum id,x.* from all_objects x where rownum<=1000;
update r1 set object_name='SSDR' where mod(id,276)=0;
update r1 set object_name='GSDR' where mod(id,876)=0;
create table r2 as select * from r1 where mod(object_id,2)=0;
update r1 set object_name='XXX'||substr(object_name,1,25);
update r2 set object_name=substr(object_name,1,25);
commit;
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'r1')
exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'r2')
create or replace view v1
as
select * from r1
where object_name<>'XXXSSDR' and object_name<>'XXXGSDR' and object_type='INDEX';
explain plan for
select count(1)
from v1,r2
where v1.object_name='XXX'||r2.object_name;
и ожидаемый, аналогичный полученному на пром-системе, результат с ошибкой оценки кардинальности для таблицы R2:
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 9 (12)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 40 | | |
|* 2 | HASH JOIN | | 1 | 40 | 9 (12)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| R2 | 1 | 15 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| R1 | 111 | 2775 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='XXX'||"R2"."OBJECT_NAME")
3 - filter('XXX'||"R2"."OBJECT_NAME"<>'XXXSSDR' AND
'XXX'||"R2"."OBJECT_NAME"<>'XXXGSDR')
4 - filter("OBJECT_TYPE"='INDEX' AND "OBJECT_NAME"<>'XXXSSDR' AND
"OBJECT_NAME"<>'XXXGSDR')
Очевидно, что ситуация в следующем: оптимизатор генерит предикат и крайне неверно оценивает его селективность.
Собственно workaround:
1) каким-либо образом заставить оптимизатор не генерить предикат, для этого можно использовать хинт NO_MERGE или NO_QUERY_TRANSFORMATION. В таком случае план будет имеет вид:
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 9 (12)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 32 | | |
|* 2 | HASH JOIN | | 57 | 1824 | 9 (12)| 00:00:01 |
| 3 | VIEW | V1 | 111 | 1887 | 5 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| R1 | 111 | 2775 | 5 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | R2 | 501 | 7515 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("V1"."OBJECT_NAME"='XXX'||"R2"."OBJECT_NAME")
4 - filter("OBJECT_TYPE"='INDEX' AND "OBJECT_NAME"<>'XXXSSDR' AND
"OBJECT_NAME"<>'XXXGSDR')
План исходного запроса с хинтом NO_QUERY_TRANSFORMATION тяжеловат, но по времени исполнения все таки существенно лучше.
2) пока искал другие пути обнаружил, что это явление - ошибка в коде оптимизатора.
Проверял следующие версии 9.2.0.4 SE Linux, 10.2.0.1 SE/EE Linux,
10.2.0.1 SE Windows, 11.1.0.6 EE Windows.
Этот эффект проявился исключиетльно на 10.2.0.1 SE Windows (такая же версия была и в продукционной системе, где собственно проблема и была обнаружена).
No comments:
Post a Comment