ࡱ> н(  h T@http://www.EvDBT.com/library.htm$/ 00DTimes New Romanv 0( 0DArialNew Romanv 0( 0" DComic Sans MSnv 0( 0B0DCourier NewSnv 0( 01c .@  @@``  @n?" dd@  @@`` P)l2    $*L #a*+,         (,,'  !$%&' -!% ,+,,-'./0 1) T=? f3t&4nC@g4>d>dv 0ppp@  <4!d!d 0L<4BdBd 0Lf ʚ;2Nʚ;<4dddd{ 0:2___PPT9/ 0? %O =+ www.SageLogix.Com&f3f3Agenda for Today`The Oracle Optimizer RBO and how it works CBO and how it works Case studies Debugging CBO Paper at http://www.EvDBT.com/library.htm Also published in  SQL>Update (RMOUG newsletter) IOUG-A  SELECT magazine And in Russian (see link on website)& t`F`*``_`F*_  "dr!0d]The infamous Oracle OptimizerRule-based optimizer (RBO) Was to have been discontinued in Oracle9i  The report of my death was an exaggeration Mark Twain, 1897 Uses a set of 15-20  rules to determine execution path based on hard-coded best practices Cost-based optimizer (CBO) Introduced in Oracle7 v7.0 Most problems were due to bugs in ANALYZE Not really usable in production until Oracle8 v8.0 Very reliable from Oracle8 v8.0 onwards Very desirable for production systems in Oracle8i onwards*-[]b(-  []("^RBO&RBO seems easy to understand Tuning SQL under RBO involves tricks like: WHERE clause predicates are evaluated from bottom-to-top Each predicate is ranked according to rules Lower rank determines order of access and join operations In the event of a tie in ranking, the order of row sources in the FROM clause can affect how the tie is broken Sometimes, the ID of an object (table or index) can be used to break a tie Dropping/recreating indexes can change plans! Parameter OPTIMIZER_MODE = RULE can be set For entire database instance (using  init.ora or ALTER SYSTEM) For individual sessions (using ALTER SESSION) For individual SQL statements (using hints)+9y++ x         5  y+RBO Access Path Rankings 1. Single row by ROWID 2. Unique indexed cluster key = constant 3. Unique hash cluster key w/ unique key = constant 4. Entire unique concatenated index = constant 5. Unique single-column index = constant 6. Entire cluster key = cluster key join within cluster 7. Hash cluster key = constant 8. Entire indexed cluster key = constant 9. Entire non-unique concatenated index = constant 10. Merge of non-unique indexesBRBO Access Path Rankings (cont d)!11. Entire concatenated index of lower-bound of range 12. Incomplete concatenated index = constant 13. Unique indexed column BETWEEN or LIKE 14. Non-unique indexed column BETWEEN or LIKE 15. Unbounded range-scan on unique indexed columns 16. Unbounded range-scan on non-unique indexed columns 17. Sort-merge join 18. MAX or MIN of indexed column 19. ORDER BY on indexed columns 20. Full table scan3 RBO example^SQL> SELECT SUM(L.SELLING_PRICE * 2 (NVL(SUM(LD.QUANTITY(+)), 3 NVL(L.ORDERED_QUANTITY,0) - 4 NVL(L.CANCELLED_QUANTITY,0)) - 5 NVL(L.INVOICED_QUANTITY,0))) 6 FROM RA_SITE_USES SU, 7 RA_ADDRESSES A, 8 SO_LINE_DETAILS LD, 9 SO_LINES L, 10 SO_HEADERS H 11 WHERE H.S1 = 1 Which predicate would RBO choose first? 12 AND A.CUSTOMER_ID = :customer_id 13 AND SU.ADDRESS_ID = A.ADDRESS_ID 14 AND H.INVOICE_TO_SITE_USE_ID = SU.SITE_USE_ID 15 AND H.CURRENCY_CODE = RTRIM(:currency_code) 16 AND NVL(LD.SCHEDULE_DATE, & SQL statement continues for 23 more lines& Zk' n(#)4(RBO example (cont d)bSQL statement belongs to an important batch program in Oracle  Order Entry named  Book Orders Generally one of the single greatest  resource hogs on any Oracle OE system& What do you think? RBO eliminates the RA_ADDRESSES row-source because of ordering in  FROM clause& RBO chooses index on S1 (instead of CURRENCY_CODE) because index on S1 was created later than index on CURRENCY_CODE (can be verified by querying DBA_OBJECTS)& L`N`N_CBOUses statistics stored in the data dictionary to determine cost CBO is just a math processor, following formulas Processing steps includes: Base table access costs All access methods are considered for all row sources Where parallelism is possible, costs are calculated for serial and parallel access Join order and join method computations All possible join orders and join methods are considered@L(9@L< '(  9`CBO (cont d)bProcessing steps (cont d) OR expansion Possible sorting for ORDER BY using indexes Partition-pruning Parameter OPTIMIZER_MODE can be set to CHOOSE, FIRST_ROWS, or ALL_ROWS At instance, session, or SQL statement level CHOOSE is the default setting If none of the tables involved in a SQL statement has statistics, then RULE is chosen, otherwise COST Using FIRST_ROWS or ALL_ROWS is intended to influence the type of join method chosenvKGKfUKGK  f  U   $So what is  cost ?xCost is intended to represent physical I/O Documentation says  physical-IO + CPU/1000 + 1.5 NetIO Let s just keep it simple and focus on physical I/O FULL table scan cost calculations Depends on the number of formatted blocks in a table (DBA_TABLES.BLOCKS) beneath the highwater mark blocks / DB_FILE_MULTIBLOCK_READ_COUNT EMPTY_BLOCKS column on DBA_TABLES displays blocks allocated to table segment but not yet used Does not affect optimizer+84"d(^ "' "U        '  ^,\   Index scansIndex scan costs Access cost of the index depends on: Which index scan operation is used UNIQUE scan Probes root->branch->leaf for a single row RANGE scan Probes root->branch->leaf to first index entry Then scans leaf-to-leaf until last index entry FULL scan Scans leaf blocks using single-block access FAST FULL scan Scans all blocks in index using multi-block access, keeps leaf blocks and discards branch blocks,Z%Z#Z Z+Z Z^Z Z,ZZaZ%#        !  ,a5(Index scans (cont d)  (Index scans (cont d) Access cost of the index depends on (cont d): B*Tree height (column BLEVEL) Affects UNIQUE index scans Number of leaf blocks (LEAF_BLOCKS) Average number of leaf blocks per distinct value Selectivity of data values Affects RANGE, FULL, and FAST FULL index scans.p/.  //   SelectivityCalculated from estimated or computed cardinality statistics from ANALYZE or DBMS_STATS DBA_INDEXES.DISTINCT_KEYS DBA_INDEXES.NUM_ROWS DBA_INDEXES.UNIQUENESS LOVAL and HIVAL data values of all indexed columns stored in DBA_TAB_HISTOGRAMS S = ratio ranging between 0 and 1 Roughly calculated as  S = 1 / DISTINCT_KEYS tending toward 0 = more distinct keys tending toward 1 = fewer distinct keysX".M& &".        (Selectivity (cont d)Selectivity is based on the predicate operation For equivalence operations (=, !=, IN, NOT IN) where col =  value selectivity = 1 / #-distinct-keys For open-ended range scans where col >  value selectivity = (HIVAL  value) / (HIVAL  LOWVAL) For bounded range scans where col between  value1 and  value2 selectivity = (value2  value1) / (HIVAL  LOWVAL) Bind-variables short-circuit this calculation Equivalence and bounded: hard-coded at S=0.05 Open-ended: hard-coded at S=0.25N0Z/ZZ"ZZZ1ZZ'Z3Z.ZOZ0                          $$$$$$$$$$$$.(( ,,,,,,,, ,,,,>eMX Histograms CBO normally only has HIVAL, LOVAL, and DISTINCT_KEYS to calculate selectivity Must assume an even distribution of data values Not always a valid assumption in real-life! ANALYZE TABLE & FOR [ ALL [ INDEXED ] ] COLUMNS [ column [, column-list ] ] Gathers histogram information to store in: DBA_TAB_HISTOGRAMS (DBA_HISTOGRAMS in v7.3) DBA_PART_HISTOGRAMS (Oracle8 v8.0 and above) DBA_SUBPART_HISTOGRAMS (Oracle8i and above) Histograms are just data range endpoints (default: 75)O0,L+7O!,2           x &Histograms (cont d) &Histograms (cont d) &Histograms (cont d)Histograms identify popular and unpopular data values Using an index RANGE scan against popular data values can be a bad idea More efficient to perform FULL table scan and filter out unwanted rows If CBO only knows that an indexed column has a small number of DISTINCT_KEYS, it will decide to perform FULL table scan Unless it can identify that the data value specified in the query is unpopular Then it will use the index instead Pop quiz: Why would the use of bind variables disable histogram utilization?6ZHZGZxZOZ$ZNZ  "GxE      $  8 CBO example^SQL> SELECT SUM(L.SELLING_PRICE * 2 (NVL(SUM(LD.QUANTITY(+)), 3 NVL(L.ORDERED_QUANTITY,0) - 4 NVL(L.CANCELLED_QUANTITY,0)) - 5 NVL(L.INVOICED_QUANTITY,0))) 6 FROM RA_SITE_USES SU, 7 RA_ADDRESSES A, 8 SO_LINE_DETAILS LD, 9 SO_LINES L, 10 SO_HEADERS H 11 WHERE H.S1 = 1 Which predicate would CBO choose first? 12 AND A.CUSTOMER_ID = :customer_id 13 AND SU.ADDRESS_ID = A.ADDRESS_ID 14 AND H.INVOICE_TO_SITE_USE_ID = SU.SITE_USE_ID 15 AND H.CURRENCY_CODE = RTRIM(:currency_code) 16 AND NVL(LD.SCHEDULE_DATE, & SQL statement continues for 23 more lines& Zk' n(#)aHIndex scans  the rest of the story& Cost of accessing the index itself is only half of the cost calculation of indexed access Cost of table access from the index also a factor For UNIQUE scans just another single logical read to retrieve a row from the table For RANGE, FULL, and FAST FULL scans CLUSTERING_FACTOR measures ordering of table rows with index entries Measuring efficiency of table access from index entries CLUSTERING_FACTOR value on DBA_INDEXES ranges between NUM_ROWS (bad extreme) and BLOCKS (good extreme) on DBA_TABLESZZ2ZZBZ%ZEZZK B%  "x  *Basic costing for index scansCalculating cost for a RANGE scan on NONUNIQUE index for predicate  col1 = :b1 Some statistics: DBA_TABLES.BLOCKS = 100,000 DBA_TABLES.NUM_ROWS = 5,000,000 DBA_INDEXES.BLEVEL = 3 DBA_INDEXES.DISTINCT_KEYS = 10,000 DBA_INDEXES.CLUSTERING_FACTOR = 1,000,000 DBA_INDEXES.LEAF_BLOCKS=10,000 DBA_INDEXES.AVG_DATA_BLOCKS_PER_KEY=2 DBA_INDEXES.AVG_LEAF_BLOCKS_PER_KEY=1LP K +LBasic costing for index scans (cont d)Basic cost calculations start with a calculation of logical reads: Full-table scan cost is 100,000 / 16 = 6,250 Full-index scan cost = 10,000 Fast full-index scan cost is 10,000 / 16 = 625 Range scan cost = BLEVEL + (AVG_LEAF_BLOCKS_PER_KEY * (NUM_ROWS * SELECTIVITY)) = 1,500 It looks like fast full-index scans win with the lowest score, but& CE4  RE  , Cost adjustments for index scans *Looking at the cost calculations so far& CBO is not considering the fact that physical-I/O from indexed access is different than physical-I/O from FULL table scan access Since Oracle v6.0.x: For indexed access, logical-I/O (a.k.a. accesses of blocks in the Buffer Cache) resulting in a cache miss, causes single-block physical-I/O (a.k.a. accesses of blocks in datafiles) Blocks retrieved by such I/O (wait event  db file sequential read ) are stored in the MRU end of the LRU chain of the Buffer Cache For FULL table scan access, logical-I/O resulting in a cache miss causes multi-block physical-I/O Blocks retrieved by such I/O (wait event  db file scattered read ) are stored in the LRU end of the LRU chain of the Buffer Cache This is not the full story, but it is the general picture& )b;)%   -  &*B            *    B  ;  6OPTIMIZER_INDEX_CACHINGWith such differences in the cacheability of index scans versus FULL table scans, is it valid to assume a 0% buffer cache hit ratio for indexed scans, as is true for FULL table scans? & um, nope& & logical reads on indexed UNIQUE, RANGE and FULL scans should then be adjusted to produce physical reads& Parameter OPTIMIZER_INDEX_CACHING Defaults to 0, can range from 0 to 99 Cost * (1  (OPTIMIZER_INDEX_CACHING / 100) I recommend setting this parameter value to  90 & Don t set to maximum value of  99 !$Z ZkZ"Z&Z,Z2Z$Z D5   #"&  ,  2$ 7OPTIMIZER_INDEX_COST_ADJFSimilarly, is the service time on I/O requests the same for indexed scans as FULL table scans? This can be measured empirically using: SQL> select event, average_wait from v$system_event 2 where event like  db file s% ; EVENT AVERAGE_WAIT -------------------------- ------------ db file scattered read 1.1283475 db file sequential read .121103 Parameter OPTIMIZER_INDEX_COST_ADJ Defaults to 100, can range from 1 through 10000 Informs CBO of relative cost of indexed access to table access Cost * (OPTIMIZER_INDEX_COST_ADJ / 100) Set parameter to the ratio of AVERAGE_TIME values for  db file sequential read /  db file scattered read _(#0?(65<(#  0 #(65-RCost adjustments for index scans (cont d))So, if OPTIMIZER_INDEX_CACHING set to 90 then: Full table scan = 6,250 (unadjusted) Fast full index scan = 625 (unadjusted) Index FULL scan = 10,000 * (1  (90/100)) = 1,000 (adjusted) Index RANGE scan = 1,500 * (1  (90/100)) = 150 (adjusted) And, if OPTIMIZER_INDEX_COST_ADJ set to 50: Full table scan = 6,250 (unadjusted) Fast full index scan = 625 (unadjusted) Index FULL scan = 1,000 * (50/100) = 500 (adjusted) Index RANGE scan = 150 * (50/100) = 75 (adjusted) But wait! There s more& /,/ , .DNow add in costs for table access& Costs for indexed scans only do not yet take into account the costs of accessing the table rows Cost + (AVG_DATA_BLOCKS_PER_KEY * (CLUSTERING_FACTOR / BLOCKS)) Full table scan = 6,250 Fast full index scan = 625 * (2 * (1,000,000/100,000)) = 12,500 Index FULL scan = 500 * (2 * (1,000,000/100,000)) = 10,000 Index RANGE scan = 75 * (2 * (1,000,000/100,000)) = 1,500 Now, plug in different values and see what happens!`:5`9  4  /Parameters which affect CBOOPTIMIZER_MODE default: CHOOSE, suggested: default OPTIMIZER_PERCENT_PARALLEL default: 0, suggested: default OPTIMIZER_INDEX_COST_ADJ default: 100, suggested: 10-50 OPTIMIZER_INDEX_CACHING default: 0, suggested: 90 DB_FILE_MULTIBLOCK_READ_COUNT Default: 16, suggested: default or less SORT_AREA_SIZE Default: 65536, suggested: 512K-8M Affects choice of join method  favors SORT-MERGE when set high HASH_AREA_SIZE Default: (SORT_AREA_SIZE * 2), suggested: default Affects choice of join method  favors HASH when set highp(cl       #ck    0Statistics to examine2If you don t like the plan that CBO chose: If you wanted to see an index RANGE scan, then check: DBA_TABLES.BLOCKS DBA_TABLES.NUM_ROWS DBA_INDEXES.CLUSTERING_FACTOR If you suspect the selectivity calculation: DBA_INDEXES.DISTINCT_KEYS DBA_TAB_HISTOGRAMS Were  bind-variables used instead of embedded data values in the SQL text? Then use constant literals& Often, you ll find that the CBO made a wise choice+6D,3+6D,' 1 Event 10053This event can be used to dump the CBO s  decision-tree Set with alter session set events  10053 trace name context forever, level 10 ; Creates a  .trc file in USER_DUMP_DEST Only when SQL statement is hard-parsed If soft-parsed, then no trace file created The dump is extremely cryptic Only displays calculations, not formulas Only displays the winning cost, not the winning execution plan permutation Illustrates how the CBO operates, what factors are considered, etc9Z ZGZ(Z'Z+ZZtZCZ9 G(       tC2 Summary for cost-based optimizerpCost-based optimizer (CBO) works very well If you configure initialization parameters sensibly If you remember  feed it properly with ANALYZEd statistics Can also use DBMS_STATS package in place of ANALYZE If you use HISTOGRAMS sparingly and appropriately, when ever it seems like that columns (indexed or not) will have popular or unpopular data values The issue of  bind variables is hopefully addressed with parameter CURSOR_SHARING = [ EXACT | FORCE ] Introduced in v8.1.6, too many bugs! Hopefully works well in v8.1.7, v9.0.1, or v9.2.0 (haven t yet tested it)+p4o+p4s to  B More time? lLet s discuss indexes! B*Tree indexes and how they work Addressing B*Tree weaknesses: Rebuilding sparse indexes Low cardinality and bitmap indexes Sequential data and reverse indexes:?a?a9B*Tree index architectureOracle implements balanced B-Tree (a.k.a. B*Tree) indexes as it s primary indexing method tree-structured mechanism consisting of root, branch, and leaf nodes each node is one database block in Oracle root is the top-level branch node which is the starting point for searches into the index branch nodes point to other branch nodes or to leaf nodes leaf nodes contain data to point to table rows when an index is created on an empty table, one database block is root, branch, and leaf together indexes can be populated transactionally as rows in the table are populated or populated upon creationZZEZ*ZZZ9( = *B#>:DB*Tree index architecture (cont d) ;DB*Tree index architecture (cont d)^When indexes are built on populated tables using CREATE INDEX, ALTER INDEX REBUILD, or direct-path INSERTs root/branch and leaf blocks are populated with entries up to the setting = ((100% - PCTFREE) * DB_BLOCK_SIZE) Exactly enough branches to support required leafs When indexes are built transactionally using conventional-path INSERT statements Blocks are split in half as they exceed (100% - PCTFREE) If data values are random, then back-filling will occur in the half-full split blocks If data values are monotonically-ascending, then back-filling will not occur to populate the half-full split blockskQ9k h +  !    P        .  <DB*Tree index architecture (cont d) =DB*Tree index architecture (cont d) >DB*Tree index architecture (cont d)Summary of issues with B*Tree indexes: B*Tree indexes are designed to optimize random data distributions with high cardinality while supporting high volumes of transactions non-random (monotonically-ascending) data values are supported completed, but result in less-efficient space management DROP/reCREATE is necessary to correct To address these short-comings: Oracle7 v7.3: faster rebuilds with ALTER INDEX REBUILD Oracle7 v7.3: bitmap indexes for low-cardinality data Oracle8 v8.0: REVERSE indexes to randomize non-random data'x& '(/x&  *?Faster index rebuildsPALTER INDEX REBUILD is faster than  DROP then re-CREATE INDEX because: existing index is used as source for new index only leaf blocks of existing index are scanned to build new index less volume of data to scan than table rows data in leaf blocks already sorted No sort operation necessary ALTER INDEX REBUILD can also use: parallel execution direct-path (no rollback generated) nologging/unrecoverable (no redo generated)H/BO"cH/BO  "c} #@Bitmap indexesBitmap indexes are designed for quickly scanning low cardinality data each database block is comprised of two bitmap segments each bitmap segment is comprised of a bitmap and a ROWID listFv1( AREVERSE indexesREVERSE indexes causes monotonically-ascending data values to become more random by simply reversing data  123456 becomes  654321  123457 becomes  754321 , etc... data is transparently converted and unconverted upon insert and retrieval data is re-fitted to fit Oracle s original design decision Impact: only equivalence operations will use the index  = ,  != ,  <> ,  IN , and  NOT IN range-scans will not use the index  > ,  >= ,  < ,  <= ,  LIKE ,  BETWEEN ZZZZ;ZZ/Z$Z#Z(Z  ;/  $  #(lQ & A /T2)+P  ` ̙33` 3` 3333f` 999MMM` f` f3` 3>?" dU@$|?" xd @  dd`8 n?" dZ(@   @@``PR    @ ` ` p>> (     `pfgֳgֳ ?``  X*   `hgֳgֳ ?`   <    `mgֳgֳ ?`   Z*pB  Hf3p?x   Zhogֳgֳ ?HH|  ? Slide Title  #  Tgֳgֳ ?  ;Body Text Second Level Third Level Fourth Level Fifth Level     <  Tgֳgֳ?Q Z*H  0޽h? ? ( PowerPointu0 5-P( (   ThpjJjJ ?G    R*    TtjJjJ ?    T*    ZdxjJjJ ?`G   R*    Z{jJjJ ?`   T*    TUU?oyW  ~ Page *Z  ###55FFp  01 ?  #  TԈgֳgֳ ? 6  ;Body Text Second Level Third Level Fourth Level Fifth Level     <H  0sTh? ? a( ` Z(     TjJjJ ?G    \*   TjJjJ ?    ^*   ZdjJjJ ?`G   \*   ZjJjJ ?`   ^*   TUU?oyW   Page *Z ###55FFH  0sTh? ? a( % wo0 (    Zgֳgֳ ?  J The Search for Intelligent Life in Oracle s Cost-Based Optimizer Tim Gorman Principal - SageLogix, Inc.Xl(t&A0(t&   s gֳgֳ ?T  " d`8   Zgֳgֳ ?Hp| mOttawa Oracle Users GroupU$f3H  0޽h ? (  p$(  r  S  HH|   r  S d   H  0޽h ? (  L$(  Lr L S h HH|   r L S $ P  H L 0޽h ? (  P$(  Pr P S l HH|   r P S (0  H P 0޽h ? ( ) 5 ,$(     `;gֳgֳ ? HH|      Zgֳgֳ ?   & 0@P`pH  0޽h ? 3eX( + 5 .&(     `gֳgֳ ? HH|      Z gֳgֳ ?r  ( @`H  0޽h ? 3eX(  L$(  Lr L S   HH|   r L S  `   H L 0޽h ? (  P$(  Pr P S   HH|   r P S L x  H P 0޽h ? (  T$(  Tr T S h, HH|   r T S $-    H T 0޽h ? (  X$(  Xr X S ` HH|   r X S    H X 0޽h ? (   $(  r  S : HH|   r  S H;   H  0޽h ? (  0$(  r  S E HH|   r  S xF    H  0޽h ? (  @*,\A(  \r \ S o HH|    \ Z8q=?PP 0  :root \ Z0T=?0  <branch \ Z0U=? 0  <branch \ ZX=? 0  <branch \ Zj=?0  <branch  \ Zn=? p  :leaf  \ Z>=?   :leaf  \ Z_=?   :leaf  \ ZZ=? `  :leaf  \ Z\=? @  :leaf \ Z<=? P 0  :leaf \ ZD&=? ` @  :leaf|B \@ TDo?PP |B \@ TDo? |B \ TDo? |B \ TDo?0  \ N1=?0 P :& &  2 \ N}=?@ ``  > & & &  2|B \@ TDo?0 p |B \@ TDo?0 @ |B \ TDo?0   \ N8=?   6&  2 \ NT=?   B& & & &  2|B \ TDo?0 @ |B \ TDo?0   |B \ TDo?0 ` |B \ TDo?0 0 | \ T=?@p|  \ T=?@pP| !\ T=?@P0| "\ T=?@0| #\ T=?@ | $\ T=?@ | %\ T=?@ | &\ T=?@ | '\ T=?@p| (\ T=?@pP| )\ T=?@P0 +\ Z=?iF P ? Index: PK_X   ,\ Z=?Y & @ <Table: X  H \ 0޽h ? (  P$(  r  S ܕ HH|   r  S    H  0޽h ? (  `$(  r  S  HH|   r  S ``  H  0޽h ? (  p$(  r  S  HH|   r  S   H  0޽h ? (  $(  r  S < HH|   r  S    H  0޽h ? (   6 .  (  r  S  HH|      BCLDE Fp?KK@F    `X gֳgֳ?[  A Frequency   Zgֳgֳ?P b.Width-Balanced? No&   ZLgֳgֳ? Z  :10  ZdKgֳgֳ?  :20   Zgֳgֳ? G  :30   Zlgֳgֳ? X  :40   Zܭgֳgֳ?   :50v   N2 @?=LM" v   N2 @?8  " v  N2 @?\ " v  N2 @? 8 9 " v  N2 @?S " dB  <p?  dB  <p? W W dB  <p? dB  <p? dB  <p? OO   Zgֳgֳ?  =RangeH  0޽h ? (   I A  (  r  S \։ HH|     Z׉gֳgֳ?,$D0 DHeight-Balanced!  Zۉgֳgֳ?   91  Z@߉gֳgֳ?  91  Zgֳgֳ? w  95  Z gֳgֳ? a  :45   ZTgֳgֳ?   :50v   N2 @?LM' v   N2 @?  ' v   N2 @? ' v   N2 @?V W ' v  N2 @?'    BCLDE Fp?KK@F dB  <p?  dB  <p? W W dB  <p? dB  <p? dB  <p? OO    `dىgֳgֳ?[  A Frequency   Zlgֳgֳ?  =RangeH  0޽h ? (  $(  r  S ( HH|   r  S P݉0  H  0޽h ? (  h0(  hx h c $\ HH|   x h c $`   H h 0޽h ? (  d$(  dr d S  HH|   r d S    H d 0޽h ? (  (0(  (x ( c $X8 HH|   x ( c $9   H ( 0޽h ? (  ,0(  ,x , c $= H`P|   x , c $D>   H , 0޽h ? (  00(  0x 0 c $ HH|   x 0 c $L`  H 0 0޽h ? (  `$(  `r ` S  HH|   r ` S `   H ` 0޽h ? (   d$(  dr d S Q HH|   r d S XRH   H d 0޽h ? (  040(  4x 4 c $V H|   x 4 c $\WP  H 4 0޽h ? (  @80(  8x 8 c $`? HH|   x 8 c $oP  H 8 0޽h ? (  P<0(  <x < c $t} HH|   x < c $~P  H < 0޽h ? (  `@6(  @x @ c $h HH|   ~ @ s *$HP`   H @ 0޽h ? (  pD6(  Dx D c $ HH|   ~ D s *̐`P   H D 0޽h ? (  H6(  Hx H c $x HH|   ~ H s *4P  H H 0޽h ? (  $(  r  S , HH|   r  S Ժ  H  0޽h ? (  x0(  xx x c $ HH|   x x c $  H x 0޽h ? (  x ++|(  |x | c $XЋ HH|    | Za=?PP 0  :root | ZL,=?0  <branch | Z=? 0  <branch | Z@=? 0  <branch | Z=?0  <branch | Z@=? p  :leaf  | Z=?   :leaf  | Z=?   :leaf  | Zh=? `  :leaf  | Z=? @  :leaf  | Zh=? P 0  :leaf | Z=? ` @  :leaf|B |@ TDo?PP |B |@ TDo? |B | TDo? |B | TDo?0  | N`=?0 P :& &  2 | N=?@ ``  > & & &  2|B |@ TDo?0 p |B |@ TDo?0 @ |B | TDo?0   | N=?   6&  2 | N=?   B& & & &  2|B | TDo?0 @ |B | TDo?0   |B | TDo?0 ` |B | TDo?0 0 | | T=?@p| | T=?@pP|  | T=?@P0| !| T=?@0| "| T=?@ | #| T=?@ | $| T=?@ | %| T=?@ | &| T=?@p| '| T=?@pP| (| T=?@P0 )| Z=?G0 7Branch entries contain: Max data value piece DBA of next level branch or leaf Leaf entries contain: Data value ROWID of table row8 7! *| Zp=?iF P ? Index: PK_X   +| Z=?Y & @ <Table: X  H | 0޽h ? (  0(  x  c $ì HH|   x  c $ì0`  H  0޽h ? (q!  !!!++ (  x  c $Ȭ HH|     Z̬=?PP 0  @   Z@Ь=?0  @   ZDӬ=? 0  @   Zլ=? 0  @   Zl=?0  @   Zho=? p  @    Z=?   @    Z=?   @    Z|=? `  @    Z=? @  @    ZH=? P 0  @   ZPR=? ` @  @ |B @ TDo?PP |B @ TDo? |B  TDo? |B  TDo?0   Nr=?0 P :& &  2  N|=?@ ``  > & & &  2|B @ TDo?0 p |B @ TDo?0 @ |B  TDo?0    N=?   6&  2  N=?   B& & & &  2|B  TDo?0 @ |B  TDo?0   |B  TDo?0 ` |B  TDo?0 0 K  Z=?ih  iImpact of monotonically-ascending data values: less efficient space usage more levels in tree-structure</; H  T,=?PP @  @    T=?0  @  ! Td=?0  @  " T=? 0  @  # Td=?0  @  $ T=? p  @  % T=? ` P  @  & T(=? P @  @  ' Tp =? @0  @  ( T =?   @  ) T=?   @  * T=?   @  + Z=? . How to detect the condition: ALTER INDEX & VALIDATE STRUCTURE populates view INDEX_STATS with one row column PCT_USED on INDEX_STATS is percentage of space utilized in blocks usually about 50-70 percent for this condition"H  0޽h ? (0  &&p(  x  c $P! HH|     Z#=?PP 0  @   ZP'=?0  @   Z)=? 0  @   Z,=? 0  @   Z0=?0  @   ZX4=? p  @    Z8=?   @    Z:=?   @    Z8>=? `  @    Z@=? @  @    ZC=? P 0  @   ZG=? ` @  @ |B @ TDo?PP |B @ TDo? |B  TDo? |B  TDo?0   N`K=?0 P :& &  2  N=?@ ``  > & & &  2|B @ TDo?0 p |B @ TDo?0 @ |B  TDo?0    NQ=?   6&  2  NT=?   B& & & &  2|B  TDo?0 @ |B  TDo?0   |B  TDo?0 ` |B  TDo?0 0 3  ZxX=?iP AImpact of monotonically-ascending data values with sliding windowLB   T<_=?PP @  @    Tb=?0  @  ! Tf=?0  @  " T@i=? 0  @  # T`l=?0  @  $ TXo=? p  @  % Tr=? ` P  @ ] & Zu=?P . How to detect the condition: column PCT_USED on INDEX_STATS is percentage of space utilized in blocks usually less than 35-50% for this condition!!"yH  0޽h ? (  0(  x  c $| HH|   x  c $`}   H  0޽h ? (  0(  x  c $ HH|   x  c $d `   H  0޽h ? (       (  x  c $< HH|   x  c $0@  |  T=?@p  Z=?   @ ROWID list    Z8=?   <Bitmap  Z=?`   @ ROWID list    Z=? ` <Bitmap^   Z=?0,  Z ROWID list is a forward-compressed list of ROWIDs, positioned according to bits in bitmap<J P,)s   ZL=? 0  ] Bitmap is organized into columns for distinct data values and rows of bits representing rowspD   Z=? 06 J More distinct data values means less room for rows within each data value"0K&   N=?@` t Overhead of rewriting a bitmap segment during INSERT, UPDATE, or DELETE results in 40-50 fold performance impact!!!uuH  0޽h ? (  0(  x  c $d HH|   x  c $    H  0޽h ? (j   (  r  S h HH|   H  0޽h ? (D0 @(     H1 ?      fГgֳgֳ ? 6    H  0sTh ? a(0 (    `tgֳgֳ ? >*   : HHh^  6?o  T1 ?3   H  0sTh ? a(0 RJ(    `gֳgֳ ? >*   Neither fast, full index scans nor bitmap index scans appear in this ranking list. They are not available with the rule-based optimizer (see Oracle8 Server Tuning). HHh^  6?o  T1 ?3   H  0sTh ? a(rP+ؽ-2(]Pr3l20 0s@0_!/^5V7N9?ACEGI @F;2=z1KLNkm-e]U: ^AOh+'0 (4@ T`    AThe Search for Intelligent Life in Oracle's Cost-Based Optimizer0uOttawa Oracle Users Group L Tim Gormanlim im im  Tim Gormanl92 Microsoft PowerPoint 4.0 L@$@Fg/s@R@aZi G6 g  -& &&#TNPP2OMi & TNPP &&TNPP    --- !---&G&ww@ 7CwCw w0- &Gy& &{-f3- $||--&-- -- @"Arialw@  aCwCw w0- . 2  1.-- -- @Times New RomanCwCw w0- (.2 PT The Search( $ . (.*2 for Intelligent Life in    & . (.2 S Oracles Cost.+ . (. 2 -. (.2 Based Optimizer* . 1.@Times New RomanCwCw w0- (.2 , Tim Gorman  . (.2 GT Principal    . (. 2 G- . (.2 GSageLogix, Inc.    .--qXX-- (@BComic Sans MS oCwCw w0- f3.!2 tpwww.SageLogix.Com    .--kY h-- f3@Times New RomanCwCw w0- f3.-2 JOttawa Oracle Users Group,# # #  # #.--"System !H)Bw-&TNPP &՜.+,D՜.+,    (08@ H ;Letter Paper (8.5x11 in)cChrista Guerrieri11SageLogix, Inc.,2 1Times New RomanArialComic Sans MS Courier New PowerPointPowerPoint PresentationAgenda for TodayThe infamous Oracle OptimizerRBORBO Access Path Rankings"RBO Access Path Rankings (contd) RBO exampleRBO example (contd)CBO CBO (contd)So what is cost? Index scansIndex scans (contd)Index scans (contd) SelectivitySelectivity (contd) HistogramsHistograms (contd)Histograms (contd)Histograms (contd) CBO example%Index scans the rest of the storyBasic costing for index scans'Basic costing for index scans (contd)!Cost adjustments for index scansOPTIMIZER_INDEX_CACHINGOPTIMIZER_INDEX_COST_ADJ*Cost adjustments for index scans (contd)#Now add in costs for table accessParameters which affect CBOStatistics to examine Event 10053!Summary for cost-based optimizer More time?B*Tree index architecture#B*Tree index architecture (contd)#B*Tree index architecture (contd)#B*Tree index architecture (contd)#B*Tree index architecture (contd)#B*Tree index architecture (contd)Faster index rebuildsBitmap indexesREVERSE indexesQ & A  Fonts UsedDesign Template Slide Titles, 8@ _PID_HLINKSA|!http://www.evdbt.com/library.htm"_b Tim GormanTim Gorman  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~Root EntrydO)Current UserSummary