Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Ashok.

Asked: April 19, 2002 - 1:34 pm UTC

Last updated: January 30, 2006 - 3:21 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi tom,

Here is my problem.Ihave a table called sf_deal_link_xref which contains around 7500 rows (Currently)

CREATE TABLE sf_deal_link_xref
(
seqid NUMBER(10) NOT NULL,
tr_comps_id VARCHAR2(10),
sfdb_id VARCHAR2(10),
tr_sf_link_date DATE,
tr_sf_link_emp_id VARCHAR2(6),
tr_unlinked_flag VARCHAR2(1),
tr_unlinked_reason VARCHAR2(2000),
clnt_id VARCHAR2(12),
clnt_id_mod_app VARCHAR2(16),
clnt_id_mod_eca VARCHAR2(6),
clnt_id_mod_date DATE,
mod_flag VARCHAR2(1),
gsf_mod_flag VARCHAR2(1),
est_allocated VARCHAR2(1),
allocation_date DATE
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
/

-- Indexes for SF_DEAL_LINK_XREF

CREATE UNIQUE INDEX sf_deal_link_xref_pk
ON sf_deal_link_xref
( seqid )
PCTFREE 10
INITRANS 2
MAXTRANS 255
/

CREATE UNIQUE INDEX sf_deal_link_xref_pk2
ON sf_deal_link_xref
( tr_comps_id,
sfdb_id )
PCTFREE 10
INITRANS 2
MAXTRANS 255
/

CREATE INDEX sf_deal_link_xref_sf
ON sf_deal_link_xref
( sfdb_id )
PCTFREE 10
INITRANS 2
MAXTRANS 255
/

CREATE INDEX sf_deal_link_xref_tr
ON sf_deal_link_xref
( tr_comps_id,
clnt_id )
PCTFREE 0
INITRANS 2
MAXTRANS 255
/

ALTER TABLE sf_deal_link_xref
ADD CONSTRAINT sf_deal_link_xref_pk PRIMARY KEY (seqid)
/

/

ALTER TABLE sf_deal_link_xref
ADD CHECK ("SEQID" IS NOT NULL)
/


When i try to run this simple query which is going for FTS why?
(we are running oracle version 8.1.7 Rule Based)

select tr_comps_id from sf_deal_link_xref where clnt_id =650474 and tr_comps_id = 107192

TIA
Ashok





and Tom said...

One comment. In 817 -- use the CBO. You paid for lots of features you won't be able to use without it. RBO is dead.

Well, two comments -- if your columns contain NUMBERS, use that datatype. Use the correct datatypes.

Well, ok three comments -- primary key IMPLIES not null. That not null constraint is just adding extra CPU overhead to every insert. We'll do that check twice now.

In my book I have a chapter on indexes. One of the sections in that chapter is entitled "Why isn't my index getting used?" You might find this book useful in your working with Oracle

You are quite simply case number 4.

<quote>

Case 4

You have indexed a character column. This column contains only numeric data. You query using the following syntax: 'select * from t where indexed_column = 5'. Note that the number five in the query is a literal number five (not a character string literal). The index on indexed_column is not used. This is because the above query is the same as 'select * from t where to_number(indexed_column) = 5' ? you have implicitly applied a function to the column and as case 3 noted, this will nullify the use of the index. This is very easy to see with a small example:

ops$tkyte@ORA8I.WORLD> create table t ( x char(1) primary key );
Table created.

ops$tkyte@ORA8I.WORLD> insert into t values ( '5' );
1 row created.

ops$tkyte@ORA8I.WORLD> set autotrace on explain

ops$tkyte@ORA8I.WORLD> select * from t where x = 5;

X
-
5


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'



ops$tkyte@ORA8I.WORLD> select * from t where x = '5';

X
-
5


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (UNIQUE SCAN) OF 'SYS_C0038216' (UNIQUE)

You should always avoid implicit conversions in all cases anyway. Always compare apples to apples and oranges to oranges. Another case where this comes up frequently is with dates. You try to query:

-- find all records for today
select * from t where trunc(date_col) = trunc(sysdate);

You discover that the index on DATE_COL will not be used. You can either index the TRUNC of DATE_COL or perhaps more easily query using BETWEEN. The following demonstrates the use of BETWEEN on a date. Once you realize that trunc(date_col) = trunc(sysdate) is the same as ?date_col between sysdate and sysdate PLUS one day MINUS one second?, doing the between is straightforward:

select *
from t
where date_col between trunc(sysdate) and trunc(sysdate)+1/(1*24*60*60)

(Note: the expression 1/(1*24*60*60) is the way to subtract 1 second from a DATE value. Subtracting 1 would take away one day, 1/24 ? one hour, 1/(24*60) one minute).

This moves all of the functions to the right hand side of the equation, allowing us to use the index on DATE_COL (and has the same exact effect as 'where trunc(date_col) = trunc(sysdate)'). You should always remove the functions from database columns when they are in the predicate if possible. Not only will it allow for more indexes to be considered for use, it will reduce the amount of processing the database needs to do. In the above case, when we used:
between trunc(sysdate) and trunc(sydate)+1/(1*24*60*60)
the values are computed once for the query and then an index could be used to find just the qualifying values. When we used trunc(date_col) = trunc(sysdate), the trunc(date_col) had to be evaluated once PER ROW for every row in the entire table (no indexes).
</quote>

Rating

  (20 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perfect Answer

ashok, April 19, 2002 - 4:32 pm UTC


A reader, April 19, 2002 - 6:11 pm UTC

A query runs for 50minutes with the following as part of the WHERE clause:

CALL_NO='500'

and the same query runs for <10secs with

CALL_NO=500

The datatype of CALL_NO is varchar2(3). And there are three indexes on the table, with two of them having CALL_NO as the leading column. And the execution plan shows the following portion:
case 1->
INDEX (RANGE SCAN) OF 'TOTAPAY_CD' (NON-UNI
QUE) (Cost=3 Card=4 Bytes=128)

case 2 ->
INDEX (FAST FULL SCAN) OF 'TOTAPAY_CD' (NON
-UNIQUE) (Cost=23 Card=1 Bytes=32)

why this difference?



Tom Kyte
April 19, 2002 - 6:28 pm UTC

The reason (and this is SHOCKING for many people)

indexes do not mean "you will get your data really fast"


Sometimes, they mean quite the opposite!!!!


As shown above, call_no = 500 is the same as "to_number(call_no) = 500" which means -- no index on call_no will be used in a range scan. Instead, we used more efficient means to get the data.

why mesurement does not support your comment

Tanel Hiir, April 20, 2002 - 7:01 am UTC 

Tom,

regarding your comment:
<quote>
Well, ok three comments -- primary key IMPLIES not null.  That not null constraint is just adding extra CPU overhead to every insert.  We'll do that check twice now.
</quote>

I did mesure and found that NULL column uses less CPU but is *slower* and uses *more* latches...

Is there something wrong with my mesurements?

NOT NULL time:995
NULL time:1058

recursive cpu usage
NOT NULL: 650
NULL: 641

------------------------------------------
SQL> create table run_stats ( runid varchar2(15), name varchar2(80), value int );

Table created.

SQL> 
SQL> create or replace view stats as 
  2    select 'STAT...' || a.name name, b.value
  3    from v$statname a, v$mystat b
  4    where a.statistic# = b.statistic#
  5    union all
  6    select 'LATCH.' || name,  gets
  7    from v$latch;

View created.

SQL> 
SQL> create table TAB_NOT_NULL
  2  ( ID NUMBER(10,0) NOT NULL
  3  , constraint TAB_NOT_NULL_PK primary key (ID)
  4  );

Table created.

SQL> 
SQL> create table TAB_NULL
  2  ( ID NUMBER(10,0) NULL
  3  , constraint TAB_NULL_PK primary key (ID)
  4  );

Table created.

SQL> 
SQL> set serveroutput ON
SQL> 
SQL> declare
  2    iStart1 NUMBER;
  3    iEnd1 NUMBER;
  4    iStart2 NUMBER;
  5    iEnd2 NUMBER;
  6  begin
  7    -- insert into NOT NULL column...
  8    insert into run_stats select 'before', stats.* from stats;
  9    iStart1 := dbms_utility.GET_TIME;
 10    insert into TAB_NOT_NULL (select OBJECT_ID from ALL_OBJECTS);
 11    iEnd1 := dbms_utility.GET_TIME;
 12    -- insert into NULL column...
 13    insert into run_stats select 'after 1', stats.* from stats;
 14    iStart2 := dbms_utility.GET_TIME;
 15    insert into TAB_NULL (select OBJECT_ID from ALL_OBJECTS);
 16    iEnd2 := dbms_utility.GET_TIME;
 17    insert into run_stats select 'after 2', stats.* from stats;
 18    -- 
 19    dbms_output.put_line( 'NOT NULL time:' || to_char(iEnd1-iStart1) );
 20    dbms_output.put_line( 'NULL time:' || to_char(iEnd2-iStart2) );
 21  end;
 22  /
NOT NULL time:995
NULL time:1058

PL/SQL procedure successfully completed.

SQL> 
SQL> select substr(a.name,1,35) as "Stat name"
  2       , b.value-a.value as "NOT NULL case"
  3       , c.value-b.value as "NULL case"
  4       , ((c.value-b.value)-(b.value-a.value)) as "Difference"
  5  from run_stats a, run_stats b, run_stats c
  6  where a.name = b.name
  7    and b.name = c.name
  8    and a.runid = 'before'
  9    and b.runid = 'after 1'
 10    and c.runid = 'after 2'
 11    and (c.value-a.value) > 0
 12    and (c.value-b.value) <> (b.value-a.value)
 13  order by abs( (c.value-b.value)-(b.value-a.value))
 14  /

Stat name                           NOT NULL case  NULL case Difference
----------------------------------- ------------- ---------- ----------
LATCH.active checkpoint queue latch            75         74         -1
LATCH.job_queue_processes parameter             0          1          1
LATCH.loader state object freelist             10         11          1
LATCH.ncodef allocation latch                   0          1          1
STAT...calls to kcmgas                         76         75         -1
STAT...redo log space requests                  5          6          1
LATCH.session switching                         0          1          1
LATCH.transaction branch allocation             0          1          1
STAT...calls to get snapshot scn: k           171        173          2
STAT...commit cleanouts successfull           204        202         -2
STAT...redo buffer allocation retri           109        111          2
STAT...parse time elapsed                       4          2         -2
STAT...commit cleanout failures: bu             0          2          2
STAT...execute count                           20         23          3
STAT...opened cursors cumulative               20         23          3
STAT...session cursor cache hits               13         16          3
STAT...table fetch by rowid                 56993      56996          3
STAT...parse count (total)                     20         23          3
STAT...parse time cpu                           5          2         -3
STAT...enqueue requests                       138        142          4
STAT...enqueue releases                       136        141          5

Stat name                           NOT NULL case  NULL case Difference
----------------------------------- ------------- ---------- ----------
STAT...messages sent                          119        125          6
STAT...no work - consistent read ge         42440      42446          6
STAT...redo entries                         18125      18132          7
LATCH.undo global data                        504        512          8
STAT...db block changes                     36120      36128          8
STAT...buffer is not pinned count           60536      60545          9
STAT...recursive cpu usage                    650        641         -9
LATCH.library cache                           590        600         10
LATCH.enqueue hash chains                     317        328         11
STAT...consistent gets                     207777     207789         12
LATCH.Token Manager                           348        363         15
STAT...write clones created in fore            78         93         15
LATCH.checkpoint queue latch                 1862       1878         16
LATCH.messages                                955        973         18
LATCH.shared pool                             283        260        -23
LATCH.redo allocation                       18537      18561         24
STAT...recursive calls                        161        185         24
STAT...db block gets                        35661      35689         28
LATCH.enqueues                                444        474         30
LATCH.row cache objects                     49146      49182         36
STAT...session logical reads               243438     243478         40

Stat name                           NOT NULL case  NULL case Difference
----------------------------------- ------------- ---------- ----------
STAT...redo log space wait time               233        291         58
LATCH.cache buffers lru chain                 903       1061        158
STAT...free buffer requested                  201        376        175
LATCH.cache buffers chains                 472061     472345        284
STAT...redo size                          5350220    5357292       7072

47 rows selected.

SQL>

Tom Kyte
April 20, 2002 - 3:18 pm UTC 

I think you introduced an un-necessary "side effect" -- potential waits on the all_objects query (or you might have had some wait on log file syncs or something for the second one and not the first).

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t1 ( id number(10,0), constraint t1_pk primary key(id) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t2 ( id number(10,0) check(id is not null), constraint t2_pk primary key(id) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t1 values (0);

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t2 values (0);

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table run_stats;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table run_stats ( runid varchar2(15), name varchar2(80), value int );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace view stats
  2  as select 'STAT...' || a.name name, b.value
  3        from v$statname a, v$mystat b
  4       where a.statistic# = b.statistic#
  5      union all
  6      select 'LATCH.' || name,  gets
  7        from v$latch;

View created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      l_start number;
  3  begin
  4      insert into run_stats select 'before', stats.* from stats;
  5  
  6      l_start := dbms_utility.get_time;
  7      for i in 1 .. 10000
  8      loop
  9          insert into t1 values(i);
 10      end loop;
 11      dbms_output.put_line( 'create table t1 ( id number(10,0), constraint t1_pk primary key(id) );' );
 12      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 13  
 14  
 15      insert into run_stats select 'after 1', stats.* from stats;
 16      l_start := dbms_utility.get_time;
 17      for i in 1 .. 10000
 18      loop
 19          insert into t2 values(i);
 20      end loop;
 21      dbms_output.put_line(
 22      'create table t2 ( id number(10,0) check(id is not null), constraint t2_pk primary key(id) );' );
 23      dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
 24  
 25      insert into run_stats select 'after 2', stats.* from stats;
 26  end;
 27  /
create table t1 ( id number(10,0), constraint t1_pk primary key(id) );
793 hsecs
create table t2 ( id number(10,0) check(id is not null), constraint t2_pk primary key(id) );
834 hsecs

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
  2         ( (c.value-b.value)-(b.value-a.value)) diff
  3    from run_stats a, run_stats b, run_stats c
  4   where a.name = b.name
  5     and b.name = c.name
  6     and a.runid = 'before'
  7     and b.runid = 'after 1'
  8     and c.runid = 'after 2'
  9     and (c.value-a.value) > 0
 10     and (c.value-b.value) <> (b.value-a.value)
 11   order by abs( (c.value-b.value)-(b.value-a.value))
 12  /

NAME                                 RUN1       RUN2       DIFF
------------------------------ ---------- ---------- ----------
....
LATCH.session queue latch             180        623        443
LATCH.direct msg latch                180        631        451
LATCH.ksfv messages                   240        693        453
LATCH.cache buffers lru chain         257        854        597
LATCH.checkpoint queue latch          267        934        667
LATCH.cache buffers chains         131993     132664        671
STAT...session pga memory           33044      59584      26540
STAT...session pga memory max       85120       7508     -77612

53 rows selected.


And the latching differences can be very important as I've said many a time... Latches = Locks, locks = serialization device.  Serialized = less scalable....

Semantic Error

Kurt, April 22, 2002 - 8:40 am UTC

between trunc(sysdate)and trunc(sysdate)+1/(1*24*60*60)
means only then first second of sysdate,
should be
between trunc(sysdate)and trunc(sysdate+1)-1/(1*24*60*60)






Tom Kyte
April 22, 2002 - 9:04 am UTC

correct -- thanks -- lost the +1 on the copy.

Why this is happening?

Ashok, April 22, 2002 - 5:56 pm UTC

I followed your advice by avoding the implicit conversion between number to varchar.

here is interseting thing i came across...

i am using user defined function the select ..total no of seconds to retrieve the 6512 rows is bout 31 sec.But if i remove the user defined function from my select it takes 41 secons why?It's suupose to take less <= 31 sec
(User Defined Function:check_user_IN_DEAL)
Oracle:8.1.7 Rule Based)

If necessary i can produce the statistics.

Here is the query:

select sysdate from dual;

SELECT
"DEAL"."NAME",
"DEAL"."DEAL_ID",
"DEAL_SOURCE"."DEAL_SIZE_AMT",
"DEAL_SOURCE"."CMB_DEAL",
"TR_DEAL_SOURCE"."READY_FOR_GSF_IND" ,
"DEAL"."INFO_ONLY",
check_user_IN_DEAL( "TR_DEAL_SOURCE"."DEAL_ID", 'ADMIN', "TR_DEAL_SOURCE"."READY_FOR_GSF_IND",SEQID),
1 "Rank",
clnt_id,
' ' ,
IN_MARKET_FROM_DT
FROM
sf_deal_link_xref,
"TR_DEAL_SOURCE",
sf_clnts ,
"DEAL_SOURCE" ,
"DEAL",
GBIS.CURRENCY CURNCY,
tr_issclnt_header


WHERE ( "DEAL_SOURCE"."DEAL_ID" = "DEAL"."DEAL_ID" ) and
( "DEAL_SOURCE"."DEAL_ID" = "TR_DEAL_SOURCE"."DEAL_ID" ) and
( "DEAL_SOURCE"."DATA_SOURCE_ID" = "TR_DEAL_SOURCE"."DATA_SOURCE_ID" ) and
( "DEAL_SOURCE"."DATA_SOURCE_ID" = 13 ) and
CURNCY.ID (+) = DEAL_SOURCE.DEAL_CURRENCY_ID and
tr_comps_id = deal.deal_id and
tr_issclnt_header.client_id(+) = sf_deal_link_xref.clnt_id and
sf_deal_link_xref.clnt_id = sf_clnts.id
and tr_comps_id is not null


union


SELECT ' ' "NAME",
'0',
0,
'',
'',
'',
'',
0 "Rank",
clnt_id ,
mktg_legal_name,
SYSDATE

FROM sf_deal_link_xref,
sf_clnts ,
tr_issclnt_header,
tr_deal_source

WHERE
tr_comps_id = tr_deal_source.deal_id and
sf_deal_link_xref.clnt_id = sf_clnts.id and
tr_issclnt_header.client_id(+) = sf_deal_link_xref.clnt_id
and tr_comps_id is not null;

select sysdate from dual;



TIA
Ashok


Tom Kyte
April 22, 2002 - 9:12 pm UTC

You should consider UNION ALL instead of UNION when possible.

Yes, one would need to see a TKPROF report of both queries, their plans, and timings with timed statistics enabled to make sense of anything.

It's very interesting.

Helena Markova, April 23, 2002 - 4:46 am UTC


Here is the statistics

Ashok, April 23, 2002 - 9:44 am UTC

With Function in Select:
Statistics
----------------------------------------------------------
9 recursive calls
10 db block gets
125820 consistent gets
84 physical reads
0 redo size
736495 bytes sent via SQL*Net to client
31225 bytes received via SQL*Net from client
444 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
6612 rows processed

Explain Plan:
------------
2.1 SORT(UNIQUE)
3.1 UNION-ALL
4.1 NESTED LOOPS
5.1 NESTED LOOPS(OUTER)
6.1 NESTED LOOPS
7.1 NESTED LOOPS
8.1 NESTED LOOPS(OUTER)
9.1 NESTED LOOPS
10.1 TABLE ACCESS(FULL) - DEAL
10.2 TABLE ACCESS(BY INDEX ROWID) - DEAL_SOURCE
11.1 INDEX(UNIQUE SCAN) - SYS_C00180065(UNIQUE)
9.2 TABLE ACCESS(BY INDEX ROWID) - CURRENCY
10.1 INDEX(UNIQUE SCAN) - CURRENCY_PK(UNIQUE)
8.2 TABLE ACCESS(BY INDEX ROWID) - TR_DEAL_SOURCE
9.1 INDEX(UNIQUE SCAN) - XAK_TR_DEAL_SOURCE(UNIQUE)
7.2 TABLE ACCESS(BY INDEX ROWID) - SF_DEAL_LINK_XREF
8.1 INDEX(RANGE SCAN) - SF_DEAL_LINK_XREF_TR(NON-UNIQUE)
6.2 INDEX(UNIQUE SCAN) - CLIENT_INDEX(UNIQUE)
5.2 TABLE ACCESS(BY INDEX ROWID) - SF_CLNTS
6.1 INDEX(UNIQUE SCAN) - SF_CLNTS_PK(UNIQUE)
4.2 NESTED LOOPS
5.1 NESTED LOOPS(OUTER)
6.1 NESTED LOOPS
7.1 TABLE ACCESS(FULL) - TR_DEAL_SOURCE
7.2 TABLE ACCESS(BY INDEX ROWID) - SF_DEAL_LINK_XREF
8.1 INDEX(RANGE SCAN) - SF_DEAL_LINK_XREF_TR(NON-UNIQUE)
6.2 INDEX(UNIQUE SCAN) - CLIENT_INDEX(UNIQUE)
5.2 TABLE ACCESS(BY INDEX ROWID) - SF_CLNTS
6.1 INDEX(UNIQUE SCAN) - SF_CLNTS_PK(UNIQUE)




Removed Function from Select:
Statistics
----------------------------------------------------------
9 recursive calls
10 db block gets
125820 consistent gets
79 physical reads
0 redo size
702209 bytes sent via SQL*Net to client
31103 bytes received via SQL*Net from client
444 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
6612 rows processed
Explain Plan:
------------
2.1 SORT(UNIQUE)
3.1 UNION-ALL
4.1 NESTED LOOPS
5.1 NESTED LOOPS(OUTER)
6.1 NESTED LOOPS
7.1 NESTED LOOPS
8.1 NESTED LOOPS(OUTER)
9.1 NESTED LOOPS
10.1 TABLE ACCESS(FULL) - DEAL
10.2 TABLE ACCESS(BY INDEX ROWID) - DEAL_SOURCE
11.1 INDEX(UNIQUE SCAN) - SYS_C00180065(UNIQUE)
9.2 TABLE ACCESS(BY INDEX ROWID) - CURRENCY
10.1 INDEX(UNIQUE SCAN) - CURRENCY_PK(UNIQUE)
8.2 TABLE ACCESS(BY INDEX ROWID) - TR_DEAL_SOURCE
9.1 INDEX(UNIQUE SCAN) - XAK_TR_DEAL_SOURCE(UNIQUE)
7.2 TABLE ACCESS(BY INDEX ROWID) - SF_DEAL_LINK_XREF
8.1 INDEX(RANGE SCAN) - SF_DEAL_LINK_XREF_TR(NON-UNIQUE)
6.2 INDEX(UNIQUE SCAN) - CLIENT_INDEX(UNIQUE)
5.2 TABLE ACCESS(BY INDEX ROWID) - SF_CLNTS
6.1 INDEX(UNIQUE SCAN) - SF_CLNTS_PK(UNIQUE)
4.2 NESTED LOOPS
5.1 NESTED LOOPS(OUTER)
6.1 NESTED LOOPS
7.1 TABLE ACCESS(FULL) - TR_DEAL_SOURCE
7.2 TABLE ACCESS(BY INDEX ROWID) - SF_DEAL_LINK_XREF
8.1 INDEX(RANGE SCAN) - SF_DEAL_LINK_XREF_TR(NON-UNIQUE)
6.2 INDEX(UNIQUE SCAN) - CLIENT_INDEX(UNIQUE)
5.2 TABLE ACCESS(BY INDEX ROWID) - SF_CLNTS
6.1 INDEX(UNIQUE SCAN) - SF_CLNTS_PK(UNIQUE)

Tom Kyte
April 23, 2002 - 10:55 am UTC

Keep running the queries -- both of them. Average the times, you'll find them to be almost the same or the query with the extra code taking longer.

The plans are identical. The blocks processed as well. You must be running on a non-single user system (not a controlled environment) and your runtimes vary from execution to execution.

Use a tool like TKPROF (as I said:

Yes, one would need to see a TKPROF report of both queries, their plans, and
timings with timed statistics enabled to make sense of anything.


) in order to diagnose this. Timing by the wallclock is useless in a mutli-user environment. You need to see CPU time, Elapsed time. TKPROF + TIMED_STATISTICS is the only thing that is useful here.

TKPROF

ashok, April 26, 2002 - 2:50 pm UTC

Here is the TKPROF


With Function on the select
call count cpu elapsed disk query
------- ------ -------- ---------- ---------- ----------
Parse 1 0.02 0.49 0 0
Execute 2 0.00 0.02 0 0
Fetch 443 2.74 9.97 82 125927
------- ------ -------- ---------- ---------- ----------Total 446 0.00 0.00 0 125927


With Function on the select

call count cpu elapsed disk query
------- ------ -------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0
Execute 2 0.00 0.00 0 0
Fetch 443 2.56 9.60 79 125927
------- ------ -------- ---------- ---------- ----------total 446 0.00 0.00 0 125927


Something i have to tune the sql?

TIA





Tom Kyte
April 26, 2002 - 8:31 pm UTC

huh?

both of these are "with function on select"

I have no idea if you "need to tune the sql" or what. The above shows the two queries are *basically the same*. if in fact the above even represents two different queries.

You should leave a TAD more detail in there -- like the query , the plans, etc.

wrong index used when we quote the literal

A reader, June 11, 2003 - 10:27 am UTC

Hi

We have a very strange problem in Oracle 7.3.4

desc tgcprt10.MSEFA_ELEM_FASE

Name Null? Type
------------------------- ------- -------------
MSPED_ID_PEDIDO NOT NULL VARCHAR2(6)
MSFAS_ID_FASE NOT NULL VARCHAR2(6)
MSFAM_ID_FAMILIA NOT NULL VARCHAR2(2)
MSCOM_ID_COMPONENTE NOT NULL VARCHAR2(2)
MSEFA_SEC_COMPONENTE NOT NULL VARCHAR2(15)
MSPAR_ID_PARAMETRO NOT NULL VARCHAR2(6)
MSEFA_SEC_PARAMETRO NOT NULL NUMBER
MSVAL_ID_VALOR NOT NULL VARCHAR2(10)
MSSER_ID_SERVICIO NUMBER
MSEFA_INS_VALOR_GENERICO VARCHAR2(2000)
MSMOV_ID_MOVIMIENTO VARCHAR2(1)
MSPIN_ID_PIN_CLIENTE VARCHAR2(12)
MSEFA_USUARIO_ALTA NOT NULL VARCHAR2(8)
MSEFA_ID_USUARIO_MODIF VARCHAR2(8)
MSEFA_TS_TIMESTAMP NOT NULL DATE
MSDOM_ID_DOM_INSTAL NUMBER



1 SELECT MSMOV_ID_MOVIMIENTO
2 FROM tgcprt10.MSEFA_ELEM_FASE
3 WHERE MSPED_ID_PEDIDO = 'CDR689'
4 AND MSFAS_ID_FASE = '001'
5 AND MSFAM_ID_FAMILIA = 'LI'
6 AND MSCOM_ID_COMPONENTE = 'FR'
7* AND MSEFA_SEC_COMPONENTE = '900000008857229'

88 rows

Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=27)
1 0 TABLE ACCESS (BY ROWID) OF 'MSEFA_ELEM_FASE' (Cost=5 Card=1 Bytes=27)
2 1 INDEX (RANGE SCAN) OF 'MSEFA_ELEM_FASE_FK1' (NON-UNIQUE)



1 SELECT MSMOV_ID_MOVIMIENTO
2 FROM tgcprt10.MSEFA_ELEM_FASE
3 WHERE MSPED_ID_PEDIDO = 'CDR689'
4 AND MSFAS_ID_FASE = '001'
5 AND MSFAM_ID_FAMILIA = 'LI'
6 AND MSCOM_ID_COMPONENTE = 'FR'
7* AND MSEFA_SEC_COMPONENTE = 900000008857229

88 rows

Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=27)
1 0 TABLE ACCESS (BY ROWID) OF 'MSEFA_ELEM_FASE' (Cost=5 Card=1 Bytes=27)
2 1 INDEX (RANGE SCAN) OF 'PK_MSEFA_ELEM_FASE' (UNIQUE)



The first query which uses FK1 is so slow!!!! And it should be the proper query because the MSEFA_SEC_COMPONENTE is varchar2 and we should quote it but by quoting it optimizer uses wrong index....

INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ---------------
PK_MSEFA_ELEM_FASE MSPED_ID_PEDIDO 1
PK_MSEFA_ELEM_FASE MSFAS_ID_FASE 2
PK_MSEFA_ELEM_FASE MSFAM_ID_FAMILIA 3
PK_MSEFA_ELEM_FASE MSCOM_ID_COMPONENTE 4
PK_MSEFA_ELEM_FASE MSEFA_SEC_COMPONENTE 5
PK_MSEFA_ELEM_FASE MSPAR_ID_PARAMETRO 6
PK_MSEFA_ELEM_FASE MSEFA_SEC_PARAMETRO 7
MSEFA_ELEM_FASE_FK1 MSFAM_ID_FAMILIA 1
MSEFA_ELEM_FASE_FK1 MSCOM_ID_COMPONENTE 2
MSEFA_ELEM_FASE_FK1 MSEFA_SEC_COMPONENTE 3
MSEFA_ELEM_FASE_FK1 MSPAR_ID_PARAMETRO 4
MSEFA_ELEM_FASE_FK1 MSEFA_SEC_PARAMETRO 5

how so?

Tom Kyte
June 11, 2003 - 6:57 pm UTC

sorry, 734 is sooo old and the optimizer is sooo much better these days.


but basically -- it is saying the cost of either index is equal -- both are "zero". it is hitting a tie and if I recall in a tie, it sorts them alphabetically. as corny as it sounds, you might recreate that MSEFA... constraint using a character that starts after P.

it was the order

A reader, June 12, 2003 - 7:33 am UTC

Hi

It was the index name which was causing that!

However I have a further query, the query I pasted returns 88 rows which takes like 1 minute. If I use a different predicate (same conditions but different values) to return 2 rows only (must less than 88) it takes 2 hours! Same execution plan (using the FK1 index) how´s that...? Puzzled because samething happens in development database and production

Cheers

Tom Kyte
June 12, 2003 - 9:19 am UTC

tkprof it.

just wondering

A reader, June 13, 2003 - 2:59 am UTC

I wonder how a simple ' ' changes the optimizer to choose different indexes? Is that a bug? The query without ' ' I guess Oracle does something like

column_a = to_char('VALUE_A')
what affected the optimizer?

The proper should be
column_a = 'VALUE_A'

column_a = to_char('VALUE_A') and column_a = 'VALUE_A' to me are the same....

Tom Kyte
June 13, 2003 - 8:08 am UTC

Not when column_a is not a string.

cardinal rule of programming 101:

compare strings to strings.
dates to dates
numbers to numbers

<datatype> to <datatype>


if not -- guess what -- internally, deep down somewhere -- the software must implicitly convert one or the other value into the SAME TYPE as the other one. That is called an implicit conversion (the cause of billions of bugs a day). That implicit conversion is (has to be, since it is) a function. The use of a function on an indexed column -- obviates the use of that index for range scanning purposes. You indexed "x", you did not index "f(x)", hence the index is not useful.




Function on where clause column

Sagar, March 24, 2004 - 3:46 pm UTC

Hi Tom,
Following are explain plans for 2 queries; one using NVL function on columns in where clause and other without. But BOTH the explain plans show index being used. Can you advise?

Thanks,
Sagar

select
1 from ETL_FEED_EXCEPTIONS EFE
WHERE EXISTS
(
SELECT 1 FROM
(
SELECT EX.FEED_INFO_ID, EX.KEY1,EX.ETL_EXCEPTION_ID,
EX.COLUMN_NM,EX.INVALID_COLUMN_VALUE
FROM ETL_FEED_EXCEPTIONS EX, BO_AGING_REP AG
WHERE EX.FEED_INFO_ID = AG.FEED_INFO_ID
AND nvl(EX.KEY1,'zzzzzzzzzzz') = nvl(AG.KEY1,'zzzzzzzzzzz')
AND nvl(EX.ETL_EXCEPTION_ID,99999999999) = nvl(AG.ETL_EXCEPTION_ID,99999999999)
AND nvl(EX.COLUMN_NM,'zzzzzzzzzzz') = nvl(AG.COLUMN_NM,'zzzzzzzzzzz')
AND nvl(EX.INVALID_COLUMN_VALUE,'zzzzzzzzzzz') = nvl(AG.INVALID_COLUMN_VALUE,'zzzzzzzzzzz')
AND EX.AGING_FLG ='N' ) VIEW_A
WHERE EFE.FEED_INFO_ID = VIEW_A.FEED_INFO_ID
AND nvl(EFE.KEY1,'zzzzzzzzzzz') = nvl(VIEW_A.KEY1,'zzzzzzzzzzz')
AND nvl(EFE.ETL_EXCEPTION_ID,99999999999) = nvl(VIEW_A.ETL_EXCEPTION_ID,99999999999)
AND nvl(EFE.COLUMN_NM,'zzzzzzzzzzz') = nvl(VIEW_A.COLUMN_NM,'zzzzzzzzzzz')
AND nvl(EFE.INVALID_COLUMN_VALUE,'zzzzzzzzzzz') = nvl(VIEW_A.INVALID_COLUMN_VALUE,'zzzzzzzzzzz') ) ;

Explain plan:
With NVL -
SELECT STATEMENT Optimizer=CHOOSE (Cost=2766 Card=83645 Bytes=2760285)
FILTER
TABLE ACCESS (FULL) OF ETL_FEED_EXCEPTIONS (Cost=2766 Card=83645 Bytes=2760285)
MERGE JOIN (CARTESIAN) (Cost=2353 Card=1 Bytes=70)
INDEX (RANGE SCAN) OF FEED_EXCEPTION_ID_N5 (NON-UNIQUE) (Cost=2229 Card=1 Bytes=34)
SORT (JOIN) (Cost=124 Card=1 Bytes=36)
INDEX (RANGE SCAN) OF BO_AGING_REP_ID_N5 (NON-UNIQUE) (Cost=124 Card=1 Bytes=36)


select 1 from ETL_FEED_EXCEPTIONS EFE
WHERE EXISTS (
SELECT 1 FROM (
SELECT EX.FEED_INFO_ID, EX.KEY1,EX.ETL_EXCEPTION_ID,EX.COLUMN_NM,EX.INVALID_COLUMN_VALUE
FROM ETL_FEED_EXCEPTIONS EX, BO_AGING_REP AG
WHERE EX.FEED_INFO_ID = AG.FEED_INFO_ID
AND EX.KEY1= AG.KEY1
AND EX.ETL_EXCEPTION_ID = AG.ETL_EXCEPTION_ID
AND EX.COLUMN_NM = AG.COLUMN_NM
AND EX.INVALID_COLUMN_VALUE = AG.INVALID_COLUMN_VALUE
AND EX.AGING_FLG ='N' ) VIEW_A
WHERE EFE.FEED_INFO_ID = VIEW_A.FEED_INFO_ID
AND EFE.KEY1 = VIEW_A.KEY1
AND EFE.ETL_EXCEPTION_ID = VIEW_A.ETL_EXCEPTION_ID
AND EFE.COLUMN_NM = VIEW_A.COLUMN_NM
AND EFE.INVALID_COLUMN_VALUE= VIEW_A.INVALID_COLUMN_VALUE ) ;

Explain plan:
WITHOUT NVL

SELECT STATEMENT Optimizer=CHOOSE (Cost=2766 Card=83645 Bytes=2760285)
FILTER
TABLE ACCESS (FULL) OF ETL_FEED_EXCEPTIONS (Cost=2766 Card=83645 Bytes=2760285)
MERGE JOIN (CARTESIAN) (Cost=5 Card=1 Bytes=70)
INDEX (RANGE SCAN) OF FEED_EXCEPTION_ID_N5 (NON-UNIQUE) (Cost=3 Card=1 Bytes=34)
SORT (JOIN) (Cost=2 Card=1 Bytes=36)
INDEX (RANGE SCAN) OF BO_AGING_REP_ID_N5 (NON-UNIQUE) (Cost=2 Card=1 Bytes=36)

INDEXES:

CREATE INDEX FEED_EXCEPTION_ID_N5 ON
ETL_FEED_EXCEPTIONS(FEED_INFO_ID, KEY1, ETL_EXCEPTION_ID, COLUMN_NM, INVALID_COLUMN_VALUE, AGING_FLG)
TABLESPACE DAC_DATA_IDX_LG PCTFREE 10
STORAGE(INITIAL 2048K NEXT 2048K PCTINCREASE 0 ) ;

CREATE INDEX BO_AGING_REP_ID_N5 ON
BO_AGING_REP(FEED_INFO_ID, KEY1, ETL_EXCEPTION_ID, COLUMN_NM, INVALID_COLUMN_VALUE)
TABLESPACE DAC_DATA_IDX_LG PCTFREE 10
STORAGE(INITIAL 2048K NEXT 2048K PCTINCREASE 0 ) ;

Tom Kyte
March 24, 2004 - 4:55 pm UTC

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:7555175291763 <code>

cbo is very smart.

index not used on DELETE statement

A reader, January 26, 2006 - 8:36 pm UTC

Tom,

I am trying to delete the table with WHERE clause indexed.
But the optimizer is going for FTS instead of index scan.

I am using FIRST_ROWS optimizer.

I tried with making where clause columns to not null but still choosing FTS. All my indexed columns are CHAR(1). Only Y/N characters are stored in those indicator columns.

I analyzed the table using dbms_stats as shown below :

exec dbms_stats.gather_table_stats(user, tabname => 'COMPANY2MCN_GRC', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all indexed columns size auto', cascade => true, degree => dbms_stats.default_degree) ;

But if i forced the optimizer to use INDEX, it is doing index range scan.

Just wondering, why optimizer is not choosing index scan when it is appropriate to do ?

Explain plans are below :

======================================================
select distinct SWVOICE_IND, DATA_IND, NODAL_IND from COMPANY2MCN_GRC ;

S D N
- - -
N N Y
N Y N
N Y Y
Y N N
Y N Y
Y Y N
Y Y Y

7 rows selected.

desc COMPANY2MCN_GRC
Name Null? Type
----------------------------------------- -------- ----------------------------
COMPANY2MCN_GRC_ID NOT NULL NUMBER(11)
COMPANY_ID NOT NULL VARCHAR2(11)
MCN VARCHAR2(9)
GRC VARCHAR2(3)
SOC VARCHAR2(2)
DATA_IND NOT NULL CHAR(1)
SWVOICE_IND NOT NULL CHAR(1)
NODAL_IND NOT NULL CHAR(1)
VALID_IND CHAR(1)
IPMIS_IND CHAR(1)
BILLING_SVC_TYPE NUMBER(2)
BAN VARCHAR2(13)
SAN VARCHAR2(13)
LEAD_ACCT_NUM VARCHAR2(13)
SUB_ACCT_NUM VARCHAR2(13)
GRC_START_DATE DATE
COMPANY_NAME VARCHAR2(80)
CDG VARCHAR2(25)
LVOICE_IND VARCHAR2(1)
CCARD_IND VARCHAR2(1)
VOIP_IND VARCHAR2(1)

select count(*) from COMPANY2MCN_GRC ;

COUNT(*)
----------
2455764

DELETE COMPANY2MCN_GRC WHERE SWVOICE_IND = 'N' AND DATA_IND = 'N' AND NODAL_IND = 'N' ;

0 rows deleted.

Elapsed: 00:00:02.57

Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=FIRST_ROWS (Cost=797 Card=692238
Bytes=29073996)

1 0 DELETE OF 'COMPANY2MCN_GRC'
2 1 TABLE ACCESS (FULL) OF 'COMPANY2MCN_GRC' (Cost=797 Card=
692238 Bytes=29073996)

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


delete /*+ INDEX(COMPANY2MCN_GRC COMPANY2MCN_GRC_IDX1) */ COMPANY2MCN_GRC WHERE SWVOICE_IND = 'N' AND DATA_IND = 'N' AND NODAL_IND = 'N' ;

0 rows deleted.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=FIRST_ROWS (Cost=19350 Card=69223
8 Bytes=29073996)

1 0 DELETE OF 'COMPANY2MCN_GRC'
2 1 INDEX (RANGE SCAN) OF 'COMPANY2MCN_GRC_IDX1' (NON-UNIQUE
) (Cost=1643 Card=692238 Bytes=29073996)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
786 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
0 rows processed



Tom Kyte
January 27, 2006 - 8:32 am UTC

how do you gather statistics, exact precise command.

and are the statistics up to date.

you do see that it believes it will hit 692,238 rows given your predicate right?

A reader, January 27, 2006 - 10:36 am UTC

Tom,

I gathered stats using dbms_stats as shown below :

exec dbms_stats.gather_table_stats(user, tabname => 'COMPANY2MCN_GRC',
estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all indexed
columns size auto', cascade => true, degree => dbms_stats.default_degree) ;

Tom Kyte
January 27, 2006 - 11:30 am UTC

and are the stats up to date or not up to date and what is your version.

and if you compute (given this is a pretty small table)...


It is likely the fact that you have a "correlation" between these columns:

WHERE SWVOICE_IND = 'N' AND DATA_IND = 'N' AND NODAL_IND = 'N' ;


and the optimizer knows "50% of the data is N for swvoice_ind", 50% is N for data_ind and 50% is N for nodal_ind

So, the optimizer thinks 1/2*1/2*1/2 or 1/8 of the table is going have this be true...

but when nodal_ind is N, the odds of data_ind being N are near zero (for example - not saying this is the case - just saying FOR EXAMPLE)

So, in reality, almost none of the table is going to have this be true...



Dynamic Sampling can be used to permit the optimizer to understand this during the optimization phase - it'll likely be what we'll be looking at.

A reader, January 27, 2006 - 12:52 pm UTC 

Thanks Tom.

Database version is : 9205 

STATS on table and indexes are upto date on this table.

select table_name, last_analyzed, num_rows from user_tables where table_name='COMPANY2MCN_GRC' ;

TABLE_NAME                     LAST_ANAL   NUM_ROWS
------------------------------ --------- ----------
COMPANY2MCN_GRC                26-JAN-06    2455764

iomdbo@GIOM> select count(*) from COMPANY2MCN_GRC ;

  COUNT(*)
----------
   2455764

I tried with DBMS_STATS.GATHER_TABLE_STATS(ESTIMATE_PERCENT => NULL) which i believe equivalent to COMPUTE (as shown below), execution plan did not change. Still going for FTS.

exec dbms_stats.gather_table_stats(user, tabname => 'COMPANY2MCN_GRC', estimate_percent => NULL, method_opt => 'for all indexed columns size auto', cascade => true, degree => dbms_stats.default_degree)


Here is the optimizer init setting i have :

SQL> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     1
optimizer_features_enable            string      9.2.0
optimizer_index_caching              integer     85
optimizer_index_cost_adj             integer     10
optimizer_max_permutations           integer     2000
optimizer_mode                       string      FIRST_ROWS


Could you explain what you mean by correlation between the "indicator" columns. 

How dynamic sampling can be used to come up with the right plan.

Tom Kyte
January 28, 2006 - 12:20 pm UTC 

by correlation I mean something like this:

ops$tkyte@ORA10GR1> create table t
  2  as
  3  select decode( mod(rownum,2), 0, 'N', 'Y' ) flag1,
  4         decode( mod(rownum,2), 0, 'Y', 'N' ) flag2,
  5             a.*
  6    from all_objects a
  7  /

Table created.

<b>there is a correlation between flag1 and flag2 here - namely, if flag1 = N, then flag2 isn't N and vice versa! (sort of like your data)...

so consider:</b>

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> create index t_idx on t(flag1,flag2);

Index created.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> begin
  2          dbms_stats.gather_table_stats
  3          ( user, 'T',
  4            method_opt => 'for all indexed columns size 254',
  5            cascade=> true );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> set autotrace traceonly explain
ops$tkyte@ORA10GR1> select * from t where flag1 = 'N';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=160 Card=23560 Bytes=2285320)
   1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=160 Card=23560 Bytes=2285320)

<b>that card = is correct</b>

ops$tkyte@ORA10GR1> select * from t where flag2 = 'N';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=160 Card=24148 Bytes=2342356)
   1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=160 Card=24148 Bytes=2342356)

<b>that card = is correct</b>

ops$tkyte@ORA10GR1> select * from t where flag1 = 'N' and flag2 = 'N';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=160 Card=11925 Bytes=1156725)
   1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=160 Card=11925 Bytes=1156725)

<b>that one, well, that one is off by QUITE A BIT.  The first two are right (1/2 of the data), the third one here is computed using "statistics 101" from high school.  If 1/2 are flag1=n and 1/2 are flag2=n, then 1/2*1/2 are flag1 = n and flag2 = n.  But, because of the correlation between flag1 and flag2 - that is wrong

see:</b>


ops$tkyte@ORA10GR1> set autotrace off
ops$tkyte@ORA10GR1> select count( case when flag1 = 'N' then 1 end ) flag1_n,
  2         count( case when flag2 = 'N' then 1 end ) flag2_n,
  3         count( case when flag1 = 'N' and flag2 = 'N' then 1 end ) both_n
  4    from t;

   FLAG1_N    FLAG2_N     BOTH_N
---------- ---------- ----------
     23854      23854          0

ops$tkyte@ORA10GR1> set autotrace traceonly explain

<b>so, for this, perhaps dynamic sampling is what we need:</b>

ops$tkyte@ORA10GR1> select /*+ dynamic_sampling(t 3) */ * from t where flag1 = 'N' and flag2 = 'N';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=4 Bytes=388)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=4 Bytes=388)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=4)



ops$tkyte@ORA10GR1> set autotrace off


<b>card = is much much "better"</b>

A reader, January 29, 2006 - 12:35 pm UTC

Tom,

Thanks for the dynamic sampling working example.

In my case, if i do a QUERY as below, it is doing index range scan which seems to be the right query plan.
But if you try to DELETE with the same exact WHERE clause with or without dynamic sampling the execution plan is FTS.

The difference is optimizer_mode, mine is "FIRST_ROWS".

Explain plans are shown below :

SELECT * FROM COMPANY2MCN_GRC WHERE SWVOICE_IND = 'N' AND DATA_IND = 'N' AND NODAL_IND = 'N' ;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5556 Card=199722
Bytes=19972200)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COMPANY2MCN_GRC' (Cost=5
556 Card=199722 Bytes=19972200)

2 1 INDEX (RANGE SCAN) OF 'COMPANY2MCN_GRC_IDX1' (NON-UNIQUE
) (Cost=476 Card=199722)

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

DELETE /*+ dynamic_sampling(COMPANY2MCN_GRC 3) */ COMPANY2MCN_GRC WHERE SWVOICE_IND = 'N' AND DATA_IND = 'N' AND NODAL_IND = 'N' ;

0 rows deleted.


Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=FIRST_ROWS (Cost=797 Card=12279 B
ytes=527997)

1 0 DELETE OF 'COMPANY2MCN_GRC'
2 1 TABLE ACCESS (FULL) OF 'COMPANY2MCN_GRC' (Cost=797 Card=
12279 Bytes=527997)

Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
20725 consistent gets
3394 physical reads
0 redo size
794 bytes sent via SQL*Net to client
778 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed


Tom Kyte
January 29, 2006 - 1:31 pm UTC

since a delete never returns until, well, it is done - first_rows doesn't really mean very much.

here is it thinking 12,279 rows out of 2.1 million and the cost of accessing 12,279 rows via the index was apparently costed out to be higher than the cost of the full scan.

do you have system statistics. (not stats on the dictionary, but system statistics - io speeds, cpu speeds and the like)

have you way overset your multiblock read count.

you can also try larger samples with dynamic sampling ( - in order to get the 12,000 "even closer" to zero which it reality. try 5 or 6 instead of "3"

A reader, January 29, 2006 - 6:38 pm UTC

Tom,

MBRC on my database is :

db_file_multiblock_read_count integer 32

I tried with dynamic sampling size of 5 / 6, optimizer still chose FTS.

STATS were gathered using the command :

exec dbms_stats.gather_table_stats(user, tabname => 'COMPANY2MCN_GRC', method_opt => 'for all indexed columns size auto', cascade => true, degree => dbms_stats.default_degree)


Given below are SYSTEM stats with or without dynamic sampling after ran the DELETE statement :


SYSTEM STATS without Dynamic sampling :
+++++++++++++++++++++++++++++++++++++++

1* select * from sys.aux_stats$
system /

SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO STATUS BADSTATS
SYSSTATS_INFO DSTART 01-29-2006 18:19
SYSSTATS_INFO DSTOP 01-29-2006 18:20
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN SREADTIM -1
SYSSTATS_MAIN MREADTIM .72
SYSSTATS_MAIN CPUSPEED 538
SYSSTATS_MAIN MBRC 30
SYSSTATS_MAIN MAXTHR -1
SYSSTATS_MAIN SLAVETHR -1

10 rows selected.


System stats with Dynamic sampling 6 :
++++++++++++++++++++++++++++++++++++++

system> select * from sys.aux_stats$ ;

SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO STATUS BADSTATS
SYSSTATS_INFO DSTART 01-29-2006 18:22
SYSSTATS_INFO DSTOP 01-29-2006 18:23
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN SREADTIM -1
SYSSTATS_MAIN MREADTIM .543
SYSSTATS_MAIN CPUSPEED 538
SYSSTATS_MAIN MBRC 29
SYSSTATS_MAIN MAXTHR -1
SYSSTATS_MAIN SLAVETHR -1

10 rows selected.


Tom Kyte
January 30, 2006 - 1:49 am UTC

but what were the cardinalities, it all comes back to that. when you sampled with 5, 6 what were the estimated cardinalities (and if you let optimizer parameters default - you can test in your session using alter session to put them at defaults - what do you see then)

what do you mean by system stats with and without dynamic sampling? they are not related - system stats are gathered when the system is doing a representative load, dynamic sampling happens during a hard parse - not sure what you mean?

A reader, January 29, 2006 - 10:16 pm UTC

Tom:

This is further update from the previous post.

After changing the following optimizer parameters to the values shown below, optimizer finally chose index range scan for both the cases (one with out dynamic sampling and another with dynamic sample level 5).

optimizer_index_caching integer 90
optimizer_index_cost_adj integer 10

Since the regular delete without dynamic sample hint performs the same consistent gets as the other one, i think dynamic sample hint is not necessary.

Given below are the execution plan + system statistics :
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


1* DELETE COMPANY2MCN_GRC WHERE SWVOICE_IND = 'N' AND DATA_IND = 'N' AND NODAL_IND = 'N'
iomdbo@GIOM> /

0 rows deleted.


Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=559 Card=199722 Byte
s=8588046)

1 0 DELETE OF 'COMPANY2MCN_GRC'
2 1 INDEX (RANGE SCAN) OF 'COMPANY2MCN_GRC_IDX1' (NON-UNIQUE
) (Cost=476 Card=199722 Bytes=8588046)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
801 bytes sent via SQL*Net to client
733 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
0 rows processed

select * from sys.aux_stats$ ;

SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO STATUS BADSTATS
SYSSTATS_INFO DSTART 01-29-2006 21:55
SYSSTATS_INFO DSTOP 01-29-2006 21:55
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN SREADTIM -1
SYSSTATS_MAIN MREADTIM -1
SYSSTATS_MAIN CPUSPEED 538
SYSSTATS_MAIN MBRC -1
SYSSTATS_MAIN MAXTHR -1
SYSSTATS_MAIN SLAVETHR -1

10 rows selected.

1* DELETE /*+ DYNAMIC_SAMPLING(COMPANY2MCN_GRC 5) */ COMPANY2MCN_GRC WHERE SWVOICE_IND = 'N' AND DATA_IND = 'N' AND NODAL_IND = 'N'
iomdbo@GIOM> /

0 rows deleted.

Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=559 Card=12279 Bytes
=527997)

1 0 DELETE OF 'COMPANY2MCN_GRC'
2 1 INDEX (RANGE SCAN) OF 'COMPANY2MCN_GRC_IDX1' (NON-UNIQUE
) (Cost=476 Card=12279 Bytes=527997)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
801 bytes sent via SQL*Net to client
776 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
0 rows processed

select * from sys.aux_stats$ ;

SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO STATUS BADSTATS
SYSSTATS_INFO DSTART 01-29-2006 21:57
SYSSTATS_INFO DSTOP 01-29-2006 21:58
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN SREADTIM -1
SYSSTATS_MAIN MREADTIM -1
SYSSTATS_MAIN CPUSPEED 540
SYSSTATS_MAIN MBRC -1
SYSSTATS_MAIN MAXTHR -1
SYSSTATS_MAIN SLAVETHR -1

10 rows selected.

Tom Kyte
January 30, 2006 - 1:51 am UTC

I'm afraid you are missing the point of the dynamic sampling - which is to get more correct cardinalities - which lead to more correct plans. Also, you must be gathering system stats without any sort of workload which isn't going to work.

would you happen to have access to "Effective Oracle By Design" - you might be interested in chapter 6 to learn more about dynamic sampling, system statistics and such..

A reader, January 30, 2006 - 11:22 am UTC

Tom,

The system stats which were provided above was taken when the system was idle and only i was the user on the database.
This database is especially used for performance testing.

I already have your book (Effective Oracle by Design) and followed the CH.6 in there before putting the SYSTEM stats up above. I will look it again if i am missing anything.

As you can see, both the cases (with or without dynamic sampling), optimizer produced the same plan with only difference is CARD values.

In reality, the query with dynamic sampling CARD's value is less atleast (not equivalent to zero) in comparison with
the another one.

Just wondering, How best to reduce the card value to zero.
I tried all different levels and could not bring down the CARD value to 0 for dynamic sampling delete.

Appreciate your feedback.



Tom Kyte
January 30, 2006 - 3:21 pm UTC

you cannot gather systems stats on that box without generating some load then. In the book - I generated a big load to gather them. Or you can use NOWORKLOAD to have dbms_stats generate the workload:

</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#sthref8122 <code>

if you have the current version.



And until the card values get to be closer to reality, you cannot expect the plan to be "the best it can be". you say you did 5, you did 6, did you stop there?

In 10g, we might be able to solve this with a sql profile (dynamic sampling in a grand fashion).

A reader, January 30, 2006 - 5:57 pm UTC

Thanks for the suggestions Tom. I will take a look at it.

<< In 10g, we might be able to solve this with a sql profile (dynamic sampling in a grand fashion).

one of the reasons to upgrade to 10g latest release.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.