Home » RDBMS Server » Performance Tuning » What is a effecting a analyzing a table for CBO.
What is a effecting a analyzing a table for CBO. [message #65543] Thu, 21 October 2004 03:03 Go to next message
BhavinShah
Messages: 105
Registered: February 2004
Senior Member
Dear frends,

I have read in Oracle Tuning guide that CBO is using data dictionary for generating a plan.

I wanted to know that what is a effect of CBO plan before analyze a table and after analyze table.

Can anybody give me Practical example.

Diffns of Before analyze statistics and after analyze statistics.

Thax in advance..

Bhavin Shah
Re: What is a effecting a analyzing a table for CBO. [message #65548 is a reply to message #65543] Thu, 21 October 2004 05:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Ok.
Lets do some stuff.
--
-- By analyzing the tables, you are collecting the statistics.
-- Before analyze the stats are not collected.
-- after analyze you udpate the stats (if it is already collected).
-- These stats are used by the CBO to identify and lay a shortest path 
-- or 'route' to fetch the data.
-- So by course of time, the data changes in base table.(deletes,updates,inserts).
-- But the CBO still works on the stats you have collected BEFORE.
-- So if you will update the stats, the CBO will understand the changes that has happened.
-- PLEASE follow the session

--
-- Lets create a small table
--

mag@mutation_mutation > create table mytable as select * from dba_segments;

Table created.

--
-- set the tracing options to seeting plan
-- You can see 1322 rows are returned by sql
-- Plan show you nothing about rows returned...
--

mag@mutation_mutation > set autotrace on explain
mag@mutation_mutation > select count(*) from mytable;

  COUNT(*)
----------
      1322

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'MYTABLE'

--
-- Lets analyze the table
--

mag@mutation_mutation > analyze table mytable compute statistics;

Table analyzed.

--
-- run the same sql again
-- Now the plan shows  something called COST and CARD
-- card=1322 means, CBO got these stats based on prior analyze.
-- To fetch 1322 rows the cost is 3.
 

mag@mutation_mutation > select count(*) from mytable;

  COUNT(*)
----------
      1322

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'MYTABLE' (Cost=3 Card=1322)

--
-- Now, lets truncate the table.
-- run the same query.
-- You will find something intresting.
-- even though all records are gone, the CBO still thinks there are some records in table.
-- SO the cost is generated based on that,
-- and THE plan is generated based on that.
-- THis is becuase the STATISTICS was outdated .!!!

mag@mutation_mutation > truncate table mytable;

Table truncated.

mag@mutation_mutation > select count(*) from mytable;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'MYTABLE' (Cost=3 Card=1322)

--
-- Now lets analyze the table and run the same query.
-- with updated statistics
-- The CARD is low and Is the COST.
-- NOTE: This example doesnt deal with real world. SO the plan does not change much.
--

mag@mutation_mutation > analyze table mytable compute statistics;

Table analyzed.

mag@mutation_mutation > select count(*) from mytable;

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'MYTABLE' (Cost=2 Card=1)

Re: What is a effecting a analyzing a table for CBO. [message #65549 is a reply to message #65543] Fri, 22 October 2004 20:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Another example:

SCOTT>
SCOTT>drop table tst
  2  /
drop table tst
           *
ERROR at line 1:
ORA-00942: table or view does not exist 

SCOTT>-- Create table with only 1 value for ID
SCOTT>create table tst as select 1 ID, object_name from all_objects
  2  /

Table created.

SCOTT>
SCOTT>-- insert 1 other value for ID
SCOTT>insert into tst values(2, 'TESTING')
  2  /

1 row created.

Execution Plan
----------------------------------------------------------                      
   0      INSERT STATEMENT Optimizer=CHOOSE                                     

SCOTT>
SCOTT>-- Create index on ID
SCOTT>create index tst_i on tst(id);

Index created.

SCOTT>
SCOTT>-- Make sure we use CBO
SCOTT>alter session set optimizer_mode=choose
  2  /

Session altered.

SCOTT>
SCOTT>-- Autotrace on with explain plan, not interested in results
SCOTT>-- so trace_only
SCOTT>set autotrace trace explain
SCOTT>
SCOTT>-- Select the odd value; should use the index
SCOTT>select * from tst where id = 2
  2  /

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

SCOTT>
SCOTT>-- Select with ID = 1 should NOT use index; Full table scan is
SCOTT>-- much more efficient
SCOTT>select * from tst where id = 1
  2  /

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

SCOTT>
SCOTT>-- CBO does not know the skewness of the data. Analyze will help this
SCOTT>analyze table tst compute statistics for table for all indexes for all indexed columns
  2  /

Table analyzed.

SCOTT>
SCOTT>-- And again our two selections
SCOTT>select * from tst where id = 2
  2  /

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=19)            
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TST' (TABLE) (Cost=2 Car          
          d=1 Bytes=19)                                                         
                                                                                
   2    1     INDEX (RANGE SCAN) OF 'TST_I' (INDEX) (Cost=1 Card=1)             

SCOTT>
SCOTT>select * from tst where id = 1
  2  /

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=58 Card=39042 Bytes=          
          741798)                                                               
                                                                                
   1    0   TABLE ACCESS (FULL) OF 'TST' (TABLE) (Cost=58 Card=39042 B          
          ytes=741798)                                                          
                                                                                

SCOTT>
SCOTT>spool off


hth
Re: What is a effecting a analyzing a table for CBO. [message #65555 is a reply to message #65548] Tue, 26 October 2004 03:03 Go to previous message
BhavinShah
Messages: 105
Registered: February 2004
Senior Member
Thax,

It is a very nice practical explanation.

bhavin
Previous Topic: Parameters changed in INIT.ora
Next Topic: Pl. clear my douts of nested loop join.
Goto Forum:
  


Current Time: Tue Jan 19 22:40:24 CST 2021