Home » RDBMS Server » Performance Tuning » Forcing optimizer to use full scan
Forcing optimizer to use full scan [message #65006] Sun, 28 March 2004 20:31 Go to next message
Ajendra
Messages: 165
Registered: February 2004
Senior Member
I wish to force the optimzer for not using an index. But it seems it dun works.

Here are the stmts.

SQL>  select * from DAIT_TIME WHERE TIME_KEY = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DAIT_TIME'
   2    1     INDEX (RANGE SCAN) OF 'DAII_PKDAIT_TIME' (UNIQUE)

 

I wish not to use the index. I can give the qry this way

SQL>  select * from DAIT_TIME WHERE TIME_KEY + 1 = 1;

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

Here it shows the table access full.

 

But I am confused with the following stmt. Why it dun use full table scan. I wish to use full table scan. Does Oracle chooses the optimal path of the qry even I force the compiler not to use the index

Also this stmt seems not forcing the optimizer to use full table scan.

SQL> select /*+ NO_INDEX(DAII_PKDAIT_TIME) */ * FROM DAIT_TIME WHERE TIME_KEY = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=7442 Bytes=18
          15848)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DAIT_TIME' (Cost=6 Card=
          7442 Bytes=1815848)

   2    1     INDEX (RANGE SCAN) OF 'DAII_PKDAIT_TIME' (UNIQUE) (Cost=
          2 Card=2977)

 

But this stmt. works

SQL> select /*+ FULL(DAIT_TIME) */ * from DAIT_TIME WHERE TIME_KEY = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=878 Card=7442 Bytes=
          1815848)

   1    0   TABLE ACCESS (FULL) OF 'DAIT_TIME' (Cost=878 Card=7442 Byt
          es=1815848)

 

Could anyone please explain me on it. I am new to SQL tuning.

I am confused with the Cost, Card and Byte no.s. Why in certain stmts it shows the COst, card and Byte and not in Others.

I know I ll be getting the answer soon.

 Thanks

Ajendra

 

 
Re: Forcing optimizer to use full scan [message #65008 is a reply to message #65006] Mon, 29 March 2004 06:28 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
In the 1st case,it looks like you are using Rule based optimizer.

When you specify the hints, CBO is used and hence you see the COST etc.


SQL>  select /*+ NO_INDEX(T_IDX) */ * from t where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
          32)

   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=
          1)

-- Now specify the table and index in the NO_INDEX hint

SQL> select /*+ NO_INDEX(T T_IDX) */ * from t where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=32)

-- or just the table_name to disallow all indexes 

SQL> select /*+ NO_INDEX(T) */ * from t where empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=32)

Re: Forcing optimizer to use full scan [message #65010 is a reply to message #65006] Mon, 29 March 2004 11:45 Go to previous messageGo to next message
Ivan
Messages: 180
Registered: June 2000
Senior Member
Ajendra,
Thiru is absolutely correct.

If you look into Oracle's "Designing and Tuning for Performance" manual

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/hints.htm#4639

you'll notice that the table name is REQUIRED in the NO_INDEX hint
Re: Forcing optimizer to use full scan [message #65015 is a reply to message #65008] Mon, 29 March 2004 21:09 Go to previous messageGo to next message
Ajendra
Messages: 165
Registered: February 2004
Senior Member
Thanks I had missed the table name with the following stmt.
select /*+ NO_INDEX(DAII_PKDAIT_TIME) */ * FROM DAIT_TIME WHERE TIME_KEY = 1;

It should be replaced with

select /*+ NO_INDEX(DAIT_TIME DAII_PKDAIT_TIME) */ * FROM DAIT_TIME WHERE TIME_KEY = 1;

Thanlks for the help.

But could u please explain me on this

I am confused with the Cost, Card and Byte no.s. Why in certain stmts it shows the COst, card and Byte and not in Others.

Thanks
Re: Forcing optimizer to use full scan [message #65018 is a reply to message #65015] Mon, 29 March 2004 23:59 Go to previous messageGo to next message
Chadders
Messages: 18
Registered: November 2003
Junior Member
Cost, Card and Bytes will only show if your optimizer_mode = CHOOSE and you've got valid statistics on one or more of the tables in the query,
or optimizer_mode is FIRST_ROWS or ALL_ROWS (i.e. anything other than RULE).

Sounds as though your optimizer_mode = CHOOSE, and certain statements query tables without stats (and hence, don't show the cost info).

Regards
Re: Forcing optimizer to use full scan [message #65020 is a reply to message #65015] Tue, 30 March 2004 05:02 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Hi
as I mentioned in my earlier post, in your first case , you are using RBO and hence do not see Cost,Card etc. Since your optimizer_mode=CHOOSE and do not have any statistics on any of the tables involved in your query, RBO is used.

In the 2nd case, you may or may not have statistics on the tables,but since you are using hints(ie NO_INDEX), CBO was used and hence you see Cost,Card etc.

-Thiru
Re: Forcing optimizer to use full scan [message #65021 is a reply to message #65018] Tue, 30 March 2004 05:28 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Quote "Cost, Card and Bytes will only show if your optimizer_mode = CHOOSE and you've got valid statistics on one or more of the tables in the query,
or optimizer_mode is FIRST_ROWS or ALL_ROWS (i.e. anything other than RULE)."

Not true.

Cost,Card can show up even if there are not ANY statistics on ANY of the tables in the query , as long as CBO is used and CBO will be used,when you specify hints(other than RULE hint).

SQL> drop table t;

Table dropped.

SQL> create table t as select * from scott.emp;

Table created.

SQL> show parameter optimizer_mode

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
optimizer_mode                       string
CHOOSE

-- There are no statistics on this table

SQL> select num_rows,blocks from user_tables where table_name='T';

  NUM_ROWS     BLOCKS
---------- ----------

SQL> set autotrace traceonly explain
SQL> select /*+ FULL(T) */ * from t;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=284
          49)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=327 Bytes=28449)

-- as seen above, CBO is still used even when there are no statistics on the table,becos of the hint.

-- Now lets go one step further and set the optimizer_goal to RULE

SQL> alter session set optimizer_goal=rule;

Session altered.

SQL> select /*+ FULL(T) */ * from t;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE (Cost=2 Card=2000 Bytes=1740
          00)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=2000 Bytes=174000)

-- although it says Optimizer=RULE , we know that CBO is still forced as we see the Cost,Card etc.



-Thiru
Re: Forcing optimizer to use full scan [message #65024 is a reply to message #65021] Tue, 30 March 2004 20:57 Go to previous messageGo to next message
Chadders
Messages: 18
Registered: November 2003
Junior Member
Yep, fair enough. I'm not a massive believer in hints, but yep, fair cop.

Forcing the use of the CBO in any manner (which was my real point) shows the cost info.
Re: Forcing optimizer to use full scan [message #65028 is a reply to message #65024] Wed, 31 March 2004 17:56 Go to previous messageGo to next message
Ajendra
Messages: 165
Registered: February 2004
Senior Member
Hai Guys
I am sorry, I was busy in some other stuff. Thanks, I got the answer when "cost, card, byte" shows and whn not.

I am yet to get my answer, what is this (Cost=2 Card=2000 Bytes=70000) meant. Does we have any control over it. Could Thiru please explain why for the "Cost=2 Card=327 Bytes=28449" in first case and "Cost=2 Card=2000 Bytes=174000" in secend case (as posted by Thiru, copied those stmts and pasted it below). Is tht bcuz of

alter session set optimizer_goal=rule. How does it affect.

Thanks
Ajendra

****************************************************
SQL> select /*+ FULL(T) */ * from t;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=284 49) 1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=327 Bytes=28449)-- as seen above, CBO is still used even when there are no statistics on the table,becos of the hint.-- Now lets go one step further and set the optimizer_goal to RULESQL> alter session set optimizer_goal=rule;Session altered.

SQL> select /*+ FULL(T) */ * from t;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=RULE (Cost=2 Card=2000 Bytes=1740 00) 1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=2000 Bytes=174000)-- although it says Optimizer=RULE , we know that CBO is still forced as we see the Cost,Card etc.
****************************************************
Re: Forcing optimizer to use full scan [message #65031 is a reply to message #65028] Thu, 01 April 2004 09:44 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Cost is a number the Oracle assigns to each and every step of the execution plan and finally arrives at the final cost for the whole plan . It then does the same thing for the different execution plans that it considers and finally chooses the one that has the lowest cost.

As such *you* cannot compare the Costs of two different queries or even the same query(unless executed in the *exact* same environment(ie optimizer settings,memory areas,statistics etc ).

Card is the estimated cardinality (ie the number of rows in the table or data source).

Bytes is the estimated number of bytes to be processed.

Do we have any control over it ?

Sort of ,not directly though. By updating statistics on the objects involved, by supplying hints, by changing optimizer settings, by changing dbfile_multiblock_read_count ,by creating indexes etc.

But you cannot say , I changed this and so I expect the Cost to go down by so much. A Query with a higher cost may be performing better than the one with the lower cost. Its an internal number used by Oracle based on the environment(ie optimizzer settings,statistics,hints etc etc).

Why is there a difference in the Cost,Card ,between the two same queries ?

We changed the optimizer_goal to Rule for the 2nd one and that changed some things in the internal calculation.

for eg) ,lets see how the Card was calculated in the 1st case.

Cardinality = Number of Blocks below HWM * (db_block_size - block overhead)/Average_Row_length

This table did not have any statistics. So the NBLKS comes from the segment header(remember HWM is stored in the segment header). In this case Number of Blocks below HWM is 4. Db_block_size , in this case = 8192.
Avg_Row_Length is assumed to be 100 , for tables that does not have statistics.

so Cardinality = 4*8192/100 = 327 !

and this is what you saw (ie CHOOSE (Cost=2 Card=327 Bytes=284 49)

Bytes , not too sure on this exact figure of 28449, but I would have guessed to be Cardinality*Avg_Row_length = 327 * 100 = 32700 or NBLKS*Block_size= 4*8K=32K or somewhere around that.

In the 2nd case,although CBO is used,the optimizer_goal was RULE ,that has affected the calcuations internally.

-Thiru
Re: Forcing optimizer to use full scan [message #65035 is a reply to message #65031] Thu, 01 April 2004 23:12 Go to previous messageGo to next message
Ajendra
Messages: 165
Registered: February 2004
Senior Member
Thanks Thiru again. I may be a little boring, but I dun able to resist my doubt to be posted. Here it again. What is the HWM(High Water Mark) please ?

Ajendra
Re: Forcing optimizer to use full scan [message #65036 is a reply to message #65035] Fri, 02 April 2004 04:26 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Highwater mark of the table is the highest point (block/extent) the segment has ever contained data and is stored in the segment header.

Have a look here HWM and
here HWM,Truncate,Delete,Move for a detailed demonstration.

-Thiru

[Updated on: Fri, 18 February 2005 23:32]

Report message to a moderator

Previous Topic: Information on Statistics generation for Partition Tables
Next Topic: Optimizer
Goto Forum:
  


Current Time: Thu Oct 01 03:44:40 CDT 2020