Andrew Max
Member
Откуда:
Сообщений: 1034
|
Наткнулся на простую ситуацию с секционированными таблицами, в которой оптимизатор Oracle 9i ведет себя, на мой взгляд, просто невменяемо.
Тестовый пример и пояснения – ниже.
SQL> select * from v$version where rownum = 1;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
SQL> create table part
2 (x number,
3 key_col number,
4 padding varchar2(500))
5 partition by list(x)
6 (
7 partition p_0 values(0),
8 partition p_1 values(1),
9 partition p_2 values(2),
10 partition p_3 values(3),
11 partition p_4 values(4),
12 partition p_5 values(5),
13 partition p_6 values(6),
14 partition p_7 values(7),
15 partition p_8 values(8),
16 partition p_9 values(9));
Table created.
SQL> insert into part
2 select mod(rownum, 10),
3 rownum,
4 rpad('*', 500, '*')
5 from dual
6 connect by level <= 100000;
100000 rows created.
SQL> create unique index idx_part on part(x, key_col) local;
Index created.
SQL> alter table part add constraint idx_part primary key(x, key_col);
Table altered.
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname => user,
4 tabname => 'PART',
5 estimate_percent => null,
6 method_opt => 'FOR ALL COLUMNS SIZE AUTO',
7 granularity => 'ALL',
8 cascade => true);
9 end;
10 /
PL/SQL procedure successfully completed.
Итак, мы создали секционированную таблицу с 10 секциями по 10000 строк каждая. Кроме того, мы создали уникальный локальный индекс с префиксом.
Посмотрим на план следующего простого запроса:
SQL> explain plan for
2 select * from part
3 where x = :bx;
Explained.
SQL> @utlxpls
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 4960K| 802 (3)| | |
| 1 | PARTITION LIST SINGLE | | | | | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PART | 10000 | 4960K| 802 (3)| KEY | KEY |
|* 3 | INDEX RANGE SCAN | IDX_PART | 10000 | | 32 (19)| KEY | KEY |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PART"."X"=TO_NUMBER(:Z))
14 rows selected.
Немедленно возникает следующий вопрос: какой смысл сканировать целую секцию индекса, если единственный access-предикат здесь – это access("PART"."X"=TO_NUMBER(:Z))? Ведь совершенно ясно, что при этом будут обработаны все листовые блоки этой секции индекса, что, по идее, должно быть намного дороже обычного сканирования одной секции таблицы.
Однако, вот план запроса, в котором мы вынуждаем оптимизатор не использовать INDEX access path:
SQL> explain plan for
2 select /*+ NO_INDEX(PART) */ * from part
3 where x = :bx;
Explained.
SQL> @utlxpls
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 4960K| 3048 (7)| | |
| 1 | PARTITION LIST SINGLE| | | | | KEY | KEY |
|* 2 | TABLE ACCESS FULL | PART | 10000 | 4960K| 3048 (7)| KEY | KEY |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PART"."X"=TO_NUMBER(:Z))
13 rows selected.
Значение COST в этом плане весьма настораживает: 3048 – не слишком ли дорого для сканирования единственной секции? Чтобы разобраться в деталях, лучше включить трассировку с event 10053. CPU costing для упрощения последующих расчетов я отключил, эта компонента стоимости все равно не оказывает решающего влияния на суть проблемы.
SQL> alter session set tracefile_identifier = partcost;
Session altered.
SQL> alter session set "_optimizer_cost_model" = IO;
Session altered.
SQL> alter session set events='10053 trace name context forever, level 1';
Session altered.
SQL> explain plan for
2 select * from part
3 where x = :bx;
Explained.
SQL> alter session set events '10053 trace name context off';
Session altered.
Вот секция из trace-файла, которая нас интересует:
***************************************
SINGLE TABLE ACCESS PATH
Column: X Col#: 1 Table: PART Alias: PART
NDV: 10 NULLS: 0 DENS: 1.0000e-001 LO: 0 HI: 9
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: PART ORIG CDN: 100000 ROUNDED CDN: 10000 CMPTD CDN: 10000
Access path: tsc Resc: 2852 Resp: 2852
Skip scan: ss-sel 0 andv 10000
ss cost 10000
index io scan cost 0
Access path: index (scan)
Index: IDX_PART
TABLE: PART
RSC_CPU: 0 RSC_IO: 779
IX_SEL: 1.0000e-001 TB_SEL: 1.0000e-001
BEST_CST: 779.00 PATH: 4 Degree: 1
***************************************
Для того, чтобы повторить расчет COST вслед за Oracle, нам потребуются следующие статистические данные:
SQL> select pname, pval1, pval2 from sys.aux_stats$;
PNAME PVAL1 PVAL2
------------------------------ ---------- -----------------
STATUS COMPLETED
DSTART 12-24-2004 19:22
DSTOP 12-24-2004 20:26
FLAGS 1
SREADTIM ,556
MREADTIM 2,754
CPUSPEED 771
MBRC 13
MAXTHR 49090560
SLAVETHR -1
10 строк выбрано.
SQL> select blocks from user_tables where table_name = 'PART';
BLOCKS
----------
7480
SQL> select blevel, leaf_blocks, clustering_factor
2 from user_indexes
3 where index_name = 'IDX_PART';
BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
1 249 7530
Для расчет стоимости используем следующую известную формулу:
Cost = (#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed ) / sreadtim
В случае доступа по индексу все чтения – одноблочные, т.е:
Cost_IRS = #SRds
Далее, согласно известной формуле, получаем:
Cost_IRS = blevel + ceil(IX_SEL * leaf_blks) + ceil(TB_SEL * clu_f) =
1 + ceil(0.1 * 249) + (0.1 * 7530) = 779
Для случая простого сканирования секции формула для расчета cost приобретает вид:
Cost_FTS = #MRds * mreadtim / sreadtim
Оказывается, в этом случае при расчете количества многоблочных чтений Oracle почему-то предполагает, что будут прочитаны все блоки всех секций таблицы. Тот факт, что во время выполнения запрос выберет данные максимум одной партиции, попросту игнорируется. Предлагаю убедиться:
Cost_FTS = ceil((7480 / 13) * 2.754 / 0.556) = 2851.
С учетом значения параметра _TABLE_SCAN_COST_PLUS_ONE = TRUE, получим Cost_FTS = 2852.
Мне известно, что используя bind variable peeking, оптимизатор девятки может подсмотреть значение :bx и построить хороший план выполнения. Тем не менее, как быть в ситуации, когда bind variable peeking ничем не поможет? Например, если вместо :bx используется SYS_CONTEXT?
Для данного конкретного примера последствия выбора неправильного плана не столь критичны:
SQL> set autot traceonly stat
SQL>
SQL> select * from part
2 where x = (select 0 from dual);
10000 строк выбрано.
Статистика
----------------------------------------------------------
0 recursive calls
0 db block gets
2066 consistent gets
...
10000 rows processed
SQL> select /*+ NO_INDEX(PART) */ * from part
2 where x = (select 0 from dual);
10000 строк выбрано.
Статистика
----------------------------------------------------------
0 recursive calls
0 db block gets
1375 consistent gets
...
10000 rows processed
Однако в других случаях, при ином распределении данных дела могут обстоять гораздо хуже.
Справедливости ради, нужно отметить, что десятка ведет себя гораздо адекватнее, но это слабо утешает: проблему хотелось бы как-то решить именно для девятой версии.
У кого-либо есть соображения или материал для чтения по этому поводу?
Заранее признателен. |
О дружбе CBO с partitioned tables [new]
Re: О дружбе CBO с partitioned tables [new]
Re: О дружбе CBO с partitioned tables [new]
.
Re: О дружбе CBO с partitioned tables [new]