Владимор Конев
Member
Откуда:
Сообщений: 3456
|
Долго я уже бьюсь с СВО, но так и не получилось вправить ему мозги...
Тут некоторое время назад я уже поднимал подобную тему, но тогда всё уперлось в отсутствие конкретного примера. Сегодня я приведу пример таблицы и того, как работает СВО и как работает RBO. Таблица из примера - сильно уменьшенная и идеализированная модель рабочих таблиц. Но суть от этого не меняется - СВО выбирает неверные планы в обоих случаях, хоть на рабочих таблицах, хоть на тестовой.
И так, поехали...
Вот скрипт на создание тестовой таблицы, на заполнение её данными, на построение индексов и по сбору статистики по этой таблице:
| Скрипт на создание таблицы и всякой лабуды к ней |
--
--
-- Если в текущей схеме существует таблица
-- TEST_TABLE, то удаляем эту таблицу
--
prompt If exists table TEST_TABLE, then drop it
--
begin
for i in (select 'drop table '||table_name sql_str
from user_tables
where upper(table_name) = 'TEST_TABLE')
loop
if i.sql_str is not null
then execute immediate i.sql_str;
else null;
end if;
end loop;
end;
/
--
--
-- Создаем в текущей схеме таблицу TEST_TABLE
--
prompt Creating table TEST_TABLE in current schema
--
create table test_table
as
select trunc((rownum-1)/4000)+1 num_1,
trunc((rownum-1)/400)+1 num_2,
trunc((rownum-1)/40)+1 num_3,
mod(rownum-1, 10)+1 num_4
from (select 1
from dba_objects
union all
select 1
from dba_objects
union all
select 1
from dba_objects
union all
select 1
from dba_objects
union all
select 1
from dba_objects
union all
select 1
from dba_objects
union all
select 1
from dba_objects
)
where rownum <= 200000
/
--
--
-- Создаем три индекса по таблице TEST_TABLE
--
prompt Create 3 indexes on TEST_TABLE
--
create index test_table_num_1 on test_table (num_1 asc)
/
create index test_table_num_2 on test_table (num_2 asc)
/
create index test_table_num_3 on test_table (num_3 asc)
/
--
--
-- Собираем статистику для таблицы TEST_TABLE и её индексов
--
prompt Compute statistics for TEST_TABLE
analyze table test_table compute statistics for table for all indexes for all indexed columns
/
|
После того как этот скрипт отработал, делаю в SQLPLUS следующее:
| Анализ плана в SQLPLUS при помощи утилиты AUTOTRACE |
test@test> set autotrace traceonly
--------------------------------------------------------------------------------
/*
Выполняем несложный запрос к таблице TEST_TABLE в режиме СВО
*/
--------------------------------------------------------------------------------
test@test> select *
2 from test_table
3 where num_1 = 40
4 and num_2 = 391
5 and num_3 = 3901;
40 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_TABLE' (Cost=2 Card=1 Bytes=17)
2 1 INDEX (RANGE SCAN) OF 'TEST_TABLE_NUM_1' (NON-UNIQUE) (Cost=9 Card=4000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
1154 bytes sent via SQL*Net to client
521 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40 rows processed
--------------------------------------------------------------------------------
/*
А теперь тот же самый запрос, но в режиме RBO
*/
--------------------------------------------------------------------------------
test@test> select /*+ rule*/ *
2 from test_table
3 where num_1 = 40
4 and num_2 = 391
5 and num_3 = 3901;
40 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_TABLE'
2 1 AND-EQUAL
3 2 INDEX (RANGE SCAN) OF 'TEST_TABLE_NUM_1' (NON-UNIQUE)
4 2 INDEX (RANGE SCAN) OF 'TEST_TABLE_NUM_2' (NON-UNIQUE)
5 2 INDEX (RANGE SCAN) OF 'TEST_TABLE_NUM_3' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
1154 bytes sent via SQL*Net to client
521 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40 rows processed
test@test> spool off
|
И несложно заметить, что даже на столь простом искусственном примере с небольшим набором данных (всего-то 200 000 записей) СВО строит не самый оптимальный план. В данном примере статистика выполнения запроса отличаеися только параметром consistent gets. На реальных таблицах работа CBO ещё хуже. :-(
Собственно вопрос, куда рыть?
Статистику уже как только не собирал, и при помощи пакета DBMS_STAT, и при помощи оператора ANALYZE - эффекта никакго. Более того, было замечено, что при сборе статистики пакетом DBMS_STAT СВО вообще никогда не использует AND_EQUAL и в любом случае к таблице доступается по одному из индексов. Если же собрать статистику оператором ANALYZE, то СВО использует AND_EQUAL, но эффективность запросов всё-равно хуже, чем в режиме RBO (в этом случае СВО выбирает всего два индекса, в то время как RBO - три. Как следствие - возрастает число physical reads и consistent gets и возрастает время выполнения запроса.)
З.Ы. Сервер ORACLE 9.2.0.4 на Win2K Advanced Server
CPU 4 х 2,8 Pentium XEON with HT
RAM 8 GGb
Дисковая подсистема RAID-10 on 10 x 140GGB HDD
Параметры инициализации :
| Содержимое SPFILE |
background_dump_dest='c:\oracle\admin\TEST\bdump'
compatible='9.2.0.0.0'
control_files='c:\oradata\test\control01.ctl','d:\oradata\test\control02.ctl','d:\oradata\test\control03.ctl'
core_dump_dest='c:\oracle\admin\test\cdump'
pga_aggregate_target=536870912
workarea_size_policy=AUTO
db_block_size=8192
db_cache_size=1073741824
db_file_multiblock_read_count=16
db_name='TEST'
db_writer_processes=2
dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'
fast_start_mttr_target=300
hash_join_enabled=TRUE
instance_name='TEST'
java_pool_size=33554432
large_pool_size=67108864
open_cursors=300
optimizer_index_caching=90
optimizer_index_cost_adj=1
parallel_max_servers=16
processes=150
query_rewrite_enabled='FALSE'
remote_login_passwordfile='EXCLUSIVE'
sga_max_size=1476395008
shared_pool_size=268435456
star_transformation_enabled='FALSE'
timed_statistics=TRUE
undo_management='AUTO'
undo_retention=10800
undo_tablespace='UNDOTBS1'
user_dump_dest='c:\oracle\admin\TEST\udump'
|
|
У меня ошибается СВО !!! [new]