1 / 83

A few things about the Optimizer

A few things about the Optimizer. Thomas Kyte http://asktom.oracle.com/. Understanding Access Paths for the RBO.

jud
Télécharger la présentation

A few things about the Optimizer

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. A few things about the Optimizer Thomas Kyte http://asktom.oracle.com/

  2. Understanding Access Paths for the RBO Using the RBO, the optimizer chooses an execution plan based on the access paths available and the ranks of these access paths. Oracle's ranking of the access paths is heuristic. If there is more than one way to execute a SQL statement, then the RBO always uses the operation with the lower rank. Usually, operations of lower rank execute faster than those associated with constructs of higher rank. The list shows access paths and their ranking: • RBO Path 9: Single-Column Indexes • RBO Path 10: Bounded Range Search on Indexed Columns • RBO Path 11: Unbounded Range Search on Indexed Columns • RBO Path 12: Sort Merge Join • RBO Path 13: MAX or MIN of Indexed Column • RBO Path 14: ORDER BY on Indexed Column • RBO Path 15: Full Table Scan • RBO Path 1: Single Row by Rowid • RBO Path 2: Single Row by Cluster Join • RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key • RBO Path 4: Single Row by Unique or Primary Key • RBO Path 5: Clustered Join • RBO Path 6: Hash Cluster Key • RBO Path 7: Indexed Cluster Key • RBO Path 8: Composite Index

  3. Datatypes

  4. Using the Wrong Datatype • Datatypes are constraints • Optimizer uses constraints • Database uses constraints (31-feb-2010) • If you use the wrong data type you will • Lose data integrity • Confuse the optimizer • Spend a lot of CPU converting things so you can use builtin features

  5. Wrong Datatypes ops$tkyte%ORA11GR2> create table t 2 as 3 select object_name, object_type, owner, 4 to_date( '01-jan-2007', 'dd-mon-yyyy' ) + rownum DT, 5 to_char( 6 to_date( '01-jan-2007', 'dd-mon-yyyy' ) + rownum, 7 'YYYYMMDD' 8 ) STR, 9 to_number( 10 to_char( 11 to_date( '01-jan-2007', 'dd-mon-yyyy' ) + rownum, 12 'YYYYMMDD' ) 13 ) NUM 14 from all_objects a 15 order by dbms_random.random 16 / Table created.

  6. Wrong Datatypes ops$tkyte%ORA11GR2> create index dt_idx on t(dt); Index created. ops$tkyte%ORA11GR2> create index str_idx on t(str); Index created. ops$tkyte%ORA11GR2> create index num_idx on t(num); Index created.

  7. Wrong Datatypes ops$tkyte%ORA11GR2> select count(object_type) from t 2 where dt = trunc(sysdate); COUNT(OBJECT_TYPE) ------------------ 1 ops$tkyte%ORA11GR2> select count(object_type) from t 2 where str = to_char(trunc(sysdate),'YYYYMMDD'); COUNT(OBJECT_TYPE) ------------------ 1 ops$tkyte%ORA11GR2> select count(object_type) from t 2 where num = to_number(to_char(trunc(sysdate),'YYYYMMDD')); COUNT(OBJECT_TYPE) ------------------ 1 “seed” dbms_stats if necessary…

  8. Wrong Datatypes ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats(user,'T'); PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> select column_name, histogram 2 from user_tab_columns 3 where table_name = 'T'; COLUMN_NAME HISTOGRAM ------------------------------ --------------- OBJECT_NAME NONE OBJECT_TYPE NONE OWNER NONE DT NONE STR NONE NUM NONE 6 rows selected.

  9. Wrong Datatypes ops$tkyte%ORA11GR2> select count(object_type) from t 2 where str between '20131231' and '20140101'; COUNT(OBJECT_TYPE) ------------------ 2 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 192 (100)| | | 1 | SORT AGGREGATE | | 1 | 18 | | | |* 2 | TABLE ACCESS FULL| T | 327 | 5886 | 192 (1)| 00:00:03 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("STR"<='20140101' AND "STR">='20131231'))

  10. Wrong Datatypes ops$tkyte%ORA11GR2> select count(object_type) from t 2 where num between 20131231 and 20140101; COUNT(OBJECT_TYPE) ------------------ 2 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 192 (100)| | | 1 | SORT AGGREGATE | | 1 | 15 | | | |* 2 | TABLE ACCESS FULL| T | 327 | 4905 | 192 (1)| 00:00:03 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("NUM"<=20140101 AND "NUM">=20131231))

  11. Wrong Datatypes ops$tkyte%ORA11GR2> select count(object_type) from t 2 where dt between to_date( '31-dec-2013', 'dd-mon-yyyy' ) 3 and to_date( '01-jan-2014', 'dd-mon-yyyy' ); COUNT(OBJECT_TYPE) ------------------ 2 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6 (100)| | 1 | SORT AGGREGATE | | 1 | 17 | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 3 | 51 | 6 (0)| 00: |* 3 | INDEX RANGE SCAN | DT_IDX | 3 | | 2 (0)| 00: -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("DT">=TO_DATE(' 2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DT"<=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'

  12. Wrong Datatypes ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats(user,'T', method_opt=>'for all indexed columns'); PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> pause ops$tkyte%ORA11GR2> ops$tkyte%ORA11GR2> select column_name, histogram 2 from user_tab_columns 3 where table_name = 'T'; COLUMN_NAME HISTOGRAM ------------------------------ --------------- OBJECT_NAME NONE OBJECT_TYPE NONE OWNER NONE DT HEIGHT BALANCED STR HEIGHT BALANCED NUM HEIGHT BALANCED 6 rows selected.

  13. Wrong Datatypes ops$tkyte%ORA11GR2> select count(object_type) from t 2 where str between '20131231' and '20140101'; COUNT(OBJECT_TYPE) ------------------ 2 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 192 (100)| | | 1 | SORT AGGREGATE | | 1 | 18 | | | |* 2 | TABLE ACCESS FULL| T | 327 | 5886 | 192 (1)| 00:00:03 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("STR"<='20140101' AND "STR">='20131231'))

  14. Wrong Datatypes ops$tkyte%ORA11GR2> select count(object_type) from t 2 where dt between to_date( '31-dec-2013', 'dd-mon-yyyy' ) 3 and to_date( '01-jan-2014', 'dd-mon-yyyy' ); COUNT(OBJECT_TYPE) ------------------ 2 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 6 (100)| | 1 | SORT AGGREGATE | | 1 | 17 | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 3 | 51 | 6 (0)| 00: |* 3 | INDEX RANGE SCAN | DT_IDX | 3 | | 2 (0)| 00: -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("DT">=TO_DATE(' 2013-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DT"<=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'

  15. Data Patterns Affect How Things Are Done

  16. Knowledge of your data is a mandatory prerequisite to understanding what is happening and why it is happening

  17. Clustering Factor ops$tkyte%ORA11GR2> create table organized 2 as 3 select x.* 4 from (select * from stage order by object_name) x 5 / ops$tkyte%ORA11GR2> create table disorganized 2 as 3 select x.* 4 from (select * from stage order by dbms_random.random) x 5 /

  18. Clustering Factor ops$tkyte%ORA11GR2> create index organized_idx on organized(object_name); Index created. ops$tkyte%ORA11GR2> create index disorganized_idx on disorganized(object_name); Index created.

  19. Clustering Factor ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats 3 ( user, 'ORGANIZED', 4 estimate_percent => 100, 5 method_opt=>'for all indexed columns size 254' 6 ); 7 dbms_stats.gather_table_stats 8 ( user, 'DISORGANIZED', 9 estimate_percent => 100, 10 method_opt=>'for all indexed columns size 254' 11 ); 12 end; 13 /

  20. Clustering Factor ops$tkyte%ORA11GR2> select table_name, blocks, num_rows, 0.05*num_rows, 0.10*num_rows from user_tables 2 where table_name like '%ORGANIZED' order by 1; TABLE_NAME BLOCKS NUM_ROWS 0.05*NUM_ROWS 0.10*NUM_ROWS ------------------------------ ---------- ---------- ------------- ------------- DISORGANIZED 1062 72774 3638.7 7277.4 ORGANIZED 1062 72774 3638.7 7277.4 ops$tkyte%ORA11GR2> select table_name, index_name, clustering_factor from user_indexes 2 where table_name like '%ORGANIZED' order by 1; TABLE_NAME INDEX_NAME CLUSTERING_FACTOR ------------------------------ ------------------------------ ----------------- DISORGANIZED DISORGANIZED_IDX 72727 ORGANIZED ORGANIZED_IDX 1036

  21. Clustering Factor ops$tkyte%ORA11GR2> select /*+ index( organized organized_idx) */ 2 count(subobject_name) 3 from organized; COUNT(SUBOBJECT_NAME) --------------------- 542 ops$tkyte%ORA11GR2> select /*+ index( disorganized disorganized_idx) */ 2 count(subobject_name) 3 from disorganized; COUNT(SUBOBJECT_NAME) --------------------- 542

  22. Clustering Factor select /*+ index( organized organized_idx) */ count(subobject_name) from organized call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.45 0.45 1036 1398 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.45 0.46 1036 1398 0 1 Row Source Operation --------------------------------------------------- SORT AGGREGATE (cr=1398 pr=1036 pw=0 time=456653 us) TABLE ACCESS BY INDEX ROWID ORGANIZED (cr=1398 pr=1036 pw=0 time=376835 us cost=1400… INDEX FULL SCAN ORGANIZED_IDX (cr=362 pr=0 pw=0 time=98362 us cost=363 … 1,398-362 = 1,036 - the clustering factor…

  23. Clustering Factor select /*+ index( disorganized disorganized_idx) */ count(subobject_name) from disorganized call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.83 0.83 1036 73089 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.83 0.83 1036 73089 0 1 Row Source Operation --------------------------------------------------- SORT AGGREGATE (cr=73089 pr=1036 pw=0 time=835554 us) TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=73089 pr=1036 pw=0 time=750651 us … INDEX FULL SCAN DISORGANIZED_IDX (cr=362 pr=0 pw=0 time=96421 us cost=363 … 73,089-362 = 72,727 - the clustering factor…

  24. Clustering Factor ops$tkyte%ORA11GR2> select * from organized where object_name like 'F%'; Execution Plan ---------------------------------------------------------- Plan hash value: 1925627673 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CP ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 149 | 14453 | 6 ( | 1 | TABLE ACCESS BY INDEX ROWID| ORGANIZED | 149 | 14453 | 6 ( |* 2 | INDEX RANGE SCAN | ORGANIZED_IDX | 149 | | 3 ( ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_NAME" LIKE 'F%') filter("OBJECT_NAME" LIKE 'F%')

  25. Clustering Factor ops$tkyte%ORA11GR2> select * from disorganized where object_name like 'F%'; Execution Plan ---------------------------------------------------------- Plan hash value: 3767053355 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost ( ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 149 | 14453 | 152 | 1 | TABLE ACCESS BY INDEX ROWID| DISORGANIZED | 149 | 14453 | 152 |* 2 | INDEX RANGE SCAN | DISORGANIZED_IDX | 149 | | 3 ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_NAME" LIKE 'F%') filter("OBJECT_NAME" LIKE 'F%')

  26. Clustering Factor ops$tkyte%ORA11GR2> select * from organized where object_name like 'A%'; Execution Plan ---------------------------------------------------------- Plan hash value: 1925627673 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CP ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1824 | 172K| 38 ( | 1 | TABLE ACCESS BY INDEX ROWID| ORGANIZED | 1824 | 172K| 38 ( |* 2 | INDEX RANGE SCAN | ORGANIZED_IDX | 1824 | | 12 ( ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_NAME" LIKE 'A%') filter("OBJECT_NAME" LIKE 'A%')

  27. Clustering Factor ops$tkyte%ORA11GR2> select * from disorganized where object_name like 'A%'; Execution Plan ---------------------------------------------------------- Plan hash value: 2727546897 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1824 | 172K| 290 (1)| 00:00:0 |* 1 | TABLE ACCESS FULL| DISORGANIZED | 1824 | 172K| 290 (1)| 00:00:0 ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME" LIKE 'A%')

  28. Not understanding WHY something worked

  29. For Example: • Query runs great in test • Same query runs great in production • Until it doesn’t • Call Support – suggest: gather stats, they seem stale • You do that, query runs great • Until it doesn’t • Gather Stats again, query runs great • Until it doesn’t • Did statistics “fix” it?

  30. +peeked_binds ops$tkyte%ORA11GR2> create table t 2 as 3 select 99 id, a.* 4 from all_objects a 5 where rownum <= 20000; Table created. ops$tkyte%ORA11GR2> update t 2 set id = 1 3 where rownum = 1; 1 row updated. ops$tkyte%ORA11GR2> create index t_idx on t(id); Index created.

  31. +peeked_binds ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats 3 ( user, 'T', 4 method_opt=>'for all indexed columns size 254', 5 estimate_percent => 100, 6 cascade=>TRUE ); 7 end; 8 / PL/SQL procedure successfully completed.

  32. +peeked_binds ops$tkyte%ORA11GR2> variable n number ops$tkyte%ORA11GR2> exec :n := 99; PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> select count(object_type) 2 from t 3 where id = :n; COUNT(OBJECT_TYPE) ------------------ 19999

  33. +peeked_binds ops$tkyte%ORA11GR2> exec :n := 1; PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> select count(object_type) 2 from t 3 where id = :n; COUNT(OBJECT_TYPE) ------------------ 1

  34. +peeked_binds ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor(format=>'+peeked_binds')); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 82 (100)| | | 1 | SORT AGGREGATE | | 1 | 14 | | | |* 2 | TABLE ACCESS FULL| T | 19999 | 273K| 82 (0)| 00:00:01 | --------------------------------------------------------------------------- Peeked Binds (identified by position): -------------------------------------- 1 - :N (NUMBER): 99 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"=:N)

  35. +peeked_binds ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats 3 ( user, 'T', 4 method_opt=>'for all indexed columns size 254', 5 estimate_percent => 100, 6 cascade=>TRUE, NO_INVALIDATE=>FALSE ); 7 end; 8 / PL/SQL procedure successfully completed.

  36. +peeked_binds ops$tkyte%ORA11GR2> select count(object_type) 2 from t 3 where id = :n; COUNT(OBJECT_TYPE) ------------------ 1 ops$tkyte%ORA11GR2> ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor(format=>'+peeked_binds')); -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | 1 | SORT AGGREGATE | | 1 | 14 | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 14 | 2 (0)| 00:0 |* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:0 -------------------------------------------------------------------------------- Peeked Binds (identified by position): -------------------------------------- 1 - :N (NUMBER): 1

  37. Understand WHY it “worked”… This case has nothing to do with statistics and everything to do with bind variable peeking… Or cardinality feedback Or adaptive cursor sharing Or …

  38. Sometimes the explanation just doesn’t sound right..

  39. Explain Plan “lies” • Explain plan should hardly ever be used… • You have to be careful when using autotrace and related tools • Never use “explain=u/p” with tkprof • Avoid dbms_xplan.display, use display_cursor

  40. Explain plan lies… ops$tkyte%ORA11GR2> create table t 2 as 3 select 99 id, to_char(object_id) str_id, a.* 4 from all_objects a 5 where rownum <= 20000; Table created. ops$tkyte%ORA11GR2> update t 2 set id = 1 3 where rownum = 1; 1 row updated. ops$tkyte%ORA11GR2> create index t_idx on t(id); Index created. ops$tkyte%ORA11GR2> create index t_idx2 on t(str_id); Index created.

  41. Explain plan lies… ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats 3 ( user, 'T', 4 method_opt=>'for all indexed columns size 254', 5 estimate_percent => 100, 6 cascade=>TRUE ); 7 end; 8 / PL/SQL procedure successfully completed.

  42. Explain plan lies… Need a volunteer

  43. Explain plan lies… Need a volunteer select count(*) from t where id = :n; What cardinality would you estimate and why?

  44. Explain plan lies… ops$tkyte%ORA11GR2> variable n number ops$tkyte%ORA11GR2> exec :n := 99; PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> set autotracetraceonly explain ops$tkyte%ORA11GR2> select count(*) from t where id = :n; ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 12 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX FAST FULL SCAN| T_IDX | 10000 | 30000 | 12 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"=TO_NUMBER(:N)) <<= a clue right here

  45. Explain plan lies… ops$tkyte%ORA11GR2> select count(*) from t where id = 1; Execution Plan ---------------------------------------------------------- Plan hash value: 293504097 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX RANGE SCAN| T_IDX | 1 | 3 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=1)

  46. Explain plan lies… ops$tkyte%ORA11GR2> select count(*) from t where id = 99; Execution Plan ---------------------------------------------------------- Plan hash value: 1058879072 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 12 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX FAST FULL SCAN| T_IDX | 19999 | 59997 | 12 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"=99)

  47. Explain plan lies… ops$tkyte%ORA11GR2> set autotracetraceonly explain ops$tkyte%ORA11GR2> select object_id from t where str_id = :n; -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:0 | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 19 | 2 (0)| 00:0 |* 2 | INDEX RANGE SCAN | T_IDX2 | 1 | | 1 (0)| 00:0 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("STR_ID"=:N) <<== interesting…

  48. Explain plan lies… ops$tkyte%ORA11GR2> select object_id from t where str_id = :n; OBJECT_ID ---------- 99 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 86 (100)| | |* 1 | TABLE ACCESS FULL| T | 1 | 19 | 86 (0)| 00:00:02 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_NUMBER("STR_ID")=:N) <<= string has to convert..

  49. Explain plan lies… 1 - filter(TO_NUMBER("STR_ID")=:N) <<= string has to convert.. STR_ID ------ 0 00 000 0.00 +0 -0 1,000 1.000

  50. Parallel, how did that happen

More Related