Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 отцы, понедельничная задача про ошибку CBO. [10053]  [new]
бобищо
Member [заблокирован]

Откуда:
Сообщений: 245
есть 2 таблицы
ocr_bilpaymentcata, мастер, 4 млн строк
ocr_bilpaymentcatapaysys, детали, 16 млн строк

в мастере есть PK индекс, в деталях FK индекс

есть запрос
select count(*)
  from ush.ocr_bilpaymentcatapaysys p,
       ush.ocr_bilpaymentcata       e
 where e.id_bilpaymentcata = p.id_bilpaymentcata

говорит что оптимальный план такой:

============
Plan Table
============
------------------------------------------------------------+-----------------------------------+
| Id  | Operation               | Name                      | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT        |                           |       |       |   27K |           |
| 1   |  SORT AGGREGATE         |                           |     1 |    12 |       |           |
| 2   |   NESTED LOOPS          |                           |   14M |  172M |   27K |  00:06:36 |
| 3   |    INDEX FAST FULL SCAN | FK_BILPS_ID_BILPAYMENTCATA|   16M |   96M |  8924 |  00:02:48 |
| 4   |    INDEX UNIQUE SCAN    | PK_ID_BILPAYMENTCATA      |     1 |     6 |     1 |  00:00:01 |
------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("E"."ID_BILPAYMENTCATA"="P"."ID_BILPAYMENTCATA")

очевидно, что такой план даёт кучу чтений, на много порядков больше, чем план

SQL> explain plan for select /*+ use_hash(p e) */
  2   count(*)
  3    from ush.ocr_bilpaymentcatapaysys p,
  4         ush.ocr_bilpaymentcata       e
  5   where e.id_bilpaymentcata = p.id_bilpaymentcata
  6  ;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
--------------------
Plan hash value: 3459366041

-------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                            |     1 |    12 |       | 29463   (2)| 00:05:54 |
|   1 |  SORT AGGREGATE        |                            |     1 |    12 |       |            |       |
|*  2 |   HASH JOIN            |                            |    15M|   172M|    77M| 29463   (2)| 00:05:54 |
|   3 |    INDEX FAST FULL SCAN| PK_ID_BILPAYMENTCATA       |  4492K|    25M|       |  2255   (2)| 00:00:28 |
|   4 |    INDEX FAST FULL SCAN| FK_BILPS_ID_BILPAYMENTCATA |    16M|    95M|       |  8924   (2)| 00:01:48 |
-------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
--------------------

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

   2 - access("E"."ID_BILPAYMENTCATA"="P"."ID_BILPAYMENTCATA")

 

оптимизатор считает что в первом случае план дешевле - почему?
у меня единственное подозрение на ошибочный расчет плотности столбца ID_BILPAYMENTCATA в таблице ush.ocr_bilpaymentcata.
То есть оптимизатор считает, что по индексу PK_ID_BILPAYMENTCATA он будет ходить очень мало раз.


есть мнения?

К сообщению приложен файл (q.txt - 49Kb) cкачать
1 мар 10, 11:56    [8410648]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
hell
Member

Откуда:
Сообщений: 3002
В первом случае надо дольше ходить по uniq index, зато во втором надо больше бегать по таблице с hash, чтобы ловить соответствия.

Думаю, оракель оценил, что дешевле будет побегать одноблочным чтением, возможно это последствия системной статистики для многоблочного/одноблочного чтения.

Файлег не смотрел :-)

__________________
For more information, please proceed to http://ot-e.biz
1 мар 10, 12:09    [8410773]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 28720
бобищо
в мастере есть PK индекс, в деталях FK индекс
Если там действительно FK, то идельный план должен бы стремиться к приблизительно такому:
------------------------------------------------------------+
| 0   | SELECT STATEMENT        |                           |
| 1   |  SORT AGGREGATE         |                           |
| 2   |    INDEX FAST FULL SCAN | FK_BILPS_ID_BILPAYMENTCATA|
------------------------------------------------------------+
1 мар 10, 12:14    [8410803]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
AlexFF__|
Member

Откуда:
Сообщений: 2815
бобищо

очевидно, что такой план даёт кучу чтений, на много порядков больше, чем план


где очевидно?
1 мар 10, 12:17    [8410831]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
бобищо
Member [заблокирован]

Откуда:
Сообщений: 245
Elic
бобищо
в мастере есть PK индекс, в деталях FK индекс
Если там действительно FK, то идельный план должен бы стремиться к приблизительно такому:
------------------------------------------------------------+
| 0   | SELECT STATEMENT        |                           |
| 1   |  SORT AGGREGATE         |                           |
| 2   |    INDEX FAST FULL SCAN | FK_BILPS_ID_BILPAYMENTCATA|
------------------------------------------------------------+


Виталий, вы правы. constrainta не существует, только индекс.
1 мар 10, 12:28    [8410916]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
бобищо
Member [заблокирован]

Откуда:
Сообщений: 245
AlexFF__|
бобищо

очевидно, что такой план даёт кучу чтений, на много порядков больше, чем план


где очевидно?


из плана :-)
и autotrace
1 мар 10, 12:28    [8410920]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
AlexFF__|
Member

Откуда:
Сообщений: 2815
бобищо
AlexFF__|
бобищо

очевидно, что такой план даёт кучу чтений, на много порядков больше, чем план


где очевидно?


из плана :-)
и autotrace


Пальцем можно ткнуть?
1 мар 10, 12:32    [8410965]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
бобищо
Member [заблокирован]

Откуда:
Сообщений: 245
AlexFF__|
бобищо
AlexFF__|
бобищо

очевидно, что такой план даёт кучу чтений, на много порядков больше, чем план


где очевидно?


из плана :-)
и autotrace


Пальцем можно ткнуть?


конечно, ща сделаем
1 мар 10, 12:33    [8410977]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
бобищо
Member [заблокирован]

Откуда:
Сообщений: 245
AlexFF__|
бобищо
AlexFF__|
бобищо

очевидно, что такой план даёт кучу чтений, на много порядков больше, чем план


где очевидно?


из плана :-)
и autotrace


Пальцем можно ткнуть?


SQL> set  autot on
SQL>
SQL> select
  2   count(*)
  3    from ush.ocr_bilpaymentcatapaysys p,
  4         ush.ocr_bilpaymentcata       e
  5   where e.id_bilpaymentcata = p.id_bilpaymentcata;
  17240057
Elapsed: 00:00:46.54

Execution Plan
----------------------------------------------------------
Plan hash value: 3723901653

-----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                            |     1 |    12 |  4259  (81)| 00:00:25 |
|   1 |  SORT AGGREGATE        |                            |     1 |    12 |            |          |
|   2 |   NESTED LOOPS         |                            |    15M|   179M|  4259  (81)| 00:00:25 |
|   3 |    INDEX FAST FULL SCAN| FK_BILPS_ID_BILPAYMENTCATA |    16M|    95M|  1057  (21)| 00:00:07 |
|*  4 |    INDEX UNIQUE SCAN   | PK_ID_BILPAYMENTCATA       |     1 |     6 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   4 - access("E"."ID_BILPAYMENTCATA"="P"."ID_BILPAYMENTCATA")


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

SQL> select /*+ use_hash(p e) */
  2   count(*)
  3    from ush.ocr_bilpaymentcatapaysys p,
  4         ush.ocr_bilpaymentcata       e
  5   where e.id_bilpaymentcata = p.id_bilpaymentcata;
  17240205
Elapsed: 00:00:09.71

Execution Plan
----------------------------------------------------------
Plan hash value: 3459366041

-------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                            |     1 |    12 |       |  5500  (13)| 00:00:32 |
|   1 |  SORT AGGREGATE        |                            |     1 |    12 |       |            |          |
|*  2 |   HASH JOIN            |                            |    15M|   179M|    79M|  5500  (13)| 00:00:32 |
|   3 |    INDEX FAST FULL SCAN| PK_ID_BILPAYMENTCATA       |  4646K|    26M|       |   279  (22)| 00:00:02 |
|   4 |    INDEX FAST FULL SCAN| FK_BILPS_ID_BILPAYMENTCATA |    16M|    95M|       |  1057  (21)| 00:00:07 |
-------------------------------------------------------------------------------------------------------------

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

   2 - access("E"."ID_BILPAYMENTCATA"="P"."ID_BILPAYMENTCATA")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13752  consistent gets
          0  physical reads
       1980  redo size
        338  bytes sent via SQL*Net to client
        346  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
1 мар 10, 12:48    [8411106]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
Timur Akhmadeev
Member

Откуда:
Сообщений: 508
Можно глянуть на
select pname, pval1 from sys.aux_stats$;
и DDL табличек с индексами?
1 мар 10, 12:52    [8411152]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
бобищо
Member [заблокирован]

Откуда:
Сообщений: 245
сделаю.

в таблице деталей из 16 млн есть 1200 строк, которых нет в мастер таблице.
поэтому сейчас план, который предложил Elic, невозможен
1 мар 10, 12:54    [8411172]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
бобищо
Member [заблокирован]

Откуда:
Сообщений: 245
бобищо
в таблице деталей из 16 млн есть 1200 строк, которых нет в мастер таблице.
поэтому сейчас план, который предложил Elic, невозможен

здесь я ошибся, поправлюсь.

В таблице деталей нет записей, отсутвтующих втаблице мастер.
Итак, при создании constraint'а, план меняется на один FFS по FK-idx.
При отключении FK план опять сваливается в NL.
Есть что-то, что сильно удешевляет NL.


Timur Akhmadeev
Можно глянуть на
select pname, pval1 from sys.aux_stats$;


SQL> select pname, pval1 from sys.aux_stats$;
STATUS
DSTART
DSTOP
FLAGS                                   1
CPUSPEEDNW                     1699.24446
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM                            5.799
MREADTIM                              7.5
CPUSPEED                             1704
MBRC                                   16
MAXTHR                              44032
SLAVETHR

13 rows selected.
1 мар 10, 13:15    [8411408]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
--Попрошайка--
Member

Откуда: АО "Попрошайка".
Сообщений: 13709
Блог
бобищо,

а всего-то надо построить ограничение целостности и не придется зубы через задний проход тянуть. :)

И опять же, за весь диалог я ни видел, что бы Вы хоть раз сослались бы на 10053, хотя громко о нем написали. :)

---
Пэ.Сэ. Elic рулит!
1 мар 10, 13:20    [8411458]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
бобищо
Member [заблокирован]

Откуда:
Сообщений: 245
--Попрошайка--
бобищо,

а всего-то надо построить ограничение целостности и не придется зубы через задний проход тянуть. :)

И опять же, за весь диалог я ни видел, что бы Вы хоть раз сослались бы на 10053, хотя громко о нем написали. :)

---
Пэ.Сэ. Elic рулит!


пришел овощ и всё опошлил.

с чего ты взял что мне необходимо ограничение целостности? ты вопрос читал? я хочу узнать где в данном кейсе вкралась ошибка CBO

файл 10053,о котором я так громко написал, прикреплен к первому посту.
возможно он сможет чем-то помочь, мне он пока не помог.


попрошайка напоминает прокаженного, который ходит от дома к дому, и везде его посылают.
1 мар 10, 13:25    [8411508]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
Timur Akhmadeev
Member

Откуда:
Сообщений: 508
Я не думаю что это какая ошибка (и если ошибка - то скорее в расчете HJ). Мое понимание того, что делает Оракл:
HJ over 2xIFFS = ~9K + 2K + HJ cost. Стоимость hash join'a, на мой взгляд, какая-то сильно неадекватная - ~19K.
NL over IFFS + UQ scan = ~9K + NL cost. Здесь самая важная особенность в том, нет доступа к таблице - идет скан по индексу. В 10053 NL помечен как "NL Join (ordered)". Понятия не имею что это значит в действительности, но мне кажется следующее: Оракл предпалагает, что будет производить UQ scan последовательно, т.е. по возрастанию ключей. Такой скан - это достаточно дешевая операция для Оракла, т.к. есть buffer pinning - вместо 4M LIO это обойдется намного дешевле. Полагаю, CBO это как-то учитывает и стоимость NL join за счет этого приближается к обращению ко всем блокам в индексе - ~10K + еще по мелочи. Как только появится доступ к мастер таблице в запросе, стоимость подобного запроса в NL многократно возрастет.
PS. все описанное никак не обосновано, только мысли как оно должно по идее работать.
1 мар 10, 13:41    [8411663]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
hell
Member

Откуда:
Сообщений: 3002
бобищо

Есть что-то, что сильно удешевляет NL.


SREADTIM                            5.799
MREADTIM                              7.5



?
1 мар 10, 13:43    [8411683]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
--Попрошайка--
Member

Откуда: АО "Попрошайка".
Сообщений: 13709
Блог
бобищо

пришел овощ и всё опошлил.
попрошайка напоминает прокаженного, который ходит от дома к дому, и везде его посылают.


Во-первых, следи за базаром, щегол сопливый.

бобищо

с чего ты взял что мне необходимо ограничение целостности?


Во-вторых, если ты назвал это FK, то делай FK, а не говнокодь

бобищо

файл 10053,о котором я так громко написал, прикреплен к первому посту.
возможно он сможет чем-то помочь, мне он пока не помог.


В-третьих, если не шаришь в таких файлах, нефиг их делать вообще.

бобищо

ты вопрос читал? я хочу узнать где в данном кейсе вкралась ошибка CBO


Ошибка у тебя в голове. СВО - не ошибается, он оценивает и принимает решение.
1 мар 10, 14:04    [8411873]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
бобищо
Member [заблокирован]

Откуда:
Сообщений: 245
забаньте уже этого говнотролля, что-ли
пришел насрал, гадина.
1 мар 10, 14:24    [8412056]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
--Попрошайка--
Member

Откуда: АО "Попрошайка".
Сообщений: 13709
Блог
бобищо,

ну ты уже понял, что тебе больше никто на вопрос не ответит, грубияну-неадеквату?

Нет? Зря...
1 мар 10, 14:27    [8412084]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
бобищо
Member [заблокирован]

Откуда:
Сообщений: 245
--Попрошайка--
мне тупому лузеру так не хватает внимания! обнимите меня все, пожалуйста :(


странная форма привлечения внимания к собственной персоне у парня
1 мар 10, 14:37    [8412155]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
AI
Member

Откуда: Москва
Сообщений: 2814
Стоп, ребята!

2 бобищо

Странно Вы реагируете на вполне разумное замечание. Тон не понравился?

2 --Попрошайка--

МОлодежь пошла горячая. Им надо говорить только понятное. Иначе, сказанное считается оскорблением. Но это не значит, что надо сразу лезть в драку.


Будете продолжать - забаню обоих...
1 мар 10, 14:48    [8412255]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
Camper
Member

Откуда:
Сообщений: 174
Timur Akhmadeev,

+1
ИМХО, проблема с тем, что не правильно оценивается значение "Cost per ptn"
HA Join
  Outer table: 
    resc: 2255.00  card 4492749.00  bytes: 6  deg: 1  resp: 2255.00
  Inner table: OCR_BILPAYMENTCATAPAYSYS  Alias: P
    resc: 8923.62  card: 16724736.00  bytes: 6  deg: 1  resp: 8923.62
    using dmeth: 2  #groups: 1
    Cost per ptn: 18284.11  #ptns: 1
    hash_area: 256 (max=167500)   Hash join: Resc: 29462.73  Resp: 29462.73  [multiMatchCost=0.00]
  HA cost: 29462.73
     resc: 29462.73 resc_io: 29060.00 resc_cpu: 7196459621
     resp: 29462.73 resp_io: 29060.00 resp_cpu: 7196459621
Идет HJ без параллелизма, для исправления можно попробовать (прошу воспринимать не как панацею, а просто размышления "на вскидку")
- хинт
- ломать статистику таблиц
- поиграться с параметром "_smm_min_size" (в трейсе hash_area: 256). ("_pga_max_size", и, соответственно, "_smm_max_size" как верхний лимит, уже поменяли)
1 мар 10, 14:54    [8412319]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
бобищо
Member [заблокирован]

Откуда:
Сообщений: 245
ребята, мне удалось проблему воспроизвести в более читаемом варианте.

цифры (aux_stats$ и стоимости ) могут быть другие, делается на другом хосте нежели файл прикрепленный к первому сообщению.


итак, делаю всё сначала.


SQL> create table master (m_id int, somedata varchar2(200));
 
Table created
 
SQL> alter table master add constraint pk_m_id primary key (m_id);
 
Table altered
 
SQL> insert /*+ append */ into master (m_id, somedata) select rownum, rpad('*', 200, '*') from dual connect by level <= 4000000;
 
4000000 rows inserted

SQL> create table detail (d_id int primary key, m_id int,  somedata varchar2(200));
 
Table created
 
SQL> insert /*+ append */ into detail (d_id, m_id, somedata) select rownum, ceil(rownum/4), rpad('@', 200, '@') from dual connect by level <= 16000000;
 
16000000 rows inserted
 
SQL> commit;
 
Commit complete


--- так выглядят данные

SQL> select * from master where rownum <=10;
 
                                   M_ID SOMEDATA
--------------------------------------- --------------------------------------------------------------------------------
                                      1 ********************************************************************************
                                      2 ********************************************************************************
                                      3 ********************************************************************************
                                      4 ********************************************************************************
                                      5 ********************************************************************************
                                      6 ********************************************************************************
                                      7 ********************************************************************************
                                      8 ********************************************************************************
                                      9 ********************************************************************************
                                     10 ********************************************************************************
 
10 rows selected
 
SQL> select * from detail where rownum <=10;
 
                                   D_ID                                    M_ID SOMEDATA
--------------------------------------- --------------------------------------- --------------------------------------------------------------------------------
                                      1                                       1 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                                      2                                       1 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                                      3                                       1 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                                      4                                       1 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                                      5                                       2 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                                      6                                       2 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                                      7                                       2 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                                      8                                       2 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                                      9                                       3 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                                     10                                       3 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
10 rows selected
 



SQL> create index idx_detail_m_id on detail(m_id);
 
Index created
 
SQL> exec dbms_stats.gather_table_stats(user, 'master', estimate_percent => 100, cascade => true, degree => 8, method_opt => 'for all columns size 254');
 
PL/SQL procedure successfully completed

 
SQL> exec dbms_stats.gather_table_stats(user, 'detail', estimate_percent => 100, cascade => true, degree => 8, method_opt => 'for all columns size 254');
 
PL/SQL procedure successfully completed



SQL> explain plan for select count(*) from master m, detail d where m.m_id = d.m_id;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3557307137
--------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |     1 |    12 |  4026  (82)|
|   1 |  SORT AGGREGATE        |                 |     1 |    12 |            |
|   2 |   NESTED LOOPS         |                 |    16M|   183M|  4026  (82)|
|   3 |    INDEX FAST FULL SCAN| IDX_DETAIL_M_ID |    16M|    91M|   949  (22)|
|*  4 |    INDEX UNIQUE SCAN   | PK_M_ID         |     1 |     6 |     0   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("M"."M_ID"="D"."M_ID")
 
16 rows selected






---------------
-- autotrace

SQL> select /*+ use_hash(m d) */ count(*) from master m, detail d where m.m_id = d.m_id;

  COUNT(*)
----------
  16000000

Elapsed: 00:00:08.14

Execution Plan
----------------------------------------------------------
Plan hash value: 626754097

--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |     1 |    12 |       |  5070  (14)| 00:00:30 |
|   1 |  SORT AGGREGATE        |                 |     1 |    12 |       |            |          |
|*  2 |   HASH JOIN            |                 |    16M|   183M|    68M|  5070  (14)| 00:00:30 |
|   3 |    INDEX FAST FULL SCAN| PK_M_ID         |  4000K|    22M|       |   210  (25)| 00:00:02 |
|   4 |    INDEX FAST FULL SCAN| IDX_DETAIL_M_ID |    16M|    91M|       |   949  (22)| 00:00:06 |
--------------------------------------------------------------------------------------------------

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

   2 - access("M"."M_ID"="D"."M_ID")


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

SQL> select count(*) from master m, detail d where m.m_id = d.m_id;

  COUNT(*)
----------
  16000000

Elapsed: 00:00:24.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3557307137

------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |     1 |    12 |  4026  (82)| 00:00:24 |
|   1 |  SORT AGGREGATE        |                 |     1 |    12 |            |          |
|   2 |   NESTED LOOPS         |                 |    16M|   183M|  4026  (82)| 00:00:24 |
|   3 |    INDEX FAST FULL SCAN| IDX_DETAIL_M_ID |    16M|    91M|   949  (22)| 00:00:06 |
|*  4 |    INDEX UNIQUE SCAN   | PK_M_ID         |     1 |     6 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   4 - access("M"."M_ID"="D"."M_ID")


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

SQL>
1 мар 10, 15:19    [8412617]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
Timur Akhmadeev
Member

Откуда:
Сообщений: 508
method_opt => 'for all columns size 254'
Это еще зачем?
1 мар 10, 15:36    [8412745]     Ответить | Цитировать Сообщить модератору
 Re: отцы, понедельничная задача про ошибку CBO. [10053]  [new]
бобищо
Member [заблокирован]

Откуда:
Сообщений: 245
Timur Akhmadeev
method_opt => 'for all columns size 254'
Это еще зачем?


а это я погорячился :-)

SQL>  exec dbms_stats.gather_table_stats(user, 'detail', estimate_percent => 100, cascade => true, degree => 8, method_opt => 'for all columns size 1');
 
PL/SQL procedure successfully completed
 
SQL>  exec dbms_stats.gather_table_stats(user, 'master', estimate_percent => 100, cascade => true, degree => 8, method_opt => 'for all columns size 1');
 
PL/SQL procedure successfully completed

ничего не меняет


прикладываю трейс, может поможет

К сообщению приложен файл (q.txt - 47Kb) cкачать
1 мар 10, 15:52    [8412884]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить