Category Archives: CBO

event 10093 CBO Enable force hash joins

Oracle 10093 event使Oracle优化器进行hash join。 … 继续阅读

发表在 CBO, Event | 留下评论

NO_Eliminate_Join and FK Constraint Novalidate

RY IS *******
SELECT “C”.”CUST_FIRST_NAME” “CUST_FIRST_NAME” FROM “BLUE”.”C_CUSTOMERS” “C”,”BLUE”.”P_COUNTRIES” “P” WHERE “C”.”COUNTRY_ID”=”P”.”COUNTRY_ID”
JE: cfro: C_CUSTOMERS objn:136533 col#:3 dfro:P_COUNTRIES dcol#:1
JE: cfro: C_CUSTOMERS objn:136533 col#:3 dfro:P_COUNTRIES dcol#:1
Query block (0x4abb39d30) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT “C”.”CUST_FIRST_NAME” “CUST_FIRST_NAME” FROM “BLUE”.”C_CUSTOMERS” “C”,”BLUE”.”P_COUNTRIES” “P” WHERE “C”.”COUNTRY_ID”=”P”.”COUNTRY_ID”
JE: eliminate table: P_COUNTRIES (P)
Registered qb: SEL$A43D1678 0xabb39d30 (JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1; “P”@”SEL$1″)
———————
QUERY BLOCK SIGNATURE
———————
signature (): qb_name=SEL$A43D1678 nbfros=1 flg=0
fro(0): flg=0 objn=136535 hint_alias=”C”@”SEL$1”

SQL:******* UNPARSED QUERY IS *******
SELECT “C”.”CUST_FIRST_NAME” “CUST_FIRST_NAME” FROM “BLUE”.”C_CUSTOMERS” “C”
Query block SEL$A43D1678 (#0) simplified

使用no_eliminate_join阻止join 消除。

SQL> select /*+ no_eliminate_join(p) */
  2   c.cust_first_name
  3    from c_customers c, p_countries p
  4   where c.country_id = p.country_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3595640479

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |              |     1 |    38 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| C_CUSTOMERS  |     1 |    25 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_COUNTRIES |     1 |    13 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   3 - access("C"."COUNTRY_ID"="P"."COUNTRY_ID")

10053内容如下

*************************
Join Elimination (JE)    
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ NO_ELIMINATE_JOIN ("P") */ "C"."CUST_FIRST_NAME" "CUST_FIRST_NAME" FROM "BLUE"."C_CUSTOMERS" "C","BLUE"."P_COUNTRIES" "P" WHERE "C"."COUNTRY_ID"="P"."COUNTRY_ID"
JE:   cfro: P_COUNTRIES objn:136535 col#:1 dfro:C_CUSTOMERS dcol#:3
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ NO_ELIMINATE_JOIN ("P") */ "C"."CUST_FIRST_NAME" "CUST_FIRST_NAME" FROM "BLUE"."C_CUSTOMERS" "C","BLUE"."P_COUNTRIES" "P" WHERE "C"."COUNTRY_ID"="P"."COUNTRY_ID"
Query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM:     PM bypassed: Outer query contains no views.
PM:     PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"C"."COUNTRY_ID"="P"."COUNTRY_ID"
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "C"."COUNTRY_ID"="P"."COUNTRY_ID"

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ NO_ELIMINATE_JOIN ("P") */ "C"."CUST_FIRST_NAME" "CUST_FIRST_NAME" FROM "BLUE"."C_CUSTOMERS" "C","BLUE"."P_COUNTRIES" "P" WHERE "C"."COUNTRY_ID"="P"."COUNTRY_ID"
kkoqbc: optimizing query block SEL$1 (#0)


*********************************
Number of join permutations tried: 2
*********************************
Consider using bloom filter between C[C_CUSTOMERS] and P[P_COUNTRIES] with ??
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000)  rejected because not a hash join
Enumerating distribution method (advanced)
--- Distribution method for 
join between C[C_CUSTOMERS](serial) and P[P_COUNTRIES](serial); jm = 12; right side access path = IndexUnique
---- NLJ default -> BROADCAST-LEFT

3 - access("C"."COUNTRY_ID"="P"."COUNTRY_ID")
 
Content of other_xml column
===========================
  db_version     : 11.2.0.4
  parse_schema   : BLUE
  plan_hash      : 3595640479
  plan_hash_2    : 1993472567
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "C"@"SEL$1")
      INDEX(@"SEL$1" "P"@"SEL$1" ("P_COUNTRIES"."COUNTRY_ID"))
      LEADING(@"SEL$1" "C"@"SEL$1" "P"@"SEL$1")
      USE_NL(@"SEL$1" "P"@"SEL$1")
    END_OUTLINE_DATA
  */

继续阅读

发表在 CBO, Hint | 留下评论

Bitmap Index OR

在测试bitmap index的时候,发现oracle在处理OR这个谓词条件的时 … 继续阅读

发表在 bitmap, CBO | 留下评论

SQL Plan Directives-part1

SQL PLAN Directives(SPD)在12.1.0.1版本被引入,目 … 继续阅读

发表在 12c, CBO | 留下评论

Adaptive plans-part2-Parallel Distribution Methods

在上篇文章中介绍了adative plan关于join method特性,本文接 … 继续阅读

发表在 12c, CBO, Parallel | 留下评论

Adaptive plan-part1-adaptive join method

CBO通过统计信息决定SQL的最优执行计划,如果统计信息不准确,谓词过于复杂,或 … 继续阅读

发表在 12c, CBO | 一条评论

cardinality feedback(基数反馈)

优化器在很多原因下会错误的评估cardinalities,比如丢失统计信息、统计 … 继续阅读

发表在 CBO | 留下评论