bad plan, cbo wrong cost with inline view?

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

Connected to:
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
SQL> explain plan for
  2  SELECT a_offer.offer_id   AS OFFER_ID,
  3           a_offer.offer_code AS OFFER_CODE,
  4           a_offer.offer_name AS OFFER_NAME,
  5           a_offer.offer_date AS OFFER_DATE,
  6           (select case
  7                     when MAX(ca.tirage_fact) = 0 then
  8                      MAX(ca.tirage_plan)
  9                     else
 10                      MAX(ca.tirage_fact)
 11                   end
 12              FROM sigma.client_action ca
 13             where ca.offer_id = a_offer.offer_id) AS TIRAGE,
 14           (select round(avg(order_sum), 2)
 15              FROM sigma.client_order co, sigma.client_action ca
 16             where ca.offer_id = a_offer.offer_id
 17               and co.action_id = ca.action_id) AS SUM_AVG_ORDERS
 18      FROM sigma.offer a_offer
 19     WHERE a_offer.offer_code = '9587';

Explained.

SQL> select * from table(dbms_xplan.display);
Plan hash value: 1422363048

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     1 |    36 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                   |     1 |    10 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| CLIENT_ACTION     |   288 |  2880 |    99   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | XIF6CLIENT_ACTION |   246 |       |     1   (0)| 00:00:01 |
|   4 |  SORT AGGREGATE              |                   |     1 |    18 |            |          |
|*  5 |   HASH JOIN                  |                   |  1333K|    22M|   121K  (3)| 00:08:47 |
|*  6 |    VIEW                      | index$_join$_003  |   288 |  2592 |    63   (4)| 00:00:01 |
|*  7 |     HASH JOIN                |                   |       |       |            |          |
|*  8 |      INDEX RANGE SCAN        | XIF6CLIENT_ACTION |   288 |  2592 |     1   (0)| 00:00:01 |
|   9 |      INDEX FAST FULL SCAN    | XPKCLIENT_ACTION  |   288 |  2592 |    61   (2)| 00:00:01 |
|  10 |    TABLE ACCESS FULL         | CLIENT_ORDER      |    32M|   276M|   120K  (3)| 00:08:45 |
|  11 |  TABLE ACCESS BY INDEX ROWID | OFFER             |     1 |    36 |     2   (0)| 00:00:01 |
|* 12 |   INDEX UNIQUE SCAN          | XAK1OFFER         |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   3 - access("CA"."OFFER_ID"=:B1)
   5 - access("CO"."ACTION_ID"="CA"."ACTION_ID")
   6 - filter("CA"."OFFER_ID"=:B1)
   7 - access(ROWID=ROWID)
   8 - access("CA"."OFFER_ID"=:B1)
  12 - access("A_OFFER"."OFFER_CODE"='9587')

это какойто баг? как может кост запроса быть 2 если в строке 10 он 120К?
почему теряется стоимомть full table scan?
статистика актуальна
на металинке не нарою как бороться, куча wrong result и почти ничего про wrong cost

повторюсь, хочу понять почему неверен кост и как на это повлиять
переписав запрос или вставив хинт ситуация исправляется
например RULE и запрос летает.

------------------------------------------------------------
| Id  | Operation                      | Name              |
------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |
|   1 |  SORT AGGREGATE                |                   |
|   2 |   TABLE ACCESS BY INDEX ROWID  | CLIENT_ACTION     |
|*  3 |    INDEX RANGE SCAN            | XIF6CLIENT_ACTION |
|   4 |  SORT AGGREGATE                |                   |
|   5 |   TABLE ACCESS BY INDEX ROWID  | CLIENT_ORDER      |
|   6 |    NESTED LOOPS                |                   |
|   7 |     TABLE ACCESS BY INDEX ROWID| CLIENT_ACTION     |
|*  8 |      INDEX RANGE SCAN          | XIF6CLIENT_ACTION |
|*  9 |     INDEX RANGE SCAN           | XIF6CLIENT_ORDER  |
|  10 |  TABLE ACCESS BY INDEX ROWID   | OFFER             |
|* 11 |   INDEX UNIQUE SCAN            | XAK1OFFER         |
------------------------------------------------------------

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

   3 - access("CA"."OFFER_ID"=:B1)
   8 - access("CA"."OFFER_ID"=:B1)
   9 - access("CO"."ACTION_ID"="CA"."ACTION_ID")
  11 - access("A_OFFER"."OFFER_CODE"='9587')

XCB

это какойто баг?

Нет, это фича, подселекты в списке select
(Scalar Subquery, а невовсе не Inline View ) рассматриваются и оптимизируются как отдельные запросы и включаются в общий план для "вида", их стоимость в общей стоимости запроса не показывается. Долгое время, если не ошибаюсь до 9-ки вы бы их вообще в плане не увидели, а потом нам стали их показывать, просто примешивая в план основного запроса.

XCB

повторюсь, хочу понять почему неверен кост и как на это повлиять

Оптимизатор, судя по всему, неверно оценивает мощность соединения sigma.client_order с sigma.client_action. Попробуйте sql tune advisor, у вас насколько понимаю Oracle не ниже 10g, возможно он постороит sql profile. Хотя я не знаю работает ли advisor c Scalar Subquery в запросе.

Ну или попытайтесь разобраться с оценками стоимостей соединения и понять почему на ваших данных и статистике такое получается, Льюис вам в руки и Wolfgang Breitling на шею.
При отсутствии гистограмм формула расчета кардинальности соединения:
join cardinality = cardA * cardB * join selectivity
join selectivity = 1/max(ndvA, ndvB)
cardA, cardB оцененая мощность выборки из таблиц после фильтрации (288 и 32M)
ndvA, ndvB - number distinct values по ключу соединения согласно статистике на таблицах.
С гистограммами значительно сложнее, как вариант можно попробовать собрать гистограммы на полях соед инения ии наоборот удалить если они есть.

Я и ёжик, спасибо, тоесть лечения искать не стоит... :(
с планом понятно, смотрю и перечитываю Льюиса